MySQL字段约束条件、自增特性、约束条件之外键

阅读目录

• MySQL字段约束条件
• 无符号、零填充
• 空与非空
• 默认值
• 唯一值
• 主键
• 自增
• 外键前戏
• 外键关系
• 一对多关系
• 外键字段的建立
• 多对多关系
• 一对一关系

 

---

• 无符号、零填充

• 空与非空

• 默认值

• 唯一值

• 主键

• 自增

• 外键前戏

• 关系判断

• 一对多关系

• 外键字段的建立

• 多对多关系

• 一对一关系

回到顶部

MySQL字段约束条件

# 字段类型与约束条件的关系
约束条件是基于字段类型之上的额外限制
	eg:id int unsigned
id int 字段类型int规定了id 字段只能存整数
约束条件unsigned指在整数的基础上还必须为正数

回到顶部

无符号、零填充

# 无需正负号
unsigned
	id int unsigned
    
# 零填充
zerofill
	id int(5) zerofill

回到顶部

空与非空

# 空
null

create table t1(
	id int,
 	name varchar(16)
);

insert into t1(id) values(1);
insert into t1(name) values('jason');
insert into t1(name,id) values('tony',2);
ps:所有字段类型不加约束条件的情况下默认都可以为空

# 非空
not null

create table t2(
	id int,
 	name varchar(16) not null
);

insert into t2(id) values(1);
insert into t2(name) values('jason');
insert into t2 values(1,'');
insert into t2 values(2,null);

回到顶部

默认值

# 默认值
default

create table t3(
	id int default 666,
    name varchar(16) default'匿名'
);

insert into t3(id) values(1);
insert into t3(name) values('jason');
insert into t3 values(2,'tony');

回到顶部

唯一值

# 唯一值
unique

'''单列唯一'''
create table t4(
	id int,
    name varchar(32) unique
)

insert into t4 values(1,'jason'),(2,'jason');  # 报错 只能有一个jason
insert into t4 values(1 'jason');

'''联合唯一'''
create table t5(
	id int,
    ip varchar(32),
    port int,
    unique(ip,port)
);

insert into t5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080);

insert into t5 values(4,'127.0.0.1',8080) # 报错

回到顶部

主键

# 主键
primary key

1.单从约束层面上而言主键相当于not null + unique(非空且唯一)
create table t6(
	id int primary key,
    name varchar(32)
);

insert into t6(name) values('jason'); # 报错 没有id
insert into t6 values(1,'tony');
insert into t6 calues(1,'tom'); # 报错 id 1 重复

2.InnoDB存储引擎规定了所有的表都必须有且只有一个主键(主键是组织数据的重要条件并且主键可以加快数据的查询速度)

	2.1当表中没有主键也没有其他非空且唯一的字段的情况下
    	InnoDB会采用一个隐藏的字段作为表的主键 隐藏意味着无法使用 基于该表的数据查询只能一行行查找 速度很慢
        
	2.2当表中没有主键但是有其他非空且唯一的字段 那么会从上往下将第一个该字段自动升级为主键
    
create table t7(
	tid int,
    pid int not null unique,
    cid int not null unique
);

'''
我们在创建表的时候应该有一个字段用来标识数据的唯一性 并且该字段通常情况下就是'id'(编号)字段
	id nid pid sid uid
	
create table userinfo(
	uid int primary key,
);
'''

回到顶部

自增

# 自增
auto_increment

该约束条件不能单独出现 并且一张表中只能出现一次 主要就是配合主键一起用

# 带主键
create table t8(
	id int primary key,
    name varchar(32)
);

# 带主键带自增
create table t9(
	id int primary key auto_increment,
    name varchar(32)
);

insert into t9(name) values('jason'),('tony'),('kevin'),('tom');

'''
自增特性
	自增不会因为数据的删除而回退 永远自增往前
	如果自己设置了更大的数 则之后按照更大的往前自增
	
	如果想重置某张表的主键值 可以使用
		truncate 表名;  清空表数据并重置主键
'''

回到顶部

外键前戏

# 创建一个员工表
id	name	age		dep_name	        dep_desc
1	jason	20		外交部			搞外交
2	tony	18		教育部			教学
3	tom	25		外交部			搞外交
4	kevin	21		教育部			教学

# 表格缺陷
1.表的重点不清晰
	因为这可以算是员工表 也可以是部门表
2.表中的部门名和职责的数据一直都在重复
	浪费存储空间
3.表的扩展性太差
	耦合度太高	不利于维护
    
# 解决措施
 将表格分为两个
    员工表
id	name  age
	部门表
id	dep_name   dep_desc

# 解决了上面的问题 但有一个致命的问题 员工跟部门之间没有了关系
 措施
    在员工表中增加一个部门编号字段与部门表中的主键字段对应
    
'''
这个就是外键字段
	外键字段就是用来记录表与表之间的数据的关系
'''

回到顶部

外键关系

表关系、数据关系其实意思是一样的 只是说法上有区分

# 关系种类
1.一对多
2.多对多
3.一对一
4.没有关系
'''
关系的判断的关键技巧就是:换位思考
'''

回到顶部

一对多关系

# 一对多时
以员工表和部门表为例
	1.先站在员工表的角度
    	问:一名员工能否对应多个部门
        答:不可以(跟现实做对比，总不能一个人好几个部门职位)
 	2.再站在部门表的角度
    	问:一个部门能否对应多名员工
     	答:可以	
	结论:一个可以一个不可以 那么关系就是'一对多'
	针对'一对多'关系 外键字段建在'多'的一方

回到顶部

外键字段的建立

小技巧:先定义出含有普通字段的表 之后再考虑外键字段的添加
 
# 普通 被关联
create table dep(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(64)
);

# 关联 外键    
create table emp(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id)
);

# foreign key的约束效果
1.创建表的时候一定要先创建被关联表
2.录入表数据的时候一定要先录入被关联表
因为外键字段填入的值,只能是被关联表已经存在的值
3、修改、删除被关联表数据都会出现障碍
update dep set id=200 where id=2;
delect from dep where id=2;

针对3有简化措施>>>:级联更新 级联删除
  
# 普通 被关联
create table dep1(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(64)
);

# 关联 外键 
create table emp1(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep1(id)
    on update cascade # 级联更新
    on delete cascade # 级联删除
);

"""
外键其实是强耦合 不符合解耦合的特性
	所以很多时候 实际项目中当表较多的情况 我们可能不会使用外键 而是使用代码建立逻辑层面的关系
"""

回到顶部

多对多关系

以书籍表与作者表为例
	1.先站在书籍表的角度
  		问:一本书能否对应多个作者
      答:可以
 	2.再站在作者表的角度
    	问:一个作者能否对应多本书
      	答:可以
   结论:两个都可以 关系就是'多对多'	
 	针对'多对多'不能在表中直接创建 需要新建第三张关系表
    
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 bwitha(
    id int primary key auto_increment,
    author_id int,
    foreign 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  # 级联删除
);
# 多对多表关系 
两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除

回到顶部

一对一关系

# 针对用户信息表，里面的数据可以分为两类
1、热数据:
  经常使用的数据: 电话、签名、地区
2、冷数据:
  不经常使用的数据: 年级、星座、学校
为了降低资源并 降低数据库压力，将表一分为二
1、用户表
  存使用频率较高的数据字段
2、用户详情表
  存使用频率较低的数据字段  

# 以用户表与用户详情表为例
1.先站在用户表的角度
  	问:一个用户能否对应多个用户详情
    答:不可以
2.再站在用户详情表的角度
    问:一个用户详情能否对应多个用户
    答:不可以
# 两边都不可以，所以有两种可能 
'没有关系'
'一对一的关系'
针对'一对一'外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中

create table userdetail(
	id int primary key auto_increment,
  	phone bigint
);

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

# 用户表和用户详细表只能一对一，有了值之后，在想往里面添加值就会直接报错