MySQL笔记——外键约束和表关系（一对一，多对一，多对多）

一、外键约束

		概念：
			外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。
			
		语法：
			（1）添加约束
				-- 创建表是添加外键约束
				CREATE TABLE 表名(
					列名 数据类型,
					…
					[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCE 主表(主表列名)
				);
				
				-- 建完表后添加外键约束
				ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCE 主表名称(主表列名称)
				
			（2）删除约束
				ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

案例

首先创建员工信息表和部门信息表，并在员工表中添加外键。（把员工表当作从表，部门表当作主表），员工表和部门表就是一个多对一的案例

-- 员工表
CREATE TABLE emp(
	id int PRIMARY KEY auto_increment, -- id
	name VARCHAR(20),-- 姓名
	age int, -- 年龄
	dep_id int, -- 部门
	-- 添加外键dep_id，关联dept表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);

-- 部门表
CREATE TABLE dept(
	id int PRIMARY KEY auto_increment, -- 部id
	dep_name VARCHAR(20), -- 部门名称
	addr VARCHAR(20) -- 地址
);


INSERT INTO emp(NAME,age,dep_id) VALUES
('张三',20,1),
('李四',20,1),
('王五',20,1),
('赵六',20,2),
('田七',22,2),
('周八',18,2);

INSERT INTO dept(dep_name,addr) VALUES
('研发部','广州'),
('销售部','深圳');

-- 删除外键
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;

按照如下图所示顺序执行sql语句：部门表–>员工表–>部门信息–>员工信息

执行完毕后如下图点击对象选中这两个表，点击鼠标右键，选中‘逆向表到模型’。

可以看到这两个表的模型中间有一条线，这条线代表外键。

此时我们执行删除外键的语句

然后再次执行上述‘逆向表到模型’ 可以看到线消失了，表示我们删除成功。

二、表关系之一对多

1、一对多（多对一）
	如：部门和员工，一个部门对应多个员工，一个员工对应一个部门
	
2、实现方式：在多的一方建立外键，指向一的一方的主键

3、具体实现参照上例，部门和员工。

三、表关系之多对多

	1、多对多
		如：商品和订单，一个商品对应多个订单，一个订单包含多个商品
		
	2、实现方式
		建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
	
	3、实现案例：

-- 订单表
CREATE TABLE tb_order(
	id int PRIMARY KEY auto_increment, -- id
	payment double(10,2),
	payment_type TINYINT,
	status TINYINT
);

-- 商品表
CREATE TABLE tb_goods(
	id int PRIMARY KEY auto_increment,
	title VARCHAR(100),
	price DOUBLE(10,2)
);


-- 订单商品中间表
CREATE TABLE tb_order_goods(
	id int PRIMARY KEY auto_increment,
	order_id int,
	goods_id int,
	count int
);

-- 添加外键，向订单商品中间表添加两个外键，关联商品表和订单表的主键。
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_order_id FOREIGN KEY(order_id) REFERENCES tb_order(id);
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_goods_id FOREIGN KEY(goods_id) REFERENCES tb_goods(id);

如图执行创建三个表

执行添加外键的语句

再次执行上述‘逆向表到模型’  具体对应关系如图标注。

四、表关系之一对一

	1、一对一
		如：用户信息和用户详情信息
		一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询能力
	
	2、实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一（UNIQUE）
	
	3、实现案例

-- 创建用户表
CREATE TABLE tb_user(
	id int PRIMARY KEY auto_increment,
	photo VARCHAR(30),
	nickname VARCHAR(20),
	age int,
	sex char(2),
	desc_id int NOT NULL UNIQUE -- 设置关联的用户详情表主键不为空且唯一
);

-- 创建用户详情表

CREATE TABLE tb_user_desc(
	id int PRIMARY KEY auto_increment,
	city VARCHAR(10),
	edu VARCHAR(10),
	income DOUBLE(7,2),
	status VARCHAR(10)
);


-- 添加用户
INSERT INTO TABLE tb_user(photo,nickname,age,sex,desc_id) VALUES
('a.jpg','一场梦',23,'女',1),
('b.png','风清扬',35,'男',2),
('c.jpg','赵云',41,'男',3),

-- 添加用户详情信息
INSERT INTO TABLE tb_user_desc(city,edu,income,status) VALUES
('广州','硕士',300000,'单身'),
('湖北','本科',100000,'离异'),
('河南','本科',200000,'单身'),

-- 添加外键
ALTER TABLE tb_user ADD CONSTRAINT fk_user_userdesc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id);

如图执行语句创建用户表和用户详情表

往两张表中添加数据

添加外键

再次执行上述‘逆向表到模型’  具体对应关系如图。