10.31总结
作业回顾：
# 把马小兰的班级编号改的和小强一样
# 小强班级编号：select class_nofrom tb_studentwhere stu_name = '小强';
update tb_studentset class_no=(select t.class_nofrom (select * from tb_student) as twhere t.stu_name = '小强')where stu_name = '马小兰';# 不一定要写成t.stu_name 这种：
select class_nofrom tb_studentwhere stu_name = '小强';
update tb_studentset class_no=(select class_nofrom (select * from tb_student) as twhere stu_name = '小强')where stu_name = '马小兰';select * from tb_student;-- with语句 根据查询结果 定义一张临时表 查询结束 这张表就没有了
-- 马小兰的班级修改成和小红一样 t.stu_name 中的t可省略
with t as (select * from tb_student)
update tb_studentset class_no = (select class_nofrom twhere t.stu_name = '小红')where stu_name='马小兰';1. 数据库查询作业：
- 三种判断名字的方法：
-- 1.找出姓名以a、b、s开始的员工信息
select * from emp where left(ename,1) in ('a','b','s'); -- 要单引号，不加引号错误
select * from emp where left(ename,1) in (a,b,s);
-- 模糊
select * from emp where ename like 'a%' or ename like 'b%' or ename like 'c%';
-- 正则
select * from emp where ename regexp '^[abc]';
- 内连接
-- 4.返回拥有员工的部门名、部门号select distinct dname,emp.deptnofrom empinner join depton emp.deptno = dept.deptno;
- 自连接
-- 6.返回员工和所属经理的姓名
select * from emp;
select 员工表.ename,经理表.enamefrom emp 员工表inner join emp 经理表on 员工表.mgr = 经理表.empno;-- 7.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
select 员工表.ename,经理表.enamefrom emp 员工表inner join emp 经理表on 员工表.mgr = 经理表.empnowhere 员工表.hiredate < 经理表.hiredate;
- 搜不出来，可能是筛选条件打错字母了
-- 正确 clerk
select ename,dnamefrom empinner join depton emp.deptno = dept.deptnowhere job = 'clerk';
-- 错误 clerl
select ename,dnamefrom empinner join depton emp.deptno = dept.deptnowhere job = 'clerl';
总结:
子窗口区分于大窗口，是在大窗口上开小窗口
窗口函数的语法:
窗口函数 over(partiton by 分组字段 [order by 排序字段 排序方式][frame 子窗口])
partiton by 分组 但是不影响数据的总行数
1.interval: 间隔 -----------遇到日期时候用
ds是日期，所以不能直接减，需要用日期的减法。
/*
对日期的操作 interval 给定一个数值 给定一个时间类型
时间类型
year month day hour minute second week quarter day_second ....
*/ 几天几小时几分几秒- 近 3天的销量和
-- 近3天 数据不连续 不能用rows
-- range ds 日期 对日期-2 -2天select *,sum(sales)over (order by ds range between interval 2 day preceding and current row ) as '近3天累计' 前2天from day_sales; -- 不能分组，分组的话，每一个日期都是一个窗口,每个窗口里面都只有一个sales
/*
对日期的操作 interval 数值 时间类型
时间类型
year month day hour minute second week quarter day_second ....
*/ 秒 季度 几天几小时几分几秒
2.排名函数：
窗口函数
- 聚合函数构成的窗口函数
max() min sum avg count
max() over() - 排名函数 用作topN问题
rank() 出现并列 并且并列会占用名次
dense_rank() 出现并列 但是不占用名次
row_number() 不会出现并列
rank dense_rank row_number
1 90 1 1 1
2 90 1 1 2
3 80 3 2 3
4 80 3 2 4
5 70 5 3 5
-- 排名函数 topN问题
select * from ym_sales;
-- 根据销量进行排名 不区分年 1-22名
-- rank 并列 占用名次 1 2 2 4
-- dense_rank 并列 不占用名字 1 2 2 3
-- row_number 不并列 1 2 3 4 5 6
select *,rank() over (order by sales desc ) as 'rank1', dense_rank() over (order by sales desc ) as 'd_rank',row_number() over (order by sales desc ) as 'r_number'from ym_sales;
# 里面没有分组，对数据整体排
- 对每年数据分开排：
-- 求每年的销量排名 2021 1-12 2022 1-12
-- 对每年的数据分开排 分组 把每年的数据放在一起
select *,rank() over (partition by yearly order by sales desc) as 'rank1', dense_rank() over (partition by yearly order by sales desc ) as 'd_rank',row_number() over (partition by yearly order by sales desc ) as 'r_number'from ym_sales;
- – 获取每年的销量的前5名
-- 获取每年的销量的前5名
-- where 中的字段来源于表中
with t as # 把它作为一张表
(select *,rank() over (partition by yearly order by sales desc) as 'rank1', dense_rank() over (partition by yearly order by sales desc ) as 'd_rank',row_number() over (partition by yearly order by sales desc ) as 'r_number'from ym_sales)
select * from t where r_number<=5;
3.自实现排序
- 自连接思路
-- mysql5实现排序 不可以用窗口实现排名
-- 思路： ym_sales 第一张表 ym_sales 第二张表 在第二张表中找比第一张表中数据要大的个数
/*
第一张表 第二张表
30 30
20 20
15 15
40 4030 2 # 在第二张表中找把它大或者等于它的，看有几个
20 3
15 4
40 1
*/select y1.yearly,y1.monthly,y1.sales,count(*) as '排名'from ym_sales y1 # 把它命名为y1 保留其所有数据 -- from谁就是以谁为主left join ym_sales y2 # 把它命名为y2 保留其所有数据；对y1里面所有数据排序，所以保留y1所有数据on y1.sales <= y2.sales # on里面就有where的功能 -- 找y2里面有几个比y1大的。group by y1.yearly, y1.monthlyorder by count(*) asc; # 按照销量升序排名
- 该自连接题目不加等号的情况 和 理解：
-- 自连接 不加等号的情况:
select y1.yearly,y1.monthly,y1.sales,count(*) as '排名'from ym_sales y1 # 这一行必须要left join ym_sales y2 # 左连接，左边必须全要on y1.sales < y2.sales # 该题不加等号group by y1.yearly, y1.monthlyorder by count(*) asc;# 理解：
87 原本是要找比它大或者等于它的数字的，加等于号的情况下，找不到比它大的，却能找到和它本身相等的，也就是另外一张相同表中的自己；
但如果不加 = 号，那么87将找不到比它大的，也找不到跟他相等的；
又由于 85 只能找比它大的，不能和它相等的，而 比85 大的，只有一个 87 ，所以，85找到一个满足条件的，也是第一个满足条件的，所以它排第1；可是 该题 选择了自连接 左连接 即：from ym_sales y1 ，所以y1必须要，所以 87必须保留；
可它根本找不到比它大或者等于它的，所以 将和 将和 85 共用一个排名，也就是都排 1
之后，84 能找到两个大于它的，也就是85 和87 ，所以排第2；该题之所以85在87 之前，是因为虽然都是排第一，但是该题目选用了升序排名，以销量进行排名，所以， 85之后是87才符合升序排名的要求，故而，85得排87前面。
- 子查询
-- 子查询
-- 双层循环遍历
-- 内层循环 找比值大的个数
-- 外层循环 取排名的数据
select *,(select count(*)+1from ym_sales y1 where y1.sales > ym_sales.sales and y1.yearly=ym_sales.yearly) as '排名'from ym_salesorder by yearly asc ,排名 asc ;
select * from ym_sales; # 排名的数据是从这里来的
select *,(select count(*) # 没有比87大的，所以为0from ym_sales y1 where y1.sales > ym_sales.sales) as '排名' -- 只有大于那么会出现0from ym_salesorder by 排名 asc ; -- 记得去引号
- 用自连接和子查询两种方法进行排序：
-- 对每年的销量排序
-- 第一张表 排序 第二种 比排序值大的数据 要求 年份相同
-- 自连接
select y1.yearly,y1.monthly,y1.sales,count(*) as '排名'from ym_sales y1left join ym_sales y2on y1.sales < y2.salesand y1.yearly = y2.yearlygroup by y1.yearly, y1.monthlyorder by yearly asc ,count(*) asc;-- 子查询
-- 双层循环遍历
-- 内层循环 找比值大的个数
-- 外层循环 取排名的数据
select *,(select count(*)+1from ym_sales y1 where y1.sales > ym_sales.sales and y1.yearly=ym_sales.yearly) as '排名'from ym_salesorder by yearly asc ,排名 asc ;
3.偏移函数：
-- 偏移函数
-- lag(字段，偏移个数) 向下偏移
-- 环比 和上个月的比较 (本月数据-上月数据)/上月数据
-- 同比 和去年同期的比较
-- 定义 和固定的月份的比较偏移函数
同比 10月 和去年同期做比较 去年的同月份
环比 和上个月的数据做比较
定比 和固定的某个月做比较 1月 lag(字段, 偏移个数) over() 向下偏移 --- 默认偏移1数据 向下偏移
例子：
1月 10 null
2月 20 10
3月 30 20
4月 40 30lead(字段，偏移个数) over() 向上偏移数据 向上偏移
例子：
1月 10 20
2月 20 30
3月 30 40
4月 40 null
select *,lag(sales ,1) over (partition by yearly order by monthly asc) '上月销量'from ym_sales;
- 用窗口函数求 环比
with t as (
select *,lag(sales ,1) over (partition by yearly order by monthly asc) '上月销量'from ym_sales)
select *,concat((sales-上月销量)*100/上月销量,'%') as '环比' from t;# 拼接的是这个数据和百分号
concat # 字符串的拼接
- 环比 换一种求法 用左连接：
-- 其他求法 y1本月 y2 上个月
select y1.*,y2.sales,concat((y1.sales-y2.sales)*100/y2.sales,'%') as '环比'from ym_sales y1left join ym_sales y2on y1.yearly = y2.yearly -- 不让年=年，会导致年无法分两大部分，而是按月分and y1.monthly = y2.monthly+1;
- 窗口函数求同比
-- 同比 偏移 12个 联表 （本月数据-去年同期）/去年同期
-- 当前月份 和去年同期数据比对select * from ym_sales;
with t as (
select *,lag(sales,11) over () as '去年同期' # 前面删了一个月，所以是11 ，正常是12from ym_sales)
select *,concat((sales-去年同期)*100/去年同期,'%') as '同比' from t;# 注意：此处over() 里面不能分组，因为要是对年进行分组的话，那么偏移需要对2021和2022年都进行偏移，2021因为前面删除了一个月，所以为11个月，那么偏移11为0，而由于分组原因，2022也要进行偏移，因为他也被删除了6月，所以也为11个月，偏移11也为0 ，结果就都为0 了。
不分组，只会对第一个年偏移：
- 同比 用连接
-- 连表
-- y1 本月数据 y2 去年同期数据
select y1.*,y2.sales,concat((y1.sales-y2.sales)*100/y2.sales,'%') as '同期'from ym_sales y1left join ym_sales y2on y1.monthly = y2.monthly -- 月份不对应上会产生笛卡尔积and y1.yearly = y2.yearly + 1;
- 定比 用窗口：
-- 定比 和同年的1月对比
-- first_value()
with t as
(select *,first_value(sales) over (partition by yearly order by monthly) as '1月'from ym_sales)
select *,concat((sales-1月)*100/1月,'%') as '定比' from t;
- 定比 联表
-- 联表
-- y1 本月数据 y2 获取的是本年的1月份的数据
select y1.*,y2.sales,concat((y1.sales-y2.sales)*100/y2.sales,'%') as '定比'from ym_sales y1left join ym_sales y2on y1.yearly = y2.yearly -- 此处月份要是相等了,那么只会有1月数据and y2.monthly = 1;
4. 向上偏移
-- lead(字段，偏移个数) over()
select *,lead(sales,1) over (partition by yearly order by monthly) as '向上偏移'from ym_sales;
-- last_value 窗口的最后一个
-- 子窗口的默认值 从分区的第一个到当前行
之所以需要用到rows 是因为
select *,last_value(sales)over (partition by yearlyorder by monthlyrows between unbounded preceding and unbounded following) as '最后一个'from ym_sales; # 该分区的上边界到下边界-- 这样也行：也能找到最后一个
select *,last_value(sales) over (partition by yearly) as '最后一个'from ym_sales;
5.数值函数：
-- 求绝对值 abs()
select abs(-10) as '绝对值' from dual; # dual是虚拟表，可以不写-- 求幂数 pow() log() sqrt()
select pow(2,5) as '幂数', # 32pow(16,1/2) as '开平方', # 4log(3,27) as '对数', # 3log10(100) as '对数', # 2sqrt(36) as '开平方'; # 6-- 取整函数 向上取整 ceil() 向下取整 floor()
select ceil(19.1) as '向上取整',floor(20.9) as '向下取整'; # 20 # 20-- 四舍五入函数
select round(89.877,1) as '四舍五入',truncate(89.877,1) as '只舍不入';# 89.9 # 89.8-- 取余函数 mod() divmod() -- 取整取余 元组
select mod(10,3) as '取余'; # 1-- 随机数
select rand() as '0-1范围中的随即小数'; # 例如 0.4310237285038677
select round(rand(),2) as '0-1范围中的随即小数'; # 例子 0.19-- 圆周率
select pi() as '圆周率'; # 3.141593
-- 千位分隔 1,000,000,000,000,000 此处保留两位小数
select format(1000000000000000.203498738974,2); # 1,000,000,000,000,000.20-- 求最大 求最大
-- max(字段名) 获取一列数据 对列求最值
-- min() select min(12,14,15); -- 错误的
select greatest(10,20,30,40,26) as '最大值',least(10,20,30,40,25) as '最小值';# 40 # 10-- 求行最大
/*
学号 姓名 语文 数学 英语 体育 greatest(数学，语文，英语，体育) 可以求每一行最大值
*/
select * from tb_score;
with t as(select stu_no,sum(if(course_no=1,score,0)) as '1号课程',sum(if(course_no=2,score,0)) as '2号课程',sum(if(course_no=3,score,0)) as '3号课程'from tb_scoregroup by stu_no)
select *,greatest(`1号课程`,`2号课程`,`3号课程`) as '最大值' from t;
6.文本函数
-- 获取字符串的字符长度 获取字符串的字节长度
select char_length('你好') as '字符长度',length('你好') as '字节长度';# 2 # 6-- 获取字符串的左边的几个字符 left right()
select left('你好天天开心',3) as '从左边获取',right('你好天天开心',3) as '从右边获取';# 你好天 # 天开心-- 获取字符串中从指定位置开始的几个字符 从1开始
select mid('你好天天开始',3,2) as '从指定位置开始，提取指定个数'; # 天天-- 如果只给开始位置 不给提取数量 到最后结束
select mid('你好天天开始',3) as '从指定位置开始，提取指定个数'; # 天天开始-- substr 和-- mid 实现操作是一样的
select substr('你好天天开始',3,2) as '从指定位置开始，提取指定个数'; # 天天
select substr('你好天天开始',3) as '从指定位置开始，提取指定个数'; # 天天开始-- 判断子串是否在字符串中 包含关系 instr() 位置从1开始
# 判断子串里有没有l 有的话显示第一次出现的位置，没有就返回0
select instr('hello','l') as '第一次出现的位置',instr('hello','m') as '没有返回0';# 3 # 0-- 判断 学生姓名有小的
select * from tb_student;
select * from tb_student where instr(stu_name,'小') <> 0; # 等于0表示没有，所以不等于0-- 替换
select replace('abcabcabc','a','A') as '把旧内容替换为新内容'; # AbcAbcAbcselect insert('12345678909',4,4,'****') as '从指定位置开始，将指定长度内容替换为新内容';# 123****8909-- 在逗号,分隔的数据中，查看指定数据是第几个
select find_in_set('b','a,b,c,d') as '第几个',find_in_set('e','a,b,c,d') as '没有返回0';# 2 # 0-- 转换
select upper('abCD') as '转大写',ucase('abCD') as '转大写', # ABCD # ABCDlower('abCD') as '转小写',lcase('abCD') as '转小写'; # abcd # abcd-- 去除字符串两端的空白
select trim(' abcd ') as '去除两端', # abcdltrim(' abcd ') as '去除左边', # abcdrtrim(' abcd ') as '去除右边'; # abcd
7. 日期函数
-- 获取当前时间 年月日 时分秒 年月日时分秒
select now() as '年月日时分秒', # 2023-11-05 17:27:50current_timestamp() as '年月日时分秒', # 2023-11-05 17:27:50curdate() as '年月日', # 2023-11-05current_date() as '年月日', # 2023-11-05curtime() as '时分秒', # 17:27:50current_time() as '时分秒'; # 17:27:50-- 提取
select year(now()) as '年', # 2023month(now()) as '月', # 11monthname(now()) as '月的英文', # Novemberday(now()) as '日', # 5hour(now()) as '时', # 17minute(now()) as '分', # 32second(now()) as '秒', # 28date(now()) as '年月日', # 2023-11-05dayofweek(now()) as '星期几', -- [星期几 默认周日是1 周一 2 周二 3] # 1dayname(now()) as '星期几英文名', # Sundaydayofyear(now()) as '这一年的第多少天', # Sundaydayofmonth(now()) as '这个月的第多少天', # 5quarter(now()) as '季度', # 4week(now()) as '这一年的第多少周', -- [周的开始默认是周天 ] # 45week(now(),1) as '这一年的第多少周', -- [周的开始默认是周一 ] # 44# 万能操作：extract(year from now()) as '年', # 2023extract(month from now()) as '月', # 11extract(hour_second from now()) as '从天到秒'; # 173228-- 获取月份的最后一天 28 29 30 31
select last_day(now()) as '最后一天'; # 2023-11-30-- 时间偏移
-- date_add 向未来偏移
-- date_sub 向过去偏移
-- 用法 date_add(时间 ,interval 偏移量的表达式 偏移类型)
/*
偏移类型 偏移量的表达式
day interval 10 day # 偏移10天
month interval 2 month
year
hour
minute
second
quarter
week
--------------------------------------
minute_second interval '5:5' minute_second # 偏移5分5秒
hour_second interval '1:10:20' hour_second # 偏移1小时10分钟10秒
hour_minute interval '1:10' hour_minute # 1小时10分钟
day_second interval '10 1:10:20' day_second # 10天 1小时10分钟20秒
day_minute interval '10 1:10' day_minute # 10天，1小时10分钟
day_hour interval '10 5' day_hour # 10天5小时
year_month interval '3-5' year_month # 3年5个月yyyy-mm-dd hh:mm:ss 时间里面用什么分割，上面用什么分割*/ 参考日期：2023-11-5
# add添加时间
select date_add(now(),interval 5 day); # 从当前偏移5天 # 2023-11-10 18:04:20
select date_add(now(),interval 4 month); # 2024-03-05 18:06:49
select date_add(now(),interval 1 quarter); # 2024-02-05 18:06:57
select date_add(now(),interval '3:10' hour_minute ); # 2023-11-05 21:17:04
select date_add(now(),interval '3:10:20' hour_second ); # 2023-11-05 21:17:30
select date_add(now(),interval '2 2' day_hour ); # 2023-11-07 20:07:17
select date_add(now(),interval '2-3' year_month ); # 2026-02-05 18:07:24
# sub 向过去偏移
select date_sub(now(),interval 1 week ); # 2023-10-29 18:07:31
select date_sub(now(),interval '10:20' hour_minute ); # 2023-11-05 07:47:39-- 时间差
-- 求两个时间差的天数
-- datediff(大时间，小时间)
select datediff('2023-10-31 16:27:30','2023-10-30 17:20:30') as '天数'; # 1
# 前面比后面小，会变负-- 其他的差值类型
-- timestampdiff(差值类型，小时间，大时间)
-- 差值类型 年 月 日 时 分 秒 周 季度
select timestampdiff(quarter ,'2022-10-30 17:20:30','2023-10-31 16:27:30') ; # 4-- 区别 差了多少天 datediff 不考虑小时 只要日期有区别 算一天
select datediff('2023-10-31 00:10:00','2023-10-30 23:50:50') as '天数'; # 1
-- 不满24小时 不计算的
select timestampdiff(day,'2023-10-30 23:50:50','2023-10-31 23:50:50') as '天数'; # 1
select timestampdiff(day,'2023-10-30 23:50:50','2023-10-31 00:10:00') as '天数'; # 0-- 时间戳 从1970-1-1 经历的描述
-- 获取当前时间的时间戳
select unix_timestamp() as '当前时间时间戳', # 1699180295unix_timestamp('2020-10-10 10:10:10') as '指定时间时间戳'; # 1602295810-- 将时间戳转化为时间
select from_unixtime(1602295810) as '从时间戳到时间', # 2020-10-10 10:10:10-- 占位符 %Y %m %d %H %i %s 注意大小写from_unixtime(1602295810,'%Y/%m/%d %H:%i:%s') as '指定格式的时间'; # 2020/10/10 10:10:10-- 时间格式化 时间类型数据转化为文本类型
select date_format('2020-10-10 10:10:10','%Y/%m/%d %H:%i:%s') as '文本类型';
# 2020/10/10 10:10:10-- 时间反格式化 将文本类型转化为时间类型
select str_to_date('2020/10/10 10:10:10','%Y/%m/%d %H:%i:%s') as '时间类型';
# 2020-10-10 10:10:10 # 要求解析格式和字符串格式一样-- 练习
select * from tb_student;
-- 本季度过生日的学生
select * from tb_student where quarter(now()) = quarter(stu_date);
-- 本月过生日的学生
select * from tb_student where month(now()) = month(stu_date);-- 下个月过生日
-- 假如 当前月是12月 下个月 应该是1月 % 是取余数
select * from tb_student where month(now()) % 12 +1 = month(stu_date);-- 本周过生日
-- 出生年月日所在的周 和 当前年 + 出生月日 时间所在的周一定一样吗？
# week(时间，参数模式)
# 默认 0 周日是第一天 计算 从第一个周天开始 为1 前面的是0
# 模式 1 周一是第一天 前面天数小于4 不计算 0
select week('2000-1-5',1),week('2015-1-5',1),week('2018-1-5');
select week('2018-1-5');
select week('2015-1-5',1);
select week('2022-1-5',1);
-- 不同年 同一个月和日 周数不一定相等
-- 出生年月日 2000-10-25 40
-- 2023-10-25 41
-- week(出生日期) 周
-- week(now()) 周
-- 出生年月日 周 2023-月-日 周
select *from tb_studentwhere week(concat(year(now()),mid(stu_date,5))) = week(now());
tudent where month(now()) = month(stu_date);
– 下个月过生日
– 假如 当前月是12月 下个月 应该是1月 % 是取余数
select * from tb_student where month(now()) % 12 +1 = month(stu_date);
– 本周过生日
– 出生年月日所在的周 和 当前年 + 出生月日 时间所在的周一定一样吗？
week(时间，参数模式)
默认 0 周日是第一天 计算 从第一个周天开始 为1 前面的是0
模式 1 周一是第一天 前面天数小于4 不计算 0
select week(‘2000-1-5’,1),week(‘2015-1-5’,1),week(‘2018-1-5’);
select week(‘2018-1-5’);
select week(‘2015-1-5’,1);
select week(‘2022-1-5’,1);
– 不同年 同一个月和日 周数不一定相等
– 出生年月日 2000-10-25 40
– 2023-10-25 41
– week(出生日期) 周
– week(now()) 周
– 出生年月日 周 2023-月-日 周
select *
from tb_student
where week(concat(year(now()),mid(stu_date,5))) = week(now());
