[Oracle]查询操作

Oracle学习笔记（查询操作）

一、一般操作

l        显示操作时间的命令：

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

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

可以大大提高查询速度。

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

SQL> --首先新建一张表SQL> create table users(id number(10),username varchar2(20));Table createdExecuted in 0.437 seconds--插入一条数据时间是0秒SQL> insert into users values(1,'woshihaogege');1 row insertedExecuted in 0 seconds--插入字表记录的时间大于10秒了SQL> insert into users (id,username) select *from users;1 row insertedExecuted in 0.016 seconds--插入2行记录还是0秒SQL> insert into users (id,username) select *from users;2 rows insertedExecuted in 0 seconds--插入1万多行的时候就需要大于10秒了SQL> insert into users (id,username) select *from users;16384 rows insertedExecuted in 0.063 seconds--查询所有列的时候512 rows selectedExecuted in 4.36 seconds--查询一个列的时候512 rows selectedExecuted 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          19500WARD           15500JONES     MARTIN         16400BLAKE     CLARK     SCOTT     KING      TURNER         18000ADAMS     JAMES     FORD      MILLER     14 rows selected

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

         

                         SQL> select ename as姓名,sal*12+nvl(comm,0) as年工资 from emp; 姓名             年工资---------- ----------SMITH           9600ALLEN          19500WARD           15500JONES          35700MARTIN         16400BLAKE          34200CLARK          29400SCOTT          36000KING           60000TURNER         18000ADAMS          13200JAMES          11400FORD           36000MILLER         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