MySql多表操作_查询价格低于 10元的菜品的名称、价格及其菜品的分类名称 .-CSDN博客

内连接语法：
隐式内连接：select 字段列表 from 表1 , 表2 where 条件 ... ;
显式内连接：select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ...
例题：
-- A. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)
select tb_emp.name as '员工姓名',tb_dept.name as '所属部门'
from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;

select e.name, d.name from tb_emp e , tb_dept d where e.dept_id = d.id;

-- B. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)

select tb_emp.name as '员工姓名',tb_dept.name as '所属部门'
from tb_emp join tb_dept on tb_emp.dept_id = tb_dept.id;

注意A中的第二种起别名的写法，需要把这个别名写在from的列表后面才会生效

外连接：

左外连接：查询左表所有数据(包括两张表交集部分数据)

左外连接：select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;

右外连接：查询右表所有数据(包括两张表交集部分数据)

右外连接：select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ...

例题：
-- =============================== 外连接 ============================
-- A. 查询员工表所有员工的姓名, 和对应的部门名称 (左外连接)
select tb_emp.name,tb_dept.name from tb_emp left join tb_dept on tb_emp.dept_id = tb_dept.id;

-- B. 查询部门表所有部门的名称, 和对应的员工名称 (右外连接)
select tb_emp.name,tb_dept.name from tb_emp right join tb_dept on tb_emp.dept_id = tb_dept.id;

左外连接不仅会显示左表和右表的交集部分，还有左表的全部元素
右外连接同样。

2：多表设计：

外键就是在数据库层面用来连接两个表。
外键分为物理外键和逻辑外键。
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名)

这是添加物理外键的语法，也可以根据idea提供的可视化工具来创建外键。

多表查询一对多：员工和部门。（外键）
一对一：身份证号和个人id（外键）
多对多：学生和课程（实现第三张中间表，中间表至少包含两个外键，分别关联两方主键）

3：子查询：

介绍：SQL语句中嵌套select语句，称为嵌套查询，又称子查询（差不多就是在查询里面套查询）

底下的四种查询种都涉及到了一个返回什么什么值：
这个怎么解释呢：就是你把这个查询拆成两步，第一步的返回值是什么就是什么
可以放回一个值（字段）
也可以是列，行，还可以是一个表。

标量子查询:返回一个值

-- A. 查询 "教研部" 的所有员工信息
-- a:查询教研部的部门id
-- b:根据部门id再进行查找信息
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
-- B. 查询在 "方东白" 入职之后的员工信息
-- a:找到这个人的入职时间
-- b：再根据入职时间进行查找

select * from tb_emp where tb_emp.entrydate>=(select entrydate from tb_emp where name = '方东白');

列子查询:返回的结果可以是多行

-- A. 查询 "教研部" 和 "咨询部" 的所有员工信息
-- a:分别查询教研部和咨询部的员工id
-- b：再根据id查询结果
select * from tb_emp where dept_id in(select id from tb_dept where name = '教研部' or name = '咨询部');
-- 行子查询
-- A. 查询与 "韦一笑" 的入职日期及职位都相同的员工信息 ;
select * from tb_emp where entrydate = (select entrydate from tb_emp where name = '韦一笑') and
job = (select job from tb_emp where name = '韦一笑');

select * from tb_emp where (entrydate,job) =
(select tb_emp.entrydate,tb_emp.dept_id from tb_emp where name = '韦一笑');

表子查询：

-- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
-- a:查询入职日期是 "2006-01-01" 之后的员工信息。
-- b：再查询这部分员工的信息和部门信息。
select * from tb_emp where entrydate > '2006-01-01';
select e.*,d.name from (select * from tb_emp where entrydate > '2006-01-01') e,tb_dept d where e.dept_id = d.id;

4：多表查询案例练习：

1：确定需要用到几张表（看好表的主键）
2：确定外连接还是内连接（默认内连接）
只有当题目要求出现的字段是一张表特有的时候，才考虑外连接，而且一般是左连接。

注意点：
1：这一点我一开始还没想到：就是两张表有外键（不管是逻辑还是物理），你想把两张表联系起来，需要where d.category_id = c.id类似这种语句
2：这一点是关于在多表查询种的分类聚合的查询，看清楚分类的条件（求价格的平均值的话就不需要分类）
3：第三点还是关于这个分类聚合查询的having，你可以将聚合函数放在这个having后面。

-- 需求:

-- 1. 查询价格低于 10元的菜品的名称、价格及其菜品的分类名称 .

-- 表： dish，catefory
-- SQL:判断是外连接还是内连接
select d.name, d.price, c.name
from category c,
dish d
where d.category_id = c.id
and d.price < 10;

-- 2. 查询所有价格在 10元(含)到50元(含)之间且状态为'起售'的菜品, 展示出菜品的名称、价格及其菜品的分类名称
-- (即使菜品没有分类 , 也需要将菜品查询出来).

-- 表：dish category
select d.name, d.price, c.name
from dish d
left join category c on d.category_id = c.id
where d.price between 10 and 50
and d.status = 1;

-- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .

-- 表：dish category
-- SQL:内连接
select c.name,max(d.price)
from category c,
dish d
where c.id = d.category_id
group by c.name;

-- 4. 查询各个分类下菜品状态为 '起售' , 并且该分类下菜品总数量大于等于3 的分类名称 .

-- 表：dish，category
-- 内连接
select c.name, count(*)
from dish d,
category c
where d.category_id = c.id
and c.status = 1
group by c.name
having count(*) >= 3;

-- 5. 查询出 "商务套餐A" 中包含了哪些菜品（展示出套餐名称、价格, 包含的菜品名称、价格、份数）.

-- 表：setmeadl dish setmeal_dish
select s.name, s.price, d.name, d.price, sd.copies
from dish d,
setmeal_dish sd,
setmeal s
where d.id = sd.dish_id and s.id = sd.setmeal_id and s.name = '商务套餐A';

-- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).

-- 表：dish
-- a：得到菜品平均价格 ,你求所有菜品的平均价格就不需要分组条件
select avg(d.price) from dish d ;
-- b：得到菜品信息
select d.name,d.price from dish d where d.price <(select avg(d.price) from dish d);