MySQL的约束

MySQL约束介绍：约束顾名思义就是不允许去做某些事情。

约束有：

唯一约束：对当前的表中某一个的字段加了唯一约束之后，这个表中的这个字段不允许出现重复值

非空约束：不允许出现空值

主键约束：唯一约束+非空约束

外键约束：保证表与表之间数据的完整性和准确性

创建表设置唯一约束

mysql> create table stu(num int unique,name varchar(11));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into stu values(1,'chenxi');
Query OK, 1 row affected (0.05 sec)
 
mysql> insert into stu values(1,'chenxi');
ERROR 1062 (23000): Duplicate entry '1' for key 'num'
mysql> insert into stu values(2,'chenxi');
Query OK, 1 row affected (0.05 sec)
mysql> select * from stu;
+------+--------+
| num  | name   |
+------+--------+
|    1 | chenxi |
|    2 | chenxi |
+------+--------+
2 rows in set (0.00 sec)

　　查看表结构

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

　　查如的数据可以为空

mysql> insert into stu values(null,"cx");
Query OK, 1 row affected (0.04 sec)
 
mysql> insert into stu values(null,"yu");
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from stu;
+------+--------+
| num  | name   |
+------+--------+
|    1 | chenxi |
|    2 | chenxi |
| NULL | cx     |
| NULL | yu     |
+------+--------+
4 rows in set (0.00 sec)

　　非空约束

mysql> create table stu(num int not null,name varchar(11));
Query OK, 0 rows affected (0.05 sec)
 
mysql> insert into stu values(null,"yu");
ERROR 1048 (23000): Column 'num' cannot be null
mysql> insert into stu values(1,"yu");
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into stu values(1,"yu");
Query OK, 1 row affected (0.03 sec)
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(11)     | NO   |     | NULL    |       |
| name  | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

　　创建非空约束与唯一建约束

mysql> drop table stu;  删除
Query OK, 0 rows affected (0.03 sec)
 
mysql> create table stu(num int not null unique,name varchar(11));  创建
Query OK, 0 rows affected (0.04 sec)
 
mysql> insert into stu values(1,"cx");  插入
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into stu values(1,"cx");  插入
ERROR 1062 (23000): Duplicate entry '1' for key 'num'
mysql> insert into stu values(null,"cx");
ERROR 1048 (23000): Column 'num' cannot be null
mysql> desc stu;  查看表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

　　主键约束：primary key

mysql> drop table stu;
Query OK, 0 rows affected (0.03 sec)
 
mysql> create table stu(num int primary key,name varchar(11));
Query OK, 0 rows affected (0.04 sec)
 
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> insert into stu values(null,"cx");
ERROR 1048 (23000): Column 'num' cannot be null
mysql> insert into stu values(1,"cx");
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into stu values(1,"cx");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

　　创建一张一列数据为自增长的数据；且设置主键约束

MySQL为用户提供了一种方式，主键由mysql帮你生成。也就是自动增长。一般数字类型主键配合mysql的自动增长策略，这个自动增长只是策略并不是约束。主键约束：primary；自动增长名称：auto_increment

mysql> drop table stu;
Query OK, 0 rows affected (0.04 sec)
 
mysql> create table stu(num int primary key auto_increment,name varchar(11));
Query OK, 0 rows affected (0.41 sec)
 
mysql> desc stu;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| num   | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(11) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

　　插入数据

mysql> insert into stu(name) values('zhaoruidong');
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into stu(name) values('chenxi');
Query OK, 1 row affected (0.05 sec)
 
mysql> insert into stu(name) values('cx');
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from stu;
+-----+-------------+
| num | name        |
+-----+-------------+
|   1 | zhaoruidong |
|   2 | chenxi      |
|   3 | cx          |
+-----+-------------+
3 rows in set (0.00 sec)

　　外键约束--clazz表num字段必须为主键

mysql> create table clazz(num int primary key auto_increment,name varchar(11));
Query OK, 0 rows affected (0.41 sec)
mysql> create table stu(num int primary key auto_increment,name varchar(11),clazznum int, foreign key(clazznum) references clazz(num));
Query OK, 0 rows affected (0.24 sec)
 
mysql> desc clazz;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| num   | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(11) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
 
mysql> desc stu;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| num      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(11) | YES  |     | NULL    |                |
| clazznum | int(11)     | YES  | MUL | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
 
 
--primary key
--auto_increment
--foreign key(clazznum) #外键约束，约束哪一个字段
--references clazz(num))    #stu表clazznum字段参考clazz表的num字段
 
注意：在MySQL中外键必须是另一张表的主键

　　插入数据测试

mysql> insert into stu values(1,'zhang',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`chenxi`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`clazznum`) REFERENCES `clazz` (`num`))   # 因为clazz表num字段里没有1
mysql> insert into clazz values(1,'one');   
Query OK, 1 row affected (0.41 sec)   #在clazz表里插入一条为1 的数据
 
mysql> insert into stu values(1,'zhang',1);     
Query OK, 1 row affected (0.11 sec)   # 因为clazz表里num字段为1，
 
mysql> insert into stu values(1,'zhang',2);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'# 因为clazz表里num字段没有为2的数据
mysql> insert into stu values(2,'zhao',1);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from clazz;
+-----+------+
| num | name |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.02 sec)
 
mysql> select * from stu;
+-----+-------+----------+
| num | name  | clazznum |
+-----+-------+----------+
|   1 | zhang |        1 |
|   2 | zhao  |        1 |
+-----+-------+----------+
2 rows in set (0.00 sec)