Day08-JavaWeb开发-MySQL(多表查询内外连接子查询事务索引)Mybatis入门

1. MySQL多表查询

1.1 概述

1.2 内连接


-- ========================================= 内连接 =================================
-- A. 查询员工的姓名, 及所属的部门名称(隐式内连接实现)
select tb_emp.name, tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;

-- 起别名
select * from tb_emp e, tb_dept d where e.dept_id = d.id;

-- B. 查询员工的姓名, 及所属的部门名称(显式内连接实现)
select tb_emp.name, tb_dept.name from tb_emp join tb_dept on tb_emp.dept_id = tb_dept.id;

1.3 外连接

表1称为左表, 表2称为右表

-- ========================================= 外连接 =================================
-- A. 查询员工表 所有 员工的姓名, 及所属的部门名称(左外连接)
select e.name,d.name from tb_emp e left join tb_dept d on e.dept_id = d.id;



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

-- 将上面的右外连接改变为左外连接, 如下:
select e.name,d.name from tb_dept d left join tb_emp e on e.dept_id = d.id;

1.4 子查询

1.4.1 标量子查询

-- ========================================= 子查询 =================================
-- 标量子查询 : 查询的结果是单行单列的结果.
-- A. 查询 "教研部" 的所有员工信息
-- a. 查询 教研部 的部门ID - tb_dept
select id from tb_dept where name = '教研部';  -- 返回2

-- b. 再查询该部门ID下的员工信息 - tb_emp
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');

-- B. 查询在 "方东白" 入职之后的员工信息
-- a. 查询 方东白 的入职时间
select entrydate from tb_emp where name = '方东白'; -- 返回2012-11-01

-- b. 查询在 "方东白" 入职之后的员工信息
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');

A的查询结果:

B的查询结果:

1.4.2 列子查询

-- 列子查询
-- A. 查询 "教研部" 和 "咨询部" 的所有员工信息
-- a. 查询 "教研部" 和 "咨询部" 的部门ID - tb_dept
select id from tb_dept where name = '教研部' or name = '咨询部'; -- (3,2)

-- b. 根据部门ID, 查询该部门下的员工信息 - tb_emp
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');

A的查询结果:

1.4.3 行子查询

-- 行子查询
-- A. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 ;
-- a. 查询 "韦一笑" 的入职日期 及 职位
select entrydate,job from tb_emp where name = '韦一笑'; -- ('2007-01-01',2)

-- b. 查询与其入职日期 及 职位都相同的员工信息  (出现两次子查询)
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 = '2007-01-01' and job = 2;

-- 以下为只出现一次子查询
select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name = '韦一笑');

A的查询结果:

1.4.4 表子查询

-- 表子查询
-- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门名称
-- a. 查询入职日期是 "2006-01-01"
select * from tb_emp where entrydate > '2006-01-01'; -- 得到一张表

-- b. 查询这部分员工信息及其部门名称 - tb_dept
select e.* , d.name from (select * from tb_emp where entrydate > '2006-01-01') e , tb_dept d where e.dept_id = d.id;

A的结果:

1.5 案例

-- 需求:
-- 1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称 .
-- 表: dish , category (注:若在需求当中没有额外说明需要完全包含哪张表的数据, 则考虑用内连接.)
-- SQL:
select d.name, d.price, c.name
from dish d,
     category c
where d.category_id = c.id
  and d.price < 10;

-- 2. 查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来).
-- 表: dish , category (此题选用外连接, 因需查询菜品的所有信息)
-- SQL:
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 dish d,
     category c
where d.category_id = c.id
group by c.name;

-- 4. 查询各个分类下 状态为 '起售' , 并且 该分类下菜品总数量大于等于3 的 分类名称 .
-- 表: dish , category (内连接)
-- SQL:(由每个分类, 按分类分组)
select c.name, count(*)
from dish d,
     category c
where d.category_id = c.id
  and d.status = 1
group by c.name
having count(*) >= 3;

-- 5. 查询出 "商务套餐A" 中包含了哪些菜品 （展示出套餐名称、价格, 包含的菜品名称、价格、份数）.
-- 表: dish , setmeal, setmeal_dish
-- SQL:
select s.name, s.price, d.name, d.price, sd.copies
from setmeal s,
     setmeal_dish sd,
     dish d
where s.id = sd.setmeal_id
  and sd.dish_id = d.id
  and s.name = '商务套餐A';

-- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
-- 表: dish
-- SQL:
-- a. 计算所有菜品的平均价格
select avg(price)
from dish;

-- b. 查询出低于菜品平均价格的菜品信息
select *
from dish
where price < (select avg(price) from dish);