[Oracle]查询操作

Oracle学习笔记（查询操作）

一、一般操作

l        显示操作时间的命令：

                     SQL> set timing on; --会显示执行命令所需消耗的时间 

l        使用选择的语句的时候最好不要用*from，而是选择列查询，

可以大大提高查询速度。

l        大数据量表的查询速度测试小方法

SQL> --首先新建一张表
SQL> create table users(id number(10),username varchar2(20));
Table created
Executed in 0.437 seconds
--插入一条数据时间是0秒
SQL> insert into users values(1,'woshihaogege');
1 row inserted
Executed in 0 seconds
--插入字表记录的时间大于10秒了
SQL> insert into users (id,username) select *from users;
1 row inserted
Executed in 0.016 seconds
--插入2行记录还是0秒
SQL> insert into users (id,username) select *from users;
2 rows inserted
Executed in 0 seconds
--插入1万多行的时候就需要大于10秒了
SQL> insert into users (id,username) select *from users;
16384 rows inserted
Executed in 0.063 seconds
--查询所有列的时候
512 rows selected
Executed in 4.36 seconds
--查询一个列的时候
512 rows selected
Executed in 4.61 seconds

l        SQL> --如果有相同的不显示的命令，要有关键字distinct

SQL> selectdistinct deptnp,job from emp;

二、问题解决以scott用户的emp表

1.        SQL> --如何显示所有员工一年的工资

SQL> select ename as姓名,sal*12 as年工资 from emp;

    ？如何处理null值在计算中出现使整个结果变为空的问题

    错误的结果：

                

SQL> select ename as姓名,sal*12+comm as年工资 from emp;
 
姓名             年工资
---------- ----------
SMITH     
ALLEN          19500
WARD           15500
JONES     
MARTIN         16400
BLAKE     
CLARK     
SCOTT     
KING      
TURNER         18000
ADAMS     
JAMES     
FORD      
MILLER    
 
14 rows selected

正确的结果：使用nvl函数解决

         

                         SQL> select ename as姓名,sal*12+nvl(comm,0) as年工资 from emp;
 
姓名             年工资
---------- ----------
SMITH           9600
ALLEN          19500
WARD           15500
JONES          35700
MARTIN         16400
BLAKE          34200
CLARK          29400
SCOTT          36000
KING           60000
TURNER         18000
ADAMS          13200
JAMES          11400
FORD           36000
MILLER         15600
 
14 rows selected<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">      </span>

         2.        SQL> --如何显示工资高于3000员工的工资

                       SQL> select ename ,sal from emp where sal>3000;

3        模糊查询问题：使用关键字like

l        %：表示任意0到多个字符

l        _：表示任意单个字符

？如何显示首字符为S的员工姓名和工资

SQL> select ename,sal from emp where ename like 'S%';

？如何显示第三个字母为O的员工的信息

SQL> select ename,sal from emp where enamelike '__O%';

4        where语句中使用in

？如何显示部门编号为10，20这些人的工资

SQL> select ename,sal,deptno from emp where deptno in (10,20);

5. 使用逻辑操作符号

    ？查询工资高于500或者岗位是manager的雇员，同时还要满足他们的姓名的首字母是大写的J

    select sal,job,ename from emp where (sal>500 or job='MANAGER') and ename like 'J%';

6．使用orderby语句

    ？如何按照工资高低的顺序显示员工的信息

    SQL> select *from emp order by sal;（desc）

    ？按照部门号升序而员工工资降序的顺序排列

    SQL> select *from emp order by deptno,sal desc;

三、复杂查询

           1.分组函数的使用max,min,avg,count,sum，一列中如果有分组函数就必须都是分组函数

                       ? SQL> --查询员工的最高工资和最低工资

SQL> selectmax(sal),min(sal) from emp;

SQL> select ename,sal from emp wheresal=(select max(sal) from emp);

                       ？SQL> --显示工资高于平均工资的员工的信息；

SQL> select *from emp where sal>(select avg(sal) from emp);

   2.    groupby和having子句

groupby用于对查询的结果进行查询统计，要显示的的字段中必须有分组函数，

错误例子：SQL> select avg(sal),max(sal),deptno,job from emp group by deptno;

having用于限制分组显示结果

？显示不同部门的平均工资和最高工资

SQL> selectavg(sal),max(sal),deptnofrom emp group by deptno;

？SQL> --每个部门的不同岗位的平均工资和最高工资

SQL> select avg(sal),max(sal),deptno,job from emp group by deptno,job;

？SQL> --显示平均工资低于2000的部门号和平均工资

SQL> select avg(sal),deptno from emp group by deptnohaving avg(sal)>2000;

    3.     注意事项：

顺序问题：group by、having、order by

在选择列中如果有列，表达式，和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则就会出错。

 本文出自 “orangleliu笔记本” 博客，请务必保留此出处http://blog.csdn.net/orangleliu/article/details/38309301