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 by
mysql> 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)