MySQL数据管理（级联的应用）

外键约束（保证两表的数据一致性）
约束表，主键，主表
非约束表，从表

添加外键约束
alter table 从表 add constraint fk_sub_gra foreign key (从表列名) references 主表（主表列）
删除外键
alter table 从表 drop foreign key fk_sub_gra;

注意创建外键的时候，有时会默认创建一个索引，提高查询效率的是索引而不是外键

作业：如何实现删主表的信息自动删除 级联操作

---

---

插入数据
insert into 表名
(各个列名)
values(一 一对应);

insert into 表名
values(一 一对应);

删除数据(or and <>)
delete from subject where SubjectNo=9;

修改数据
update ‘subject’ set 修改内容 where 条件

in 包涵的内容选择
between and从哪到哪

alter table student convert to character set utf8;

---

	级联

– 级联中的级联删除和级联删除置空以及级联更新，级联更新置空
– person户口信息表（从表）和nativePlace籍贯表（主表）

– 创建数据库
CREATE DATABASE test_person;

– 首先创建从表person

CREATE TABLE person(
id  INT AUTO_INCREMENT PRIMARY KEY COMMENT '编号',
idCard VARCHAR(20) UNIQUE NOT NULL COMMENT '身份证号',
p_name VARCHAR(20) COMMENT '姓名',
p_gender VARCHAR(2) COMMENT '性别',
p_birthday DATETIME COMMENT '出生日期',
p_nation VARCHAR(30) COMMENT '名族',
p_nativePlaceId INT COMMENT '籍贯'
)CHARSET =`utf8`,COMMENT='户口信息表';
SELECT * FROM person;

– 然后创建主表nativePlace

CREATE TABLE nativePlace(
	id AUTO_INCREMENT INT PRIMARY KEY  COMMENT '编号',
	province VARCHAR(30) COMMENT '省份',
	city VARCHAR(30) COMMENT '市/区',
	county VARCHAR(30) COMMENT '县',
	Township VARCHAR(30) DEFAULT '不详' COMMENT '乡/镇',
	address VARCHAR(100) COMMENT '家庭住址'
)COMMENT = '籍贯表';

– 给主表添加数据

INSERT INTO nativePlace VALUES
(DEFAULT,'江西省', '赣州市', '于都县', DEFAULT, '渡江大道66666号'),
(DEFAULT,'北京市', '海定区', 'A县', DEFAULT, '黄埔路8888号'),
(DEFAULT,'湖南省', '长沙市', 'C县', DEFAULT, '南京路9999号'),
(DEFAULT,'湖北省', '武汉市', 'D县', DEFAULT, '人民路5555号'),
(DEFAULT,'江苏省', '南京市', 'F县', DEFAULT, '长安路8686号');

– 忘记更改编码格式，就用下面的代码更改

ALTER TABLE nativePlace CONVERT TO CHARACTER SET utf8; 
-- 删除数据从新添加，但是id得从1开始，就需要用到truncate

– 修改属性nativeplace

ALTER TABLE nativePlace MODIFY Township VARCHAR(30) DEFAULT '不详' COMMENT '乡/镇';

– 传统添加外键

ALTER TABLE person ADD CONSTRAINT fk_per_nat FOREIGN KEY(p_nativePlaceId) REFERENCES nativePlace(`id`)

– 删除外键约束

ALTER TABLE person DROP FOREIGN KEY fk_per_nat;

– 删除外键索引

ALTER TABLE person DROP INDEX fk_per_nat;

#理论上如果想删除主表，那我们需要先删除从表
#但是呢，我们可以通过级联删除来强制删除主表
#注意啦注意啦：级联删除和级联置空是写在从表的，也就是设置外键的时候设置的，具体如下：
#方式一：级联删除（删除主表记录的同时删除从表相关记录，属于强制性（心狠手辣型）：从表伴生于主表）

#其实就是在传统添加外键的基础上加上关键词 on delete cascade层级

ALTER TABLE person ADD CONSTRAINT fk_per_nat FOREIGN KEY(p_nativePlaceId) REFERENCES nativePlace(`id`) ON DELETE CASCADE;

#接下来咱们试试行不行
#咱就直接删除主键里面的信息

DELETE  FROM nativePlace WHERE id=4;

#经过测试达到老师的要求删除主表信息后，从表相关数据也消失

#删除外键约束

 ALTER TABLE person DROP FOREIGN KEY fk_per_nat;

#看看目前几个键_只有咱们设定的几个键：主键，唯一键，外键

SHOW INDEX FROM person;

#删除外键索引

ALTER TABLE person DROP INDEX fk_per_nat;

#直接看看还有几个索引

SHOW INDEX FROM person;

#方式二：级联置空(手下留情型：删除主表记录的同时将从表相关记录的外键的值重置为null，但是不影响其他属性的数据信息)
#首先创建外键

ALTER TABLE person  ADD CONSTRAINT fk_per_nat FOREIGN KEY (p_nativePlaceId) REFERENCES nativePlace(`id`) ON DELETE SET NULL;

#咱们再次试试删除主表的id为3的
DELETE FROM nativePlace WHERE id=3;
#删除外键约束

 ALTER TABLE person DROP FOREIGN KEY fk_per_nat;

#删除外键索引

ALTER TABLE person DROP INDEX fk_per_nat;

– 级联更新

ALTER TABLE person ADD CONSTRAINT fk_per_nat FOREIGN KEY(p_nativePlaceId) REFERENCES nativePlace(`id`) ON UPDATE CASCADE;

– 咱们试试更改主表中的信息，再看从表有何变化

UPDATE `nativeplace` SET id=1111 WHERE id=1;

#经过测验我们发现，从表中的原p_nativePlaceId=1的值也全部【被】更新成了1111

#删除外键约束

 ALTER TABLE person DROP FOREIGN KEY fk_per_nat;

#删除外键索引
ALTER TABLE person DROP INDEX fk_per_nat;

– 级联更新置空

ALTER TABLE person ADD CONSTRAINT fk_per_nat FOREIGN KEY(p_nativePlaceId) REFERENCES nativePlace(`id`) ON UPDATE SET NULL;

– 咱们试试更改主表中的信息，再看从表有何变化

UPDATE `nativeplace` SET id=1 WHERE id=1111;

#经过测验我们发现，从表中的原p_nativePlaceId=1111的值全部【被】更新置空成了null，同删除置空一样，只单纯的置空了外键值为null，其他属性值不变

#删除外键约束

ALTER TABLE person DROP FOREIGN KEY fk_per_nat;

#删除外键索引

ALTER TABLE person DROP INDEX fk_per_nat;

SHOW INDEX FROM person;

清空表数据的

TRUNCATE TABLE person;
TRUNCATE TABLE nativePlace;
SELECT * FROM person;
SELECT * FROM nativePlace;