MYSQL约束
各期链接
空属性
NULL不参与数据的计算,所以有些数据必须保证其值不为空
mysql> select null;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> select null+1;
+--------+
| null+1 |
+--------+
| NULL | << 不参与计算
+--------+
1 row in set (0.00 sec)
使用NOT NULL可以防止一些数据为空
NOT NULL
示例
create table tt1(
id int NOT NULL,
name varchar(10) NOT NULL
);
insert into tt1 (id,name) values (1,'张三');
-- 无法插入
insert into tt1 (id) values (2);
mysql> create table tt1(
-> id int NOT NULL,
-> name varchar(10) NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tt1 (id,name) values (1,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt1 (id) values (2);
ERROR 1364 (HY000): Field 'name' doesn't have a default value << 设置了NOT NULL,name默认值不能为空,sql拒绝插入
mysql> desc tt1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select * from tt1;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
+----+--------+
1 row in set (0.00 sec)
默认值
设置当你没有给数据赋值时会sql默认赋给数据的值
default
示例
create table tt2(
name varchar(10) NOT NULL,
age int NOT NULL,
sex varchar(4) DEFAULT '男' NOT NULL
);
insert into tt2 values('张三',18,'男');
-- 没有设置sex的值,sql默认插入
insert into tt2 (name,age) values('李四',19);
mysql> desc tt2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| sex | varchar(4) | NO | | 男 | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from tt2;
+--------+-----+-----+
| name | age | sex |
+--------+-----+-----+
| 张三 | 18 | 男 |
| 李四 | 19 | 男 |
+--------+-----+-----+
2 rows in set (0.01 sec)
列描述
对指定列做注释,对sql不起作用给程序员看的
comment
__示例
create table tt3(
name varchar(10) comment '名字',
age int comment '年龄',
sex varchar(3) comment '性别'
);
mysql> show create table tt3 \g
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt3 | CREATE TABLE `tt3` (
`name` varchar(10) DEFAULT NULL COMMENT '名字',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`sex` varchar(3) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
主键
- 主键用来唯一约束一个字段数据,不能为空,不能重复。
- 一张表只能有一个主键
- 主键通常为整数
primary key
create table tt4(
id int primary key,
name varchar(10),
age int
);
insert into tt4 values(1,'沮授',25);
-- 主键不能为空,插入失败
insert into tt4 (name,age)values('袁绍',36);
-- 主键不能重复,插入失败
insert into tt4 (id,name,age)values(1,'许攸',28);
insert into tt4 (id,name,age)values(2,'刘表',28);
mysql> create table tt4(
-> id int primary key,
-> name varchar(10),
-> age int
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tt4 values(1,'沮授',25);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt4 (name,age)values('袁绍',36);
ERROR 1364 (HY000): Field 'id' doesn't have a default value << 主键不能为空
mysql> insert into tt4 (id,name,age)values(1,'许攸',28);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' << 主键不能重复
mysql> insert into tt4 (id,name,age)values(2,'刘表',28);
Query OK, 1 row affected (0.00 sec)
mysql> select *from tt4;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 沮授 | 25 |
| 2 | 刘表 | 28 |
+----+--------+------+
2 rows in set (0.00 sec)
自增长
设置一个数据默认从1开始,插入数据后自己增加1
auto_increment
注意
- 做自增长必须本身是索引(key栏有值)
- 一张表最多有一个字段有自增长
- 自增长字段必须是整数
示例
create table tt5(
id int auto_increment primary key,
name varchar(10) not null,
age int not null
);
insert into tt5 (name,age) values ('张三',20);
insert into tt5 (name,age) values ('李四',23);
insert into tt5 (id,name,age) values (5,'王五',18);
insert into tt5 (name,age) values ('赵六',19);
mysql> select * from tt5;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | 张三 | 20 |
| 2 | 李四 | 23 |
| 5 | 王五 | 18 |
| 6 | 赵六 | 19 |
+----+--------+-----+
4 rows in set (0.00 sec)
mysql> desc tt5;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
唯一键
除了主键还需要有限制字段唯一的必要,但是主键一个表只有一个所以就需要唯一键来约束字段唯一。(唯一键可以为空,一张表可以多个)
unique (key)
示例
create table tt6(
id int unique key,
QQ varchar(10) unique,
name varchar(10)
);
insert into tt6 values(1,'12345','张良');
-- 可以为空
insert into tt6 (name) values ('刘邦');
-- 不能重复
insert into tt6 values(1,'12346','项羽');
-- 正常插入
insert into tt6 values(2,'12346','项羽');
-- null不能做数据计算,不算重复
insert into tt6 (name) values ('韩信');
mysql> create table tt6(
-> id int unique key,
-> QQ varchar(10) unique,
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> insert into tt6 values(1,'12345','张良');
Query OK, 1 row affected (0.03 sec)
mysql> insert into tt6 (name) values ('刘邦');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt6 values(1,'12346','项羽');
ERROR 1062 (23000): Duplicate entry '1' for key 'id' << 唯一键不能重复
mysql> insert into tt6 values(2,'12346','项羽');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt6 (name) values ('韩信');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt6;
+------+-------+--------+
| id | QQ | name |
+------+-------+--------+
| 1 | 12345 | 张良 |
| NULL | NULL | 刘邦 |
| 2 | 12346 | 项羽 |
| NULL | NULL | 韩信 |
+------+-------+--------+
4 rows in set (0.00 sec)
外键
foreign key (字段名) references 主表(列名)
示例
create table class(
class_index int primary key auto_increment,
class_name varchar(10) unique not null
);
create table student(
id int primary key auto_increment,
name varchar(10),
class_id int,
foreign key(class_id) references class(class_index)
);
insert into class (class_name)values('c++');
insert into class (class_name)values('java');
insert into class(class_name)values('python');
insert into student values(1,'张三',1);
insert into student values(2,'李四',2);
insert into student values(3,'王五',3);
-- 没有这个班级无法插入
insert into student values(4,'张三',4);
-- class表中关联student无法删除
drop table class;
-- 先删除student表即可删除class表
drop table student;
drop table class;
sql> select * from class;
+-------------+------------+
| class_index | class_name |
+-------------+------------+
| 1 | c++ |
| 2 | java |
| 3 | python |
+-------------+------------+
3 rows in set (0.01 sec)
mysql> select * from student;
+----+--------+----------+
| id | name | class_id |
+----+--------+----------+
| 1 | 张三 | 1 |
| 2 | 李四 | 2 |
| 3 | 王五 | 3 |
+----+--------+----------+
3 rows in set (0.00 sec)
mysql> insert into student values(4,'张三',4); << 没有这个班级无法插入
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`Type`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_index`))
mysql> drop table class;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table class;
Query OK, 0 rows affected (0.00 sec)
zerofill
补零
create table tt7(
id int,
name varchar(10)
);
insert into tt7 values(1,'张三');
show create table tt7 \g
select * from tt7;
alter table tt7 modify id int zerofill;
show create table tt7 \g
select * from tt7;
mysql> show create table tt7 \g
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| tt7 | CREATE TABLE `tt7` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from tt7;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 | << 没有加zerofill
+------+--------+
1 row in set (0.01 sec)
mysql> alter table tt7 modify id int zerofill;
Query OK, 1 row affected (0.16 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table tt7 \g
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| tt7 | CREATE TABLE `tt7` (
`id` int(10) unsigned zerofill DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from tt7;
+------------+--------+
| id | name |
+------------+--------+
| 0000000001 | 张三 | << 增加了zerofill
+------------+--------+
1 row in set (0.00 sec)