Oracle基础查询（1）

---
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';