MySQL数据库：表的约束
2023-12-19 23:45:30
目录
一.?空属性null
对于表中的数据，如果在进行插入的时候不显示地给定值，那么默认就取null表示空。空（非空）属性有两个值可以表示：null?和 not null。
如果在创建表的时候，声明某个成员为not null，那么在向表中插入数据的时候，这个成员的值不能是null，在实际项目中会存在某些字段不能为空场景，例如：调查问卷中的某些必填选项。
语法：Field?DataType?not null?
如下代码，在表t1中，字段id不能为空，如果在向t1插入数据时，不显示给出id的值，那么默认就认为id为null，而not null属性会拦截id为null的插入或修改操作。
mysql> create table t1 (
-> id int not null,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.25 sec)
mysql> insert into t1 values (1, 'zhangsan');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t1 (name) values (2); -- 声明not null，没有默认值，插入失败
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> insert into t1 values (null, 'lisi'); -- id不可以为空
ERROR 1048 (23000): Column 'id' cannot be null
二.?默认值default
如果表中的某个字段有经常被使用的值，那么可以在建表的时候就声明这个字段的默认值，在向表中插入数据时，如果用户给定数据值就用用户给定的，如果用户没有显示给定就用默认值。
语法：Field?DataType?default?默认值
如下代码中的表t2，设置成员age的默认值为20，gender的默认值为'男'，插入数据时，可以忽略age和gender字段，这样就会使用默认数据。如果显示给定被default声明的字段值为null，那么就使用用户给定的null值而不是使用默认值。
mysql> create table t2 (
-> id int not null,
-> age tinyint unsigned default 20,
-> gender varchar(2) default '男'
-> );
Query OK, 0 rows affected (0.32 sec)
mysql> desc t2;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 20 | |
| gender | varchar(2) | YES | | 男 | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t2 values (10, 13, '女'); -- 用户显示给定值就使用用户给定的
Query OK, 1 row affected (0.02 sec)
mysql> insert into t2 (id) values (11); -- 如果省略值就采用默认的
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 (id, age) values (11, 19);
Query OK, 1 row affected (0.05 sec)
mysql> insert into t2 (id, age) values (12, null); -- 插入null不会采用default值
Query OK, 1 row affected (0.05 sec)
mysql> select * from t2;
+----+------+--------+
| id | age | gender |
+----+------+--------+
| 10 | 13 | 女 |
| 11 | 20 | 男 |
| 11 | 19 | 男 |
| 12 | NULL | 男 |
+----+------+--------+
4 rows in set (0.00 sec)三.?列描述comment
comment：列描述，仅仅是作为注释来使用的，类似于C/C++中的代码注释。
语法：Field?DataType?comment?'描述信息'
?由于comment描述信息完全不影响表的属性，因此：
- 通过desc TableName?无法查询到comment信息。
- 通过show create table TableName?能够看到comment信息。
mysql> create table t3 (
-> id int comment '标号',
-> message varchar(30) comment '属性信息'
-> );
Query OK, 0 rows affected (0.24 sec)
mysql> desc t3;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| message | varchar(30) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> show create table t3 \G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`id` int(11) DEFAULT NULL COMMENT '标号',
`message` varchar(30) DEFAULT NULL COMMENT '属性信息'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
四.?填充零zerofill
如下代码，当用户创建 整形/字符串 类型字段的时候，通过desc查看详细的表属性信息，可以看到类型信息后面有一对圆括号，里面有一个数字。对于 char/varchar?很好理解，表示字符串的长度，但是，整形类型后面括号里的数字又有什么含义呢？
mysql> create table t4 (
-> c1 char(10),
-> c2 varchar(5),
-> n1 tinyint unsigned,
-> n2 int unsigned
-> );
Query OK, 0 rows affected (0.25 sec)
mysql> desc t4;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c1 | char(10) | YES | | NULL | |
| c2 | varchar(5) | YES | | NULL | |
| n1 | tinyint(3) unsigned | YES | | NULL | |
| n2 | int(10) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
对于整形类型，如果不声明zerofill，那么括号里的数字是没有意义的。zerofill的作用是当前端显示的数据长度不足指定长度时，通过前导0来补充，如果超过指定长度，按照原本的格式显示，不会将超过设定长度的部分截断。整形类型括号后面的数字，就是默认的显示长度，如果没有声明zerofill，那么这个数字会被忽略。
语法：Field DataType(M) zerofill
解释：如果显示长度不足M，那么就使用前导0补充到M?
如下代码，在表t5中定义字段val int(5) unsigned，插入1、12、123、1234、12345、123456，之后使用select * from t5输出表t5中全部的val信息，可以观察到长度不足5的数据，前面都填充了0，而123456的长度超过了5，显示其原本的值，没有被截断。
声明zerofill不影响数据在数据库中存储的内容，受影响的仅是在前端的输出格式。?
mysql> create table t5 (
-> val int(5) unsigned zerofill
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> insert into t5 values(1);
Query OK, 1 row affected (0.04 sec)
mysql> insert into t5 values(12);
Query OK, 1 row affected (0.03 sec)
mysql> insert into t5 values(123);
Query OK, 1 row affected (0.04 sec)
mysql> insert into t5 values(1234);
Query OK, 1 row affected (0.03 sec)
mysql> insert into t5 values(12345);
Query OK, 1 row affected (0.03 sec)
mysql> insert into t5 values(123456);
Query OK, 1 row affected (0.03 sec)
mysql> select * from t5;
+--------+
| val |
+--------+
| 00001 |
| 00012 |
| 00123 |
| 01234 |
| 12345 |
| 123456 |
+--------+
6 rows in set (0.00 sec)五.?主键primary key
主键是MySQL数据库中用于数据检索的关键字，一张表中只能有一个主键，并且主键不能重复，不能为null，主键一般是整形数据。
语法：Field DataType primary key
解释：primary?key 为主键声明的关键字
如下代码，定义表stu1，其中包含int?unsigned类型数据id，将id设置为主键，如果试图插入重复的主键，或者主键值为null，那么MySQL会拦截这样的操作。
mysql> create table stu1 (
-> id int unsigned primary key comment '学号作为主键，不能重复',
-> name varchar(10) not null comment '学生姓名，不能为空',
-> gender enum('男','女') comment '学生性别'
-> );
Query OK, 0 rows affected (0.21 sec)
mysql> desc stu1;
+--------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
+--------+-------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into stu1 values (1,'zhangsan','男');
Query OK, 1 row affected (0.04 sec)
mysql> insert into stu1 values (2,'lisi','男');
Query OK, 1 row affected (0.04 sec)
mysql> insert into stu1 values (3,'wangwu','女');
Query OK, 1 row affected (0.03 sec)
mysql> insert into stu1 values (1,'wangwu','女'); -- 不允许有重复的主键
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into stu1 values (3,'wangwu','女');
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> insert into stu1 values (null,'wangwu','女'); -- 主键不能为null
ERROR 1048 (23000): Column 'id' cannot be null
mysql> select * from stu1;
+----+----------+--------+
| id | name | gender |
+----+----------+--------+
| 1 | zhangsan | 男 |
| 2 | lisi | 男 |
| 3 | wangwu | 女 |
+----+----------+--------+
3 rows in set (0.00 sec)
主键可以被删除，也可以在完成表的基本结构创建后再添加主键：
- 删除主键：alter table?表名称 drop primary key;
- 添加主键：alter table?表名称 add primary key(字段名);
下面的代码以上面的表stu1为操作对象，先删除主键，通过desc查看表属性信息，在重新设置id为主键，再通过desc查看表属性信息，观察到了主键被成功删除和添加。
mysql> alter table stu1 drop primary key;
Query OK, 3 rows affected (0.68 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc stu1;
+--------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
+--------+-------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table stu1 add primary key(id);
Query OK, 0 rows affected (0.56 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu1;
+--------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
+--------+-------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)主键一般用于检索，通过唯一的主键，用户可以实现对数据库中某行数据的查询、修改或删除：
- 通过主键进行检索：select * from?表名称 where?主键字段=XXX;
- 通过主键更改某行数据：update?表名称 set?待修改字段=XXX where?主键字段=XXX;
- 通过主键删除某行数据：delete from 表名称 where?主键字段=XXX;
mysql> select * from stu1;
+----+----------+--------+
| id | name | gender |
+----+----------+--------+
| 1 | zhangsan | 男 |
| 2 | lisi | 男 |
| 3 | wangwu | 女 |
+----+----------+--------+
3 rows in set (0.00 sec)
mysql> select * from stu1 where id=1; -- 检索id=1的行
+----+----------+--------+
| id | name | gender |
+----+----------+--------+
| 1 | zhangsan | 男 |
+----+----------+--------+
1 row in set (0.00 sec)
mysql> update stu1 set name='zhaoliu' where id=3; -- 更改id=3的行的name字段
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from stu1 where id=2; -- 删除id=2的数据
Query OK, 1 row affected (0.02 sec)
mysql> select * from stu1;
+----+----------+--------+
| id | name | gender |
+----+----------+--------+
| 1 | zhangsan | 男 |
| 3 | zhaoliu | 女 |
+----+----------+--------+
2 rows in set (0.00 sec)
复合主键：表中两个或两个以上字段共同充当主键。
如下代码，定义了表stu2，使用字段id和字段class_id组合来充当主键，向表中插入数据时，允许id或class_id这两个字段其中之一出现重复，但不允许两行数据的id和class_id全部重复，即：以复合主键用所有参与组合的字段的值作为比较依据，而不限制单个字段出现重复。
mysql> create table stu2 (
-> id int unsigned,
-> class_id int unsigned,
-> name varchar(10) not null,
-> primary key(id, class_id) -- id和class_id作为复合主键
-> );
Query OK, 0 rows affected (0.25 sec)
mysql> insert into stu2 values (1,101,'zhangsan');
Query OK, 1 row affected (0.04 sec)
mysql> insert into stu2 values (1,102,'zhangsan'); -- id重复，class_id不重复，允许插入
Query OK, 1 row affected (0.04 sec)
mysql> insert into stu2 values (2,102,'zhangsan'); -- id不重复，class_id重复，允许插入
Query OK, 1 row affected (0.05 sec)
mysql> insert into stu2 values (1,101,'lisi'); -- id和class_id全部重复，禁止插入
ERROR 1062 (23000): Duplicate entry '1-101' for key 'PRIMARY'六.?自增长auto_increment
自增长，必须配合主键来使用。如果一张表中，找不出一个合适的字段（唯一、方便检索）作为主键，那么就应当引入自增长字段。
自增长字段具有以下特点：
- 自增长auto_increment必须配合primary key使用，否则MySQL会拦截建表操作。
- 如果用户显示给定了值，那么就使用用户给定的，如果用户没有显示给出值，那么自增长字段的值就是 当前表中最大值 + 1。
- 如果用户没有显示给定值，表中也没有数据，那么默认自增长字段的值为1。如果在创建表的时候显示给定了起始值，那么就采用用户给出的起始值。
mysql> desc t6;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into t6 (name) values ('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t6 (name) values ('lisi');
Query OK, 1 row affected (0.04 sec)
mysql> insert into t6 (id,name) values (100,'wangwu');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t6 (name) values ('zhaoliu');
Query OK, 1 row affected (0.03 sec)
mysql> select * from t6;
+-----+----------+
| id | name |
+-----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 100 | wangwu |
| 101 | zhaoliu |
+-----+----------+
4 rows in set (0.00 sec)
如果不想使用默认值1为自增长主键的起始值，那么也可以在建表的时候声明起始值。
显示给定auto_increment起始值语法：
create table?表名称 (
? ? ? ? Field DataType primary key auto_increment,
? ? ? ? // ... ...
)auto_increment=起始值;
如下代码，创建表t7，显示给定自增长字段的起始值为10，依次省略自增长字段值插入两行数据，可见两行数据的自增长字段值为10和11。
mysql> create table t7 (
-> id int unsigned primary key auto_increment,
-> name varchar(10) not null
-> )auto_increment=10;
Query OK, 0 rows affected (0.26 sec)
mysql> insert into t7 (name) values ('zhangsan');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t7 (name) values ('lisi');
Query OK, 1 row affected (0.02 sec)
mysql> select * from t7;
+----+----------+
| id | name |
+----+----------+
| 10 | zhangsan |
| 11 | lisi |
+----+----------+
2 rows in set (0.01 sec)七.?唯一键unique
唯一键，用于表示一张表中不允许出现重复的字段，如果尝试向表中插入唯一键重复的数据，那么MySQL会拦截操作，唯一键与主键十分类似，但又有所不同。
唯一键和主键的区别：
- 一张表中可以有多个唯一键，但只能有一个主键。
- 唯一键可以为null，主键不能为null。
- 主键最重要的功能是用于数据检索，而唯一键一般只是为了避免数据重复。
注意：唯一键为null时不进行重复性比较，即：一张表中可以有多行唯一键为null的数据存在。
mysql> insert into t8 values (1,10,'hello');
Query OK, 1 row affected (0.05 sec)
mysql> insert into t8 values (1,12,'good');
ERROR 1062 (23000): Duplicate entry '1' for key 'u_val1' -- 尝试插入相同的唯一键，插入失败
mysql> insert into t8 values (2,10,'good');
ERROR 1062 (23000): Duplicate entry '10' for key 'u_val2'
mysql> insert into t8 values (2,null,'haha');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t8 values (3,null,'bye'); -- 插入两个值为null的唯一键插入成功
Query OK, 1 row affected (0.05 sec)
mysql> select * from t8;
+--------+--------+-------+
| u_val1 | u_val2 | val3 |
+--------+--------+-------+
| 1 | 10 | hello |
| 2 | NULL | haha |
| 3 | NULL | bye | -- null不进行唯一性比较
+--------+--------+-------+
3 rows in set (0.00 sec)
八.?外键foreign key
外键通常用于定义主表字段和从表字段之间的约束关系，外键约束具有以下特点：
- 外键定义必须在从表中，其所关联的主表字段必须是唯一键或者主键。
- 从表中的外键约束字段值，要么为主表对应字段存在的值，要么为null。
语法：foreign key (字段名) references?主表名(字段名)?
图8.1为外键约束的案例，定义两张表stu和class，存储学生信息和班级信息，如果在学生信息的表中附加班级信息，那么会造成大量的数据冗余，因此在学生信息表stu中定义字段class_id关联到表class的id字段，这样就建立起了表stu和class的关联。
如果企图在从表中，插入主表不存在的外键约束字段，那么MySQL会拦截这种插入操作。
// 1.创建主表
mysql> create table class (
-> id int unsigned primary key,
-> name varchar(10) not null
-> );
Query OK, 0 rows affected (0.16 sec)
// 2.创建从表
mysql> create table student (
-> id int unsigned primary key,
-> class_id int unsigned,
-> name varchar(10),
-> foreign key(class_id) references class(id) -- 定义外键约束
-> );
Query OK, 0 rows affected (0.27 sec)
文章来源:https://blog.csdn.net/weixin_43908419/article/details/135091764
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！