MySQL进阶使用

【第一章】约束【重点】

外键约束

问题：什么是外键约束以及外键约束的作用是什么？

当一个表中需要使用到另一个表的的数据时，但是又要保证数据的正确性，可以是用外键约束。如下图。

外键约束语法

CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)

添加外键

建表时添加外键

-- 创建user用户表(主表)
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
	NAME VARCHAR(20) NOT NULL             -- 姓名
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');

-- 创建orderlist订单表(从表)
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
	number VARCHAR(20) NOT NULL,          -- 订单编号
	uid INT,			      -- 外键列
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加订单数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
(NULL,'hm003',2),(NULL,'hm004',2);

建表后添加外键

-- 语法
alter table 表名 add constraint 外键名称 foreign key(外键列) references 主表(主键);
-- orderlist中的uid关联user表中的id列
alter table orderlist add constraint fk foreign key(uid) references user(id);

删除外键

-- 语法
alter table 表名 drop foreign key 外键名称;
-- 删除orderlist表中的外键
alter table orderlist drop foreign key ou_fk1;

外键级联操作(了解)

外键让多个表之间产生了关系，但是如果主表中的数据所有改变，从表中关联的数据也必须跟着改变。这叫做外键级联操作。

-- 添加外键级联更新、级联删除
-- ON UPDATE CASCADE 级联更新
-- ON DELETE CASCADE 级联删除
ALTER TABLE 从表 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键列) REFERENCES 主表(主键列) ON UPDATE CASCADE ON DELETE CASCADE;

【第二章】多表操作【重点】

多表关系

问题1：多表关系有哪几种？

问题2：思考：学生和班级属于哪一种？商品和订单属于哪一种？

在实际项目开发的时候可能设计到多张表，表与表之间存在一定关系，如何设计表之间的关系让系统更加优化，需要考虑到多表之间关系，学习多表关系主要就是学习多表的建表原则。

1. 一对一(了解)
	* 如：人和身份证
	* 分析：一个人只有一个身份证，一个身份证只能对应一个人
2. 一对多(多对一)
	* 如：部门和员工
	* 分析：一个部门有多个员工，一个员工只能对应一个部门
3. 多对多
	* 如：学生和课程
	* 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

一对一关系

问题：一对一关系中如何添加外键？

一个人有一个身份证号码，一个身份证号码只对应一个人

 

-- 创建身份证表
CREATE TABLE card(
	id INT PRIMARY KEY,
	number VARCHAR(18)
);
-- 设置身份证号码唯一
ALTER TABLE card MODIFY number VARCHAR(20) UNIQUE;

INSERT INTO card(id,number) VALUES(1,420923199211090512);
INSERT INTO card(id,number) VALUES(2,420923199310230807);
INSERT INTO card(id,number) VALUES(3,420923199007080908);

-- 创建人员表
CREATE TABLE person(
	id INT PRIMARY KEY,
	NAME VARCHAR(20),
	cid INT
);
-- 给人员表添加外键
ALTER TABLE person ADD CONSTRAINT cid_kf FOREIGN KEY(cid) REFERENCES card(id);
-- 设置外键唯一
ALTER TABLE person MODIFY cid INT UNIQUE;

-- 向person表中添加数据
INSERT INTO person(id,NAME,cid) VALUES(1,"张三",1);
INSERT INTO person(id,NAME,cid) VALUES(2,"李四",2);
INSERT INTO person(id,NAME,cid) VALUES(3,"王五",3);

==建表原则：在任意一个表建立唯一(UNIQUE)外键，去关联另外一个表的主键。==

一对多关系

问题：一对多关系中如何添加外键？

一个部门可以有多个员工，一个员工只能对应一个部门

-- 创建部门表(主表)
CREATE TABLE department(
	id INT PRIMARY KEY,
	NAME VARCHAR(20)
);
-- 向部门表中添加数据
INSERT INTO department(id,NAME) VALUES(1,"教研部");
INSERT INTO department(id,NAME) VALUES(2,"学工部");
INSERT INTO department(id,NAME) VALUES(3,"就业部");

SELECT * FROM department;

-- 创建员工表(从表)