SQL基础（3）——排序查询和常见函数

排序查询

order by

#案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】
SELECT *
FROM employees
WHERE department_id >=90
ORDER BY HIREDATE ASC
#案例3：按年薪的高低显示员工信息和 年薪【按表达式排序】
#这里的按表达式排序是指年薪是一个表达式
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
WHERE 
ORDER BY salary*12*(1+IFNULL(commission_pct,0))  DESC；
#案例4：按年薪的高低显示员工信息和 年薪【按别名】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
WHERE 
ORDER BY  年薪 DESC；
#案例5：按姓名的长度显示员工的姓名和工资【函数排序】
SELECT last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;

#案例6：查询员工信息，先按工资排序，再按员工编号排序【按多个字段排序】
SELECT * 
FROM employees
ORDER BY salary ASC,employee_id DESC

#查询员工姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name deparment_id salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC,LENGTH(last_name) ASC;
#选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name,salary
FROM employees
WHERE salary NOT between 8000 and 17000 
ORDER BY salary DESC
#查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT *
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC

常见函数

概念：类似于java的方法，将一组逻辑语句封装再方法体系中，对外暴露方法名。

好处：1.隐藏了实现细节；2.提高了代码的重用性

调用：select 函数名(实参列表) [from 表]

特点：叫什么（函数名）；干什么（函数功能）

分类：1.单行函数；2.concat,length,ifnull;3.分组函数（聚合函数，组函数）：做统计使用

一、 字符函数

length;concat;upper,lower;substr;instr;trim;lpad rpad;replace

#一、字符函数
#length 获取参数值的字节个数
SELECT LENGTH('john')
SELECT LENGTH('张三丰hahaha')
#一个字母占一个字节，一个汉字占三个字节

#CONCAT 拼接字符
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
#upper lower
SELECT UPPER('john');
SELECT LOWER('john');
#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees
#4.substr,substring
注意：索引从开始
SELECT SUBSTR('李莫愁爱上了路展元',7) out_put;
#截取从第七个索引开始的字符串
SELECT SUBSTR('李莫愁爱上了路展元',1,3) out_put
#截取从指定索引处指定字符长度的字符，从第一个字符开始到第三个字符
#案例：姓名中，首字符大写，其它字符小写，然后用下划线拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put FROM employees;
#5.instr
#返回字串在原字符串中的初始索引；找不到返回0
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠')  AS out_put
#6.trim
#去掉字符串前后的空格,去除不了中间的空格
SELECT LENGTH(TRIM('		张翠山		') )AS out_put
SELECT TRIM('a',FROM 'aaaaaaa张aaaaa翠山aaaaaaaaa') AS out_put;
#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',10,'*') AS out_put；
#8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put；
#9.replace 替换
#将周芷若替换成了赵敏
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

二、数学函数

round;ceil;floor;mod;truncate

#二、数学函数
#round 四舍五入
SELECT ROUND(-1.55); #-2
SELECT ROUND(-1.45); #-1
SELECT ROUND(1.567,2); #1.57
#ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(1.52);#2
SELECT CEIL(1.00);#1
SELECT CEIL(-1.02);#-1
#floor 向下取整,返回<=该参数的最小整数
SELECT FLOOR(9.99);#9
SELECT FLOOR(-9.99);#-10
#truncate 截断
SELECT TRUNCATE(1.65,1);#1.6
#mod 取余
#mod(a,b)=a-a/b*b
SELECT MOD(-10,3);#-1
SELECT MOD(10,-3);#1
SELECT 10%3;#-1

日期函数

now;curdate;curtime;year;month;monthname;str_to_date;date_format

#now返回当前系统日期+时间
select NOW();
#curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
#curtime 返回当前时间，不包含日期
SELECT CURTIME();
#可以获取指定部分，年，月，日，小时，分钟，秒
SELECT YEAR(NOW() 年；#2022
SELECT YEAR('1999-1-1') 年；#1999
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月；#5
SELECT MONTHNAME(NOW()) 月；#May
#str_to_date  将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1999-3-2','%Y-%c-%d') AS out_put;#1998-03-02
#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate ='1992-4-3';
SELECT * FROM employees WHERE hiredate= STR_TO_DATE('4-3 1992','%c-%d %Y');
#date_format:将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put; #2022年5月22日
#查询有奖金的员工名和入职日期(xx月xx日 xx年)
SELECT last_name DATE_FORMAT(hiredate,'%m月%d日 %年') 入职日期 FROM employees WHERE commission_pct is not NULL

其他函数

version;database;user

SELECT VERSION();
SELECT DATABASE();
SELECT USER()

流程控制函数

if ;case

#1.if 函数 if else 的效果
SELECT IF(10>5,'大','小');
SELECT last_name commission_pct, IF(commission_pct is not null,'有奖金','无奖金') 备注 FROM employees;
#2.case 函数的使用一：switch case 的效果
#案例：查询员工工资，部门号，要求：部门号=30，显示的工资为1.1倍；部门号=40，显示的工资为1.2倍；部门号=50，显示的工资为1.3倍；其他部门，显示的工资为原工资。
SELECT salary department_id
CASE department_id
WHERE 30 THEN salary*1.1
WHERE 40 THEN salary*1.2
WHERE 50 THEN salary*1.3
else salary
end AS 新工资
#3.case 函数的使用二：类似于多重if （多用于区间查询）
#案例：查询员工的工资情况:如果员工工资>20000,显示A级别;如果员工工资>15000,显示B级别；如果>10000，显示C级别；否则，显示D级别。
SELECT salary, 
case
when salary>20000 then 'A级别'
when salary between 15000 and 20000 then 'B级别'
when salary between 10000 and 15000 then 'C级别'
else AS 工资级别
FROM employees;

课后习题

# 将员工的姓名按首字母排序，并写出姓名的长度(length)
#写法1
SELECT last_name,LENGTH(last_name)
FROM employees
ORDER BY SUBSTR(last_name,1,1);
#写法2
SELECT last_name,LENGTH(last_name)，SUBSTR(last_name,1,1) 首字符
FROM employees
ORDER BY 首字符;
#4.做一个查询：
SELECT CONCAT(last_name,'earn', salary,'monthly','but','wants',salary*3 ) AS dream salary
FROM  employees
WHERE salary=24000;
#5.
SELECT last_name,job_id AS job,
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'
END AS Grade
FROM employees
WHERE job_id='AD_PERS';