初谈表的约束

2024-08-05 08:32:12 浏览数 (3)

概念

真正约束字段的是数据类型,但是数据类型约束很单一,需要有一些额外的约束,更好的保证数据的合法性,从业务逻辑角度保证数据的正确性。比如有一个字段是email,要求是唯一的。 表的约束很多,这里主要介绍如下几个: null/not null,default, comment, zerofill,primarykey,auto_increment,unique key

表中一定要有各种约束,通过约束让我们未来插入数据库表中的数据是符合预期的。约束本质是通过技术手段,倒逼程序员插入正确的数据,反过来站在MySQL的视角,凡是插入进来的数据,都是符合数据约束的。 因此约束的最终目标是保证数据的完整性和可预期性。

空属性

  • 两个值:null(默认的)和not null(不为空)
  • 数据库默认字段基本都是字段为空,但是实际开发时,尽可能保证字段不为空,因为数据为空没办法参与运算。
代码语言:javascript复制
mysql> select NULL;
 ------ 
| NULL |
 ------ 
| NULL |
 ------ 
1 row in set (0.00 sec)

mysql> select 1 NULL;
 -------- 
| 1 NULL |
 -------- 
|   NULL |
 -------- 
1 row in set (0.00 sec)
  • 实例: 创建一个班级表,包含班级名和班级所在的教室。 站在正常的业务逻辑中: 如果班级没有名字,你不知道你在哪个班级 如果教室名字可以为空,就不知道在哪上课
代码语言:javascript复制
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.06 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    |       |
 ------------ ------------- ------ ----- --------- ------- 
3 rows in set (0.03 sec)

上述创建了一个myclass表,其中class_nameclass_room具有非空约束,not null 意味着这一列的值不允许为空,必须提供值,在添加数据的时候必须插入数据,但是other不需要.

默认值

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

实例:

代码语言:javascript复制
mysql> create table if not exists t14( name varchar(20) not null, age tinyint unsigned default 18, gender char(1) default '男' );
Query OK, 0 rows affected (0.03 sec)

mysql> desc t14;
 -------- ------------------ ------ ----- --------- ------- 
| 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)

age tinyint unsigned default 18: 这是定义了第二个列 agedefault 18 指定了默认值为18,如果插入数据时未指定 age 的值,则会默认为18

gender char(1) default '男': 这是定义了第三个列 gender。default '男' 指定了默认值为 ‘男’,即性别默认为男性。如果插入数据时未指定 gender 的值,则会默认为 '男'

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

空属性和默认值对比

defaultnot null不冲突,是互相补充的。 如果没有明确指定一列需要插入,就用default;如果建表中,对应列默认没有设置default,无法直接插入。

代码语言:javascript复制
mysql> create table t16( name varchar(20) not null, age tinyint default 18, gender char(1) not null default '男' );

gender char(1) not null default '男': 这是定义了第三个列 genderchar(1) 表示这是一个固定长度为1的字符类型。not null 指定这一列的值不允许为空。default '男' 指定了默认值为 ‘男’,即性别默认为男性。如果插入数据时未指定 gender 的值,则会默认为 ‘男’。不可以插入NULL,因为有一个非空属性。

我们在建表的时候,没有指定属性defaultnot null,但是MySQL会自动给我们加上:

代码语言:javascript复制
mysql> create table t17(
    -> name varchar(20),
    -> age tinyint unsigned
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t17G
*************************** 1. row ***************************
       Table: t17
Create Table: CREATE TABLE `t17` (
  `name` varchar(20) DEFAULT NULL,
  `age` tinyint unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

列描述

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

代码语言:javascript复制
mysql> create table if not exists t18( name varchar(20) not null comment '用户名字', age tinyint unsigned default 18 comment '用户年龄', gender char(1) default '男' comment '性别' );

mysql> show create table t18G
*************************** 1. row ***************************
       Table: t18
Create Table: CREATE TABLE `t18` (
  `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
1 row in set (0.00 sec)

注意:not nulldefalut一般不需要同时出现,因为default本身有默认值,不会为空。

zerofill

为了理解zerofill,先观察着两个表的区别;

不添加zerofill

代码语言:javascript复制
mysql> desc t19;
 ------- -------------- ------ ----- --------- ------- 
| Field | Type         | Null | Key | Default | Extra |
 ------- -------------- ------ ----- --------- ------- 
| a     | int unsigned | NO   |     | NULL    |       |
| b     | int unsigned | NO   |     | NULL    |       |
 ------- -------------- ------ ----- --------- ------- 
2 rows in set (0.01 sec)

mysql> insert into t19 (a,b) values (1,2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t19;
 --- --- 
| a | b |
 --- --- 
| 1 | 2 |
 --- --- 
1 row in set (0.00 sec)

修改b的属性,添加zerofill

代码语言:javascript复制
mysql> alter table t19 modify b int unsigned zerofill not null;
Query OK, 0 rows affected, 1 warning (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc t19;
 ------- --------------------------- ------ ----- --------- ------- 
| Field | Type                      | Null | Key | Default | Extra |
 ------- --------------------------- ------ ----- --------- ------- 
| a     | int unsigned              | NO   |     | NULL    |       |
| b     | int(10) unsigned zerofill | NO   |     | NULL    |       |
 ------- --------------------------- ------ ----- --------- ------- 
2 rows in set (0.00 sec)

mysql> select * from t19;
 --- ------------ 
| a | b          |
 --- ------------ 
| 1 | 0000000002 |
 --- ------------ 
1 row in set (0.00 sec)

mysql> insert into t19 values (100,200);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t19;
 ----- ------------ 
| a   | b          |
 ----- ------------ 
|   1 | 0000000002 |
| 100 | 0000000200 |
 ----- ------------ 
2 rows in set (0.00 sec)

这次可以看到b的值由原来的2变成0000000002,这就是zerofill属性的作用,如果宽度小于设定的宽度(这里设置的是10),自动填充0。要注意的是,这只是最后显示的结果,在MySQL中实际存储的还是2。

主键

主键:primary key用来唯一的约束该字段里面的数据,不能重复,不能为空,一张表中最多只能有一个主键;主键所在的列通常是整数类型。不意味着一个表中的主键只能添加给一列,于是有了复合主键。

  • 创建表的时候直接在字段上指定主键:
代码语言:javascript复制
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.02 sec)

mysql> desc test_key;
 ------- -------------- ------ ----- --------- ------- 
| Field | Type         | Null | Key | Default | Extra |
 ------- -------------- ------ ----- --------- ------- 
| id    | int unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)  | NO   |     | NULL    |       |
 ------- -------------- ------ ----- --------- ------- 
2 rows in set (0.00 sec)
  • 主键约束:主键对应的字段中不能重复,一旦重复,操作失败

  • 可以根据主键对表格内容进行增删查改:

增加:

代码语言:javascript复制
mysql> insert into test_key values (222,'liubei');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_key;
 ----- ---------- 
| id  | name     |
 ----- ---------- 
| 111 | zhangfei |
| 222 | liubei   |
 ----- ---------- 
2 rows in set (0.00 sec)

查找:

代码语言:javascript复制
mysql> select * from test_key where id=222;
 ----- -------- 
| id  | name   |
 ----- -------- 
| 222 | liubei |
 ----- -------- 
1 row in set (0.00 sec)

修改:

代码语言:javascript复制
mysql> update test_key set name='zhugeliang' where id=222;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_key;
 ----- ------------ 
| id  | name       |
 ----- ------------ 
| 111 | zhangfei   |
| 222 | zhugeliang |
 ----- ------------ 
2 rows in set (0.00 sec)

  • 删除主键:

语法格式:alter table 表名 drop primary key;

代码语言:javascript复制
mysql> alter table test_key drop primary key;
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc test_key;
 ------- -------------- ------ ----- --------- ------- 
| Field | Type         | Null | Key | Default | Extra |
 ------- -------------- ------ ----- --------- ------- 
| id    | int unsigned | NO   |     | NULL    |       |
| name  | varchar(20)  | NO   |     | NULL    |       |
 ------- -------------- ------ ----- --------- ------- 
2 rows in set (0.00 sec)

  • 当表创建好以后但是没有主键的时候,可以再次追加主键 语法格式:alter table 表名 add primary key(字段列表)
代码语言:javascript复制
mysql> alter table test_key add primary key(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_key;
 ------- -------------- ------ ----- --------- ------- 
| Field | Type         | Null | Key | Default | Extra |
 ------- -------------- ------ ----- --------- ------- 
| id    | int unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)  | NO   |     | NULL    |       |
 ------- -------------- ------ ----- --------- ------- 
2 rows in set (0.00 sec)

  • 复合主键
代码语言:javascript复制
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)

0 人点赞