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

In this paper, an example is given to describe the query operation of MySQL single table. Share with you for your reference, the details are as follows:

Grammar

1. Syntax of single table query

SELECT field 1, field 2... FROM table name

WHERE condition

GROUP BY field

HAVING screening

ORDER BY field

Limit the number of LIMIT entries

Second, the execution priority of keywords (key points)

The key in the focus: the execution priority of the keyword

From

Where

Group by

Having

Select

Distinct

Order by

Limit

1. Table found: from

two。 With the constraints specified by where, go to the file / table and take out each record.

3. The extracted records are grouped into group by. If there is no group by, the records are grouped as a group.

4. Filter the results of the grouping by having

5. Execute select

6. Deweighting

7. Sort the results by condition: order by

8. Limit the number of results displayed

(1) where constraint

Where operator

Can be used in the where clause

1. Comparison operators: >, =, select * from employee group by post +- -+-+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | + -+ | 14 | Zhangye | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | 9 | askew | female | 48 | 2015-03-11 | sale | | NULL | 3000.13 | 402 | 2 | 2 | alex | male | 78 | 2015-03-02 | teacher | 1000000.31 | 401 | 1 | 1 | egon | male | 18 | 2017-03-01 | Old Boy's diplomatic Ambassador in Shahe Office | NULL | 7300.33 | 401 | 1 | +-+-- -+-+ 4 rows in set (0.00 sec)

Set sql_mode to ONLY_FULL_GROUP_BY, exit, and then enter will take effect

Mysql > set global sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';Query OK, 0 rows affected (0.00 sec)

Enter again

Mysql > select @ @ sql_mode +-+ | @ @ sql_mode | +-- -+ | ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-mysql > select * from emp group by post / / now you will get an error ERROR 1054 (42S22): Unknown column 'post' in' group statement'mysql > select * from employee group by post;ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BYmysql > select post from employee group by post +-- + | post | +-- + | operation | | sale | | teacher | Ambassador of the Old Boy's Office in Shahe | +-+ 4 rows in set (0.00 sec) |

Or use the following

Mysql > select name,post from employee group by post,name +-+-+ | name | post | +-+- -+ | Zhang Ye | operation | | Cheng Yaojin | operation | | Cheng Yaotie | operation | | Chengyao Copper | operation | | Chengyaoyin | operation | | Tintin | sale | | Yaya | sale | Star | sale | | GE | Grid | sale | | crooked | sale | | alex | teacher | | jingliyang | teacher | | jinxin | teacher | | liwenzhou | teacher | | wupeiqi | teacher | | xiaomage | teacher | | yuanhao | teacher | | egon | Old Man | Ambassador of Children's Office in Shahe | +-+-+ 18 rows in set (0.00 sec) mysql > select post Count (id) from employee group by post +-- +-+ | post | count (id) | +-- -+ | operation | 5 | sale | 5 | teacher | 7 | | Ambassador of the Old Boy's Office in Shahe | 1 | +-+-+ 4 rows in set (0.00 sec)

(3) aggregate function

Max () to find the maximum

Min () to find the minimum

Avg () to find the average

Sum () summation

Count () to find the total number

# emphasize: the aggregate function aggregates the content of the group. If there is no grouping, the default group is # how many employees are there in each department select post,count (id) from employee group by post;# the highest salary in each department select post,max (salary) from employee group by post;# the minimum salary in each department select post,min (salary) from employee group by post;# the average salary select post,avg (salary) from employee group by post in each department # all salaries in each department select post,sum (age) from employee group by post

(4) HAVING filtering

The difference between HAVING and WHERE is that

#! Execution priority from high to low: where > group by > having

# 1. Where occurs before grouping group by, so there can be any fields in Where, but aggregate functions must not be used.

# 2. Having occurs after grouping group by, so grouped fields can be used in Having, and other fields cannot be fetched directly. Aggregate functions can be used.

Mysql > select * from employee where salary > 1000000 +-+ | id | name | sex | age | hire_date | post | post_comment | salary | office | | depart_id | +-- + | 2 | alex | male | 78 | 2015-03-02 | teacher | | | 1000000.31 | 401 | 1 | +-+ 1 row in set (sec) mysql > select * from employee having salary > 1000000 | ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause# must use group by to use the group_concat () function to connect all name values to mysql > select post,group_concat (name) from emp group by post having salary > 10000; # # error: unable to directly fetch the salary field ERROR 1054 (42S22): Unknown column' post' in 'field list' after grouping

Practice

1. Inquire the name of the post with less than 2 employees in each post, and the name and number of employees in the post.

two。 Inquire about the position name and average salary of each post whose average salary is more than 10000.

3. Inquire about the job name and average salary of each post whose average salary is more than 10000 and less than 20000.

Answer

Mysql > select post,group_concat (name), count (id) from employee group by post +-+-- +-+ | post | | group_concat (name) | count (id) | +-- -+-+ | operation | Cheng Yaotie Cheng bites Copper, Cheng bites Silver, Cheng Yaojin, Zhang Ye | 5 | | sale | GE, Xing, Tintin, Yaya, askew | 5 | | teacher | xiaomage,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi Alex | 7 | | diplomatic Ambassador of the Old Boy's Office in Shahe | egon | 1 | + -+-+ 4 rows in set (0.00 sec) mysql > select post Group_concat (name), count (id) from employee group by post having count (id) select post,avg (salary) from employee group by post having avg (salary) > 10000 +-+-+ | post | avg (salary) | +-+-+ | operation | 16800.026000 | teacher | 151842.901429 | +-+-+ 2 rows in set (0.00 sec) # questions 3:mysql > select post Avg (salary) from employee group by post having avg (salary) > 10000 and avg (salary) select * from employee limit 0min5 +- -- +-+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | + -+ | 1 | egon | male | 18 | 2017-03-01 | diplomatic Ambassador of the Old Boy in Shahe Office | NULL | 7300.33 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | 4 | yuanhao | yuanhao | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | 5 | liwenzhou | male | 28 | 2012-11-01 | Teacher | NULL | 2100.00 | 401 | 1 | +-+- -+ 5 rows in set (0.00 sec) # Page 2 data mysql > select * from employee limit 5 +-+ | id | name | sex | age | hire_date | post | post _ comment | salary | office | depart_id | +-+ | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 9000.00 | 1 | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | 8 | xiaomage | 48 | 2010-11 | teacher | NULL | 10000.00 | 401 | 1 | 9 | crooked | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | 10 | 10 | Yaya | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 2000.35 | 2 | +-+-- -+-+ 5 rows in set (0.00 sec) # Page 3 data mysql > select * from employee limit 10Pol 5 +-+ | id | name | sex | age | hire_date | post | | post_comment | salary | office | depart_id | +-+ | 11 | D | D | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | 12 | Star | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 3000.29 | 2 | 13 | Grid | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | 14 | Zhangye | male | 28 | 2016-03-11 | operation | NULL | 10000. 13 | 403 | 3 | 15 | Cheng Yaojin | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | + -+ 5 rows in set (0.00 sec)

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

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.