Day45 MySQL

1、约束条件

1.1、主键：primary key

单从约束角度上而言 主键等于 非空且唯一 not null unque

create table t1(
	id int primary key,
    name varchar(32)
);
1
2
3
4

InnoDB 存储引擎规定一张表 必须且只有一个主键
如果创建的表中没有主键 也没有非空且唯一的字段 那么 InnoDB存储引擎会自动采用一个隐藏的字段座位主键（主键可以加快数据查询 类似于新华字典的目录）

如果创建的表中没有主键 但是有非空且唯一的字段 那么InnoDB存储引擎会自动将该字段设置为主键

create table t2(
	nid int not null unique,
    sid int not null unique,
    uid int not null unique,
    name varchar(32)
);
1
2
3
4
5
6

创建表的时候应该有一个id字段 并且该字段 应该作为主键
uid sid pid gid cid id

补充说明
单列主键：

create table t(id int primary key ,name varchar(32))
1

联合主键：

create table t(sid int,nid int,name varchar(32),primary key(sid,nid))；
1

1.2、自增 auto_increment

约束条件 不能单独使用 必须跟在键后面 （主要配合主键一起使用）

create table t3(
	id int auto_increment
    
);

there can be only one auto column and it must be defined as a key

create table t4(
	id int primary key auto_increment;
    name varchar(32)
);
1
2
3
4
5
6
7
8
9
10
11

补充说明：自增的特点
自增的操作不会因为执行删除数据的操作而回退 或重置

#如果 非要重置主键 需要格式化表
truncate 表名； #删除表数据 并重置主键值
# deletet from 并不会重置 键值
1
2
3

2、约束条件之外键

外键前戏
需要创建一张员工表

# 创建一张员工表
create table t3(id int primary key auto_increment,gender varchar(32),dep_name varchar(32),dep_desc varchar(32);
1
2

上述表的缺陷：

1. 表结构 不清晰 到底是员工表 还是 部门表
2. 字段数据反复存取 浪费存储空间 （不严重 无所谓）
3. 表的扩展性极差 牵一发动全身（很严重 效率极低）

优化操作>>>:拆表

拆表之后解决了上述的三个问题 但是出现了一个致命的缺陷

解决措施

添加一个部门 编号字段填写部门数据的主键值

外键字段
专门用于记录表与表之间数据的关系

2.1、外键字段得创建

外键字段是用来记录表与表之间数据得关系 而数据得关系 有四种

• 一对多关系
• 多对多关系
• 一对一关系
• 没有关系

2.2、表数据得关系判定

换位思考

针对员工表 和 部门表 判断数据关系

1. 先站在员工表的坏的有

   问：一条员工数据 能否 对应多条部门数据
   翻：一名员工能否属于多个部门
   答：不可以

2. 再站在部门表的角度

   问：一条部门数据能否对应多员工数据
   翻：一个部门能否拥有多个员工
   答：可以

完成换位思考之后得出答案 一个可以 一个不可以
那么表关系就是 一对多
部门是一 员工是多
针对 一对多 的关系 外键字段建在多的一方
没有多对一 统称为 一对多

3 、Foreign Key

先写普通字段 再写外键字段

create table emp(
	id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
create table dep(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);
1
2
3
4
5
6
7
8
9
10
11
12

1. 创建表的时候需要先创建被关联的表（没有外键） 然后再是关联表（有外键）

2. 插入表 数据的时候 针对外键字段 只能填写被关联表字段已经出现过的数据值

3. 被关联字段无法修改和删除

   有点 不太好 操作限制性太强

级联更新、级联删除
被关联的数据一旦变动 关联的数据同步变动

create table emp1(
	id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep1(id)
    on update cascade #级联更新
    on delete cascade #级联删除
);
create table dep1(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

扩展 :
在实际工作中 很多时候可能并不会使用外键
因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
我们为了能够描述出表数据的关系 又不想使用外键
自己通过写SQL 建立代码层面的关系

4、表关系之多对多

以书籍表和作者为例

1. 先站在书籍表的角度

   问：一条书籍 数据能否对应多条 作者数据

   答：可以

2. 再站在作者表的角度

   问：一条作者数据能否应对多条书籍数据

   答：可以

总结：两边都可以 那么表数据关系就是多对多

4.1、多对多 实现

create table book(
	id int primary key auto_increment,
    title varchar(32),
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade  #级联更新
    on delete cascade #级联删除
);
#需要单独开设第三张关系表 存储数据关系
create table book(
	id int primary key auto_increment,
    title varchar(32)
);
create table author(
	id int primary key auto_increment,
    name varchar(32)
);
create table book2author(
	id int primary key auto_increment,
    book_id int,
    foreign key(book_id) references book(id)
    on update cascade  #级联更新
    on delete cascade, #级联删除
	author_id int,
    foreign key(author_id) references author(id)
    on update cascade #级联更新
    on delete cascade #级联删除
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

5、表关系之一对一

以用户表和用户详情表

1. 先站在用户表的角度

   问：一条用户数据 能否对应多个用户详情数据

   答：不可以

2. 再站在用户详情表的角度

   问：一条用户详情数据 能否对应

   答：不可以

总结：
两边都不可以 那么先考虑是不是没有关系
如果有关系 那么肯定就是一对一
针对一对一的表关系 外键字段建在任何一张表都可以 但是建议你建在查询频率较高的表中便于后续查询

create table user(
	id int primary key auto_increment,
    name varchar(32)
    detail_id int unique,
    foreign key(detail_id) references userDetail(id)
    on update cascade #级联更新
    on delete cascade #级联删除
);
create table userDetail(
	id int primary key auto_increment,
    phone bigint
);
1
2
3
4
5
6
7
8
9
10
11
12

作业：

课程表与班级表

学生表与班级表

老师表与课程表

书籍表与出版社表