基本oracle语句应用
目录
1. 字符和日期型数据应包含在单引号中
select job,sum(sal) from emp
where job !='SALESMAN'
group by job
having sum(sal)>5000
order by sum(sal) ;
select * from emp
where sal>(select sal from emp where ename='SMITH');
select * from emp
where sal>(select avg(sal) from emp );
2. 子查询
select * from emp
where job=(select job from emp where ename='ALLEN')
and sal=(select sal from emp where ename='ALLEN');
3. 找出和经理一样工资的员工信息（经理不止一个,且工资不同)
select * from emp
where sal in (select sal from emp where job='MANAGER');
4. 上述问题的等价形式
select * from emp
where sal =any(select sal from emp where job='MANAGER');
select sal from emp e right join dept d on e.deptno=e.deptno
5. 返回部门最高的平均工资
select max(avg(sal) )from emp
group by deptno;
select dname,count(*),avg(e.sal)
from dept d left join emp e on d.deptno=e.deptno
group by dname;
6.查询出每个部门的编号、名称、位置、部门人数、平均工资
/*
1.问题：什么时候用多字段分组？(查询的(多个)字段位于非主表时(分组表)时)
2.多字段分组时，groupby后面的字段存在优先级，一般统计函数所在的字段优先；
*/
select d.deptno,dname,count(*),d.loc,avg(e.sal)
from dept d left join emp e on d.deptno=e.deptno
group by d.deptno,dname,loc;
-- 上述问题使用子查询
select deptno,dname,b.avgSal,loc
from dept d,(select deptno dn,avg(sal) as avgSal from emp group by deptno) b
where deptno=b.dn;
7. 查询行号
/*
3.问题：为什么用e.*而不用*？
-- * 已经代表全部，再加rownum时会出现矛盾；所以这里需要特别指定；
*/
select rownum,e.* from emp e;
-- 查询前 5 条记录
select rownum,e.* from emp e
where rownum<6;
-- 查询 6-10 条记录
--select rownum,e.* from emp e
--where rownum between 6 and 10;
-- 上述语句会报错，原因是rownum不能同时使用>和<
-- 改进
select * from
(select rownum rm ,e.* from emp e where rownum<11) b
where b.rm>5;
8. 分页
select * from
(select rownum as rm,e.* from emp e where rownum<to_number('&endNum')) b
where b.rm > to_number('&startNum');
9.要求查询出每一位雇员的姓名、职位、雇员的直接上级领导的姓名
select e1.ename,e1.job,e2.ename
from emp e1 left join emp e2 on e1.mgr=e2.empno ;
select e1.ename,e1.job,e2.ename,d.dname
from emp e1 left join emp e2 on e1.mgr=e2.empno
left join dept d on e1.deptno=d.deptno;
select e1.empno,e1.sal,e1.ename,e1.job,e2.ename,d.dname,d.loc
from emp e1 left join emp e2 on e1.mgr=e2.empno
left join dept d on e1.deptno=d.deptno;
10. 创建子表
create table B as
select * from emp
where sal>=3000;
11.行号练习
-- 先取行号，再排序
select rownum,e.* from emp e order by sal desc;
12.查询每个部门平均薪水的等级 部门编号，部门平均薪水，等级
select *
from salgrade s,(select deptno,avg(sal) as 平均薪水 from emp group by deptno) t
where t.平均薪水>=s.losal and t.平均薪水<=s.hisal
