#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一

create table department(

id int primary key,

name varchar(20) not null

)engine=innodb;

#dpt_id外键，关联父表（department主键id），同步更新，同步删除

create table employee(

id int primary key,

name varchar(20) not null,

dpt_id int,

constraint fk_name foreign key(dpt_id)

references department(id)

on delete cascade

on update cascade

)engine=innodb;

#先往父表department中插入记录

insert into department values

(1,'欧德博爱技术有限事业部'),

(2,'艾利克斯人力资源部'),

(3,'销售部');

#再往子表employee中插入记录

insert into employee values

(1,'egon',1),

(2,'alex1',2),

(3,'alex2',2),

(4,'alex3',2),

(5,'李坦克',3),

(6,'刘飞机',3),

(7,'张火箭',3),

(8,'林子弹',3),

(9,'加特林',3)

;

#删父表department，子表employee中对应的记录跟着删

mysql> delete from department where id=3;

mysql> select * from employee;

+----+-------+--------+

| id | name  | dpt_id |

+----+-------+--------+

|  1 | egon  |      1 |

|  2 | alex1 |      2 |

|  3 | alex2 |      2 |

|  4 | alex3 |      2 |

+----+-------+--------+

#更新父表department，子表employee中对应的记录跟着改

mysql> update department set id=22222 where id=2;

mysql> select * from employee;

+----+-------+--------+

| id | name  | dpt_id |

+----+-------+--------+

|  1 | egon  |      1 |

|  3 | alex2 |  22222 |

|  4 | alex3 |  22222 |

|  5 | alex1 |  22222 |

+----+-------+--------+

 示范

=====================多对一=====================

create table press(

id int primary key auto_increment,

name varchar(20)

);

create table book(

id int primary key auto_increment,

name varchar(20),

press_id int not null,

foreign key(press_id) references press(id)

on delete cascade

on update cascade

);

insert into press(name) values

('北京工业地雷出版社'),

('人民音乐不好听出版社'),

('知识产权没有用出版社')

;

insert into book(name,press_id) values

('九阳神功',1),

('九阴真经',2),

('九阴白骨爪',2),

('独孤九剑',3),

('降龙十巴掌',2),

('葵花宝典',3)

;

=====================多对多=====================

create table author(

id int primary key auto_increment,

name varchar(20)

);

#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了

create table author2book(

id int not null unique auto_increment,

author_id int not null,

book_id int not null,

constraint fk_author foreign key(author_id) references author(id)

on delete cascade

on update cascade,

constraint fk_book foreign key(book_id) references book(id)

on delete cascade

on update cascade,

primary key(author_id,book_id)

);

#插入四个作者，id依次排开

insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

#每个作者与自己的代表作如下

egon:

九阳神功

九阴真经

九阴白骨爪

独孤九剑

降龙十巴掌

葵花宝典

alex:

九阳神功

葵花宝典

yuanhao:

独孤九剑

降龙十巴掌

葵花宝典

wpq:

九阳神功

insert into author2book(author_id,book_id) values

(1,1),

(1,2),

(1,3),

(1,4),

(1,5),

(1,6),

(2,1),

(2,6),

(3,4),

(3,5),

(3,6),

(4,1)

;

#一定是student来foreign key表customer，这样就保证了：

#1 学生一定是一个客户，

#2 客户不一定是学生，但有可能成为一个学生

create table customer(

id int primary key auto_increment,

name varchar(20) not null,

qq varchar(10) not null,

phone char(16) not null

);

create table student(

id int primary key auto_increment,

class_name varchar(20) not null,

customer_id int unique, #该字段一定要是唯一的

foreign key(customer_id) references customer(id) #外键的字段一定要保证unique

on delete cascade

on update cascade

);

#增加客户

insert into customer(name,qq,phone) values

('李飞机','',13811341220),

('王大炮','',15213146809),

('守榴弹','',1867141331),

('吴坦克','',1851143312),

('赢火箭','',1861243314),

('战地雷','',18811431230)

;

#增加学生

insert into student(class_name,customer_id) values

('脱产3班',3),

('周末19期',4),

('周末19期',5)

;