MySQL讲义第14讲——完整性约束之非空（NOT NULL）约束与默认值（DEFAULT）

最新推荐文章于 2024-05-09 18:05:56 发布

睿思达DBA_WGX

最新推荐文章于 2024-05-09 18:05:56 发布

阅读量4.5k

点赞数 8

分类专栏： MySQL基本操作 MySQL讲义文章标签：数据库 mysql 完整性约束

本文链接：https://blog.csdn.net/weixin_44377973/article/details/108949743

版权

MySQL基本操作同时被 2 个专栏收录

180 篇文章 103 订阅

订阅专栏

MySQL讲义

57 篇文章 28 订阅

订阅专栏

MySQL讲义第14讲——完整性约束之非空（NOT NULL）约束与默认值（DEFAULT）

文章目录

MySQL讲义第14讲——完整性约束之非空（NOT NULL）约束与默认值（DEFAULT）

一、非空约束（NOT NULL）

非空约束强制列不能为 NULL 值。插入或更新字段值的时候，必须为该字段指定一个非空的数据，否则会出现插入或更新失败。

1、定义非空约束

创建表时，所有字段默认可以取空值，如果需要将某个字段定义为不允许取空值，可以使用非空约束（NOT NULL）。语法格式如下：

create table 表名(
    列名 类型 not null,
    ....
);

举例：创建 t1 表，其中 name 字段不允许取空值，age 字段允许取空值。

mysql> create table t1(
       id int primary key auto_increment,
       name char(20) not null,
       age int
    );
Query OK, 0 rows affected (0.02 sec)

-- 注：主键不允许取空值，因此不需要添加 not null

mysql> desc t1;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| age   | int(11)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

为 t1 表插入数据：

mysql> insert into t1(name,age) values('Jack',30);
Query OK, 1 row affected (0.01 sec)   -- 插入成功

mysql> insert into t1(name) values('Jerry');
Query OK, 1 row affected (0.01 sec)   -- 插入成功

mysql> insert into t1(age) values(25);   --插入失败
ERROR 1364 (HY000): Field 'name' doesn't have a default value

mysql> select * from t1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  5 | Jack  |   30 |
| 15 | Jerry | NULL |
+----+-------+------+
2 rows in set (0.00 sec)

2、删除非空约束

如果需要使某个字段允许取空值，只需要修改该字段的属性，去掉 not null 选项即可。例如：

mysql> alter table t1 modify name char(20);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | YES  |     | NULL    |                |
| age   | int(11)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

二、默认值（DEFAULT）

如果为某个列指定默认值，在表中插入一条新记录时，如果没有为该字段赋值，系统就会自动为这个字段插入默认值。
比如：员工表中，部门位置在北京的较多，那么部门位置就可以设置默认值为北京，如果输入数据时不指定部门位置，则系统就会自动把部门位置填写为北京。

注意：默认值通常用在已经设置了非空约束的列。

1、在创建表时设置默认值约束

创建表时可以使用 DEFAULT 为某个字段设置默认值，语法如下：

create table 表名 (
    <字段名> <数据类型> DEFAULT <默认值>,
    ....
);

举例：创建表 t2，为字段 addr 设置默认值。

create table t2(
    id int primary key auto_increment,
    name char(20),
    birth datetime,
    salary decimal(10,2),
    addr char(20) not null default '新乡'
);

mysql> desc t2;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| name   | char(20)      | YES  |     | NULL    |                |
| birth  | datetime      | YES  |     | NULL    |                |
| salary | decimal(10,2) | YES  |     | NULL    |                |
| addr   | char(20)      | NO  |     | 新乡    |                |
+--------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

为 t2 表插入数据：

mysql> insert into t2(name,birth,salary) values('Jack','1998-1-23',4500);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t2(name,birth,salary) values('Tom','1996-11-2',7400);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t2;
+----+------+---------------------+---------+--------+
| id | name | birth               | salary  | addr   |
+----+------+---------------------+---------+--------+
|  5 | Jack | 1998-01-23 00:00:00 | 4500.00 | 新乡   |
| 15 | Tom  | 1996-11-02 00:00:00 | 7400.00 | 新乡   |
+----+------+---------------------+---------+--------+
2 rows in set (0.00 sec)

2、删除字段的默认值

当一个表中的列不需要设置默认值时，就需要从表中将其删除。删除默认值约束的语法格式如下：

ALTER TABLE <表名>
MODIFY <字段名> <数据类型> DEFAULT NULL;
或者
alter table <表名> alter column <字段名> drop default;

举例：删除 t2 表中 addr 字段的默认值。

mysql> desc t2;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| name   | char(20)      | YES  |     | NULL    |                |
| birth  | datetime      | YES  |     | NULL    |                |
| salary | decimal(10,2) | YES  |     | NULL    |                |
| addr   | char(20)      | NO   |     | 新乡    |                |
+--------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table t2 alter column addr drop default;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| name   | char(20)      | YES  |     | NULL    |                |
| birth  | datetime      | YES  |     | NULL    |                |
| salary | decimal(10,2) | YES  |     | NULL    |                |
| addr   | char(20)      | NO   |     | NULL    |                |
+--------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

-- 重新添加默认值
mysql> alter table t2 alter column addr set default 'Beining';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| name   | char(20)      | YES  |     | NULL    |                |
| birth  | datetime      | YES  |     | NULL    |                |
| salary | decimal(10,2) | YES  |     | NULL    |                |
| addr   | char(20)      | NO   |     | Beining |                |
+--------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

-- 删除默认值
mysql> alter table t2 modify addr char(20) default null;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| name   | char(20)      | YES  |     | NULL    |                |
| birth  | datetime      | YES  |     | NULL    |                |
| salary | decimal(10,2) | YES  |     | NULL    |                |
| addr   | char(20)      | YES  |     | NULL    |                |
+--------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

3、为某个字段添加默认值

添加默认值的语法格式如下：

ALTER TABLE <表名>
MODIFY <字段名> <数据类型> DEFAULT <默认值>;
或者
ALTER TABLE <表名> ALTER COLUMN <字段名> SET DEFAULT <默认值>;

举例：为 t2 表的 addr 列添加默认值为郑州

mysql> alter table t2 alter column addr set default '郑州';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| name   | char(20)      | YES  |     | NULL    |                |
| birth  | datetime      | YES  |     | NULL    |                |
| salary | decimal(10,2) | YES  |     | NULL    |                |
| addr   | char(20)      | YES  |     | 郑州    |                |
+--------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

-- 删除字段 addr 的默认值
mysql> alter table t2 alter column addr drop default;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| name   | char(20)      | YES  |     | NULL    |                |
| birth  | datetime      | YES  |     | NULL    |                |
| salary | decimal(10,2) | YES  |     | NULL    |                |
| addr   | char(20)      | YES  |     | NULL    |                |
+--------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

-- 重新添加默认值
mysql> alter table t2 modify addr char(20) not null default '郑州';
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| name   | char(20)      | YES  |     | NULL    |                |
| birth  | datetime      | YES  |     | NULL    |                |
| salary | decimal(10,2) | YES  |     | NULL    |                |
| addr   | char(20)      | NO   |     | 郑州    |                |
+--------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)