mysql关系表的使用_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

);