数据约束条件

MySQL字段约束条件

• 无符号， 零填充
  
• 非空
  
• 默认值
  
• 唯一值
  
• 主键
  
• 自增
  
• 外键
  
• 无符号，零填充
  

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

1. 1. 验证零填充，查看数据345是否会被零填充
2. mysql> create table t1(id int unsigned, uid int(5) zerofill);
3. Query OK, 0 rows affected (0.05 sec)
5. mysql> insert into t1 values(123, 345);
6. Query OK, 1 row affected (0.02 sec)
8. mysql> select * from t1;
9. +-----+-------+
10. | id  | uid   |
11. +-----+-------+
12. | 123 | 00345 |
13. +-----+-------+
14. 1 row in set (0.02 sec)
16. mysql>

复制代码

• 非空 not null
  

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

1. 1. 验证MySQL数据值默认情况下是否可以为空
2. mysql> create table t2(id int, name varchar(16));
3. Query OK, 0 rows affected (0.05 sec)
5. mysql> insert into t2(name) values('li');
6. Query OK, 1 row affected (0.03 sec)
8. mysql> select * from t2;
9. +------+------+
10. | id   | name |
11. +------+------+
12. | NULL | li   |
13. +------+------+
14. # 这里如果id不写数据则直接为NULL
15. 1 row in set (0.02 sec)
18. 2. 验证MySQL添加约束条件后，是否可以继续为空
19. mysql> create table t3(id int not null, name varchar(16) not null);
20. Query OK, 0 rows affected (0.07 sec)
21. # 这里如果插入为空则直接报错
22. mysql> insert into t3(name) values('jin');
23. 1364 - Field 'id' doesn't have a default value
24. # 必须需要插入设定不能为空的值
25. mysql> insert into t3 values(1, 'jin');
26. Query OK, 1 row affected (0.03 sec)
28. mysql> select * from t3;
29. +----+------+
30. | id | name |
31. +----+------+
32. |  1 | jin  |
33. +----+------+
34. 1 row in set (0.02 sec)

复制代码

• 默认值 default
  

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

1. mysql> create table t4(id int, name varchar(16), gender varchar(16) default 'male' );
2. Query OK, 0 rows affected (0.06 sec)
4. mysql> insert into t4(id,name) values(1,'wesley');
5. Query OK, 1 row affected (0.03 sec)
7. mysql> select * from t4;
8. +----+--------+--------+
9. | id | name   | gender |
10. +----+--------+--------+
11. |  1 | wesley | male   |
12. +----+--------+--------+
13. 1 row in set (0.02 sec)
15. mysql>

复制代码

• 唯一值 unique
  

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

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

复制代码

• 主键 primary key
  

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

1. # 设定id字段为主键
2. mysql> create table t6(id int primary key, name varchar(16));
3. Query OK, 0 rows affected (0.04 sec)
5. # insert 数据
6. mysql> insert into t6 values(1, 'wesley');
7. Query OK, 1 row affected (0.02 sec)
9. # 想在主键位置从新insert数据后发现无法插入，这就是主键的唯一性
10. mysql> insert into t6 values(1, 'andy');
11. 1062 - Duplicate entry '1' for key 'PRIMARY'
12. mysql> select * from t6;
13. +----+--------+
14. | id | name   |
15. +----+--------+
16. |  1 | wesley |
17. +----+--------+
18. 1 row in set (0.03 sec)
20. mysql> show create table t6;
21. ----------------------------------+
22. | t6    | CREATE TABLE `t6` (
23.   `id` int(11) NOT NULL,
24.   `name` varchar(16) DEFAULT NULL,
25.   PRIMARY KEY (`id`)
26. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
27. """
28. 上述通过对表结构的查看可以看到，PRIMARY KEY (`id`) 这是主键的标识
29. """
31. 2. InnoDB存储引擎规定了所有的表都必须且只有一个主键，主键是组织数据的重要条件并且可以数据的查询速度
32.         2.1 当表中没有主键也没有其他非空且唯一的字段的情况下
33.                         InnoDB会采用一个隐藏的字段作为表的主键，隐藏就意味着无法使用，对于该表的数据查询就只能遍历全表
34.         2.2 当表中没有主键但是有非空且唯一的指端，那么会从上往下将第一个该字段升级为主键
35.        
36.         mysql> create table t7(
37.         id int,
38.         age int not null unique,
39.         phone bigint not null unique
40. );
41. Query OK, 0 rows affected (0.05 sec)
43. mysql> insert into t7 values(1, 18, 110),(2,19,120);
44. Query OK, 2 rows affected (0.03 sec)
45. Records: 2  Duplicates: 0  Warnings: 0
47. mysql> select * from t7;
48. +----+-----+-------+
49. | id | age | phone |
50. +----+-----+-------+
51. |  1 |  18 |   110 |
52. |  2 |  19 |   120 |
53. +----+-----+-------+
54. 2 rows in set (0.02 sec)
56. # 使用desc查看可以看到Key的PRI对应的是age，age为第一个非空且唯一
57. mysql> desc t7;
58. +-------+------------+------+-----+---------+-------+
59. | Field | Type       | Null | Key | Default | Extra |
60. +-------+------------+------+-----+---------+-------+
61. | id    | int(11)    | YES  |     | NULL    |       |
62. | age   | int(11)    | NO   | PRI | NULL    |       |
63. | phone | bigint(20) | NO   | UNI | NULL    |       |
64. +-------+------------+------+-----+---------+-------+
65. 3 rows in set (0.02 sec)
67. """
68. 1. 一般主键字段名称都是为*id
69. """

复制代码

• 自增 auto_increment
  

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

1. """
2. auto_increment的特性
3. 1. 自增不会应为数据的删除而回退，永远向前
4. 2. 如果在写入数据时设定了更大的值，则之后按照更大的值向前自增
5. 3. 如果需要重置某一张表的主键值，可以使用truncate tablename;
6. 4. 请注意：truncate tablename; 作用是清除表数据并重置主键
7. """
9. # 创建t9并将id作为自增主键
10. mysql> create table t9(
11.         id int primary key auto_increment,
12.         name varchar(16)
13. );
15. Query OK, 0 rows affected (0.06 sec)
17. # 反复插入数据，不需要指定主键
18. mysql> insert into t9(name) values('li'),('jin'),('ke');
19.        
20. Query OK, 3 rows affected (0.02 sec)
21. Records: 3  Duplicates: 0  Warnings: 0
23. mysql> insert into t9(name) values('li'),('jin'),('ke');
24.        
25. Query OK, 3 rows affected (0.02 sec)
26. Records: 3  Duplicates: 0  Warnings: 0
28. mysql> insert into t9(name) values('li'),('jin'),('ke');
29.        
30. Query OK, 3 rows affected (0.03 sec)
31. Records: 3  Duplicates: 0  Warnings: 0
33. # 结果为主键从1开始向后自增
34. mysql> select * from t9;
35. +----+------+
36. | id | name |
37. +----+------+
38. |  1 | li   |
39. |  2 | jin  |
40. |  3 | ke   |
41. |  4 | li   |
42. |  5 | jin  |
43. |  6 | ke   |
44. |  7 | li   |
45. |  8 | jin  |
46. |  9 | ke   |
47. +----+------+
48. 9 rows in set (0.02 sec)

复制代码

• 外键
  

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

• 关系判断
  

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

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

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

• 外键字段的建立
  

先定义含有平凡字段的表，然后再思量外键字段的添加

简化版

1. # 员工表
2. create table emp(
3.         id int primary key auto_increment,
4.         name varchar(32),
5.         age int,
6.         dep_id int,
7.         foreign key(dep_id) references dep(id)
8. );
10. # 部门表
11. create table dep(
12.         id int primary key auto_increment,
13.         dep_name varchar(32),
14.         dep_desc varchar(64)
15. );
17. 1. 创建表的时候，被关联表一定需要先创建，否则找不到关联字段
18. 2. 录入数据时需要先录入被关联表的信息，否则找不到关联信息
19. 3. 修改数据的时候外键字段无法修改和删除

复制代码

级联版

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

1. create table dmp1(
2.         id int primary key auto_increment,
3.         name varchar(32),
4.         age int,
5.         dep_id int,
6.         foreign key(dep_id) references dep1(id)
7.         on update cascade
8.         on delete cascade
9. );
11. create table dep1(
12.         id int primary key auto_increment,
13.         dep_name varchar(32),
14.         dep_desc varchar(64)
15. );
17. insert into dep1(dep_name,dep_desc) values('dev','dev apply'),('ops','ops');
18. insert into dmp1(name, age, dep_id) values('wesley',18,1),('andy', 44, 2);
20. # 查询表数据
21. mysql> select * from dep1;
22. +----+----------+-----------+
23. | id | dep_name | dep_desc  |
24. +----+----------+-----------+
25. |  1 | dev      | dev apply |
26. |  2 | ops      | ops       |
27. +----+----------+-----------+
28. 2 rows in set (0.02 sec)
30. mysql> select * from dmp1;
31. +----+--------+-----+--------+
32. | id | name   | age | dep_id |
33. +----+--------+-----+--------+
34. |  1 | wesley |  18 |      1 |
35. |  2 | andy   |  44 |      2 |
36. +----+--------+-----+--------+
37. 2 rows in set (0.02 sec)
39. # 删除dep1表id为1的记录，同时观察表编号
40. mysql> delete from dep1 where id = 1;
41. Query OK, 1 row affected (0.02 sec)
43. mysql> select * from dep1;
44. +----+----------+----------+
45. | id | dep_name | dep_desc |
46. +----+----------+----------+
47. |  2 | ops      | ops      |
48. +----+----------+----------+
49. 1 row in set (0.02 sec)
51. mysql> select * from dmp1;
52. +----+------+-----+--------+
53. | id | name | age | dep_id |
54. +----+------+-----+--------+
55. |  2 | andy |  44 |      2 |
56. +----+------+-----+--------+
57. 1 row in set (0.02 sec)
59. """
60. 上述可以发现当使用级联后，删除表里的关系数据后，其他相关表里的数据也会被删除
61. 需要注意：外键是数据间的强耦合
62. """

复制代码

• 多对多关系
  以册本表与作者表为例
  

• 册本表
  一本书是否可以拥有多个作者，可以
  
• 作者表
  一个作者是否可以对应多本书，可以
  

结论： 两者都可以，关系就是多对多，针对多对多不能直接在表中创建，需要创建第三张表来生存对应关系
[code]# 创建册本表
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。