Query records
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:
balance refers to the balance
province refers to the province
Basic query
mysql> select * from money; +----+-----------+---------+-----------+-----+-----+ | id | username | balance | province | age | sex | +----+-----------+---------+-----------+-----+-----+ | 1 | 李文凯 | 120.02 | 湖北 | 29 | 1 | | 2 | 范冰冰 | 260.23 | 山东 | 40 | 0 | | 3 | 黄晓明 | 150.86 | 山东 | 40 | 1 | | 4 | 井柏然 | 810 | 辽宁 | 27 | 1 | | 5 | 李冰冰 | 20.15 | 黑龙江 | 43 | 0 | | 6 | 成龙 | 313 | 山东 | 63 | 1 | | 7 | 杨幂 | 123 | 北京 | 30 | 0 | | 8 | 刘诗诗 | 456 | 北京 | 29 | 1 | | 9 | 柳岩 | 23.4 | 湖南 | 36 | 0 | | 10 | 赵本山 | 3456 | 辽宁 | 63 | 1 | | 11 | 汪峰 | 34.32 | 北京 | 44 | 1 | | 12 | 郭德纲 | 212 | 天津 | 43 | 1 | +----+-----------+---------+-----------+-----+-----+ 12 rows in set (0.00 sec)Specified field query
mysql> select id,username, balance from money; +----+-----------+---------+ | id | username | balance | +----+-----------+---------+ | 1 | 李文凯 | 120.02 | | 2 | 范冰冰 | 260.23 | | 3 | 黄晓明 | 150.86 | | 4 | 井柏然 | 810 | | 5 | 李冰冰 | 20.15 | | 6 | 成龙 | 313 | | 7 | 杨幂 | 123 | | 8 | 刘诗诗 | 456 | | 9 | 柳岩 | 23.4 | | 10 | 赵本山 | 3456 | | 11 | 汪峰 | 34.32 | | 12 | 郭德纲 | 212 | +----+-----------+---------+ 12 rows in set (0.00 sec)Query a single field for non-duplicate records distinct
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
mysql> select * from money where age = 29; +----+-----------+---------+----------+-----+-----+ | id | username | balance | province | age | sex | +----+-----------+---------+----------+-----+-----+ | 1 | 李文凯 | 120.02 | 湖北 | 29 | 1 | | 8 | 刘诗诗 | 456 | 北京 | 29 | 1 | +----+-----------+---------+----------+-----+-----+ 2 rows in set (0.00 sec)Conditions that can be followed by whereComparison operator The records that meet the conditions will be listed in the result set. In the above example, what is added after where is the ‘=’ of a field. In addition, you can also use comparison operators such as >, <, >=, <=, !=;
##Logical operatorsYou can also use or, and and other logical operators to perform multi-condition joint queries for multiple conditions
mysql> select * from money where id <10 and province='湖北'; +----+-----------+---------+----------+-----+-----+ | id | username | balance | province | age | sex | +----+-----------+---------+----------+-----+-----+ | 1 | 李文凯 | 120.02 | 湖北 | 29 | 1 | +----+-----------+---------+----------+-----+-----+ 1 row in set (0.00 sec)
Sort the result set
Use order by to sort the result set after the select comes out, where desc and asc are in the sorting order Keywords. 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 | 赵本山 | 3456 | | 4 | 井柏然 | 810 | | 8 | 刘诗诗 | 456 | | 6 | 成龙 | 313 | | 2 | 范冰冰 | 260.23 | | 12 | 郭德纲 | 212 | | 3 | 黄晓明 | 150.86 | | 7 | 杨幂 | 123 | | 1 | 李文凯 | 120.02 | | 11 | 汪峰 | 34.32 | | 9 | 柳岩 | 23.4 | | 5 | 李冰冰 | 20.15 | +----+-----------+---------+ 12 rows in set (0.00 sec)
Multi-field sorting order by can be followed by multiple different sorting fields, and the order of different result sets of the sorting field is also different. If the value of the sorting field is the same, the fields with the same value will be sorted according to the second sorting field. * 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. * Result set limit For queries or sorted result sets, if you want to display only part instead of all, use the limit key Limit on the number of word result sets. Limit the result set and sort Results Set interval selection Suppose I take 3 records starting from item 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. What about starting from the third one and taking three more? Through the above idea, the display completes paging. The first page is limit 0,10 And so on... ... Using statistical functions 1. What if we want to know the total number of users? 2. How to check who is the richest person in the data table? 3. What if we want to know the average amount of money spent by users? 4. What if we want to know the total amount of all users? We have four most commonly used statistical functions: 
mysql> select id,username, balance from money order by balance desc,age asc;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 10 | 赵本山 | 3456 |
| 4 | 井柏然 | 810 |
| 8 | 刘诗诗 | 456 |
| 6 | 成龙 | 313 |
| 2 | 范冰冰 | 260.23 |
| 12 | 郭德纲 | 212 |
| 3 | 黄晓明 | 150.86 |
| 7 | 杨幂 | 123 |
| 1 | 李文凯 | 120.02 |
| 11 | 汪峰 | 34.32 |
| 9 | 柳岩 | 23.4 |
| 5 | 李冰冰 | 20.15 |
+----+-----------+---------+
12 rows in set (0.00 sec)
mysql> select * from money limit 5;
+----+-----------+---------+-----------+-----+-----+
| id | username | balance | province | age | sex |
+----+-----------+---------+-----------+-----+-----+
| 1 | 李文凯 | 120.02 | 湖北 | 29 | 1 |
| 2 | 范冰冰 | 260.23 | 山东 | 40 | 0 |
| 3 | 黄晓明 | 150.86 | 山东 | 40 | 1 |
| 4 | 井柏然 | 810 | 辽宁 | 27 | 1 |
| 5 | 李冰冰 | 20.15 | 黑龙江 | 43 | 0 |
+----+-----------+---------+-----------+-----+-----+
5 rows in set (0.00 sec)
mysql> select id,username, balance from money order by balance desc limit 5;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 10 | 赵本山 | 3456 |
| 4 | 井柏然 | 810 |
| 8 | 刘诗诗 | 456 |
| 6 | 成龙 | 313 |
| 2 | 范冰冰 | 260.23 |
+----+-----------+---------+
5 rows in set (0.00 sec)
mysql> select id,username, balance from money limit 0,3;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 1 | 李文凯 | 120.02 |
| 2 | 范冰冰 | 260.23 |
| 3 | 黄晓明 | 150.86 |
+----+-----------+---------+
3 rows in set (0.00 sec)
mysql> select id,username, balance from money limit 3,3;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 4 | 井柏然 | 810 |
| 5 | 李冰冰 | 20.15 |
| 6 | 成龙 | 313 |
+----+-----------+---------+
3 rows in set (0.00 sec)
Each page displays 10 records, then:
The second page is limit 10,10
The third page is limit 20,10
##mysql> select count(id) from money;
+-----------+
| count(id) |
+-----------+
| 12 |
+-----------+
1 row in set (0.00 sec)
mysql> select count(id) as zongshu from money; +---------+ | zongshu | +---------+ | 12 | +---------+ 1 row in set (0.00 sec)Query the average amount
mysql> select avg(balance) from money; +--------------------+ | avg(balance) | +--------------------+ | 498.24833393096924 | +--------------------+ 1 row in set (0.00 sec)Query the 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 by
We group the data by province in the amount table. You will find out after grouping the data. The same provinces will be removed. That is, a province is a group.
mysql> select * from money group by province; +----+-----------+---------+-----------+-----+-----+ | id | username | balance | province | age | sex | +----+-----------+---------+-----------+-----+-----+ | 7 | 杨幂 | 123 | 北京 | 30 | 0 | | 12 | 郭德纲 | 212 | 天津 | 43 | 1 | | 2 | 范冰冰 | 260.23 | 山东 | 40 | 0 | | 1 | 李文凯 | 120.02 | 湖北 | 29 | 1 | | 9 | 柳岩 | 23.4 | 湖南 | 36 | 0 | | 4 | 井柏然 | 810 | 辽宁 | 27 | 1 | | 5 | 李冰冰 | 20.15 | 黑龙江 | 43 | 0 | +----+-----------+---------+-----------+-----+-----+
Statistical grouping (classification) total number:
mysql> select deptno, count(1) from emp group by deptno; +--------+----------+ | deptno | count(1) | +--------+----------+ | 1 | 1 | | 2 | 5 | | 3 | 1 | | 5 | 4 | +--------+----------+ 4 rows in set (0.04 sec)
After counting the number of provinces, group display
mysql> select count(province),province from money group by province; +-----------------+-----------+ | count(province) | province | +-----------------+-----------+ | 3 | 北京 | | 1 | 天津 | | 3 | 山东 | | 1 | 湖北 | | 1 | 湖南 | | 2 | 辽宁 | | 1 | 黑龙江 | +-----------------+-----------+ 7 rows in set (0.00 sec)
On the basis of grouping Statistics
with rollup are 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.
Based on the above statistics, the total number is calculated. In the result of the following example, there is an extra 12 NULL at the end.
mysql> select count(province),province from money group by province with rollup; +-----------------+-----------+ | count(province) | province | +-----------------+-----------+ | 3 | 北京 | | 1 | 天津 | | 3 | 山东 | | 1 | 湖北 | | 1 | 湖南 | | 2 | 辽宁 | | 1 | 黑龙江 | | 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.
mysql> select count(province) as result ,province from money group by province having result >2; +--------+----------+ | result | province | +--------+----------+ | 3 | 北京 | | 3 | 山东 | +--------+----------+ 2 rows in set (0.00 sec)
Using SQL as a whole
We have used certain statements individually in the above statements, not 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: The above [] can be used in the statement to represent optional.
The final syntax is summarized as follows:
We perform an overall combination and query the money table fields: id, username, balance, province requires that the balance of id>1 is greater than 50, and the region is used for grouping. We use the user ID for 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 | 郭德纲 | 212 | 天津 | | 7 | 杨幂 | 123 | 北京 | | 4 | 井柏然 | 810 | 辽宁 | +----+-----------+---------+----------+ 3 rows in set (0.00 sec)
