SQL form query
I. Grammar of Single Table Query
select List 1,List 2... from Table name
where condition
group by field
having screen
order by field
limit Limited number of bars2. Priority of keyword execution
from
where
group by
having
select
distinct
order by
limit
1. Find the table:
2.where specified constraints
3. Grouping the extracted records into groups by. If there is no group by, the whole group is a group.
4. having filter the results of grouping
5. Execute select
6.distinct de-weighting
7. Sort the results by condition: order by
8.limit limits the number of display bars
3. select statement
3.1 Query all columns
select * from;
3.2 Set aliases for columns
Aliases can be used in Chinese, which needs to be enclosed in double quotation marks ("). Please note that
Not single quotation marks (')
3.3 Constant Query
SELECT'Commodity'AS string, 38 AS number,'2009-02-24' AS date, product_id, product_name FROM Product; results of enforcement string | number | date | product_id | product_name ---------+-----------+--------------+-------------+-------------- Commodity | 38 | 2009-02-24 | 0001 | T-shirt Commodity | 38 | 2009-02-24 | 0002 | perforator Commodity | 38 | 2009-02-24 | 0003 | Sports T-shirt Commodity | 38 | 2009-02-24 | 0004 | kitchen knife Commodity | 38 | 2009-02-24 | 0005 | Pressure cooker Commodity | 38 | 2009-02-24 | 0006 | Fork Commodity | 38 | 2009-02-24 | 0007 | Cleaning the Vegetable Board Commodity | 38 | 2009-02-24 | 0008 | Ball Pen
As shown in the above execution results, constants in the SELECT clause are displayed in all rows.
3.4 Deweighting
1. Syntax: SELECT DISTINCT product_type FROM Product;
2. ** The DISTINCT keyword can only be used before the first column **
Error: register_date, DISTINCT product_type.
3. If multiple data are null, they will be merged into one.3.5 Where statement
The where clause should follow the from clause.
Firstly, a record that meets the specified criteria is queried through where clause, and then a select clause is selected to refer to
Fixed columns.
4. arithmetic, comparison and logic operations
1. Examples of SELECT product_name, sale_price, sale_price* 2 AS "sale_price_x2" FROM Product;
2. Including NULL calculations, the result is NULL
3. The unequal sign is <>
4. Data of string type should be sorted in dictionary order in principle, and can not be confused with the size order of numbers. For example,'10'and'11' are also strings beginning with'1', which are smaller than'2'.
5. You can't use comparison operators for NULL (=,<>,>,<,>,>,>=,<=) for example:... where purchase_price = NULL is not available for data query, you can use IS NULL, otherwise, when you want to select records that are not NULL, you need to use IS NOT NULL.
6. The priority of the AND operator is higher than that of the OR operator. Parentheses can be used when you want to execute the OR operator first.
SELECT product_name, product_type, regist_date FROM Product WHERE product_type = office supplies AND regist_date = '2009-09-11' OR regist_date = '2009-09-20'; results of enforcement product_name | product_type | regist_date ---------------+--------------+------------ T-shirt | Clothes | 2009-09-20 Perforator | Office Supplies | 2009-09-11 Kitchen knife | kitchen utensils | 2009-09-20 Fork | Kitchenware | 2009-09-20 SELECT product_name, product_type, regist_date FROM Product WHERE product_type = office supplies AND ( regist_date = '2009-09-11' OR regist_date = '2009-09-20'); product_name | product_type | regist_date ---------------+--------------+------------ Perforator | Office Supplies | 2009-09-11 In this way, we select the "punch" we want to get. rule
5. Common aggregation functions in SQL
sum() sum count () count max() maximum min() minimum avg() average value
3.1 ** The aggregation function excludes null. The exception to count(*) does not exclude null. * *
3.2 The results of count function vary according to the parameters. Count*) will get data containing NULL
The number of rows, and count() gets the number of data rows other than null.
3.3 Using distinct in the parameters of aggregation function, duplicate data can be deleted. For example sum(distinct column name)
group by statement
1. The columns specified in the group by clause are called aggregate keys or group columns.
2. When null is included in the aggregate key, the result will be expressed in the form of "uncertain" lines (blank lines).
3. When the where clause is executed, the results of group by are filtered according to the conditions specified in the where clause, and then aggregated.
Common errors in aggregation functions and GROUP BY clauses
Error 1: Write redundant columns in SELECT clauses (column names other than aggregation keys are written in SELECT clauses)
When using aggregation functions such as COUNT, the elements in the SELECT clause are strictly restricted. In fact, there are only three types of aggregation functions in the SELECT clause Elements. Constants Aggregation function The column name specified in the GROUP BY clause (that is, aggregation key) If you need columns other than aggregate keys, use group_concat (column name)
Error 2: Using aggregation functions in WHERE clauses
Notes
Single-line notes
Write on the same line after "--".
Multi-line notes
Written between "/" and "/", can span multiple lines.
8. Code Demonstration
create table class1_score(id int primary key auto_increment,name char(10),gender enum('male'，'female'),math int default 0,eng int default 0)
insert into class1_score(id,name,gender,math,eng) values
(1,'Zhao Yi','female',85,88),
(2,'Qian Er','female',85,90),
(3,'Sun San','male',90,90),
(4,'Li Si','male',80,85),
(5,'Zhou Wu','male',80,85),
(6,'Wu Liu','female',90,90),
(7,'Zheng Qi','male',70,75),
(8,'king','male',70,75),
(9,'Feng 9','male',95,85),
(10,'Chen Shi','male',60,60);
mysql> select * from class1_score;
+----+--------+--------+------+------+
| id | name | gender | math | eng |
+----+--------+--------+------+------+
| 1 | Zhao Yi | female | 85 | 88 |
| 2 | Qian Er | female | 85 | 90 |
| 3 | Sun San | male | 90 | 90 |
| 4 | Li Si | male | 80 | 85 |
| 5 | Zhou Wu | male | 80 | 85 |
| 6 | Wu Liu | female | 90 | 90 |
| 7 | Zheng Qi | male | 70 | 75 |
| 8 | king | male | 70 | 75 |
| 9 | Feng 9 | male | 95 | 85 |
| 10 | Chen Shi | male | 60 | 60 |
+----+--------+--------+------+------+
10 rows in set (0.00 sec)
mysql> select * from class1_score where gender='male';
+----+--------+--------+------+------+
| id | name | gender | math | eng |
+----+--------+--------+------+------+
| 3 | Sun San | male | 90 | 90 |
| 4 | Li Si | male | 80 | 85 |
| 5 | Zhou Wu | male | 80 | 85 |
| 7 | Zheng Qi | male | 70 | 75 |
| 8 | king | male | 70 | 75 |
| 9 | Feng 9 | male | 95 | 85 |
| 10 | Chen Shi | male | 60 | 60 |
+----+--------+--------+------+------+
7 rows in set (0.00 sec)
mysql> select name,math,eng from class1_score where math>89 and eng> 89;
+--------+------+------+
| name | math | eng |
+--------+------+------+
| Sun San | 90 | 90 |
| Wu Liu | 90 | 90 |
+--------+------+------+
2 rows in set (0.00 sec)
mysql> select * from class1_score where gender='male' having math>70;
+----+--------+--------+------+------+
| id | name | gender | math | eng |
+----+--------+--------+------+------+
| 3 | Sun San | male | 90 | 90 |
| 4 | Li Si | male | 80 | 85 |
| 5 | Zhou Wu | male | 80 | 85 |
| 9 | Feng 9 | male | 95 | 85 |
+----+--------+--------+------+------+
4 rows in set (0.00 sec)
#Sex-selective, grouped according to sex
mysql> select gender from class1_score group by gender;
+--------+
| gender |
+--------+
| male |
| female |
+--------+
2 rows in set (0.00 sec)
mysql> select name,math from class1_score where math between 80 and 90;
+--------+------+
| name | math |
+--------+------+
| Zhao Yi | 85 |
| Qian Er | 85 |
| Sun San | 90 |
| Li Si | 80 |
| Zhou Wu | 80 |
| Wu Liu | 90 |
+--------+------+
6 rows in set (0.00 sec)
#Wildcard% and_
#% The pronoun is not positioned and the pronoun is a character.
mysql> insert into class1_score values(11,'Zhao Yiyi','female',85,88),(12,'Zhao Twelve','female',85,88);
mysql> select * from class1_score;
+----+-----------+--------+------+------+
| id | name | gender | math | eng |
+----+-----------+--------+------+------+
| 1 | Zhao Yi | female | 85 | 88 |
| 2 | Qian Er | female | 85 | 90 |
| 3 | Sun San | male | 90 | 90 |
| 4 | Li Si | male | 80 | 85 |
| 5 | Zhou Wu | male | 80 | 85 |
| 6 | Wu Liu | female | 90 | 90 |
| 7 | Zheng Qi | male | 70 | 75 |
| 8 | king | male | 70 | 75 |
| 9 | Feng 9 | male | 95 | 85 |
| 10 | Chen Shi | male | 60 | 60 |
| 11 | Zhao Yiyi | female | 85 | 88 |
| 12 | Zhao Twelve | female | 85 | 88 |
| 13 | Zhao XIXIXIII | male | 82 | 88 |
| 14 | Zhao Shisi | female | 85 | 85 |
+----+-----------+--------+------+------+
14 rows in set (0.00 sec)
mysql> select * from class1_score where name like 'Zhao%';
+----+-----------+--------+------+------+
| id | name | gender | math | eng |
+----+-----------+--------+------+------+
| 1 | Zhao Yi | female | 85 | 88 |
| 11 | Zhao Yiyi | female | 85 | 88 |
| 12 | Zhao Twelve | female | 85 | 88 |
+----+-----------+--------+------+------+
mysql> select * from class1_score where name like '_thinking'; ,85,85)
+----+--------+--------+------+------+
| id | name | gender | math | eng |
+----+--------+--------+------+------+
| 4 | Li Si | male | 80 | 85 |
+----+--------+--------+------+------+
1 row in set (0.00 sec)
#Number of inquiries grouped by sex
mysql> select count(1),gender from class1_score group by gender;
# select count(1) as `Number of men/women', gender from class 1_score group by gender;
+----------+--------+
| count(1) | gender |
+----------+--------+
| 8 | male |
| 6 | female |
+----------+--------+
2 rows in set (0.00 sec)
#Query and calculate the total math scores of boys and girls
mysql> select sum(math),gender from class1_score group by gender;
+-----------+--------+
| sum(math) | gender |
+-----------+--------+
| 627 | male |
| 515 | female |
+-----------+--------+
2 rows in set (0.00 sec)
#Query the highest scores of boys and girls in English.
mysql> select max(eng),gender from class1_score group by gender;
+----------+--------+
| max(eng) | gender |
+----------+--------+
| 90 | male |
| 90 | female |
+----------+--------+
2 rows in set (0.00 sec)
#Finding the Mathematical Average Value of Male and Female Students
mysql> select avg(math),gender from class1_score group by gender;
+-----------+--------+
| avg(math) | gender |
+-----------+--------+
| 78.3750 | male |
| 85.8333 | female |
+-----------+--------+
2 rows in set (0.00 sec)
#When group by is combined with having, the field after having should be the same as group by.
#If you want to be segregated by sex and look at the name, the following error will occur, because only the gender field is segregated by sex.
mysql>select name,gender from class1_score group by gender;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db1.class1_score.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
# group_concat
mysql> select group_concat(name),gender from class1_score group by gender;
+---------------------------------------------------------+--------+
| group_concat(name) | gender |
+---------------------------------------------------------+--------+
| Sun San,Li Si,Zhou Wu,Zheng Qi,king,Feng 9,Chen Shi,Zhao XIXIXIII | male |
| Zhao Yi,Qian Er,Wu Liu,Zhao Yiyi,Zhao Twelve,Zhao Shisi | female |
+---------------------------------------------------------+--------+
2 rows in set (0.00 sec)
#Search for names and math scores and ascend by math scores
mysql> select name,math from class1_score order by math;
+-----------+------+
| name | math |
+-----------+------+
| Chen Shi | 60 |
| Zheng Qi | 70 |
| king | 70 |
| Li Si | 80 |
| Zhou Wu | 80 |
| Zhao XIXIXIII | 82 |
| Zhao Yi | 85 |
| Qian Er | 85 |
| Zhao Yiyi | 85 |
| Zhao Twelve | 85 |
| Zhao Shisi | 85 |
| Sun San | 90 |
| Wu Liu | 90 |
| Feng 9 | 95 |
+-----------+------+
14 rows in set (0.00 sec)
#asc in mathematics and desc in English
#When NULL is included in the sort key, it is aggregated at the beginning or at the end.
mysql> select name,math,eng from class1_score order by math asc,eng desc;
+-----------+------+------+
| name | math | eng |
+-----------+------+------+
| Chen Shi | 60 | 60 |
| Zheng Qi | 70 | 75 |
| king | 70 | 75 |
| Li Si | 80 | 85 |
| Zhou Wu | 80 | 85 |
| Zhao XIXIXIII | 82 | 88 |
| Qian Er | 85 | 90 |
| Zhao Yi | 85 | 88 |
| Zhao Yiyi | 85 | 88 |
| Zhao Twelve | 85 | 88 |
| Zhao Shisi | 85 | 85 |
| Sun San | 90 | 90 |
| Wu Liu | 90 | 90 |
| Feng 9 | 95 | 85 |
+-----------+------+------+
14 rows in set (0.00 sec)
#Mathematics Top Three (limit Starting Position, Get Number Can Be Used for Paging Index Similar, Starting from 0)
mysql> select name,math from class1_score order by math desc limit 0,3;
+--------+------+
| name | math |
+--------+------+
| Feng 9 | 95 |
| Sun San | 90 |
| Wu Liu | 90 |
+--------+------+
3 rows in set (0.00 sec)