【教程】MySQL数据库学习笔记（七）——多表操纵（连续更新）
https://img-blog.csdnimg.cn/direct/b66561aa9c7645dcb67da59cd21c4e82.png#pic_center写在前面：
如果文章对你有帮助，记得点赞关注加收藏一波，利于以后需要的时候复习，多谢支持！
【MySQL数据库学习】系列文章
第一章 《熟悉与环境搭建》
第二章 《数据类型》
第三章 《数据定义语言DDL》
第四章 《数据操纵语言DML》
第五章 《约束》
第六章 《数据查询语言DQL》
第七章 《多表操纵》
一、多表关系
（一）多表关系概念
在实际的项目中，通常需要举行处理多表数据，而多表的关系通常可以概括为以下几种。
[*]一对一关系：例如一个门生只有一个身份证号，体现为一张表的一行对应另一张表的一行。但这种关系使用较少，由于通常一对一关系可以合成为一张表。
[*]一对多关系：例如一个部分有着多个员工，体现为一张表的一行对应另一张表的多行。
[*]多对多关系：例如门生和选课之间，一个门生可以选多节课，而一节课也可以被多个门生所选，体现在一张表对应另一张表的多行的同时，另一张表的一行也对应这张表的多行。通常多对多的关系需要中心表将其分割为一对多的关系。
（二）外键约束
外键约束会在表中创建一种关系，这种关系使得从表（子表）中的列（外键）引用主表（父表）中的列（主键或唯一键）。通过这种方式，可以确保子表中的数据在父表中有对应的条目。
这用于确保数据的一致性和完备性，具体而言，则是用于维护表与表之间的关系，确保在一个表中引用的值在另一个表中存在。
外键约束有着以下特点。
[*]主表必须已经存在于数据库，或者是当前正在创建的表。
[*]必须为主表定义主键。
[*]主键不能包含空值，但允许在外键中出现空值。
[*]在主表的表名后面指定列名或列名的组合，而这个列或者列组合必须是主表的主键或者候选键。
[*]外键中列的数量必须和主键中列的数量相同。
[*]外键中列的数据类型必须和主键中列的数据类型相同。
如果想要创建外键约束，有两种方式。
方式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) -- 外键约束
);
创建完外键约束后，可以通过模子检察外键约束关系。点击表，选中两个表，右键选择“逆向表到模子”即可检察。
https://i-blog.csdnimg.cn/direct/4e3c80ee4baf44369ab542e1ab82c36a.png#pic_center
可以看到，两张表之间的外键约束已经创建。
https://i-blog.csdnimg.cn/direct/a08a0825dc604dfaaabdc9bb67af7952.png#pic_center
除此之外，另有另一种创建外键约束的方式。
方式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);
检察表的模子即可看到外键约束已创建完毕。
https://i-blog.csdnimg.cn/direct/fec0c1115385405bb0fd7305e1c26ae7.png#pic_center
二、多表联合查询
多表联合查询（也称为联接查询）用于从多个表中检索相干数据，由于在实际项目需要时，大概需要表现的查询结果来自于两个或两个以上的表。
多表查询有以下分类。
[*]交织连接查询
[*]内连接查询
[*]外连接查询
[*]子查询
[*]表自关联
作为使用的数据，仍旧主要沿用上面的部分和员工表示例，只不过不参加外键约束。
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;
返回结果如下。
https://i-blog.csdnimg.cn/direct/768460a7985d44ccbcbf91ee9c99a6d0.png#pic_center
（二）内连接查询
内连接（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，一个永久在学习中的探索者，关注我，让我们一起进步！
https://img-blog.csdnimg.cn/direct/469a9865f78e4b8ea10e40a9825494d5.png#pic_center
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]