Before explaining the query, I prepared a data table for everyone. This table stores the bank's balance and basic information about the user.

We defined a table structure named money.

The statement to create the table is as follows:

CREATE TABLE money (
id INT NOT NULL AUTO_INCREMENT ,
  username VARCHAR(50) NOT NULL ,
  balance FLOAT NOT NULL ,
  province VARCHAR(20) NOT NULL ,
age TINYINT UNSIGNED NOT NULL ,
sex TINYINT NOT NULL ,
PRIMARY KEY (id(10))
) ENGINE = InnoDB CHARACTER SET utf8;

The table structure and data are displayed as follows:

##idusernamebalanceprovinceagesex##123##4Jing Boran810Liaoning271Li Bingbing##6Jackie Chan313Shandong6317Yang Mi123北京300456##9Liu Yan23.4Hunan360Liaoning##11王峰34.32Beijing44143 Note: province refers to the provinceBasic query

Wang Baoqiang	120.02	上海	29	1
Fan Bingbing	260.23	Shandong	40	0
黄晓明	150.86	Shandong	40	1
					##5
20.15	Heilongjiang	43	0
				##8	Liu Shishi
Beijing	29	1
			##10	Zhao Benshan	3456
63	1
		##12	Guo Degang	212	天津
1			balance refers to the balance

Category

Detailed explanation

##Basic syntaxselect * from Table;Exampleselect * from money;Example descriptionQuery the money table All results in all fields

Note: "*" is a regular expression, which means matching everything. The above query statement is equivalent to the following:

mysql> select * from money;
+- ---+-----------+----------+-----------+-----+-----+
| id | username | balance | province | age | sex |
+----+-----------+---------+--- --------+-----+-----+
| 1 | Wang Baoqiang | 120.02 | Hubei | 29 | 1 |
| 2 | Fan Bingbing | 260.23 | Shandong | 40 | 0 |
| 3 | Huang Xiaoming | 150.86 | Shandong | 40 | 1 |
| 4 | Jing Boran | 810 | Liaoning | 27 | 1 |
| 5 | Li Bingbing | 20.15 | Heilongjiang | 43 | 0 |
| 6 | Jackie Chan | 313 | Shandong | 63 | 1 |
| 7 | Yang Mi | 123 | Beijing | 30 | 0 |
| 8 | Liu Shishi | 456 | Beijing | 29 | 1 |
| 9 | Liu Yan | 23.4 | Hunan | 36 | 0 |
| 10 | Zhao Benshan | 3456 | Liaoning | 63 | 1 |
| 11 | Wang Feng | 34.32 | Beijing | 44 | 1 |
| 12 | Guo Degang | 212 | Tianjin | 43 | 1 |
+----+-----------+----------+------ -----+-----+-----+
12 rows in set (0.00 sec)

Specify field query

##Basic syntaxselect field from table;Exampleselect id,username, balance from money;Example descriptionQuery id,username, in the money table All results in the balance field

mysql> select id,username, balance from money;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 1 | Wang Baoqiang | 120.02 |
| 2 | Fan Bingbing | 260.23 |
| 3 | Huang Xiaoming | 150.86 |
| 4 | Jing Boran | 810 |
| 5 | Li Bingbing | 20.15 |
| 6 | Jackie Chan | 313 |
| 7 | Yang Mi | 123 |
| 8 | Liu Shishi | 456 |
| 9 | Liu Yan | 23.4 |
| 10 | Zhao Benshan | 3456 |
| 11 | Wang Feng | 34.3 2 |
| 12 | Guo Degang | 212 |
+----+-----------+---------+
12 rows in set (0.00 sec)

Query a single field for non-duplicate records distinct

Category	Detailed explanation

Category	Detailed explanation
Basic syntax	select distinct field from table;
Example	select distinct age deptno from money;
Example description	Query all results with unique age in the money table

##mysql> select distinct age deptno from money;

+--------+
| deptno |
+--------+
| 29 |
| 40 |
| 27 |
| 43 |
| 63 |
| 30 |
| 36 |
| 44 |
+--------+
8 rows in set (0.00 sec )

Conditional query where

CategoryDetailed explanationBasic syntaxselect field from table where where condition;Exampleselect * from money where age = 29;Example descriptionQuery all results with age 29 in the money table

mysql> select * from money where age = 29;
+----+-----------+---------+----- -----+-----+-----+
| id | username | balance | province | age | sex |
+----+------- ----+---------+----------+-----+-----+
| 1 | Li Wenkai | 120.02 | Hubei | 29 | 1 |
| 8 | Liu Shishi | 456 | Beijing | 29 | 1 |
+----+----------+---------- +----------+-----+-----+
2 rows in set (0.00 sec)

Conditions that can be connected after where

Comparison operatorThe records that meet the conditions are listed in the result set. In the above example, the field after where is the ‘=’ of a field.

In addition, you can also use comparison operators such as >, <, >=, <=, !=;

## Symbol Description##><>=<=##!= is not equal to = is equal to Logical operators

is greater than
Less than
Greater than or equal to
Less than or equal to

You can also use or, and and other logical operators to perform multi-condition joint queries for multiple conditions

SymbolDescriptionororandand Let’s look at an example of multiple conditions:

TypeDetailsExampleselect * from money where id <10 andInstructionsQuerying all fields requires that the id is less than 10 and province='Hubei'mysql> select * from money where id <10 and province='Hubei'; +----+-----------+---------+----------+-----+----- +

| id | username | balance | province | age | sex |

+----+-----------+---------+----------+-----+----- +
| 1 | Wang Baoqiang | 120.02 | Hubei | 29 | 1 |
+----+-----------+---------+----------+-----+----- +
1 row in set (0.00 sec)


Result set sorting

CategoryDetailed explanationBasic syntaxselect field from table order by field sort keywordExample select id, username, balance from money order by balance desc;Example descriptionQuery the id, username, balance fields in the money table and sort them in descending order according to the balance

Keywords used in sorting:

Keywords	Description
asc	Arrange in ascending order, from small to large (default)
desc	Arrange in descending order, from large to small

Use order by to sort the result set after the select comes out, where desc and asc are keywords in the sort order. desc means to sort by fields in descending order, and asc means to sort in ascending order. If no keyword is written, the default is to sort in ascending order.

mysql> select id,username, balance from money order by balance desc;
+----+-----------+-------- -+
| id | username | balance |
+----+-----------+---------+
| 10 | Zhao Benshan | 3456 |
| 4 | Jing Bairan | 810 |
| 8 | Liu Shishi | 456 |
| 6 | Jackie Chan | 313 |
| 2 | Fan Bingbing | 260.23 |
| 12 | Guo Degang | 212 |
| 3 | Huang Xiaoming | 150.86 |
| 7 | Yang Mi | 123 |
| 1 | Wang Baoqiang | 120.02 |
| 11 | Wang Feng | Liu Yan | 23.4 |
| 5 | Li Bingbing | 20.15 |
+----+-----------+---------+
12 rows in set (0.00 sec)

Multi-field sorting

order by can be followed by multiple different field sorting, and the order of different result sets of the sorting field is also different, if the values ​​of the sorting fields are the same , then fields with the same value are sorted according to the second sorting field.

CategoryDetailed explanationBasic syntaxselect field from table order by field 1 sort keyword,... ...Field n desc|asc;Exampleselect id,username, balance from money order by balance desc,age asc;Example descriptionQuery the id, username, and balance fields in the money table, and sort them in descending order according to the balance. If the balance If they are all the same, then use age to sort in ascending order

* Note: If the first field has already arranged the results. The second field sort field does not take effect. In this case, the second field is invalid. *

mysql> select id,username, balance from money order by balance desc,age asc;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 10 | Zhao Benshan | 3456 |
| 4 | Jing Boran | 810 |
| 8 | Liu Shishi | 456 |
| 6 | Jackie Chan | 313 |
| 2 | Fan Bingbing | 260.23 |
| 12 | Guo Degang | 212 |
| 3 | Huang Xiaoming | 150.86 |
| 7 | Yang Mi | 123 |
| 1 | Wang Baoqiang | 120.02 |
| 11 | Wang Feng | 34.32 |
| 9 | Liu Yan | 23.4 |
| 5 | Li Bingbing | 20.15 |
+----+-----------+---------+
12 rows in set (0.00 sec)

Result set limit

For queries or sorted result sets, if you want to display only part instead of all, use the limit keyword result set Quantitative restrictions.

## Example descriptionDisplay the first five users

mysql> select * from money limit 5;
+----+----------------+---------+------- ----+-----+-----+
| id | username | balance | province | age | sex |
+----+-------- ---+---------+-----------+-----+-----+
| 1 | Wang Baoqiang | 120.02 | Hubei | 29 | 1 |
| 2 | Fan Bingbing | 260.23 | Shandong | 40 | 0 |
| 3 | Huang Xiaoming | 150.86 | Shandong | 40 | 1 |
| 4 | Jing Boran | 810 | Liaoning | 27 | 1 |
| 5 | Li Bingbing | 20.15 | Heilongjiang | 43 | 0 |
+----+----------+----------+- ----------+-----+-----+
5 rows in set (0.00 sec)

Limit and sort the result set

Category	Detailed explanation
Basic syntax	select field from table limit quantity;
Example	select id,username, balance from money limit 5;

##Basic syntaxselect field from table order by field keyword limit quantityExampleselect id,username, balance from money order by balance desc limit 5;Example descriptionSort by money, display the top five richest users##mysql> select id,username, balance from money order by balance desc limit 5;

Category	Detailed explanation

+----+-----------+---------+

| id | username | balance |
+----+-----------+---------+
| 10 | Zhao Benshan | 3456 |
| 4 | Jing Boran | 810 |
| 8 | Liu Shishi | 456 |
| 6 | Jackie Chan | 313 |
| 2 | Fan Bingbing | 260.23 |
+----+----------+ ---------+
5 rows in set (0.00 sec)

Result set interval selection

Suppose I fetch 3 records starting from 0. I want to fetch 3 more records starting from the 3rd one. What should I do if I want to fetch 4 records starting from the 6th one?

At this time, you need to use the result set interval selection.

CategoryBasic syntaxExampleExample description

Note: The first record is 0.

mysql> select id,username, balance from money limit 0,3;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 1 | Wang Baoqiang | 120.02 |
| 2 | Fan Bingbing | 260.23 |
| 3 | Huang Xiaoming | 150.86 |
+----+-----------+---------+
3 rows in set (0.00 sec)

How about taking three more rows starting from the third row?

mysql> select id,username, balance from money limit 3,3;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 4 | Jing Boran | 810 |
| 5 | Li Bingbing | 20.15 |
| 6 | Jackie Chan | 313 |
+----+-----------+---------+
3 rows in set (0.00 sec)

Through the above idea, the display completes paging.
Each page displays 10 records, then:

The first page is limit 0,10
The second page is limit 10,10
The third page is limit 20,10

And so on... ...

Use of statistical functions

1. What if we want to know the total number of users?
2. How to query who is the richest person in the data table?
3. What if we want to know the average amount of money for a user?
4. What if we want to know the total amount for all users?

We have four most commonly used statistical functions:

Detailed explanation
select field from table limit offset, quantity
select id,username, balance from money limit 0,3;
Get three records starting from the first one

##avgAverage

Function	Description
sum	Sum
count	Total statistics
max	Maximum value
min	Minimum value

Note: Of course you know that other mysql functions can also be used. However, in actual work, it is rarely used in many large and medium-sized projects in large companies, and they all have dedicated counting servers. Because the calculation amount of MySQL itself is very large, in order to reduce the pressure, we usually leave the actual calculation tasks to the business server or other servers to complete.

CategoryDetailed explanationBasic syntaxselect function (field) from tableExampleselect count(id) from moneyExample DescriptionQuery the total number of ids in the money table

mysql> select count(id) from money;
+-----------+
| count(id) |
+-----------+
| 12 |
+-----------+
1 row in set (0.00 sec)

You can also give the field an alias! Use the as keyword.

mysql> select count(id) as zongshu from money;
+---------+
| zongshu |
+---------+
| 12 |
+---------+
1 row in set (0.00 sec)

Query average amount

mysql> select avg(balance) from money;
+--------------------+
| avg(balance) |
+--------------------+
| 498.24833393096924 |
+--------------------+
1 row in set (0.00 sec)

Query total amount

mysql> select sum(balance) from money;
+-------------------+
| sum(balance) |
+-------------------+
| 5978.980007171631 |
+-------------------+
1 row in set (0.00 sec)

Query the maximum amount

mysql> select max(balance) from money;
+-------------+
| max(balance) |
+-------------+
| 3456 |
+-------------+
1 row in set (0.00 sec)

Query the minimum amount

mysql> select min(balance) from money;
+--------------------+
| min(balance) |
+--------------------+
| 20.149999618530273 |
+--------------------+
1 row in set (0.00 sec)

Group group by

We use the provinces in the amount table to group the data. You will find after grouping the data. The same provinces will be removed. That is, a province is a group.

Category	Detailed explanation
Basic syntax	select * from table group by field
Example	select * from money group by province;
Example description	Group by region

mysql> select * from money group by province;
+----+-----------+---------+------ -----+-----+-----+
| id | username | balance | province | age | sex |
+----+------- ----+---------+-----------+-----+-----+
| 7 | Yang Mi | 123 | Beijing | 30 | 0 |
| 12 | Guo Degang | 212 | Tianjin | 43 | 1 |
| 2 | Fan Bingbing | 260.23 | Shandong | 40 | 0 |
| 1 | Wang Baoqiang | 120.02 | Hubei | 29 | 1 |
| 9 | Liu Yan | 23.4 | Hunan | 36 | 0 |
| 4 | Jing Boran | 810 | Liaoning | 27 | 1 |
| 5 | Li Bingbing | 20.15 | Black Dragon Jiang | 43 | 0 |
+----+-----------+----------+-----------+-----+ -----+

Statistical grouping (category) total number:

mysql> select deptno, count(1) from emp group by deptno;
+----- ---+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 1 1 |
| 2 | 5 |
| 3 | 1 1 |
| 5 | 4 |
+--------+----- -----+
4 rows in set (0.04 sec)

Count the number of provinces and then display them in groups

mysql> select count(province),province from money group by province;
+------------------+----------+
| count(province) | province |
+------------------+----------+
| 3 | Beijing |
| | | Tianjin |
| 3 | Shandong |
| 1 1 Hubei |
| 1 1 | Hunan |
| 2 | Liaoning |
| Heilongjiang |
+------------------+----------+
7 rows in set (0.00 sec)

Statistics based on grouping

with rollup is rarely used. This knowledge point is set to the understanding level.

Its main function is to count the grouped data and then perform a total count.

##Basic syntaxselect * from table group by field with rollup##ExampleExample descriptionOn the basis of the above, count the total number. In the result of the following example, there is one more at the end. 12 NULL.

Category	Detailed explanation
select count(province),province from money group by province with rollup;
Count the number of groups again

mysql> select count(province),province from money group by province with rollup;

+------------------+----------+

| count(province) | province |
+------------------+----------+
| 3 | Beijing |
| | | Tianjin |
| 3 | Shandong |
| 1 1 Hubei |
| 1 1 | Hunan |
| 2 | Liaoning |
| Heilongjiang |
| 12 | NULL |
+------------------+----------+
8 rows in set (0.00 sec)

The results are then filtered having

The having clause is similar to where but also different. They are both statements that set conditions.

having is the filtering group and where is the filtering record.

CategoryBasic syntaxExampleExample description

mysql> select count(province) as result ,province from money group by having province result >2;
+--------+----------+
| result | province |
+--------+----------+
| 3 | Beijing |
| 3 | Shandong |
+--------+----------+
2 rows in set (0.00 sec)

Use SQL as a whole

We have only used certain statements in the above statements, and have not used them as a whole.

We will now integrate the statements and use them together once. The syntax structure used with the overall SQL statement is as follows:

SELECT
[Field 1 [as alias 1], [Function (Field 2),]...Field n]
FROM table name
[WHERE where condition]
[GROUP BY field]
[HAVING where_continition]
[order condition]
[limit condition]

Note: [] can be used to represent optional in the above statement.

The final syntax summary is as follows:

Detailed explanation
select * from table group by field having conditions
select count(province) as result,province from money group by province having result >2;
Group regions and count the total, and display the grouped regions greater than 2 in the grouping results

Keywords	Description
select	Selected columns
from	Table
where	Query conditions
group by	Group attribute having group filter conditions
order by	Sort attribute
limit	Starting record position, take the number of records

us Perform an overall use and query the money table fields: id, username, balance, province. It is required that id>1 and the balance be greater than 50. Use regions for grouping. We use the user ID to perform descending order, and only 3 items are allowed to be displayed.

Finally write the SQL statement as follows, and the query results are as follows:

mysql> select id,username,balance,province from money where id > 1 and balance > 50 group by province order by id desc limit 3;
+----+-----------+---------+----------+
| id | username | balance | province |
+----+-----------+---------+----------+
| 12 | Guo Degang | 212 | Tianjin |
| 7 | Yang Mi | 123 | Beijing |
| 4 | Jing Boran | 810 | Liaoning |
+----+-----------+---------+----------+
3 rows in set (0.00 sec)