一、约束

1 null / not null

1.1 创建表的时候默认是：null

也就是默认情况下，表中字段的值是允许存储为空值的：NULL

mysql> create table t1(num int, ft float, name varchar(10), neum1 enum('a','b') );
Query OK, 0 rows affected (0.08 sec)

mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| num   | int(11)       | YES  |     | NULL    |       |
| ft    | float         | YES  |     | NULL    |       |
| name  | varchar(10)   | YES  |     | NULL    |       |
| neum1 | enum('a','b') | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into t1 (num) value(1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t1;
+------+------+------+-------+
| num  | ft   | name | neum1 |
+------+------+------+-------+
|    1 | NULL | NULL | NULL  |
+------+------+------+-------+
1 row in set (0.00 sec)

mysql>

1.2 设置为非空: not null

当设置某个字段的值为非空时候，存储值的时候就必须提供符合其字段属性的值。

mysql> create table t2(num int not null, name varchar(10) not null);
Query OK, 0 rows affected (0.09 sec)
mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

表现形式就是 Null 列的值是 NO

查看入值做测试:

mysql> insert into t2(num) value(10);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql>
mysql>
mysql> insert into t2(num, name) value(10, 'shark');
Query OK, 1 row affected (0.03 sec)

mysql> select * from t2;
+-----+-------+
| num | name  |
+-----+-------+
|  10 | shark |
+-----+-------+
1 row in set (0.00 sec)

当某个字段不允许存放 NULL 的情况下，就必须同一个具体的值。或给其提供一个默认值。

二、默认值 default

mysql> create table t3(num int default 10  , name varchar(10) not null default 'shark');
Query OK, 0 rows affected (0.13 sec)

mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(11)     | YES  |     | 10      |       |
| name  | varchar(10) | NO   |     | shark   |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into t3(num) value(20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3(name) value('xiguatian');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t3;
+------+-----------+
| num  | name      |
+------+-----------+
|   20 | shark     |
|   10 | xiguatian |
+------+-----------+
2 rows in set (0.00 sec)

mysql>

三、唯一性 unique

1 单字段唯一

指定是在同一个表中的某个字段的值必须是唯一的，不允许出现重复的值。

同一个表中可以有多个字段设置唯一性。

表达主键默认有唯一性属性

2 联合唯一

可以将多个字段联合起来进行唯一性约束

假如我们以字段 name 和 age 作为联合唯一

id	name	age
	shark	19
2	xiguatian	19
3	shark	18

3 创建表时候设置唯一性

3.1 单个字段的唯一性的创建

语法

create table 表名(字段1 数据属性 unique);

示例：

create table t5(num int unique, name varchar(6) unique);

3.2 联合唯一的创建

语法

create table 表名(字段1 属性,字段2 属性, unique(字段1,字段2));

示例：

create table t6(id int primary key,name varchar(6),age int, unique(name, age));

插入数据：

mysql> insert into t6 value(1,'shark', 18);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t6 value(1,'shark', 19);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into t6 value(2,'shark', 19);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t6 value(3,'xi', 19);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> select * from t6;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | shark |   18 |
|  2 | shark |   19 |
|  3 | xi    |   19 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql>

4 唯一性的约束值的范围

NULL 不受唯一性约束

“” 空的字符串是手唯一性约束的

四、自增 auto_increment

每张表只有一个字段为自增,需要给一个主键设置自增。

语法

create table t7(字段 int auto_increment primary key);

示例

mysql> create table t7(id int   auto_increment primary key,  name varchar(10));
Query OK, 0 rows affected (0.07 sec)

mysql> desc t7;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

插入数据

mysql> insert into t7(name) value('a');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t7(name) value('a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t7(name) value('a');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t7;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |

五、主键

每张表只能有一个主键，可以给一个字段设置主键，也可以给多个字段进行组合起来创建联合主键。

六、外键

在一个表中可以有多个外键

被参考的主表中的字段必须是主键。

语法

# 主表、主键
create table t11(字段1 int, 字段2 varchar(6) primary key);

# 从表、外键
create table t12(
    字段1 int, 字段2 varchar(10),字段3 varchar(6),
    foreign key (从表的字段3) references  t11(主表的主键字段2));

示例：

create table t1(id int,manager char(10) primary key);

create table t12(id int,admin char(10),foreign key (admin) references  t11 (manager)); 

插入数据

mysql> insert into t11 value(1, "abc");
Query OK, 1 row affected (0.03 sec)

mysql> insert into t11 value(2, "cde");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t11;
+------+---------+
| id   | manager |
+------+---------+
|    1 | abc     |
|    2 | cde     |
+------+---------+
2 rows in set (0.00 sec)

mysql> insert into t12 value(1,'abc')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t12 value(2,'cde');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t12 value(2,'efg');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`t12`, CONSTRAINT `t12_ibfk_1` FOREIGN KEY (`admin`) REFERENCES `t11` (`manager`))
mysql>

删除数据

必须先删除从表中对应的值，才能删除主表中的值。

mysql> select * from t12;
+------+-------+
| id   | admin |
+------+-------+
|    1 | abc   |
|    2 | cde   |
+------+-------+
2 rows in set (0.00 sec)

mysql> select * from t11;
+------+---------+
| id   | manager |
+------+---------+
|    1 | abc     |
|    2 | cde     |
+------+---------+
2 rows in set (0.01 sec)

mysql> delete from t12 where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * t12;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't12' at line 1
mysql> select * from t12;
+------+-------+
| id   | admin |
+------+-------+
|    2 | cde   |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from t11;
+------+---------+
| id   | manager |
+------+---------+
|    1 | abc     |
|    2 | cde     |
+------+---------+
2 rows in set (0.00 sec)

mysql> delete from t11 where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t11;
+------+---------+
| id   | manager |
+------+---------+
|    2 | cde     |
+------+---------+
1 row in set (0.00 sec)

mysql> select * from t12;
+------+-------+
| id   | admin |
+------+-------+
|    2 | cde   |
+------+-------+
1 row in set (0.00 sec)