Mysql - 多表查询

目录

• 概述
• 等值连接(内连接)
• 外部连接
  • 左外连结
  • 右外连结
• 自连结
• 联合查询 union
• 子查询
  • 标量子查询
  • 列子查询
  • 行子查询
  • 表子查询

概述

• 多表查询概述： 指从多张表中查询数据
• 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A 和 集合B 的所有组合情况

检索出的数目是第一个表中的行数乘以第二个表中的行数

对于数据库来说：

多表查询需要消除无效的笛卡尔乘积，如何实现？ 就需要建立连接

案例数据：

员工表：

部门表：

等值连接(内连接)

内连接查询的是两张表的交集部分：

• 隐士内连接：

select * from employees e, departments d where e.deptid = d.deptid;

• 显示内连接 - inner 可省略：

 select * from employees e [inner] JOIN departments d on e.deptid = d.deptid;

以id作为关联，列出两表关联查出的数据：

需要注意的是 内连接中， 员工 赵云的数据查询不到，因为没有deptid

外部连接

左外连结

相当于查询左表数据 以及 右表与左表的交集数据

# OUTER 可省略 
select * from employees e LEFT [OUTER] JOIN departments d on e.deptid = d.deptid;

13行没有关联到数据：

左外连接3张表查询案例：查询所有学生的选课情况，展示出学生名称，学号，课程名称

关系图：

# 写法一
SELECT * from student s
left join student_course sc on s.id = sc.studentid
left join course c on c.id = sc.courseid;

结果：

# 写法二,比较困惑的一种写法：
select * from student_course sc
left join student s on s.id = sc.studentid
left join course c on c.id = sc.courseid

结果：

通过对比发现：
1.第二种写法无法查询出没有选课的学生，因为是以 student_course 为最左边的表
2.在进行多表关联的时候，最终那一张表的数据的顺序是表出现的顺序

如第一种写法：

右外连结

查询右表所有数据 以及 与 左表的交集部分

select * from employees e RIGHT [OUTER] JOIN departments d on e.deptid = d.deptid;

最后一行没有关联到数据，因为没有员工属于1004 部门

自连结

自联结查询，可以是内连接查询，也可以是外连接查询

案例1：

# 自连接，查询员工及其所属领导的名字
# 方法一： a 表 作为 员工表 ，b 作为领导表
SELECT b.empname '领导' , a.empname '员工' from employees a inner join employees b on a.leader =b.empid;

# 方法二： a 作为 领导表， b 作为员工表
SELECT a.empname '领导',b.empname '员工'  from employees a inner join employees b on a.empid =b.leader;

# 左右表的连接条件是不一样的，两种方式

结果：

# 自连接，查询员工及其所属领导的名字，如果员工没有领导也要查询出来
SELECT  a.empname '员工' , b.empname '领导' from employees a left join employees b on a.leader = b.empid;

结果：

需要注意是，思考清楚连接的条件，一般都是左边的外键连接右表的主键

连接的条件一定要写对,如果写为:

SELECT  a.empname , b.empname from employees a left join employees b on a.empid = b.leader;

会有错误的结果：

案例2：

地址表：

需求1：查询河南省所有的市

select * from areas a1 inner JOIN areas a2 on a1.aid = a2.pid where a1.atitle  = '河南省';

结果：

需求2：

# a1 作为省表，a2作为市表, a3 作为区表
select a1.atitle '省', a2.atitle '市', a3.atitle '区' from areas a1
LEFT JOIN areas a2 on a1.aid = a2.pid
LEFT JOIN areas a3 on a2.aid = a3.pid
where a1.atitle = "河南省";

结果：

联合查询 union

对于 union 查询，就是把多次查询的结果合并起来， 形成一个新的查询结果集

语法：

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
• union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重

需求1：查询出薪资低于5000的员工 和 年两大于 50 的员工

select * from emp where salary < 5000
union all
select * from emp where age > 50;

结果：需要注意的是 鹿杖客出现了两次

如果不需要重复的数据可以使用 union：

select * from emp where salary < 5000
union
select * from emp where age > 50;

结果：

子查询

• 概念： SQL语句中嵌套SELECT 语句，称为嵌套查询，又称子查询

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2)

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个

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

• 标量子查询(子查询的结果为单个值，一行一列)
• 列子查询(子查询结果为一列)
• 行子查询(子查询结果为一行)
• 表字查询(子查询结果为多行多列)

标量子查询

子查询返回的结果是单个值(数字，字符串，日期等)，最简单的形式，这种子查询称为 标量子查询

常用的操作符: = <> > >= < <=

# 查看销售部的所有员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');

结果：

列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询

常用的操作符： IN、 NOT IN、ANY、SOME、ALL

案例1: 查询比财务部所有人工资都高的员工信息

#1.查询财务部的部门id
select id from dept where name = '财务部';

#2.财务部每个人的工资
select salary from emp where dept_id = (select id from dept where name = '财务部');

#3.
select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '财务部'));

案例2：查询比研发部其中任意一人工资高的员工信息

# 写法1
select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name = '研发部'));

# 写法2
select * from emp where salary > some(select salary from emp where dept_id = (select id from dept where name = '研发部'));

行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询

常用的操作符： =、<>、IN、NOT IN

案例： 查询与 "张无忌" 的薪资及直属领导相同的员工信息

#1.查询张无忌的薪资及直属领导
select salary,managerid from emp where name = "张无忌"

select * from emp where (salary,managerid) = (select salary,managerid from emp where name = "张无忌");

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询

案例：查询与“鹿杖客”，"宋远桥" 的职位和薪资相同的员工信息

#1. 查询与“鹿杖客”，"宋远桥" 的职位和薪资
select job,salary from emp where name = '鹿杖客' or name = '宋远桥';

结果：

#2.
select * from emp where (job,salary) in (select job,salary from emp where name = '鹿杖客' or name = '宋远桥')

结果：