05-分组汇总查询

合集 - mysql(11)

1.02-数据库和表的基本操作2023-07-302.03-简单查询2023-07-303.04-条件查询2023-07-30

4.05-分组汇总查询2023-07-30

5.06-连接查询和子查询2023-07-306.07-数据定义2023-07-307.08-存储引擎和事务2023-07-308.09-索引和视图2023-07-309.10-DBA命令和数据库设计的三范式2023-07-3010.11-MySQL数据库的存储过程2023-07-3011.12-MySQL数据库的触发器2023-07-30

收起

05-分组汇总查询

课程目标

掌握查询中的排序、处理函数。

5.1单一字段排序数据

排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面。

1.按照薪水由小到大排序(系统默认由小到大) ，如下图5-1所示：

select * from emp order by sal;

图5-1

2.取得job为MANAGER的员工，按照薪水由小到大排序(系统默认由小到大) ，如下图5-2所示：

select * from emp where job='MANAGER' order by sal;

图5-2

如果包含where语句order by必须放到where后面，如果没有where语句order by放到表的后面

以下写法是错误的：

select * from emp order by sal where job='MANAGER';

3.按照多个字段排序，如：首先按照job排序，再按照sal排序，如下图5-3所示：

select * from emp order by job,sal;

图5-3

5.2 手动指定排序顺序

1.手动指定按照薪水由小到大排序，如下图5-4所示：

select * from emp order by sal asc;

图5-4

2.手动指定按照薪水由大到小排序，如下图5-5所示：

select * from emp order by sal desc;

图5-5

5.3 多个字段排序

按照job和薪水倒序，如下图5-6所示：

select * from emp order by job desc, sal desc;

图5-6

如果采用多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序

5.4 使用字段的位置来排序

按照薪水升序，如下图5-7所示：

select * from emp order by 6;

图5-7

不建议使用此种方式，采用数字含义不明确，程序不健壮

5.5 数据处理函数/单行处理函数

1. 数据处理函数/单行处理函数，详见下表5-1

表5－1

lower	转换小写
upper	转换大写
substr	取子串（substr(被截取的字符串,起始下标,截取的长度)）
length	取长度
trim	去空格
str_to_date	将字符串转换成日期
date_format	格式化日期
format	设置千分位
round	四舍五入
rand()	生成随机数
ifnull	可以将null转换成一个具体值

2. lower

查询员工，将员工姓名全部转换成小写，如下图5-8所示：

select lower(ename) from emp;

图5-8

3.upper

查询job为manager的员工，如下图5-9所示：

select * from emp where job=upper('manager');

图5-9

4.substr

查询姓名以M开头所有的员工，如下图5-10所示：

select * from emp where substr(ename, 1, 1)=upper('m');

图5-10

5.trim

trim会去首尾空格，不会去除中间的空格

取得工作岗位为manager的所有员工，如下图5-11所示：

select * from emp where job=trim(upper(' manager '));

图5-11

6.str_to_date

查询1981-02-20入职的员工（第一种方法，与数据库的格式匹配上），如下图5-12所示：

select * from emp where HIREDATE='1981-02-20';

图5-12

查询1981-02-20入职的员工（第二种方法，将字符串转换成date类型），如下图5-13所示：

select * from emp where HIREDATE=str_to_date('1981-02-20','%Y-%m-%d');

select * from emp where HIREDATE=str_to_date('02-20-1981','%m-%d-%Y');

图5-13

str_to_date可以将字符串转换成日期，具体格式str_to_date (字符串，匹配格式)

7.date_format

查询1981-02-20以后入职的员工，将入职日期格式化成yyyy-mm-dd hh:mm:ss，如下图5-14所示：

select empno, ename, date_format(hiredate, '%Y-%m-%d %H:%i:%s') as hiredate from emp;

图5-14

select date_format(now(),'%Y-%m-%d %H %i %s');

now() 获得当前时间

日期格式的说明

%Y：代表4位的年份

%y：代表2位的年份

%m：代表月, 格式为(01……12)

%c：代表月, 格式为(1……12)

%H：代表小时,格式为(00……23)

%h： 代表小时,格式为(01……12)

%i： 代表分钟, 格式为(00……59)

%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)

%T：代表 时间,格式为24 小时(hh:mm:ss)

%S：代表 秒,格式为(00……59)

%s：代表 秒,格式为(00……59)

8. format

查询员工薪水加入千分位，如下图5-15所示：

select empno, ename, format(sal,'$999,999') from emp;

图5-15

查询员工薪水加入千分位和保留两位小数，如下图5-16所示：

select empno, ename, Format(sal, 2) from emp;

图5-16

9.round

四舍五入，如下图5-17所示：

select round(123.56);

图5-17

10.rand

生成随机数，如下图5-18所示：

select rand();

图5-18

随机抽取记录数

select * from emp order by rand() limit 2;

order by 必须写上。

11. case … when … then …..else …end

如果job为MANAGER的薪水上涨10%,如果job为SALESMAN工资上涨50%，如下图5-19所示：

select empno, ename, job, sal, case job when 'MANAGER' then

sal*1.1 when 'SALESMAN' then sal*1.5 end as newsal from emp;

图5-19

其他的工资不动，需要添加else，如下图5-20所示：

select e.*,sal ,case job when 'salesman' then sal*1.1 when 'clerk' then sal*1.2 else sal end as new_sal from emp e;

e.*:取emp表所有的字段 , emp as e 是表的别名可以省略as emp e

图5-20

12.ifnull

，如下图5-21所示：

select ifnull(comm,0) from emp;

如果comm为null 就替换为 0

在SQL如果一定是NULL

为了防止计算结果出现NULL，建议先使用ifnull空值处理函数预先处理。句子当中若有NULL值参与数学运算，计算结果如下：

以下SQL是计算年薪的：

select empno,ename,sal,(sal+ifnull(comm,0))*12 as yearsal from emp;

图5-21

数据处理函数又被称为单行处理函数，特点：输入一行输出一行

5.6 分组函数/聚合函数/多行处理函数

如下表5-2所示：

表5-2

count	取得记录数
sum	求和
avg	取平均
max	取最大的数
min	取最小的数

注意：分组函数自动忽略空值，不需要手动的加where条件排除空值。

select count(*) from emp where xxx; 符合条件的所有记录总数。

select count(comm) from emp; comm这个字段中不为空的元素总数。

注意：分组函数不能直接使用在where关键字后面。

MySQL> select ename,sal from emp where sal > avg(sal);

ERROR 1111 (HY000): Invalid use of group function

1.count

取得所有的员工数，如下图5-22所示：

select count(*) from emp;

图5-22

count(*)表示取得所有记录，忽略null，为null的值不会取得

取得津贴不为null员工数，如下图5-23所示：

select count(comm) from emp;

图5-23

采用count(字段名称)，不会取得为null的记录

取得工作岗位的个数，如下图5-24所示：

select count(distinct job) from emp;

图5-24

2.sum

sum可以取得某一个列的和，null会被忽略

取得薪水的合计，如下图5-25所示：

select sum(sal) from emp;

图5-25

取得津贴的合计，如下图5-26所示：

select sum(comm) from emp;

图5-26

null会被忽略

取得薪水的合计（sal+comm），如下图5-27所示：

select sum(sal+comm) from emp;

图5-27

从以上结果来看，不正确，原因在于comm字段有null值，所以无法计算，sum会忽略掉，正确的做法是将comm字段转换成0，如下图5-28所示：

select sum(sal+IFNULL(comm, 0)) from emp;

图5-28

3.avg

取得某一列的平均值

取得平均薪水，如下图5-29所示：

select avg(sal) from emp;

图5-29

4.max

取得某个一列的最大值

取得最高薪水，如下图5-30所示：

select max(sal) from emp;

图5-30

取得最晚入职的员工，如下图5-31所示：

select max(str_to_date (hiredate, '%Y-%m-%d')) from emp;

图5-31

5.min

取得某个一列的最小值

取得最低薪水，如下图5-32所示：

select min(sal) from emp;

图5-32

取得最早入职的员工（可以不使用str_to_date转换），如下图5-33所示：

select min(str_to_date(hiredate, '%Y-%m-%d')) from emp;

图5-33

5.7 组合聚合函数

可以将这些聚合函数都放到select中一起使用，如下图5-34所示：

select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

图5-34

5.8 分组汇总查询

1.group by

取得每个工作岗位的工资合计，要求显示岗位名称和工资合计，如下图5-35所示：

select job, sum(sal) from emp group by job;

图5-35

如果使用了order by，order by必须放到group by后面，如下图5-36所示：

图5-36

按照工作岗位和部门编码分组，取得的工资合计

原始数据，如下图5-37所示：

图5-37

分组语句，如下图5-38所示：

select job,deptno,sum(sal) from emp group by job,deptno;

图5-38

MySQL> select empno,deptno,avg(sal) from emp group by deptno;

+-------+--------+-------------+

| empno | deptno | avg(sal) |

+-------+--------+-------------+

| 7782 | 10 | 2916.666667 |

| 7369 | 20 | 2175.000000 |

| 7499 | 30 | 1566.666667 |

+-------+--------+-------------+

以上SQL语句在Oracle数据库中无法执行，执行报错。

以上SQL语句在MySQL数据库中可以执行，但是执行结果矛盾。

在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数+参与分组的字段。

2. having

如果想对分组数据再进行过滤需要使用having子句

取得每个岗位的平均工资大于2000，如下图5-39所示：

select job, avg(sal) from emp group by job having avg(sal) >2000;

图5-39

分组函数的执行顺序：

根据条件查询数据

分组

采用having过滤，取得正确的数据

5.9 select语句总结

一个完整的select语句格式如下

select 字段

from 表名

where …….

group by ……..

having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)

order by ……..

以上语句的执行顺序

1. 首先执行where语句过滤原始数据
2. 执行group by进行分组
3. 执行having对分组数据进行操作
4. 执行select选出数据
5. 执行order by排序

原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。

5.10 本章小结

本章主要阐述了SQL语句中的分组汇总查询。