mysql复合查询 -- 多表查询(介绍,笛卡尔积,使用),自连接(介绍,使用)
目录
多表查询
介绍
使用
表数据
显示雇员名,雇员工资,以及所在部门名
显示部门号为10的部门名,员工名,工资
自连接
介绍
场景
表数据
题目
子查询
自连接
多表查询
介绍
实际开发中往往数据来自不同的表，所以需要多表查询语法:
- from + 表1,表2 (笛卡尔积方式)
- 或是使用内连接的语法(在其他博客中介绍,努力肝ing)
使用
表数据
来自 -- scott_data.sql · YoungMLet/scott_data - Gitee.com
显示雇员名,雇员工资,以及所在部门名
需要显示部门名,但是emp表里只有部门编号,所以还需要在dept(部门)表里进行查询:
如果直接对两张表做整合(生成笛卡尔积),每个员工会重复出现四次,因为dept里有四个部门:
- 整合时会将[emp表中每一条信息]分别与[dept表中的每一条信息]结合 -> 4条新的记录:
- 得到的结果是穷举组合 -- 笛卡尔积(所有可能的有序对组合)
因为"mysql中一切皆表"
- 所以得到的穷举结果也是表结构
- 所以我们可以对这张表进行条件筛选
因为每个员工都有自己所属部门,其他三条信息是多余的,我们可以直接过滤掉:
- where emp.deptno = dept.deptno (这里的部门编号就类似于外键功能)
然后进行信息筛选,因为我们只要三列数据(ename,sal,dname):
- 如果列名在两张表中是唯一的,可以不用加所属表名
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;
显示部门号为10的部门名,员工名,工资
要得到部门名,还是和上面一样的操作,只不过增加一个筛选(deptno=10):
select dname,ename,sal from emp,dept where emp.deptno = dept.deptno and emp.deptno=10;
显示每个员工的姓名,工资,工资级别:
- 又是同样的问题,在员工表中没有工资级别这一信息,需要查询工资表
那么还是一样的,将之前得到的结果视为一张表,和工资表进行整合,并过滤多余信息(工资要处在工资等级的区间内才算有效信息):
select ename,sal,grade from emp,salgrade where sal>losal and sal < hisal; //或是 select ename,sal,grade from emp,salgrade where sal between losal and hisal;
自连接
介绍
对同一张表进行连接查询
- 也就是对一张表做笛卡尔积
但sql不允许from后面写两次同一个表名
- 所以我们需要对表做重命名
- 一旦重命名后,新名字也可以在sql其他地方出现 -- 因为[从表中拿数据]一定是sql语句执行的第一步
场景
表数据
我们使用来自oracle 9i的经典测试表作为测试样例 -- scott_data.sql · YoungMLet/scott_data - Gitee.com
- 下载好后,在mysql中使用source命令+.sql文件所在路径,恢复出emp表:
题目
显示某个员工的上级领导的编号和姓名 (mgr是员工领导的编号)
- 先找到该员工的领导编号,再拿着编号去员工表里找信息
- 以上两个动作都与员工表有关
有两种做法:
子查询
简单来说,就是在其他sql语句中嵌套使用select语句
select empno ,ename from emp where empno = (select mgr from emp where ename = 'FORD' );
自连接
让员工表自己与自己组合起来
- 先找出整合后的特定员工FORD的相关信息:
select * from emp e1 ,emp e2 where e1.ename='FORD';筛选出来的是FORD分别与所有员工组合在一起的信息
- 然后找到 FORD的mgr (领导的员工编号)与 其他员工的empno相等的那一条 -- 那条信息就是FORD与其老板的数据
select * from emp e1 ,emp e2 where e1.ename='FORD' and e1.mgr=e2.empno;(这里我们筛选出的信息(where e1.mgr=e2.empno)
- 所以e1代表员工,e2代表领导
而我们只想要领导数据(保留e2中的编号和姓名),所以过滤掉无关信息:
select e2.empno,e2.ename from emp e1 ,emp e2 where e1.ename='FORD' and e1.mgr=e2.empno;
