MySQL单表查询操作实例详解【语法、约束、分组、聚合、过滤、排序等】
程序员文章站
2022-06-07 23:09:53
本文实例讲述了mysql单表查询操作。分享给大家供大家参考，具体如下：
语法
一、单表查询的语法
select 字段1,字段2... from 表名...
本文实例讲述了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)