【MySQL】第六弹---数据库表约束详解：从空属性到主键的全方位指南
✨个人主页： 熬夜学编程的小林
💗系列专栏： 【C语言详解】 【数据结构详解】【C++详解】【Linux系统编程】【MySQL】
目录
1. 表的约束
1.1 空属性
1.2 默认值
1.3 列描述
1.4 zerofill
1.5 主键
1. 表的约束
真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。
表的约束很多，这里主要介绍如下几个： null/not null,default, comment, zerofill，primary
key，auto_increment，unique key 。
表的约束：表中一定要有各种约束，通过约束，让我们未来插入数据库中表的数据是符合预期的，约束本质是通过技术手段，倒逼程序员，插入正确的数据。反过来，站在mysql的视角，凡是插入进来的书，都是符合数据约束的！
（图片来源网络，侵删）
- 约束的最终目标：保证数据的完整性和可预期性。
1.1 空属性
- 两个值：null（默认的）和not null(不为空)
- 数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。
mysql> select null; +------+ | NULL | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> select null + 1; +----------+ | null + 1 | +----------+ | NULL | +----------+ 1 row in set (0.02 sec)
案例：
mysql> create table t1(id int,name varchar(20) not null); # 创建表，name不能为空 Query OK, 0 rows affected (0.03 sec) mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into t1 values(1,'张三'); # 正常插入数据，插入成功 Query OK, 1 row affected (0.00 sec) mysql> insert into t1 (name) values('李四'); # 单独插入name，插入成功 Query OK, 1 row affected (0.01 sec) mysql> insert into t1 (id) values(2); # 单独插入id，插入失败，没有默认值 ERROR 1364 (HY000): Field 'name' doesn't have a default value mysql> select * from t1; +------+--------+ | id | name | +------+--------+ | 1 | 张三 | | NULL | 李四 | +------+--------+ 2 rows in set (0.00 sec)1.2 默认值
（图片来源网络，侵删）默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。
案例一：
（图片来源网络，侵删）创建表
mysql> create table t2 (id int not null, -> gender varchar(2) default '男', -> age tinyint unsigned default 18 -> ); Query OK, 0 rows affected (0.03 sec) mysql> desc t2; +--------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | gender | varchar(2) | YES | | 男 | | | age | tinyint(3) unsigned | YES | | 18 | | +--------+---------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)插入数据
- 默认值的生效：数据在插入的时候不给该字段赋值，就使用默认值
mysql> insert into t2 values(1,'男',15); # 正常插入数据，使用用户输入的数据 Query OK, 1 row affected (0.00 sec) mysql> insert into t2 (id,gender) values(2,'男'); # age用户没有输入数据，使用默认值 Query OK, 1 row affected (0.00 sec) mysql> insert into t2 (id,gender) values(3,'女'); Query OK, 1 row affected (0.01 sec) mysql> insert into t2 (id) values(4); # age和gender用户没有输入数据，均使用默认值 Query OK, 1 row affected (0.00 sec) mysql> select * from t2; +----+--------+------+ | id | gender | age | +----+--------+------+ | 1 | 男 | 15 | | 2 | 男 | 18 | | 3 | 女 | 18 | | 4 | 男 | 18 | +----+--------+------+ 4 rows in set (0.00 sec)
- 注意：只有设置了default的列，才可以在插入值的时候，对列进行省略
案例二：既有not null又有default
创建表
# 创建表，age既有not null又有default mysql> create table t3 (id int not null,age tinyint unsigned not null default 18); Query OK, 0 rows affected (0.03 sec) mysql> desc t3; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 18 | | +-------+---------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into t3 values(1,20); Query OK, 1 row affected (0.01 sec)
插入数据
mysql> insert into t3 values(1,20); # 正常插入，使用用户输入的数据 Query OK, 1 row affected (0.01 sec) mysql> insert into t3 (id) values(2); # 只插入id值，age使用默认值 Query OK, 1 row affected (0.00 sec) mysql> insert into t3 values(3,NULL); # name插入NULL，插入失败 ERROR 1048 (23000): Column 'age' cannot be null mysql> select * from t3; +----+-----+ | id | age | +----+-----+ | 1 | 20 | | 2 | 18 | +----+-----+ 2 rows in set (0.00 sec)
- 如果我们没有明确指定一列要插入，用的是default，如果建表中，对应列默认没有设置default值，无法直接插入。
- default 和 not null 不冲突，而是互相补充的。
not null : 当用户想插入的时候 -> 1. NULL 2. 合法数据
default : 当用户忽略这一列的使用 -> 1. 使用默认值(如果设置类)，2. 如果没有设置直接报错
1.3 列描述
列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA(Database Administrator -> 数据库管理员)来进行了解。
案例：
创建表
mysql> create table t4 (id int comment '编号', -> name varchar(20) comment '姓名', -> age tinyint unsigned comment '年龄' -> ); Query OK, 0 rows affected (0.06 sec) mysql> desc t4; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | +-------+---------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)- 注意：not null和defalut一般不需要同时出现，因为default本身有默认值，不会为空
通过desc查看不到注释信息。
查看注释信息
mysql> show create table t4 \G; *************************** 1. row *************************** Table: t4 Create Table: CREATE TABLE `t4` ( `id` int(11) DEFAULT NULL COMMENT '编号', `name` varchar(20) DEFAULT NULL COMMENT '姓名', `age` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified1.4 zerofill
刚开始学习数据库时，很多人对数字类型后面的长度很迷茫。通过show看看t5表的建表语句：
mysql> create table t5(a int unsigned ,b tinyint); Query OK, 0 rows affected (0.04 sec) mysql> show create table t5 \G; *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `a` int(10) unsigned DEFAULT NULL, `b` tinyint(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)可以看到int(10)这个代表什么意思呢？整型不是4字节码？这个10又代表什么呢？其实没有zerofill这个属性，括号内的数字是毫无意义的。a和b列就是前面插入的数据，如下：
mysql> insert into t5 values(1,2); Query OK, 1 row affected (0.01 sec) mysql> select * from t5; +------+------+ | a | b | +------+------+ | 1 | 2 | +------+------+ 1 row in set (0.00 sec)
但是对列添加了zerofill属性后，显示的结果就有所不同了。修改t5表的属性：
mysql> alter table t5 modify a int(5) unsigned zerofill; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t5 \G; *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `a` int(5) unsigned zerofill DEFAULT NULL, `b` tinyint(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)对a列添加了zerofill属性，再进行查找，返回如下结果：
mysql> select * from t5; +-------+------+ | a | b | +-------+------+ | 00001 | 2 | +-------+------+ 1 row in set (0.00 sec)
这次可以看到a的值由原来的1变成00001，这就是zerofill属性的作用，如果宽度小于设定的宽度（这里设置的是5），自动填充0。要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1。为什么是这样呢？我们可以用hex函数来证明。
mysql> select a,hex(a) from t5; +-------+--------+ | a | hex(a) | +-------+--------+ | 00001 | 1 | +-------+--------+ 1 row in set (0.00 sec)
- hex()为转换为16进制数的函数。
可以看出数据库内部存储的还是1,00001只是设置了zerofill属性后的一种格式化输出而已。
1.5 主键
主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。
案例：
创建表
# 学号为主键，name不能为NULL mysql> create table t6 (id int comment '学号' primary key,name varchar(20) not null ); Query OK, 0 rows affected (0.03 sec) mysql> desc t6; # PRI为主键 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
插入数据
mysql> insert into t6 values(1,'张三'); Query OK, 1 row affected (0.01 sec) mysql> insert into t6 values(1,'李四'); # 不可以插入相同学号的数据 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> insert into t6 values(2,'李四'); Query OK, 1 row affected (0.00 sec) mysql> insert into t6 values(3,'李四'); # 可以插入相同name的数据 Query OK, 1 row affected (0.01 sec) mysql> select * from t6; +----+--------+ | id | name | +----+--------+ | 1 | 张三 | | 2 | 李四 | | 3 | 李四 | +----+--------+ 3 rows in set (0.00 sec)
- 主键约束：主键对应的字段中不能重复，一旦重复，操作失败。
按照 id 查询数据
mysql> select * from t6 where id = 1; +----+--------+ | id | name | +----+--------+ | 1 | 张三 | +----+--------+ 1 row in set (0.00 sec) mysql> select * from t6 where id = 2; +----+--------+ | id | name | +----+--------+ | 2 | 李四 | +----+--------+ 1 row in set (0.00 sec)
对主键列查询能查询到唯一的一条结果。
- 当表创建好以后但是没有主键的时候，可以再次追加主键(前提该列数据没有重复)。
alter table 表名 add primary key(字段列表)
创建表
mysql> create table t7 (id int,name varchar(20)); Query OK, 0 rows affected (0.03 sec) mysql> desc t7; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
插入数据
mysql> insert into t7 values(1,'张三'); Query OK, 1 row affected (0.01 sec) mysql> insert into t7 values(2,'李四'); Query OK, 1 row affected (0.01 sec) mysql> insert into t7 values(2,'王五'); Query OK, 1 row affected (0.01 sec) mysql> select * from t7; +------+--------+ | id | name | +------+--------+ | 1 | 张三 | | 2 | 李四 | | 2 | 王五 | +------+--------+ 3 rows in set (0.00 sec)
添加主键
mysql> alter table t7 add primary key(id); ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
给 id 添加主键，但是 id 有重复数据，不能添加主键，因此需要删除重复的内容。
删除name = 王五的数据
语法：
delete from 表名 where 条件;
删除重复的数据
mysql> delete from t7 where name = '王五'; Query OK, 1 row affected (0.01 sec) mysql> select * from t7; +------+--------+ | id | name | +------+--------+ | 1 | 张三 | | 2 | 李四 | +------+--------+ 2 rows in set (0.00 sec)
添加主键
mysql> alter table t7 add primary key(id); Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t7; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
- 删除主键
alter table 表名 drop primary key;
因为一个表只能有一个主键，因此直接删除主键即可。
mysql> alter table t7 drop primary key; Query OK, 2 rows affected (0.15 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> desc t7; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
- 复合主键
在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段
作为主键，可以使用复合主键。
创建复合主键表
mysql> create table t8 (id int unsigned, -> course int unsigned comment '课程编号', -> score int unsigned comment '分数', -> primary key(id,course)); Query OK, 0 rows affected (0.05 sec) mysql> desc t8; +--------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | course | int(10) unsigned | NO | PRI | NULL | | | score | int(10) unsigned | YES | | NULL | | +--------+------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)插入数据
mysql> insert into t8 values(1,123,60); Query OK, 1 row affected (0.01 sec) mysql> insert into t8 values(1,124,75); Query OK, 1 row affected (0.01 sec) mysql> insert into t8 values(2,124,80); Query OK, 1 row affected (0.01 sec) mysql> insert into t8 values(2,124,90);# 复合主键内容都相同则报错 ERROR 1062 (23000): Duplicate entry '2-124' for key 'PRIMARY'
当复合主键的那几列数值均相同才报错，凡是一列不相同均能插入成功。
- 复合主键
- 删除主键
- 当表创建好以后但是没有主键的时候，可以再次追加主键(前提该列数据没有重复)。
- 主键约束：主键对应的字段中不能重复，一旦重复，操作失败。
- hex()为转换为16进制数的函数。
- 注意：not null和defalut一般不需要同时出现，因为default本身有默认值，不会为空
- 注意：只有设置了default的列，才可以在插入值的时候，对列进行省略
- 默认值的生效：数据在插入的时候不给该字段赋值，就使用默认值
免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们。
