day3 DQL语句与窗口函数
1. 知识点合计
- 嵌套查询
- 成员运算 in
- 多表链接
- 内连接：inner join 表 on
- 外连接：
- 内连接:（inner） join 只有满足的条件才会查出来 —> 无法查询到未选课的同学
- 外连接：左外连接left（outer）join、右外连接、全外连接（full join）（MySQL不支持）
- 左表：写join前的表
- 右表： 写在join后面的表
- 左外连接left (outer) join：把不满足连表条件的左表记录也能完成的查询出来，不满足条件地方填充null
- ifnull()：ifnull(a,b) 如果a为null则填充为b
- limit 和 offset
- limit 3 限制前三条记录
- limit 3 offset 6 跳过1-6条拿后面三条的记录 即789条件
- limit 5,3 = limit 3 offset 5 —> 跳过前5个拿3个调剂
-- 查询每个学生的学号和平均成绩(分组和聚合函数)
select sid,round(avg(score),2) from tb_record group by sid;
-- 查询平均成绩大于等于90分的学生的学号和平均成绩
-- 分组以前的数据筛选使用where子句，分组以后的数据筛选使用having子句
select
sid,
round((score),2) as 平均分
from tb_record
group by sid having 平均分 >=90;
-- 查询年龄最大的学生的姓名(子查询 / 嵌套查询)
-- 使用两次查询
-- 嵌套查询：一个查询的结果作为另一个查询的条件
select stu_name from tb_studnent where stu_birth =(
select min(stu_birth) from tb_student
);
-- 查询年龄最大的学生姓名和年龄(子查询+运算)
select
stu_name,
floor(datediff(curdate(),stu_birth)/365) as 年龄
from tb_student where stu_birth=(
select min(stu_birth) from tb_student
);
-- 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
-- 不能用等号 要用成员运算 in
select stu_name from tb_student where stu_id in(
select sid from tb_record group by sid having count(*) >2
);
-- 查询课程的名称、学分（课程表）和授课老师（老师表）的名字
-- 直接查询两张表 得到的是两个集合的笛卡尔积
select cou_name,cou_credit,tea_name
from tb_course,tb_teacher
where tb_course.tea_id = tb_teacher.tea_id; -- 课程表的老师编号和老师表的老师编号能对上才行
-- 法二 inner join 内连接
-- 给表别名不建议写as
select cou_name,cou_credit,tea_name from tb_course t1
inner join tb_teacher t2 on t1.tea_id=t2.tea_id;
-- 查询学生姓名(学生表)、课程名称（课程表）以及成绩(连接查询)
-- 越小的表 越靠右写 小表靠右（驱动表） 大表靠左
-- 学号和sid连上 课程号和cid连上
select stu_name,cou_name,score
from tb_student,tb_course,tb_record
where stu_id= sid and cou_id= cid and score is not null;
-- 法二 inner joint 表 on 条件
select stu_name,cou_name,score from tb_student
inner join tb_record on stu_id=sid
inner join tb_course on cou_id=cid
where score is not null;
-- 查询选课学生的姓名和平均成绩(子查询和连接查询)
-- 分组聚合建立了一个临时表 一定要给临时表别名
SELECT
stu_name, avg_score
FROM
tb_student,
(SELECT
sid, ROUND(AVG(score), 1) AS avg_score
FROM
tb_record
GROUP BY sid) tb_temp
WHERE
stu_id = sid;
-- 查询 每个 学生的姓名和选课数量(左外连接和子查询)
-- 没有选课的学生也要调出来
-- 内连接:（inner） join 只有满足的条件才会查出来 ---> 无法查询到未选课的同学
-- 外连接：左外连接left（outer）join、右外连接、全外连接（MySQL不支持）
-- 左表：写join前的表称 ---> tb_student/ 右表： 写在join后面的表 ---> tb_temp
-- 左外连接：把不满足连表条件的记录也能完成的查询出来，不满足条件地方填充null
-- ifnull(a,b) 如果a为null则填充为b
select
stu_name as 姓名,
ifnull(total,0) as 选课数量
from tb_student left outer join(
select sid, count(*) as total from tb_record group by sid) tb_temp
on stu_id=sid
order by 选课数量 desc limit 3 offset 6;
2. MySQL练习
2.1 知识点合集
-
any 和all
-
in not in 成员运算 性能不是很好
-
distinct 效率很低
-
尽量少用in / not in / distinct操作
-
可以考虑利用存在性判断(exists / not exists) 替代集合元素和去重操作
-
select ‘x’ (from dual) 查找常量 x写成y也行，写成啥都行
- from dual —> 伪表可省略
-
要查很后面的列
- 先把主键eno拿出来 主键的速度很快 然后用主键做条件
select ..., ... , from ... where eno=
(select eno from ..., order by ...limit 1 offset 1000000);
数据量很大的话就要分表分库
cobar —> MyCat —> 数据库中间件
- 连接池和链接监控
- 分表分库支持
- 负载均衡
2.2 练习
- 查询月薪最高的员工姓名和月薪(子查询)
use hrs;
SELECT
ename, sal
FROM
tb_emp
WHERE
sal = (SELECT
MAX(sal)
FROM
tb_emp);
-- 法二 all()集合 不让使用orderby 和 聚合函数的情况
SELECT
ename, sal
FROM
tb_emp
WHERE
sal >= ALL (SELECT
sal
FROM
tb_emp);
- 查询员工的姓名和年薪((月薪+补贴)*13)
注意：必须对null进行处理，由于null计算后也为空值，故年薪也会变成null
SELECT
ename, (sal + IFNULL(comm, 0)) * 13 AS ann_sal
FROM
tb_emp
ORDER BY ann_sal DESC;
-
查询有员工的部门的编号和人数
-
查询所有部门的名称和人数
-- 查询有员工的部门的编号和人数
select dno,count(*)as 人数 from tb_emp group by dno;
-- 查询所有部门的名称和人数
SELECT
dname, IFNULL(count1, 0)
FROM
tb_dept
LEFT JOIN
(SELECT
dno, COUNT(*) AS count1
FROM
tb_emp
GROUP BY dno) tb_temp ON tb_temp.dno = tb_dept.dno;
- 查询月薪最高的员工(Boss除外)的姓名和月薪
SELECT
ename, sal
FROM
tb_emp
ORDER BY sal DESC
LIMIT 1 OFFSET 1;
-- 法二
SELECT
ename, sal
FROM
tb_emp
WHERE
sal = (SELECT
MAX(sal)
FROM
tb_emp
WHERE
mgr IS NOT NULL);
-
查询月薪超过平均月薪的员工的姓名和月薪
-
查询月薪超过其所在部门平均月薪的员工的姓名、部门编号和月薪
-- 查询月薪超过其所在部门平均月薪的员工的姓名、部门编号和月薪
SELECT
ename, dno, sal
FROM
tb_emp,
(SELECT
AVG(sal) AS avgsal
FROM
tb_emp
GROUP BY dno) tb_temp
WHERE
sal >= avgsal;
-- 查询部门中月薪最高的人姓名、月薪和所在部门名称（三表联查）
-- 我的错误示范：
select
ename,
dno,
sal
from tb_emp,
(
select max(sal) as maxsal from tb_emp group by dno
) tb_temp
where sal =maxsal;
-- 结果错误 第二部分出出现了两个人 给出的是编号 不是名称
-- 虽然都是dno 但是未说明都是同样的意思
-- 正确结果：
select ename, sal, dname
from tb_emp t1,tb_dept t2,(
select dno, max(sal) as max_sal from tb_emp group by dno) t3
where t1.dno= t2.dno and t1.dno = t3.dno and sal= max_sal;
- 查询主管的姓名和职位
-- 查询主管的姓名和职位
select ename,job
from tb_emp
where job like '%主管%';
-- 不仅是职位中带有主管的就是主管
-- 只要员工编号出现在别人的主管编号中就是主管
select ename,job from tb_emp where eno= any(
select distinct mgr from tb_emp where mgr is not null
);
-- 法二
-- select 'x' (from dual) 查找常量 x写成y也行，写成啥都行
-- from dual ---> 伪表可省略
select ename, job from tb_emp t1 where exists(
select 'x' from tb_emp t2 where t1.eno=t2.mgr
);
3. 窗口函数
MySQL8 有窗口函数：row_num() / rank() /dense_rank() 效率也不高
窗口数据库不适合业务数据库，只适合离线数据分析
做业务的时候 不能用 用户体验很糟糕
-
MySQL8 有窗口函数：row_num() / rank() /dense_rank()
- rownum() 是独一无二的
- dense_rank 允许排序重复 不跳过数值 1 22 3
- ranking 允许排名重复 跳过数值 1 22 4
3.1 使用窗口函数
- 查询月薪排名4~6名的员工排名、姓名和月薪 (关键:排名)
-- 三种函数结果：
select
ename, sal,
row_number() over (order by sal desc) as row_num,
rank() over(order by sal desc) as ranking,
dense_rank() over (order by sal desc) as den_ranking
from tb_emp limit 3,3;
用row_num()是最好的，保证了唯一性也便于后续利用limit&offset取值。如果要求使用dense_rank()和rank()函数，则需要建立一个临时表再次排序。
-- 要求必须使用dense_rank
select
ename, sal,
dense_rank() over (order by sal desc) as den_ranking
from tb_emp limit 3,3; -- 这样得到的结果为 3 4 5
-- 取得时 第四条 第五条 第六条 但不代表是 第四五六名alter
-- 把结果做成临时表
select
ename, sal, ranking from(
select ename, sal, dense_rank() over (order by sal desc) as den_ranking from tb_emp
) tb_temp where ranking between 4 and 6;
3.2 不允许使用窗口函数
如果不允许使用窗口函数，则利用select @a:= @a+1 这种方式来人工添加行号，其中@a为用户自定义的变量a， :=称为海象运算符起到赋值的作用，同seta =0 含义相同。
- @a 用户自定义的变量a
- := 海象运算符 赋值 或者使用 set a =0
- select @a:= @a+1 利用这种方式 添加行号
SELECT
row_name, ename, sal
FROM
(SELECT
@a:=@a + 1 AS row_num, ename, sal
FROM
tb_emp, (SELECT @a:=0) t1
ORDER BY sal DESC) t2
WHERE
row_num BETWEEN 4 AND 6;
3.3 窗口函数语法
rank() over (partition by dno order by sal desc)
4. TopN问题
4.1 有窗口函数MySQL8.0
窗口函数主要用于解决TopN查询问题
- 例题：查询每个部门月薪排前2名的员工姓名和月薪
partition by 和group by
- 不能使用group by ，使用group up 分组后记录只有3条
- partition by 不会使记录变少，还会在每个组里面排名
select ename,sal,dno from(
select ename,sal,dno,rank() over (partition by dno order by sal desc) as ranking
from tb_emp) tb_temp where ranking<=2;
4.2 没有窗口函数 MySQL5.X
思路：同一个部门中，工资比我高的人不超过2个，我就是工资的前两名
select ename,sal,dno from tb_emp t1
where (select count(*) from tb_emp t2 where t1.dno=t2.dno and t2.sal>t1.sal) <2
order by dno asc, sal desc;
附录——导入数据
drop database if exists hrs;
create database hrs default charset utf8mb4;
use hrs;
create table tb_dept
(
dno int not null comment'编号',
dname varchar(10)not null comment '名称',
dloc varchar(20) not null comment '所在地',
primary key (dno)
);
insert into tb_dept values
(10,'会计部','北京'),
(20,'研发部','成都'),
(30,'销售部','重庆'),
(40,'运维部','深圳');
create table tb_emp
(
eno int not null comment '员工编号',
ename varchar(20) not null comment '员工姓名',
job varchar(20) not null comment '员工职位',
mgr int comment '主管编号',
sal int not null comment '员工月薪',
comm int comment '每月补贴',
dno int comment '所在部门编号',
primary key (eno),
foreign key (dno) references tb_dept(dno) ,
foreign key (mgr) references tb_emp(eno)
);
insert into tb_emp values
(7800,'张三丰', '总裁',null, 9000, 1200, 20),
(2056,'乔峰', '分析师', 7800,5000, 1500, 20),
(3088,'李莫愁','设计师', 2056, 3500, 800, 20),
(3211,'张无忌', '程序员', 2056, 3200,null, 20) ,
(3233,'丘处机','程序员',2056,3400, null, 20),
(3251,'张翠山', '程序员',2056,4000,null, 20),
(5566,'宋远桥', '会计师', 7800,4000,1000, 10),
(5234,'郭靖', '出纳', 5566, 2000, null, 10),
(3344,'黄蓉', '销售主管', 7800,3000,800, 30),
(1359,'胡一刀','销售员',3344,1800, 200, 30),
(4466,'苗人凤', '销售员', 3344,2500, null, 30),
(3244,'欧阳锋','程序员', 3088, 3200, null, 20) ,
(3577,'杨过','会计', 5566, 2200, null, 10),
(3588,'朱九真', '会计', 5566, 2500,null, 10);
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
