MySQL学习--多表查询总结

MySQL学习–多表查询总结

概述

• 从多张表中查询出指定的数据；
• “笛卡尔积”：笛卡尔乘积是指在数学中，两个集合：A集合 和 B集合 的所有组合情况。
  • 注意：在多表查询时，需要消除无效的笛卡尔积；

-- 单表查询
select * from emp;

-- 多表查询 
select * from emp, dept where dept_id = dept.id;# 笛卡尔积，使用where消除无效的数据

分类

连接查询

内连接

• 相当于查询A,B两集合交集部分的数据；

隐式内连接

语法

select 字段列表 from 表1, 表2 where 条件...;

案例

-- 查询每一个员工的姓名、关联的部门名称 （隐式内连接实现）
select emp.name 员工姓名, dept.name 部门 from emp, dept where dept_id = dept.id;
-- 给表起别名
select e.name 员工姓名, d.name 部门 from emp e, dept d where e.dept_id = d.id;

显式内连接

语法

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

案例

-- 查询每一个员工的姓名、关联的部门名称 （显式内连接实现）
select emp.name 员工姓名, dept.name 部门 from emp inner join dept on emp.dept_id = dept.id;
select e.name 员工姓名, d.name 部门 from emp e join dept d on e.dept_id = d.id;

显式和隐式内连接的区别

• 两种内连接的作用相同；
• 但是，通常建议使用显式内连接语法，因为它更易于阅读和理解，尤其是在复杂查询中；

外连接

• 左表是指outer join 左边的表；
• 右表是指outer join 右边的表；

左外连接

• 查询左表所有的数据，以及两张表交集部分的数据；

语法

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

案例

-- 查询emp中的所有数据，和对应的部门信息（左外连接实现）
select * from emp left outer join dept on emp.dept_id = dept.id;

注意：如果emp表中，有一条记录的dept_id字段值为null，则：若使用内连接查询，则不显示该记录；而使用左外连接可以显式；

右外连接

• 查询右表所有的数据，以及两张表交集部分的数据；

语法

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

案例

-- 查询dept中的所有数据，和对应的员工信息（右外连接实现）
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

-- 改成左外连接实现
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

自连接

• 查询当前表与自身的连接查询，自连接必须使用表的别名；

语法

select 字段列表 from 表1 别名1 join 表2 别名2 on 连接条件...;

案例

-- 查询员工 及其 所属领导的名字（自连接-内连接查询）
select e1.*, e2.name 所属领导 from emp e1 join emp e2 on e1.managerid = e2.id;


-- 查询所有员工 及其 领导的名字，如果没有领导，也要查询出来；（自连接-外连接查询）
select e1.*, e2.name 所属领导 from emp e1 left join emp e2 on e1.managerid = e2.id;

-- 其实就是，自己和自己连接，再查询；

联合查询（union/union all）

• 把多次查询的结果合并起来，形成一个新的查询结果集；
• 多次查询的字段数量和类型需要相同；
• union和union all区别：去掉all，可以去除查询出来的重复记录；

语法

select 字段列表 from 表1 ...
union [all]
select 字段列表 from 表2 ...;

案例

-- 把薪资低于8000 的员工， 和 年龄小于 25 的员工都查询出来
select * from emp where salary < 80000
union all
select * from emp where age < 25;

-- 去掉all，可以去除重复记录
select * from emp where salary < 80000
union
select * from emp where age < 25;

子查询

概念

• SQL语句中嵌套select语句，又称嵌套查询。

select * from 表1 where 字段名1 = (select 字段名1 from 表2);

-- 子查询外部的语句可以insert/update/delete/select的任意一个；

分类

• 根据子查询结果的不同，分为：

  • 标量子查询（子查询结果为单个值）

  • 列子查询（子查询结果为一列）

  • 行子查询（子查询结果为一行）

  • 表子查询（子查询结果为多行多列）

• 根据子查询出现的位置，分为：

  • where之后
  • from之后
  • select之后

标量子查询

• 子查询结果为单个值

• 案例

 -- 标量子查询(子查询结果为单个值)
 
 select * from emp;
 
 -- 1.查询“销售部”的所有员工信息
SELECT * FROM emp e WHERE e.dept_id = (
	SELECT d.id FROM dept d WHERE d.name = "销售部"
)
 
 -- 2.查询在“赵敏” 之后入职的员工
 SELECT * FROM emp WHERE entrydate > (
	SELECT entrydate from emp where name ="赵敏"
)

列子查询

• 子查询结果为一列(或多列)
• 常用的操作符

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

• 案例

-- 列子查询（子查询结果为一列或多列）

-- 1.查询“销售部”和“市场部”的所有员工信息   => IN
SELECT * FROM emp WHERE dept_id in (
	SELECT id FROM dept WHERE name in ("销售部","市场部")
)

-- 2.查询比财务部所有人工资都高的员工信息   => ALL 
SELECT * FROM emp WHERE salary > ALL(
	SELECT salary FROM emp WHERE dept_id = (
		SELECT id FROM dept WHERE name = "财务部"
	) 
)

SELECT * FROM emp WHERE salary > ( # max()
	SELECT max(salary) FROM emp WHERE dept_id = (
		SELECT id FROM dept WHERE name = "财务部"
	) 
)

-- 3.查询比研发部其中任意一人工资高的员工信息   => ANY/SOME
SELECT * FROM emp WHERE salary > ANY(
	SELECT salary FROM emp WHERE dept_id = (
		SELECT id FROM dept WHERE name = "研发部"
	)
)

SELECT * FROM emp WHERE salary > SOME(
	SELECT salary FROM emp WHERE dept_id = (
		SELECT id FROM dept WHERE name = "研发部"
	)
)

行子查询

• 子查询结果为一行(或多行)
• 常用的操作符

操作符	描述
=	等于
<>	不等于
IN	在指定集合范围内，多选一
NOT IN	不在指定集合范围内

• 案例

-- 行子查询（返回结果为一行或多行）

-- 1. 查询与“张无忌”的薪资及直属领导相同的员工信息；
SELECT * FROM emp WHERE salary = (
	SELECT salary FROM emp WHERE name = "张无忌"
)
AND 
managerid = (
	SELECT managerid FROM emp WHERE name = "张无忌"
)


SELECT * FROM emp WHERE (salary, managerid) = (
	SELECT salary, managerid FROM emp WHERE name = "张无忌"
)

表子查询

• 子查询结果为多行多列

• 常用操作符：IN

• 案例

-- 表子查询

-- 1.查询与“小张”，“小陈”的薪资和岗位相同的员工信息
SELECT * FROM emp WHERE (salary, job) IN (
	SELECT salary, job FROM emp WHERE name IN ("小张", "小陈")
);


-- 2.查询入职日期是“2002-01-01”之后的员工及其部门信息

-- 隐式内连接 + 列子查询
SELECT e.*, d.name 部门 FROM emp e, dept d WHERE e.dept_id = d.id AND entrydate IN (
	SELECT entrydate FROM emp WHERE entrydate > "2002-01-01"
)

-- 左外连接 + 表子查询
SELECT e.*, d.name 部门 FROM (SELECT * FROM emp WHERE entrydate > "2002-01-01") e LEFT JOIN dept d ON e.dept_id = d.id

多表查询案例

• 案例描述

-- 多表查询总结

-- 1.查询员工的姓名、年龄、职位、部门信息。
SELECT emp.name, age, job, dept_id, d.name 部门 FROM emp, dept d WHERE emp.dept_id = d.id

-- 2.查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
SELECT emp.name, age, job, dept_id, d.name 部门 FROM emp, dept d WHERE emp.dept_id = d.id AND age < 30

-- 3.查询拥有员工的部门ID、部门名称。
SELECT id, name FROM dept WHERE (
	SELECT COUNT(id) FROM emp
) > 0

-- 4.查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来。
SELECT e.*,d.name 部门 FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.age > 40

-- 5.查询所有员工的工资等级。
create table salgrade(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,300000);

SELECT e.name, e.salary, s.grade 薪资等级 FROM emp e LEFT JOIN salgrade s ON e.salary >= s.losal AND e.salary < s.hisal

-- 6.查询“研发部”所有员工的信息及工资等级。
SELECT e.*, s.grade 薪资等级 FROM emp e LEFT JOIN salgrade s ON e.salary >= s.losal AND e.salary < s.hisal WHERE e.dept_id = (
	SELECT id FROM dept WHERE name = "研发部"
)

-- 7.查询“研发部”员工的平均工资。
SELECT AVG(e.salary) 平均薪资 FROM emp e JOIN dept d on e.dept_id = d.id WHERE d.name = "研发部" 

-- 8.查询工资比“赵敏"高的员工信息。
SELECT * FROM emp WHERE salary > (
	SELECT salary FROM emp WHERE name = "赵敏"
)

-- 9.查询比平均薪资高的员工信息。
SELECT * FROM emp WHERE salary > (
	SELECT AVG(e.salary) FROM emp e
)

-- 10.查询低于本部门平均工资的员工信息。
SELECT e.* FROM emp e
	LEFT JOIN ( SELECT dept_id, AVG( salary ) avg_salary FROM emp GROUP BY dept_id ) avg_s ON e.salary > avg_s.avg_salary 
WHERE
	e.dept_id = avg_s.dept_id

-- 11.查询所有的部门信息，并统计部门的员工人数。
SELECT d.name, COUNT(e.id) 部门人数 FROM emp e JOIN dept d ON e.dept_id = d.id GROUP BY e.dept_id

-- 12.查询所有学生的选课情况,展示出学生名称,学号，课程名称。
-- (1)3张表连接
SELECT s.name, s.no, c.name 课程名称 FROM student s, course c, student_course s_c WHERE s.id = s_c.stu_id AND s_c.course_id = c.id 

-- (2) 表子查询 + 显示内连接
SELECT s.name, s.no, s_c_c.name 课程名称 FROM student s JOIN (SELECT s_c.stu_id, s_c.course_id, c.name FROM student_course s_c, course c WHERE s_c.course_id = c.id) s_c_c ON s.id = s_c_c.stu_id