# 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、连接查询 🎈

说明

  连接查询的作用是对为查询的 每条记录 追加更多 有关联的字段

详细内容

图示:

# 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)
  • 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
  // })
})

# 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; # 要先对查询结果进行分组
更新于 : 7/8/2024, 10:21:14 AM