目录
一、约束
1.什么是约束 constraint
**1.约束（constraint）：表中数据的限制条件 **
2.约束的作用：为了保证表中的记录完整、有效
2.mysql中的约束
1.unique
1.唯一性约束(unique)：unique约束的字段，具有唯一性，不可重复，但可以为null
2.使用场景：例如身份证号码,学号等
2.not null
**1.非空约束(not null)：用not null约束的字段不能为null值，必须给定具体的数据 **
2.应用场景：例如,账户名,密码等
3.null
有些数据类型默认可以为空，则可以使用这个约束
4.default
默认值,用于给某一个字段设置默认值，如果不给该字段输入值，则使用默认值，可以为null
1.普通约束测试:
#完整的建表语句
create table table_name(字段名称 字段类型(宽度) 约束) charset 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); # ok，null是一个特殊的值，可以赋值给默认值约束，并且id的唯一约束，也可以为null
insert into student(name,id) values("jack",null); #ok，当没有给gender指定参数时,将使用默认值
alter table student modify id int unique not null; # 为已经存在的字段添加约束
5. primary key
1.主键约束：从约束角度来看就等同于非空+唯一（表设计时一定要有主键）
2.主键与普通的约束的区别
create table person(
id char(19) primary key,
name char(20)
);
insert into person values("1","rose");# ok
insert into person values("1","jack");# ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'主键冲突;
insert into person values("2","jack"); # ok
insert into person values(null,"tom"); # Column 'id' cannot be null #主键不能为空
#从约束角度来看就等同于,非空+唯一
create table person2(
id char(19),
name char(20)
);
# 特点：
# 1.在innodb存储引擎中，主键用于组织数据 (树形结构)；
# 2.主键对于innodb引擎来说是必须要的,没有不行；
# 3.如果没有手动指定主键，mysql会自动查找一个具备非空且唯一的字段作为主键；
# 4.如果也没有这样的字段,mysql会创建一个隐藏字段作为主键；
# 5.首先明确主键是一种索引，unique也是；
# 6.如果我们在查询语句中没有使用索引字段，mysql将无法为我们的加速查询，也就是说，索引的作用是加速查询，我们使用它可以提高查询速度
# 补充：
# 在关系型数据库中，索引是一种对表中的值进行排序的一种结构
# 如何使用主键:
# 1.主键具备约束的作用,还能加快我们的查询速度,所以今后在创建表的时候都应该创建索引字段；
# 2.如果本来的业务中就存在非空且唯一的字段，那就把它设为主键，如果没有就自己添加一个字段专门作为主键，通常我们会将主键设置为类int类型,主要是为了方便保证其唯一
# 案例:
# 建表
create table PC(
id int primary key,
pp char(20),
model char(10),
price float
);
# 加值
insert into PC values(1,"IBM","1214SB",40000);
insert into PC values(2,"DELL","1200DSB",4000);
# 查询
select *from PC;
select *from PC where id = 1;
select *from PC where pp = "DELL";
1.为主键设置自动增长
1.当我们创建了主键字段时,插入数据必须保证主键是唯一的不能重复,这就需要我们自己管理主键值,每次赋值都要知道该赋值哪一个数据，比较麻烦,所以mysql有一个自动增长的属性,可以添加在整形字段上,每次插入数据时,都可以自动的插入值,并且每次加1不会冲突
2.auto_increment：自增约束，可以对数字类型且具备索引（unique或primary key)的字段使用，通常与主键一起使用
3.作用是按序递增，对于自动增长的字段可以赋值null，该字段也会自动生成值
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 teacher3(
id char unique auto_increment ,
name char(10)
);
'''
ERROR 1063 (42000): Incorrect column specifier for column 'id'
'''
2.将两个字段设为主键
create table user(
id int not null unique,
username char(10) not null,
password char(20) not null,
primary key(username, password)
);
6.foreign key
1.外键约束（foreign key）：用于指向另一个表的主键字段，也即是用来保证两张表之间的关联关系是正确的
2.格式：先在从表中设置一个数字字段，通过 foreign key(字段名) references 主表名(主表主键字段名)的书写格式进行外键的设置
3.什么时候使用外键: 表之间存在关联关系
# 创建表的时候添加外键
create table teacher(
id int primary key auto_increment,
name char(20),
gender char(1),
dept_id int,
foreign key(dept_id) references dept(id)
);
解释:
foreign key(dept_id) references dept(id)
dept_id 表示当前表的外键字段
dept 表示要关联哪个表
dept(id) id表示关联的dept表的id字段
二、表之间的关系
1.外键的使用
1.首先要确定表之间的关系
2.表与表之间的关系分三种情况：一对一、一对多、多对多
2.涉及表的三种关系
1. 一对一关系
1.特点：站在两边看都是一对一的关系
2.处理方式：
-
1.确定先后顺序
-
2.将先存在的数据作为主表
-
3.后存在的作为从表
-
4.使两个表的id保持一一对应
- 方法1: 从表的id既是主键又是外键
- 方法2:从表的id设置为外键，并保证唯一
3.案例:
# 人员表
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)
);
# 在这样的关系中，必须先插入主表的主键id，再添加详情表的数据
# 将一条完整数拆分到不同表中,可以提高查询的效率,上述方式称之为垂直分表!
# 补充：水平分表是保持相同的字段，将整个表水平切分，分成两个表
2.一对多多对一(常用)
1.站在部门的角度想：一个部门可以有多个职工
2.站在老师的角度想：一个职工只能属于一个部门
# 详细分析老师和部门的关系 ：
# 1.老师的角度看：
一个老师应该对应有一个部门
一个老师可以对应对多个部门? 不行 一个老师只能属于一个部门 (要看具体业务要求)!
多个老师可以对应一个部门
多对一
# 2.部门的角度看
一个部门可以对应多个老师
一个部门可以对应一个老师
多个部门可以对应一个老师? 不行
一对多
# 3.如何处理一对多(多对一)?
在老师表中存储部门id
即多的一方存储单的一方的id
多的作为从表，一的作为主表
1.处理方式
在一的一方即teacher表中保存相应部门(多的一方)的编号;
案例:
#部门:
create table dept(
id int primary key auto_increment,
name char(20),
job char(50),
manager char(10)
);
#老师表:
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),
);
2.主表与从表
1.先有主表 再有从表
2.比如先有单位，后有职工，因此主表一般是单位信息表，从表一般是职工信息表，但也不是绝对的，根据业务需求来
3.我们使用foreign key设置外键后，在插入记录时，必须保证外键的值是主表中存在的主键id
4.插入数据的顺序：先插入主表记录，在插入从表记录
5.当然，从表更新外键时也必须保证外键的值在主表中是存在的
6.更新主表记录的主键时，要保证从表中没有外键关联主表中被更改的主键id
7.必须先删除从表，再删除主表，删除主表记录前，要保证从表中没有外键关联主表中被删除的主键id
creat table dept(id int primary key auto_increment, name char);
create table worker(
id int primary key auto_increment,
name char,
dept_id int,
foreign key(dept_id) references dept(id)
);
drop table dept; # ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
drop table worker;
drop table dept; # ok
1.练习：
1.班级表和学员表
2.主表是班级，从表是学员
create table class(
id int primary key auto_increment,
name char(10),
);
create table student(
id int primary key auto_increment,
name char(10),
c_id int,
foreign key(c_id) references class(id)
);
2.级联操作
1.cascade
1.作用：当我们需要删除部门(主表)信息时，必须先删除从表中关联的数据，很麻烦 ，级联操作就是解决这个问题，它可以在你操作主表时，自动的随之操作从表
2.级联操作应用场景: 用在外键关联中,可以级联的删除或更新从表记录
**3.格式：
# 级联更新/删除可以一起写，也可以分开写，按需求来
foreign key(从表的外键字段) references 主表(主表主键字段)
on update cascade # 级联更新
on delete cascade # 级联删除
2.两种级联操作
1.级联的删除
**当删除主表时自动删除从表中相关数据**
2.级联更新
**当主表的主键更新时自动的更新关联的从表数据**
1.案例:以班级和学员为例:
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);
3.多对多
1.如何确定多对多关系
# 老师表和学生表（套路一样）
# 1.老师表角度: 一个老师可以教多个学生（一对多）
# 2.学生表角度: 一个学生可以由多个老师教（一对多）
# 3.如果双方都是一对多的关系，那么，两者是多对多关系
1.处理方式
建立一个中间表,用于存储关系,至少具备两个字段分别指向老师和学生的主键,两个字段都是外键，如下:
create table t_s_r(
id int primary key auto_increment,
t_id int,
s_id int,
foreign key(t_id) references teacher(id),
foreign key(s_id) references 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(10));
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);
已知老师名称为bgon 请找出他教过那些学生
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;
select * 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 s_id = 2;
3.通过老师的id取出老师的信息
select name from teacher where id = x;
# 子查询方式:
select name from teacher where id = (
select t_id from t_s_r where s_id = (
select id from student where name = "老李"
)
);
# 子查询方式（查看多条记录）：
# 1.可以在条件处加any
select name from teacher where id = any(
select t_id from t_s_r where s_id = any(
select id from student where name = "老李"
)
);
# 2.可以将条件处的“=”换成“in”
select name from teacher where id in (
select t_id from t_s_r where s_id in (
select id from student where name = "老李"
)
);
2.子查询方式
1.将先查询到的结果作为之后查询的条件
2.可以查看多条记录：
- 1.将查询条件语句中的"="换成"in"
- 2.在查询条件语句中的"="后面加上"any"
- 3.当想查询字段相同的多条记录时，可以用"or"将多个条件连在一起
# 子查询方式（查询单条记录）:
select name from teacher where id = (
select t_id from t_s_r where s_id = (
select id from student where name = "老李"
)
);
# 子查询方式（查看多条记录）：
# 1.可以在条件处加any
select name from teacher where id = any(
select t_id from t_s_r where s_id = any(
select id from student where name = "老李"
)
);
# 2.可以将条件处的“=”换成“in”
select name from teacher where id in (
select t_id from t_s_r where s_id in (
select id from student where name = "老李"
)
);
# 3.条件是同字段时，用"or"相连
select name from teacher where id in (
select t_id from t_s_r where s_id in (
select id from student where name = '老李' or name = '老王'
)
)