【MySQL】005.MySQL表的约束(上)

技术文档

文章目录

表的约束
- 1. 约束概念
- 2. 空属性
- - 2.1 基本语法
  - 2.2 使用示例
- 3. 默认值
- - 3.1 基本概念
  - 3.2 使用示例
- 4. 列描述
- - 4.1 基本概念
  - 4.2 使用示例
- 5. zerofill
- - 5.1 基本功能
  - 5.2 使用示例
  - 5.3 注意事项
- 6. 主键
- - 6.1 基本概念
  - 6.2 使用示例

表的约束

1. 约束概念

真正约束字段的是数据类型，但数据类型约束很单一，需要一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。

表的约束：表中一定要有各种约束，通过约束，让我们未来插入数据库表中的数据是符合预期的。就好比我们在vs上编译代码时，vs会对我们进行各种约束，保证我们的代码在语法上是正确的。

约束本质是通过技术手段倒逼程序员插入正确的数据。反过来，站在mysql的视角，插入进来的数据，都是符合数据约束的。

约束的最终目标：保证数据的完整性和可预期性。

2. 空属性

2.1 基本语法

两个值：null（默认的）和not null (不为空)

null是不能参与数据运算的

mysql> select null;+------+| NULL |+------+| NULL |+------+1 row in set (0.00 sec)mysql> select 2+null;+--------+| 2+null |+--------+| NULL |+--------+1 row in set (0.00 sec)

2.2 使用示例

创建一个班级表，包含班级名和班级所在的教室。
站在正常的业务逻辑中：

如果班级没有名字，你不知道你在哪个班级
如果教室名字可以为空，就不知道在哪上课

所以我们在设计数据库表的时候，一定要在表中进行限制，满足上面条件的数据就不能插入到表中。这就是约束。

mysql> create table if not exists myclass( -> class_name varchar(20) not null, -> class_room varchar(20) not null, -> other varchar(20) -> );Query OK, 0 rows affected (0.04 sec)mysql> desc myclass;+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| class_name | varchar(20) | NO | | NULL | || class_room | varchar(20) | NO | | NULL | || other | varchar(20) | YES | | NULL | |+------------+-------------+------+-----+---------+-------+

【MySQL】005.MySQL表的约束(上)
插入数据：

//全插入mysql> insert into myclass (class_name,class_room,other) values (\'高三二班\',\'101教室\',\'普通班\');mysql> select * from myclass;+--------------+------------+-----------+| class_name | class_room | other |+--------------+------------+-----------+| 高三二班 | 101教室 | 普通班 |+--------------+------------+-----------+//最后一行不做插入mysql>insert into myclass (class_name,class_room) values (\'高三三班\',\'102教室\');mysql> select * from myclass;+--------------+------------+-----------+| class_name | class_room | other |+--------------+------------+-----------+| 高三二班 | 101教室 | 普通班 || 高三三班 | 102教室 | NULL |+--------------+------------+-----------+//只插入班级,我们会发现不让我们插入mysql>insert into myclass (class_name) values (\'高三三班\');ERROR 1364 (HY000): Field \'class_room\' doesn\'t have a default value//插入教室为NULL,我们会发现提示我们class_room不能为空mysql> insert into myclass (class_name,class_room) values (\'高三三班\',NULL);ERROR 1048 (23000): Column \'class_room\' cannot be null

3. 默认值

3.1 基本概念

默认值： 某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。

3.2 使用示例

mysql> create table if not exists t12( -> name varchar(20) not null, -> age tinyint unsigned default 18, -> gender char(1) default \'男\' -> );Query OK, 0 rows affected (0.03 sec)mysql> desc t12;+--------+------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+------------------+------+-----+---------+-------+| name | varchar(20) | NO | | NULL | || age | tinyint unsigned | YES | | 18 | || gender | char(1) | YES | | 男 | |+--------+------------------+------+-----+---------+-------+

【MySQL】005.MySQL表的约束(上)
插入：

mysql> insert into t12 (name,age,gender) values (\'张三\',19,\'女\');Query OK, 1 row affected (0.01 sec)mysql> select * from t12;+--------+------+--------+| name | age | gender |+--------+------+--------+| 张三 | 19 | 女 |+--------+------+--------+1 row in set (0.00 sec)//插入时并未指明性别和年纪mysql> insert into t12 (name) values (\'李四\');Query OK, 1 row affected (0.00 sec)mysql> select * from t12;+--------+------+--------+| name | age | gender |+--------+------+--------+| 张三 | 19 | 女 || 李四 | 18 | 男 |+--------+------+--------+2 rows in set (0.00 sec)

default如果设置了，用户将来插入如果有具体的数据，就用用户给的，如果没有，就用默认的。

mysql> create table t13( -> name varchar(20) not null, -> age tinyint default 18, -> gender char(1) not null default \'男\' -> );Query OK, 0 rows affected (0.03 sec)mysql> desc t13;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| name | varchar(20) | NO | | NULL | || age | tinyint | YES | | 18 | || gender | char(1) | NO | | 男 | |+--------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> insert into t13 (name,age,gender) values (NULL,\'18\',\'男\');ERROR 1048 (23000): Column \'name\' cannot be nullmysql> insert into t13 (age,gender) values (\'18\',\'男\');//如果我们没有明确指定一列要插入，用的是default,如果建表中对应列默认没有设置default值，将无法插入。ERROR 1364 (HY000): Field \'name\' doesn\'t have a default valuemysql> insert into t13 (name,age,gender) values (\'张三\',\'20\',NULL);ERROR 1048 (23000): Column \'gender\' cannot be nullmysql> insert into t13 (name,age) values (\'张三\',\'20\');Query OK, 1 row affected (0.01 sec)mysql> select * from t13;+--------+------+--------+| name | age | gender |+--------+------+--------+| 张三 | 20 | 男 |+--------+------+--------+mysql> insert into t13 (name,age,gender) values (\'张三\',NULL,\'男\');Query OK, 1 row affected (0.00 sec)mysql> insert into t13 (name,gender) values (\'张三\',\'男\');Query OK, 1 row affected (0.00 sec)mysql> select * from t13;+--------+------+--------+| name | age | gender |+--------+------+--------+| 张三 | 20 | 男 || 张三 | NULL | 男 || 张三 | 18 | 男 |+--------+------+--------+3 rows in set (0.00 sec)

如果我们没有明确指定一列要插入，用的是default,如果建表中对应列默认没有设置default值，将无法插入。
default和not null不冲突，而是互相补充的。
null和not null是作用在用户想插的时候。
default是作用在用户忽略了这一列的时候，如果设置了使用默认值，如果没有设置，直接报错。

如果我们在建表时没有设置默认值，mysql会对我们的表进行优化。

mysql> create table t14( -> name varchar(20), -> age varchar(20) -> );Query OK, 0 rows affected (0.02 sec)mysql> show create table t14 \\G;*************************** 1. row *************************** Table: t14Create Table: CREATE TABLE `t14` ( `name` varchar(20) DEFAULT NULL, `age` varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)

【MySQL】005.MySQL表的约束(上)
设置了not null后mysql就不设置默认的default了。

4. 列描述

4.1 基本概念

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。

4.2 使用示例

mysql> create table if not exists t15( -> name varchar(20) not null comment \'这个是用户的用户名\', -> age tinyint unsigned default 18 comment \'这个是用户的年纪\', -> gender char(1) default \'男\' comment \'这个是用户的性别\' -> );Query OK, 0 rows affected (0.02 sec)mysql> desc t15;+--------+------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+------------------+------+-----+---------+-------+| name | varchar(20) | NO | | NULL | || age | tinyint unsigned | YES | | 18 | || gender | char(1) | YES | | 男 | |+--------+------------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> insert into t15 (name,age,gender) values (\'张三\',19,\'女\');Query OK, 1 row affected (0.07 sec)//通过desc查看不到注释信息：mysql> select * from t15;+--------+------+--------+| name | age | gender |+--------+------+--------+| 张三 | 19 | 女 |+--------+------+--------+1 row in set (0.00 sec)//通过show可以看到：mysql> show create table t15;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t15 | CREATE TABLE `t15` ( `name` varchar(20) NOT NULL COMMENT \'这个是用户的用户名\', `age` tinyint unsigned DEFAULT \'18\' COMMENT \'这个是用户的年纪\', `gender` char(1) DEFAULT \'男\' COMMENT \'这个是用户的性别\') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

首先这个数据不是给数据库看的，它是给数据库的管理员

5. zerofill

zerofil是一种关于显示方面的约束

5.1 基本功能

自动填充零：当显示数值时，如果数值的位数小于列定义的宽度，MySQL 会在左侧用零填充。
隐式 UNSIGNED：从 MySQL 8.0.17 开始，ZEROFILL 属性已被标记为已弃用，并且使用它会自动将列设置为 UNSIGNED（无符号）。

5.2 使用示例

CREATE TABLE example ( id INT(5) ZEROFILL, code INT(10) ZEROFILL);INSERT INTO example VALUES (12, 12345);

查询结果会显示为：

+-------+------------+| id | code |+-------+------------+| 00012 | 0000012345 |+-------+------------+

MySQL 8.0 中的变化

弃用通知：从 MySQL 8.0.17 开始，ZEROFILL 已被标记为弃用，未来版本可能会移除。
替代方案：建议使用 LPAD 函数或应用程序逻辑来实现零填充效果：
```
SELECT LPAD(column_name, desired_length, \'0\') FROM table_name;
```
与显示宽度结合：ZEROFILL 需要与显示宽度（如 INT(5)）一起使用，但显示宽度本身在 MySQL 8.0 中也已变得不那么重要。

5.3 注意事项

ZEROFILL 只影响显示格式，不影响实际存储的值
它不适用于 FLOAT 或 DOUBLE 类型
在 MySQL 8.0 中，考虑迁移到其他格式化方法，因为此功能可能在未来版本中移除

虽然 ZEROFILL 仍然可以在 MySQL 8.0 中使用，但建议在新应用中避免使用它，转而采用更灵活的格式化方法。

6. 主键

6.1 基本概念

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键（不意味着一个表中的主键只能添加一列，一个主键可以被添加到一列或者多列中【复合主键】）；主键所在的列通常是整数类型。

6.2 使用示例

创建表的时候直接在字段上指定主键

mysql> create table if not exists test_key( -> id int unsigned primary key comment \'这个是学生的学号\', -> name varchar(20) not null -> );Query OK, 0 rows affected (0.03 sec)mysql> desc test_key;+-------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id | int unsigned | NO | PRI | NULL | | <= key 中 pri表示该字段是主键| name | varchar(20) | NO | | NULL | |+-------+--------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> show create table test_key \\G;*************************** 1. row *************************** Table: test_keyCreate Table: CREATE TABLE `test_key` ( `id` int unsigned NOT NULL COMMENT \'这个是学生的学号\', `name` varchar(20) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)

主键约束：主键对应的字段中不能重复，一旦重复，操作失败。

mysql> insert into test_key values (1,\'刘备\');ERROR 1062 (23000): Duplicate entry \'1\' for key \'test_key.PRIMARY\'

有了主键可以准确性的对数据进行增删查改

mysql> select *from test_key;+----+--------+| id | name |+----+--------+| 1 | 张飞 || 2 | 刘备 |+----+--------+2 rows in set (0.00 sec)mysql> select *from test_key where id=2;+----+--------+| id | name |+----+--------+| 2 | 刘备 |+----+--------+1 row in set (0.00 sec)mysql> update test_key set name=\'曹老板\' where id=2;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from test_key;+----+-----------+| id | name |+----+-----------+| 1 | 张飞 || 2 | 曹老板 |+----+-----------+2 rows in set (0.00 sec)

当表创建好以后但是没有主键的时候，可以再次追加主键

alter table 表名 add primary key(字段列表)

【MySQL】005.MySQL表的约束(上)

删除主键

alter table 表名 drop primary key;

【MySQL】005.MySQL表的约束(上)

复合主键

mysql> create table pick_course( -> id int unsigned, -> course_id int unsigned comment \'课程编号\', -> score tinyint unsigned comment \'这个学生这门课程考的分数\', -> primary key (id,course_id) -> );Query OK, 0 rows affected (0.03 sec)mysql> desc pick_course;+-----------+------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+------------------+------+-----+---------+-------+| id | int unsigned | NO | PRI | NULL | || course_id | int unsigned | NO | PRI | NULL | || score | tinyint unsigned | YES | | NULL | |+-----------+------------------+------+-----+---------+-------+3 rows in set (0.00 sec)

【MySQL】005.MySQL表的约束(上)

mysql> insert into pick_course values(12346,1,90);Query OK, 1 row affected (0.00 sec)mysql> insert into pick_course values(12347,1,90);Query OK, 1 row affected (0.01 sec)mysql> insert into pick_course values(12346,1,90); //主键冲突ERROR 1062 (23000): Duplicate entry \'12346-1\' for key \'pick_course.PRIMARY\'mysql> insert into pick_course values(12346,1,80);ERROR 1062 (23000): Duplicate entry \'12346-1\' for key \'pick_course.PRIMARY\'

👍 如果对你有帮助，欢迎：

点赞 ⭐️
收藏 📌
关注 🔔

【MySQL】005.MySQL表的约束(上)

文章目录

表的约束

1. 约束概念

2. 空属性

2.1 基本语法

2.2 使用示例

3. 默认值

3.1 基本概念

3.2 使用示例

4. 列描述

4.1 基本概念

4.2 使用示例

5. zerofill

5.1 基本功能

5.2 使用示例

5.3 注意事项

6. 主键

6.1 基本概念

6.2 使用示例

公告

DeepSeek全套部署资料免费下载

免费可商用字体批量下载

标签

【MySQL】005.MySQL表的约束(上)

文章目录

表的约束

1. 约束概念

2. 空属性

2.1 基本语法

2.2 使用示例

3. 默认值

3.1 基本概念

3.2 使用示例

4. 列描述

4.1 基本概念

4.2 使用示例

5. zerofill

5.1 基本功能

5.2 使用示例

5.3 注意事项

6. 主键

6.1 基本概念

6.2 使用示例

相关问题

公告

DeepSeek全套部署资料免费下载

免费可商用字体批量下载

标签