上一篇：08【子查询】
下一篇：10【DCL数据库控制语言】
文章目录
09【多表查询案例】
9.1 准备数据
9.1.1 SQL语句
-- 图书表
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`id` int(11) NOT NULL COMMENT '图书id',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '书名',
`price` decimal(10, 2) NULL DEFAULT NULL COMMENT '定价',
`publish` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '出版社',
`publish_date` datetime(0) NULL DEFAULT NULL COMMENT '出版日期',
PRIMARY KEY (`id`) USING BTREE
) ;
INSERT INTO `book` VALUES (1, 'Java入门到精通', 49.80, '机械工业出版社', '2020-10-28 00:00:00');
INSERT INTO `book` VALUES (2, '高性能MySQL', 68.90, '北京大学出版社', '2021-08-05 00:00:00');
INSERT INTO `book` VALUES (3, 'Java并发编程实战', 65.50, '电子工业出版社', '2010-07-06 00:00:00');
INSERT INTO `book` VALUES (4, '深入理解Java虚拟机', 88.90, '清华大学出版社', '2013-03-14 00:00:00');
INSERT INTO `book` VALUES (5, '图解TCP/IP', 76.90, '机械工业出版社', '2014-10-28 00:00:00');
-- 学校表
DROP TABLE IF EXISTS `university`;
CREATE TABLE `university` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学校id',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学校名称',
`location` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学校地址',
`short_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学校简称',
`info` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学校简介',
PRIMARY KEY (`id`) USING BTREE
) ;
INSERT INTO `university` VALUES (1, '中国科学技术大学', '合肥', '中科大', '世界一流大学');
INSERT INTO `university` VALUES (2, '西安交通大学', '西安', '西安交大', '世界知名高水平大学');
INSERT INTO `university` VALUES (3, '江西财经大学', '南昌', '江财', '中国一流大学');
INSERT INTO `university` VALUES (4, '华南农业大学', '广州', '华农', '中国一流大学');
INSERT INTO `university` VALUES (5, '长沙理工大学', '长沙', '长沙理工', '中国一流大学');
-- 学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL COMMENT 'id',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`age` int(11) NULL DEFAULT NULL COMMENT '年龄',
`sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '籍贯',
`u_id` int(11) NULL DEFAULT NULL COMMENT '学校id',
PRIMARY KEY (`id`) USING BTREE,
INDEX `school_s_id_fk`(`u_id`) USING BTREE,
CONSTRAINT `school_s_id_fk` FOREIGN KEY (`u_id`) REFERENCES `university` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ;
INSERT INTO `student` VALUES (1, '小明', 20, '男', '辽宁辽阳', 1);
INSERT INTO `student` VALUES (2, '小红', 22, '女', '山东威海', 3);
INSERT INTO `student` VALUES (3, '小军', 27, '男', '山西吕梁', 3);
INSERT INTO `student` VALUES (4, '小龙', 24, '男', '河北保定', 2);
INSERT INTO `student` VALUES (5, '小丽', 22, '女', '陕西延安', 4);
INSERT INTO `student` VALUES (6, '小辉', 19, '男', '河南洛阳', 5);
-- 借阅表
DROP TABLE IF EXISTS `borrow`;
CREATE TABLE `borrow` (
`s_id` int(11) NOT NULL COMMENT '学生id',
`b_id` int(11) NULL DEFAULT NULL COMMENT '图书id',
`borrow_date` datetime(0) NULL DEFAULT NULL COMMENT '借阅日期',
`count` int(11) NULL DEFAULT NULL COMMENT '借阅数量',
INDEX `stu_s_id_fk`(`s_id`) USING BTREE,
INDEX `book_b_id_fk`(`b_id`) USING BTREE,
CONSTRAINT `book_b_id_fk` FOREIGN KEY (`b_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `stu_s_id_fk` FOREIGN KEY (`s_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ;
INSERT INTO `borrow` VALUES (1, 2, '2020-01-08 21:22:39', 1);
INSERT INTO `borrow` VALUES (3, 1, '2020-04-15 21:30:30', 2);
INSERT INTO `borrow` VALUES (3, 3, '2020-06-18 09:22:39', 2);
INSERT INTO `borrow` VALUES (2, 4, '2020-09-28 12:22:39', 3);
INSERT INTO `borrow` VALUES (4, 5, '2020-04-14 13:22:39', 2);
INSERT INTO `borrow` VALUES (5, 3, '2020-08-19 13:22:39', 1);
INSERT INTO `borrow` VALUES (6, 5, '2020-12-24 13:22:39', 2);
9.1.2 表关系
学生表对学校表：一对多
学生表对图书表：多对多
9.2 练习1
- 需求：查询所有学生信息。显示学生编号，学生姓名，籍贯，学校名称，学校简介
具体操作：
-
确定要查询哪些表：学生表和学校表
-
确定表连接条件：student.s_id=university.id
-
确定查询的字段：员工编号(student)，员工姓名(student)，籍贯(student)，学校名称(university)，学校简介(university)
-- 练习1：查询所有学生信息。显示员工编号，员工姓名，籍贯，学校名称，学校简称
-- 1. 确定查询哪些表：学生表，学校表
select * from student s inner join university u;
-- 2.确定表连接的条件
select * from student s inner join university u on s.u_id = u.id;
-- 3. 确定查询哪些列：员工编号，员工姓名，工资，职务名称，职务描述
select s.id 学生编号,s.name 学生姓名,s.address 籍贯,u.name 学校名称,u.info 学校简介 from student s
inner join university u on s.u_id = u.id;
查询结果：
9.3 练习2
- 需求：查询学校是’中国一流大学’的学生姓名、年龄、籍贯、学校名称
具体操作：
- 确定要查询哪些表：学生表和学校表
- 确定表连接条件：s.s_id=u.id
- 确定查询添加：学校是’中国一流大学’
- 确定查询字段：学生姓名、年龄、籍贯、学校名称
-- 查询学校是'中国一流大学'的学生姓名、年龄、籍贯、学校名称
-- 1.确定查询哪些表：学生表和学校表
select * from student s inner join university u;
-- 2. 确定表连接条件：s.s_id=u.id
select * from student s inner join university u on s.u_id = u.id;
-- 2. 确定表连接条件：s.s_id=u.id
select * from student s inner join university u on s.u_id = u.id where u.name='中国一流大学';
-- 3. 查询哪些列：学生姓名、年龄、籍贯、学校名称
select s.name 姓名,s.age 年龄,s.address 籍贯,u.name 学校名称 from student s
inner join university u on s.u_id = u.id
where u.info='中国一流大学';
查询结果：
9.4 练习3
- 需求：查询所有学生借的书的名称、单价、以及学生id、姓名、籍贯
具体操作：
- 确定要查询哪些表：学生表、学生借阅表、图书表
- 确定表连接条件：
- 学生关联借阅表：student.id=borrow.s_id
- 再关联图书表：borrow.b_id=book.id
- 确定查询的字段：学生id、姓名、籍贯、书名、单价
-- 方式1：先连接4张表，再通过on指定所有的条件
-- 1. 查询4张表并确定连接条件
select * from student s
inner join borrow bo on s.id=bo.s_id
inner join book b on b.id=bo.b_id;
-- 2. 确定查询的字段
select s.id 学生id,s.name 姓名,s.address 籍贯,b.name 书名,b.price 单价 from student s
inner join borrow bo on s.id=bo.s_id
inner join book b on b.id=bo.b_id;
查询结果
9.5 练习4
- 需求：查询借书数量最高的学生详情
具体操作：
- 确定要查询哪些表：学生表、借阅表
- 确定关联条件：student.id=borrow.s_id
- 根据学生id进行分组，求出每个学生的总借阅数量，查询学生信息、学生借阅总数
- 根据条件总借阅数量倒叙排序
- 只要第一条数据
-- 1. 确定要查询哪些表并且确定连接条件
select * from student s inner join borrow bo on s.id=bo.s_id;
-- 2. 根据学生id进行分组，求出每个学生的总借阅数量
select s.*,sum(bo.count) 借阅总数 from
student s inner join borrow bo on s.id=bo.s_id
group by s.id;
-- 3. 根据条件总借阅数量倒叙排序并且只要第一条数据
select s.*,sum(bo.count) 借阅总数 from
student s inner join borrow bo on s.id=bo.s_id
group by s.id
order by sum(bo.count) desc
limit 1;
9.6 练习5
- 需求：查询学校是”中国一流大学”的学生借书情况，只查询Java相关书籍的记录，查询学生id、姓名、籍贯、书籍名称、出版社、借阅数量、借阅时间、学校名称、学校地址
具体操作：
- 确定要查询哪些表：学生表、学生借阅表、图书表、学校表
- 确定表连接条件：
- 学生关联借阅表：student.id=borrow.s_id
- 再关联图书表：borrow.b_id=book.id
- 在关联学校表：university.id=student.u_id
- 确定查询条件：
university.info='中国一流大学'并且book.name like '%Java%' - 确定查询的字段：学生id、姓名、籍贯、书籍名称、出版社、借阅数量、借阅时间、学校名称、学校地址
-- 1. 确定要查询哪些表并且确定连接条件
select * from student s
inner join borrow bo on s.id=bo.s_id
inner join book b on b.id=bo.b_id
inner join university u on u.id=s.u_id;
-- 2.确定查询条件
select * from student s
inner join borrow bo on s.id=bo.s_id
inner join book b on b.id=bo.b_id
inner join university u on u.id=s.u_id
where u.info='中国一流大学' and b.name like '%Java%';
-- 3.确定查询的字段
select
s.id 学生id,s.name 姓名,s.address 籍贯,b.name 书籍名称,b.publish 出版社,bo.count 借阅数量,
bo.borrow_date 借阅时间,u.name 学校名称,u.location 学校地址
from student s
inner join borrow bo on s.id=bo.s_id
inner join book b on b.id=bo.b_id
inner join university u on u.id=s.u_id
where u.info='中国一流大学' and b.name like '%Java%';
查询结果
9.7 练习6
需求：统计每个大学共借书多少本
具体操作：
- 确定要查询哪些表：学校表、学生表、借阅表
- 确定表连接条件：
- 学校表关联学生表：university.id=student.u_id
- 学生表关联借阅表：student.id=borrow.s_id
- 根据学校名称进行分组，统计借阅表中的借阅数量
select u.name 大学名称,sum(bo.count) 借阅数量 from university u
inner join student s on u.id=s.u_id
inner join borrow bo on bo.s_id=s.id
group by u.name;
查询结果：
9.8 练习7
需求：查询借阅数量大于等于2的大学借阅数量
具体操作：
- 确定要查询哪些表：university、student、borrow
- 确定表连接条件：
- 学校表关联学生表：university.id=student.u_id
- 学生表关联借阅表：student.id=bo.s_id
- 根据学校名称进行分组，统计借阅表中的借阅数量
- 统计之后在统计结果中进行筛选，只要借阅数量大于等于2的大学借阅数量
select u.name 大学名称,sum(bo.count) 借阅数量 from university u
inner join student s on u.id=s.u_id
inner join borrow bo on bo.s_id=s.id
group by u.name having sum(bo.count)>=2;
查询结果
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/131702.html
