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语句的时候，没有报错，但本身是没有意义的

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，并且退出，再进入才会生效

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)

再次进入

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 BYmysql> select post from employee group by post;+-----------------------------------------+| post                  |+-----------------------------------------+| operation                || sale                  || teacher                 || 老男孩驻沙河办事处外交大使       |+-----------------------------------------+4 rows in set (0.00 sec)

或者如下使用

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() 求总个数

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组# 每个部门有多少个员工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中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

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的岗位名、平均工资

答案

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)

#题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)

#题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 查询排序

按单列排序

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

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

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

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

示例：

  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页数据 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相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

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