MySQL多表操作

多表关系

概念：在实际开发中，一个项目通常需要多张表才能完成。
多表关系可以概括为：一对一、一对多/多对一，多对多

一对一关系

• 一个学生只有一张身份证，一张身份证只能对应一个学生
• 在任一表中添加唯一外键，指向另一方主键，确保一对一关系
• 一般一对一关系很少见，遇到一对一关系的表最好是合并表

一对多/多对一

• 一个部门有多个员工，一个员工只能对应一个部门
• 再多的一方建立外键，指向一的一方的主键

多对多关系

• 一个学生可以选择很多门课程，一个课程也可以被很多学生选择
• 多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键

外键约束

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

特点
定义一个外键时，需要遵循下列规则：

• 主表必须已经存在于数据库中，或者是当前正在创建的表
• 必须为主表定义主键
• 逐渐不能包含控制，但允许在外键中出现空值。也就是说，主子要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的
• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键
• 外键中列的数目必须和主表的主键中列的数目相同
• 外键中列的数据类型必须和主表主键中对应的数据类型相同

创建外键约束

方法一：
[constraint<外键名>] foreign key 字段名 [,字段名2，……] references <主表名> 主键列1[,主键列2……]

例如：

-- 创建部门表，主表
CREATE TABLE IF NOT EXISTS dept(
deptno VARCHAR(20) PRIMARY KEY,
name VARCHAR(20)
);

-- 创建员工表，从表，并创建dept_id外键约束
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)
); 

方法二：
alter table <数据表名> add constraint <外键名> foreign <列名> references <主表名>(<列名>)；

例如：

-- 创建部门表，主表
CREATE TABLE IF NOT EXISTS dept2(
deptno VARCHAR(20) PRIMARY KEY,
name VARCHAR(20)
);

-- 创建员工表，从表，并创建dept_id外键约束
CREATE TABLE IF NOT EXISTS emp2(
eid VARCHAR(20) PRIMARY KEY,
ename VARCHAR(20),
age INT,
dept_id VARCHAR(20)
); 

ALTER TABLE emp2 add CONSTRAINT emp2_fk FOREIGN KEY(dept_id) REFERENCES dept2(deptno);

数据插入

-- 外键约束操作
-- 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','乔峰',20,'1001');
INSERT INTO emp VALUES('2', '段誉', 21, '1001');
INSERT INTO emp VALUES('3', '虚竹', 23, '1001');
INSERT INTO emp VALUES('4', '阿紫', 18, '1002');
INSERT INTO emp VALUES('5', '扫地僧', 35, '1002');
INSERT INTO emp VALUES('6', '李秋水', 33, '1003');
INSERT INTO emp VALUES('7', '鸠摩智', 50, '1003');

数据删除

-- 3.删除数据
-- 注：
-- 1.主表的数据被从表依赖时，不能删除
-- 2.从表的数据可以随便删除

DELETE FROM dept WHERE deptno=1004;
DELETE FROM emp	 WHERE eid='7';

删除外键约束

概念：当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会接触主表与从表的关联关系。

方法：
alter table <表名>drop foreign key<外键约束名>;
例如：

ALTER TABLE emp2 drop FOREIGN KEY emp2_fk;

多对多关系

在多对多关系中，A表的一行对应B的多行，B的一行对应A的多行，我们姚新增加一个中间表，来建立多对多的关系。

-- 创建学生表student(左侧主表)
CREATE TABLE IF NOT EXISTS	student(
sid INT PRIMARY KEY auto_increment,
name VARCHAR(20),
age INT,
gender VARCHAR(20)
);

-- 创建课程表course（右侧主表）
CREATE TABLE course(
cid INT PRIMARY KEY auto_increment,
cidname VARCHAR(20)
);

-- 创建中间表student_course