Oracle数据库学习笔记5

select 字段名from 表明where 条件group by 分组字段having 筛选的条件order by 排序--伪表 dual--伪列 rownum,对面原表中的数据select rownum，empno,ename,sal from emporder by sal desc;--分析函数--row_number()over():为有序组中的每一行返回一个--唯一的排序值，序号由order by 子句指定，从1开始select ename,job,deptno,sal,row_number() over(order by sal asc)from emp;--根据每个部门返回排序值select ename,job,deptno,sal,row_number() over(partition by deptno                  order by sal)from emp;--dense_rank:计算一个值在一组有序中的排序值，--排名是从1开始，具有相同值的排名相同，并且--后面的排序是连续的select ename,job,deptno,sal,dense_rank() over(partition by deptno order bysal asc)from emp;--rwoid 对应字段的物理地址select rowid,ename,deptnofrom emp;--子查询(单列)select * from emp;--查询谁的工资比ALLEN的工资高select * from emp where sal >(select sal from emp where ename = 'ALLEN');--销售部有哪些职位select * from dept;--distinct 去重--单值运算符连接,子查询只能有一个结果select distinct job from emp where deptno=(select deptno from dept where dname='SALES');--查询那些员工的薪水比ALLEN高--有问题的select * from emp_temp where sal >(select sal from emp_temp where ename = 'ALLEN');--改进select * from emp_temp where sal >(select max(sal) from emp_temp where ename = 'ALLEN');-- >allselect * from emp_temp where sal >all(select sal fron emp_temp where ename='ALLEN');select * from emp_temp;insert into emp_temp(empno,ename,sal)values(8888,'ALLEN',3200);select * from emp_temp where ename='ALLEN';--查询那些人的薪水比人一个ALLEN的薪水高-->any(大于最小值)select * from emp_temp where sal >any(select sal from emp_temp where ename = 'ALLEN');--in--谁和ALLEN同部门,列出除了ALEEN以外的员工名子select * from emp_temp;update emp_temp set deptno = 20 where empno = 8888;select ename,sal from emp_temp where deptno in(select deptno from emp_temp where ename = 'ALLEN')and ename <> 'ALLEN'--子查询(多列)--查询每个部门薪水最高人的名子和薪水和部门号select ename,sal,deptno from empwhere(deptno,sal) in(select deptno,max(sal) from empgroup by deptno);--查询哪些部门的人数比20部门的少？select deptno,count(*) from empgroup by deptnohaving count(*) <(select count(*) from empwhere deptno = 20);--那些员工的薪水比本部门的平均薪水select ename,sal,deptnofrom emp awhere sal <(select avg(nvl(sal,0)) from empwhere deptno=a.deptno);--exists: 存在子查询,关联子查询--那些人是其他人的经理select * from emp;select ename from emp awhere exists(select 1 from emp where mgr = a.empno);--1是不管结果，看存不存在--那些部门没有员工select * from dept;select deptno,dname from dept dwhere not exists--两个表相连的部门(select 1 from emp where deptno = d.deptno);