09【多表查询案例】_数据库多表查询某人的基本情况和图书借阅情况的代码

于 2024-09-02 21:38:27 发布
阅读量852 收藏 7
点赞数 25
文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/2401_86437198/article/details/141831056
版权

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 表关系


![在这里插入图片描述](https://img-blog.csdnimg.cn/6b535f87bacb42e1b4a4612212941dc6.png#pic_center)


学生表对学校表：一对多


学生表对图书表：多对多


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


#### 查询结果：


![在这里插入图片描述](https://img-blog.csdnimg.cn/27ba004899a749c4ab4bec2f1fb4e157.png#pic_center)


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


#### 查询结果：


![在这里插入图片描述](https://img-blog.csdnimg.cn/c4d1e25e26ec4e55948ad55761f117fc.png#pic_center)


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


#### 查询结果


![在这里插入图片描述](https://img-blog.csdnimg.cn/87c7265f578b443488f9a04a250c065b.png#pic_center)


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


![在这里插入图片描述](https://img-blog.csdnimg.cn/7669689bb6ad45d3a580e95c97d9a2f0.png#pic_center)




---


### 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%’;


#### 查询结果


![在这里插入图片描述](https://img-blog.csdnimg.cn/ecfa57151c2b49f782a3475678946ce8.png#pic_center)


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


#### 查询结果：


![在这里插入图片描述](https://img-blog.csdnimg.cn/be713d641a2c48e3a54392c730717f42.png#pic_center)


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


#### 查询结果


![在这里插入图片描述](https://img-blog.csdnimg.cn/09dbe12e82634fd8ba5da99e16a8f257.png#pic_center)
2401_86437198
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值