# DQL(多表)
mysql 中的难点莫过于其中的 多表查询
# 一、一对多查询
# 1、库表准备
准备一个一对多的 关系表。
一个 建筑(building)可以有多个 房间(rooms)
CREATE TABLE IF NOT EXISTS buildings (
building_no INT PRIMARY KEY AUTO_INCREMENT,
building_name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL
) COMMENT = '建筑表';
INSERT INTO buildings(building_name,address)
VALUES('海南大厦','海口市国兴大道1234号'),
('万达水城','海口市大同路1200号'),
('中山大厦', '海口市骑楼街56号');
CREATE TABLE IF NOT EXISTS rooms (
room_no INT PRIMARY KEY AUTO_INCREMENT,
room_name VARCHAR(255) NOT NULL,
building_no INT,
FOREIGN KEY (building_no) REFERENCES buildings (building_no) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = '房间表';
INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
('War Room',1),
('Office of CEO',1),
('Marketing',2),
('Lakeside Cottage',2),
('Serene Meadows',null),
('Country Charm',null),
('Mountain Chalet',null);
# 2、连接查询 🎈
说明
连接查询的作用是对为查询的 每条记录 追加更多 有关联的字段
详细内容
![](https://blog-cloudbed.oss-cn-chengdu.aliyuncs.com/note-taking/SQL_JOINS.png)
图示:
![](https://blog-cloudbed.oss-cn-chengdu.aliyuncs.com/note-taking/joins_in_mysql.png)
# 3、交叉连接
会产生一些毫无关联的数据,一般不用!
-- 笛卡尔积(3 * 8 = 24 条记录)
SELECT * FROM rooms, buildings;
SELECT * FROM buildings, rooms;
# 4、内连接 ✨
使用内连接时,我们也要注意 表的先后顺序,以便得到更好的查询结果
内连接可以筛选一些数据出来,但也会漏掉大量数据,比如:
rooms.building_no
为 null 的数据
内连接 和 where 条件 的区别
下面演示的 内连接 where 条件 两种方式查询的结果是一样的。
- 内连接:在两张表连接时约束数据间的关系,来决定之后的查询结果
- where 条件:是先得到笛卡尔积结果,然后在其数据基础上进行 where 条件的筛选。
- 等值连接
-- 只有相关联数据
-- 1、where筛选
SELECT * FROM rooms, buildings WHERE rooms.building_no = buildings.building_no; # 数据角度
-- SELECT * FROM buildings, rooms WHERE rooms.building_no = buildings.building_no; # 查看角度
-- 2、内连接约束
SELECT * FROM rooms JOIN buildings ON rooms.building_no = buildings.building_no;
-- SELECT * FROM rooms INNER JOIN buildings ON rooms.building_no = buildings.building_no;
-- SELECT * FROM rooms CROSS JOIN buildings ON rooms.building_no = buildings.building_no;
- 非等值连接
-- 略
# 5、外连接 ✨
左右表 与 外连接
其实在上面我们就可以发现:表的顺序不同,查询结果展示的数据顺序也不同。
- 左连接
在整个连接查询中,可能也就 左连接 用得最多了。
-- 相关联数据 + rooms 表中未关联数据
SELECT * FROM rooms LEFT JOIN buildings ON rooms.building_no = buildings.building_no;
-- rooms 表中未关联数据
SELECT * FROM rooms LEFT JOIN buildings ON rooms.building_no = buildings.building_no WHERE rooms.building_no IS NULL;
- 右连接
右连接用的不多,意义不大。
-- 略
# 其他
- 全连接
mysql 是不支持全连接的。
但我们可以通过 UNION 得到 全连接一样的查询结果
(SELECT * FROM rooms LEFT JOIN buildings ON rooms.building_no = buildings.building_no)
UNION
(SELECT * FROM rooms RIGHT JOIN buildings ON rooms.building_no = buildings.building_no)
- 自然连接
略
# 二、多对多查询
# 1、库表准备
准备一个多对多的 关系表。
一个学生(student)可以选择多个课程(courses);一个课程(course)可以被多个学生(students)选中
添加数据
INSERT INTO `students` (name, age) VALUES('why', 18);
INSERT INTO `students` (name, age) VALUES('tom', 22);
INSERT INTO `students` (name, age) VALUES('lilei', 25);
INSERT INTO `students` (name, age) VALUES('lucy', 16);
INSERT INTO `students` (name, age) VALUES('lily', 20);
INSERT INTO `courses` (name, price) VALUES ('英语', 100);
INSERT INTO `courses` (name, price) VALUES ('语文', 666);
INSERT INTO `courses` (name, price) VALUES ('数学', 888);
INSERT INTO `courses` (name, price) VALUES ('历史', 80);
INSERT INTO `courses` (name, price) VALUES ('物理', 100);
# why 选修了 英文和数学
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 1);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 3);
# lilei选修了 语文和数学和历史
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 2);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 4);
-- 创建学生表
CREATE TABLE IF NOT EXISTS `students`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT
) COMMENT = '学生表';
-- 创建课程表
CREATE TABLE IF NOT EXISTS `courses`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
price DOUBLE NOT NULL
) COMMENT = '课程表';
-- 创建学生-课程关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = '学生-课程关系表';
# 2、内连接应用
- 所有有选课学生的选课情况(内连接)
SELECT
stu.name stuName, stu.age stuAge, cs.name csName, cs.price csPrice
FROM `students` stu
JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
JOIN `courses` cs ON ssc.course_id = cs.id;
# 3、外连接应用
- 所有的学生(不管有没有选课)的选课情况
SELECT
stu.name stuName, stu.age stuAge, cs.name csName, cs.price csPrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
LEFT JOIN `courses` cs ON ssc.course_id = cs.id;
- 单个学生的选课情况(lily 的选课情况)
SELECT
stu.name stuName, stu.age stuAge, cs.name csName, cs.price csPrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
LEFT JOIN `courses` cs ON ssc.course_id = cs.id
WHERE stu.name = 'lilei';
- 查看哪些学生没有选择课程
SELECT
stu.name stuName, stu.age stuAge, cs.name csName, cs.price csPrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
LEFT JOIN `courses` cs ON ssc.course_id = cs.id
WHERE cs.id IS NULL;
- 查看哪些课程没有被选择
SELECT
stu.name stuName, stu.age stuAge, cs.name csName, cs.price csPrice
FROM `students` stu
RIGHT JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
RIGHT JOIN `courses` cs ON ssc.course_id = cs.id
WHERE stu.id IS NULL;
# 三、开发应用
# 1、数据库连接
在不同的编程环境下我们可以使用不同的 mysql 驱动,来连接数据库;还可以通过 ORM 框架简化开发流程:
① 后端:
- mysql-connector-java-5.1.48.jar(java 环境下的 JDBC 驱动)
数据库连接(原生 JDBC API)
![](https://blog-cloudbed.oss-cn-chengdu.aliyuncs.com/note-taking/20231012104924.png)
- druid-1.1.12.jar(数据库连接池:管理数据库连接)
数据库连接管理
// 1、注册驱动
// Class.forName("com.mysql.jdbc.Driver")
// 2、获取Connection连接
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection conn = dataSource.getConnection()
- druid.properties
# Connect/J version 5.X
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/db_1?useSSL=false&useServerPrepStmts=true&useUnicode=true&characterEncoding=utf-8
username=root
password=mysql666
# Connect/J version 6.X以后的版本,适合于MySQL8.x新版本
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/db_1?serverTimezone=UTC&useSSL=false&useServerPrepStmts=true&useUnicode=true&characterEncoding=utf-8
username=root
password=mysql666
- mybatis✨(ORM 框架:可以简化 JDBC 开发)
常应用于 JavaWeb 项目开发中
使用示例
// 1、注册驱动
// Class.forName("com.mysql.jdbc.Driver")
// 2、获取Connection连接
String resource = "mybatis-config.xml";
InputStream inputSteam = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputSteam);
SqlSession sqlSession = sqlSessionFactory.openSession();
- mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/db_1"/>
<property name="username" value="root"/>
<property name="password" value="mysql666"/>
</dataSource>
</environment>
</environments>
</configuration>
- Spring JDBC✨(Spring Boot 通过 Spring JDBC 模块支持使用 JDBC 来进行数据库访问)
常应用于 Spring Boot 项目开发中
使用示例
- pom.xml
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
- application.yml
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/db_1
username: root
password: 123456
② 前端:
- mysql2(node 环境下的 mysql 驱动程序)
const { writeFile } = require('fs')
// 1、引入驱动
const mysql = require('mysql2')
// 2、获取Connection连接
const connection = mysql.createConnection({
host: 'localhost',
port: 3306,
user: 'root',
password: 'mysql666.',
database: 'db_2'
})
// 使用测试
connection.query(`SELECT * FROM rooms;`, (err, results) => {
if (err) throw err
console.log(results)
// writeFile('./data.txt', JSON.stringify(results, null, 2), { flag: 'a' }, (err) => {
// if (err) throw err
// })
})
- prisma (opens new window)(ORM 框架:简化数据库操作和与数据库的交互)
# 2、json 对象(1:n)
以经典的左查询为例,如果在一对多的场景中,在 mysql2 代码层面的查询结果为:
SELECT *
FROM rooms LEFT JOIN buildings ON rooms.building_no = buildings.building_no
WHERE room_no = 1;
- 查询结果
[
{
room_no: 1,
room_name: 'Amazon',
building_no: 1,
building_name: '海南大厦',
address: '海口市国兴大道1234号'
}
]
但这样可能会显得比较混乱(毕竟两表数据融合),此时我们可以使用 mysql 的聚合函数 JSON_OBJECT()
将右表匹配到的数据放到一个单独的 object 中:
SELECT
rooms.room_no as room_no, rooms.room_name as room_name,
JSON_OBJECT('id',buildings.building_no,'building_name', buildings.building_name, 'address', buildings.address) as building
FROM rooms
LEFT JOIN buildings ON rooms.building_no = buildings.building_no
WHERE room_no = 1;
- 查询结果
[
{
room_no: 1,
room_name: 'Amazon',
building: { id: 1, address: '海口市国兴大道1234号', building_name: '海南大厦' }
}
]
# 3、json 数组(n:n)
以经典的左查询为例,如果在一对多的场景中,在 mysql2 代码层面的查询结果为:
SELECT *
FROM students stu
LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id
LEFT JOIN courses cs ON ssc.course_id = cs.id
WHERE cs.id IS NOT NULL;
- 查询结果
提示
在 mysql2 使用中,会出现相同字段名合并的情况 🤔,后者的 name 字段覆盖了前者的 name 字段
[
{ id: 1, name: '英语', age: 18, student_id: 1, course_id: 1, price: 100 },
{ id: 3, name: '数学', age: 18, student_id: 1, course_id: 3, price: 888 },
{ id: 2, name: '语文', age: 25, student_id: 3, course_id: 2, price: 666 },
{ id: 3, name: '数学', age: 25, student_id: 3, course_id: 3, price: 888 },
{ id: 4, name: '历史', age: 25, student_id: 3, course_id: 4, price: 80 }
]
但这样可能会显得比较混乱(毕竟两表数据融合),此时我们可以使用 mysql 的聚合函数 JSON_ARRAYAGG()
将右表匹配到的数据放到一个单独的 array 中:
SELECT
stu.id as id, stu.name as stuName, stu.age as stuAge,
JSON_ARRAYAGG(JSON_OBJECT('id', cs.id,'name', cs.name, 'price',cs.price)) as courses
FROM students stu
LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id
LEFT JOIN courses cs ON ssc.course_id = cs.id
WHERE cs.id IS NOT NULL
GROUP BY stu.id; # 要先对查询结果进行分组
- 查询结果
[
{
"id": 1,
"stuName": "why",
"stuAge": 18,
"courses": [
{ "id": 1, "name": "英语", "price": 100 },
{ "id": 3, "name": "数学", "price": 888 }
]
},
{
"id": 3,
"stuName": "lilei",
"stuAge": 25,
"courses": [
{ "id": 2, "name": "语文", "price": 666 },
{ "id": 3, "name": "数学", "price": 888 },
{ "id": 4, "name": "历史", "price": 80 }
]
}
]
# 代码
json 对象案例
CREATE TABLE IF NOT EXISTS buildings (
building_no INT PRIMARY KEY AUTO_INCREMENT,
building_name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL
) COMMENT = '建筑表';
INSERT INTO buildings(building_name,address)
VALUES('海南大厦','海口市国兴大道1234号'),
('万达水城','海口市大同路1200号'),
('中山大厦', '海口市骑楼街56号');
CREATE TABLE IF NOT EXISTS rooms (
room_no INT PRIMARY KEY AUTO_INCREMENT,
room_name VARCHAR(255) NOT NULL,
building_no INT,
FOREIGN KEY (building_no) REFERENCES buildings (building_no) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = '房间表';
INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
('War Room',1),
('Office of CEO',1),
('Marketing',2),
('Lakeside Cottage',2);
SELECT *
FROM rooms LEFT JOIN buildings ON rooms.building_no = buildings.building_no
WHERE room_no = 1;
SELECT
rooms.room_no as room_no, rooms.room_name as room_name,
JSON_OBJECT('id',buildings.building_no,'building_name', buildings.building_name, 'address', buildings.address) as building
FROM rooms
LEFT JOIN buildings ON rooms.building_no = buildings.building_no
WHERE room_no = 1;
json 数组案例
-- 创建学生表
CREATE TABLE IF NOT EXISTS `students`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT
) COMMENT = '学生表';
INSERT INTO `students` (name, age) VALUES('why', 18);
INSERT INTO `students` (name, age) VALUES('tom', 22);
INSERT INTO `students` (name, age) VALUES('lilei', 25);
INSERT INTO `students` (name, age) VALUES('lucy', 16);
INSERT INTO `students` (name, age) VALUES('lily', 20);
-- 创建课程表
CREATE TABLE IF NOT EXISTS `courses`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
price DOUBLE NOT NULL
) COMMENT = '课程表';
INSERT INTO `courses` (name, price) VALUES ('英语', 100);
INSERT INTO `courses` (name, price) VALUES ('语文', 666);
INSERT INTO `courses` (name, price) VALUES ('数学', 888);
INSERT INTO `courses` (name, price) VALUES ('历史', 80);
INSERT INTO `courses` (name, price) VALUES ('物理', 100);
-- 创建学生-课程关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = '学生-课程关系表';
# why 选修了 英文和数学
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 1);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 3);
# lilei选修了 语文和数学和历史
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 2);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 4);
SELECT *
FROM students stu
LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id
LEFT JOIN courses cs ON ssc.course_id = cs.id
WHERE cs.id IS NOT NULL;
SELECT
stu.id as id, stu.name as stuName, stu.age as stuAge,
JSON_ARRAYAGG(JSON_OBJECT('id', cs.id,'name', cs.name, 'price',cs.price)) as courses
FROM students stu
LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id
LEFT JOIN courses cs ON ssc.course_id = cs.id
WHERE cs.id IS NOT NULL
GROUP BY stu.id; # 要先对查询结果进行分组