复合查询
1、单表查询
select empno,ename,sal,job from emp
where (sal > 500 or job='MANAGER') and ename like 'J%';
select deptno,sal,ename from emp
order by deptno asc, sal desc;
select ename, sal*12+ifnull(comm, 0) as nianxin from emp
order by nianxin desc;
select ename, job, sal from emp
where sal = (select max(sal) from emp);
select ename, job, sal from emp
where sal > (select avg(sal) from emp);
select max(sal), format(avg(sal), 2), deptno from emp
group by deptno;
select deptno, avg(sal) from emp
group by deptno having avg(sal) < 2000;
select count(empno), avg(sal), job from emp
group by job;
2、多表查询
有三张表EMP,DEPT,SALGRADE
select emp.ename, emp.sal, dept.dname from emp, dept
where emp.deptno=dept.deptno;
select dept.dname, dept.deptno, emp.ename, emp.sal from emp, dept
where emp.deptno=dept.deptno and emp.deptno = 10;
select emp.ename, emp.sal, salgrade.grade from emp, salgrade
where sal between losal and hisal;
1.select empno, ename from emp
where empno=(select mgr from emp where ename='FORD');
2.select l.empno, l.ename from emp as l, emp as w
where l.empno=w.mgr and w.ename='FORD';
-
子查询---子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询
select ename,job,empno,sal,deptno from emp
where deptno=(select deptno from emp where ename='SMITH');
select ename, job, sal, deptno from emp
where job in (select job from emp where deptno=10);
1.select ename,sal,deptno from emp
where sal > all(select sal from emp where deptno=30); //子查询单行
2.select ename,sal,deptno from emp
where sal > (select max(sal) from emp where deptno=30); //子查询多行
1.select ename,sal,deptno from emp
where sal > any(select sal from emp where deptno=30);
2.select ename,sal,deptno from emp
where sal > (select min(sal) from emp where deptno=30);
select ename,sal,empno,deptno,job from emp
where (deptno, job)=
(select deptno,job from emp where ename='SMITH' and ename<>'SIMTH');
每个部门的平均工资表:select deptno dt, avg(sal) lsal from emp group by deptno; //临时表
select emp.ename, emp.deptno, emp.sal, tmp.lsal
from emp, (select deptno dt, avg(sal) lsal from emp group by deptno) tmp
where emp.deptno=tmp.dt and sal > tmp.lsal;
每个部门的最高工资表:select deptno dt, max(sal) msal from emp group by deptno; //临时表
select emp.ename, emp.deptno, emp.sal, tmp.msal
from emp, (select deptno dt, max(sal) msal from emp group by deptno) tmp
where emp.deptno=tmp.dt and sal = tmp.msal;
每个部门的人员数量表:select deptno, count(empno) tatal from emp group by deptno; //临时表
1.select dept.dname, dept.deptno, dept.loc, tmp.tatal
from dept, (select deptno, count(empno) tatal from emp group by deptno) tmp
where dept.deptno=tmp.deptno;
// 多表查询
2.select dept.dname, dept.loc, dept.deptno, count(*) from dept, emp
where dept.deptno=emp.deptno
group by dept.dname, dept.deptno, dept.loc;
select ename from emp where sal > 2500 or job='MANAGER';
// 1.union all 不去重
select ename from emp where sal > 2500
union all
select ename from emp where job='MANAGER';
// 2.union 去重.当使用该操作符时,会自动去掉结果集中的重复行。
select ename from emp where sal > 2500
union
select ename from emp where job='MANAGER';