数据库的约束语句和表之间的关系

约束

约束就是一种限制，规定不能做某些事情

数据库的约束，是对数据的安全性，完整性的保证

mysql中的约束

unique

唯一性约束，表示这个字段不能出现重复的值，用于唯一标识一条记录

比如身份证号码，学号等

not null

非空约束，表示这个字段的值不能为空

比如账号，密码

null

一些数据类型默认就是可以为空的

default

默认值，用于给某一个字段设置默认值

普通约束测试

#完整的建表语句
create table table_name(字段名称 字段类型[(宽度)约束]) charest utf8;

#学生表具备 姓名 性别 学号
create table student(
	#非空
    name char(20) not null,
    #默认值
    gender enum('g','b') default 'b',
    #唯一
    id int unique
)
#测试
insert into student values(null,null,null);#错误原因是 name不能为空
insert into student values('jack',null,null);#可以插入，null也是一个特殊的值，并且id的唯一约束，也可以为null
insert into student(name,id) values('jack',null);#可以插入，当没有给gender指定参数时，将使用默认值
alter table student modify id int unique not null;#为已经存在的字段添加约束

primary key

主键约束，从约束角度来看就等同于，非空+唯一

主键与普通的约束的区别：

create table person(
	id char(19) primary key,
    name char(20)
);
insert into person values('1','rose');#ok
insert into person values('1','jack');#主键唯一，不能为'1'，报错
insert into person values('2','jack');#ok
insert into person values(null,'tom');#主键不能为空，报错

#从约束角度来看就等同于，非空+唯一
create table person2(
	id char(19),
    name char(20)
);

#在innodb存储引擎中，主键用于组织数据(树形结构)
#也就是说对于主键对于innodb引擎来说是必须要的，没有不行！
#如果没有手动指定主键，mysql会自动查找一个具备非空且唯一的字段作为主键
#如果也没有这样的字段，mysql会创建一个隐藏字段，作为主键
#首先明确主键是一种索引，unique也是，索引的作用是加速查询
#如果我们在查询语句中没有使用索引字段，mysql将无法为我们加速查询，意味着如果没有主键，将无法加速查询

总的来说，主键具有约束的作用，还能加快查询速度，所以在创建表的时候应该创建一个索引字段；

如果本来业务中就存在一个非空且唯一的字段，那就把它设置为主键，如果没有那就自己添加一个字段专门作为主键，通常将主键设置为int类型，是为了方便保证其唯一。

案例：

create table PC（
id int primary key,
pp char(20),
model char(10),
price float
);

mysql> insert into PC values(1,"IBM","1214SB",40000);
mysql> insert into PC values(2,"DELL","1200DSB",4000);
mysql> select *from PC;
mysql> select *from PC where id = 1;
mysql> select *from PC where pp = "DELL";

为主键设置自动增长

当我们创建了主键字段时,插入数据必须保证主键是唯一的不能重复,这就需要我们自己管理主键值,这是很麻烦的,所以mysql有一个自动增长的属性,可以添加在整形字段上,每次插入数据时,都可以自动的插入值,并且每次加1不会冲突;

create table teacher(
	id int primary key auto_increment,
    name char(10)
);
insert into teacher values(null,'jack');#对于自动增长得字段可以给null，也会自动生成值
insert into teacher(name) values('jack');#也可以跳过这个字段

create table teacher(
	id char unique auto_increment,
    name char(10)
);

自动增长可以用在具备索引，并且是数字类型的字段上，但是通常与主键一起使用

表之间的关系

foreign key

外键约束，用于指向另一个表的主键字段

#创建表的时候添加外键
create table teacher(
	id int primary key auto_increment,
    name char(20),
    gender char(1),
    dept_id int,
    foreign key(dept_id) regerences dept(id)
);
解释：
foreign key(dept_id) references dept(id)
dept_id表示当前表的外键字段
dept 表示要关联哪个表
dept（id） id表示关联的的普通表的id字段

主表与从表
先有主表 再有从表
先有dept 再有teacher

foreign key 带来的约束作用：

在从表中插入一条记录，关联了一个主表中不存在的id, 会导致插入失败

必须保证部门id(外键的值)必须是在主表中存在的
插入数据的顺序

现插入主表记录，再插入从表记录
从表更新外键时也必须保证外键的值在主表中是存在的

删除主记录前，要保证从表中没有外键关联被删除的id

delete from dept where id=1;#报错
#必须先删除从表，再删除主表
delete from teacher where dept_id=1;
delete from dept where id=1;

更新主表记录的主键时，要保证从表中没有外键关联被删除的id
必须先创建主表
删除表必须先删除从表

强调：foreign key就是用来保证两张表之间的关联是正确的

练习：班级表和学员表

主表是班级

从表是学员外键加给学员

create table class(
	id int primary key auto_increment,
    name char(20)
);

create table student(
	id int primary key auto_increment,
    name char(20),
    gender char(1),
    c_id int,
    foreign key(c_id) references class(id)
);

级联操作

当我们需要删除主表的信息时，必须先删除从表中关联的数据，很麻烦

级联操作指的就是，当操作主表时，自动的操作从表

两种级联操作

级联的删除

当删除主表时自动删除从表中的相关数据
级联更新

当主表的主键更新时自动更新的更新关联的从表数据

案例：

drop table if exists class;
# 如果这表存在 才执行删除  可以避免报错  if exists
# if not exists  如果不存在才执行
create table class(
        id int primary key auto_increment,
        name char(20)
	);
insert into class values(null,"py9");
insert into class values(null,"py10");

#创建表的时候指定级联操作 
drop table if exists student;
create table student(
        id int primary key auto_increment,
        name char(20),
        gender char(1),
        c_id int,
    	foreign key(c_id) references class(id)
         on update cascade
   	 	on delete cascade
	);
# 级联操作可以单独使用 也可以一起使用   空格隔开即可 
insert into student values(null,"jack","m",1);
insert into student values(null,"rose","m",1);
insert into student values(null,"tom","m",2);

外键的使用

什么时候使用外键：

表之间存在关联关系：

多对一（一对多）

老师和部门的关系 
老师的角度看
	一个老师应该对应有一个部门 
	一个老师可以对应对多个部门?  不行 一个老师只能属于一个部门 (要看具体业务要求)!
	多个老师可以对应一个部门 
	多对一
部门的角度看
	一个部门可以对应多个老师
	一个部门可以对应一个老师
	多个部门可以对应一个老师? 不行 
	一对多 
如何处理一对多(多对一)?
	在老师表中存储 部门id
	即多的一方存储 一的一方的id

处理方式

在一的一方即teacher表中保存相应的部门（多的一方）的编号；

案例：

#部门
create table dept(
	id int primary key auto_increment,
    name char(20),
    job char(50),
    manager char(19),
);
#老师表
create table teacher(
	id int primary key auto_increment,
    name char(20),
    gender char(1),
    dept_id int,
    foreign key(t_id) references teacher(id),
);

多对多

如何确定多对多关系

例如：老师表和学生表

老师表角度：一个老师可以对应多个学生一对多

学生表角度：一个学生可以对应多个老师一对多

如果双方都是一对多的，那么两者都是多对多关系

处理方式

建立一个中间表，用于存储关系，至少具备两个字段分别指向老师和学生的主键，两个字段都是外键，如下：

create table t_s_r(
	id int primary key auto_increment,
    t_id int,
    s_id int,
    foreign key(t_id) reference teacher(id),
    foreign key(s_id) regerence student(id),
    unique key(t_id,s_id)
);

上表中id是可选的，问题是如何保证没有重复干关系？

方式1：
给两个字段设置为唯一+非空
#假设表已经存在了
alter table t_s_r add unique key(t_id,s_id);
#创建表时指定，多字段联合唯一
create table t_s_r2(t_id int,s_id int,unique key(t_id,s_id));
方式2：
#推荐 将中间的关系表 两个id 作为联合主键 同时具备了 唯一且非空约束
create table t_s_r3(t_id int ,s_id int,primary key(t_id,s_id));

处理多对多关系：
1. 创建两个主表 如学生和老师
2. 创建关系表 包含两个字段 分别设置外键 指向对应的表
3. 将两个字段 作为联合外键

案例

create table student(id int primary key auto_increment,name char(19));
create table teacher(id int primary key auto_increment,name char(10));
create table t_s_r(
	t_id int ,
    s_id int ,
    foreign key(t_id) references teacher(id),
    foreign key(s_id) references student(id),
    primary key(t_id,s_id)
);
#先插入 学生表或者老师表都可以 但是关系表一定是最后添加的
insert into teacher values(null,'bgon'),(null,'nike')

insert into student values(null,'老王'),(null,'老李')

#老王被bgon教过
insert into t_s_r values(1,1)
#nike教过老李
insert into t_s_r values(2,2)
#nike教过老王
insert into t_s_r values(2,1)


已知老师名为egon 请找出他教过的学生
1. 通过名字获取 bgon的id
2. 拿着id取关系表中拿到一堆学生的id
3. 通过学生的id取出学生的信息
select id from teacher where name='bgon';
select s_id from t_s_r where t_id=1;
selsct * from student where id=1;

#子查询方式 把一条语句的结果作为另一条语句的条件
select * from student where id=(select s_id from t_s_r where t_id=(select id from teacher where name='bgon'));


已知学生名为老李 请查询出 哪些老师教过他
1.通过名字获取老李id
select id from student where name='老李'；
2.拿着id去关系表，找到老师id
select t_id from t_s_r where t_id=2;
3.通过老师的id取出老师的信息
select * from teacher where id=x;

#子查询方式
select name form teacher where id=(select t_id form t_s_r where s_id=(select id from student where name='老李'))

一对一关系

如一个身份证对应一个公民

粘在两边都是一对一的关系

处理方式：

确定先后顺序，将先存在的数据作为主表，后存在的作为从表

使两个表的id保持一一对应

方法一：从表的id 即是主表又是外键

方法二：从表的id设置为外键，并保证唯一

案例：

#人员表
create table person(
	id int primary key auto_increment,
    name char(10),
    age int
);
#详情表
create table person_info(
	id int primary key,
    height float,
    weight float,
    foreign key(id) references person(id)
);
#在这样的关系中 必须先插入主表即person 拿到一个id 在添加详情表的数据
#将一条完整数据拆分到不同表中，可以提高查询效率，上述方法称之为垂直分表