数据库三（子查询）

(1)不相关子查询 :()里的查询可以独立运行  ,先执行子查询，再执行外查询
  单行子查询： 因为子查询的结果是单行单列

select * from emp;
--查询薪水比allen高的员工信息
-- (1)查询allen的薪水
select sal from emp where ename='ALLEN';
-- (2)查询比薪水高于1600的员工信息
select * from emp where sal>1600;
--总结
select * from emp where sal >(select sal from emp where ename='ALLEN');
 子查询的作用: 
   a)子查询可以作为查询/修改/删除的条件

--案例:
  --查询与SCOOT职位相同的员工信息
  --(1)先查询scott所从事的职位
   select job from emp where ename='SCOTT';
  --(1)作为2的查询条件来使用
select * from emp where  job=(select job from emp where ename='SCOTT') and ename<>'SCOTT';

  
  --删除薪水低于‘ALLEN’的员工
  delete from emp where  sal<(select sal from emp where ename='ALLEN');

  
   b)子查询可以作为修改和新增值来使用
   
  --将SMITH的薪水设置为与SCOTT相同
  update emp set sal=(select sal from emp where ename='SCOTT' ) where ename='SMITH';
oracle支持，mysql不支持
  --MARRY今天入职到'WARD'部门
    insert into emp (empno,ename,job,hiredate,deptno)
     values  (1111,'MARRY','CLERK',sysdate,
   (select deptno from emp where ename='WARD'));
  

   c)子查询可以作为“表”来使用

 --查询每个部门的总人数，平均薪水及部门名称
select deptno,count(*)as num ,avg(sal) as avgsal from emp where deptno is not null
 group by deptno;
select d.deptno,dname,num,avgsal from dept d,
  (select deptno,count(*)as num ,avg(sal) as avgsal from emp where deptno is not null
 group by deptno) t
where d.deptno=t.deptno
(2)相关子查询  :()里子查询不能独立运行，先执行外查询，再执行子查询

--查询本部门最高工资的员工
--(1)查询10号最门的员工的最高工资  (相当于查询10号部门薪水为5000的员工信息)
select * from emp where deptno=10 and  sal=(select max(sal) from emp where deptno=10);
--(2)查询20号最门的员工的最高工资
select * from emp where deptno=20 and  sal=(select max(sal) from emp where deptno=20);
--(3)查询300号最门的员工的最高工资
select * from emp where deptno=30 and  sal=(select max(sal) from emp where deptno=30);
--相关子查询
select * from emp e1 where sal=(select max(sal) from emp e2 where e1.deptno=e2.deptno);
查询本部门工资高于平均工资的那些员工
select * from emp where deptno=10 and sal>(select avg(sal) from emp where deptno=10);
select * from emp where deptno=20 and sal>(select avg(sal) from emp where deptno=20);
select * from emp where deptno=30 and sal>(select avg(sal) from emp where deptno=30);
--相关子查询
select * from emp t1 where  sal>(select avg(sal) from emp t2 where t1.deptno=t2.deptno  )
order by deptno;

多行子查询:查询的结果多行单列

   ALL 和子查询返回的所有值比较
   ANY 和子查询返回的任意一个值比较
   IN 等于列表中的任何一个

  经验就是（1）根据关键字 “任何，所有，相同”
               (2) 根据子查询的结果 是否是多行单列
 -- 查询工资低于任何一个“CLERK”的工资的雇员信息。
select sal from emp where job='CLERK';
select * from emp where sal<any(select sal from emp where job='CLERK');
select * from emp where sal<(select max(sal) from emp  where job='CLERK');
-- 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
select sal from emp where job='SALESMAN';
select sal from emp where sal>all(select sal from emp where job='SALESMAN');
SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>(select max(sal) from emp where job='SALESMAN');
 --查询部门20中职务同部门10的雇员一样的雇员信息。
select distinct job from emp where deptno=10;
select * from emp where  deptno=20 and 
job in(select distinct job from emp where deptno=10);
等量替换
select * from emp where deptno=20 and 
( job='MANAGER' OR JOB='PRESIDENT' OR JOB='CLERK');

 

 

-- 求emp表ename中含’A‘或含有‘M’
select * from emp where ename like '%A%'  -- union all包含重复数据
UNION all
select * from emp where ename like '%M%'  --将两个查询语句的结果放一个结果窗口中显示
select * from emp where ename like '%A%'   -- union 不包含重复数据
UNION 
select * from emp where ename like '%M%' 
使用如下语句进行替换呢
select * from emp where ename like'%A%' or ename like '%M%';
--交集
--求emp表ename中即含’A‘又含有‘M’   mysql不支持,oracle支持
select * from emp where ename like '%A%'
intersect
select * from emp where ename like '%M%';
--使用如下语句替代呢
select * from emp where ename like '%A%' and ename like '%M%';
--查询出dept表中哪个部门下没有员工  --mysql不支持
select deptno from dept 
minus
select deptno from emp;
--可以使用如下语句进行替换
select deptno from dept where deptno not in
(select distinct deptno from emp where deptno is not null);

 

oracle的分页  使用子查询

 --查询排名6到10的员工的信息
select e.* from emp e order by sal desc  ;  --伪列 rownum 将本查询作为一张表
select rownum as empid ,t.* from  (select e.* from emp e order by sal desc)t;
--将以上查询作为一张表
select * from (select rownum as empid ,t.* from  (select e.* from emp e order by sal desc)t)temp
 where empid  empid>5 and empid<=10
分页的公式是     每页显示的数据范围为
         ( page-1)*item<范围 <=page*item
 每页显示5条，显示第二页，那么第二页的数据范围为  (2-1)*5<范围<=2*5
每页显示3条，显示第四页,                (4-1)*3<范围 <=4*3;

mysql的分页

select * from emp order by sal desc limit 3;--第一页，每页显示3条
select * from emp order by sal desc limit 4,3;
select * from emp order by sal desc limit 3,3;
-- limit用法如下
-- 第一个参数是指要开始的地方，第二个参数是指每页显示多少条数据
-- 第一页用0表示
select * from emp order by sal desc ;
select * from emp order by sal desc limit 0,4;
select * from emp order by sal desc limit 1,4;