2、数据查询语言(DQL)学习
- 基础查询
- 条件查询
- 排序查询
- 单行函数
- 分组函数
- 分组查询
- 连接查询
- 子查询
- 分页查询
- union联合查询
# 进阶4:常见函数
/*
概念:将一组逻辑语句封装在方法体中,对外暴露方法名
好处:隐藏了实现细节;提高代码的重复使用性;
调用:select 函数名(实参数列表) 【from 表】;
特点:
叫什么(函数名)
干什么(函数功能)
常见函数分类:
1、单行函数(处理)
2、分组函数(统计)
*/
2.4 单行函数
(1) 字符函数
#一、字符函数
#length 获取参数值的直接个数
SELECT LENGTH('john'); #4
# 中文三个字节,英文和数字一个字节
SELECT LENGTH('张三丰hhhh12'); #15
# 获取字符个数
SELECT CHAR_LENGTH(s)('张三丰hhhh12'); #9
#concat 拼接字符串
SELECT CONCAT(last_name,'-',first_name) AS 姓名 FROM employees;
#upper变大写、lower变小写
SELECT UPPER('john'); #JOHN
SELECT LOWER('joHn'); #john
# 案例:将姓变大写,名字变小写,然后拼接
SELECT CONCAT(UPPER(last_name),'-',LOWER(first_name)) AS 姓名 FROM employees;
#substr(substring)前面是后面的简写,索引从1开始,在指定位置开始向后截取字符串
#或者指定截取长度
SELECT SUBSTR('李莫愁爱上陆展元',6) AS out_put; #陆展元
SELECT SUBSTR('李莫愁爱上陆展元',6,2) AS out_put; #陆展
#案例:姓名中首字符大写,其他字符小写,然后用-拼接
SELECT
CONCAT(UPPER(SUBSTR(last_name,1,1)),'-',LOWER(SUBSTR(last_name,2))) AS 姓名
FROM employees;
LEFT(s,n) 返回字符串s最左边的n个字符
RIGHT(s,n) 返回字符串s最右边的n个字符
select left("张三丰",2); #张三
select right("张三丰",1); #丰
select left("张三丰",4); #张三丰
#instr 返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR('李莫愁爱上陆展元',6) AS out_put; #0
SELECT INSTR('李莫愁爱上陆展元','爱上') AS out_put; #4
#trim 不指定参数就是去空格(前后去空格,中间不去)
SELECT TRIM(' 李 莫愁 ') AS out_put; #李 莫愁
SELECT TRIM('a' FROM 'aa aaa 李莫 愁 aa aaaa') AS out_put;# aaa 李莫 愁 aa
#lpad 用指定的字符实现左填充以达到指定长度
#rpad 右填充
SELECT LPAD('李莫愁',10,'*') AS out_put;
SELECT LPAD('李莫愁',2,'*') AS out_put; #李莫
# STRCMP 比较两个字符大小,前面大于后面返回1
select strcmp('abc','acb'); #-1
select strcmp('acb','abc'); #1
select strcmp('acb','acb'); #0
#replace 替换,有几个替换几个
SELECT REPLACE('周芷若周芷若张无忌爱上周芷若周芷若周芷若','周芷若','赵敏') AS out_put;
(2)数学函数
# 二、数学函数
#round 四舍五入,3表示小数点后位数
SELECT ROUND(4.65); #5
SELECT ROUND(-4.6533) #-5
SELECT ROUND(-4.6533,3); # -4.653
#ceil 向上取整,返回值大于等于该参数的最小整数
#floor 向下取整,返回值小于等于该参数的最大整数
SELECT CEIL(4.65); #5
SELECT FLOOR(4.65); #4
#truncate 小数点后截断,参数是小数点后位数
SELECT TRUNCATE(1.69999,2); #1.69
SELECT TRUNCATE(500,2); #500
#mod 取余,符号看前面的数
#a%b=a-a/b*b
SELECT MOD(10,-3); #1
SELECT MOD(-10,-3); #-1
SELECT MOD(-10,3); #-1
SELECT MOD(10,3); #1
(3)日期函数
#三、日期函数
#now返回当前系统日期+时间
SELECT NOW(); #2020-08-14 21:21:17
#curdate返回当前系统日期,不包括时间
SELECT CURDATE(); #2020-08-14
#curtime返回当前时间,不包括日期
SELECT CURTIME(); #21:21:17
#DATEDIFF(date1,date2)返回date1 - date2的日期间隔
#TIMEDIFF(time1, time2)返回time1 - time2的时间间隔
select DATEDIFF('2020-10-28','1993-2-2'); #10130,这个是天数
select TIMEDIFF('21:07','6:30'); #14:37:00
#可以获取指定的部分,年,月,day日,hour小时,minute分钟,second秒
SELECT YEAR(NOW()) AS 年; #2020
SELECT YEAR('1993-02-02') AS 年; #1993
SELECT YEAR(hiredate) FROM employees;
SELECT MONTH(NOW()) AS 月; #8
# 英文显示
SELECT MONTHNAME(NOW()) AS 月; #August
SELECT MONTH('1993-02-02') AS 月; #2
SELECT MONTH(hiredate) FROM employees;
#str_to_date: 将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y'); #1999-09-13
# 查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('3-4 1992','%d-%c %Y');
#date_format:将日期转换成字符
SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日'); #2018年06月06日
SELECT DATE_FORMAT('2018/6/6','%Y-%m-%d'); #2018-06-06
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); #2020-08-14
# 查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;
(4)其他函数
- version版本
- database当前库
- user当前连接用户
#四、其他函数
#查看mysql版本信息
SELECT VERSION(); #5.5.15
#查看当前使用的数据库
SELECT DATABASE(); #myemployees
#查看用户
SELECT USER(); #root@localhost
(5)流程控制函数
- if 处理双分支
- case语句 处理多分支
- 情况1:处理等值判断
- 情况2:处理条件判断
#五、流程控制函数
#1、if函数: if-else的效果
# if 10<5:大 else: 小
SELECT IF(10<5,'大','小'); # 小
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金','有奖金') AS 备注
FROM employees;
#2、case语句处理多分支
#使用1:swith case的效果,等值判断
/*
语法:
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值1或语句2;
。。。。
else 要现实的值n或者语句n;
end
*/
# 案例
/*
查询员工的工资,要求:
部门号30,工资为1.1倍
部门号40,工资为1.2倍
部门号50,工资为1.3倍
部门号其他,工资为原工资
*/
SELECT salary AS 原工资,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
END
AS 新工资
FROM employees;
#使用2:多重if,区间判断
/*
语法:
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end
*/
# 案例
/*
查询员工的工资,要求:
如工资>20000,评级A
如工资>15000,评级B
如工资>10000,评级C
其他,评级D
*/
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END
AS 评级
FROM employees;
测试:
2.5 分组函数
- sum 求和
- max 最大值
- min 最小值
- avg 平均值
- count 计数
#二、分组函数
/*
功能:用作统计使用,又称为聚合函数或统计函数或组函数
分类:
sum求和;avg平均值;max最大值;min最小值;count计算非空个数
注意:
null不参与运算
和distinct搭配
*/
# 简单使用:不是每个人求统计,而是所有的求一个统计值
SELECT SUM(salary) FROM employees; #691400.00
SELECT AVG(salary) FROM employees; #6461.682243
SELECT MIN(salary) FROM employees; #2100.00
SELECT MAX(salary) FROM employees; #24000.00
SELECT COUNT(salary) FROM employees; #107
# 复合使用
SELECT
SUM(salary) AS 和,
AVG(salary) AS 均值,
MAX(salary) AS 最大值,
MIN(salary) AS 最小值,
COUNT(salary) AS 个数
FROM employees;
#和distinct搭配(去重再统计)
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
#count函数的详细介绍
SELECT COUNT(salary) FROM employees; # 107
# 用来统计行数,其中1可以换成其他常量值或者字符,常用*号
SELECT COUNT(*) FROM employees; # 107
SELECT COUNT(1) FROM employees; # 107
SELECT COUNT('嘻嘻') FROM employees; # 107
#和分组函数一同查询的字段有限制,一般不要一块使用
# 分组函数是所有的统计一个值,所以影响
SELECT AVG(salary),employee_id FROM employees;
# 测试
#查询员工表中最大入职时间和最小入职时间的相差天数,命名为diffrence
SELECT DATEDIFF('2017-10-1','2017-9-29') AS difference;
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) AS difference
FROM employees;
# 查询部门编号为90的员工个数
SELECT COUNT(*) 个数
FROM employees
WHERE department_id=90;
2.6 分组查询
# 进阶5:分组查询
/*
语法:
select 分组函数,列(要求出现在哎group by的后面)
from 表
【where 筛选条件】
group by
【order by 子句】
注意:
查询列表比较特殊,要求是分组函数和group by后出现的字段
where必须出现在from后面
特点:
分组查询中的筛选条件分为两类:
分组前筛选(where) group by 前
分组后筛选(having)group by 后
*/
# 一、简单的分组查询
#案例1:查询每个工种的最高工资
SELECT
MAX(salary) AS 最高工资,
job_id AS 工种
FROM employees
GROUP BY job_id;
# 案例2:查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
# 二、添加筛选条件的分组查询
#案例1:查询邮箱中包含a字符的,每个部门的平均工资
SELECT AVG(salary),department_id,email
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#案例2:有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
#案例3:查询哪个部门的员工个数>2
/*
1、查询每个部门的员工个数
2、根据1中的结果进行筛选
*/
#1、第一步
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
#2、第二步
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
#案例4:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
/*
1、查询每个工种有奖金的员工的最高工资
2、在1的基础上判断最高工资>12000
*/
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL # 分组前筛选
GROUP BY job_id
HAVING MAX(salary)>12000; # 分组后筛选
#案例5:查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪些,以及对应的最低工资
/*
1、查询领导编号>102的领导手下的最低工资
2、在1的基础上判断最低工资>12000
*/
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102 # 分组前筛选
GROUP BY manager_id
HAVING MIN(salary)>5000; # 分组后筛选
#三、按表达式或者函数进行分组
#案例1:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
SELECT LENGTH(last_name),COUNT(*)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
#四、按多个字段分组
#案例:查询每个部门每个工种的员工的平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
#五、添加排序
#案例:查询每个非空部门每个工种的员工平均工资>10000的信息,并且平均工资排序
SELECT department_id,job_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC;