Day45 MySQL

Tao.686

于 2022-08-16 22:19:09 发布

阅读量254

点赞数

分类专栏： mysql 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/weixin_71967396/article/details/126375421

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

Day45 MySQL

文章目录

Day45 MySQL

1、约束条件

1.1、主键：primary key

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

create table t1(
	id int primary key,
    name varchar(32)
);

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

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

create table t2(
	nid int not null unique,
    sid int not null unique,
    uid int not null unique,
    name varchar(32)
);

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

补充说明
单列主键：

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

联合主键：

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

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)
);

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

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

2、约束条件之外键

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

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

上述表的缺陷：

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

优化操作>>>:拆表

id	name	gender
1	kk	男

id	dep_name	dep_desc
1

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

解决措施

id	name	gender	dep_id

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

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

2.1、外键字段得创建

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

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

2.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)
);

创建表的时候需要先创建被关联的表（没有外键）然后再是关联表（有外键）
插入表数据的时候针对外键字段只能填写被关联表字段已经出现过的数据值
被关联字段无法修改和删除

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

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

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)
);

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

4、表关系之多对多

以书籍表和作者为例

先站在书籍表的角度

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

答：可以
再站在作者表的角度

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

答：可以

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

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 #级联删除
);

5、表关系之一对一

以用户表和用户详情表

先站在用户表的角度

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

答：不可以
再站在用户详情表的角度

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

答：不可以

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

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
);