目录
1. 多表设计
一对多
多对多
1.1 一对多
例如 部门信息表(tb_dept) 和 员工信息表(tb_emp), 部门信息表存放着部门名称(name)和id, 员工需要添加所属部门(dept_id)的信息。
可以分析到, 一个部门下会有多个员工, 而员工只属于某一个部门(暂不考虑身兼数职的情况)。因此两表构成 一(部门表)对多(员工表) 的关系, 为了将两表关联上, 需要在数据库表中多的一方,添加字段,来关联一的一方的主键。
例如, 在 tb_emp 中添加字段 dept_id 表示员工的归属部门, 同时称部门表为父表而员工表为子表。
1.1.1 外键约束
部门数据可以直接删除,然而还有部分员工归属于该部门下,此时就出现了数据的不完整、不一致问题。
在数据库层面操作添加物理外键约束
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);
这样在删除某一部门时, 会首先检查员工信息表, 若发现该部门下还有员工, 则这一删除操作不会执行.
然而, 其缺点在于:
- 影响增、删、改的效率(需要检查外键关系)。
- 仅用于单节点数据库,不适用与分布式、集群场景。
- 容易引发数据库的死锁问题,消耗性能。
因此, 通常在业务层逻辑中,解决外键关联, 也即通过java代码实现.
1.2 多对多
例如选课关系, 一名学生可以选修多门课程, 一门课程也会有多名学生来听. 因此这构成多对多关系.
此时, 为了将两表关联起来, 需要新建一个 学生选课表, 一条记录表示某一学生学习了某一门课程。
1.3 设计案例
苍穹外卖后台管理系统
本次只分析 分类管理(category), 菜品管理(dish), 套餐管理(setmeal) 三个表.
- category
包括菜品分类、套餐分类 - dish
菜品名称, 2-20, 汉字、字母大小写、阿拉伯数字, 不可重复
菜品分类, 必填, 下拉框
价格, 必填, 输入框, 数字,可以有小数点后2位小数 - setmeal
套餐名称, 套餐分类, 套餐价格, 套餐菜品
关系分析
一个菜品只对应一个菜品分类, 一个菜品分类下可以有多种菜品;
一个套餐只对应一个套餐分类, 一个套餐分类下可以有多种套餐;
一个套餐可包含多种菜品, 一个菜品可位于多种套餐下.
因此:
category: dish = 1: n;
category: setmeal = 1: n;
setmeal: dish = m: n;
setmeal_dish: record the relation between setmeal and dish, note that copies is recorded too;
2. 多表查询
多表查询:指从多张表中查询数据
笛卡尔积:笛卡尔乘积是指在数学中,两个集合(A集合和B集合)的所有组合情况
分类
- 连接查询
- 内连接:相当于查询A、B交集部分数据
- 外连接
- 左外连接:查询左表所有数据(包括两张表交集部分数据)
- 右外连接:查询右表所有数据(包括两张表交集部分数据)
- 子查询
2.1 连接
- 查询员工的姓名及所属的部门名称(隐式内连接实现)
select 字段列表 from 表1,表2 where 条件 ...;
- 查询员工表所有员工的姓名,和对应的部门名称 (左外连接)
select 字段列表 from 表1 left [outer] join 表2 on 连接条件 ...;
- 查询部门表 所有部门的名名称,和对应的员工名称 (右外连接)
2.2 子查询
-
SQL语句中嵌套select语句,称为嵌套查询,又称子查询。
-
形式:
select * from tl where column1 = ( select column1 from t2 ... );
-
子查询外部的语句可以是insert / update / delete / select 的任何一个,最常见的是 select。
2.2.1 分类
标量子查询:子查询返回的结果为单个值(数字、字符串、日期等)
列子查询:子查询返回的结果为一列
行子查询:子查询返回的结果为一行
表子查询:子查询返回的结果为多行多列
2.2.2 案例
-
查询 教研部 的所有员工信息 (标量子查询)
-
查询 教研部 和 咨询部 的所有员工信息 (列子查询)
-
查询与 韦一笑 的入职日期 及 职位都相同的员工信息 (行子查询)
-
查询入职日期是 2006-01-01 之后的员工信息及其部门名称 (表子查询)
3. 附录
-- 1
select tb emp.name,tb dept.name from th emp,tb dept where tb emp.dept id = th dept.id;
-- 2
select e.name, d.name from tb emp e left join th dept d on e.dept id = d.id;
--4
select * from tb_emp where dept_id =(select id from tb_dept where name = '教研部';
--5
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
--6
select * from th_emp where (entrydate,job)=(select entrydate, job from tb_emp where name = '韦一笑');
--7
select e.* , d.name from (select * from tb_emp where entrydate > '2006-01-01') e , tb_dept d where e.dept id = d.id;
MySQL 索引
主键索引 primary key
唯一索引 unique
数据结构–B+树
每一个节点,可以存储多个key (有n个key,就有n个指针)。所有的数据都存储在叶子节点,非叶子节点仅用于索引数据。叶子节点形成了一颗双向链表,便于数据的排序及区间范围查询