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;

•