写在前面：
如果文章对你有帮助，记得点赞关注加收藏一波，利于以后需要的时候复习，多谢支持！
【MySQL数据库学习】系列文章
第一章 《认识与环境搭建》
第二章 《数据类型》
第三章 《数据定义语言DDL》
第四章 《数据操作语言DML》
第五章 《约束》
第六章 《数据查询语言DQL》
第七章 《多表操作》
文章目录
- 【MySQL数据库学习】系列文章
- 一、多表关系
- （一）多表关系概念
- （二）外键约束
- 1.一对多关系
- 2.多对多关系
- 二、多表联合查询
- （一）交叉连接查询
- （二）内连接查询
一、多表关系
（一）多表关系概念
在实际的项目中，往往需要进行处理多表数据，而多表的关系通常可以概括为以下几种。
- 一对一关系：例如一个学生只有一个身份证号，表现为一张表的一行对应另一张表的一行。但这种关系使用较少，因为通常一对一关系可以合成为一张表。
- 一对多关系：例如一个部门有着多个员工，表现为一张表的一行对应另一张表的多行。
- 多对多关系：例如学生和选课之间，一个学生可以选多节课，而一节课也可以被多个学生所选，表现在一张表对应另一张表的多行的同时，另一张表的一行也对应这张表的多行。通常多对多的关系需要中间表将其分割为一对多的关系。
（二）外键约束
外键约束会在表中建立一种关系，这种关系使得从表（子表）中的列（外键）引用主表（父表）中的列（主键或唯一键）。通过这种方式，可以确保子表中的数据在父表中有对应的条目。
这用于确保数据的一致性和完整性，具体而言，则是用于维护表与表之间的关系，确保在一个表中引用的值在另一个表中存在。
外键约束有着以下特点。
- 主表必须已经存在于数据库，或者是当前正在创建的表。
- 必须为主表定义主键。
- 主键不能包含空值，但允许在外键中出现空值。
- 在主表的表名后面指定列名或列名的组合，而这个列或者列组合必须是主表的主键或者候选键。
- 外键中列的数目必须和主键中列的数目相同。
- 外键中列的数据类型必须和主键中列的数据类型相同。
如果想要创建外键约束，有两种方式。
方式1：在创建表时设置外键约束。
在CREATE TABLE语句中，通过FOREIGN KEY关键字来指定外键，具体的语法格式如下。
CONSTRAINT 外键名 FOREIGN KEY 字段名1,字段名2,... REFERENCES 主表名 主键列1,主键列2,...
下面是简单的示例。
USE mydb1;
-- 创建主表（部门表）
CREATE TABLE IF NOT EXISTS dept (
did VARCHAR(20) PRIMARY KEY, -- 部门编号，设置主键
name VARCHAR(20) -- 部门名字
);
-- 创建从表（员工表）
CREATE TABLE IF NOT EXISTS emp (
eid VARCHAR(20) PRIMARY KEY, -- 员工编号，设置主键
ename VARCHAR(20), -- 员工名字
age INT, -- 员工年龄
dept_id VARCHAR(20), -- 员工所属部门编号
CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept(did) -- 外键约束
);
创建完外键约束后，可以通过模型查看外键约束关系。点击表，选中两个表，右键选择“逆向表到模型”即可查看。
可以看到，两张表之间的外键约束已经建立。
除此之外，还有另一种创建外键约束的方式。
方式2：在修改表时设置外键约束。
在ALTER TABLE语句中，通过FOREIGN KEY关键字来指定外键，具体的语法格式如下。
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY 字段名1,字段名2,... REFERENCES 主表名 主键列1,主键列2,...
下面是简单的示例。
-- 创建主表（部门表）
CREATE TABLE IF NOT EXISTS dept (
did VARCHAR(20) PRIMARY KEY, -- 部门编号，设置主键
name VARCHAR(20) -- 部门名字
);
-- 创建从表（员工表）
CREATE TABLE IF NOT EXISTS emp (
eid VARCHAR(20) PRIMARY KEY, -- 员工编号，设置主键
ename VARCHAR(20), -- 员工名字
age INT, -- 员工年龄
dept_id VARCHAR(20), -- 员工所属部门编号
);
-- 创建外键约束
ALTER TABLE emp ADD CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept(did);
1.一对多关系
为了验证外键约束的作用，首先应该将上面创建的两张空表，进行一对多关系的数据填充。
-- 1.添加主表数据
INSERT INTO dept VALUES ('1001','研发部');
INSERT INTO dept VALUES ('1002','销售部');
INSERT INTO dept VALUES ('1003','财务部');
INSERT INTO dept VALUES ('1004','人事部');
-- 2.添加从表数据
INSERT INTO emp VALUES ('1','刘邦',25,'1001');
INSERT INTO emp VALUES ('2','樊哙',24,'1001');
INSERT INTO emp VALUES ('3','张良',26,'1001');
INSERT INTO emp VALUES ('4','韩信',25,'1002');
INSERT INTO emp VALUES ('5','萧何',27,'1002');
INSERT INTO emp VALUES ('6','曹参',23,'1003');
INSERT INTO emp VALUES ('7','陈平',26,'1003');
INSERT INTO emp VALUES ('8','周勃',28,'1004');
注意，当删除数据的时候，有外键依赖的主表数据是不能删除的，除非先清除从表中依赖主表的外键，否则会报错。但反之，从表中的外键都是可以随意删除的。
而如果希望删除外键约束时，需要在ALTER TABLE语句中使用DROP关键字来删除外键约束。具体语法如下所示。
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名
简单的实现示例则如下所示。
ALTER TABLE emp DROP FOREIGN KEY emp_fk;
这样就能够删除刚才在上面的示例中在从表emp中创建的emp_fk外键约束。
2.多对多关系
对于多对多关系，比如之前提到的学生和选课的关系，此时学生表和选课表都是主表，而简化其关系的中间表则是从表，其中的外键列依赖于学生表和选课表两个主表。
具体的实现示例如下。
-- 创建学生表（主表）
CREATE TABLE IF NOT EXISTS student (
sid INT PRIMARY KEY auto_increment, -- 学生编号
name VARCHAR(20), -- 学生姓名
age INT, -- 学生年龄
gender VARCHAR(20) -- 学生性别
);
-- 创建课程表（主表）
CREATE TABLE IF NOT EXISTS course (
cid INT PRIMARY KEY auto_increment, -- 课程编号
cname VARCHAR(20) -- 课程名
);
-- 创建中间表（从表）
CREATE TABLE IF NOT EXISTS score (
sid INT,
cid INT,
score DOUBLE
);
-- 创建外键约束
ALTER TABLE score ADD FOREIGN KEY (sid) REFERENCES student(sid);
ALTER TABLE score ADD FOREIGN KEY (cid) REFERENCES course(cid);
-- 学生表数据填充
INSERT INTO student VALUES (1,'刘邦',21,'男'),(2,'吕雉',19,'女'),(3,'项羽',20,'男');
-- 课程表数据填充
INSERT INTO course VALUES (1,'语文'),(2,'数学'),(3,'英语');
-- 中间表数据填充
INSERT INTO score VALUES (1,1,78),(1,2,75),(2,1,88),(2,3,90),(3,2,80),(3,3,65);
查看表的模型即可看到外键约束已创建完毕。
二、多表联合查询
多表联合查询（也称为联接查询）用于从多个表中检索相关数据，因为在实际项目需要时，可能需要显示的查询结果来自于两个或两个以上的表。
多表查询有以下分类。
- 交叉连接查询
- 内连接查询
- 外连接查询
- 子查询
- 表自关联
作为使用的数据，仍然主要沿用上面的部门和员工表示例，只不过不加入外键约束。
CREATE TABLE IF NOT EXISTS dept (
did VARCHAR(20) PRIMARY KEY, -- 部门编号，设置主键
name VARCHAR(20) -- 部门名字
);
CREATE TABLE IF NOT EXISTS emp (
eid VARCHAR(20) PRIMARY KEY, -- 员工编号，设置主键
ename VARCHAR(20), -- 员工名字
age INT, -- 员工年龄
dept_id VARCHAR(20) -- 员工所属部门编号
);
INSERT INTO dept VALUES
('1001','研发部'),
('1002','销售部'),
('1003','财务部'),
('1004','人事部');
INSERT INTO emp VALUES
('01','刘邦',25,'1001'),
('02','樊哙',24,'1001'),
('03','张良',26,'1001'),
('04','韩信',25,'1001'),
('05','萧何',27,'1002'),
('06','曹参',23,'1002'),
('07','陈平',26,'1002'),
('08','周勃',28,'1003'),
('09','彭越',27,'1003'),
('10','吕雉',24,'1005');
（一）交叉连接查询
交叉连接（Cross Join） 是 SQL 中的一种连接类型，它返回两个表的笛卡尔积，可以理解为一张表的每一行都和另一张表的任意一行进行匹配（假如A表有m行数据，B表有n行数据，则返回m*n行数据）。笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选。
其语法格式为以下所示。
SELECT * FROM 表1,表2,...
具体实现示例如下所示。
SELECT * FROM dept,emp;
返回结果如下。
（二）内连接查询
内连接（INNER JOIN） 是 SQL 中最常用的连接类型之一，用于从两个或多个表中提取符合条件的记录。内连接只返回满足连接条件的记录，实际上是求的两张表的交集，可以将表中的相关数据组合在一起，从而进行更加复杂的查询和分析。
其具体语法格式如下所示。
-- 隐式内连接
SELECT * FROM A表,B表 WHERE 条件; -- 可以理解为从笛卡尔积中筛选出符合条件的值
-- 显式内连接
SELECT * FROM A表 INNER JOIN B表 ON 条件; -- INNER可省略
具体示例如下所示。
-- 查询每个部门的所属员工
SELECT * FROM dept,emp WHERE dept.did = emp.dept_id;
SELECT * FROM dept INNER JOIN emp ON dept.did = emp.dept_id;
-- 查询研发部和销售部的所属员工
SELECT * FROM dept,emp WHERE dept.did = emp.dept_id AND name IN ('研发部','销售部');
SELECT * FROM dept INNER JOIN emp ON dept.did = emp.dept_id AND name IN ('研发部','销售部');
-- 查询每个部门的员工数，并升序排序
SELECT a.name,count(*) FROM dept a JOIN emp b ON a.did = b.dept_id GROUP BY a.did;
-- 查询人数大于3的部门，并按照人数降序排序
SELECT a.name,count(*) AS count FROM dept a JOIN emp b ON a.did = b.dept_id GROUP BY a.did HAVING count >= 3 ORDER BY count DESC;
我是EC，一个永远在学习中的探索者，关注我，让我们一起进步！
