【MySQL】 复合查询 | 内外连接

1. 复合查询

多表笛卡尔积

显示雇员名、雇员工资以及所在部门的名字

由于员工 信息属于 emp表 而所在部门名字属于 dept表
数据来自不同的表，所以需要进行多表查询

---

表示从 emp (员工表) 和dept (部门表) 中获取信息

---

分别用emp员工的信息 与 dept 部门表的信息 做 穷举 组合
就称 两张表 进行笛卡尔积
就把两张表 变成了 一张表 ，就可以进行单表查询

---

如 smith 本来是20号部门的，把10号部门的信息传过来 是没有意义的

---

输入 select * from emp,dept where emp.deptno =dept.deptno;
把对应的部门号 与部门信息 匹配上

---

输入 select ename ,sal,dname from emp,dept where emp.deptno =dept.deptno;
就可以显示雇员名、雇员工资以及所在部门的名字

自连接

自连接 指的是在同一张表连接查询

---

将同一张表单纯的写两次，是不可以一起查询的

---

可以把同一张表的表名通过重命名的方式 分别 进行不同命名 即可

把对同一张表 进行笛卡尔积的 行为 叫做 自连接

---

显示员工FORD的上级领导的编号和姓名

先找 员工FORD 和领导编号 ，与员工表有关
再通过领导的编号，找领导信息，也是与员工表有关
所以就需要借助 自连接

---

输入 select e2.ename ,e2.empno from emp e1,emp e2 where e1.ename=‘FORD’ and e1.mgr=e2.empno;
通过第一张表emp中的 员工FORD， 找到对应的领导编号，正好对应第二张emp表的员工编号
所以设置 e1.mgr =e2.empno

这样就可以 通过员工FORD 找到 对应的 领导 的名字和编号

在where子句使用子查询

子查询 是指嵌入其他sql语句中的select语句，也叫嵌套查询

单行子查询

返回一行记录的子查询

查看emp表 的员工信息

---

想要查看工资最高的员工的名字和工作岗位
首先 要找到 最高工资，但是发现这样写是不可以的

子查询： 在 一条sql内部 再执行 select查询
先执行内部的sql语句，再执行外部的sql语句

---

先找到 最高工资 ，再通过最高工资找到对应这个人的名字和工作信息

---

多行子查询

返回多行记录的子查询

in关键字

用来进行判断一个对应的列值是否在一个集合当中
若在集合中，则为查找成功

---

查询和10号部门的工作岗位相同雇员的名字、岗位、工资、部门号 ，但是不包含10号自己的

首先要找到10号部门的工作岗位

---

再通过in关键字，只有工作种类在 10号部门的工作岗位的集合中 ，才能返回

---

使用and 关键字 ，同时也不包含10号部门的人的信息

---

all关键字

表示所有人

---

显示工资比部门30的所有员工的工资高的员工的姓名 、工资 和部门号

先取到 30号部门的所有员工对应的工资
加入 distinct 去除重复的工资

---

all (select distinct sal from emp where deptno=30) 表示大于 30号部门的所有员工的薪资

---

any关键字

表示比任意一个高

---

显示工资比部门30的任意员工的工资高的员工的排名(包含自己部门)

先取到 30号部门的所有员工对应的工资
加入 distinct 去除重复的工资

---

any(select distinct sal from emp where deptno=30) 表示大于 30号部门的任意员工的薪资

---

多列子查询

多列子查询 是指 返回多个列数据的子查询语句

---

查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

首先要找到SMITH对应的部门和岗位

---

因为要比较的是 两个条件 部门和岗位
而mysql 支持 圆括号 直接写两列
将SMITH 对应的部门和岗位作为筛选条件

---

还需添加条件 ename <&gt; ‘SMITH’ 不能为SMITH员工本身

---

在from子句中使用子查询

在查询语句在from子句中，把一个子查询当作一个临时表使用

---

显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

首先找到每个部门的平均工资
通过group by 将各个部门分开，再获取对应的每个部门的平均工资

---

将 select deptno,avg(sal) from emp group by deptno 整体操作所形成的数据 看作一张表，并重命名为 tmp，与emp表 做 笛卡尔积

---

寻找到emp表的部门号 与 tmp表的部门号 相同的 数据 才是合适的，所以加上 emp.deptno=tmp.deptno

---

select deptno,avg(sal) myavg from emp group by deptno
为了不让其在后面比较起冲突，所以 将avg(sal) 重命名为 myavg
再添加 emp.sal > tmp.myavg 筛选条件，显示每个高于自己部门平均工资的员工信息

---

合并查询

为了合并多个select的执行结果，可以使用集合符 union 和 union all

---

union

该操作符用于取得 两个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行

---

select * from emp where sal>2500 即工资大于2500 的人的信息

---

select * from emp where job=‘MANAGER’ 即工作岗位为 manager的人的信息

---

发现两者使用重合信息存在的，所以可以使用 union

使用 union ，会自动去除 两者 重合部分的 重复数据

---

union all

在union的基础上，不会去除重复部分的重复数据

重复的数据会被保留

---

2. 内连接

内连接 是指 使用where子句对两种表形式的笛卡尔积 进行筛选

---

语法：
select 字段 from 表1 inner join 表2 on 连接条件 and 连接条件;

---

显示SMITH的名字和部门名称

筛选条件为 emp.deptno=dept.deptno and ename=‘SMITH’
表示 两张表的 对应 部门号 要相同 ，同时 名字为 SMITH

3. 外连接

左外连接

左侧表完全显示 右侧的表按条件拼接(条件满足拼接，条件不满足拼NULL)

---

语法：
select 字段名 from 表名1 left join 表名2 on 连接条件

相对于内连接的语法，外连接语法只是把inner 替换成了 left

---

创建一张表 stu ，内部包含 id 和 name

---

向stu表中 插入对应的数据 ，并 将其显示出来

---

再次创建一张成绩表 exam ，内部 包含id 和grade

---

向exam表中 插入对应的数据，并将其显示出来

---

发现stu 学生表中 有四名同学，而exam成绩表中 只有三个成绩
而且成绩表中有一个 不存在的id值为11 的学生

---

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

由于有些学生是没有成绩的，就导致两张表中的id 匹配不上
若只拿id 做内连接，就只有 id值为 1 2 的信息能够显示

---

为了保证所有学生的信息都显示，所以使用 左外连接

右外连接

左侧的表按条件拼接(条件满足拼接，条件不满足拼NULL) 右侧表完全显示

---

语法：
select 字段 from 表名1 right join 表名2 on 连接条件

---

对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要
显示出来

stu 学生表中 有四名同学，而exam成绩表中 只有三个成绩
而且成绩表中有一个 不存在的id值为11 的学生

---

此时右侧exam表的三个id值 全部显示出来了
左侧stu表 由于只有两个id值与exam表中的id值对应，所以只显示2个，剩下显示为NULL