Oracle- 表查询

这些都比较基础,还是重温一下。

一、简单的查询语句
1、查询表结构
sql>desc dept; (使用PL/SQL Developer查询时,必需在命令窗口才能使用该语句)

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

 

转载自:http://www.cnblogs.com/BeautyOfCode/archive/2010/09/15/1826576.html

 

Oracle- 表查询

上一篇:Using sql azure for Elmah


下一篇:sqlserver自定义函数与存储过程的区别 实例详解