MySQL数据库:表的约束

2023-12-19 23:45:30

目录

一.?空属性null

二.?默认值default

三.?列描述comment

四.?填充零zerofill

五.?主键primary key

六.?自增长auto_increment

七.?唯一键unique

八.?外键foreign key


一.?空属性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会拦截这种插入操作。

图8.1?外键约束案例
// 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
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。