【MySQL】子查询、合并查询、表的连接

目录

一、子查询

1、单行子查询

显示SMITH同一部门的员工信息

2、多行子查询

in关键字

查询和10号部门的工作岗位相同的雇员的名字、岗位、工资、部门号，但是筛选出的雇员的部门不能有10号部门

all关键字 

查询工资比30号部门中所有雇员工资高的雇员的姓名、工资、部门号

any关键字

显示工资比30部门中任意雇员工资高的雇员的姓名、工资、部门号，可以包含部门号是30的雇员

3、多列子查询

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

4、在from子句中使用子查询

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

查找每个部门工资最高的雇员的姓名、工资、部门、最高工资

二、合并查询

union

将工资大于2500或职位是MANAGER的雇员找出来

union all

三、表的连接

内连接

显示SMIATH的名字和部门名称

外连接

左外连接

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

 右外连接

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

显示部门名称和这些部门的雇员信息，同时列出没有雇员的部门

一、子查询

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

1、单行子查询

返回一行记录的子查询

显示SMITH同一部门的员工信息

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

2、多行子查询

返回多行记录的子查询

in关键字

查询和10号部门的工作岗位相同的雇员的名字、岗位、工资、部门号，但是筛选出的雇员的部门不能有10号部门

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

all关键字 

查询工资比30号部门中所有雇员工资高的雇员的姓名、工资、部门号

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

//或者是使用聚合函数
select ename,sal,deptno from emp where sal > (select max(sal) from emp);

any关键字

显示工资比30部门中任意雇员工资高的雇员的姓名、工资、部门号，可以包含部门号是30的雇员

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

3、多列子查询

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

select * from emp where (deptno,job) = (select deptno,job where ename='SMITH') and ename<>'SMITH';

4、在from子句中使用子查询

子查询出现的两种场景：1.在where后出现充当筛选筛选条件。2.在from后出现充当临时表(一般是笛卡尔积)。

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

select ename,detpno,sal,a_s from emp,(select avg(sal) a_s,deptno dt from emp group by deptno) tmp where emp.sal>tmp.a_s and emp.deptno=tmp.dt;

查找每个部门工资最高的雇员的姓名、工资、部门、最高工资

select ename,sal,emp.deptno,m_s form emp,(select deptno,max(sal) m_s from emp group by deptno) tmp where emp.deptno=tmp.deptno AND sal = m_s;

二、合并查询

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

union

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

将工资大于2500或职位是MANAGER的雇员找出来

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

union all

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

三、表的连接

表的连接分为内连接和外连接

内连接

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

//基本格式：
select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

显示SMIATH的名字和部门名称

//之前的写法:
select ename,dname from emp,dept where emp.deptno=dept.deptno and ename='SMITH';

//使用标准的内连接写法:
select ename,dname form emp inner join dept on emp.deptno=dept.deptno and ename='SMITH';

外连接

左外连接

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

//语法:
select 字段名 form 表1 left join 表2 on 连接条件

 建两张表：

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

 右外连接

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

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

显示部门名称和这些部门的雇员信息，同时列出没有雇员的部门

//使用左外连接:
select d.dname,e.* from  dept d left join emp e on d.deptno=e.deptno;