2、SQL学习：排序查询and分组查询_分组排序查询

@爱学习的DUO

第三章 查询语言

——————————————————————————————

3.3 排序查询

3.3.1 排序查询案例

• 语法：
  select 查询列表
  from 表
  where 筛选条件
  order by 排序列表 asc/desc

例1：查询员工信息，要求：salary从低到高进行排序。

SELECT * 
FROM  employees 
ORDER BY salary ASC 
1
2
3

例2：查询部门编号>=90的员工信息，按入职时间先后排序。

SELECT * 
FROM  employees 
WHERE department_id>=90
ORDER BY hiredate ASC 
1
2
3
4

例3：【按表达式排序】， 按年薪高低显示员工信息、年薪。

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM  employees 
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC
1
2
3

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM  employees 
ORDER BY 年薪 DESC
1
2
3

note1：结果表中会有员工信息列、年薪列。
note2：order by也支持别名。

例4：【按函数排序】，根据last_name的长度排序，显示员工的姓名、工资，last_name长度。

• length()介绍

SELECT LENGTH('Zeng')  #结果为4
1

• 代码

SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDER BY 字节长度
1
2
3

• 结果
  

例5：查询员工信息，要求先按工资排序，再按员工编号排序。

SELECT salary,employee_id
FROM employees
ORDER BY salary ASC,employee_id DESC
1
2
3

• 结果：整体上salary从低到高，如果salary相等，则employee_id从高到低。

3.3.2 排序查询总结

Ⅰ：order by 单个字段、多个字段、表达式、函数、别名；
Ⅱ：order by子句一般放再查询语句最后面。
Ⅲ：执行顺序：from 表—where 条件—select 字段——order by

——————————————————————————————

3.4 常见函数

• 语法：

SELECT  函数名()  FROM  表
note: 函数用到了表中的字段
1
2

• 函数分类：
  （1）单行函数：concat、length、ifnull，肯定会有1个返回值；
  （2）分组函数/聚合函数/统计函数：做统计使用的

3.4.1 单行函数

3.4.1.1 字符函数

（1）length函数

SELECT LENGTH('Zeng')  #结果为4字节
SELECT LENGTH('朵朵朵hahaha')  #结果为15，1汉字3字节（utf8）
1
2

（2）concat函数：拼接字符串
例1：拼接last_name、frist_name，中间用下划线。

SELECT 
  CONCAT(last_name, '_', first_name) 姓名
FROM
  employees 
1
2
3
4

(3)upper和lower

SELECT  UPPER('jenny')  #结果为JENNY，变为大写
SELECT  LOWER('JENNy')  #结果为jenny，变为小写
1
2

例2：将last_name大写，first_name小写，然后拼接。

SELECT 
  CONCAT(UPPER(last_name), LOWER(first_name)) 姓名 
FROM
  employees 
1
2
3
4

（4）substr：截取字符串

• 截取的是字符长度，不是字节长度。
• 索引从1开始，不是从0开始。

SELECT SUBSTR('天天都要开心呀',5)  结果   #索引从1开始，返回值“开心呀”
SELECT SUBSTR('天天都要开心呀',3,2)  结果 #返回“都要”，从第3位开始，截取2位
1
2

（5）instr函数

• 作用：返回子串第一次出现的索引，如果找不到则返回0。

SELECT INSTR("爱学习的阿曾曾",'学习') 结果  #返回值为2
1

（6）replace函数：替换

SELECT REPLACE('摸鱼摸鱼摸鱼和学习摸鱼','摸鱼','睡觉')
结果为：睡觉睡觉睡觉和学习睡觉
1
2

3.4.1.2 数学函数

（1）round：四舍五入

SELECT ROUND(3.14)  #结果为3，默认取整
SELECT ROUND(1.567,2) #小数点后保留2位，结果1.57
1
2

（2）cell：向上取整
（3）floor：向下取整
（4）mod：取余数

3.4.1.3 日期函数

（1）now、curdata、curtime

SELECT NOW()  #返回当前日期+时间
SELECT CURDATE()  #返回当前日期，无时间
SELECT CURTIME()  #返回当前时间，无日期
1
2
3

（2）str_to_date:将日期格式转化为指定的日期格式

SELECT STR_TO_DATE('4-23-2022','%m-%d-%Y')  #按%m-%d-%Y去解析，2022-04-23
1

3.4.1.4 流程控制函数

（1）if函数

SELECT IF('10>5','大','小')
1

SELECT 
  IF(commission_pct IS NULL,'没奖金', '有奖金') 备注 
FROM
  employees 
1
2
3
4

（2）case函数1

• 语法：
  case 要判断的字段或表达式
  when 常量 then 要显示的东西
  when 常量 then 要显示的东西
  else 要显示的东西
  end

例1：查询员工工资，要求：
①如果department_id=30，显示工资为1.1倍；
②如果department_id=40，显示工资为1.2倍；
③如果department_id=50，显示工资为1.3倍；
④其他，显示原工资。

#将case—end作为一个字段
SELECT salary 原始工资,department_id 部门编号,
---------------------------------------------------------------------
CASE `department_id`
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary*1.1
END 新工资
---------------------------------------------------------------------
FROM employees
1
2
3
4
5
6
7
8
9
10
11

• 结果显示：
  

（3）case函数2

• 语法
  case
  when 条件1 then 要显示的东西
  when 条件2 then 要显示的东西
  else 要显示的东西
  end

例2：查询员工工资，要求：
①如果工资>20000，显示A级别；
②如果工资>15000，显示B级别；
③如果工资>10000，显示C级别；
④其他，显示D级别。

#将case—end作为一个字段
SELECT salary 工资,
CASE 
WHEN salary>20000 THEN 'A级别'
WHEN salary>15000 THEN 'B级别'
WHEN salary>10000 THEN 'C级别'
ELSE 'D级别'
END 工资级别
FROM employees
1
2
3
4
5
6
7
8
9

• 结果：
  

3.4.2 聚合函数/分组函数

- 功能：用作统计和处理，又叫统计函数、聚合函数、分组函数。
- 常见：sum、avg、max、min、count

3.4.2.1 sum函数

SELECT SUM(salary) FROM employees
1

• 结果
  

3.4.2.2 avg函数

SELECT AVG(salary) FROM employees
1

3.4.2.3 count函数

SELECT COUNT(commission_pct) FROM employees  #count非空的值
SELECT COUNT(*) FROM employees  #统计行数
1
2

3.4.3 分组函数和distinct使用

SELECT 
  SUM(DISTINCT salary) 去重求和,
  SUM(salary) 求和 
FROM
  employees 
1
2
3
4
5

• 结果：
  

3.5 分组查询（group by）

• 语法
  select 字段，group_function
  from 表
  where 筛选条件
  group by group_by_expression
  order by

• 注意：查询列表的列，要求分组函数和group by后出现的字段。

• 分组查询的筛选：
  ①分组前的筛选（where），以分组前的数据源来查询
  ②分组后的筛选（having)，以分组后的数据源来查询

3.5.1 分组前查询（单个字段）

例1：查询每个工种（按工种group by）的最高工资（分组函数）

SELECT 
  AVG(salary) 平均工资,job_id
FROM
  employees 
GROUP BY job_id
1
2
3
4
5

结果：

例2：查询每个位置上（group by）的部门个数（分组函数sum）

SELECT 
  COUNT(*) 部门个数,location_id 
FROM
  departments 
GROUP BY location_id 
1
2
3
4
5

• 结果：
  
  例3：查询邮箱中包含a字符的，每个部门的平均工资。

SELECT email ,AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id
1
2
3
4

• 结果：
  

3.5.2 分组后查询

例1：分组后的查询，查询哪个部门的员工个数大于2。
step1：查询每个部门的员工个数

SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
1
2
3

• 结果：
  
  step2：根据step1，查询员工个数大于2。

SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2
1
2
3
4

• 结果：
  

3.5.3 按表达式或函数分组查询

例1：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些？

SELECT COUNT(*),LENGTH(`last_name`) 名长度
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5
1
2
3
4

3.5.4 按多个字段分组查询

例1：查询每个部门、每个工种的员工平均工资

SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
1
2
3

• 结果
  

3.5.5 分组查询总结

1、group by支持单个字段、多个字段
2、order by在group by后面