目录
一，多表
1. 多表的概述
实际开发中, 一个项目通常需要很多张表才能完成。
例如, 一个商城项目的数据库, 需要很多张表, 用户表, 分类表, 商品表, 订单表...
2. 单表的缺点
2.1 数据准备
创建一张员工表 emp，字段: eid, ename, age, dep_name, dep_location
eid为主键并且自增。
添加五条测试数据。
create table emp(
id int primary key auto_increment,
ename varchar(10),
age int,
dep_name varchar(10),
dep_location varchar(10)
);
-- 添加数据
insert into emp (ename, age, dep_name, dep_location) values('贾宝玉', 20, '研发部', '广州');
insert into emp (ename, age, dep_name, dep_location) values('林黛玉', 21, '研发部', '广州');
insert into emp (ename, age, dep_name, dep_location) values('薛宝钗', 20, '研发部', '广州');
insert into emp (ename, age, dep_name, dep_location) values('元春', 20, '销售部', '深圳');
insert into emp (ename, age, dep_name, dep_location) values('探春', 22, '销售部', '深圳');
insert into emp (ename, age, dep_name, dep_location) values('甄士隐', 18, '销售部', '深圳');2.2 单表存在的问题
冗余, 同一个字段出现大量重复的数据。
3. 解决方案
设计为两张表
-
多表方式设计 employee 员工表: eid ename age
department 部门表: depid, dep_name, dep_location
-
创建员工和部门表
create table employee(
eid int primary key auto_increment,
ename varchar(10),
age int,
dep_id int
);
create table department(
dep_id int primary key auto_increment,
dep_name varchar(10),
dep_location varchar(10)
);
-- 添加2个部门
insert into department values(default, '研发部','广州'),(default, '销售部', '深圳');
select * from department;
-- 添加5个人员工
insert into employee values(default, '贾宝玉', 20, 1),
(default, '林黛玉', 21, 1),
(default, '薛宝钗', 20, 1),
(default, '元春', 20, 2),
(default, '探春', 22, 2),
(default, '甄士隐', 18, 2);
select * from employee;
3.1 表关系分析
3.1 表关系分析
-
员工表中有一个字段dep_id与部门表中的主键对应字段，这个字段就叫做关系属性
-
拥有关系属性的员工表被称为 从表, 与关系属性对应的主键所在的表叫做 主表
3.2 多表设计上的问题
当我们在员工表的dep_id里面输入了不存在的部门编号, 数据依然可以添加, 显然这是不合理的。
应该保证员工表所添加的dep_id, 必须在部门表dep_id中存在。
解决方案:
使用外键约束, 约束员工表中的dep_id必须在部门表dep_id中存在。
3.3 外键约束
使用外键约束可以让两张表之间产生一个对应关系, 从而保证主从表数据的完整性。
3.3.1 创建外键约束
1，新建表时添加外键约束
constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段); 2，为已创建好的表添加外键约束
alter table 从表名 add constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段名);3.3.2 删除外键约束
语法格式:
alter table 从表名 drop foreign key 外键约束的名称;3.4 外键约束的注意事项
-
从表的外键类型必须和主表的主键类型保持一致
-
添加从表数据时
从表中添加的外键值, 必须在主表的主键中存在
-
删除和变更数据主表数据时
先删除从表中的数据或将外键设置为null, 再删除主表中的数据
-
通过navicat设置外键约的束变更和删除的级联操作
| 选项 | 作用 |
|---|---|
| RESTRICT(默认) | 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 |
| No action | 同RESTRICT, 都是立即检查外键约束 |
| CASCADE | 在主表上update/delete记录时，同步update/delete掉从表匹配的记录 |
| SET NULL | 在主表上update/delete记录时，将子表上匹配记录的列设为null 注意: 子表的外键列不能为not null |
4.外键约束总结
外键约束作用：
1.操作从表
1.向子表中添加的外键值时，添加的外键值必须在关联的主表主键值中存在。
2.修改子表中外键值时，修改后的外键值必须在关联的主表主键值中存在。
2.操作主表
1.删除主表中数据时，必须满足从表中的外键值在主表的主键值中存在
2.变更主键中主键值是，必须满足从表中的外键值在主表的主键值中存在
删除表时：先删除从表，再删除主表
创建表时：先创建主表，再创建从表
外键约束设置：
1.删除时
1.RESTRICT(默认) No action：作用类似，都会检查外键约束
2.setNull：删除主表中数据时，将关联从表中的外键值设置为null
3.cascade：删除主表中数据时，将关联从表中的数据删除
2.变更时
1.RESTRICT(默认) No action：作用类似，都会检查外键约束
2.setNull：变更主表中主键值时，将关联从表中的外键值设置为null
3.cascade：变更主表中主键值时，将关联从表中的外键值变为新的主键值二，多表关系设计
1. 介绍
实际的开发过程中, 一个项目通常需要很多张表才能完成.
| 表与表之间的3种关系 | 举例 |
|---|---|
| 一对多关系(最常见) | 员工表和部门表 学生表和班级表 |
| 多对多关系 | 学生表和课程表 用户表和角色表 |
| 一对一关系(使用较少) | 一对一的关系可以设计成一张表 |
2. 一对多关系(常见)
-
一对多关系(1 : n) 例如: 班级和学生, 部门和员工, 客户和订单, 类别和商品
-
一对多建表原则 在从表(多方)创建一个字段, 该字段作为外键指向主表的主键
3. 多对多关系
-
多对多关系(m : n) 例如: 老师和学生, 学生和课程, 用户和角色。
-
多对多关系建表原则 多对多的关系不能直接处理, 需要创建第三张表, 也称为中间表, 中间表至少两个字段,这两个字段分别作为外键指向各自一方的主键, 实际就是将多对多拆分为两个一对多。
4. 一对一关系
在实际的开发过程中应用不多, 因为一对一的关系可以设计为一张表。
三，多表查询
1. 什么是多表查询
-
DQL: 查询多张表, 获取到需要的数据
-
比如: 我们要查询家电分类下都有哪些商品, 那么我们就需要查询分类与商品两张表
2. 多表查询的分类
4.1 内连接查询(等值连接)
from子句后面直接写多个表名 使用where指定连接条件的 这种连接方式是隐式内连接. 使用where条件过滤无用的数据。
语法格式：
SQL92隐式内连接
select 字段名 from 表1, 表2 where 连接条件;
SQL99显式内连接
select 字段名 from 表1 [inner] join 右表 on 条件;4.2 自连接查询
自连接就是某张表自己和自己关联。
比如员工表，它可以认为是员工表，也可以认为是领导表。
所以我们可以通过关联，查询员工的信息及他的领导的信息。
4.3 非等值连接
非等值连接就是两张表进行关联的时候不是使用关联的id相等进行连接的。
select 字段名，字段名... from 表1，表2 where 字段 between 值1 and 值2;4.4 外连接查询
通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上也可以显示。
4.4.1 左外连接
使用 left outer join , outer 可以省略。
select 字段名 from 表1 left join 表2 on 条件;左外连接的特点:
-
以左表为主, 左表中的数据全部显示
-
右表匹配到数据就显示匹配到的数据
-
右表没有匹配的数据显示为null
4.4.2 右外连接
使用 right outer join, outer可以省略。
语法格式：
select 字段名 from 表1 right join 表2 on 条件;4.4.3 全外连接
注意： MySQL 中不支持 FULL OUTER JOIN 连接,可以使用 union 实现全完连接。
-
UNION: 可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了 DISTINCT。
-
UNION ALL: 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据， 那么返回的结果集就会包含重复的数据了。
语法结构：
(SELECT 列名 FROM 表名 LEFT OUTER JOIN 表名 ON 连接条件)
UNION
(SELECT 列名 FROM 表名 RIGHT OUTER JOIN 表名 ON 连接条件)4.5 内连接和外连接总结
-
内连接: inner join: 只获取两张表中 交集部分的数据。
-
左外连接: left join: 以左表为基准, 查询左表的所有数据, 以及与右表有交集的部分 。
-
右外连接: right join: 以右表为基准, 查询右表的所有的数据, 以及与左表有交集的部分。
四，表的约束大总结
表的约束：为表中的字段添加约束，保证表中数据的完整性，正确性。
分类:
1.主键约束
特点：非空，唯一（值不允许为null且不能重复），一个表中只能有一个主键约束
关键词：primary key
使用建议：表中通常都会创建一个id的字段，即使没有实际意义也会创建，将id添加主键约束。
主键自增：primary key auto_increment
create table 表名(
id int primary key,
...
)
alter table 表名 add primary key(字段);
2.非空约束
特点：非空（值不允许为null），一个表中可以使用多次，和其他约束配合使用
关键词：not null
create table 表名(
id int primary key,
name varchar(10) not null,
...
)
3.唯一约束
特点：唯一（值不能重复），一个表中可以使用多次，和其他约束配合使用
关键词：unique
create table 表名(
id int primary key,
email varchar(10) unique,
...
)
4.默认值约束
特点：添加时没有指定值或指定为default，使用默认值
添加时执行了值使用指定值。
关键词：default 值
create table 表名(
id int primary key,
sex varchar(10) default '男',
...
)
5.检查约束（MySQL8新增）
特点：检查添加的值是够符合要求
关键词：check
create table 表名(
id int primary key,
age int check(age>1 and and<100),
...
)
6.外键约束
特点：使用在多表中（从表中的外键值必须在主表的主键值中存在）
1.操作从表
1.向从表中添加数据时，外键值必须在主表的主键值中存在
2.变更从表中的外键值是，变更的新外键值必须在主表的主键值中存在
2.操作主表
1.删除主表中数据时，从表中有关联的外键值，设置为null | 直接删除关联数据
2.变更主表中主键值时，从表中有关联的外键值，设置为null | 直接删除关联数据 | 将从表中关联外键值同步为新主键值
3.先创建主表，再创建从表
4.先删除从表，再删除主表
关键词：constrains foreign key references
create table 表名(
id int primary key,
...,
constrains 外键名 foreign key(字段) references主表(主键字段)
)
alter table 表名 add constrains 外键名 foreign key(字段) references主表(主键字段);
外键约束设置：
1.删除时
1.RESTRICT(默认) No action：作用类似，都会检查外键约束
2.setNull：删除主表中数据时，将关联从表中的外键值设置为null
3.cascade：删除主表中数据时，将关联从表中的数据删除
2.变更时
1.RESTRICT(默认) No action：作用类似，都会检查外键约束
2.setNull：变更主表中主键值时，将关联从表中的外键值设置为null
3.cascade：变更主表中主键值时，将关联从表中的外键值变为新的主键值五，多表连接方式大总结
连接方式：
1.内连接：通过条件匹配两张表中的数据，能匹配就显示，不能匹配不显示。
SQL92：select 字段,... from 表1,表2 where 条件;
SQL99：elect 字段,... from 表1 [inner] join 表2 on 条件;
实现：
1.不同的表进行内连接
2.同一张表(自连接)进行内连接
3.判断条件为等值判断，等值内连接
SQL92：select 字段,... from 表1,表2 where 字段1=字段2;
SQL99：select 字段,... from 表1 join 表2 on 字段1=字段2;
4.判断条件为非等值判断，非等值内连接
SQL92：select 字段,... from 表1,表2 where 字段1 between 字段2 and 字段3;
SQL99：select 字段,... from 表1 join 表2 on 字段1 between 字段2 and 字段3;
2.外连接：
1.左外连接：以左表为主，左表中的数据全部显示，右表没有匹配的数据以null填充。
select 字段,... from 表1 left [outter] join 表2 on 条件;
实现：
1.不同的表进行左外连接
2.同一张表(自连接)进行左外连接
3.判断条件为等值判断，等值左外连接
SQL99：select 字段,... from 表1 left join 表2 on 字段1=字段2;
4.判断条件为非等值判断，非等值左外连接
SQL99：select 字段,... from 表1 left join 表2 on 字段1 between 字段2 and 字段3;
2.右外连接：以右表为主，右表中的数据全部显示，左表没有匹配的数据以null填充。
select 字段,... from 表1 right [outter] join 表2 on 条件;
实现：
1.不同的表进行右外连接
2.同一张表(自连接)进行右外连接
3.判断条件为等值判断，等值右外连接
SQL99：select 字段,... from 表1 right join 表2 on 字段1=字段2;
4.判断条件为非等值判断，非等值右外连接
SQL99：select 字段,... from 表1 right join 表2 on 字段1 between 字段2 and 字段3;
3.MySQL不支持全外连接
左右表中数据都展示，没有匹配填充null
左外连接
union
右外连接
将左外连接的结果和右外连接的结果进行合并，合并时去重。
7554
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
