mysql多表查询之数据库数据 - 掘金 (juejin.cn)
内连接
-- 语法：
-- 隐式内连接；select 字段列表 from 表1,表2 where 条件...;
-- 显示内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
-- 练习
-- 1.查询员工的姓名，及所属的部门名称(隐式内连接);
select tb_emp.name as '员工姓名', tb_dept.name as '部门名称' from tb_emp, tb_dept where tb_emp.dept_id = tb_dept.id;
-- 2.查询员工的姓名，及所属的部门名称(显示内连接);
select tb_emp.name as '员工姓名', tb_dept.name as '部门名称' from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;
外连接
-- 语法
-- 左外连接：select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;
-- 右外连接：select 字段列表 from 表1 right [ounter] join 表2 on 连接条件...;
-- 练习
-- 1.查询员工表所有员工的姓名，和对应的部门名称（左外连接）
select tb_emp.name as '员工姓名', tb_dept.name as '部门名称' from tb_emp left outer join tb_dept on tb_emp.dept_id = tb_dept.id;
-- 2.查询部门表所有部门的名称，和对应的员工名称（右外连接）
select tb_emp.name as '员工姓名', tb_dept.name as '部门名称' from tb_emp right outer join tb_dept on tb_emp.dept_id = tb_dept.id;
子查询
概述
- 介绍：SQL语句中嵌套select语句，称为嵌套查询，有称为子查询。
- 形式：select * from t1 column1 = (select column1 from t2 ...);
- 子查询外部的语句可以是insert, update, delect, select 的任何一个，最常见的是select。
标量子查询
-- 介绍：SQL语句中嵌套select语句，称为嵌套查询，有称为子查询。
-- 语法：select * from t1 column1 = (select column1 from t2 ...);
-- 子查询外部的语句可以是insert, update, delect, select 的任何一个，最常见的是select。
-- 标量子查询：子查询返回的结果为单个值
-- 子查询返回的结果是单个值（数字，字符串，日期等），最简单的形式
-- 常用的操作符：=, <>, >, >=, <, <=
-- 练习
-- 1.查询“教研部”的所有员工信息
select * from tb_emp where id = (select id from tb_dept where name = '教研部');
-- 2.查询“张三丰”入职之后的员工信息
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '张三丰');
列子查询
-- 列子查询：子查询返回的结果为一列
-- 子查询返回的结果是一列（可以多列）
-- 常用的操作符：in, not in 等
-- 练习
-- 查询“教研部”和“咨询部”的所有员工信息
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' || name = '咨询部');
行子查询
-- 行子查询：子查询返回的结果为一行
-- 子查询返回的结果是一行（可以是多行）
-- 常用的操作符：=，<>，in, not in
-- 练习
-- 1.查询”韦一笑“的入职日期及职位都相同的员工信息
-- 写法一
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 entrydate,job from tb_emp where name = '韦一笑');
表子查询
-- 表子查询：子查询返回的结果为多行多列
-- 子查询返回的结果是多行多列，常作为临时表
-- 常用的操作符：in
-- 练习 查询入职日期是“2010-01-01”之后的员工信息，及其部门名称
select tbEmp.*, tbDept.name as 'dept_name' from (select * from tb_emp where entrydate > '2010-01-01') as tbEmp, tb_dept as tbDept where tbEmp.dept_id = tbDept.id;
案例练习
mysql多表查询案例练习之数据库数据 - 掘金 (juejin.cn)
-- 分类表：category; 菜品表：dish; 套餐表：setmeal; 套餐菜品中间表 setmeal_dish
-- 1.查询价格低于10元的菜品名称、价格、及其菜品的分类名称
select dish.name as '菜品名称', dish.price as '菜品价格', category.name as '菜品分类' from dish, category where dish.category_id = category.id and dish.price < 10;
-- 2.查询所有价格再10元（含）到50元（含）之间且状态为‘起售’的菜品，展示除菜品的名称（即使菜品没有分类，也需要将菜品查询出来）
select dish.name as '菜品名称', dish.price as '菜品价格', category.name as '菜品分类' from dish left join category on dish.category_id = category.id where dish.price between 10 and 50 and dish.`status` = 1;
-- 3.查询每个分类下最贵的菜品，展示除分类的名称、最贵的菜品价格
select category.name as '菜品分类', max(dish.price) as '菜品价格' from dish, category where dish.category_id = category.id group by category.name;
-- 4.查询各个分类下菜品状态为‘起售’，并且该分类下菜品总数量大于等于3的分类名称
select category.name as '分类名称', count(*) as '数量' from dish, category where dish.category_id = category.id and dish.`status` = 1 group by category.name having count(*) >= 3;
-- 5.查询“商务套餐A”中包含了哪些菜品（展示除套餐名称，价格，包含的菜品名称、价格、份数）
select setmeal.name as '套餐名称', setmeal.price as '套餐价格', dish.name as '菜品名称' from dish, setmeal, setmeal_dish where dish.id = setmeal_dish.dish_id and setmeal.id = setmeal_dish.setmeal_id and setmeal.name = '商务套餐A'
-- 6.查询除低于菜品平均价格的菜品信息（展示除菜品名称、菜品价格）。
select * from dish where price < (select avg(price) from dish);
