MySQL表关系

表关系

表关系的建立需要用到foreign key
分类

• 一对多，一个部门有多个员工，一个员工只能对应一个部门
• 多对多，如学生可以对应多个课程，双向对应
• 一对一，如各软件上各人只有单独对应的用户详情表

一对多（多对一）

实现方式

• 在多的一方建立外键，指向一的一方的主键
• 在创建表时先建被关联表
• 在录入数据时先录入被关联表

实现代码
以员工表与部门表为例

Create table dep(	-- 部门表
	Id int primary key auto_increment,
	dep_name varchar(20),
	dep_desc varchar(20)
);

Create table emp(	-- 员工表
	Id int primary key auto_increment,
	name varchar(20),
	Addr varchar(20),
	dep_id int,
	Foreign key(dep_id) references dep(id)
	On update cascade	-- 同步更新
	On delete cascade	-- 同步删除
);

insert into dep(dep_name,dep_desc) values(‘技术部’,’运维’),(‘安全部’,’监管’),(‘客服部’,’安抚客人’);
insert into emp(name,Addr,dep_id) values(‘小马’,’深圳’,1),(‘小天’,’湖南’,2),(‘久久’,’西安’,3);

多对多

实现方式

• 多对多关系不能在原有表中创建外键，需要借助第三方中间表
• 中间表至少包含两个字段，这两个字段作为中间表的外键，指向原两张表的主键

实现代码
以学生表与课程表为例

create table stu(
	id int primary key auto_increment,
	name varchar(20)
);

create table class(
	id int primary key auto_increment,
	class_name varchar(20)
);

insert into stu(name) values('qq'),('aa'),('bb'),('cc');
insert into class(class_name) values('math'),('english'),('language'),('physical'),('chemistry');


create table stu2class(
	id int primary key auto_increment,
	class_id int,
	stu_id int,
	foreign key(class_id) references class(id)
	on update cascade
	on delete cascade,
	foreign key(stu_id) references stu(id)
	on update cascade
	on delete cascade
);

insert into stu2class(class_id,stu_id) values(1,2),(1,4),(2,3),(2,1),(5,3);

一对一

一对一的情况下，外键字段建在任意一方都可，最好建在查询频率高的一方

实现代码
以个人与个人详情表为例

create table person_desc(
	id int primary key auto_increment,
    phone int,
    addr varchar(64)
);
create table person(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    person_id int unique,
    foreign key(person_id) references person_desc(id) 
    on update cascade 
    on delete cascade  
);

修改表

修改表名

• alter table 表名 rename 新表名;

增加字段

• alter table 表名 add 字段名 字段类型(宽度) 约束条件;
• alter table 表名 add 字段名 字段类型(宽度) 约束条件 first;
• alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名;

删除字段

• alter table 表名 drop 字段名;

修改字段

• alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
• alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;