复合查询
基础查询
-
查询工资高于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';