MySQL-复合查询

复合查询

基础查询

• 查询工资高于500或者岗位为MANAGER的员工，同时名字首字母是J

  select * from emp where (sal>500 or job='MANAGER') and substr(ename,1,1)='J';

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

  mysql> select * from emp order by deptno asc,sal desc;

  

• 年薪降序

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

• 显示工资最高的员工的名字和工作岗位（子查询结果作为筛选条件）

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

  

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

  select *from emp where sal>(select avg(sal) from emp);

  

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

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

  

• 显示平均工资<2000的部门号和他的平均工资having

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

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

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

  

多表查询

将一个表中的一条信息和另一个表的所有信息进行拼接，就是 笛卡尔积，将很多表拼接成一个表，所以将来的多表查询都是单表查询。穷举之后有很多信息是无效的，需要进行清洗。（可能一个人既是10号部门又是20部门）

所有select查询出来的记录，都可以把他看成表。先将所有表都拼接在一起，然后再进行清洗，再筛选你想要的东西。

• 显示员工名工资和所在部门名字

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

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

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

• 显示各个员工的姓命和工资和工资级别

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

自连接

就是相同的表做笛卡尔积，同一张表做连接查询叫做自连接。

• 显示员工FORD上级领导的个人信息

  • 采用子查询的方式

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

  • 笛卡尔积的方式

    select leader.ename,leader.empno from emp leader,emp worker where worker.ename='FORD'and worker.mgr=leader.empno;

子查询

嵌套查询

单行子查询

• 选出和SMITH 相同部门的人

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

多行子查询

• in关键字，我是否是你们中的一元

  • 查询和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 distinct sal from emp where deptno=30); 30);

多列子查询

• 单行多列子查询

  • 查询和SMITH部门和岗位完全相同的所有员工，不包含SMITH。

  select ename from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH')and ename!='SMITH';

where条件和from字句中使用，所有的表都可以进行笛卡尔积进行穷举，所拥有的表都可认为是单表，那么如何将多表转化为单表。

• 子查询语句出现在from后面

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

    select ename,emp.deptno,sal,myavg from emp,(select deptno,avg(sal) myavg from emp group by deptno) avg_tb where emp.deptno=avg_tb.deptno and emp.sal>avg_tb.myavg;

  

  • 再加一个部门名称（表再进行拼接）

    select dname, ename,sal from dept,(select ename,emp.deptno,sal,myavg from emp,(select deptno,avg(sal) myavg from emp group by deptno) avg_tb where emp.deptno=avg_tb.deptno and emp.sal>avg_tb.myavg) tmp where dept.deptno=tmp.deptno;

  • 查找每个部门的最高工资的人的姓名,部门名，薪水。

    select ename,emp.deptno,sal from emp,(select deptno,max(sal) max_sal from emp group by(deptno)) max_tb whereemp.deptno=max_tb.deptno and emp.sal=max_sal;

  • 显示每个部门的信息（部门名编号地址）

    select dept.deptno,dname, loc,部门人数 from dept,(select deptno,count(*) 部门人数 from emp group by deptno) cnt_tb where dept.deptno=cnt_tb.deptno;

    第二种做法：

  

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

  如果有报错就将所有的筛选信息在后面都写一遍。

  this is incompatible with sql_mode=only_full_group_by

合并查询

就是将两个结果集的并集（常规+union）

uion会自动去重

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

  select * from emp where sal>2500 or job='MANAGER';

union all就是不去重

uion会自动去重

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

  select * from emp where sal>2500 or job='MANAGER';

union all就是不去重