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

 Welcome to 9ilk's Code World

       

(๑•́ ₃ •̀๑) 个人主页:       9ilk

(๑•́ ₃ •̀๑) 文章专栏：     MySQL  

---

🏠 基本查询回顾

假设有以下表结构：

• 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

思路 :  用两个条件对员工表进行筛选。条件1：工资高于500或岗位为MANAGER。条件2：姓名首字母为大写的J。条件1内为或关系，条件1和条件2为并联关系。

参考代码：

//like
select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';
//子串
select * from emp where (sal>500 or job='MANAGER') and substring(ename,1,1)=='J'; //截取子串 

测试结果：

• 按照部门号升序而雇员的工资降序排序

参考代码：

select * from emp order by deptno asc , sal desc; //asc升序 desc降序

测试结果：

• 使用年薪进行降序排序

员工表中的comm奖金字段可以为空，但是MySQL中NULL是不参与运算的,我们可以使用ifnull函数进行处理。

参考代码：

//奖金可以为空有的岗位没奖金 所以对这种情况可以使用ifnull 是null就第二个参数
select *,sal*12+ifnull(comm,0) 年薪  from emp;

测试结果：

• 显示工资最高的员工的名字和工作岗位

求最高可以使用排序也可以使用聚合函数。

参考代码：

select ename,job from emp order by sal desc limit 1;//排序
select ename, job from emp where sal = (select max(sal) from emp); //聚合函数子查询

测试结果：

注：MySQL允许在一条SQL内部再执行select查询，称为子查询！

• 显示工资高于平均工资的员工信息

思路：我们先需要知道员工表中所有员工的平均信息，然后在员工表中根据平均工资筛选员工信息。

参考代码：

select * from emp where sal > (select AVG(sal) from emp); //先聚合统计平均工资

测试结果：

• 显示每个部门的平均工资和最高工资

参考代码：

select deptno,AVG(sal),max(sal) from emp group by deptno;
//先分组 再聚合

测试结果：

• 显示平均工资低于2000的部门号和它的平均工资

思路“我们先根据部门进行分组，然后对每个组进行聚合取得平均工资，最后对分组之后的结果having进行筛选。

参考代码：

select deptno,AVG(sal)平均工资  from emp group by deptno having 平均工资 < 2000;

测试结果：

• 显示每种岗位的雇员总数，平均工资

参考代码：

select AVG(sal) 平均工资, count(ename) from emp group by job;
select job,count(*), format(avg(sal),2) from emp group by job;

测试结果：

🏠 多表查询

实际开发中往往数据来自不同的表，所以需要多表查询。本节我们用一个简单的公司管理系统，有三张表emp,dept,salgrade来演示如何进行多表查询。

案例：

• 显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询

分析：雇员名和雇员工资信息来自员工表，而所在部门名字的信息来自部门表，那我们需要两张表的数据进行组合。

多表查询本质：将多张表中数据进行穷举组合，多张表进行笛卡尔积。此时多张表变为单表，多表操作转化为对单表的操作！

多表笛卡尔积会有多种组合结果,但有的组合结果是没有意义的,所以只要emp表中的deptno = dept表中的deptno字段的记录,其他的都是没意义的。

参考代码：

select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno= dept.deptno;
//
select ename,sal,dname from emp,dept where emp.deptno= dept.deptno;

测试结果：

注：对于两张表中各自的特有字段在查询时，不需要指明所属哪张表，如果是共有字段则需要指明是哪一张表的，否则会发生冲突。

MySQL中一切皆表，组合之后的表也是表结构！也可以对该表结构的数据进行整合。

• 显示部门号为10的部门名，员工名和工资

参考代码：

select emp.ename,dept.dname,sal from emp,dept where (emp.deptno=dept.deptno and emp.deptno=10);

测试结果：

• 显示各个员工的姓名，工资，及工资级别

思路：工资级别以及工资信息在工资表里，因此我们需要多表查询。同时工资表中有工资等级所属的工资范围，我们可以根据范围来判断员工表中员工薪资所属等级。

参考代码：

select ename,sal,grade from emp,salgrade where sal between losal and hisal;

测试结果：

🏠 自连接

自连接是指在同一张表连接查询，也就是同一张表进行笛卡尔积。

可行性：

select * from salgrade,salgrade;

测试结果：

注：两张相同的表进行笛卡尔积，表名相同会造成冲突！我们需要对两张表进行重命名。

案例： 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）

• 方法1：使用子查询

思路：先用子查询获取工FORD的上级领导的编号，再通过编号筛选出领导的相关信息。

参考代码：

select ename,empno from emp where empno=(select mgr from emp where ename='FORD');

测试结果:

• 方法2：使用多表查询

思路：两张相同表进行笛卡尔积，假设t1为单纯员工表，t2用来作为“查询上级表”，则我们可以根据t2的mgr找出t1中是xxx的上级的员工；然后再筛选出t2表中名字是FORD，最后筛选出t1表中所求上级的编号和姓名。

参考代码：

 select t1.empno,t1.ename from emp as t1,emp as t2 where t1.empno=t2.mgr and t2.ename='FORD';

测试结果：

注：from执顺序先于where，因此where可以使用表的重命名！

🏠 子查询

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

🎵 单行子查询

单行子查询：返回一行记录的查询。

• 显示SMITH同一部门的员工

参考代码：

select * from emp where deptno=(select deptno from emp where ename='SMITH');

测试结果：

🎵 多行子查询

多行子查询：返回多行记录的子查询。

• in关键字；查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的。

思路：我们可以根据子查询10号部门的工作岗位然后进一步筛选。

参考代码：

select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10;

测试结果：

如果还想知道上面条件对应的员工属于部门的名字呢？

此时我们可以用上面筛选出来的“表”再和部门表进行笛卡尔积，筛选出部门名字！

参考代码：

select ename,job,sal,dname from (select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10) as tmp,deptp,dept where tmp.deptno=dept.deptno;

测试结果：

注：一个SQL的查询结果也是一个表结构，MySQL一切皆表，不是物理上真实存在的表才能做笛卡尔积。同时子查询不仅能出现在where后，也能出现在from后！

• all关键字；显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。

all表示的是查询结果中最大的

测试代码：

select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=30);
//也可以使用聚合函数MAX
select ename,sal,deptno from emp where sal > (select MAX(sal) from emp where deptno=30);

测试结果：

• any关键字；显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）。

any表示的就是查询结果中的任意一个。

参考代码：

select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30);

测试代码：

🎵 多列子查询

单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句。

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

思路：我们需要根据两个列的字段(部门和岗位)进行筛选，然后根据筛选结果筛选出其他雇员的信息。

参考代码：

select * from emp where deptno=(select deptno from emp where ename='SMITH') and job=(select job from emp where ename='SMITH') and ename<>'SMITH';
//多列子查询
select * from emp where (deptno,job) = (select deptno,job from emp where ename='SMITH') and ename<>'SMITH';

测试结果：

注：使用多列子查询时，括号内的列顺序和数目要和子查询的列顺序和数目匹配！

总结：目前全部的子查询都在where子句中，充当判断条件！但其实任何时刻，查询出来的结构，本质在逻辑上也是表结构！

🎵 from子句中使用子查询

子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。

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

思路：先找出每个部门的平均工资（分组）,再根据这个子表和原表进行笛卡尔积,筛选出原表工资大于子表平均工资并且部门号不冲突的

参考代码：

//
select ename, deptno, sal, format(asal,2) from emp, (select avg(sal) asal, deptno dt from emp  group by deptno) tmp where emp.sal > tmp.asal and emp.detnnoptno=tmp.dt;
//
select emp.ename,emp.deptno,emp.sal,tmp.asal from (select AVG(sal) asal,deptno from emp group by deptno) as tmp,emp where (emp.sal > tmp.asal) and emp.depdeptno=tmp.deptno;

测试结果：

• 查找每个部门工资最高的人的姓名、工资、部门、最高工资

思路：先找出每个部门的最高工资（分组）,再根据这个子表和原表进行笛卡尔积,找出原表中工资等于子表中筛选出的每个部门的最高工资的&&满足两表部门号相同。

参考代码：

select emp.deptno,emp.ename,emp.sal,tmp.msal from (select deptno,MAX(sal) msal from emp group by deptno) tmp,emp where tmp.msal = emp.sal and tmp.deptno=emp.deptno;

测试结果：

• 显示每个部门的信息（部门名，编号，地址）和人员数量

（1）方法1：使用子查询

参考代码：

select dept.dname,dept.loc,dept.deptno,tmp.num from dept,(select deptno,count(*) num from emp group by deptno) tmp where dept.deptno = tmp.deptno;
//1.对EMP表进行人员统计
//2.将上面的表看作临时表

测试结果：

（2）使用多表

参考代码：

select emp.deptno,count(*),dept.dname,dept.loc from emp,dept where emp.deptno=dept.deptno group by emp.deptno,dept.loc,dept.dname;

测试结果：

总结：解决多表问题的本质:首先是先想办法把多表转化为单表,所以MySQL中所有select的问题全部都可以转化为单表问题!

🎵 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。

1. union

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

案例：将工资大于2500或职位是MANAGER的人找出来

参考代码：

select ename,sal,job from emp where sal>2500 union select ename ,sal,job from emp where job='MANAGER';

测试结果：

2. union all

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

注：使用合并查询时列信息必须一样

🏠 表的内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；

注：前面学习的都是内连接。

案例：显示SMITH的名字和部门名称

1. where

-- 用前面的写法
select ename, dname from EMP, DEPT where EMP.deptno=DEPT.deptno and
ename='SMITH';

2. 标准内连接

-- 用标准的内连接写法
select ename, dname from EMP inner join DEPT on EMP.deptno=DEPT.deptno and
ename='SMITH';

🏠 表的外连接

外连接分为左外连接和右外连接。

🎵 左外连接

如果联合查询，左侧的表完全显示我们就说是左外连接。

案例：

-- 建两张表
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);

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

参考代码：

-- 当左边表和右边表没有匹配时，也会显示左边表的数据
select * from stu left join exam on stu.id=exam.id;

测试结果：

此时左表中每一个id都会显示,即使在右表没找到相同的id。

🎵 右外连接

如果联合查询，右侧的表完全显示我们就说是右外连接。

语法：

select 字段 from 表名1 right join 表名2 on 连接条件；

案例：

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

参考代码：

select * from stu right join exam on stu.id=exam.id;

测试结果：

• 列出部门名称和这些部门的员工信息，同时列出没有员工的部门

1.  方法一：左外连接

select d.dname, e.* from dept d left join emp e on d.deptno=e.deptno;

2. 方法二：右外连接

select d.dname, e.* from emp e right join dept d on d.deptno=e.deptno;

测试结果：

---

完。