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

• 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; # 要先对查询结果进行分组