【2022-08-16】mysql基础知识(三)

mysql基础知识(三)

约束条件之主键

作用：
1、单从约束条件上而言主键相当于not null + unique(非空且唯一)
2、主键的功能目前简单的理解为能够加快数据的查询速度，相当于字典的目录
3、InnoDB存储引擎规定了每张表都必须有且只有一个主键
	3.1 如果表中没有任何的主键和其他约束条件
		InnoDB默认会采用一个隐藏字段作为表的主键
	3.2 如果表中没有主键但是有not null + unique(非空且唯一的字段)
		InnoDB会自动将该字段升级为主键
结论：每张表都必须要有一个'id'字段(序号字段)并且该字段就是主键(通常在第一列)
 
关键字：primary key
可分为单列主键和多列主键(联合主键)

实例验证

单列主键：验证非空且唯一
	create table test1(
    	id int primary key,
    	name varchar(32)
	);
	
	insert into test1(name) values('jason');		# 报错
	insert into test1 values(1, 'tony');			# 报错

单列主键：验证not null + unique是否自动升级为主键
	create table test1(
		id int not null unique,
		age int not null unique,
		num int not null unique
		name varchar(32)
	);

多列主键(联合主键)：两个字段联合起来是一个主键(不常用)
	create table test3(
		id int,
		age int,
		name varchar(32)
		primary key(id,age)
	);

约束条件之自增

作用：自增(专门配合主键一起使用的 让主键能够自增)
关键字：auto_increment

特性：主键的自增是不会受到delete from删除操作的影响
 
 
如果删除某一个字段的话它还是保持原来的自增顺序依次自增。
 
比如：
删除3字段，那么4字段就会填充到3字段的位置，再来数据就是以5字段开始
 
truncate关键字既可以清空表数据也会重置主键值(但是这个没必要，一般不会重置数据，如果重置重新创建一个表就好了)
eg：truncate test1;  (清空test1表的数据并且重置主键)

实例验证

验证自增

创建表
create table test4(
	id int primary key auto_increment,
	name varchar(32)
);

写入数据

insert into test4(name) values('刘备'),('张飞'),('关羽'),('曹操'),('孙权');

约束条件之外键

1.外键前戏
	需要创建一张员工表
	id	name gender	dep_name dep_desc
	上述表的缺陷
        1.表结构不清晰 到底是员工表还是部门表(不严重 无所谓)
        2.字段数据反复存取 浪费存储空间(不严重 无所谓)
        3.表的扩展性极差 牵一发动全身(很严重 效率极低)
	优化操作>>>:拆表
        id	name	   gender	
        id	dep_name   dep_desc
       拆表之后解决了上述的三个问题 但是出现了一个致命的缺陷
   	解决措施
    	id		name	gender 		dep_id
       添加一个部门编号字段填写部门数据的主键值
   	外键字段
    	专门用于记录表与表之间数据的关系
  
2.外键字段的创建
	外键字段是用来记录表与表之间数据的关系 而数据的关系有四种
        一对多关系
        多对多关系
        一对一关系
        没有关系
	2.1.表数据关系的判定  >>>: '换位思考'
      针对员工表和部门表判断数据关系
          1.先站在员工表的角度
          问:一条员工数据能否对应多条部门数据
          翻:一名员工能否属于多个部门
          答:不可以
          2.再站在部门表的角度
          问:一条部门数据能否对应多条员工数据
          翻:一个部门能否拥有多个员工
          答:可以
          完成换位思考之后得出的答案 一个可以一个不可以
          那么表关系就是"一对多"
          部门是一 员工是多
          针对'一对多'的关系 外键字段建在多的一方 
          ps:没有多对一 统一称为'一对多'

外键字段之Foreign key

1.如果表中有外键字段，那么建议先编写普通字段，最后再考虑外键字段

创建表

create table temp(
	id int primary key auto_increment,
	name varchar(32)
	gender enmun('male','female','others') default 'male',
	dep_id int,
	froeign key(dep_id) references dep(id)
);


create table dep(
	id int primary key auto_increment,
	dep_name varchar(32),
	dep_desc varchar(32)
);

写入数据

insert into dep(dep_name,dep_desc) values('技术部','技术测试'),('安保部','维护治安');
insert into emp(name,dep_id) values('jason', 1)
insert into emp(name,dep_id) values('tony', 2)

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


级联更新、级联删除
被关联数据一旦变动 关联的数据同步变动
	
create table dep1(
	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');
	dep_id int,
	foreign key(dep_id) references dep(id)
	on update cascade			# 级联更新
	on delete cascade			# 级联删除
);


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

表关系之多对多

以书籍表与作者表为例
    1.先站在书籍表的基础之上
    	问:一本书能否对应多个作者
        答:可以(很多书籍都是由多个作者编写而成得)
    2.在站在作者表的基础之上
    	问:一个作者能否对应多本书
         答:可以
    结论:两个都可以那么表关系就是"多对多"
        这时就需要第三个表：外键字段建在第三张关系表中

实例验证

如果只有两个表：
 
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 author(
	id int primart key auto_increment,
	name varchar(32),
	book_id int,
	foreign key(book_id) references book(id)
	on update cascade
	on delete cascade
);
 
 
这样创建外键建立彼此之间的联系的话是不行的，
因为在创建第一个表关联第二个表的时候没有被关联表二
在创建第二个表关联第一个表的时候没有被关联表一
所以这是错误的多对多创建方式

正确的创建方式：引入第三张表，用于存放两者的关系

创建书籍表

create table book1(
	id int primary key auto_increment,
	title varchar(32)
);

创建作者表

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


创建两者的关系表

create table book2author(
	id int primary key auto_increment,
	author_id int,
	foregin key(author_id) references author(id)
	on update cascade
	on delete cascade,
	book_id int,
	foreign key(book_id) references book(id)
	on update cascade
	on delete cascade
);



写入数据

书籍表数据
insert into book1(title) values('python'),('java'),('golang'),('mysql');

作者表数据
insert into author1(name) values('tony'),('kevin');

关系表数据

insert into book2author(author_id,book_id) values(1,2),(3,4);
insert into book2author(author_id,book_id) values(2,2),(2,3);

表关系之一对一

将这两个数据拆分成两个表 ： 一个用户表 一个用户详情表
 
那么用户在查看基本信息得时候只给用户表的信息。
如果用户点击查看详情，那么才会把用户得详情表内信息给到用户
 
那么这两种表是什么关系呢？

以用户表与用户详情表为例：
	问：一个用户是否可以对应多条用户详情信息？
	答：不可以
	
	问：一个用户详情能否对应多个用户？
	答：不可以
	
我们上述分析过：一个可以一个不可以即为多对一，那么两边都不可以是什么呢？
那么两边都不可以换位思考后，表关系只有两种
	1.没有关系
	2.一对一表关系
	
那么一对一表关系得外键字段建立在哪里呢？
	理论上一对一外键字段建立在任何一个表里都可以，但是推荐建立在查询频率较高的表中

实例验证

创建表：
提示 ：外键关键字：foreign key(本表的外键字段) references 被关联表表明(被关联表的id字段)
 
用户表：
create table user(
    id int primary key auto_increment,
    name varchar(32),
    detail_id int unique,  # 这里记得添加约束条件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,
    addr varchar(32),  # 地址
    phone bigint  # 手机号
);


结论：其实一对一关系表就是添加了一个约束条件unique