sql基础查询语句进阶3和4

3.排序查询
select 列表 from 表
where 筛选条件
order by 排序列表【asc/desc】*默认升序
特点：

1. 支持单个字段、多个字段、表达式、函数、别名
2. limit子句在order by 前面

案例1: 按年薪高低显示员工的信息和年薪【按表达式排序】
select ， salary12（ifnull（1+commission_pct,0)）as 年薪
from employees
order by salary12*（ifnull（1+commission_pct,0)）desc；

案例2: 按年薪高低显示员工的信息和年薪【按别名排序】
select ， salary12*（ifnull（1+commission_pct,0)）as 年薪
from employees
order by 年薪 desc；

案例3: 按年薪高低显示员工的姓名和工资【按函数排序】
select length（last_name）as 字节长度， last_name，salary
from employees
order by length（last_name） desc；

案例4: 先按工资升序，对于工资一样的信息再按员工编号降序，显示全部信息【按多字段排序】
select*
from employees
order by salary asc，employee_id desc；

4.常见函数

select 函数（实参列表）from 表；

分类1
1.单行函数
concat（）；ifnull（）
2.分组函数
调用多列表，只输出一个值——统计

分类2

1.字符函数

select length（‘张三丰hahaha’）；
结果：共15个字节
*因为utf8里，一个汉字占3个字节

upper、lower 大写/小写

substr/substring 截取
1.截取从指定索引处后面所有字符
select substr（‘李莫愁爱上了陆展元’，6）；
结果：了陆展元
*索引从1开始

2.截取从指定索引处指定字符长度的字符
select substr（‘李莫愁爱上了陆展元’，1，3）；
李莫愁

instr 返回子串第一次出现的索引，如果找不到返回0
select instr （‘杨不悔爱上了殷六侠’，‘殷六侠’）；
结果：7

trim 去前后空格
select trim（‘a’ from ‘aaaaa张aaaaaaaaaa翠山aaaaaaaaaaa’）；
结果：张aaaaaaaaaa翠山
*只取掉首尾字符

lpad用指定字符左填充指定长度
select lpad（‘殷素素’，10，‘’）；
结果：**殷素素
10 为字符总长度
若为2，<原字符长度，则从2将后面截断
结果：殷素
rpad用指定字符右填充指定长度

replace替换
select replace（‘周芷若周芷若’，‘周芷若’，‘赵敏’）；
结果：赵敏赵敏

2.数学函数

四舍五入
round（0.65，1）；1为小数位
向上取整
ceil(1.00）；
向下取整
floor（）；
数值截断
select truncate（1.699999，1）1为小数位
结果：1.6，截断后面位数不要
取余
select mod(10,-3)；
=select 10 % 3；

3.日期函数

当前日期+时间
select now（）；
当前日期
select curdate（）；
当前时间
select curtime（）；
获取指定的部分，年、月、日、小时、分、秒
select year（now（））；
select monthname（now（））；英文月份

将字符通过指定格式转换为日期
select str_to_date （‘1998-3-2’，‘%Y-%c-%d’);
将日期转换成字符
select date_format (now（），‘%y年%m月%d日’）；

4.流程控制函数

if函数；if else效果
select lest_name,commission_pct,if(commission_pct is null,”no”,”yes”) as 备注
from employee；

case 多条件
用法1：等值判断

case 要判断的字段或表达式 when 常量1 then要显示的值1或语句1； when 常量2 then要显示的值2或语句2； … else 要显示的值n或语句n（默认值）； end

案例：
select salary 原始工资，department_id，

case department_id when 30 then salary1.1 when 40 then salary1.2 when 50 then salary*1.3 else salary end as 新工资

from employee；

用法2: 多重if，条件判断

case when 条件1 then要显示的值1或语句1； when 条件2 then要显示的值2或语句2； … else 要显示的值n或语句n（默认值）； end

案例：
select salary，

case when salary>20000 then ‘A’ when salary>15000 then ‘B’ else ‘C’ end as 工资级别

from employee；

5.分组函数

用于统计，又称聚合函数/统计函数/组函数
sum/max/min/avg/count

特点：

• sum、avg只能处理数值型；max、min、count可以处理任何类型
• 以上分组函数都忽略null值
• 搭配distinct实现去重
• 一般使用count（*）统计行数
• 和分组函数一同查询的字段要求是group by后的字段

select sum(last_name),avg(last_name) from employees; ❌
select sum(hiredate),avg(hiredate) from employees;✅

select max(last_name),min(last_name) from employees;✅
*按字母排序
select sum(hiredate),avg(hiredate) from employees;✅

select count(commission_pct) from employees;
*忽略null值

count函数的详细介绍
select count(salary) from employees；

select count(）from employees；
一整行只要有非null值，就+1
count（）效率高，count（字段）效率低

select count(1）from employees；

案例：
1.查询员工表中最大和最小入职时间的相差天数
select datediff（max（hiredate），min（hiredate））as difference
from employees；
2.查询部门编号为90 的员工个数
select count（*）
from employees
where department_id = 90;