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)