mysql中的约束条件_MySQL约束条件例子详解

1、no null(非空)

create table t1(

id int,

name char(4) not null

)

insert into t1 value(1,null);

insert into t1 value(1,'json');

2、default (默认)

create table t2(

id int,

name char(4),

sex enum('male','female','others') default 'male'

);

insert into t2(id,name) value(1,'json');

insert into t2 value(2,'alex','female');

3、unique(唯一)

(1)单个唯一：

create table t3(

id int unique,

name char(4)

);

insert into t3 value(1,'json');

insert into t3 value(1,'hel');

insert into t3 value(2,'hel');

(2)联合唯一(不能出现两个合并起来一样的，类似IP-port)

create table t4(

id int,

ip char(16),

port int,

unique(ip,port)

);

insert into t4 value(1,'127.0.0.1',8080);

insert into t4 value(2,'127.0.0.2',8080);

insert into t4 value(3,'127.0.0.1',8081);

insert into t4 value(4,'127.0.0.1',8080);

4、primary key(主键)

(1)、从约束效果上看，primary key等价于 not null and unique，即非空且唯一。

create table t5(id int primary key);

insert into t5 value(1);

insert into t5 value(null);

insert into t5 value(1);

desc t5;

(2)它除了有约束效果外，同时也是存储引擎Innodb(MySQL5.5版本及之后默认的存储引擎)组织数据的依据。即Innodb存储引擎在创建表的时候，必须要有primary key.

primary key 的作用类似于书的目录，可以提高查询效率

注意：

1、 一张表中有且只有一个主键，如果你没有设置主键，那么将会在表中从上往下寻找非空且唯一的字段，并将该字段升级为主键。

create table t6(

id int,

name char(4),

iphone int not null unique,

sid int not null unique

);

desc t6;

2、如果表中既没有主键，也没有非空且唯一的字段，那么Innodb将会采用内部提供的隐藏主键，但是，该主键无法提高查询速度，也看不到。

3、在表中通常都是以id这种类型做主键

(1)单个主键：

create table t7(

id int primary key,

name char(4)

);

(2) 联合主键：(多个字段联合作为主键，本质还是一个主键)

create table t8(

id int,

phone char(11),

name char(4),

primary key(id,phone)

);

desc t8;

5、auto_increment(自增)

注意：

1、auto_increment只能用在primary key主键上

create table t10(id int auto_increment);

create table t9(

id int primary key auto_increment,

name char(4)

);

insert into t9(name) value('json'),('hello'),('world');

2、清除表的数据的时候，如果使用delete from t9，则不会将自增器的计数归零，需要使用truncate t9，才可以实现清除表的数据同时也将自增器计数归零

delete from t9 #只负责清除表数据

truncate t9 #负责清除表数据同时将计数器归零。

6、外键(foreign key)

外键是用来建立表与表之间的联系的

判断表之间的关系的时候，要站在两张表上来思考问题，比如员工表跟部门表：

员工表：一个员工是否对应多个部门

部门表：一个部门是否对应多个员工

得出结论：是一对多的关系

(1)一对多关系：

外键字段要建在多的一方

在创建表的时候，一定要先建立被关联表

#员工表跟部门表，之间的关系就是一对多关系

create table staff(

id int primary key auto_increment,

name varchar(32),

sex enum('female','male')

dep_id int,

foreign key(dep_id) references demp(id)

on update cascade #更新级联

on delete cascade #删除级联

);

#部门表

create table demp(

id int primary key auto_increment,

name varchar(32)

);

注意：一对多关系中，只能修改被关联的表(部门表)，不能修改员工表中对应的部门。

(2)多对多关系：

多对多关系的两张表，不能像一对多那样直接加外键，因为如果在两个表都加上一个外键，那么，这两张表都是关联表都无法创建成功。

解决方法：再单独创建一个表，来存储两张表之间的关系。

#学生表

create table student(

id int primary key auto_increment,

name varchar(16),

sex enum('male','female') default 'male'

);

insert into student(name,sex) value('alex','male'),('json','female'),('python','male')

#教师表

create table teacher(

id int primary key auto_increment,

name varchar(16),

subject varchar(16)

);

insert into teacher(name,subject) value('猪哥‘,'数学'),('浩哥','计算机'),('大佬','英语')

#关系表

create table s_t(

id int primary key auto_increment,

t_id int,

s_id int,

foreign key(s_id) references studnt(id)

on update cascade

on delete cascade,

foreign key(t_id) references teacher(id)

on update cascade

on delete cascade

);

insert into s_t(t_id,s_id) value(1,2),(1,3),(2,1)(2,2),(3,1),(3,3);

(3) 一对一关系：

外键建在任何一方都可以，这里推荐建立在查询频率比较高的一方。

create table studentDetail(

id int primary key auto_increment,

addr varchar(16),

iphone varchar(16),

xueli varchar(16)

);

create table student1(

id int primary key auto_increment,

name varchar(16),

sex enum('male','fimeal') default 'male',

sD_id int unique,

foreign key(sD_id) references studentDetail(id)

on update cascade

on delete cascade

);

insert into studentDetail(add,iphone,xueli) value('广东','121','本科'),('湖南','111','专科'),('北京','222','博士');

insert into student1(name,sex,sD_id) value('alex','male',1),('val','fimeal',2),('hello','male',3);