SQL自学总结二 MySQL的约束

一 基本概念

约束(constraint)实际上就是表中数据的限制条件, 表在设计的时候加入约束的目的就是保证表中的记录完整性和有效性，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复

约束分类主要有主键约束，自增长约束，非空约束，唯一性约束，默认约束，零填充约束，外键约束。

二 主键约束primary key

MySQL的主键约束是一个列或者多个列的组合，其值能唯一的标识表中每一行，方便在RDBMS中尽快找到某一行。主键约束相当于唯一约束和非空约束的组合，主键约束列不能重复，也不允许出现空值，每个表最多允许一个主键，当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引

添加单列主键1：在定义字段时同时指定主键,创建emp1表时指定id列为主键列

create table emp1(
id int primary key,
name varchar(20),
deptid int,
salary double
);

添加单列主键2：在定义字段后再指定主键

create table emp2(
id int ,
name varchar(20),
deptid int,
salary double，
constraint pk1 primary key(id)
);

添加多列主键，所谓联合主键，就是这个主键是由一张表中的多个字段组成，当主键由多个字段组成时，不能直接在字段后声明主键约束。一张表只能由一个主键，联合主键也是一个主键。

创建表emp3指定name和deptid组成联合主键

create table emp3(
id int ,
name varchar(20),
deptid int,
salary double，
primary key(name,deptid)
);

通过修改表结构添加主键：主键约束不仅可以在创建表时同时创建，也可以在修改表时添加

create table emp4(
id int ,
name varchar(20),
deptid int,
salary double，
);
alter table emp4 add primary key(id);

删除主键约束：删除emp4的主键

alter table emp4 drop primary key;

三 自增长约束auto_increment

在MySQL中，当主键定义为自增长时，这个主键的值就不需要用户输入数据了，而由数据库系统根据定义自动赋值，通过给字段添加auto_increment属性实现主键自增长。默认情况下，auto_increment 的初始值为1，每新增一条记录，字段值就加一。一个表中只能有一个字段使用自增长约束，且该字段必须有唯一索引，以避免序号重复。自增长约束的字段必须具备not null属性，自增长约束的字段只能是整数类型，自增长约束的字段最大值受该字段的数据类型约束，如果达到上限auto_increment就会失效

创建表user1，指定id列为主键并且自增长

create table user1(
id int primary key auto_increment,
name varchar(20)
);

指定自增字段的初始值： 如果第一条记录设置了该字段的初始值，那么新增加的记录就会从这个初始值开始自增。比如创建表时设置初始id为100

create table user1(
id int primary key auto_increment,
name varchar(20)
)auto_increment=100;

创建表后指定初始id为100

create table user2(
id int primary key auto_increment,
name varchar(20)
);
alter table user2 auto_increment=100;

delete数据之后自动增长从断点开始，truncate数据之后自动增长从默认起始值开始

四 非空约束not null 

MySQL 非空约束指定字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

创建表时添加非空约束

create table user3(
id int ,
name varchar(20) not null,
address varchar(20) not null
);

创建表后添加非空约束

create table user4(
id int ,
name varchar(20) ,
address varchar(20) 
);
alter table user4 modify name varchar(20) not null;
alter table user4 modify address varchar(20) not null;

删除表user4的非空约束

alter table user4 modify name varchar(20) ;
alter table user4 modify address varchar(20);

五 唯一约束unique

唯一约束是指所有记录中字段的值不能重复出现。例如为id字段加上唯一性约束后，每条记录的id都是唯一的

创建表时指定唯一约束

create table user5(
id int ,
name varchar(20) ,
phone varchar(20) unique
);

创建表后指定唯一约束

create table user5(
id int ,
name varchar(20) ,
phone varchar(20) 
);
alter table user5 add constraint unique_ph unique(phone);

删除唯一约束

alter table user5 drop index unique_ph;

六 默认约束default

添加默认约束：创建表user6,指定address列默认值为北京

create table user5(
id int ,
name varchar(20) ,
address varchar(20) default '北京'
);

创建表后指定默认约束

create table user5(
id int ,
name varchar(20) ,
address varchar(20) 
);
alter table user5 modify address varchar(20) default '北京';

删除默认约束

alter table user5 modify column address varchar(20) default null;

七 零填充约束zerofill

插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0，zerofill默认为int(10),当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128~+127，无符号为0~256。

添加零填充约束

create table user6(
id int zerofill,
name varchar(20)
);

删除

alter table user6 modify id int;