Mysql Day03

多表设计

一对多

在多的一方添加外键约束，关联另外一方主键

一对一

任意一方添加外键约束，关联另外一方主键

多对多

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

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)