知识文档站

Appearance

多表查询练习（必做）

第一组 ​

导入测试数据 ​

sql
-- 部门管理
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. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)


-- 2. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)


-- 3. 查询员工的 姓名、性别、职位、部门名称 （隐式内连接）


-- 4. 查询 薪资 高于 10000 的员工的姓名、性别、职位、部门名称（显式内连接）


-- 5. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)


-- 6. 查询员工表 所有 员工的姓名, 和对应的部门名称 (右外连接)


-- 7. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)


-- 8. 查询 "教研部" 的所有员工信息 （标量子查询）


-- 9. 查询在 "方东白" 入职之后的员工信息 （标量子查询）


-- 10. 查询 "教研部" 和 "咨询部" 的所有员工信息 （列子查询）


-- 11. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 （行子查询）


-- 12. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息 （表子查询）


-- 13. 查询 拥有员工的 部门ID、部门名称 (没有员工的部门无需展示)


-- 14. 查询所有 在 2010-01-01 之后入职的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来


-- 15. 查询 "教研部" 员工的平均工资


-- 16. 查询工资比 "俞莲舟" 高的员工信息。


-- 17. 查询 工资 比该企业员工的平均薪资 还要高的员工信息


-- 18. 查询所有的部门信息, 并统计部门的员工人数


-- 19. 查询所有员工的 姓名, 工资 , 及 工资等级 (有难度的哦)


-- 20. 查询 "教研部" 所有员工的信息 及 工资等级 (有难度的哦)


-- 21. 查询 工资 低于 本部门平均工资的员工信息 (有难度的哦)


-- 22. 列出所有部门的详细信息(包括部门ID, 部门名称)和人数


-- 23、取得每个薪资等级有多少员工  (有难度的哦)


-- 24. 取得每个部门最高薪水的人员名称

练习答案 ​

sql
-- 1. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)
select emp.name, dept.name from tb_emp emp, tb_dept dept where emp.dept_id = dept.id;

-- 2. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)
select emp.name, dept.name from tb_emp emp inner join tb_dept dept on emp.dept_id = dept.id;

-- 3. 查询员工的 姓名、性别、职位、部门名称 （隐式内连接）
select emp.name, emp.gender, emp.job, dept.name from tb_emp emp, tb_dept dept where emp.dept_id = dept.id;

-- 4. 查询 薪资 高于 10000 的员工的姓名、性别、职位、部门名称（显式内连接）
select emp.name, emp.gender, emp.job, dept.name from tb_emp emp join tb_dept dept on emp.dept_id = dept.id where emp.salary > 10000;

-- 5. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
select emp.name, dept.name from tb_emp emp left join tb_dept dept on emp.dept_id = dept.id;

-- 6. 查询员工表 所有 员工的姓名, 和对应的部门名称 (右外连接)
select emp.name, dept.name from tb_dept dept right join tb_emp emp on emp.dept_id = dept.id;

-- 7. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
select dept.name, emp.name from tb_emp emp right join tb_dept dept on emp.dept_id = dept.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 in ('教研部', '咨询部'));

-- 11. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 （行子查询）
select * from tb_emp where (job, entrydate) in (select job, entrydate 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;

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

-- 15. 查询 "教研部" 员工的平均工资
select avg(salary) from tb_emp where dept_id = (select id from tb_dept where 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.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;

-- 19. 查询所有员工的 姓名, 工资 , 及 工资等级 (有难度的哦)
select e.name, e.salary, s.grade from tb_emp e, tb_salgrade s where e.salary between s.losal and s.hisal;

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

-- 21. 查询 工资 低于 本部门平均工资的员工信息 (有难度的哦)
select e.*, a.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;

-- 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, tb_salgrade s where e.salary between s.losal and s.hisal group by s.grade;

-- 24. 取得每个部门最高薪水的人员名称
-- a. 查询每个部门薪资最高的人
select dept_id, max(salary) maxsalary from tb_emp group by dept_id;
-- b. 取得每个部门最高薪水的人员名称
select e.name, e.salary, e.dept_id from tb_emp e, (select dept_id, max(salary) maxsalary from tb_emp group by dept_id) a where e.dept_id = a.dept_id and e.salary = a.maxsalary;

第二组 ​

导入测试数据 ​

sql
/*
Navicat MySQL Data Transfer

Source Server         : 李福琳106
Source Server Version : 50528
Source Host           : localhost:3306
Source Database       : librarydb

Target Server Type    : MYSQL
Target Server Version : 50528
File Encoding         : 65001

Date: 2024-11-05 14:27:15
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
  `bid` char(8) NOT NULL,
  `bname` varchar(50) DEFAULT NULL,
  `author` char(50) DEFAULT NULL,
  `publisher` char(50) DEFAULT NULL,
  `publishdate` date DEFAULT NULL,
  `price` float DEFAULT NULL,
  `total` int(11) DEFAULT NULL,
  `position` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES ('00539040', '大学物理辅导', '吕金钟', '清华大学出版社', '2020-03-01', '52', '18', '一层C-18-4');
INSERT INTO `book` VALUES ('00551060', 'PLC应用技术', '黄中玉', '人民邮电出版社', '2018-09-01', '42', '10', '一层E-12-5');
INSERT INTO `book` VALUES ('00632333', '数学分析习题演练', '周民强', '科学出版社', '2020-01-01', '65', '10', '一层J-6-1');
INSERT INTO `book` VALUES ('00868171', '物理学中的群论基础', '徐建军', '清华大学出版社', '2010-09-01', '287.8', '3', '一层D-5-6');
INSERT INTO `book` VALUES ('01059432', 'FPGA设计', '张义和', '科学出版社', '2013-07-01', '52', '9', '一层D-1-3');
INSERT INTO `book` VALUES ('01086319', '空间信息数据库', '牛新征', '人民邮电出版社', '2014-04-01', '65', '6', '二层A-4-1');
INSERT INTO `book` VALUES ('01244785', 'MATLAB科学计算', '温正', '清华大学出版社', '2017-08-01', '99', '4', '二层C-12-2');
INSERT INTO `book` VALUES ('01257680', 'SQL进阶教程', 'MICK', '人民邮电出版社', '2017-11-01', '79', '8', '二层B-8-2');
INSERT INTO `book` VALUES ('01315502', 'MySQL数据库管理实战', '甘长春', '人民邮电出版社', '2019-04-01', '99', '8', '二层B-10-1');
INSERT INTO `book` VALUES ('01331088', '大数据技术基础', '薛志东', '人民邮电出版社', '2018-08-01', '55', '12', '二层F-3-4');

-- ----------------------------
-- Table structure for borrow
-- ----------------------------
DROP TABLE IF EXISTS `borrow`;
CREATE TABLE `borrow` (
  `rid` varchar(12) NOT NULL,
  `bid` char(8) NOT NULL,
  `borrowtime` date DEFAULT NULL,
  `returntime` date DEFAULT NULL,
  PRIMARY KEY (`rid`,`bid`),
  KEY `bid` (`bid`),
  CONSTRAINT `borrow_ibfk_2` FOREIGN KEY (`bid`) REFERENCES `book` (`bid`) ON DELETE CASCADE,
  CONSTRAINT `borrow_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `reader` (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of borrow
-- ----------------------------
INSERT INTO `borrow` VALUES ('10100391', '01086319', '2022-06-03', '2022-07-10');
INSERT INTO `borrow` VALUES ('10100391', '01331088', '2022-06-03', null);
INSERT INTO `borrow` VALUES ('10501561', '00868171', '2022-03-01', '2022-04-28');
INSERT INTO `borrow` VALUES ('10501561', '01086319', '2022-03-01', '2022-04-28');
INSERT INTO `borrow` VALUES ('10501561', '01244785', '2022-06-30', null);
INSERT INTO `borrow` VALUES ('10501561', '01315502', '2022-03-18', '2022-04-28');
INSERT INTO `borrow` VALUES ('10501561', '01331088', '2022-06-30', null);
INSERT INTO `borrow` VALUES ('120211010103', '00539040', '2022-03-15', '2022-04-27');
INSERT INTO `borrow` VALUES ('120211010103', '00632333', '2022-03-15', '2022-04-27');
INSERT INTO `borrow` VALUES ('120211010103', '01315502', '2022-04-10', null);
INSERT INTO `borrow` VALUES ('120211041129', '00539040', '2022-03-10', '2022-05-05');
INSERT INTO `borrow` VALUES ('120211041129', '00632333', '2022-03-10', '2022-05-05');
INSERT INTO `borrow` VALUES ('120211041129', '01257680', '2022-07-01', null);
INSERT INTO `borrow` VALUES ('120211041129', '01315502', '2022-06-22', '2022-07-01');
INSERT INTO `borrow` VALUES ('120211070101', '00632333', '2022-04-06', '2022-07-03');

-- ----------------------------
-- Table structure for reader
-- ----------------------------
DROP TABLE IF EXISTS `reader`;
CREATE TABLE `reader` (
  `rid` varchar(12) NOT NULL,
  `rname` varchar(50) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `typeno` char(1) DEFAULT '1',
  `dept` varchar(50) DEFAULT NULL,
  `tel` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`rid`),
  KEY `typeno` (`typeno`),
  CONSTRAINT `reader_ibfk_1` FOREIGN KEY (`typeno`) REFERENCES `readertype` (`typeno`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of reader
-- ----------------------------
INSERT INTO `reader` VALUES ('10100391', '杨丽', '女', '1', '外国语学院', '92331458');
INSERT INTO `reader` VALUES ('10501561', '赵晓丽', '女', '1', '经济与管理学院', '92337521');
INSERT INTO `reader` VALUES ('120211010103', '宋洪博', '男', '2', '外国语学院', '81771211');
INSERT INTO `reader` VALUES ('120211041129', '侯明斌', '男', '2', '电气与电子工程学院', '81771234');
INSERT INTO `reader` VALUES ('120211070101', '李淑子', '女', '2', '数理学院', '81775643');
INSERT INTO `reader` VALUES ('120211070106', '刘丽', '女', '2', '数理学院', '81775644');

-- ----------------------------
-- Table structure for readertype
-- ----------------------------
DROP TABLE IF EXISTS `readertype`;
CREATE TABLE `readertype` (
  `typeno` char(1) NOT NULL,
  `typename` varchar(50) DEFAULT NULL,
  `max_quantity` tinyint(4) DEFAULT NULL,
  `max_days` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`typeno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of readertype
-- ----------------------------
INSERT INTO `readertype` VALUES ('1', '教师', '20', '90');
INSERT INTO `readertype` VALUES ('2', '学生', '15', '60');

多表查询练习 ​

-- (1)查询“人民邮电出版社”的相关图书信息，结果包含图书编号、书名、作者、出版社和定价。


-- (2)查询女性读者的相关信息，结果包含读者姓名、性别和所属院系。


-- (3)查询 2017年至2019年出版的相关图书信息，结果包含图书编号、书名、作者、出版日期和定价。


-- (4)查询图书定价打7折后的图书编号、书名和打折后价格。


-- (5)查询所有馆存图书的总类别数量和总库存数量。


-- (6)查询借阅的书名称中包含“数据”的读者的借阅信息，结果包含读者姓名和书名。


-- (7)查询读者为“教师”的借阅信息，结果包含读者姓名、书名和借阅日期。


-- (8)查询尚未还书的相关读者信息，结果包含读者编号、读者姓名、书名和借阅日期。


-- (9)查询每本图书的借阅次数，将结果按照借阅次数降序排列，结果包含图书编号、书名、借阅次数、作者和出版社。


-- (10)查询每个院系的借阅次数，将结果按照借阅次数降序排列，结果包含院系名称和借阅次数。


-- (11)查询借阅了书名为“大数据技术基础”的图书但尚未还书的相关读者信息，结果包含读者编号、读者姓名、书名和借阅日期。


-- (12)查询定价高于平均定价的相关图书信息，结果包含图书编号、书名、作者、出版社和定价。


-- (13)查询从未被读者借阅的相关图书信息，结果包含图书编号、书名、作者、出版社和定价。

练习答案 ​

sql
-- (1)查询“人民邮电出版社”的相关图书信息，结果包含图书编号、书名、作者、出版社和定价。
SELECT bid AS 图书编号, bname AS 书名, author AS 作者, publisher AS 出版社, price AS 定价
FROM book
WHERE publisher = '人民邮电出版社';

-- (2)查询女性读者的相关信息，结果包含读者姓名、性别和所属院系。
SELECT rname AS 读者姓名, sex AS 性别, dept AS 所属院系
FROM reader
WHERE sex = '女';

-- (3)查询 2017年至2019年出版的相关图书信息，结果包含图书编号、书名、作者、出版日期和定价。
SELECT bid AS 图书编号, bname AS 书名, author AS 作者, publishdate AS 出版日期, price AS 定价
FROM book
WHERE publishdate BETWEEN '2017-01-01' AND '2019-12-31';

-- (4)查询图书定价打7折后的图书编号、书名和打折后价格。
SELECT bid AS 图书编号, bname AS 书名, price * 0.7 AS 打折后价格
FROM book;

-- (5)查询所有馆存图书的总类别数量和总库存数量。
SELECT COUNT(DISTINCT position) AS 总类别数量, SUM(total) AS 总库存数量
FROM book;

-- (6)查询借阅的书名称中包含“数据”的读者的借阅信息，结果包含读者姓名和书名。
SELECT rname AS 读者姓名, bname AS 书名
FROM borrow
JOIN book ON borrow.bid = book.bid
JOIN reader ON borrow.rid = reader.rid
WHERE bname LIKE '%数据%';

-- (7)查询读者为“教师”的借阅信息，结果包含读者姓名、书名和借阅日期。
SELECT rname AS 读者姓名, bname AS 书名, borrowtime AS 借阅日期
FROM borrow
JOIN book ON borrow.bid = book.bid
JOIN reader ON borrow.rid = reader.rid
WHERE typeno = '教师';

-- (8)查询尚未还书的相关读者信息，结果包含读者编号、读者姓名、书名和借阅日期。
SELECT reader.rid AS 读者编号, rname AS 读者姓名, bname AS 书名, borrowtime AS 借阅日期
FROM borrow
JOIN book ON borrow.bid = book.bid
JOIN reader ON borrow.rid = reader.rid
WHERE returntime IS NULL;

-- (9)查询每本图书的借阅次数，将结果按照借阅次数降序排列，结果包含图书编号、书名、借阅次数、作者和出版社。
SELECT book.bid AS 图书编号, bname AS 书名, COUNT(*) AS 借阅次数, author AS 作者, publisher AS 出版社
FROM borrow
JOIN book ON borrow.bid = book.bid
GROUP BY book.bid
ORDER BY 借阅次数 DESC;


-- (10)查询每个院系的借阅次数，将结果按照借阅次数降序排列，结果包含院系名称和借阅次数。
SELECT dept AS 院系名称, COUNT(*) AS 借阅次数
FROM borrow
JOIN reader ON borrow.rid = reader.rid
GROUP BY dept
ORDER BY 借阅次数 DESC;

-- (11)查询借阅了书名为“大数据技术基础”的图书但尚未还书的相关读者信息，结果包含读者编号、读者姓名、书名和借阅日期。
SELECT reader.rid AS 读者编号, rname AS 读者姓名, bname AS 书名, borrowtime AS 借阅日期
FROM borrow
JOIN book ON borrow.bid = book.bid
JOIN reader ON borrow.rid = reader.rid
WHERE bname = '大数据技术基础' AND returntime IS NULL;

-- (12)查询定价高于平均定价的相关图书信息，结果包含图书编号、书名、作者、出版社和定价。
SELECT bid AS 图书编号, bname AS 书名, author AS 作者, publisher AS 出版社, price AS 定价
FROM book
WHERE price > (SELECT AVG(price) FROM book);

-- (13)查询从未被读者借阅的相关图书信息，结果包含图书编号、书名、作者、出版社和定价。
SELECT bid AS 图书编号, bname AS 书名, author AS 作者, publisher AS 出版社, price AS 定价
FROM book
WHERE bid NOT IN (SELECT DISTINCT bid FROM borrow);

Copyright © 2025 Jackson 凌 All Rights Reserved. | 粤ICP备2025441629号-1