MySql约束整理 - 乐耶园

MySQL约束

1.主键约束(primary key)——PK

2.自增长约束(auto_increment)

3.非空约束(not null)

4.唯一约束(unique)

5.默认约束(default)

6.零填充约束(zerofill)

7.外键约束(foreign key)——FK

MySQL约束——非空约束(not null)

a.非空约束

方式一:创建表时指定

 
语法:<字段名><数据类型> not null;
create table user04(
	id int,
	name varchar(20) not null,
	address varchar(20) not null
);
 
insert into user04(id,name,address) values(1001,'魏硕','武汉');
insert into user04(id,name,address) values(1001,null,null);
insert into user04(id,name,address) values(1001,'','');

方式二:创建表之后指定

 
语法:alter table 表名 modify 字段 not null;
use mydb3;
 
create table user05(
	id int,
	name varchar(20) not null,
	address varchar(20) not null
);
 
 
alter table user05 modify name varchar(20) not null;
alter table user05 modify address varchar(20) not null;
 
desc user05;    -- 查看表结构
 
insert into user05(id,name,address) values(1001,'魏硕','武汉');
insert into user05(id,name,address) values(1001,null,null);
insert into user05(id,name,address) values(1001,'','');

b.删除非空约束

 
语法:alter table 表名 modify 字段 类型
alter table user05 modify name varchar(20);
 
alter table user05 modify address varchar(20);

MySQL约束——零填充约束(zerofill)

a.零填充约束

 
use mydb3;
 
create table user10 (
	id int zerofill,
	name varchar(20)
);
 
insert into user10 values(123,'张三');
insert into user10 values(3,'李四');

b.删除零填充约束


alter table user10 modify id int;

MySQL约束——默认约束(default)

a.默认约束

方式一：<字段名><数据类型> default <默认值>;

 
create table user08(
	id int,
	name varchar(20),
	address varchar(20) default '北京'   -- 指定默认约束
);
 
insert into user08(id,name,address) values(1001,'魏硕',null);
insert into user08(id,name) values(1001,'魏硕');
delete from user08 where id = 1001;

方式二：alter table 表名 modify 列名类型 default 默认值;

 
create table user09(
	id int,
	name varchar(20),
	address varchar(20)
);
 
alter table user09 modify address varchar(20) default '武汉';
 
insert into user09(id,name,address) values(1001,'魏硕',null);
insert into user09(id,name) values(1001,'魏硕');
delete from user09 where id = 1001;

b.删除默认约束

语法:alter table <表名> modify 列名类型 default null;


alter table user09 modify address varchar(20) default null;

MySQL约束——唯一约束(unique)

a.唯一约束

方式一:<字段名><数据类型> unique

 
create table user06(
	id int,
	name varchar(20),
	phone_number varchar(20) unique
);
 
insert into user06 values(1001,'张三1',138);
insert into user06 values(1002,'张三2',139);
insert into user06 values(1002,'张三2',139); //error
 
insert into user06 values(1001,'张三3',NULL);
insert into user06 values(1002,'张三4',NULL);

方式二:alter table 表名 add constraint 约束名 unique(列);

 
create table user07(
id int,
name varchar(20),
phone_number varchar(20)
);
	
alter table user07 add constraint unique_pn unique(phone_number);
 
insert into user07 values(1001,'张三1',138);
insert into user07 values(1002,'张三2',139);
insert into user07 values(1002,'张三2',139); //error
 
insert into user07 values(1001,'张三3',NULL);
insert into user07 values(1002,'张三4',NULL);

b.删除唯一约束

格式:alter table <表名> drop index <唯一约束名>

 
-- 方式一:
alter table user06 drop index phone_number;
 
-- 方式二:
alter table user07 drop index unique_pn;

MySQL约束——主键约束(primary key)——PK

主键约束：

1.添加单列主键

2.添加多列联合主键

3.删除主键

a.添加单列主键

 
-- (方式一)
-- 语法:
 
create table 表名(
	...
	<字段名><数据类型> primary sky
	...
);
-- 案例
 
create table emp1(
	eid int primary key,
	name varchar(20),
	salary double
);
-- (方式二)
-- 语法:
 
create table 表名(
	...
	[constraint <约束名>] primary sky [字段名]
);
create table emp2(
	eid int,
	name varchar(20),
	salary double,
	constraint pk1 primary key(eid)      -- constraint pk1 可省略
);

b.添加多列联合主键

 
-- 语法:
 
create table 表名(
	...
	primary key(字段1,字段2,...,字段n)
);
-- 案例
 
create table emp3(
	eid int,
	name varchar(20),
	salary double,
	constraint pk1 primary key(eid,name)      -- constraint pk1 可省略
);

c.通过修改表结构添加主键

 
-- 语法:
 
create table 表名(
	...
);
alter table <表名> add primary key (字段列表);
-- 案例
 
create table emp4(
	eid int,
	name varchar(20),
	salary double
);
alter table emp4 add primary key (eid,name);

d.删除主键

单列主键/多列主键都可以删除

alter table <数据表名> drop primary key;

 
-- 单列主键
alter table emp4 drop primary key;
 
-- 多列主键
alter table emp5 drop primary key;

MySQL约束——自增长约束(auto_increment)

a.自增长约束

 
-- 语法:
 
字段名 数据类型 auto_increment
-- 自增长约束
create table user01(
	id int primary key auto_increment,
	name varchar(20)
);

方式一:创建表时指定

 
use mydb3;
 
create table user02(
	id int primary key auto_increment,
	name varchar(20)
)auto_increment = 100;
 
insert into user02 value(NULL,'魏硕');
insert into user02 value(NULL,'嘻嘻');

方式二:创建表之后指定

 
use mydb3;
 
create table user03(
	id int primary key auto_increment,
	name varchar(20)
);
 
alter table user03 auto_increment = 1001;
 
insert into user03 value(NULL,'七七');
insert into user03 value(NULL,'六六');

b.删除自增长约束

 
-- delete和truncate在删除后自增列的变化
			-- delete数据之后自动增长从断点开始
			-- truncate数据之后自动增长从默认起始值开始
delete from user03;
 
truncate user03;

	语法:<字段名><数据类型> not null;
	create table user04(
	id int,
	name varchar(20) not null,
	address varchar(20) not null
	);

	insert into user04(id,name,address) values(1001,'魏硕','武汉');
	insert into user04(id,name,address) values(1001,null,null);
	insert into user04(id,name,address) values(1001,'','');

	语法:alter table 表名 modify 字段 not null;
	use mydb3;

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


	alter table user05 modify name varchar(20) not null;
	alter table user05 modify address varchar(20) not null;

	desc user05; -- 查看表结构

	insert into user05(id,name,address) values(1001,'魏硕','武汉');
	insert into user05(id,name,address) values(1001,null,null);
	insert into user05(id,name,address) values(1001,'','');

	语法:alter table 表名 modify 字段类型
	alter table user05 modify name varchar(20);

	alter table user05 modify address varchar(20);

	use mydb3;

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

	insert into user10 values(123,'张三');
	insert into user10 values(3,'李四');

	create table user08(
	id int,
	name varchar(20),
	address varchar(20) default '北京' -- 指定默认约束
	);

	insert into user08(id,name,address) values(1001,'魏硕',null);
	insert into user08(id,name) values(1001,'魏硕');
	delete from user08 where id = 1001;

	create table user09(
	id int,
	name varchar(20),
	address varchar(20)
	);

	alter table user09 modify address varchar(20) default '武汉';

	insert into user09(id,name,address) values(1001,'魏硕',null);
	insert into user09(id,name) values(1001,'魏硕');
	delete from user09 where id = 1001;

	create table user06(
	id int,
	name varchar(20),
	phone_number varchar(20) unique
	);

	insert into user06 values(1001,'张三1',138);
	insert into user06 values(1002,'张三2',139);
	insert into user06 values(1002,'张三2',139); //error

	insert into user06 values(1001,'张三3',NULL);
	insert into user06 values(1002,'张三4',NULL);

	create table user07(
	id int,
	name varchar(20),
	phone_number varchar(20)
	);

	alter table user07 add constraint unique_pn unique(phone_number);

	insert into user07 values(1001,'张三1',138);
	insert into user07 values(1002,'张三2',139);
	insert into user07 values(1002,'张三2',139); //error

	insert into user07 values(1001,'张三3',NULL);
	insert into user07 values(1002,'张三4',NULL);

	-- 方式一:
	alter table user06 drop index phone_number;

	-- 方式二:
	alter table user07 drop index unique_pn;

	-- (方式一)
	-- 语法:

	create table 表名(
	...
	<字段名><数据类型> primary sky
	...
	);
	-- 案例

	create table emp1(
	eid int primary key,
	name varchar(20),
	salary double
	);
	-- (方式二)
	-- 语法:

	create table 表名(
	...
	[constraint <约束名>] primary sky [字段名]
	);
	create table emp2(
	eid int,
	name varchar(20),
	salary double,
	constraint pk1 primary key(eid) -- constraint pk1 可省略
	);

	-- 语法:

	create table 表名(
	...
	primary key(字段1,字段2,...,字段n)
	);
	-- 案例

	create table emp3(
	eid int,
	name varchar(20),
	salary double,
	constraint pk1 primary key(eid,name) -- constraint pk1 可省略
	);

	-- 语法:

	create table 表名(
	...
	);
	alter table <表名> add primary key (字段列表);
	-- 案例

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

	-- 单列主键
	alter table emp4 drop primary key;

	-- 多列主键
	alter table emp5 drop primary key;

	-- 语法:

	字段名数据类型 auto_increment
	-- 自增长约束
	create table user01(
	id int primary key auto_increment,
	name varchar(20)
	);

	use mydb3;

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

	insert into user02 value(NULL,'魏硕');
	insert into user02 value(NULL,'嘻嘻');

	use mydb3;

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

	alter table user03 auto_increment = 1001;

	insert into user03 value(NULL,'七七');
	insert into user03 value(NULL,'六六');

	-- delete和truncate在删除后自增列的变化
	-- delete数据之后自动增长从断点开始
	-- truncate数据之后自动增长从默认起始值开始
	delete from user03;

	truncate user03;