完整性约束：非空约束与测试默认值

非空约束

1. 被标志这非空的字段，不可以是空的。
   – 比如用户名与密码字段在注册网站的时候就被标志非空
2. 主键自动非空
3. 默认值是可以为空的
4. 非空一般会和默认值一起使用

mysql> CREATE TABLE IF NOT EXISTS user7(
    -> id INT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password CHAR(32) NOT NULL
    -> );
Query OK, 0 rows affected (0.77 sec)

mysql> INSERT user7 VALUES(1,"zhouruc16","123456");
Query OK, 1 row affected (0.46 sec)

mysql> DESC user7;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)      | NO   |     | NULL    |                |
| password | char(32)         | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT*FROM user7;
+----+-----------+----------+
| id | username  | password |
+----+-----------+----------+
|  1 | zhouruc16 | 123456   |
+----+-----------+----------+
1 row in set (0.00 sec)

默认值DEFAULT

1. 一般来说非空约束会配合默认值使用
2. DEFAULT 就是指在你不赋值的时候，给与一个默认值。 在非空字段中，如果没有赋值，将会直接使用默认值
3. 这里同样用用户注册做例子

mysql> CREATE TABLE IF NOT EXISTS user8(
    -> id INT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password CHAR(32) NOT NULL,
    -> age TINYINT UNSIGNED DEFAULT 18,
    -> addr VARCHAR(50) NOT NULL DEFAULT 'NY',
    -> sex ENUM('MALE','FEMALE','UNKNOWN') NOT NULL DEFAULT 'UNKNOWN'
    -> );
Query OK, 0 rows affected (0.77 sec)

mysql> INSERT user8 VALUES(1, "zhouruc16","123456",DEFAULT,DEFAULT,DEFAULT);
Query OK, 1 row affected (0.54 sec)

mysql> INSERT user8(username, password) VALUES("rzhou8","456789");
Query OK, 1 row affected (0.46 sec)

mysql> DESC user8;
+----------+---------------------------------+------+-----+---------+----------------+
| Field    | Type                            | Null | Key | Default | Extra          |
+----------+---------------------------------+------+-----+---------+----------------+
| id       | int(10) unsigned                | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)                     | NO   |     | NULL    |                |
| password | char(32)                        | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned             | YES  |     | 18      |                |
| addr     | varchar(50)                     | NO   |     | NY      |                |
| sex      | enum('MALE','FEMALE','UNKNOWN') | NO   |     | UNKNOWN |                |
+----------+---------------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> SELECT*FROM user8;
+----+-----------+----------+------+------+---------+
| id | username  | password | age  | addr | sex     |
+----+-----------+----------+------+------+---------+
|  1 | zhouruc16 | 123456   |   18 | NY   | UNKNOWN |
|  2 | rzhou8    | 456789   |   18 | NY   | UNKNOWN |
+----+-----------+----------+------+------+---------+
2 rows in set (0.00 sec)

mysql> INSERT user8(username, password,age,addr) VALUES("IFYOUFEEL","KOF9242",23,"310EXCHANGE");
Query OK, 1 row affected (0.47 sec)

mysql> SELECT*FROM user8;
+----+-----------+----------+------+-------------+---------+
| id | username  | password | age  | addr        | sex     |
+----+-----------+----------+------+-------------+---------+
|  1 | zhouruc16 | 123456   |   18 | NY          | UNKNOWN |
|  2 | rzhou8    | 456789   |   18 | NY          | UNKNOWN |
|  3 | IFYOUFEEL | KOF9242  |   23 | 310EXCHANGE | UNKNOWN |
+----+-----------+----------+------+-------------+---------+
3 rows in set (0.00 sec)