多表查询练习 24则

已于 2025-01-16 18:13:09 修改
阅读量436 收藏 6
点赞数 7
分类专栏： SQL 文章标签： java 数据库 服务器
于 2025-01-09 15:20:25 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_51921036/article/details/145034905
版权

数据准备

  • 创建 day08 数据库，在其中， 执行如下脚本，创建表结构，导入测试数据
-- 部门管理
create table tb_dept(
  id int unsigned primary key auto_increment comment '主键ID',
  name varchar(10) not null unique comment '部门名称',
  create_time datetime not null comment '创建时间',
  update_time datetime not null comment '修改时间'
) comment '部门表';

insert into tb_dept (id, name, create_time, update_time) 
        values(1,'学工部',now(),now()),
        (2,'教研部',now(),now()),
        (3,'咨询部',now(),now()), 
        (4,'就业部',now(),now()),
        (5,'人事部',now(),now());

-- 员工管理
create table tb_emp (
  id int unsigned primary key auto_increment comment 'ID',
  username varchar(20) not null unique comment '用户名',
  password varchar(32) default '123456' comment '密码',
  name varchar(10) not null comment '姓名',
  gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
  image varchar(300) comment '图像',
  job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
  entrydate date comment '入职时间',
  dept_id int unsigned comment '部门ID',
  salary int unsigned comment '工资',
  create_time datetime not null comment '创建时间',
  update_time datetime not null comment '修改时间'
) comment '员工表';

INSERT INTO tb_emp
    (id, username, password, name, gender, image, job, entrydate,dept_id,salary, create_time, update_time) VALUES
    (1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,20000,now(),now()),
    (2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,18000,now(),now()),
    (3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,16800,now(),now()),
    (4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,12000,now(),now()),
    (5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,9000,now(),now()),
    (6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,6000,now(),now()),
    (7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,6500,now(),now()),
    (8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,7200,now(),now()),
    (9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,5300,now(),now()),
    (10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,12000,now(),now()),
    (11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,8900,now(),now()),
    (12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,7800,now(),now()),
    (13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,6800,now(),now()),
    (14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,15800,now(),now()),
    (15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,11500,now(),now()),
    (16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2007-01-01',2,8300,now(),now()),
    (17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,4500,now(),now());


-- 薪资等级表
create table tb_salgrade(
  grade int comment '等级',
  losal int comment '该等级最低薪资',
  hisal int comment '该等级最高薪资'
) comment '薪资等级表';

insert into tb_salgrade values (1,0,3000);
insert into tb_salgrade values (2,3001,5000);
insert into tb_salgrade values (3,5001,8000);
insert into tb_salgrade values (4,8001,10000);
insert into tb_salgrade values (5,10001,15000);
insert into tb_salgrade values (6,15001,20000);
insert into tb_salgrade values (7,20001,25000);
insert into tb_salgrade values (8,25001,30000);

需求：

-- 1. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)
SELECT e.name '姓名', d.name '部门名称'
FROM tb_dept d, tb_emp e 
WHERE e.dept_id = d.id;

隐式内连接

tb_dept和tb_emp是要连接的两个表，WHERE语句后的e.dept_id = d.id是两个表连接的条件，用于指定这两个表应该如何匹配。

-- 2. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)
SELECT e.name '姓名', d.name '部门名称'
FROM tb_emp e JOIN tb_dept d 
ON e.dept_id = d.id;

显示内连接

使用 INNER JOIN 关键字对两个表进行连接，使用ON关键字指定连接的条件

-- 3. 查询员工的 姓名、性别、职位、部门名称 （隐式内连接）
SELECT e.name, e.gender, e.job, d.name
FROM tb_emp e , tb_dept d
WHERE e.dept_id = d.id;

-- 4. 查询 薪资 高于 10000 的员工的姓名、性别、职位、部门名称（显式内连接）
SELECT e.name, e.gender, e.job, d.name
FROM tb_emp e JOIN tb_dept d
ON e.dept_id = d.id 
WHERE e.salary > 10000;

-- 5. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
SELECT e.name,d.name
FROM tb_emp e LEFT JOIN tb_dept d
ON e.dept_id = d.id;

-- 6. 查询员工表 所有 员工的姓名, 和对应的部门名称 (右外连接)
SELECT e.name,d.name
FROM tb_dept d RIGHT JOIN tb_emp e
ON e.dept_id = d.id;


-- 7. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
SELECT e.name,d.name
FROM tb_emp e RIGHT JOIN tb_dept d
ON e.dept_id = d.id;


-- 8. 查询 "教研部" 的所有员工信息 （标量子查询）
SELECT *
FROM tb_emp 
WHERE dept_id =
(SELECT id FROM tb_dept WHERE name = '教研部');

标量子查询返回单个值，即一行一列的数据。

-- 9. 查询在 "方东白" 入职之后的员工信息 （标量子查询）
SELECT *
FROM tb_emp 
WHERE entrydate >
(SELECT entrydate FROM tb_emp WHERE name = '方东白');

-- 10. 查询 "教研部" 和 "咨询部" 的所有员工信息 （列子查询）
SELECT *
FROM tb_emp 
WHERE dept_id 
IN (SELECT id FROM tb_dept WHERE name = '教研部' OR name = '咨询部' );

SELECT * 
FROM tb_emp 
WHERE dept_id 
IN ( SELECT id FROM tb_dept WHERE name IN ( '教研部', '咨询部' ) );

-- 11. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 （行子查询）
SELECT *
FROM tb_emp 
WHERE (entrydate, job) 
IN (SELECT entrydate, job FROM tb_emp WHERE name = '韦一笑' );

-- 12. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息 （表子查询）
SELECT * FROM 
	(SELECT *
	FROM tb_emp 
	WHERE entrydate > '2006-01-01' ) a,
tb_dept d 
WHERE a.dept_id = d.id;

-- 13. 查询 拥有员工的 部门ID、部门名称 (没有员工的部门无需展示)
SELECT d.id, d.name
FROM tb_dept d, tb_emp e 
WHERE d.id = e.dept_id;

(没有员工的部门无需展示)

使用了隐式内连接，匹配条件为部门id，只有匹配上员工的部门才给予展示

-- 14. 查询所有 在 2010-01-01 之后入职的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
SELECT e.*, d.name
FROM tb_emp e LEFT JOIN tb_dept d 
ON e.dept_id = d.id
WHERE e.entrydate > '2010-01-01';

如果员工没有分配部门, 也需要展示出来

使用左连接，保证左边emp表的内容即使没有匹配也会全部展示出来

-- 15. 查询 "教研部" 员工的平均工资
SELECT avg(salary) 
FROM tb_emp 
WHERE dept_id = (SELECT id FROM tb_dept WHERE name = '教研部');

聚合函数AVG(column_name)返回数值列的平均值

-- 16. 查询工资比 "俞莲舟" 高的员工信息。
SELECT *
FROM tb_emp
WHERE salary > 
	(SELECT salary FROM tb_emp WHERE name = '俞莲舟');

-- 17. 查询 工资 比该企业员工的平均薪资 还要高的员工信息
SELECT *
FROM tb_emp 
WHERE salary > 
	(SELECT avg(salary) FROM tb_emp);

-- 18. 查询所有的部门信息, 并统计部门的员工人数
SELECT d.*, count(e.id) 
FROM tb_emp e RIGHT JOIN tb_dept d  
ON e.dept_id = d.id
GROUP BY d.id;

查询所有的部门信息

右连接部门表，输出所有的部门信息，以部门id为连接条件

统计部门的员工人数

count()聚合函数，传入员工id参数计算总共有几行，用GROUP BY关键字指定以部门id为分组条件

-- 19. 查询所有员工的 姓名, 工资 , 及 工资等级 (有难度的哦)
SELECT e.name, e.salary, s.grade
FROM tb_emp e, tb_salgrade s
WHERE e.salary BETWEEN s.losal AND s.hisal;

BETWEEN关键字用来检测是否在这两个值之间

-- 20. 查询 "教研部" 所有员工的信息 及 工资等级 (有难度的哦)
SELECT e.*, s.grade
FROM tb_emp e, tb_dept d, tb_salgrade s 
WHERE 
e.dept_id = d.id
AND
d.name = '教研部'
AND
salary BETWEEN s.losal AND s.hisal;

SELECT e.*, s.grade
FROM tb_emp e
INNER JOIN tb_dept d ON e.dept_id = d.id
INNER JOIN tb_salgrade s ON e.salary BETWEEN s.losal AND s.hisal
WHERE d.name = '教研部';

显式内连接可以提高可读性和避免潜在的列名冲突

-- 21. 查询 工资 低于 本部门平均工资的员工信息 (有难度的哦)
SELECT e.*, avgsal
FROM tb_emp e, (SELECT dept_id, avg(salary) avgsal FROM tb_emp GROUP BY dept_id) a
WHERE 
e.dept_id = a.dept_id
AND
e.salary < a.avgsal;

子查询返回一个包含部门id和部门平均工资的、以部门id分类打包的结果集

部门id是用来充当两表连接条件

**低于 本部门平均工资 **

新表的平均工资则根据部门id匹配到对应部门的员工，与其工资进行比较

-- 22. 列出所有部门的详细信息(包括部门ID, 部门名称)和人数
SELECT d.id, d.name, count(e.id)
FROM tb_dept d LEFT JOIN tb_emp e
ON d.id = e.dept_id
GROUP BY d.id;

所有部门

左连接部门，显示部门表的全部行

-- 23、取得每个薪资等级有多少员工  (有难度的哦)
SELECT s.grade, count(e.id)
FROM tb_emp e RIGHT JOIN tb_salgrade s
ON e.salary BETWEEN s.losal AND s.hisal
GROUP BY s.grade

每个薪资等级

右连接薪资等级表，以s.grade进行分组，聚合函数count(e.id)计算每个等级的员工数

-- 24. 取得每个部门最高薪水的人员名称
SELECT e.name, e.dept_id, e.salary
FROM tb_emp e,
(SELECT dept_id, max(salary) max_salary
FROM tb_emp GROUP BY dept_id) a 
WHERE e.dept_id = a.dept_id
AND e.salary = a.max_salary
ORDER BY dept_id ASC;

子查询得到一张结果为部门id和每个部门最高薪资的表，以部门id分组

以部门id与原emp表连接，并且添加薪水匹配的条件

返回 名字 部门 薪水

结果再以部门id进行升序排序

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值