需要一些约束条件来保证数据的一致性和完整性。
完整性约束条件(非常重要):
- unsigned:无符号,没有负数从0开始;
- zerofill :零填充,当显示长度不够的时候可以使用前补0的效果填充到指定的长度;
- NOT NULL 非空约束,也就是插入值的时候这个字段必须要给值;
- PRIMARY KEY:主键,标识记录的唯一性,值不能重复,一个表只有一个主键;
- UNIQUE KEY:唯一性,一个表中可以有多个字段是唯一索引,同样的值不能重复但是null除外;
- AUTO_INCREMENTS:自动增长,只能用于数值列,而且配合索引使用;
- FOREIGN KEY:外键约束
主键
主键的目的是标识记录的唯一性,目的是快速定位到表中的某一条元素。一般给一些无意义的字段设置位主键比如说编号。
1 2 3 4 5 6 7 8 |
-- 测试主键 CREATE TABLE test_primary_key( id INT UNSIGNED PRIMARY KEY, username VARCHAR(20) ); INSERT test_primary_key(id,username) VALUES(1,'king'); INSERT test_primary_key(id,username) VALUES(1,'king123'); INSERT test_primary_key(username) VALUES('QUEEN'); |
可以看到一旦设置为主键会被默认不能为空,主键是唯一的,重复的主键会报错并且不能够添加。另外主键可以省略primary
1 2 3 4 |
CREATE TABLE test_primary_key1( id INT UNSIGNED KEY, username VARCHAR(20) ); |
也可以在下方通过PRIMARY KEY来指定主键,对于多字段主键比较方便。
1 2 3 4 5 |
CREATE TABLE test_primary_key2( id INT UNSIGNED, username VARCHAR(20), PRIMARY KEY(id) ); |
一个表中只能有一个主键,否则会报错
1 2 3 4 5 6 |
CREATE TABLE test_primary_key3( id INT UNSIGNED PRIMARY KEY, courseId INT UNSIGNED PRIMARY KEY, username VARCHAR(20), email VARCHAR(50) ); |
但是我们可以使用复合主键:
符合主键由多个字段组成,这是允许的
1 2 3 4 5 6 7 |
CREATE TABLE test_primary_key3( id INT UNSIGNED, courseId INT UNSIGNED, username VARCHAR(20), email VARCHAR(50), PRIMARY KEY(id,courseId) ); |
这个时候并不是有两个主键,因为之前我们提到过主键只可以有一个,这个时候id和courseId是一个整体,也就是他们两个可以共同定位到同一条记录,这个时候它们两个都全部相同才能代表重复记录,只有一条相同不算重复。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE test_primary_key4( id INT UNSIGNED, courseId VARCHAR(20), username VARCHAR(20), email VARCHAR(50), PRIMARY KEY(id,courseId) ); -- 复合主键 -- 1-a INSERT test_primary_key4(id,courseId,username,email) VALUES(1,'a','king','382771946@qq.com'); INSERT test_primary_key4(id,courseId,username,email) VALUES(1,'b','king','382771946@qq.com'); INSERT test_primary_key4(id,courseId,username,email) VALUES(2,'a','king','382771946@qq.com'); INSERT test_primary_key4(id,courseId,username,email) VALUES(1,'a','king','1382771946@qq.com'); |
可以看到id和courseId只要有一个不一样就是可以的,如果两个一样比如说有重新插入1和a那么就会报错。一般复合主键最多只用到两个。
自增
就是自动排号,默认起始值从1开始,默认增长1
需要注意的是默认增长的需要是主键。
1 2 3 4 5 6 7 8 |
CREATE TABLE test_auto_increment( id INT UNSIGNED KEY AUTO_INCREMENT, username VARCHAR(20) ); INSERT test_auto_increment(username) VALUES('A'); INSERT test_auto_increment(username) VALUES('B'); INSERT test_auto_increment(username) VALUES('C'); |
可以看到没有给username值的时候会自动给id,每次增加1。
1 2 3 |
INSERT test_auto_increment(id,username) VALUES(NULL,'E'); INSERT test_auto_increment(id,username) VALUES(DEFAULT,'F'); INSERT test_auto_increment(id,username) VALUES(15,'G'); |
如果给的值是NULL或者DEFAULT不会影响自增,如果给出了指定的数值,那么id就会使我们指定的数值。
NOT NULL 非空约束
插入值的时候必须是有值,并且不能是空值,空值不能够存入并且会报错。
注意:空字符串不是空值。
1 2 3 4 5 6 7 8 9 10 |
-- 测试非空 CREATE TABLE test_not_null( a varchar(20), b varchar(20) not null ); INSERT test_not_null(a,b) VALUES('',''); INSERT test_not_null(a,b) VALUES(NULL,NULL); INSERT test_not_null(a,b) VALUES(NULL,'abc'); INSERT test_not_null(a) VALUES('TEST'); |
DEFAULT 默认值
当没有给值的时候设定为默认值。
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE test_default( id INT UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(20) NOT NULL, age TINYINT UNSIGNED DEFAULT 18, email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com' ); INSERT test_default(username) VALUES('A'); INSERT test_default(username,age,email) VALUES('B',30,'imooc@qq.com'); INSERT test_default(username,age,email) VALUES('C',NULL,'imooc@qq.com'); INSERT test_default(username,age,email) VALUES('D',NULL,NULL); INSERT test_default(username,age,email) VALUES('D',NULL,DEFAULT); |
id是自增长的,age是可以为空的,但没有给age赋值的时候为默认值18,email没有给值的时候为默认值。
另外,枚举的时候也可以给出默认值:
1 2 3 4 |
CREATE TABLE test_default1( id INT UNSIGNED AUTO_INCREMENT KEY, sex ENUM('a','b','c') NOT NULL DEFAULT 'a' ); |
UNIQUR KEY
唯一性索引,一个表中可以有多个字段是唯一索引,同样的值不能重复,但是null不算重复,比如说电话邮箱是唯一的,就可以给这个约束
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 测试UNIQUE KEY CREATE TABLE test_unique( id INT UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(20) NOT NULL UNIQUE KEY, email VARCHAR(50) UNIQUE, card CHAR(18) UNIQUE ); INSERT test_unique(username,email,card) VALUES('A','A@QQ.COM','1'); INSERT test_unique(username,email,card) VALUES('A','1A@QQ.COM','12'); INSERT test_unique(username,email,card) VALUES('B',NULL,NULL); INSERT test_unique(username,email,card) VALUES('C',NULL,NULL); |
可以看到当插入重复的值的时候会报错,但是NULL是可以重复的,就说,邮箱电话之类的不能够重复,但是可以不填。