【MySQL】复合查询

参考表

1.子查询和查询回顾

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

select empno,ename from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';

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

select  * from emp order by deptno ASC sal desc;

• 使用年薪进行降序排序

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

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

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

--/不要对format处理的数据进行重命名，该命名无效

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

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

2.多表查询

select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno = DEPT.deptno;

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

select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno = 10;

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

select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;

3.自连接

同一张表连接查询

案例：
需要给表名重命名
显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号–empno）

• 使用子查询

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

• 使用多表查询

-- 使用到表的别名
--from emp leader, emp worker，给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识别 
select leader.empno,leader.ename from emp leader, emp worker where leader.empno = worker.mgr and worker.ename='FORD';

4.子查询

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

4.1单行子查询

2.多行子查询

返回多行记录的子查询

3.多子列查询

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

4.4 在from子句中使用子查询

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

5.合并查询

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

5.1union

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

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

mysql> select ename, sal, job from EMP where sal>2500 union
-> select ename, sal, job from EMP where job='MANAGER';--去掉了重复记录
+-------+---------+-----------+
| ename | sal | job |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| CLARK | 2450.00 | MANAGER |
+-------+---------+-----------+

5.2union all

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

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

mysql> select ename, sal, job from EMP where sal>2500 union all
-> select ename, sal, job from EMP where job='MANAGER';
+-------+---------+-----------+
| ename | sal | job |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| CLARK | 2450.00 | MANAGER |
+-------+---------+-----------+

6.内外连接

6.1 内连接

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

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

6.2 外连接

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

左外连接

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

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

右外连接

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

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