【教程】MySQL数据库学习笔记（七）——多表操纵（连续更新） ...

写在前面：
如果文章对你有帮助，记得点赞关注加收藏一波，利于以后需要的时候复习，多谢支持！

---

【MySQL数据库学习】系列文章

第一章 《熟悉与环境搭建》
第二章 《数据类型》
第三章 《数据定义语言DDL》
第四章 《数据操纵语言DML》
第五章 《约束》
第六章 《数据查询语言DQL》
第七章 《多表操纵》

---

  

---

一、多表关系

（一）多表关系概念

在实际的项目中，通常需要举行处理多表数据，而多表的关系通常可以概括为以下几种。

• 一对一关系：例如一个门生只有一个身份证号，体现为一张表的一行对应另一张表的一行。但这种关系使用较少，由于通常一对一关系可以合成为一张表。
  
• 一对多关系：例如一个部分有着多个员工，体现为一张表的一行对应另一张表的多行。
  
• 多对多关系：例如门生和选课之间，一个门生可以选多节课，而一节课也可以被多个门生所选，体现在一张表对应另一张表的多行的同时，另一张表的一行也对应这张表的多行。通常多对多的关系需要中心表将其分割为一对多的关系。
  

（二）外键约束

外键约束会在表中创建一种关系，这种关系使得从表（子表）中的列（外键）引用主表（父表）中的列（主键或唯一键）。通过这种方式，可以确保子表中的数据在父表中有对应的条目。
这用于确保数据的一致性和完备性，具体而言，则是用于维护表与表之间的关系，确保在一个表中引用的值在另一个表中存在。
外键约束有着以下特点。

• 主表必须已经存在于数据库，或者是当前正在创建的表。
  
• 必须为主表定义主键。
  
• 主键不能包含空值，但允许在外键中出现空值。
  
• 在主表的表名后面指定列名或列名的组合，而这个列或者列组合必须是主表的主键或者候选键。
  
• 外键中列的数量必须和主键中列的数量相同。
  
• 外键中列的数据类型必须和主键中列的数据类型相同。
  

如果想要创建外键约束，有两种方式。
方式1：在创建表时设置外键约束。
在CREATE TABLE语句中，通过FOREIGN KEY关键字来指定外键，具体的语法格式如下。

1. CONSTRAINT 外键名 FOREIGN KEY 字段名1,字段名2,... REFERENCES 主表名 主键列1,主键列2,...

复制代码

下面是简单的示例。

1. USE mydb1;
3. -- 创建主表（部门表）
4. CREATE TABLE IF NOT EXISTS dept (
5.     did VARCHAR(20) PRIMARY KEY, -- 部门编号，设置主键
6.     name VARCHAR(20) -- 部门名字
7. );
9. -- 创建从表（员工表）
10. CREATE TABLE IF NOT EXISTS emp (
11.     eid VARCHAR(20) PRIMARY KEY, -- 员工编号，设置主键
12.     ename VARCHAR(20), -- 员工名字
13.     age INT, -- 员工年龄
14.     dept_id VARCHAR(20), -- 员工所属部门编号
15.     CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept(did) -- 外键约束
16. );

复制代码

创建完外键约束后，可以通过模子检察外键约束关系。点击表，选中两个表，右键选择“逆向表到模子”即可检察。

可以看到，两张表之间的外键约束已经创建。

除此之外，另有另一种创建外键约束的方式。
方式2：在修改表时设置外键约束。
在ALTER TABLE语句中，通过FOREIGN KEY关键字来指定外键，具体的语法格式如下。

1. ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY 字段名1,字段名2,... REFERENCES 主表名 主键列1,主键列2,...

复制代码

下面是简单的示例。

1. -- 创建主表（部门表）
2. CREATE TABLE IF NOT EXISTS dept (
3.     did VARCHAR(20) PRIMARY KEY, -- 部门编号，设置主键
4.     name VARCHAR(20) -- 部门名字
5. );
7. -- 创建从表（员工表）
8. CREATE TABLE IF NOT EXISTS emp (
9.     eid VARCHAR(20) PRIMARY KEY, -- 员工编号，设置主键
10.     ename VARCHAR(20), -- 员工名字
11.     age INT, -- 员工年龄
12.     dept_id VARCHAR(20), -- 员工所属部门编号
13. );
15. -- 创建外键约束
16. ALTER TABLE emp ADD CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept(did);

复制代码

1.一对多关系

为了验证外键约束的作用，首先应该将上面创建的两张空表，举行一对多关系的数据添补。

1. -- 1.添加主表数据
2. INSERT INTO dept VALUES ('1001','研发部');
3. INSERT INTO dept VALUES ('1002','销售部');
4. INSERT INTO dept VALUES ('1003','财务部');
5. INSERT INTO dept VALUES ('1004','人事部');
7. -- 2.添加从表数据
8. INSERT INTO emp VALUES ('1','刘邦',25,'1001');
9. INSERT INTO emp VALUES ('2','樊哙',24,'1001');
10. INSERT INTO emp VALUES ('3','张良',26,'1001');
11. INSERT INTO emp VALUES ('4','韩信',25,'1002');
12. INSERT INTO emp VALUES ('5','萧何',27,'1002');
13. INSERT INTO emp VALUES ('6','曹参',23,'1003');
14. INSERT INTO emp VALUES ('7','陈平',26,'1003');
15. INSERT INTO emp VALUES ('8','周勃',28,'1004');

复制代码

注意，当删除数据的时候，有外键依赖的主表数据是不能删除的，除非先扫除从表中依赖主表的外键，否则会报错。但反之，从表中的外键都是可以随意删除的。
而如果希望删除外键约束时，需要在ALTER TABLE语句中使用DROP关键字来删除外键约束。具体语法如下所示。

1. ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名

复制代码

简单的实现示例则如下所示。

1. ALTER TABLE emp DROP FOREIGN KEY emp_fk;

复制代码

这样就可以大概删除刚才在上面的示例中在从表emp中创建的emp_fk外键约束。
2.多对多关系

对于多对多关系，比如之前提到的门生和选课的关系，此时门生表和选课表都是主表，而简化其关系的中心表则是从表，此中的外键列依赖于门生表和选课表两个主表。
具体的实现示例如下。

1. -- 创建学生表（主表）
2. CREATE TABLE IF NOT EXISTS student (
3.     sid INT PRIMARY KEY auto_increment, -- 学生编号
4.     name VARCHAR(20), -- 学生姓名
5.     age INT, -- 学生年龄
6.     gender VARCHAR(20) -- 学生性别
7. );
8. -- 创建课程表（主表）
9. CREATE TABLE IF NOT EXISTS course (
10.     cid INT PRIMARY KEY auto_increment, -- 课程编号
11.     cname VARCHAR(20) -- 课程名
12. );
13. -- 创建中间表（从表）
14. CREATE TABLE IF NOT EXISTS score (
15.     sid INT,
16.                 cid INT,
17.                 score DOUBLE
18. );
20. -- 创建外键约束
21. ALTER TABLE score ADD FOREIGN KEY (sid) REFERENCES student(sid);
22. ALTER TABLE score ADD FOREIGN KEY (cid) REFERENCES course(cid);
24. -- 学生表数据填充
25. INSERT INTO student VALUES (1,'刘邦',21,'男'),(2,'吕雉',19,'女'),(3,'项羽',20,'男');
26. -- 课程表数据填充
27. INSERT INTO course VALUES (1,'语文'),(2,'数学'),(3,'英语');
28. -- 中间表数据填充
29. INSERT INTO score VALUES (1,1,78),(1,2,75),(2,1,88),(2,3,90),(3,2,80),(3,3,65);

复制代码

检察表的模子即可看到外键约束已创建完毕。

二、多表联合查询

多表联合查询（也称为联接查询）用于从多个表中检索相干数据，由于在实际项目需要时，大概需要表现的查询结果来自于两个或两个以上的表。
多表查询有以下分类。

• 交织连接查询
  
• 内连接查询
  
• 外连接查询
  
• 子查询
  
• 表自关联
  

作为使用的数据，仍旧主要沿用上面的部分和员工表示例，只不过不参加外键约束。

1. CREATE TABLE IF NOT EXISTS dept (
2.     did VARCHAR(20) PRIMARY KEY, -- 部门编号，设置主键
3.     name VARCHAR(20) -- 部门名字
4. );
5. CREATE TABLE IF NOT EXISTS emp (
6.     eid VARCHAR(20) PRIMARY KEY, -- 员工编号，设置主键
7.     ename VARCHAR(20), -- 员工名字
8.     age INT, -- 员工年龄
9.     dept_id VARCHAR(20) -- 员工所属部门编号
10. );
11. INSERT INTO dept VALUES
12. ('1001','研发部'),
13. ('1002','销售部'),
14. ('1003','财务部'),
15. ('1004','人事部');
16. INSERT INTO emp VALUES
17. ('01','刘邦',25,'1001'),
18. ('02','樊哙',24,'1001'),
19. ('03','张良',26,'1001'),
20. ('04','韩信',25,'1001'),
21. ('05','萧何',27,'1002'),
22. ('06','曹参',23,'1002'),
23. ('07','陈平',26,'1002'),
24. ('08','周勃',28,'1003'),
25. ('09','彭越',27,'1003'),
26. ('10','吕雉',24,'1005');

复制代码

（一）交织连接查询

交织连接（Cross Join） 是 SQL 中的一种连接类型，它返回两个表的笛卡尔积，可以理解为一张表的每一行都和另一张表的任意一行举行匹配（如果A表有m行数据，B表有n行数据，则返回m*n行数据）。笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的底子上举行条件筛选。
其语法格式为以下所示。

1. SELECT * FROM 表1,表2,...

复制代码

具体实现示例如下所示。

1. SELECT * FROM dept,emp;

复制代码

返回结果如下。

（二）内连接查询

内连接（INNER JOIN） 是 SQL 中最常用的连接类型之一，用于从两个或多个表中提取符合条件的记录。内连接只返回满足连接条件的记录，实际上是求的两张表的交集，可以将表中的相干数据组合在一起，从而举行更加复杂的查询和分析。
其具体语法格式如下所示。

1. -- 隐式内连接
2. SELECT * FROM A表,B表 WHERE 条件; -- 可以理解为从笛卡尔积中筛选出符合条件的值
3. -- 显式内连接
4. SELECT * FROM A表 INNER JOIN B表 ON 条件; -- INNER可省略

复制代码

具体示例如下所示。

1. -- 查询每个部门的所属员工
2. SELECT * FROM dept,emp WHERE dept.did = emp.dept_id;
3. SELECT * FROM dept INNER JOIN emp ON dept.did = emp.dept_id;
5. -- 查询研发部和销售部的所属员工
6. SELECT * FROM dept,emp WHERE dept.did = emp.dept_id AND name IN ('研发部','销售部');
7. SELECT * FROM dept INNER JOIN emp ON dept.did = emp.dept_id AND name IN ('研发部','销售部');
9. -- 查询每个部门的员工数，并升序排序
10. SELECT a.name,count(*) FROM dept a JOIN emp b ON a.did = b.dept_id GROUP BY a.did;
12. -- 查询人数大于3的部门，并按照人数降序排序
13. 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，一个永久在学习中的探索者，关注我，让我们一起进步！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。