[mysql数据库学习] 四 mysql查询语句整理基于部门和员工表新手必看（进阶）附MySQL8窗口函数

最新推荐文章于 2024-06-23 10:00:00 发布

许愿明天过一面！

最新推荐文章于 2024-06-23 10:00:00 发布

阅读量1k

点赞数 3

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_44753691/article/details/117752809

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

mysql查询语句练习集合 - 基于部门和员工表

数据库hrs包含了 tb_dept，tb_emp 共两张表

1.数据库、表的建立及数据插入

每张表的具体意义请细读代码，这里也不赘述了QAQ

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)
);

-- alter table tb_emp add constraint pk_emp_eno primary key (eno);
-- alter table tb_emp add constraint uk_emp_ename unique (ename);
-- alter table tb_emp add constraint fk_emp_mgr foreign key (mgr) references tb_emp (eno);
-- alter table tb_emp add constraint fk_emp_dno foreign key (dno) references tb_dept (dno);

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);

2.msql查询语句编写及分析

查询月薪最高的员工姓名和月薪（子查询）

use hrs;
select ename, sal from tb_emp where sal=(select max(sal) from tb_emp);

select ename, sal from tb_emp where sal>=all(select sal from tb_emp);

另类思路：可以不用子查询

select ename, sal 
from tb_emp 
order by sal desc
limit 1;

查询员工的姓名和年薪((月薪+补贴)*13)（数据运算）

select 
	ename, (sal+ifnull(comm,0))*13 as ann_sal 
from tb_emp 
order by ann_sal desc;

查询有员工的部门的编号和人数（分组）

select dno, count(*) as total from tb_emp group by dno;

另类思路：

select distinct dno, count(*) over(partition by dno) as total from tb_emp;

查询所有部门的名称和人数（左外连接）

部门人数可能为空，但需要统计所有部门人数，所以部门表放左边

select 
	dname, ifnull(total,0) as total 
from tb_dept left join 
	(select dno, count(*) as total from tb_emp group by dno) tb_temp 
on tb_dept.dno=tb_temp.dno;

查询月薪最高的员工(Boss除外)的姓名和月薪（子查询）

查询出的最高月薪是一个值，所以可以用等号=。但仍建议用 in

select 
	ename, sal 
from tb_emp 
where sal in (
	select max(sal) from tb_emp where mgr is not null
);

查询月薪排第2名的员工的姓名和月薪（子查询、去重、排序）

力扣:176. 第二高的薪水点击查看

select ename, sal from tb_emp where sal=(
	select distinct sal from tb_emp order by sal desc limit 1,1
);

select ename, sal from tb_emp where sal=(
	select max(sal) from tb_emp where sal<(select max(sal) from tb_emp)
);

查询月薪超过平均月薪的员工的姓名和月薪（子查询、筛选）

select ename, sal from tb_emp where sal>(select avg(sal) from tb_emp);

查询月薪超过其所在部门平均月薪的员工的姓名、部门编号和月薪（连接查询、分组、筛选）

员工表中有部门号和薪水。先根据部门号分组查询到每个部门的平均工资及部门号，作为临时表；然后通过部门号连接员工表，再筛选工资条件即可。

select 
	ename, t1.dno, sal 
from tb_emp t1 inner join 
	(select dno, avg(sal) as avg_sal from tb_emp group by dno) t2
on t1.dno=t2.dno and sal>avg_sal;

查询部门中月薪最高的人姓名、月薪和所在部门名称（子查询，连接查询，筛选）

先分组查询找到每个部门的最高月薪及部门号作为临时表，在和员工表、部门表连接，最后筛选

select 
	ename, sal, dname 
from 
	tb_emp as t1, 
	tb_dept as t2,
	(select dno, max(sal) as max_sal from tb_emp group by dno) as t3
where t1.dno=t2.dno and t1.dno=t3.dno and sal=max_sal;

查询主管的姓名和职位

提示：尽量少用in/not in运算，尽量少用distinct操作，因为耗时
可以使用存在性判断（exists/not exists）替代集合运算和去重操作
当一个sql语句中既有distinct操作又有in/not in运算，考虑更换为exists/not exists

select ename, job from tb_emp where eno in (
	select distinct mgr from tb_emp where mgr is not null
);

select ename, job from tb_emp where eno=any(
	select distinct mgr from tb_emp where mgr is not null
);

如果一个员工的mgr字段为不为空，则这个数据是一个主管的工号。所以我们需要做的是：拿到所有员工的非空的mgr字段信息，然后去重。既有distinct操作又有in运算---->考虑更换为exists
select 'x' from tb_emp t2;生成一张临时表，利用这个临时表，如果临时表中存在一个记录的主管工号是员工表中的某一个，则这个工号对应的是一个主管。

select ename, job from tb_emp t1 where exists (
	select 'x' from tb_emp t2 where t1.eno=t2.mgr
);

查询月薪排名4~6名的员工的排名、姓名和月薪（窗口函数）

海象运算符 := （横着看冒号是眼睛，等号是象牙。因为没有大耳朵所以不是“大象运算符”）
三种排名方法：先排序，
1. 依次编号
2. 遇见相同数据，排名相同，往后的排名是不连续的。比如有两个第一名，则第三个还是第三名
3. 遇见相同数据，排名相同，往后的排名是连续的。比如有两个第一名，则第三个是第二名
方式一
直接取第4，5，6名的员工信息。但是可能不符合要求。

 select ename, sal from tb_emp order by sal desc limit 3,3;

方式二
引入一个变量记录编号。也可能不符合要求。
先通过select @a:=0获取变量初值，然后按工资排序，并且每次取一行数据就让a自增，最后通过临时表t2中记录的编号拿到第4，5，6名的员工信息。

select 
	row_num, 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;

方式三：使用MySQL8的窗口函数
窗口函数主要用于解决TopN查询问题

MySQL8窗口函数：
- row_number() / 直接生成序号
- rank() /生成连续不等的排名
- dense_rank()生成连续可等的排名
  窗口函数不适合业务数据库，只适合做离线数据分析，因为会降低数据库使用性能。

用于对比结果

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 dense_ranking
from tb_emp;

可根据实际情况换用不同窗口函数，此处使用dense_rank()

select ename, sal, ranking from (
	select ename, sal, dense_rank() over (order by sal desc) as ranking from tb_emp
) tb_temp where ranking between 4 and 6;

查询每个部门月薪排前2名的员工姓名、月薪和部门编号（窗口函数）

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;



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;