数据约束条件

MySQL字段约束条件

• 无符号， 零填充

• 非空

• 默认值

• 唯一值

• 主键

• 自增

• 外键

• 无符号，零填充

无符号，忽略数据中的正负符号关键字unsigned
零填充，数据未到数据位的情况下使用零填充

1. 验证零填充，查看数据345是否会被零填充
mysql> create table t1(id int unsigned, uid int(5) zerofill);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values(123, 345);
Query OK, 1 row affected (0.02 sec)
 
mysql> select * from t1;
 ----- ------- 
| id  | uid   |
 ----- ------- 
| 123 | 00345 |
 ----- ------- 
1 row in set (0.02 sec)
 
mysql> 

• 非空 not null

MySQL数据库在不加约束条件的情况下字段的值是可以为空的

1. 验证MySQL数据值默认情况下是否可以为空
mysql> create table t2(id int, name varchar(16));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t2(name) values('li');
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from t2;
 ------ ------ 
| id   | name |
 ------ ------ 
| NULL | li   |
 ------ ------ 
# 这里如果id不写数据则直接为NULL
1 row in set (0.02 sec)


2. 验证MySQL添加约束条件后，是否可以继续为空
mysql> create table t3(id int not null, name varchar(16) not null);
Query OK, 0 rows affected (0.07 sec)
# 这里如果插入为空则直接报错 
mysql> insert into t3(name) values('jin');
1364 - Field 'id' doesn't have a default value
# 必须需要插入设定不能为空的值
mysql> insert into t3 values(1, 'jin');
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from t3;
 ---- ------ 
| id | name |
 ---- ------ 
|  1 | jin  |
 ---- ------ 
1 row in set (0.02 sec)

• 默认值 default

默认值指的是这里会给一个默认的值，如果在insert数据时无数据则按照默认值显示

mysql> create table t4(id int, name varchar(16), gender varchar(16) default 'male' );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t4(id,name) values(1,'wesley');
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from t4;
 ---- -------- -------- 
| id | name   | gender |
 ---- -------- -------- 
|  1 | wesley | male   |
 ---- -------- -------- 
1 row in set (0.02 sec)
 
mysql> 

• 唯一值 unique

即每列数值只能存储一次，相对于列数据来说是唯一

mysql> create table t5(id int unique, name varchar(16) unique);
Query OK, 0 rows affected (0.05 sec)
 
mysql> insert into t5 values(1,'wesley');
Query OK, 1 row affected (0.02 sec)
 
# 这里可以看到对使用了unique的列数据无法从新insert
mysql> insert into t5 values(2, 'wesley');
1062 - Duplicate entry 'wesley' for key 'name'
mysql> select * from t5;
 ---- -------- 
| id | name   |
 ---- -------- 
|  1 | wesley |
 ---- -------- 
1 row in set (0.02 sec)
mysql> 

• 主键 primary key

从约束层面上而言主键相当于not null unique （非空且唯一）

# 设定id字段为主键
mysql> create table t6(id int primary key, name varchar(16));
Query OK, 0 rows affected (0.04 sec)
 
# insert 数据
mysql> insert into t6 values(1, 'wesley');
Query OK, 1 row affected (0.02 sec)
 
# 想在主键位置从新insert数据后发现无法插入，这就是主键的唯一性
mysql> insert into t6 values(1, 'andy');
1062 - Duplicate entry '1' for key 'PRIMARY'
mysql> select * from t6;
 ---- -------- 
| id | name   |
 ---- -------- 
|  1 | wesley |
 ---- -------- 
1 row in set (0.03 sec)
 
mysql> show create table t6;
---------------------------------- 
| t6    | CREATE TABLE `t6` (
  `id` int(11) NOT NULL,
  `name` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
"""
上述通过对表结构的查看可以看到，PRIMARY KEY (`id`) 这是主键的标识
"""

2. InnoDB存储引擎规定了所有的表都必须且只有一个主键，主键是组织数据的重要条件并且可以数据的查询速度
	2.1 当表中没有主键也没有其他非空且唯一的字段的情况下
			InnoDB会采用一个隐藏的字段作为表的主键，隐藏就意味着无法使用，对于该表的数据查询就只能遍历全表
	2.2 当表中没有主键但是有非空且唯一的指端，那么会从上往下将第一个该字段升级为主键
	
	mysql> create table t7(
	id int,
	age int not null unique,
	phone bigint not null unique
);
Query OK, 0 rows affected (0.05 sec)
 
mysql> insert into t7 values(1, 18, 110),(2,19,120);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from t7;
 ---- ----- ------- 
| id | age | phone |
 ---- ----- ------- 
|  1 |  18 |   110 |
|  2 |  19 |   120 |
 ---- ----- ------- 
2 rows in set (0.02 sec)
 
# 使用desc查看可以看到Key的PRI对应的是age，age为第一个非空且唯一
mysql> desc t7;
 ------- ------------ ------ ----- --------- ------- 
| Field | Type       | Null | Key | Default | Extra |
 ------- ------------ ------ ----- --------- ------- 
| id    | int(11)    | YES  |     | NULL    |       |
| age   | int(11)    | NO   | PRI | NULL    |       |
| phone | bigint(20) | NO   | UNI | NULL    |       |
 ------- ------------ ------ ----- --------- ------- 
3 rows in set (0.02 sec)

"""
1. 一般主键字段名称都是为*id
"""

• 自增 auto_increment

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

"""
auto_increment的特性
1. 自增不会应为数据的删除而回退，永远向前
2. 如果在写入数据时设定了更大的值，则之后按照更大的值向前自增
3. 如果需要重置某一张表的主键值，可以使用truncate tablename;
4. 请注意：truncate tablename; 作用是清除表数据并重置主键
"""

# 创建t9并将id作为自增主键
mysql> create table t9(
	id int primary key auto_increment,
	name varchar(16)
);

Query OK, 0 rows affected (0.06 sec)
 
# 反复插入数据，不需要指定主键
mysql> insert into t9(name) values('li'),('jin'),('ke');
	
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> insert into t9(name) values('li'),('jin'),('ke');
	
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> insert into t9(name) values('li'),('jin'),('ke');
	
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
# 结果为主键从1开始向后自增
mysql> select * from t9;
 ---- ------ 
| id | name |
 ---- ------ 
|  1 | li   |
|  2 | jin  |
|  3 | ke   |
|  4 | li   |
|  5 | jin  |
|  6 | ke   |
|  7 | li   |
|  8 | jin  |
|  9 | ke   |
 ---- ------ 
9 rows in set (0.02 sec)

• 外键

表与表的关系建立一般是通过外键建立，用于标识表字段数据之间的关系

• 关系判断

在外键的关系判断中，一共有4种
一对多
多对多
一对一
没有关系

1. 一对多关系
   员工角度：
   一个员工不可以对应多个部门
   部门角度：
   一个部门可以对应多个员工

结论：一个可以一个不可以，关系就是一对多，针对一对多关系，外键字段建在多的一方

• 外键字段的建立

先定义含有普通字段的表，然后再考虑外键字段的添加

简化版

# 员工表
create table emp(
	id int primary key auto_increment,
	name varchar(32),
	age int,
	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(64)
);

1. 创建表的时候，被关联表一定需要先创建，否则找不到关联字段
2. 录入数据时需要先录入被关联表的信息，否则找不到关联信息
3. 修改数据的时候外键字段无法修改和删除

级联版

级联就是可以将数据绑定，删除数据对应的关系数据也会被删除

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

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

insert into dep1(dep_name,dep_desc) values('dev','dev apply'),('ops','ops');
insert into dmp1(name, age, dep_id) values('wesley',18,1),('andy', 44, 2);

# 查询表数据
mysql> select * from dep1;
 ---- ---------- ----------- 
| id | dep_name | dep_desc  |
 ---- ---------- ----------- 
|  1 | dev      | dev apply |
|  2 | ops      | ops       |
 ---- ---------- ----------- 
2 rows in set (0.02 sec)

mysql> select * from dmp1;
 ---- -------- ----- -------- 
| id | name   | age | dep_id |
 ---- -------- ----- -------- 
|  1 | wesley |  18 |      1 |
|  2 | andy   |  44 |      2 |
 ---- -------- ----- -------- 
2 rows in set (0.02 sec)

# 删除dep1表id为1的记录，同时观察表编号
mysql> delete from dep1 where id = 1;
Query OK, 1 row affected (0.02 sec)
 
mysql> select * from dep1;
 ---- ---------- ---------- 
| id | dep_name | dep_desc |
 ---- ---------- ---------- 
|  2 | ops      | ops      |
 ---- ---------- ---------- 
1 row in set (0.02 sec)
 
mysql> select * from dmp1;
 ---- ------ ----- -------- 
| id | name | age | dep_id |
 ---- ------ ----- -------- 
|  2 | andy |  44 |      2 |
 ---- ------ ----- -------- 
1 row in set (0.02 sec)

"""
上述可以发现当使用级联后，删除表里的关系数据后，其他相关表里的数据也会被删除
需要注意：外键是数据间的强耦合
"""

• 多对多关系
  以书籍表与作者表为例

1. 书籍表
   一本书是否可以拥有多个作者，可以
2. 作者表
   一个作者是否可以对应多本书，可以

结论： 两者都可以，关系就是多对多，针对多对多不能直接在表中创建，需要创建第三张表来保存对应关系

# 创建书籍表
create table book(
	id int primary key auto_increment,
	title varchar(36),
	price float(5,2)
);

# 创建作者表
create table author(
	id int primary key auto_increment,
	name varchar(32)
);


# 创建第三方关系表
create table book_and_author(
	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
);

# 查询数据表
mysql> desc author;
 ------- ------------- ------ ----- --------- ---------------- 
| Field | Type        | Null | Key | Default | Extra          |
 ------- ------------- ------ ----- --------- ---------------- 
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
 ------- ------------- ------ ----- --------- ---------------- 
2 rows in set (0.02 sec)
 
mysql> desc book;
 ------- ------------- ------ ----- --------- ---------------- 
| Field | Type        | Null | Key | Default | Extra          |
 ------- ------------- ------ ----- --------- ---------------- 
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| title | varchar(32) | YES  |     | NULL    |                |
| price | float       | YES  |     | NULL    |                |
 ------- ------------- ------ ----- --------- ---------------- 
3 rows in set (0.02 sec)
 
mysql> desc book_and_author;
 ----------- --------- ------ ----- --------- ---------------- 
| Field     | Type    | Null | Key | Default | Extra          |
 ----------- --------- ------ ----- --------- ---------------- 
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| author_id | int(11) | YES  | MUL | NULL    |                |
| book_id   | int(11) | YES  | MUL | NULL    |                |
 ----------- --------- ------ ----- --------- ---------------- 
3 rows in set (0.02 sec)

# 写入作者信息和书籍信息
insert into author(name) values('wesley'),('andy')
insert into book(title, price) values('html', 15),('css',16),('python',17);

# 将对应关系写入中间表
insert into book_and_author(book_id,author_id) values(1,1),(1,2),(2,1),(3,2);
mysql> select * from book_and_author;
 ---- ----------- --------- 
| id | author_id | book_id |
 ---- ----------- --------- 
|  5 |         1 |       1 |
|  6 |         2 |       1 |
|  7 |         1 |       2 |
|  8 |         2 |       3 |
 ---- ----------- --------- 
4 rows in set (0.02 sec)

• 一对一关系
  用户表与用户详情

1. 这里有个概念，为了提升网络效率和减少服务器压力，在开发过程中，一般会将冷热数据分开存储
2. 热数据： 频繁使用的数据
3. 冷数据： 不频繁使用的数据，如果需要使用设置触发条件向后查询

用户表：
一个用户是否可以对应多个用户详情： 不可以

用户详情表：
一个用户是否可以对应多个用户： 不可以

结论： 两个都可以，关系就是一对一或者没有关系，推荐外键建立在热数据这边

# 创建用户表
create table user(
	id int primary key auto_increment,
	name varchar(32),
	detai_id int unique,
	foreign key(detail_id) references userdetail(id)
	on update cascade
	on delete cascade
);

# 创建用户详情表
create table userinfo(
	id int primary key auto_increment,
	phone bigint
);

mysql> desc user;
 ----------- ------------- ------ ----- --------- ---------------- 
| Field     | Type        | Null | Key | Default | Extra          |
 ----------- ------------- ------ ----- --------- ---------------- 
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(32) | YES  |     | NULL    |                |
| detail_id | int(11)     | YES  | UNI | NULL    |                |
 ----------- ------------- ------ ----- --------- ---------------- 
3 rows in set (0.02 sec)
 
mysql> desc userinfo;
 ------- ------------ ------ ----- --------- ---------------- 
| Field | Type       | Null | Key | Default | Extra          |
 ------- ------------ ------ ----- --------- ---------------- 
| id    | int(11)    | NO   | PRI | NULL    | auto_increment |
| phone | bigint(20) | YES  |     | NULL    |                |
 ------- ------------ ------ ----- --------- ---------------- 
2 rows in set (0.23 sec)

mysql> insert into userinfo(phone) values(110); 
Query OK, 1 row affected (0.02 sec)
mysql> insert into user(name,detail_id) values('wesley',1);
Query OK, 1 row affected (0.02 sec)

# 插入数据后进行查询
mysql> select name,detail_id,phone from user inner join userinfo on user.detail_id = userinfo.id;
 -------- ----------- ------- 
| name   | detail_id | phone |
 -------- ----------- ------- 
| wesley |         1 |   110 |
 -------- ----------- ------- 
1 row in set (0.02 sec)