MySQL数据库(四):约束条件

@lianst  July 23, 2016

安装环境:
操作系统版本:RHEL 6.5
版本:MYSQL 5.5


约束条件的作用:
限制如何给字段赋值,创建表的时候如果没有设置的话,就是mysql建表的默认设置包括表结构的后四列。

1、NULL
是否允许空值 在不设置的时候,默认允许字段值为空

mysql> desc yueshu;
+-------+--------------------+------+-----+---------+-------+
| Field | Type               | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| name  | varchar(20)        | NO   | PRI | NULL    |       |
| age   | int(2)             | YES  |     | 10      |       |
| sex   | enum('boy','girl') | YES  |     | boy     |       |
+-------+--------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

查询字段值是否为空

mysql> select * from yueshu where age is null;
+------+------+------+
| name | age  | sex  |
+------+------+------+
|      | NULL | boy  |
+------+------+------+
1 row in set (0.00 sec)
mysql> insert into yueshu(age) values(null);
Query OK, 1 row affected, 1 warning (0.00 sec)

查询字段的值

mysql> select * from yueshu where sex="boy";
+------+------+------+
| name | age  | sex  |
+------+------+------+
|      | NULL | boy  |
+------+------+------+
1 row in set (0.00 sec)

2、NOT NULL
不允许为空

mysql> desc yueshu;
+-------+--------------------+------+-----+---------+-------+
| Field | Type               | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| name  | varchar(20)        | NO   | PRI | NULL    |       |
| age   | int(2)             | YES  |     | 10      |       |
| sex   | enum('boy','girl') | YES  |     | boy     |       |
+-------+--------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into yueshu(name) values(null);
ERROR 1048 (23000): Column 'name' cannot be null

3、KEY
索引类型(是否为主键)
4、DEFAULT
设置默认值,缺省为NULL.向表中插入新的记录时,若不给字段赋值,就使用默认值给字段赋值。建表时,没有设置字段的默认值,mysql吧字段的默认值设置为空

例子:

mysql> create table yueshu(
    -> name varchar(20) not null,
    -> age int(2) default 10,
    -> sex enum("boy","girl") default "boy",
    -> primary key(name)
    -> );


添加新评论