数据库（MySQL）—— 多表查询

今天我们来进入MySQL中一个非常重要的部分：多表查询：

多表关系

多表关系是数据库设计中常见的概念，指的是在关系型数据库中，两个或多个数据表之间存在的关联关系。这些关系可以是一对一（1:1）、一对多（1:N）或多对多（M:N）等类型。多表关系的建立有助于实现数据的规范化存储和高效查询。

1. 一对一关系（1:1）：在这种关系中，一个表中的记录与另一个表中的记录有且仅有一个对应关系。例如，一个学生表（Student）和一个学生详情表（StudentDetail），每个学生都有一个唯一的详情记录，反之亦然。
2. 一对多关系（1:N）：在这种关系中，一个表中的一条记录可以与另一个表中的多条记录相关联。例如，一个班级表（Class）和学生表（Student），一个班级可以有多个学生，但每个学生只能属于一个班级。
3. 多对多关系（M:N）：在这种关系中，一个表中的一条记录可以与另一个表中的多条记录相关联，反之亦然。例如，一个学生表（Student）和一个课程表（Course），一个学生可以选修多门课程，同时一门课程也可以被多个学生选修。为了实现这种关系，通常需要引入一个中间表（如StudentCourse），该表记录学生和课程之间的关联信息。

在数据库设计中，通过定义主键（Primary Key）和外键（Foreign Key）来建立和维护多表关系。主键用于唯一标识一个表中的记录，而外键则用于在一个表中引用另一个表的主键。当两个表之间存在关联关系时，可以在一个表的外键列中存储另一个表的主键值，从而实现表的关联查询。

通过合理地设计多表关系，可以提高数据库的查询性能和数据完整性。同时，多表关系也有助于实现数据的逻辑分离和模块化，使得数据库结构更加清晰易懂。

一对多

• 案例: 部门 与 员工的关系
• 关系: 一个部门对应多个员工，一个员工对应一个部门
• 实现: 在多的一方建立外键，指向一的一方的主键

对应的SQL语句如下：

-- 创建emp表
CREATE TABLE emp(
    -- id号
    id      int PRIMARY KEY AUTO_INCREMENT COMMENT '员工id',
    name    VARCHAR(10) UNIQUE COMMENT '员工姓名',
    age     TINYINT UNSIGNED COMMENT '员工年龄',
    dept_id TINYINT UNSIGNED COMMENT '部门编号',
    -- 外键
    CONSTRAINT fk_key_dept_id FOREIGN KEY(dept_id) REFERENCES
    dept(id)
)COMMENT '员工表';

-- 部门表
CREATE TABLE dept(
  id TINYINT UNSIGNED PRIMARY KEY  COMMENT '部门编号',
  name VARCHAR(10) COMMENT '部门名字'
)COMMENT '部门表';

多对多

• 案例: 学生 与 课程的关系
• 关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
• 实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

CREATE TABLE students(
    id int UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '序号',
    name VARCHAR(10) UNIQUE COMMENT '学生姓名',
    no int UNSIGNED UNIQUE COMMENT '学生学号'
)COMMENT '学生表';

-- 选课表
CREATE TABLE courses(
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '序号',
    name VARCHAR(10) COMMENT '课程名字'
)COMMENT '选课表';

-- 中间表
CREATE TABLE stu_cour(
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '序号',
    studentid int UNSIGNED NOT NULL COMMENT '学生ID',
    courseid int  NOT NULL COMMENT '课程ID',

    -- 外键
    CONSTRAINT fk_stu_no FOREIGN KEY (studentid) REFERENCES
    students(id),
    CONSTRAINT  fk_cour_no FOREIGN KEY (courseid) REFERENCES
    courses(id)
)COMMENT '学生选课详情表';

一对一

• 案例: 用户 与 用户详情的关系
• 关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
• 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

CREATE TABLE tb_user(
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '序号',
    age TINYINT UNSIGNED COMMENT '年龄',
    name VARCHAR(10) NOT NULL COMMENT '姓名',
    gender char(1) NOT NULL COMMENT '性别',
    phone char(11) UNIQUE COMMENT '电话号码'
)COMMENT '用户基本信息表';

CREATE TABLE tb_user_edu(
  id int PRIMARY KEY AUTO_INCREMENT COMMENT '序号',
  degree VARCHAR(10) NOT NULL COMMENT '学历',
  major VARCHAR(10) NOT NULL COMMENT '学位',
  primaryschool VARCHAR(20) NOT NULL COMMENT '小学',
  middleschool VARCHAR(20) NOT NULL COMMENT '中学',
  university VARCHAR(20) NOT NULL COMMENT '大学',
  userid int UNIQUE COMMENT '用户编号',
  CONSTRAINT fk_to_id FOREIGN KEY(userid) REFERENCES
  tb_user(id) -- 外键
)COMMENT '用户教育信息表';


insert into tb_user(id, name, age, gender, phone) values
(null,'黄渤',45,'1','18800001111'),
(null,'冰冰',35,'2','18800002222'),
(null,'码云',55,'1','18800008888'),
(null,'李彦宏',50,'1','18800009999');
insert into tb_user_edu(id, degree, major, primaryschool, middleschool,
university, userid) values
(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

多表查询概述

数据准备

删除之前 emp, dept表的测试数据
执行如下脚本，创建emp表与dept表并插入测试数据

DROP TABLE IF EXISTS emp,dept;
create table dept(
    id int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
)comment '部门表';
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
'销售部'), (5, '总经办'), (6, '人事部');

-- 创建emp表，并插入数据
create table emp(
    id int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '姓名',
    age int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID'
)comment '员工表';

-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);

INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

查询形式

原来查询单表数据，执行的SQL形式为：select * from emp;

那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept; 具体的执行结果如下:

此时,我们看到查询结果中包含了大量的结果集，总共102条记录，而这其实就是员工表emp所有的记录
(17) 与 部门表dept所有记录(6) 的所有组合情况，这种现象称之为笛卡尔积。接下来，就来简单介绍下笛卡尔积。

笛卡尔积

笛卡尔积（Cartesian Product）是数学中的一个概念，它表示两个集合A和B的所有可能的有序对的集合。用符号表示为A × B，其中“×”表示笛卡尔积运算。具体来说，A × B = {(a, b) | a ∈ A 且 b ∈ B}。

例如，设集合A = {1, 2}，集合B = {a, b, c}，则A × B = {(1, a), (1, b), (1, c), (2, a), (2, b), (2, c)}。

而在多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。

在SQL语句中，如何来去除无效的笛卡尔积呢？ 我们可以给多表查询加上连接查询的条件即可：

select * from emp , dept where emp.dept_id = dept.id;

分类

连接查询

• 内连接：相当于查询A、B交集部分数据
• 外连接：
  左外连接：查询左表所有数据，以及两张表交集部分数据
  右外连接：查询右表所有数据，以及两张表交集部分数据
  自连接：当前表与自身的连接查询，自连接必须使用表别名

我们先来介绍内连接：

内连接

内连接的语法分为两种: 隐式内连接、显式内连接。先来学习一下具体的语法结构。

隐式内连接：

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

显式内连接：

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ; 1

案例:

A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
表结构: emp , dept
连接条件: emp.dept_id = dept.id

select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;

我们也可以取别名简化操作：

-- 为每一张表起别名,简化SQL编写
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

外连接：

B. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) — INNER JOIN …ON …
表结构: emp , dept
连接条件: emp.dept_id = dept.id

select emp.name,dept.id
from emp inner join
dept on emp.dept_id = dept.id;

表的别名:
tablea as 别名1 , tableb as 别名2 ;
tablea 别名1 , tableb 别名2 ;

外连接

外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为：

左外连接

案例:
A. 查询emp表的所有数据, 和对应的部门信息
由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
表结构: emp, dept
连接条件: emp.dept_id = dept.id

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

select * from
emp left join
dept on emp.dept_id = dept.id;

右外连接

案例

查询dept表的所有数据, 和对应的员工信息(右外连接)
由于需求中提到，要查询dept表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
表结构: emp, dept
连接条件: emp.dept_id = dept.id

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...; 

注意事项：
左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

自连接

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。我们先来学习一下自连接的查询语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

而对于自连接查询，可以是内连接查询，也可以是外连接查询。

案例：

A. 查询员工 及其 所属领导的名字
表结构: emp

select a.name, b.name from emp a,emp b
where a.managerid = b.id;

或者这样也行：

select a.name,b.name from emp a
inner join emp b
on a.managerid = b.id;

查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
表结构: emp a , emp b

这个时候我们可以用上左外连接：

select a.name,b.name from emp a
left join emp b
on a.managerid = b.id;

注意事项:
在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

案例:

A. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.
当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可。 那这里呢，我们也可以通过union/union all来联合查询.

union all查询出来的结果，仅仅进行简单的合并，并未去重。

select * from emp where salary < 5000
union
select * from emp where age > 50;

union 联合查询，会对查询出来的结果进行去重处理。