Mysql中的复合查询详解_Mysql

1. 基本查询简单回顾

条件筛选	使用 and、or、like 等条件操作符进行多条件筛选。
排序	使用 order by 对查询结果进行排序，支持多个字段排序（如按部门号升序，工资降序）。
聚合函数与分组	使用 group by 对数据进行分组，并使用聚合函数（如 avg(), max(), count()）进行汇总。
子查询	用于动态选择数据，如查询工资高于平均工资的员工。
内嵌查询	查询工资最高的员工、显示员工的年薪等。

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

使用年薪进行降序排序：

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

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

方案一：

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

方案二：

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

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

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

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

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

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

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

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

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

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

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

笛卡尔积：

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

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

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

select ename,sal,dname 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 emp e1, emp e2 where e1.empno=e2.mgr;

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

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

返回多行记录的子查询

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

select ename ,job,sal,deptno from emp where 
job in(select 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);

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

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

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

查询和smith的部门和岗位完全相同的所有雇员，不含smith本人

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

在mysql中每一次的查询结果都是一个子表。

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

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

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

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

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

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