MySQL单表查询操作实例详解【语法、约束、分组、聚合、过滤、排序等】

本文实例讲述了MySQL单表查询操作。分享给大家供大家参考，具体如下：

语法

一、单表查询的语法

   SELECT 字段1,字段2... FROM 表名
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

二、关键字的执行优先级（重点）

重点中的重点：关键字的执行优先级

from
where
group by
having
select
distinct
order by
limit

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数

（1）where 约束　

 where运算符

where子句中可以使用
1.比较运算符：>、<、>=、<=、<>、!=
2.between 80 and 100 ：值在80到100之间
3.in(80,90,100)值是10或20或30
4.like 'xiaomagepattern': pattern可以是%或者_。%小时任意多字符，_表示一个字符
5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

（2）group by 分组查询

#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的

#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

#3、为何要分组呢？
    取每个部门的最高工资
    取每个部门的员工数
    取男人数和女人数

小窍门：‘每'这个字后面的字段，就是我们分组的依据

#4、大前提：

可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数

当执行以下sql语句的时候，没有报错，但本身是没有意义的

?

1 2 3 4 5 6 7 8 9 10

mysql> select * from employee group by post; + ----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name  | sex  | age | hire_date | post                  | post_comment | salary   | office | depart_id | + ----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 14 | 张野  | male  | 28 | 2016-03-11 | operation                | NULL     |  10000.13 |  403 |     3 | | 9 | 歪歪  | 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 | 老男孩驻沙河办事处外交大使       | NULL     |  7300.33 |  401 |     1 | + ----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 4 rows in set (0.00 sec)

设置sql_mode为ONLY_FULL_GROUP_BY，并且退出，再进入才会生效

?

1 2	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)

再次进入

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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;//现在的情况下就会报错 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 BY mysql> select post from employee group by post; + -----------------------------------------+ | post                  | + -----------------------------------------+ | operation                | | sale                  | | teacher                 | | 老男孩驻沙河办事处外交大使       | + -----------------------------------------+ 4 rows in set (0.00 sec)

或者如下使用

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

mysql> select name ,post from employee group by post, name ; + ------------+-----------------------------------------+ | name    | post                  | + ------------+-----------------------------------------+ | 张野    | operation                | | 程咬金   | operation                | | 程咬铁   | operation                | | 程咬铜   | operation                | | 程咬银   | operation                | | 丁丁    | sale                  | | 丫丫    | sale                  | | 星星    | sale                  | | 格格    | sale                  | | 歪歪    | sale                  | | alex    | teacher                 | | jingliyang | teacher                 | | jinxin   | teacher                 | | liwenzhou | teacher                 | | wupeiqi  | teacher                 | | xiaomage  | teacher                 | | yuanhao  | teacher                 | | egon    | 老男孩驻沙河办事处外交大使       | + ------------+-----------------------------------------+ 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 | | 老男孩驻沙河办事处外交大使       |     1 | + -----------------------------------------+-----------+ 4 rows in set (0.00 sec)

（3）聚合函数

max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求总个数

?

1 2 3 4 5 6 7 8 9 10 11

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组 # 每个部门有多少个员工 select post, count (id) from employee group by post; # 每个部门的最高薪水 select post, max (salary) from employee group by post; # 每个部门的最低薪水 select post, min (salary) from employee group by post; # 每个部门的平均薪水 select post, avg (salary) from employee group by post; # 每个部门的所有薪水 select post, sum (age) from employee group by post;

（4）HAVING过滤

HAVING与WHERE不一样的地方在于

#！！！执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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 (0.00 sec)   mysql> select * from employee having salary>1000000; ERROR 1463 (42000): Non- grouping field 'salary' is used in HAVING clause   # 必须使用 group by 才能使用group_concat()函数，将所有的 name 值连接 mysql> select post,group_concat( name ) from emp group by post having salary > 10000; ##错误，分组后无法直接取到salary字段 ERROR 1054 (42S22): Unknown column 'post' in 'field list'

练习

1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
2. 查询各岗位平均薪资大于10000的岗位名、平均工资
3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

答案

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

mysql> select post,group_concat( name ), count (id) from employee group by post; + -----------------------------------------+-----------------------------------------------------------+-----------+ | post                  | group_concat( name )                    | count (id) | + -----------------------------------------+-----------------------------------------------------------+-----------+ | operation                | 程咬铁,程咬铜,程咬银,程咬金,张野             |     5 | | sale                  | 格格,星星,丁丁,丫丫,歪歪                 |     5 | | teacher                 | xiaomage,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |     7 | | 老男孩驻沙河办事处外交大使       | 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)<2; + -----------------------------------------+--------------------+-----------+ | post                  | group_concat( name ) | count (id) | + -----------------------------------------+--------------------+-----------+ | 老男孩驻沙河办事处外交大使       | egon        |     1 | + -----------------------------------------+--------------------+-----------+ 1 row in set (0.00 sec)

?

1 2 3 4 5 6 7 8 9

#题2： mysql> 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)

?

1 2 3 4 5 6 7 8

#题3： mysql> select post, avg (salary) from employee group by post having avg (salary) > 10000 and avg (salary) <20000; + -----------+--------------+ | post   | avg (salary) | + -----------+--------------+ | operation | 16800.026000 | + -----------+--------------+ 1 row in set (0.00 sec)

（5）order by 查询排序

按单列排序

?

1 2 3

SELECT * FROM employee ORDER BY age; SELECT * FROM employee ORDER BY age ASC ; SELECT * FROM employee ORDER BY age DESC ;

按多列排序:先按照age升序排序，如果年纪相同，则按照id降序

?

1 2 3

SELECT * from employee    ORDER BY age ASC ,    id DESC ;

（5）limit  限制查询的记录数:

示例：

?

1 2 3 4 5 6 7 8

SELECT * FROM employee ORDER BY salary DESC   LIMIT 3;          #默认初始位置为0   SELECT * FROM employee ORDER BY salary DESC    LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条   SELECT * FROM employee ORDER BY salary DESC    LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

 练习：每次显示5条

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

# 第1页数据   mysql> select * from employee limit 0,5; + ----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name   | sex | age | hire_date | post                  | post_comment | salary   | office | depart_id | + ----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon   | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使       | 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  | male | 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) # 第2页数据 mysql> select * from employee limit 5,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 |  401 |     1 | | 7 | jinxin   | male  | 18 | 1900-03-01 | teacher | NULL     | 30000.00 |  401 |     1 | | 8 | xiaomage  | male  | 48 | 2010-11-11 | teacher | NULL     | 10000.00 |  401 |     1 | | 9 | 歪歪    | female | 48 | 2015-03-11 | sale  | NULL     | 3000.13 |  402 |     2 | | 10 | 丫丫    | female | 38 | 2010-11-01 | sale  | NULL     | 2000.35 |  402 |     2 | + ----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec) # 第3页数据 mysql> select * from employee limit 10,5; + ----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name   | sex  | age | hire_date | post   | post_comment | salary  | office | depart_id | + ----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 11 | 丁丁   | female | 18 | 2011-03-12 | sale   | NULL     | 1000.37 |  402 |     2 | | 12 | 星星   | female | 18 | 2016-05-13 | sale   | NULL     | 3000.29 |  402 |     2 | | 13 | 格格   | female | 28 | 2017-01-27 | sale   | NULL     | 4000.33 |  402 |     2 | | 14 | 张野   | male  | 28 | 2016-03-11 | operation | NULL     | 10000.13 |  403 |     3 | | 15 | 程咬金  | male  | 18 | 1997-03-12 | operation | NULL     | 20000.00 |  403 |     3 | + ----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec)

希望本文所述对大家MySQL数据库计有所帮助。

原文链接：https://www.cnblogs.com/mmyy-blog/p/9626857.html