目录
-- 1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称 .
-- 2. 查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品, 展示出菜品的名称、价格 及其 菜品的分类名称-- (即使菜品没有分类 , 也需要将菜品查询出来).
-- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .
-- 4. 查询各个分类下 菜品状态为 '起售' , 并且 该分类下菜品总数量大于等于3 的 分类名称 .
-- 5. 查询出 "商务套餐A" 中包含了哪些菜品 (展示出套餐名称、价格, 包含的菜品名称、价格、份数).
-- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
多表查询:
多表查询: 指从多张表中查询数据
笛卡尔积: 笛卡尔乘积是指在数学中,两个集合(A集合 和 B集合)的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)
1:连接查询:
内连接:相当于查询A、B交集部分数据
内连接语法:
隐式内连接: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);