oracle基础(三)--查询

1.查看表结构
sql> desc dept;
查询所有列
select * from dept;
查询指定列
select ename, sal,job,deptno from emp;
如何取消重复行
select distinct deptno,job from emp;

2.显示sql执行时间
set timing on;

3.null字段参与验算的问题

null的处理 使用nvl函数来处理

4.如何连接字符串(||)
select ename || 'is a ’ || job from emp;

5.时间的问题

6.如何使用like操作符
%: 表示任意0到多个字符
_: 表示任意单个字符

7.显示order by 多个字段

8.查询
select ename,sal from emp where sal=(select max(sal) from emp);
select avg(sal),max(sal),deptno,job from emp group by deptno,job;

9.数据分组的总结
分组函数只能出现在选择列表,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;
这里deptno就一定要出现在group by中

10. 在多行子查询中使用all操作符
    请思考:如何显示工资比部门30的所有员工的工资高的员工的i姓名,工资和部门号
    select ename,sal,dept from emp where sal>all( select sal from emp where deptno=30 );
    请思考:如何显示工资比部门30的任意一个员工的工资高的员工的姓名,工资和部门号
    select ename,sal,dept from emp where sal>any(select sal from emp where deptno=30)

    多列子查询:
    单行子查询是指子查询只返回单列,单行数据,多行子查询是返回单列多行数据,都是针对单列而言的,而多列子查询则是指查询返回多个列数据的子查询语句
    select * from emp where (job,deptno)=(select job,deptno from emp where ename=‘SMITH’)

11. 在from子句中使用子查询
    请思考:如何显示高于自己部门平均工资的员工的信息
    11.1 查询出各个部门的平均工资和部门号
    select deptno,avg(sal) mysal from emp group by deptno;
    11.2 把上面的查询看做一张 子表
    select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2, (select deptno,avg (sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal

12.分页查询
12.1 根据ROWID来分
select * from t_xiaoxi where rowid in (select rid from ( select rownum rn,rid from (select rowid rid,cid from t_xiaoxi order by cid desc) where rownum<1000 ) where rn >9980 ) order by cid desc;
执行时间0.0.3秒

12.2按分析函数来分
select * from (select t.*, row_number() over (order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980
执行时间1.01秒

12.3按ROWNUM来分
select * from (select t.*,rownum rn from (select * from t_xiaoxi order by cid desc) t wehre rownum<1000) where rn>9980;
执行时间0.1秒
其中t_xiaoxi为表名称,cid为表得关键字段,取按CID降序排序后得第9981-9999条记录,t_xiaoxi表有70000多条记录

13.用查询结果创建新表
这个命令式一种快捷的建表方法
create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

14.合并查询
有时在实际应用中,为了合并多个select语句的结果,可以使用集合操作符号union, union all,interset,minus
14.1 union
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中重复行。
select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp
where job=‘manager’;
14.2 union all
该操作赋于union相似,但是它不会取消重复行,而且不会排序
select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job=‘manager’;
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中重复行
14.3 合并查询
minus
使用该操作符用于取得两个结果集的差集,它只会显示存在第一个集合中,而不存在第二个集合中的数据。
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job=‘manager’;