MySQL数据库多表操作通关指南(外键约束和多表联合查询)

本篇文章主要是结合我之前面试的各种经历和实战开发中遇到的问题解决经验整理的，希望这篇《MySQL数据库多表操作通关指南(外键约束和多表联合查询)》对你有很大帮助！欢迎收藏，分享给更多的需要的朋友学习~

1 多表关系

🍑 一对一关系

比如：一个人有一个身份证，一个身份证对应一个人；实现原则：在任一表中添加唯一外键，指向另一方主键；在实际开发中遇到一对一情况比较少，遇到一对一关系一般合并表。在下图中，可以将两个表根据 id 合并：

🍑 一对多/多对一关系

比如：一个部门有多个员工，一个员工只能对应一个部门；实现原则：在多的一方建立外键，指向另一方的主键， 示意图如下：

🍑 多对多关系

比如：一个学生可以修多个课程，一个课程也可以被多个学生选择；实现原则：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系，折成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来两个表的主键， 示意图如下：

2 外键约束（FOREIGN KEY）

2.1 外键约束说明

🍓 简介：

  MySQL 外键约束是表的一种特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

  外键约束用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

🍉 举例说明：

  在下图中，从表中的部门号受主表中的主键部门号的范围限制， 即，从表中的部门号列只能取值为1001、1002或1003。

👮 定义外键的规则：

1. 主表必须已经存在于数据库中，或者是当前正在创建的表（即在创建外键时，主表必须存在）；
2. 必须为主表定义主键；
3. 主键不能包含空值，但允许在外键中出现空值；
4. 在主表的表名后面指定列名或列名的组合，这个列或者组合必须是主表的主键或候选键；
5. 外键中列的数目必须和主表的主键中列的数目相同（通俗解释：主键的列数必须与外键的列数相同）；
6. 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

2.2 外键约束的创建

🐅 1.在创建表时设置外键约束

  在 create table 语句中，使用 foreign key 关键字来指定外键。具体语法格式如下：

[constraint ] foreign key 字段名1 [, 字段名2, ...] 
references  主键列1, [, 主键列2, ...]

🐅 2.修改表时添加外键约束

  这样做的前提是：从表中外键的列中的数据必须与主键中主键列中的数据一致或没有数据。 语法如下：

alter table 
add constraint  foreign key()
references  ();

2.3 外键约束实操：一对多关系

🔑 下面使用下面这段代码创建两个表，dept 作主表存储部门信息，emp 作从表存储员工信息，主键约束与外键约束详细见代码注释：

-- 创建部门表（主表）
CREATE TABLE IF NOT EXISTS dept
(
    deptno 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 (deptno) -- 外键约束
)

🍎 结果: 外键约束创建成功，图示表明 emp 表的 eid 受到主表 dept 的主键 deptno 外键约束。

🐍 下面我们给已经创建好的两个表添加数据，用于 验证外键约束的作用， 相关代码如下图：

因此我们将最后一条更正为 ‘1004’，更正后的代码如下：

-- 1. 添加主表数据
INSERT INTO dept
VALUES ('1001', '研发部'),
       ('1002', '销售部'),
       ('1003', '财务部'),
       ('1004', '人事部');

-- 2. 添加从表数据
INSERT INTO emp
VALUES ('1', 'Nezuko627', 20, '1001'),
       ('2', 'Whisper', 19, '1001'),
       ('3', '大头呢', 21, '1001'),
       ('4', '小牛马', 21, '1002'),
       ('5', '小鹏', 25, '1002'),
       ('6', '几何心凉', 25, '1002'),
       ('7', '草帽路飞', 17, '1003'),
       ('8', '娜美', 16, '1003'),
       ('9', '乔巴', 5, '1004');

创建好的两张表如下图：

🐑 在表中删除数据:

主表中的数据被从表依赖时不能删除， 比如，dept 表中的 deptno 中的所有数据都被 emp 表中的 dept_id 所依赖了，因此，dept 表中的数据不可删除；

从表中是数据可以随意删除。 代码及删除后的表数据如下：

2.4 删除外键约束

  当一个表中不需要外键约束时，就需要从表中删除。外键一旦删除，就会解除和主表的关联关系

🚗 语法格式：

alter table  drop foreign key ;

2.5 外键约束实操：多对多关系

  多对多关系相对来说更加复杂，简单的来说，就是需要：使用一张中间表作为从表来关联其余的主表。 如下图所示：

下面我们根据图示来创建这三个表，并添加相应的外键约束，相关代码如下：

-- 1. 创建主表1 学生表 student
CREATE TABLE IF NOT EXISTS student
(
    sid  INT PRIMARY KEY AUTO_INCREMENT, -- 学号
    name VARCHAR(20),                    -- 姓名
    age  INT                             -- 年龄
);

-- 2. 创建主表2 课程表 course
CREATE TABLE IF NOT EXISTS course
(
    cid   INT PRIMARY KEY, -- 课程号
    cname VARCHAR(20)      -- 课程名
);

-- 3. 创建中间表 额外添加一个成绩
CREATE TABLE IF NOT EXISTS score
(
    sid   INT,   -- 学号
    cid   INT,   -- 课程号
    score DOUBLE -- 成绩
);

-- 4. 添加外键约束
ALTER TABLE score
    ADD FOREIGN KEY (sid) REFERENCES student (sid);

ALTER TABLE score
    ADD FOREIGN KEY (cid) REFERENCES course (cid);

-- 5. 添加数据
INSERT INTO student
VALUES (1, 'Whisper', 18),
       (2, '大头呢', 19),
       (3, 'Nezuko', 20);

INSERT INTO course
VALUES (1, '程序设计'),
       (2, '大学英语'),
       (3, '高等数学');

INSERT INTO score
VALUES (1, 1, 98),
       (2, 2, 86),
       (2, 1, 95),
       (2, 3, 75),
       (3, 2, 99),
       (3, 3, 76);

🍎 结果：

🐑 在表中删除数据: 中间从表可以随便删除和修改，但是两边的主表受从表依赖的数据不能删除或修改。

3 多表联合查询

3.1 联合查询的简介和分类

🆔 简介：

  多表查询就是同时查询两个或两个以上的表，主要用于展示一对多、多对多的数据，需要展示的数据来自于多张表。

🐱 分类概述：

3.2 联合查询数据准备

  接下来准备多表查询需要的数据。需要注意的是， 外键约束只影响增删，对多表查询并无影响！ 数据准备代码如下：

-- 创建部门表
CREATE TABLE IF NOT EXISTS dept
(
    deptno 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', '研发部');
INSERT INTO dept VALUES ('1002', '销售部');
INSERT INTO dept VALUES ('1003', '财务部');
INSERT INTO dept VALUES ('1004', '人事部');

-- 给员工表添加数据
INSERT INTO emp VALUES ('1', '乔巴', 20, '1001');
INSERT INTO emp VALUES ('2', '路飞', 21, '1001');
INSERT INTO emp VALUES ('3', '祢豆子', 23, '1001');
INSERT INTO emp VALUES ('4', '漩涡鸣人', 18, '1001');
INSERT INTO emp VALUES ('5', '春野樱', 85, '1002');
INSERT INTO emp VALUES ('6', '洛克李', 33, '1002');
INSERT INTO emp VALUES ('7', '皮卡丘', 50, '1002');
INSERT INTO emp VALUES ('8', '胖丁', 60, '1003');
INSERT INTO emp VALUES ('9', '喵喵', 58, '1003');
INSERT INTO emp VALUES ('10', '淼淼', 3, '1005');

🐘 数据表如下：

3.3 交叉联合查询

🆔 简介：

• 交叉联合查询返回被连接的两个表所有数据行的 笛卡儿积；
• 笛卡尔积可以理解成一张表的每行去和另一张表的任意一行进行匹配；
• 如果A表有m行数据，B表有n行数据，则返回m*n行数据；
• 笛卡尔积会产生很多冗余的数据， 可以通过其他查询在该集合基础上进行条件筛选。

🍓 语法：

select * from 表1, 表2, 表3...;

🍉 实现：

SELECT * FROM emp, dept;

3.4 内连接查询

🆔 简介： 内连接查询求 多张表的交集。 示意图如下：

🍓 语法：

-- 隐式内连接（SQL92标准）
select * from A, B where 条件;

-- 显式内连接（SQL99标准）
select * from A inner join B on 条件;

🍉 实现：

查询每个部门的所属员工

-- 隐式内连接方式
SELECT * FROM dept, emp WHERE dept.deptno = emp.dept_id;
-- 显示内连接方式
SELECT * FROM dept INNER JOIN emp ON dept.deptno = emp.dept_id;

查询研发部门的所属员工

-- 隐式内连接方式
SELECT * FROM dept, emp WHERE dept.deptno = emp.dept_id AND dept.name = '研发部';
-- 显示内连接方式
SELECT * FROM dept INNER JOIN emp ON dept.deptno = emp.dept_id AND dept.name = '研发部';

查询每个部门的员工数，并升序排序

-- 隐式内连接方式
SELECT dept.name, count(*) 'count'
FROM dept,
     emp
WHERE dept.deptno = emp.dept_id
GROUP BY dept.deptno
ORDER BY count ASC;

-- 显示内连接式
SELECT dept.name, count(*) 'count'
FROM dept
         INNER JOIN emp ON dept.deptno = emp.dept_id
GROUP BY dept.deptno
ORDER BY count ASC;

查询人数大于等于3的部门，并按照人数降序排序

-- 隐式内连接方式
SELECT dept.name, count(emp.eid) count
FROM dept,
     emp
WHERE dept.deptno = emp.dept_id
GROUP BY dept.deptno
HAVING count >= 3
ORDER BY count DESC;

-- 显式内连接方式
SELECT dept.name, count(emp.eid) count
FROM dept
         JOIN
     emp
     ON dept.deptno = emp.dept_id
GROUP BY dept.deptno
HAVING count >= 3
ORDER BY count DESC;

3.5 外连接查询

  外连接分为左外连接、右外连接、满外连接。需要注意的是，在Oracle中有 full join，但是在 mysql 中对 full join 支持不友好，可以使用 union 来达到目的。

通俗说，在mysql中满外连接可以通过求左外连接与右外连接的并集实现。

🐶 来看几个例子吧：

1️⃣ 查询哪些部门有员工，哪些部门没有员工

分析： 使用左外连接，将左表所有数据输出，右表对应的数据输出，没有则补 null。

SELECT * FROM dept LEFT OUTER JOIN emp ON dept.deptno = emp.dept_id;

2️⃣ 查询哪些员工有对应的部门，哪些没有

分析： 使用右外连接，将右表所有数据输出，左表对应的数据输出，没有则补 null。

SELECT * FROM dept RIGHT OUTER JOIN emp ON dept.deptno = emp.dept_id;

3️⃣ 利用 union 关键字实现求左外连接和右外连接的并集

分析： 使用满外连接，将左右表所有数据输出，对应位置没有则均补 null。

本质： union 实质上是将两表之间合并并且去重。

SELECT *
FROM dept
         LEFT OUTER JOIN emp ON dept.deptno = emp.dept_id
UNION
SELECT *
FROM dept
         RIGHT OUTER JOIN emp ON dept.deptno = emp.dept_id;

3.6 子查询

3.6.1 子查询说明与实操

🆔 简介：

  子查询就是 在一个完整的查询语句中，嵌套若干个不同功能的小查询， 从而一起完成复杂的查询的一种编写方式

🍑 子查询的返回结果分为如下四种：

• 单行单列：一个具体列的内容，可以理解为一个单值数据；
• 单行多列：返回一行数据中多个列的内容；
• 多行单列：返回多行记录之中同一列的内容，相当于给出了操作范围；
• 多行多列：查询返回的结果是一张临时表。

1️⃣ 查询年龄最大的员工信息，显示该员工的员工号、员工名字和年龄

分析： 利用子查询返回 单行单列——即查询最大年龄

SELECT eid, ename, age
FROM emp
WHERE age = (SELECT max(age) FROM emp);

2️⃣ 查询研发部和销售部的员工信息，包含员工号、姓名

方式1： 利用关联查询方式，先连接两张表后再根据条件进行查询

SELECT eid, ename
FROM dept
         JOIN emp ON dept.deptno = emp.dept_id AND (name in ('销售部', '研发部'));

方式2： 利用子查询返回 多行单列——即先查询研发部与销售部的部门号，然后再以此为条件查询员工信息

SELECT eid, ename
FROM emp
WHERE dept_id IN (SELECT deptno FROM dept WHERE name IN ('销售部', '研发部'));

3️⃣ 查询研发部30岁以下员工的所有信息（指员工表）

方式1： 利用关联查询方式，先连接两张表后再根据条件进行查询

SELECT *
FROM dept
         JOIN emp ON dept.deptno = emp.dept_id AND (dept.name = '研发部' AND emp.age 
方式2： 利用子查询返回 多行多列——即先在部门表中查询研发部信息，然后在员工表中查询小于30岁的员工信息，最后将两个查询的结果进行关联查询
SELECT *
FROM (SELECT *
      FROM dept
      WHERE name = '研发部') T1
         JOIN (SELECT *
               FROM emp
               WHERE age 

3.6.2 子查询中的关键字
3.6.2.1 ALL关键字
🆔 语法格式:
SELECT ... FROM ... WHERE c > ALL(查询语句);

🐘 说明：
ALL 与子查询返回的所有值比较；
ALL 可以与 = > >= 结合使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的所有数据；
⭕️ 操作：
🖊 查询年龄大于 1003 部门所有员工的员工信息
SELECT *
FROM emp
WHERE age > ALL (SELECT age FROM emp WHERE dept_id = '1003');


3.6.2.2 ANY 与 SOME关键字
🆔 语法格式:
SELECT ... FROM ... WHERE c > ANY(查询语句);

🐘 说明：

• ANY 与子查询返回的所有值比较，与 ALL 不同的是，ANY 只需要满足查询语句中的任一值符合即可；
• ANY 可以与 = > >= 结合使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任一数据；
• SOME 与 ANY作用一样，可以理解成ANY的别名。

⭕️ 操作：

🖊 查询年龄大于 1003 部门任一员工的员工信息

SELECT *
FROM emp
WHERE age > ANY (SELECT age FROM emp WHERE dept_id = '1003');

3.6.2.3 IN关键字

🆔 语法格式:

SELECT ... FROM ... WHERE c IN(查询语句);

🐘 说明：

1. IN 关键字，用于判断某个记录的值，是否在指定集合中；
2. 在 IN 关键字前加 NOT 可以取非。

⭕️ 操作：

🖊 查询研发部和销售部的员工信息

SELECT *
FROM emp
WHERE dept_id IN (SELECT deptno FROM dept WHERE name = '研发部' OR name = '销售部');

3.6.2.4 EXISTS关键字

🆔 语法格式:

SELECT ... FROM ... WHERE EXISTS(查询语句);

🐘 说明：

1. 该子查询如果至少返回一行数据，则该EXISTS()返回true，外层查询执行；
2. 该子查询如果没有数据返回，则该EXISTS()返回false，外层查询不执行。

Tips：EXISTS关键字运算效率比IN高，实际开发中更推荐使用。

⭕️ 操作：

🖊 查询公司是否有大于60岁的员工，有则输出

SELECT *
FROM emp
WHERE EXISTS (SELECT * FROM emp WHERE age > 60);

是不是困惑？为什么查询结果中有年龄小于60岁的呢？ 其实 EXISTS 只是判断子查询中是否有返回数据，在查询中，判断出有大于60岁的员工，因此 外层查询实际上是查询了所有员工的信息。

我们可以 通过取别名的方式，一条一条取数据，让外查询来决定条件是否成立，来达到只取年龄大于60岁的员工信息。

3.7 自关联查询

🆔 简介：

  MySQL有时在信息的查询时需要进行对表自身进行关联查询。即一张表和自己关联，将一张表当成多张表来使用。自相关查询时必须给表取别名。

🍓 语法：

-- 方式1
select 字段列表 from 表1 别名1, 表1 别名2 where 条件;

-- 方式2
select 字段列表 from 表1 别名1 [left] join 表1 别名2 on 条件;

🍑 使用场景举例：

比如员工表，员工中既有老板又有普通员工，这时候就可以通过自关联查询的方式，将员工表分成多个来进行关联查询，查询员工的老板是谁… …

写在最后

以上就是本文的全部内容了，是否有顺利帮助你解决问题？若是能给你带来学习上的帮助，请大家多多支持golang学习网！更多关于数据库的相关知识，也可关注golang学习网公众号。