MySQL SELECT Sub-query

First create a table

 mysql> create table total (id int auto_increment PRIMARY key,name char, stu_num int not null,teacher_num int not NULL);  Query OK, 0 rows affected (0.11 sec) mysql> insert into Total (name,stu_num,teacher_num) v Alues ("Primary school 1", 400,20), ("Primary School 2", 356,24), ("Primary School 3", 403,19), ("Primary School 4", 367,26), ("Primary School 5", 373,20), ("Primary School 6", 406,21);  Query OK, 6 rows Affected (0.00 sec) records:6 duplicates:0 warnings:0mysql> select * from Total;  +----+---------+---------+-------------+| ID | name | Stu_num |  Teacher_num |+----+---------+---------+-------------+| 1 |     Primary 1 |          400 |  20 | | 2 |     Primary 2 |          356 |  24 | | 3 |     Primary 3 |          403 |  19 | | 4 |     Primary 4 |          367 |  26 | | 5 |     Primary 5 |          373 |  20 | | 6 |     Primary 6 |          406 | |+----+---------+---------+-------------+6 rows in Set (0.00 sec)  

Next, take this table as an example to introduce the deep use of SELECT statements.

First, the query does not duplicate records

Statement: SELECT DISTINCT [Property 1, Property 2] from table name

Note: The distinct must be placed in the first position;

Distinct can only use the fields that need to be heavy, that is, if distinct takes name,stu_num two fields, but it is not possible to sort by the ID later, because only the name and Stu_name two fields can be manipulated;

Distinct when multiple fields are removed, it means that several fields are repeated at the same time to be filtered.

Example:

SELECT DISTINCT Name,teacher_num from total; +---------+-------------+| Name    | teacher_num |+---------+-------------+| Elementary School 1 |          20 | | Primary 2 |          24 | | Primary 3 |          19 | | Primary 4 |          26 | | Primary 5 |          20 | | Primary 6 |          SELECT DISTINCT Teacher_num from total; +-------------+| Teacher_num |+-------------+|          | |          | |          | |          | |          |+-------------+5 rows in Set (0.00 sec)

Second, sort

Syntax: SELECT * FROM table name [WHERE Condition] [order by property 1 [DESC/ASC], property 2 [DESC/ASC] ...];

Description: Desc Descending arrangement, ASC ascending;

An order by can have multiple parameters, and each sort parameter can have a different sort order;

If the value of the first sort field is the same, it is sorted by the second sort field;

If there is only one sort field, records with the same field values will be unordered.

Example:

Select Id,name from total where id<4 order by teacher_num Desc; +----+---------+| ID | Name    |+----+---------+|  2 | Primary 2 | |  1 | Primary 1 | |  3 | Primary 3 |+----+---------+3 rows in Set (0.00 sec)

Third, restrictions

Statement: Select ...    [Limit start offset, number of rows]; or select ... [Number of limit rows] offset offsets;

Description:. By default, the starting offset is 0

Example:

Mysql>SELECT * from Total order by teacher_num ASC;+----+---------+---------+-------------+| ID | name | Stu_num |  Teacher_num |+----+---------+---------+-------------+| 3 |     Primary 3 |          403 |  19 | | 1 |     Primary 1 |          400 |  20 | | 5 |     Primary 5 |          373 |  20 | | 6 |     Primary 6 |          406 |  21 | | 2 |     Primary 2 |          356 |  24 | | 4 |     Primary 4 |          367 | |+----+---------+---------+-------------+6 rows in Set (0.00 sec) mysql>SELECT * FROM total limit 4 offset 1;+----+---------+---------+-------------+| ID | name | Stu_num |  Teacher_num |+----+---------+---------+-------------+| 2 |     Primary 2 |          356 |  24 | | 3 |     Primary 3 |          403 |  19 | | 4 |     Primary 4 |          367 |  26 | | 5 |     Primary 5 |          373 | |+----+---------+---------+-------------+4 rows in Set (0.00 sec)
Mysql>SELECT * from Total ORDER by teacher_num ASC limit 4 offset 1;+----+---------+---------+-------------+| ID | name | Stu_num |  Teacher_num |+----+---------+---------+-------------+| 1 |     Primary 1 |          400 |  20 | | 5 |     Primary 5 |          373 |  20 | | 6 |     Primary 6 |          406 |  21 | | 2 |     Primary 2 |          356 | |+----+---------+---------+-------------+4 rows in Set (0.00 sec)

Mysql> (SELECT * from all limit 4 offset 1) Order by teacher_num ASC;
+----+---------+---------+-------------+
| ID | name | Stu_num | Teacher_num |
+----+---------+---------+-------------+
| 3 | Primary 3 | 403 | 19 |
| 5 | Primary 5 | 373 | 20 |
| 2 | Primary 2 | 356 | 24 |
| 4 | Primary 4 | 367 | 26 |
+----+---------+---------+-------------+
4 rows in Set (0.00 sec)

Iv. Aggregation

Statement: Select field fun_name from table name [where condition] [Group BY property 1, Property 2 ...] [With Rollup] [Having conditions];

Description: Fun_name represents the aggregation operation to be done, that is, aggregation functions, commonly used are sum (sum), COUNT (*) (number of records), Max (maximum), min (minimum), etc.

Group by indicates the fields to be aggregated by category, such as the number of employees to be classified by department;

With rollup is an option that indicates whether the aggregated results of the classification are aggregated;

Having expressed the conditional filtering on the results of the classification.

Example:

 mysql> CREATE TABLE Staff--(id int auto_increment PRIMARY KEY, Xing Char (a), Ming char ((a), slary int not NULL); Query OK, 0 rows affected (0.07 sec) mysql> INSERT into staff (xing,ming,slary) value (' Guo ', ' ding ', 3200), (' Din G ', ' Tao ', 2800), (' Hao ', ' Fugui ', 3500),--(' Guo ', ' Ming ', 4000), (' Hao ', ' Tian ', 2900), (' Feng ', ' Fei ', ' 3200 '), (' Guo ', ' ting ', 2600); Query OK, 7 rows Affected (0.00 sec) records:7 duplicates:0 warnings:0mysql> SELECT * FROM staff;+----+------+----- --+-------+| ID | Xing | Ming |  Slary |+----+------+-------+-------+| 8 | Guo |  Ding |  3200 | | 9 | Ding |  Tao | 2800 | | 10 | Hao |  Fugui | 3500 | | 11 | Guo |  Ming | 4000 | | 12 | Hao |  Tian | 2900 | | 13 | Feng |  Fei | 3200 | | 14 | Guo |  Ting | 2600 |+----+------+-------+-------+7 rows in Set (0.00 sec)  

Select count (' Xing ') as xing_num from the staff where xing= ' Guo ';   The new syntax +----------+| is covered here. Xing_num |+----------+|        3 |+----------+1 row in Set (0.00 sec)

select sum (slary) from the staff; +------------+| SUM (slary) |+------------+|      Select Xing,sum (slary) from the staff group by Xing; +------+------------+| Xing | SUM (slary) |+------+------------+| Ding |       2800 | | Feng |       3200 | | Guo  |       9800 | | Hao  |       6400 |+------+------------+4 rows in Set (0.00 sec)

Mysql> Select Xing,sum (slary) from the staff group by Xing have sum (slary) >5000;+------+------------+| Xing | SUM (slary) |+------+------------+| Guo  |       9800 | | Hao  |       6400 |+------+------------+2 rows in Set (0.00 sec)

　　

Five, Cascade

Cascading is divided into inner and outer joins, in which the inner join selects only two records that match each other in the table, and the outer joins select other mismatched records.

Suppose you have the following staff table and post table:

Mysql>Select* fromStaff ;+----+----------+-------+| ID | name | Slary |+----+----------+-------+|1| guoding |3200||2| Dingtao |2800||3| Haofugui |3500||4| guoming |4000||5| Haotian |2900||6| Fengfei |3200||7| guoting |2600|+----+----------+-------+7Rowsinch Set(0.00sec) MySQL>Select* fromPost;+-----+----------+-------+| num | name | Level |+-----+----------+-------+|1| |6||2| Fengfei |4||3| Haotian |3||4| guoming |1||5| Haofugui |2||6| Dingtao |5||7| guoding |4|+-----+----------+-------+7Rowsinch Set(0.00Sec

example: (inner join), statement: Select property 1, Attribute 2 from table 1, table 2 where table 1. property value = Table 2. Property value

Select  from where staff.name=post.name; +----------+-------+-------+| Name     | level | slary |+----------+-------+-------+| Fengfei  4     |  3200 | | Haotian  3     |  2900 | | guoming  1     |  4000 2     |  3500 | | Dingtao  5     |  2800 | | Guoding  4     |  3200 |+----------+-------+-------+

The outer joins are also divided into left and right connections:

Left join: Contains all records from the table on the left, even those that do not match him in the right table.

Right connection: Contains all records in the right table, even those that do not match him in the right table.

Example: (left connection), statement: Select Property 1, Property 2 from Table 1 left JOIN table 2 on table 1. Attribute value = Table 2. Property value

Mysql>SelectPost.name,level,slary fromStaff left join post on Staff.name=Post.name;+----------+-------+-------+| name | Level | Slary |+----------+-------+-------+| guoding |4|3200|| Dingtao |5|2800|| Haofugui |2|3500|| guoming |1|4000|| Haotian |3|2900|| Fengfei |4|3200|| NULL | NULL |2600|+----------+-------+-------+7Rowsinch Set(0.00Sec

Example: (right connection), statement:Select Property 1, Property 2 from Table 1 Right Join table 2 on table 1. Attribute value = Table 2. Property value

Mysql>SelectPost.name,level,slary fromStaff right join post on Staff.name=Post.name;+----------+-------+-------+| name | Level |          Slary |+----------+-------+-------+| |6| NULL | | Fengfei |4|3200|| Haotian |3|2900|| guoming |1|4000|| Haofugui |2|3500|| Dingtao |5|2800|| guoding |4|3200|+----------+-------+-------+7Rowsinch Set(0.00Sec

MySQL SELECT subquery