MySQL主键外键
约束条件之主键
主键 primary key
1.单从约束层面上来讲,相当于not null加unique >>> 非空且唯一
验证:
create table t1(id int primary key);
create table t2(id int not null unique);
2.但是主键还是InnoDB存储组织数据表的依据
1.InnoDB规定了一个表必须要有且只有一个主键
2.如果你不指定主键则会采用隐藏的字段作为主键
3.当表中没有主键但是有非空且唯一的字段则自动升级为主键(自上而下第一个)
主键是可以加快查询速度的(类似字典的目录),隐藏意味着无法使用主键,即速度无法提升
create table t2(
id int,
name char(16),
age int not null unique,
addr char(16) not null unique
);
结论:
在创建表的时候一般都需要有一个id字段(uid,sid,pid...)
并且该字段应该设置为表的主要字段
了解:
主键可以单列主键也可以联合主键,但是联合主键使用频率很低
create table t3(
id int,
name char(16),
primary key(id,name)
);
约束条件之自增
create table t4(
id int primary key,
name char(16)
);
自增:auto_increment
(配合主键一起使用)
create table t5(
id int primary key auto_increment,
name char(16)
);
以后创建表,主键字段的固定写法如下:
id int primary key auto_increment
补充
1.自增的特性不会因为delete操作而重置/回退
delete from 只删除数据
2.如果真的想重置需要清空表数据和表结构
truncate 删除数据又重置主键
约束条件之外键
前言:
定义一张员工表为例
id name age dep_name der_desc
上表的问题有:
1.表结构不清晰
2.表数据重复
3.数据扩展性极差
为了解决上述三个问题:拆表
员工表:id name age
部门表:id dep_name dep_desc
拆表解决了三个问题,但员工和部门之间的关系没有了
因此需要在员工表里面添加一个部门编号字典:dep_id(外键)
外键:记录表和表之间数据关系的字段
表关系的种类
一对多关系
多对多关系
一对一关系
没有关系
如何判断关系
分别站在两个表的角度考虑,是否可以同时和另一个表的多个字段产生联系
一对多关系
eg:员工表与部门表
1.站在员工表的角度
一个员工能否对应多个部门?
不可以
2.站在部门表的层面
一个部门能否对应多个员工
可以
结论:一个可以,一个不可以,name员工表与部门表就是一对多关系
(一对多表关系外键字段加在多的一方,本例中员工方多)
建议:先把表的基本字段类型和约束条件完成再加外键
create table emp(
id int primary key auto_increment,
name char(16),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
);
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
外键字段的特性
1.在创建表的时候一定要先创建被关联表(自身没有外键字段的表)
2.在插入数据的时候也是先插入被关联表再插入关联表
只能够填写被关联字段中出现的值
3.被关联表中的数据无法自有删除和修改
4.级联更新 级联删除
(写了级联更新,级联删除之后,对一个表中的字段进行更新和删除时,另一个表中的对应字段也会被修改和删除)
create table emp(
id int primary key auto_increment,
name char(16),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade
on delete cascade
);
注意:on update cascade和on delete cascade都是作为约束条件加在后面的,因此前面不加逗号
多对多关系
eg:书籍表与作者表
1.站在书籍表
一本书能否对应多个作者
可以
2.站在作者表
一名作者能否对应多本书
可以
结论:两边都可以,那么表关系就是多对多
错误示范:
create table book(
id int primary key auto_increment,
name char(16),
price int
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
create table author(
id int primary key auto_increment,
name char(16),
age int,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
这样一来,由于两张表都没用来建立外键的对象,都不能先创建,因此无法进行
结论:针对多对多的表关系,外键字段需要建在第三张关系表中
正确做法:
create table book(
id int primary key auto_increment,
name char(16),
price int
);
create table author(
id int primary key auto_increment,
name char(16),
age int
);
create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
foreign key(book_id) rederences book(id)
on update cascade
on delete cascade
);
一对一关系
eg:
客户表与学生表,或用户表与用户详情表
用户表与用户详情表:
1.站在用户表的角度:
一个用户能否对应多个用户详情
不可以
2.站在用户详情表的角度:
一个用户详情能否对应多个用户
不可以
结论:两边都不可以,name表关系有么是没有关系,要么是一对一关系
注意:一对一关系表的外键建在任意一方都可以,但是推荐建在查询频率较高的表中
create table user(
id int primary key auto_increment,
name varchar(16),
detail_id int unique,
foreign key(detail_id) references user_detail(id)
on update cascade
on delete cascade
);
create table user_detail(
id int primary key auto_increment,
phone bigint,
addr varchar(16)
);
修改表相关SQL语句
1.修改表名 rename
alter table 表名 rename 新表名;
2.增加字段 add
2.1.
alter table 表名 add 字段名 数据类型 [完整性约束条件],
add 字段名 数据类型[完整性约束条件];
2.2.
alter table 表名 add 字段名 数据类型 [完整性约束条件]first;
alter table 表名 add 字段名 数据类型 [完整性约束条件]after 字段名;
3.删除字段 drop
alter table 表名 drop 字段名;
4.修改字段 change
(modify只能改数据类型,完整约束,不能改字段名
change可以,建议用change)
alter table 表名 modify 字段名 数据类型 [完整性约束条件];
alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件];
alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件];
总结
1.建表时先用创建好两个个基本表结构,加上id字段加上主键
2.根据实际需求判断,分别站在两个表的角度上判断题目,是否可以同时和另一个表的多个字段简历联系
3.如果只有一边可以,则是一对多,在字段多的表格后建立外键,并加上级联更新和级联删除
4.如果两边都不可以,则是一对一或没有关系,如果没有关系则不用管,如果是一对一,在3的基础上在外键上加上unique条件
5.如果两边都可以则需要额外创建一个表,使其同时对两个表的字段建立外键联系,并加上级联更新和级联删除
判断下列表的关系
'''有时候根据业务需求的不同 表管理也可能会发生变化 一对多 多对多'''
必须完整写出判断流程 并且书写sql语句并插入数据
书籍表与出版社表
1.书籍表:每本书只能对应一个出版社
2.出版社表:每个出版社可以对应很多本书
create table book(
id int primary key auto_increment,
name char(16),
company_id int,
foreign key(company_id) references company(id)
on update cascade
on delete cascade
);
create table company(
id int primary key auto_increment,
company_name char(16)
);
老师表与课程表
1.老师表:一个老师对应一门课
2.课程表:一门课程可以对应很多老师
create table teacher(
id int primary key auto_increment,
name char(16),
subject_id int,
foreign key(subject_id) references subject(id)
on update cascade
on delete cascade
);
create table subject(
id int primary key auto_increment,
subject_name char(16)
);
学生表与班级表
1.学生表:一个学生对应一个班级
2.班级表:一个班级可以对应很多学生
create table student(
id int primary key auto_increment,
name char(16),
class_id int,
foreign key(class_id) references class(id)
on update cascade
on delete cascade
);
create table class(
id int primary key auto_increment,
class_name char(16)
);
书籍表与作者表
1.书籍表:一本书籍可以对应多个作者
2.作者表:一个作者可以对应很多书籍
create table book(
id int primary key auto_increment,
name char(16)
);
create table author(
id int primary key auto_increment,
name char(16)
);
create table bookauthor(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
foreign key(book_id) rederences book(id)
on update cascade
on delete cascade
);
作者表与作者详情表
1.作者表:一个作者对应一个作者详情
2.作者详情表:一个作者详情对应一个作者
create table author(
id int primary key auto_increment,
name varchar(16),
detail_id int unique,
foreign key(detail_id) references detail(id)
on update cascade
on delete cascade
);
create table detail(
id int primary key auto_increment,
detail varchar(16)
);