MySQL表之间的关系概述

foreign key 会带来什么效果 ？
1 在创建表时 先建被关联的表 table1，才能建立被关联的表table2
2 在插入记录时，必须先插入被关联的表table1 才能插入被关联的表table2，
3 更新和删除都需要考虑到关联被关联的关系 》》》同步更新与删除
4 表之间的一对多和多对多以及一对一关系都是用的foreign key区别在于如何使用以及其他条件限制

表与表之间一对多关系(Foreign Key)
一：建立被关联表
create table dep(
    id int primary key auto_increment,
    dep_name char(10),
    dep_comment,char(60)
    );
二：建立关联表
create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
    );
三：插入被关联表
insert into dep(dep_name,dep_comment) values
('教学部','辅导学生学习，教授python课程'),
('销售部','销售产品课程'),
('技术部','技术能力有限部门');
四：插入关联表
insert into emp(name,gender,dep_id) values
('alex','male',1),
('egon','male',2),
('lxx','male',2),
('wxx','male',1),
('cxx','female',3);

五：更新被关联表
create table dep(
    id int primary key auto_increment,
    dep_name char(10),
    dep_comment char(60)
    );
create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female')not null default 'male',
    dep_id int,
    foreign key(dep_id) reference dep(id)
    on update cascade
    on delete cascade
    );

insert into dep(dep_name,dep_comment)values
('教学部','辅导学生学习，教授python课程')
('销售部','销售产品课程')，
('技术部','技术能力有限部门');

insert into emp(name,gender,dep_id) values
('alex','male',1),
('egon','male',2),
('lxx','male',2),
('wxx','male',1),
('cxx','female',3);


表与表之间多对多关系(Foreign Key)
create table author(
    id int primary key auto_increment,
    name char(16)
    );

create table book(
    id int primary key auto_increment,
    book_name char(16),
    price int,
    );

insert into author(name) vaules
('egon'),
('alex'),
('wxx');

insert into book(book_name,price) values
('python入门书',200),
('python提高书',400),
('python核心书',600),
('python自通书',800);

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


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

表与表之间一对一的关系(Foreign Key):
create table customer(
    id int primary key auto_increment,
    name char(20) not null,
    qq char(10) not null,
    phone char(16) not null
    );


create table student(
    id int primary key auto_increment,
    class_name char(10) not null,
    customer_id int unique,
    foreign key(customer_id) reference customer(id)
    on delete cascade
    on update cascade
    );