Mysql外键对应关系

一.外键:
foreign key 注意：
1.被关联的字段必须是一个key，通常是id字段

2.创建表时：必须先建立被关联的表，才能建立关联表
外键：
constraint 外键名字 （可以不写，不写系统会自动分配）。
foreign key (当前表需要关联的id，直接写自己设置的id)
references 被关联的表名（被关联表的id）

3.插入记录时：必须先往被关联的表插入记录，才能往关联表中插入记录
删除时：应该先删除关联表中的记录，再删除被关联对应的记录

4.更新和删除同步：on update cascade on delete cascade

5.必须先删除关联的表 才能删被关联的表
二.下面举例说明：
多对一
1.先创建被关联表：

create table dep(
id int primary key auto_increment,
dep_name varchar(20),
dep_info varchar(20)
);

2.创建关联表：

create table emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
constraint fk_emp_dep
foreign key(dep_id) references dep(id)
on update cascade on delete cascade);

3.插入被关联表的内容：

insert into dep(dep_name,dep_info)values
('python','python_course'),
('music','music_course'),
('java','java_course');

4.插入关联表内容：

insert into emp(name,age,dep_id) values
('dahai',18,1),
('xishi',19,2),
('zhuge',23,3),
('xialuo',24,1),
('zhouyu',21,3);

5.查询：

select * from emp,dep where emp.dep_id=dep.id;

+----+--------+------+--------+----+----------+---------------+
| id | name | age | dep_id | id | dep_name | dep_info |
+----+--------+------+--------+----+----------+---------------+
| 1 | dahai | 18 | 1 | 1 | python | python_course |
| 4 | xialuo | 24 | 1 | 1 | python | python_course |
| 2 | xishi | 19 | 2 | 2 | music | music_course |
| 3 | zuge | 23 | 3 | 3 | java | java_course |
| 5 | zhouyu | 21 | 3 | 3 | java | java_course |
+----+--------+------+--------+----+----------+---------------+

2.多对多

关系：
author 多对多 book
author2book 多对一 author
author2book 多对一 book

解决办法：
1.先创建两个被关联表

create table author(
id int primary key auto_increment,
name varchar(16),
age int
);

create table book(
id int primary key auto_increment,
bname varchar(20),
price int
);

2.创建关联表并删改同步：

create table author2book(
id int primary key auto_increment,
author_id int,
bool_id int,
unique key(author_id,book_id),
foreign key(author_id) references author(id)
on update cascade on delete cascade,
foreign key(book_id) references book(id)
on update cascade on delete cascade
);

3.插入被关联表数据

insert into author(name,age)values
('dahai',22),
('xialuo',23),
('guan',18),
('xishi',19),
('jiujiu',20);

insert into book(bname,price)values
('玉女真经',5),
('九阳神功',3),
('太极拳',4),
('如来神掌',2),
('玉女剑法',6);
4.插入关联表数据

insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(2,3),
(2,4),
(3,2),
(3,3),
(3,4),
(4,3),
(5,2);

5.查询:

select * from author,book,author2book where author.id=author2book.author_id and book.id=author2book.book_id;

结果这里就不展示了

3.一对一（主键id当外键）
1.创建表

create table student(
id int primary key,
name varchar(10)
);

create table stu_detail(
s_id int primary key,
age int,
sex enum('man','woman') not null,
foreign key(s_id) references student(id)
on update cascade
on delete cascade
);

插入数据

insert into student values
(1,'dahai'),
(2,'xialuo'),
(3,'xishi');

insert into stu_detail values
(1,18,'man'),
(2,18,'man'),
(3,18,'woman');

select * from student,stu_detail where student.id = stu_detail.s_id;

+----+--------+------+------+-------+
| id | name | s_id | age | sex |
+----+--------+------+------+-------+
| 1 | dahai | 1 | 18 | man |
| 2 | xialuo | 2 | 18 | man |
| 3 | xishi | 3 | 18 | woman |
+----+--------+------+------+-------+