Mysql中的复合查询详解

古乐悦

1. 基本查询简单回顾

条件筛选	使用 AND、OR、LIKE 等条件操作符进行多条件筛选。
排序	使用 ORDER BY 对查询结果进行排序，支持多个字段排序（如按部门号升序，工资降序）。
聚合函数与分组	使用 GROUP BY 对数据进行分组，并使用聚合函数（如 AVG(), MAX(), COUNT()）进行汇总。
子查询	用于动态选择数据，如查询工资高于平均工资的员工。
内嵌查询	查询工资最高的员工、显示员工的年薪等。

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

1. select *from emp where ename like 'j%' and (sal>500 or job='MANAGER');

复制代码

使用年薪进行降序排序：

1. select ename ,job,sal*12+ifnull(comm,0) sarn  from emp order by sarn desc;

复制代码

显示工资最高的员工的名字和工作岗位：
方案一：

1. select ename ,job ,sal from emp where sal in(select max(sal) from emp );

复制代码

方案二：

1. select ename ,job ,sal from emp order by sal desc limit 1;

复制代码

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

1. select ename ,job ,sal from emp where sal>(select avg(sal) from emp);

复制代码

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

1. select deptno,format(avg(sal),2),max(sal) from emp group by deptno;

复制代码

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

1. select deptno ,avg(sal) avgsal from emp group by deptno having avgsal<2000;

复制代码

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

1. select job ,count(*) from emp group by job;

复制代码

2. 多表查询

联接查询	通过表之间的关系字段（如 deptno）进行联合查询，常用的连接方式包括内连接（INNER JOIN）等。
跨表查询	通过连接多个表（如 EMP 和 DEPT）来获取联合结果。
使用 GROUP BY 和聚合函数	计算每个部门的平均工资和最高工资等。
连接条件	可以通过多个条件进行查询，显示特定条件下的数据。

将第一张表的每一条数据和第二章表的每一条数据相结合，不加过滤条件得到的结果被称为笛卡尔积

笛卡尔积：

显示部门为10的员工和部门的全部信息

1. select * from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;

复制代码

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

1. select ename,sal,dname from emp,dept
   
2. where emp.deptno=dept.deptno and emp.deptno=10;

复制代码

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

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

复制代码

3. 自连接（Self-Join）

表的别名	自连接时需要使用别名来区分同一表的不同实例。
示例	查找员工的上级（使用 mgr 字段指向经理的 empno）。
应用场景	员工与其上级、领导层次的关系查询。

将两张相同的表做笛卡尔积

• 显示员工及员工领导的个人信息
  

1. select * from emp e1, emp e2 where e1.empno=e2.mgr;

复制代码

4. 子查询

单行子查询	子查询只返回一行结果，通常用于 =、< 等条件。
多行子查询	子查询返回多行结果，常配合 IN、ALL、ANY 等关键字使用。
多列子查询	返回多个列的数据，适用于多条件的筛选。
FROM 子查询	将子查询作为临时表使用，来简化复杂查询。
嵌套查询	通过将查询嵌套在其他查询语句中，增加查询灵活性。

4.1单行子查询

• 显示SMITH同一部门的员工
  

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

复制代码

4.2多行子查询

返回多行记录的子查询
in关键字；查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

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

复制代码

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

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

复制代码

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

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

复制代码

4.3多列子查询

单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句
查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

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

复制代码

4.4在from中使用子查询

在mysql中每一次的查询结果都是一个子表。
显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

1. select ename,deptno,sal,asal from emp t1,(select deptno dt,avg(sal) asal  
   
2. from emp group by dt)t2 where t1.sal>t2.asal and t1.deptno=t2.dt;

复制代码

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

1. select ename,sal,emp.deptno,msal
   
2. from emp ,(select deptno,max(sal)msal from emp group by deptno)
   
3. emp2 where sal=msal and emp.deptno=emp2.deptno ;

复制代码

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

1. select *from dept,
   
2. (select deptno,count(deptno) from emp group by deptno)dept2
   
3. where dept.deptno=dept2.deptno;

复制代码

5. 合并查询

UNION	将多个 SELECT 查询结果合并，去除重复记录。
UNION ALL	将多个 SELECT 查询结果合并，不去除重复记录。
应用场景	如查询职位为 MANAGER 或工资高于某数值的员工。

5.1UNION

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

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

5.2UNION ALL

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

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持晓枫资讯。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

杨二朵

路过，支持一下