• 09【多表查询案例】

    上一篇：08【子查询】


    下一篇：10【DCL数据库控制语言】

    目录：【MySQL零基础系列教程】

    文章目录

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77

    9.1.2 表关系

    在这里插入图片描述

    学生表对学校表：一对多

    学生表对图书表：多对多

    9.2 练习1

    • 需求：查询所有学生信息。显示学生编号，学生姓名，籍贯，学校名称，学校简介

    具体操作：

    1. 确定要查询哪些表：学生表和学校表

    2. 确定表连接条件：student.s_id=university.id

    3. 确定查询的字段：员工编号(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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    查询结果：

    在这里插入图片描述

    9.3 练习2

    • 需求：查询学校是’中国一流大学’的学生姓名、年龄、籍贯、学校名称

    具体操作：

    1. 确定要查询哪些表：学生表和学校表
    2. 确定表连接条件：s.s_id=u.id
    3. 确定查询添加：学校是’中国一流大学’
    4. 确定查询字段：学生姓名、年龄、籍贯、学校名称
    -- 查询学校是'中国一流大学'的学生姓名、年龄、籍贯、学校名称

-- 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='中国一流大学';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    查询结果：

    在这里插入图片描述

    9.4 练习3

    • 需求：查询所有学生借的书的名称、单价、以及学生id、姓名、籍贯

    具体操作：

    1. 确定要查询哪些表：学生表、学生借阅表、图书表
    2. 确定表连接条件：
      1. 学生关联借阅表：student.id=borrow.s_id
      2. 再关联图书表：borrow.b_id=book.id
    3. 确定查询的字段：学生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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    查询结果

    在这里插入图片描述

    9.5 练习4

    • 需求：查询借书数量最高的学生详情

    具体操作：

    1. 确定要查询哪些表：学生表、借阅表
    2. 确定关联条件：student.id=borrow.s_id
    3. 根据学生id进行分组，求出每个学生的总借阅数量，查询学生信息、学生借阅总数
    4. 根据条件总借阅数量倒叙排序
    5. 只要第一条数据
    -- 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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    在这里插入图片描述

    9.6 练习5

    • 需求：查询学校是"中国一流大学"的学生借书情况，只查询Java相关书籍的记录，查询学生id、姓名、籍贯、书籍名称、出版社、借阅数量、借阅时间、学校名称、学校地址

    具体操作：

    1. 确定要查询哪些表：学生表、学生借阅表、图书表、学校表
    2. 确定表连接条件：
      1. 学生关联借阅表：student.id=borrow.s_id
      2. 再关联图书表：borrow.b_id=book.id
      3. 在关联学校表：university.id=student.u_id
    3. 确定查询条件：university.info='中国一流大学' 并且book.name like '%Java%'
    4. 确定查询的字段：学生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%';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    查询结果

    在这里插入图片描述

    9.7 练习6

    需求：统计每个大学共借书多少本

    具体操作：

    1. 确定要查询哪些表：学校表、学生表、借阅表
    2. 确定表连接条件：
      1. 学校表关联学生表：university.id=student.u_id
      2. 学生表关联借阅表：student.id=borrow.s_id
    3. 根据学校名称进行分组，统计借阅表中的借阅数量
    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;

    • 1
    • 2
    • 3
    • 4

    查询结果：

    在这里插入图片描述

    9.8 练习7

    需求：查询借阅数量大于等于2的大学借阅数量

    具体操作：

    1. 确定要查询哪些表：university、student、borrow
    2. 确定表连接条件：
      1. 学校表关联学生表：university.id=student.u_id
      2. 学生表关联借阅表：student.id=bo.s_id
    3. 根据学校名称进行分组，统计借阅表中的借阅数量
    4. 统计之后在统计结果中进行筛选，只要借阅数量大于等于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;

    • 1
    • 2
    • 3
    • 4

    查询结果

    在这里插入图片描述

  • 相关阅读:
    【云原生】kubernetes中pod的生命周期、探测钩子的实战应用案例解析
    STM32 使用HAL库，HAL_Delay()会卡死, 程序一直卡在 HAL_GetTick( ) 函数中（已解决）
    SpringBoot事件发布监听
    JavaScript基础语法
    OWT Server进程结构和JS代码处理流程 [Open WebRTC Toolkit]
    ES6中数组新增了哪些扩展？
    Linux08——面试题篇
    【Python】Numpy排序函数详解
    如何提高社交产品的活跃度？
    Java代码基础算法练习-判断学生成绩等级-2024.06.28
  • 原文地址：https://blog.csdn.net/Bb15070047748/article/details/126564623