Oracle表查询

一、简单的查询语句
1、查询表结构
sql>desc dept;

2、查询所有列
sql>select * from dept;

3、查询指定列
sql>select ename,sal,job,deptno from emp;

4、如何取消重复行
sql>select distinct deptno,job from emp;

5、使用算术表达式
实例：显示每个雇员的年工资
sql>select ename, sal * 12 "年工资" from emp;

6、使用列的别名
sql>select ename "姓名", sal*12 as "年收入" from emp;

7、如何处理null值
说明：对于null值，我们可以使用nvl函数来处理。
实例：显示每个雇员的年工资（包括奖金）
sql>select ename, sal*12+nvl(comm,0)*12 "年收入" from emp;

8、如何连接字符串
sql>select ename || 'is a ' || job from emp;

9、使用where子句
实例：如何显示工资高于3000的员工
sql>select ename,sal from emp where sal>3000;
实例：如何查找1982.1.1后入职的员工
sql>select ename,hiredate from emp where hiredate>'1-1月-1982';
实例：如何显示工资在2000到2500的员工情况
sql>select ename,sal from emp where sal between 2000 and 2500;

10、使用like操作符
（1）%：表示任意0到多个字符。
（2）_：表示任意单个字符。
实例：如何显示首个字符为S的员工姓名和工资
sql>select ename,sal from emp where ename like 'S%';
实例：如何显示第三个字符为大写O的所有员工的姓名和工资
sql>select ename,sal from emp where ename like '__O%';

11、在where条件中使用in
实例：如何显示empno为123，345，800的雇员情况
sql>select * from emp where empno in (123,345,800);

12、使用is null的操作符
实例：如何显示没有上级的雇员的情况
sql>select * from emp where mgr is null;

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

14、使用order by子句
实例：如何按照工资中低到高的顺序显示雇员的信息
sql>select * from emp order by sal;
实例：按照部分号升序而雇员的工资降序排列
sql>select * from emp order by deptno,sal desc;

15、使用列的别名排序
sql>select ename,sal*12 "年薪" from emp order by "年薪" desc;
注：别名需要使用双引号括起来。

二、复杂的查询语句
说明：在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较为复杂的select语句。
1、数据分组——max,min,avg,sum,count
实例：如何显示所有员工中最高工资和最低工资
sql>select max(sal),min(sal) from emp;
实例：显示所有员工的平均工资和工资总和
sql>select avg(sal),sum(sal) from emp;
实例：计算共有多少员工
sql>select count(* ) from emp;
实例：请显示工资最高的员工的名字，工作岗位
sql>select ename,job from emp where sal=(select max(sal) from emp);
实例：显示工资高于平均工资的员工
sql>select * from emp where sal>(select avg(sal) from emp);

2、group by和having子句
group by用于对查询的结果分组统计。
having用于限制分组显示结果。
实例：如何显示每个部门的平均工资和最高工资
sql>select avg(sal),max(sal),deptno from emp group by deptno;
实例：显示每个部门的每种岗位的平均工资和最低工资
sql>select avg(sal),min(sal),deptno,job from emp group by deptno,job;
实例：显示平均工资低于2000的部门号和他的平均工资
sql>select avg(sal),deptno from emp group by deptno having avg(sal)>2000;

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

三、多表查询
说明：多表查询是指基于两个或两个以上的表或视图的查询，在实际应用中，查询单个表可能不能满足需求（如显示sales部门位置和其员工的姓名），这种情况下需要使用到（dept表和emp表）。
1、显示雇员名，雇员工资及所在部门的名字
sql>select a1.ename,a1.sal,a2.dname from emp a1 inner join dept a2 on a1.deptno=a2.deptno;

2、如何显示部门号为10号的部门名、员工和工资
sql>select a2.dname,a1.ename,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno and a2.deptno=10;

3、显示各个员工的姓名、工资及其工资的级别
sql>select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;

4、显示雇员名、雇员工资及其所在部门的名称，并按部门排序
sql>select a1.ename,a1.sal,a2.dname from emp a1 inner join dept a2 on a1.deptno=a2.deptno order by a1.deptno;

5、自连接
说明：自连接是指在同一张表的连接查询。
案例：显示FORD的上级领导的姓名
sql>select worker.ename "员工",boss.ename "上级领导" from emp worker,emp boss where worker.mgr=boss.empno and worker.ename='FORD';

四、子查询
概述：子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。
1、单行子查询
说明：单行子查询是指只返回一行数据的子查询语句。
案例：如何显示与SMITH同一部门的所有员工
sql>select * from emp where deptno=(select deptno from emp where ename='SMITH');

2、多行子查询
说明：多行子查询指返回多行数据的子查询。
案例：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
sql>select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10);

3、在多行子查询中使用all操作符
案例：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
sql>select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=30);

4、在多行子查询中使用any操作符
案例：如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号
sql>
sql>select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30);

5、多列子查询
说明：单行子查询是指子查询只返回单列、单行数据。多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句。
案例：如何查询与SMITH的部门和岗位完全相同的所有雇员
sql>select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

6、在from子句中使用子查询
案例：如何显示高于自己部门平均工资的员工的信息
sql>select a1.ename,a1.sal,a1.deptno,a2.avgSal from emp a1,(select deptno,avg(sal) from emp group by deptno) a2 where a1.deptno=a2.deptno and sal>avgSal;
总结：这里需要说明的是当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名。

7、用查询结果创建新表
说明：这个命令是一种快捷的建表方法。
案例：create table myemp(id,ename,sal) as (select empno,ename,sal from emp);

8、使用子查询插入数据
说明：当使用values子句时，一次只能插入一行数据，当使用子查询插入数据时，一条insert语句可以插入大量的数据。当处理行迁移或者装载外部表的数据到数据库时，可以使用子查询来插入数据。
案例：sql>insert into Users select * from emp where deptno=10;

9、使用子查询更新数据
说明：使用Update语句更新数据时，既可以使用表达式或者数值直接修改数据，也可以使用子查询修改数据。
案例：希望更新员工scott的岗位、工资、补助与Smith员工一样
sql>update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';
五、分页查询
1、根据ROWID来分,效率最高
sql>select * from emp where rowid in (select rid from (select rownum rn,rid from (select rowid rid,empno from emp order by empno desc) where rownum<10) where rn>6) order by empno desc;

2、按分析函数来分，效率最差
sql>select * from (select t.*,row_number() over (order by empno desc) rk from emp t) where rk between 6 and 10;

3、按ROWNUM来分,效率次之
sql>select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6;

六、合并查询
概述：有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus。
1、union
说明：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
案例：sql>select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where ename='MANAGER';

2、union all
说明：该操作符用于取得两个结果集的并集。当使用该操作符时，不会自动去掉结果集中重复行。
案例：sql>select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where ename='MANAGER';

3、intersect
说明：使用该操作符用于取得两个结果集的交集。
案例：sql>select ename,sal,job from emp where sal>2500 intersect ename,sal,job,from emp where job='MANAGER';

4、minus
说明：使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据。
案例：sql>select ename,sal,job from emp where sal>2500 minusename,sal,job,from emp where job='MANAGER';