mySQL（DQL排序查询和分组查询）

最新推荐文章于 2024-02-13 21:57:41 发布

卿先生的学习之路

最新推荐文章于 2024-02-13 21:57:41 发布

阅读量170

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.youkuaiyun.com/qingmenghan/article/details/120794438

版权

一. 排序查询

* 语法：
select
要查询的东西
from
表
where
条件
order by 排序的字段|表达式|函数|别名【asc|desc】
* 排序方式：
   * ASC：升序，默认的
   * DESC：降序
*排序分类：
   1.按单个字段进行排序
   2.按多个字段排序
   3.按表达式排序
   4.按别名排序
   5.按函数排序
* 注意：
   * 如果有多个排序条件，则当之前的条件值一样时，才会判断第二条件

例题：查询员工的姓名和部门号和年薪，按年薪降序按姓名升序

SELECT `last_name`,`department_id`,`salary`*12*(1+IFNULL(`commission_pct`,0)) 年薪
FROM employees
ORDER BY 年薪 DESC,`last_name`;

二. 常见函数

2.1 字符函数

concat: 拼接
   substr: 截取子串
   upper: 转换成大写
   lower: 转换成小写
   trim: 去前后指定的空格和字符
   ltrim: 去左边空格
   rtrim: 去右边空格
   replace: 替换
   lpad: 左填充
   rpad: 右填充
   instr: 返回子串第一次出现的索引
   length: 获取字节个数

例题：做一个查询，产生下面的结果：

<last_name> earns <salary> monthly but wants <salary*3>

Dream Salary

King earns 24000 monthly but wants 72000

SELECT CONCAT(last_name,'earns',salary,'monthly but wants',salary*3) 'Dream Salary'
FROM employees 
WHERE salary=24000;

2.2 数学函数

round: 四舍五入
   rand: 随机数
   floor: 向下取整
   ceil: 向上取整
   mod: 取余
   truncate: 截断

2.3 日期函数

now: 当前系统日期+时间
   curdate: 当前系统日期
   curtime: 当前系统时间
   str_to_date: 将字符转换成日期

date_format: 将日期转换成字符

datediff:两个日期天数之差

例题：查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFRENCE）

SELECT DATEDIFF(MAX(`hiredate`),MIN(`hiredate`)) AS DIFFERENT 
FROM employees;

2.4 流程控制函数

if 处理双分支
   case语句处理多分支
       情况1：处理等值判断
       情况2：处理条件判断

例题：使用case-when，按照下面的条件：

job grade

AD_PRES A

ST_MAN B

IT_PROG C

SA_REP D

ST_CLERK E

产生下面的结果

Last_name	Job_id	Grade
king	AD_PRES	A

SELECT `last_name`,`job_id`,
CASE `job_id`
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
ELSE `job_id`
END AS grade
FROM employees
WHERE `job_id`='AD_PRES';

2.5 其他函数

version: 版本
database: 当前库
user: 当前连接用户

三. 分组函数

3.1 函数

count：计算个数
* 一般选择非空的列：主键
* count(*)
* max：计算最大值
* min：计算最小值
* sum：计算和
* avg：计算平均值

例题：查询员工最高工资和最低工资的差距（DIFFERENCE）

SELECT MAX(salary)-MIN(salary) DIFFERENCE 
FROM employees;

3.2 特点

1、以上五个分组函数都忽略null值，除了 count(*)
   2、sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型
   3、都可以搭配distinct使用，用于统计去重后的结果
   4、count的参数可以支持：
* 字段、*、常量值，一般放1

3.3 注意

聚合函数的计算，排除null值。
解决方案：
* 选择不包含非空的列进行计算
* IFNULL函数

四.分组查询

4.1 语法

select 查询的字段，分组函数
from 表
group by 分组的字段

例题：选择具有各个job_id的员工人数

select `job_id`,count(*) 员工人数 
from employees 
where `job_id` is not null
group by `job_id`;

4.2 特点

1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、可以按多个字段分组，字段之间用逗号隔开
4、可以支持排序
5、having后可以支持别名

4.3 面试相关

where 和 having 的区别？
1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。