多表查询的案例

已于 2023-04-07 16:11:15 修改
阅读量236 收藏
点赞数
分类专栏： MySQL 文章标签： java 数据库 mysql
于 2023-04-07 16:08:40 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_53547525/article/details/130009338
版权

准备数据

  • 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

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

  • 具体操作

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

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

        3）确定要查询的字段：员工编号(student)，员工姓名，籍贯，学校名称，学校简介

  • SQL语句
-- 练习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;
  • 查询结果

 练习2

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

  • 具体操作：

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

        2）确定表连接条件：s.s_id=u.id

        3）确定查询添加：学校是'中国一流大学'

        4）确定查询字段：学生姓名、年龄、籍贯、学校名称

  • SQL语句：
-- 查询学校是'中国一流大学'的学生姓名、年龄、籍贯、学校名称

-- 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='中国一流大学';
  • 查询结果： 

练习3

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

  • 具体操作：

        1）确定要查询哪些表：学生表、学生借阅表、图书表

        2）确定表连接条件：

                a. 学生关联借阅表：student.id=borrow.s_id

                b. 再关联图书表：borrow.b_id=book.id

         3）确定查询的字段：学生id、姓名、籍贯、书名、单价

  • SQL语句：
-- 方式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;
  • 查询结果：

 练习4

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

  • 具体操作：

        1）确定要查询哪些表：学生表、借阅表

        2）确定关联条件：student.id=borrow.s_id

        3）根据学生id进行分组，求出每个学生的总借阅数量，查询学生信息、学生借阅总数

        4）根据条件总借阅数量倒叙排序

        5）只要第一条数据

  • SQL语句：
-- 方式一

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

-- 方式二：子查询

select s.*,max(s.count) 借阅总数 from 
 (select s.*, sum(bo.count) count from student s 
  inner join borrow bo on s.id = bo.s_id group by s.id) s;
  • 查询结果：

 练习5

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

  • 具体操作：

        1）确定要查询哪些表：学生表、学生借阅表、图书表、学校表

        2）确定表连接条件：

                a. 学生关联借阅表：student.id=borrow.s_id

                b. 再关联图书表：borrow.b_id=book.id

                c. 在关联学校表：university.id=student.u_id

        3）确定查询条件：university.info='中国一流大学'并且book.name like '%Java%'

        4）确定查询的字段：学生id、姓名、籍贯、书籍名称、出版社、借阅数量、借阅时间、学

              校名称、学校地址

  • SQL语句
-- 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%';
  • 查询结果：

 练习6

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

  • 具体操作：

        1）确定要查询哪些表：学校表、学生表、借阅表

        2）确定表连接条件：

                a. 学校表关联学生表：university.id=student.u_id

                b. 学生表关联借阅表：student.id=borrow.s_id

        3）根据学校名称进行分组，统计借阅表中的借阅数量

  • SQL语句：
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;
  •  查询结果：

练习7

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

  • 具体操作：

        1）确定要查询哪些表：university、student、borrow

        2）确定表连接条件：

                a. 学校表关联学生表：university.id=student.u_id

                b. 学生表关联借阅表：student.id=bo.s_id

        3）根据学校名称进行分组，统计借阅表中的借阅数量

        4）统计之后在统计结果中进行筛选，只要借阅数量大于等于2的大学借阅数量

  • SQL语句：
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;
  • 查询结果：

燕君澜
关注
专栏目录
多表查询案例（例题+答案）
qq_43119297的博客
09-09 5845
– 创建book表格 CREATE TABLE book( bookID VARCHAR(20) PRIMARY KEY , bookName VARCHAR(50) DEFAULT NULL ); – 插入数据 INSERT INTO book VALUES (1,'java基础'), (2,'javaweb'), (3,'JDBC'), (...
[MySQL]学生表、教师表、课程表、授课表、成绩表的多表查询案例(2)
qq_55898300的博客
05-10 5680
多表查询环境：已有5个表，student为学生表，teacher为教师表,teaching为授课表,course为课程表，sc为成绩表。 (1) 查询“李勇”同学所选课程的成绩。 SELECT degree FROM sc LEFT JOIN student s on s.sno = sc.sno WHERE sname = '李勇'; (2) 查询“李新”老师所授课程的课程名称。 SELECT cname FROM course LEFT OUTER JOIN teaching
多表查询
donjor的博客
10-13 143
带你入门多表查询 文章目录带你入门多表查询0.多表查询1.多表查询分类:2.交叉连接查询2.1清除笛卡尔积3.内连接查询3.1显示内连接3.2隐式内连接4.外连接查询4.1左外连接4.2右外连接5.子查询5.1子查询的结果是一个值的时候5.2子查询结果是单列多行的时候5.3子查询的结果是多行多列结论:多表查询的规律 0.多表查询 多表查询:同时查询多张表获取到需要的数据就是多表查询 1.多表查询分...
PTA：查询某人 (10 分)
weixin_47942875的博客
05-30 2043
7-13 查询某人 (10 分) 有一个人员数组，要在此数组中查询某个人是否存在，具体要求如下： 1.定义Person类： a 属性 name和age b 两个属性的getters 和setters c 带参数构造方法，为name和age给值 以及无参构造方法，给name赋值为none，age赋值为1 d ToString方法 public String toString() { return “Person [name=” + name + “, age=” + age + “]”; } 2.在main
数据库librarydb多表查询
weixin_74228197的博客
06-06 5473
5.查询库存表中每本书的条码、位置和借阅的读者编号。没有借阅的，读者编号用NULL表示。4.查询每个读者的借阅信息，包括读者姓名、书名、借阅日期、借阅状态。3.查询每个读者的姓名、单位、`可借天数`和可借数量。2.查询借阅状态为借阅的图书的书号和条码。1.查询张小东的基本情况和图书借阅情况。#全连接、内连接、右连接、左连接。
Mysql索引优化
我的博客
11-17 5390
mysql索引的优化
mysql library DB实操练习
2301_76916026的博客
06-20 1万+
0001 | 张小东 | 1 | 软件学院 | 有效 | 100005 | 321124 | 0001 | 2020-10-15 | NULL | 借阅 || 0001 | 张小东 | 1 | 软件学院 | 有效 | 100008 | 411111 | 0001 | 2020-09-25 | NULL | 借阅 |
多表查询案例
04-10
这是一个多表查询案例 希望能帮到初学者
12-多表查询案例-答案.sql
11-23
12-多表查询案例-答案.sql
Mybatis框架（多表查询典型案例）
06-30
Mybatis框架（多表查询典型案例）
实验三 SQL查询数据
sinb妃的博客
08-18 1750
SQL查询数据 一、实验目的 熟练掌握使用SQL查询语言。完成各类查询操作（单表查询，连接查询，嵌套查询，集合查询）。 二、实验原理 基于数据库系统概论，熟悉实验环境，熟悉SQL查询数据等本次实验的基本概念，了解SQL查询数据及其相关操作的语法。根据要求，编写相应的SQL代码，并运行、记录和分析结果，测试所编写代码是否满足步骤要求，完成实验。 三、使用仪器、材料 Oracle 11g，windows10； 四、实验步骤 现有一个单位内部的小型图书借阅系统，假设每本图书的数量无限制，并且可以借给任何单位成员，
数据库实验
新橙
07-12 6510
library实验前言题1、从USER表中查询所有用户的姓名和单位。2、查询所有图书的信息。3、查询单位为“计算机学院”的用户的全部信息。4、查询已经预约但还没有借出的书的信息。5、查询借书超过60天且还没有归还的借阅证号、书号。6、查询“清华大学出版社”出版的所有中文书的书名、作者、价格。7、查询在流通总库或者是属于计算机学院资料室的尚未借出的书。8、查询价格在30元到50元之间的书。9、查询2019年的借阅历史情况。！10、查询作者为兰苓、孙海涛、刘明编写的书的书名、作者、出版社和价格。11、查询书名包
MySQL数据库——图书管理系统中LibaryDB数据库中的查询
m0_65480095的博客
08-02 8277
（2）查询读者表中的姓名和单位列，显示的列标题改为姓名显示“name”，单位为“college”。select 姓名,读者表.读者编号,借阅表.条码,借阅日期,还书日期,借阅状态。where 借阅状态 = '借阅' and 库存状态 = '借出';（1）查询图书表中数量高于10本的图书的书名、数量和出版社。（3）查询图书表中每种书的书名和金额（金额=数量*单价）。（2）查询库存表中库存状态为“借出”的图书的信息。（2）查询借阅状态为“借阅”的图书的书号和条码。（1）查询“张小东”的基本情况和图书借阅情况。
【课程设计/毕业设计】python图书借阅管理系统源码+开发文档
疯狂屌丝程序员
03-22 3225
一直想做一款图书借阅管理系统，看了很多优秀的开源项目但是发现没有合适的。于是利用空闲休息时间开始自己写了一套管理系统。学习过程中遇到问题可以咨询留言。系统分为后台和前台两部分。图书管理：管理系统可以录入、修改和查询图书的基本信息，如图书名称、简介、备注等。类型管理：系统可以管理图书的类型信息，包括类型的名称等。标签管理：管理标签录入、修改和查询标签的信息。评论管理：管理和浏览整个网站的评论信息。借阅管理：管理和浏览整个网站的借阅信息。
查询读者的借阅信息mysql_MySQL数据库命令行【篇章九】之数据库综合查询(SELECT)...
热门推荐
weixin_34206129的博客
02-05 2万+
1．掌握SELECT语句的基本语法格式。2．掌握SELECT语句的执行方法。设有一个图书馆数据库，其中包括三个表，即图书表、读者表和借阅表。三个表的结构如下，根据要求，写出相应的SQL 命令。查询全部图书的图书号、书号、作者、出版社和单价。查询全体图书的信息，其中单价打8折，并设置该列的别名为“打折价”。显示所有借过书的借阅者的读者号、并去掉重复行。查询所有单价在20-30元之间的图书信息。查询所...
求助各位 拜托了
最新发布
2401_83835449的博客
05-10 218
5、查询库存表中每本书的条码、位置和借阅的读者编号，没有借阅的读者编号用null表示。4、查询每个读者的借阅信息，包括读者姓名、书名、借阅日期、借阅状态。3、查询每个读者的姓名、单位、可借天数和可借数量。2、查询借阅状态为“借阅”的图书的书号和条码。1、查询“张小东”的基本情况和图书借阅情况。
pta mysql训练题集（141-160）
daybreak_alonely的博客
05-18 7537
10-141 B1-8查询特定订单的详细信息 select a.CustomerID,CompanyName,a.OrderID,ProductID from orders as a, customers as b ,orderdetails as c where a.OrderID=c.OrderID and a.CustomerID=b.CustomerID and City='Madrid'; 10-142 查询图书表中售价介于50元到70元之间的图书的全部信息 select * fr
查询计算机系所有借阅过图书的学生姓名,第七讲      数据库的查询:多表连接查询...
weixin_33646989的博客
07-19 6515
SQL Server 2000第七讲数据库的查询:多表连接查询一、多表连接查询基本知识:p791.多表连接查询的概念：若一个查询同时涉及两个或两个以上的表，则称之为多表连接查询。连接查询可以对两个或两个以上的表进行查询，查询结果通常含有参加连接运算的两个表(或多个表)的指定列。即当用户所需要的数据涉及到两个或两个以上的表，那么就要对两个或两个以上的表先进行连接，然后再查询。例如：在xsbook库...
数据库实验三 SQL查询数据
qq_39792908的博客
05-11 1万+
实验三 SQL查询数据 实验目的 熟练掌握使用SQL查询语言。完成各类查询操作（单表查询，连接查询，嵌套查询，集合查询）。 实验内容 现有一个单位内部的小型图书借阅系统，假设每本图书的数量无限制，并且可以借给任何单位成员，每个单位成员可以借多本书，单位成员与图书的关系是多对多的关系。假设系统中仅有三个关系模式。 数据表结构 Reader表 属性名 ...
mybatis-plus多表查询案例
06-09
好的，下面是一个 Mybatis-Plus 多表查询的案例： 假设我们有两个表，一个是 `user`，另一个是 `order`。它们的结构分别如下： ``` user - id - name - age order - id - user_id - amount ``` 现在我们需要查询出所有用户及其对应的订单总金额，可以按照以下步骤进行： 1. 创建一个包含用户及订单总金额的 DTO 类： ```java @Data public class UserOrderDto { private Long id; private String name; private Integer age; private BigDecimal totalAmount; } ``` 2. 创建一个 Mapper 接口，并使用 `@Select` 注解编写 SQL 语句： ```java @Mapper public interface UserMapper extends BaseMapper<User> { @Select("SELECT user.id, user.name, user.age, SUM(order.amount) AS total_amount " + "FROM user " + "LEFT JOIN order ON user.id = order.user_id " + "GROUP BY user.id") List<UserOrderDto> selectUserOrderDtoList(); } ``` 3. 在 Service 中调用 Mapper 的方法即可： ```java @Service public class UserService { @Autowired private UserMapper userMapper; public List<UserOrderDto> getUserOrderDtoList() { return userMapper.selectUserOrderDtoList(); } } ``` 这样就可以查询出所有用户及其对应的订单总金额了。需要注意的是，这里使用了左连接（`LEFT JOIN`）来查询，这样即使用户没有订单，也能查询出来。同时，在 SQL 语句中使用了 `SUM` 函数来计算订单总金额。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值