约束条件

约束条件，限制约束字段的条件

# 之前接触的 
null、not null  # 空、不能为空
zerofill	# 数字0填充显示
unsigned	# 无符号

default默认值

default默认值，插数据的时候可以使用默认值

注意：插数据要匹配插入字段的个数，默认不写插入字段时，values括号内要全部写上。

create table t1(
    id int,
    name char(16),
    sex enum('male', 'female') default 'male'
);

insert into t1(id, name) values(1, 'jack');
insert into t1 values(2,'tom', 'male');
insert into t1 values(3, 'mack', 'female');

unique唯一键

单字段唯一：这个字段内不数据不允许重复，如name

create table t2(
	id int,
    name char(10) unique
);
insert into t2 values(1, 'jack');
insert into t2 values(2, 'jack')	
# 报错  Duplicate entry 'jack' for key 'name'

联合唯一：联合字段内数据不允许重复，如 ip + port

create table t3(
	id int,
    ip char(16),
    port int,
    unique(ip, port)
);
insert into t3 values(1,'127.0.0.1',8080);
insert into t3 values(2,'127.0.0.1',8081);
insert into t3 values(3,'127.0.0.2',8080);
insert into t3 values(4,'127.0.0.1',8080);  
# 报错 Duplicate entry '127.0.0.1-8080' for key 'ip'

primary key主键

primary key 的约束效果等同于 not null + unique，即非空且唯一。

主键处理约束之外，还是Innodb存储引擎组织数据的依据。Innodb创建表时必须要有主键，并且通过主键可以提升查询效率。

一张表中有且只有一个主键。
如果没有设置主键，则从上往下第一个非空且唯一的字段将自动升级为主键。
如果表中没有设置逐渐和非空且唯一的字段，则Innodb默认提供一个隐藏的主键，但是这个默认隐藏的主键无法提供快速查询的服务。
一般，表中的主键应该设置为id字段
主键可以为一个字段，也可以是联合主键（多个字段联合起来作为表的主键，本质还是一个主键）。

create table t4(
	id int primary key
);
insert into t4 values(1);
insert into t4 values(1); # 报错 Duplicate entry '1' for key 'PRIMARY'
insert into t4 values(null); # 报错 Column 'id' cannot be null

create table t5(
	id int not null unique,
    name char(10) not null unique
);

# 只要设置了主键，则设置的字段为主键，及时它在not null + unique字段后面
create table t6(
	id int not null unique,
    name char(10) primary key
);

# 主键有一个主键
create table t7(
	id int primary key,
    name char(10) primary key
);
# 报错 Multiple primary key defined

# 联合主键,本质还是一个主键
create table t7(
	id int,
    ip char(16),
    port int,
    primary key(ip, port)
);

auto_increment自增

自增通常用在主键上，不给普通字段使用

补充：

delete from t1 删除表后，主键的自增不是停止。
truncate t1 清空表数据并且重置主键的自增。
步长：auto_increment_increment
起始偏移量： auto_increment_offset
可以设置全局的也可以设置会话级别的。

create table t8(
	id int primary key auto_increment,
    name char(10),
    cid int auto_increment
);
# 报错Incorrect table definition; there can be only one auto column and it must be defined as a key

create table t8(
	id int primary key auto_increment,
    name char(10)
);
insert into t8 values('jack') 
# 报错Column count doesn't match value count at row 1
insert into t8(name) values('jack');
insert into t8(name) values('tom'); # 实现id自增加

foreign key外键

外键是建立表与表之间关系的的约束条件。

# 表与表之间的关系
<1> 一对多 （即，多对一）
<2> 多对多 
<3> 一对一

一对多

如员工表和部分表
一对多时，外键设置在'多'的一方（员工表）
创建表时，先创建被关联表，即先创建'一'的一方（部门表）
插入数据时，先插入被关联的表，即先创建'一'的一方（部门表）

create table dep(
	id int primary key auto_increment,
    dep_name char(10)
);

create table emp(
	id int primary key auto_increment,
    emp_name char(10),
    dep_id int,
    foreign key(dep_id) references dep(id)
);

insert into dep(dep_name) values('IT'),('TEC'),('ACC');
insert into emp(emp_name, dep_id) values('jack', 1)
insert into emp(emp_name, dep_id) values('tom', 2)
insert into emp(emp_name, dep_id) values('lili', 3)

注意：

建立关联后，表中不能随意插入被关联表中，被关联字段不存在的数据。
建立关联后，被关联表的被关联资管也不能随意修改和删除

只有使用 级联更新和级联删除 才能做到同步更新和同步删除。

# 创建建表时设置，设置后修改被关联表的关联字段后，另一个表就会同步更新和删除

create table dep(
	id int primary key auto_increment,
    dep_name char(10)
);

create table emp(
	id int primary key auto_increment,
    emp_name char(10),
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade	# 同步更新
    on delete cascade	# 同步删除
);

多对多

如图书表和作者表；一本书可以有多个作者，一个作者可以写多本书。
多对多时，单独建第三个表，存储两张表数据之间的关系，即再建一个关系表。
手动在第三张关系表中插入两张表数据之间的关系。

create table book(
	id int primary key auto_increment,
    book_name char(20)
);
# 关系表
create table book2author(
	id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(book_id) references book(id)
    on update cascade  # 同步更新
    on delete cascade,  # 同步删除
    foreign key(author_id) references author(id)
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);

一对一

如作者信息表和作者详细信息表；两张表时一一对应的。
此时，外键可以在任意一张表，但是推荐将外键放在查询频率较高的表中。字段必须唯一 unique。

create table author_detail(
	id int primary key auto_increment,
    nick_name char(10),
    email varchar(20)
);

create table author(
    id int primary key auto_increment,
    name char(10),
    author_detail_id int unique,
    foreign key(author_detail_id) references author_detail(id)
    on update cascade
    on delete cascade
);

补充

外键，通过'硬关系'，关联表之间的关系
其实也可以，不使用外键，手动在多张表中同步更新，建立'软关系'

posted @ 2020-05-05 16:07 the3times 阅读(641) 评论(0) 收藏举报

刷新页面返回顶部