【2022.8.16】MySQL数据库（3）

标签：2022.8 16 int 外键主键 key MySQL table id

学习内容概要

约束条件之主键
约束条件之外键
操作表的SQL语句补充

内容详细

约束条件

1 . 主键：primary key
- 主键含义：从约束角度上而言
  - 主键等于非空且唯一
  - not null unique

#  例如：
		create table t1(
            id int primary key,
            name varchar(32)
        );
    # 上述创建一个表 且里面的字段名id 在填写数据时不能为空 且 不能重复（唯一）

1 .2 在InnoDB存储引擎规定一张表必须且只有一个主键
- 如果在创建的表中没有主键也没有约束条件（非空（not noll）、唯一（unique））那么存储引擎 InnoDB 存储引擎会自动采用一个隐藏的字段作为主键（主键可以加速数据查询：如同新华字典的目录）
- 如果在创建的表中没有主键约束条件但是有（非空（not noll）、唯一（unique））约束条件那么InnoDB存储引擎会自动将该字段设置为主键
```
create table t2(
               nid int not null unique,
               sid int not null unique,
               uid int not null unique,
               name varchar(32)
        	); 

# 按照从上到下的顺序 把第一个 nid 自动设置为主键
```

1 .3 创建表的时候都应该有一个" id " 字段并且该字段应该作为主键

例如：id、uid、sid、pid、cid

# 主键也分为单例主键 和 联合主键

create table t2(
    id int primary key,
    name varchar(32)
)  # 单例主键

create table t3(
    id int ,
    nid int,
    primary key(id,nid))  # 联合主键

2 . 约束条件之自增

自增：auto_increment

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

# 例如：

create table t4(
        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)    
);


# 自增的特点：
		
    	自增的操作不会因为执行删除数据的操作而进行回退或者重置：意思就是比如你在表格中添加了 id 为自增的约束条件  然后添加数据了5条数据  后续删除了一条  （delete from 表名 where 筛选条件  按照条件删除数据）   然后再添加数据时  表格不会自动把你添加的数据id设为5 不会回退填充  只会往前增 6 7 8 9 ......
        
        
# 如果需要重置主键  需要格式化表
 
    truncatr 表名;  # 删除数据并重置主键值

约束条件之外键

1.外键的铺垫：
- 需要创建一张员工表：
- 输入字段：id name gender dep_name dep_desc
- 上述表在使用时有缺陷：
  - 1.表的结构不清晰不能明确看出到底是员工表还是部门表不能见名知意
  - 2.字段数据反复的存取浪费存储空间
  - 3.表的延展性差不能直接进行明确的更改
- 2.优化操作>>>：把表进行拆分
  - 分解成两个表：
    - 表1.id name gender
    - 表2.id dep_name dep_desc
  - 发现无法进行关联员工和部门无关联
  - 处理方式（在表1后面加入一个关联字段用来表明这个员工属于那个部门）：
    - id name gender dep_id
    - 添加一个部门编号字段填写部门数据的主键值
    - 外键字段
    - 专门用于记录表与表之间数据的关系
2.外键字段的创建
- 外键字段是用来记录表与表之间数据的关系共4种
顺序关系

1 一对多关系

2 多对多关系

3 一对一关系

4 没有关系
推导：判断表数据关系判断

顺序	关系
1	一对多关系
2	多对多关系
3	一对一关系
4	没有关系

针对员工表和部门表判断数据关系
    		1.先站在员工表的角度
        		问:一条员工数据能否对应多条部门数据
              翻译:一名员工能否属于多个部门
              答:不可以
          2.再站在部门表的角度
        		问:一条部门数据能否对应多条员工数据
              翻译:一个部门能否拥有多个员工
              答:可以
          完成换位思考之后得出的答案 一个可以一个不可以
          那么表关系就是"一对多"
            	部门是一 员工是多
          针对'一对多'的关系 外键字段建在多的一方 
          ps:没有多对一 统一称为'一对多'

Foreign Key

研究怎么去SQL中创建外键关系表
- 1.先写普通字段
- 2.然后再写外键字段

# 员工表（此表关联其他表）
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.问题：被关联字段无法进行修改和删除
    	   限制了操作  不舒服
        
# 解决方式：级联更新、级联删除

	# 被关联数据一旦变动 关联的数据同步进行变动
 


    # 员工表（此表关联其他表）
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 建立代码层面的关系

表关系之多对多

1 . 铺垫：（书籍和作者之间的关系）
- 1.1 在书籍表的角度：
  - 问：一本书能不能有多个作者创作的
  - 答：可以
- 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 primary key auto_increment,
           name varchar(32),
           book_id int,
           foreign key(book_id) references book(id) 
           on update cascade  # 级联更新 
           on delete cascade  # 级联删除
       );

# 证明得出：多对多表关系  表与表之间相互都是对方的被关联表 在SQL中  根本无法下手去创建


# 解决措施：需要单独开设第三张表  去存储数据关系

        # 书籍表
    
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.铺垫：（账号（手机号码）与支付宝的关系）
- 1.1 在手机号的角度：
  - 问：一个手机号能否注册多个支付宝
  - 答：不可以
- 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
    );

标签：2022.8,16,int,外键,主键,key,MySQL,table,id
From： https://www.cnblogs.com/55wym/p/16592912.html