Oracle表查询

使用scott用户中存在的emp、dept表等做演示

一、单表查询

查看表结构：desc dept;

查看所有列：select * from dept；

查询指定列：select ename,sal,job,deptno from emp;

如何取消重复行：select disrinct deptno,job from emp;

1.1 基本查询

①查询SMITH的薪水，工作，部门号
select deptno,job,sal from emp where eanme=’SMITH’;


②显示每个雇员的年工资
select ename,sal*12 from emp;              不合理
select ename,sal*12+comm*12 from emp;      不合理
    

③修改列的名字，起别名
select ename ‘姓名’, sal*12 ‘年收入’ from emp;


④如何处理null值：nvl函数处理
select ename,sal*12+nvl(comm,0)*12 ‘年收入’ from emp;   每个雇员的年工资


⑤如何连接字符串（||）
select ename || ‘is a’ || job from emp;


⑥工资高于3000元的员工
select ename,sal from emp where sal>3000;


1982.1.1后入职的员工
select ename,hiredate from emp where hiredate>’1-1月-1982’


显示工资在2000-3000的员工情况
Select ename,sal from emp where sal>=2000 and sal<=3000;


⑦使用like操作
%：表示任意0到多个字符
_：表示任意单个字符


查询首字符为S的员工的姓名和工资
select ename,sal from emp where ename like ‘S%’;


查询第三个字符为大写O的所有的员工姓名和工资
select ename,sal from emp where ename like ‘__O%’;


⑧where中使用in
显示雇员的编号为123,345,800的雇员情况
select * from emp where empno in (7844,345,800);


⑨使用is null的操作
显示没有上级的雇员的情况
select * from emp where mgr is null;


使用逻辑操作符号
查询工资高于500或是岗位为MANAGER的雇员，用时还要满足他们的姓名手写字母为大写的J
select * from emp where (sal>500 or job=’MANAGER’) and ename like ‘J%’;


使用order by
按照工资高低显示雇员的信息
select * from emp order by sal;       从低到高
select * from emp order by sal desc;   从高到低


按照部门号升序而雇员的工资降序排列显示雇员的信息
Select * from emp order by deptno, sal desc;


使用列的别名排序
按照年薪排序
select ename,(sal+nvl(comm,0))*12 “年薪” from emp order by “年薪” asc;   从低到高

1.2 复杂查询

①数据分组：max、min、avg、sum、count

所有员工中最高工资和最低工资
select ename,sal from emp where sal=(select max(sal) from emp);
select ename,sal from emp where sal=(select min(sal) from emp);
    

工资最高的员工的名字，工作岗位
select ename job from emp where sal=(select max(sal) from emp);


工资高于平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp);



②group by与having
group by用于对查询的结果分组统计
having用于限制分组显示结果


显示每个部门的平均工资和最高工资
select avg(sal),max(sal),deptno from emp group by deptno;


显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),deptno,job from emp group by deptno,job;


显示平均工资低于2000的部门号和它的平均工资
select deptno, avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;



③总结：分组函数只能出现在选择列表、having、order by中
如果在select语句中同时包含有group by、having、order by 那么它们的顺序是group by、having、order by
在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by中，否则就会出错
如：select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;

二、多表查询

2.1 基本查询

①显示雇员名，雇员工资及所在部门的名字(笛卡尔积)
select a1.name,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;
注：多表查询的条件是 至少不能少于表的个数-1


②显示部门号为10的部门名、员工名和工资
Select a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10;


③显示各个员工的姓名、工资及工资的级别
Select a1.ename,a1.sal,a2.grade from dept a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;


④显示雇员名，雇员工资及所在部门的名字，并按部门排序
select a1.ename,a2.dname,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;


⑤自连接：在同一张表中的连接查询
显示FORD员工的上级领导的姓名
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename=’FORD’;

2.2 复杂查询

①子查询：嵌入在其他sql语句中的select语句
单行子查询：只返回一行数据的子查询语句
显示与SMITH同一部门的所有员工：先查询出SMITH的部门号
select * from emp where deptno=( select deptno from emp where ename=‘SMITH’
)


多行子查询：返回多行数据的子查询
显示和部门10的工作相同的雇员的名字、岗位、工资和部门号
select * from emp where job in (select distinct job from emp where 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 sal from emp where deptno=30);


在from子句中使用子查询：显示高于自己部门平均工资的员工信息
解决：首先查询出各个部门的平均工资和部门号
select deptno,avg(sal) mysql from emp group by deptno;
把上面的查询看做是一张子表
select a2.ename,a2.sal,a2.deptno,a1.mysql from emp a2,(select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
注：当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫作内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名




②分页查询
oracle分页一共有三种方式：
rownum分页
select a1.*,rownum rn from (select * from emp) a1;



③用查询结果创建新表(快捷建表)
create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;



④合并查询
在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union、union all、intersect、minus

union：用于取得两个结果集的并集，当使用该操作符是，会自动去掉结果集中重复行
如：select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job=’MANAGER’;


union all：操作与union相似，但是它不会取消重复行，而且不会排序
select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job=’MANAGER’;


intersect：用于取得两个结果的交集
select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job=’MANAGER’;


minus：用于取得两个结果集的差集，它只会显示存在第一个集合中，而不会存在第二个集合中的数据
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job=’MANAGER’;