多表设计
一对多
在多的一方添加外键约束,关联另外一方主键
一对一
任意一方添加外键约束,关联另外一方主键
多对多
建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
id | stu_id | course_id |
1 | 1 | 1 |
2 | 1 | 2 |
3 | 1 | 3 |
4 | 2 | 1 |
5 | 2 | 4 |
案例:苍穹外卖
分类表:菜品表(category_id) = 1 : n
分类表:套餐表(category_id) = 1 : n
菜品表:套餐表 = m:n
中间表记录setmeal_id, dish_id以及份数:copies
多表查询
从多张表中查询数据
全连接:笛卡尔积
A集合和B集合全部的组合情况
内连接
A集合和B集合交集
隐式内连接:select 字段列表 from 表1,表2 where condition...;
显式内连接:select 字段列表 from 表1 join 表2 on condition...;
起别名之后要用别名,不能用原名
左外连接
select 字段列表 from 表1 left join 表2 on condition...;
右外连接
select 字段列表 from 表1 right join 表2 on condition...;
相当于
select 字段列表 from 表2 left join 表1 on condition...;
子查询
标量子查询:返回标量
select * from tb_emp where dept_id = (select id from tb_dept where name = "教研部" )
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = "房东白")
列子查询:返回一列
select * from tb_emp where dept_id in (select id from tb_dept where name = "教研部" or name = "教务处";
行子查询:返回一行
select * from tb_emp where (entrydate, job) = (select entrydate, job from tb_emp where name = "weiyixiao");
表子查询:返回多行多列
-- Inquire employees' information and department name
select * from tb_emp where entrydate > '2007-01-01';
select e.*, d.name from (select * from tb_emp where entrydate > '2007-01-01') e, tb_dept d where e.dept_id = d.id;
案例
-- 1.查询价格低于10元的菜品的名称、价格及其菜品的分类名称
select dish.name, dish.price, category.name from dish, category where dish.category_id = category.id and dish.price < 10-- 2.查询所有价格在10元(含)到50元(含)之间且状态为'起售'的菜品,展示出菜品的分类名称
select dish.name, category.name from dish left join category on dish.category_id = category.id where (dish.price between 10 and 50) and dish.status = 0;-- 3,查询每个分类下最贵的菜品,展示出分类的名称、最贵的菜品的价格
select category.name, max(dish.price) from dish, category where dish.category_id = category.id group by category.id-- 4,查询各个分类下菜品状态为‘起售’,并且该分类下菜品总数大于等于3的分类名称
select category.name where dish.category_id = category.id and dish.status = 1 group by category.name having count(*) >= 3-- 5,查询出“商务套餐a”中包含了哪些菜品―(展示出套餐名称、价格,包含的菜品名称、价格、份数)
table: setmeal, dish, setmeal_dish
select s.name, s.price, d.name, d.price, sd.copies from setmeal s, dish d, setmeal_dish sd where sd.setmeal_id = s.id and sd.dish_id = d.id and s.name = '商务套餐a'-- 6.查询出低于菜品平均价格的菜品信息(展示出菜品名称、菜品价格)
select name, price from dish where price < (select avg(price) from dish)
事务
一组操作的集合,要么同时成功,要么同时失败
开启事务:start transaction;
结束事务:commit;
回滚事务:rollback;
原子性、一致性、隔离性、持久性ACID
索引
create index idx_tb_attr on table(attr);
show index from table;
drop index idx_tb_attr on table;
默认:主键索引、唯一索引、
没有索引:全表扫描
有索引:构建B+二叉搜索树,左侧字树比根节点小,右侧子树比根节点大
二叉搜索树有可能会产生偏向一边的情况
二叉搜索树和红黑树会产生深度较深的问题
- 一个结点可以有多个孩子
- 所有数据都在叶子结点中保存,非叶子结点仅用于索引数据
- 叶子结点按从小到大排列,是双向链表