1、DEFAULT:默认值约束
比如当插入一些数据为空或者没有插入数据的时候,我们可以给一个默认值
mysql> CREATE TABLE students (
-> no INT,
-> name VARCHAR(32),
-> sex CHAR(1) DEFAULT '男',
-> age INT(3),
-> email VARCHAR(255)
-> );
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> desc students
-> ;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no | int | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | char(1) | YES | | 男 | |
| age | int | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
插入语句进行验证:
mysql> insert into students(no,name,age,email) values(1,'www',18,'123@qq.***');
Query OK, 1 row affected (0.01 sec)
mysql> select * from students;
+------+------+------+------+------------+
| no | name | sex | age | email |
+------+------+------+------+------------+
| 1 | www | 男 | 18 | 123@qq.*** |
+------+------+------+------+------------+
1 row in set (0.00 sec)
mysql> insert into students(no,name,sex,age,email) values(2,'zzz','女',18,'123@qq.***');
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+------+------+------+------+------------+
| no | name | sex | age | email |
+------+------+------+------+------------+
| 1 | www | 男 | 18 | 123@qq.*** |
| 2 | zzz | 女 | 18 | 123@qq.*** |
+------+------+------+------+------------+
2 rows in set (0.00 sec)
(如下同理)
2、使用ENUM类型限制字段取值
ENUM
是一个字符串对象,其值是从列创建时定义的允许值列表中选择的
CREATE TABLE student (
name VARCHAR(32),
sex ENUM('男', '女'),
age INT(3),
email VARCHAR(255)
);
3、CHECK 检查约束
注意:(CHECK 约束:用于限制列中的值的范围,MySQL5.7不支持该约束,但写入语句不会报错,MySQL8.0版本支持该约束。)
create table test_user
(
id int primary key,
name VARCHAR(20),
sex VARCHAR(1),
check (sex='男'or sex='女')
);