为了保证MySQL中数据存储的可靠性,此时需要对存储的数据进行额外的约束,倒逼用户输入正确格式的数据。
空属性
空属于用于限制值是否允许为空,默认值为null
表示允许为空,not null
表示不允许为空。虽然默认可以为空,但由于一些字段要参与运算,而空无法被计算,所以要尽可能保证不为空。
create table t1(
class_name varchar(20) not null,
class_room varchar(20) not null,
other varchar(20)
);
第三列Null
表示该列是否允许为空,建表时设置not null
的列该项为NO
,而other
列没有指定,默认为YES
。
默认值
在插入数据时,如果某些列没有指定数据,那么就会启用默认值。默认值通过default
来指定。
create table t2(
name varchar(20) not null,
age tinyint unsigned default 18,
gender char(1) default '男'
);
值得注意的是,name
列设置了not null
属性,但是其default
为NULL
。如果你不插入name
列,此时启用默认值NULL
,但是由于该列有not null
属性,最后就会报错。
列描述
列描述相当于代码中的注释,用于描述一个列的作用。它对列的约束不是硬性的,而是给维护该数据库的程序员看的,当程序员看到该列的列描述后,就知道这个列要放入什么数据,从而形成一个软性约束。
列描述通过comment
关键字来指定:
create table t3(
name varchar(20) not null comment '用户名',
age tinyint unsigned default 18 comment '年龄',
gender char(1) default '男' comment '性别'
);
想要查看列描述,可以通过show create table
指令:
零填充
零填充用于控制数据显示的格式,当数据位数不足时,自动在前面补0
。零填充通过zerofill
关键字来指定。
create table t4(
a int,
b int(5),
c int(5) zerofill
);
通过desc
查看表的属性:
在Type
列中,int
后面()
内的数字,用于指定数据的显示宽度,在没有zerofill
属性时,该数字无意义。注意该值不影响任何数据的真实存储范围,存储范围由unsigned
以及int
,tinyint
等类型本身的属性来决定。
显示宽度要配合zerofill
使用才有意义,c
的类型是int(5)
,但是最后却多出了unsigned
。在指定zerofill
属性时,会自动给整型添加unsigned
属性。如果数字显示时位数不足5位,就会自动填充到5位。第二次插入了1234567
,超出了5位,但是显示时没有截断。说明()
内的数字,不会影响数据的显示。
主键
主键用于约束每一行数据之间的唯一性,被指定为主键的列不允许重复,不能为空,且一张表只能有一个主键。
建表时,通过primary key
来指定主键。
create table t5(
id int unsigned primary key,
name varchar(20)
);
id
被指定为了主键,其Key
的值位PRI
表示这是主键,被设置主键的同时,自动增加了not null
属性。
主键不允许重复:
- 当创建好表后如果没有主键,可以追加主键:
alter table 表名 add primary key(列名);
- 删除主键:
alter table 表名 drop primary key;
复合主键
建表时,可以让多个列共同成为一个主键,这种主键叫做复合主键。
通过primary key (列1, 列2 ...)
来指定复合主键。
create table t6(
id_1 int unsigned,
id_2 int unsigned,
name varchar(20),
primary key (id_1, id_2)
);
第一次的复合主键为(1, 555)
,第二次的复合主键为(1, 666)
,虽然两次的id_1
重复了,但是复合主键这个整体没有重复,所以插入合法。第三次同理,只有id_2
重复。
第四次插入,复合主键为(1, 555)
,与第一次插入重复,所以不允许插入。
复合主键是一个整体,只有整体重复了,此时插入才会受限。
自增长
被设置为自增长的列,如果插入数据时没有指定值,那么会根据当前列中的最大值+1
进行插入。
自增长要求:
- 任何一个字段想要自增长,前提是该列本身是一个索引(
Key
栏有值) - 自增长字段必须是整数
- 一张表最多只有一个自增长
通过auto_increment
来设置自增长。
create table t7(
id int unsigned primary key auto_increment,
name varchar(20) not null
);
其中id
列被设为了主键,Key
的值为PRI
,该列有值说明是索引,因此可以添加auto_increment
属性,在Extra
中可以看到id
具有该属性。 插入李四
时,指定了id=100
,下一次插入王五
没有指定id
,其自动增长为了101
。自增长的值为该列中的最大值+1
。
其实自增长是依靠一个变量auto_increment
来计算的,该变量可以在建表时指定:
create table t8(
id int unsigned primary key auto_increment,
name varchar(20) not null
)auto_increment=50;
变量auto_increment的含义是:下一次自增长的值。每次插入数据时,如果没有指定自增长的列,那么该列填入auto_increment,随后auto_increment + 1。如果指定了自增长的列,那么就把插入的值与auto_increment对比,如果新来的值更大,就更新auto_increment的值为新来的值 + 1。
要注意的是:删除数据不会影响auto_increment
的值,也就是说如果删除了最大值,不会导致auto_increment
变小。
唯一键
表中往往有很多字段都需要唯一性,主键只有一个,不可能所有字段都通过主键来保证唯一性,此时就需要唯一键。
唯一键允许为空,空字段不做唯一性比较,也就是说对于有数据的内容不允许重复,但是允许多个数据同时为空。
唯一键通过unique key
或unique
关键字指定,后面的key
省略。
create table t9(
id char(20) unique,
name varchar(32) not null
);
插入重复的1
时,发生报错,因为破坏了唯一性。但是插入多条数据id
为空是合法的,因为唯一键不对空进行重复性比较。
外键
在数据库中,往往不只有一张表,而多张表之间往往是有从属关系的。左侧是学生表,右侧是班级表。学生表中class
被指定为外键,受到班级表中id
的限制,班级表中id
列叫做参照键。
上图中,1 2 3 5
是合理的,也就是说外键允许为空,而4
是错误的数据,因为根本没有103
这个班级,也就是说外键的数据必须在参照键中存在。
外键在从表中,参照键在主表中。外键受到参照键影响:
- 外键的值必须在参照键中存在
- 外键的值允许为空
建表时,设置外键的语法为:
foreign key(外键) references 主表(参照键)
另外的,参照键必须具有唯一性,一般为主键或者唯一键。
主表 - 班级表:
create table class(
id int primary key,
name varchar(32) not null
);
从表 - 学生表:
create table student(
id int unsigned primary key,
name varchar(32) not null,
tel varchar(32) not null,
class_id int,
foreign key(class_id) references class(id)
);
建立学生表时,foreign key(class_id) references class(id)
就建立了外键,对应的参照键为class
表的id
。外键class_id
的Key
属性为MUL
,表示外键。
另外的,外键其实也会反过来约束参照键:删除班级表的id=101
的数据时报错了。因为如果把这个数据删掉,那么外键中class_id=101
的值就会出错,所以不允许删除这个数据。