【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 <> ‘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