Shulou(Shulou.com)06/01 Report--

This article mainly shows you "what is the data query sentence in MySQL and PHP", the content is simple and clear, and I hope it can help you solve your doubts. Let me lead you to study and learn "what is the data query statement in MySQL and PHP".

ORDER BY

In SQL, we can use ORDER BY to sort query results in one or more columns.

SQL statement:

SELECT column name 1, column name 2 FROM table name 1, table name 2

ORDER BY column name, column name [ASC | DESC]

Note:

ASC means to sort in ascending order, and DESC means to sort in descending order

Sort columns in ascending order by default

Table record:

Example 1

Wages are sorted from low to high:

Output result:

Server connected successfully!

The SQL statement was executed successfully!

Array

(

[id] = > 3

[username] = > Wang Fugui

[password] = > 123456

[sex] = > 1

[salary] = > 1000.00

)

Array

(

[id] = > 4

[username] > Uncle Li

[password] = > 123123

[sex] = > 1

[salary] = > 1500.00

)

Array

(

[id] = > 2

[username] = > Zhang Mei

[password] = > 123456

[sex] = > 2

[salary] = > 5000.00

)

Array

(

[id] = > 5

[username] = > Wang Ergu

[password] = > 123123

[sex] = > 1

[salary] = > 6000.00

)

Array

(

[id] = > 1

[username] = > I am a rookie

[password] = > 123456

[sex] = > 2

[salary] = > 30000.00

)

Example 2

Wages are ranked from high to low:

LIMIT

We can use LIMIT in the SELECT statement to constrain the number of records to be returned, usually using LIMIT for paging.

SQL statement:

SELECT column name 1, column name 2 FROM Table 1, Table 2 LIMIT [offset] rows

Note:

The offset of the first line is 0, not 1

Number of rows is the maximum number of rows returned

Example 1

Take the first two data:

Output result:

Server connected successfully!

The SQL statement was executed successfully!

Array

(

[id] = > 1

[username] = > I am a rookie

[password] = > 123456

[sex] = > 2

[salary] = > 30000.00

)

Array

(

[id] = > 2

[username] = > Zhang Mei

[password] = > 123456

[sex] = > 2

[salary] = > 5000.00

)

Example 2

Take items 2 and 3:

Output result:

Server connected successfully!

The SQL statement was executed successfully!

Array

(

[id] = > 2

[username] = > Zhang Mei

[password] = > 123456

[sex] = > 2

[salary] = > 5000.00

)

Array

(

[id] = > 3

[username] = > Wang Fugui

[password] = > 123456

[sex] = > 1

[salary] = > 1000.00

)

GROUP BY

Literally, GROUP BY means grouping data according to certain rules. It must be used with aggregate function. After grouping the data, it can perform COUNT, SUM, AVG, MAX and MIN operations.

SQL statement:

SELECT column name, aggregate function (column name)

FROM table name

GROUP BY column name

Note:

GROUP BY can group one or more columns

Table record:

Example 1

Output result:

Server connected successfully!

The SQL statement was executed successfully!

Array

(

[gender] = > 1

[count (*)] = > 3

)

Array

(

[gender] = > 2

[count (*)] = > 6

)

Example 2

Output result:

Server connected successfully!

The SQL statement was executed successfully!

Array

(

[grade] = > Senior three

[count (*)] = > 2

)

Array

(

[grade] = > Grade one of senior high school

[count (*)] = > 4

)

Array

(

[grade] = > Grade two of high school

[count (*)] = > 3

)

HAVING

HAVING can solve the problem that WHERE keywords cannot be used with aggregate functions. HAVING can filter the grouped data.

SQL statement:

SELECT column name, aggregate function (column name)

FROM table name

GROUP BY column name

HAVING aggregate function (column name) condition

Example 1

Output result:

Server connected successfully!

The SQL statement was executed successfully!

Array

(

[grade] = > Grade one of senior high school

[count (*)] = > 4

)

Array

(

[grade] = > Grade two of high school

[count (*)] = > 3

)

Example 2

Output result:

Server connected successfully!

The SQL statement was executed successfully!

Array

(

[gender] = > 2

[count (*)] = > 6

)

GROUP_CONCAT

When we use GROUP BY, we can get the grouping, but we can't see the specific information. At this point, we can use GROUP_CONCAT to get specific information. GROUP_CONCAT is used with GROUP BY to concatenate the values of a column according to the specified delimiter (default is ",").

SQL statement:

GROUP_CONCAT (column name [sort ASC/DESC] [delimiter])

Example 1

Output result:

Database link successful SQL statement execution successful! Array

(

[grade] = > Grade one of senior high school

[count (*)] = > 4

[GROUP_CONCAT (student_name)] = > Wang Fugui, Yang Mei

)

Array

(

[grade] = > Senior three

[count (*)] = > 2

[GROUP_CONCAT (student_name)] = > Yang Mei, Yang Mei

)

Array

(

[grade] = > Grade two of high school

[count (*)] = > 3

[GROUP_CONCAT (student_name)] = > Yang Mei, Yang Mei, Zhao Youcai

)

Example 2

Output result:

Database link successful SQL statement execution successful! Array

(

[gender] = > 1

[count (*)] = > 3

[GROUP_CONCAT (student_name)] = > Yang Mei, Yang Mei, Yang Mei

)

Array

(

[gender] = > 2

[count (*)] = > 6

[GROUP_CONCAT (student_name)] = > Wang Fugui, Yang Mei, Zhao Youcai

)

DISTINCT

DISTINCT is used to return different values of the parent in a query, supporting single or multiple columns. In practical applications, it is common that a column in a table contains duplicate values. If you want to get all the different values of a column when querying data, you can use DISTINCT.

SQL statement:

SELECT DISTINCT column name 1, column name 2 FROM table name

Example 1

Output result:

Database link successful SQL statement execution successful! Array

(

[grade] = > Senior three

)

Array

(

[grade] = > Grade one of senior high school

)

Array

(

[grade] = > Grade two of high school

)

Example 2

Output result:

Database link successful SQL statement execution successful! Array

(

[gender] = > 1

)

Array

(

[gender] = > 2

)

These are all the contents of the article "what are the data query statements in MySQL and PHP?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.