【sql查询与优化】1.单表查询-阿里云开发者社区

开发者社区> 数据库> 正文

【sql查询与优化】1.单表查询

简介:
注:以下所有sql案例均取自"oracle查询优化改写技巧与案例"丛书。

EMP表的详细:


1.查询表中所有的行与列

查询所有信息,
SQL> select * from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE       SAL        COMM       DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------


      1110 张三                 主管                     3322 12-3月 -14      5200                    20


      1111 李四                 销售                     3321 03-11月-15      3400        500         30


      1112 王五                 销售                     3321 25-4月 -12      4400        800         30


      1113 赵二                 后勤                     3320 30-5月 -11      3450                    40


      1114 李磊磊               会计                     3319 22-12月-15      2500                    50


      1115 张少丽               销售                     3321 11-3月 -16      3400       1400         30


      1116 林建国               主管                     3322 22-1月 -16      5700                    20


      1117 马富邦               后勤                     3320 22-7月 -13      2800                    40


      1118 沈倩                 会计                     3319 06-5月 -10      2100                    50
已选择9行。

2.从表中检索部分行
查看公司有多少销售人员
SQL> select * from emp where job='会计';

     EMPNO ENAME                JOB                       MGR HIREDATE       SAL        COMM       DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      1114 李磊磊               会计                     3319 22-12月-15     2500                    50
  
      1118 沈倩                 会计                     3319 06-5月 -10     2100                    50


3.查找空值
SQL> select * from emp where comm is null;

    EMPNO ENAME                JOB                       MGR HIREDATE        SAL        COMM       DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      1110 张三                 主管                     3322 12-3月 -14     5200                    20


      1113 赵二                 后勤                     3320 30-5月 -11     3450                    40


      1114 李磊磊               会计                     3319 22-12月-15     2500                    50


      1116 林建国               主管                     3322 22-1月 -16     5700                    20


      1117 马富邦               后勤                     3320 22-7月 -13     2800                    40


      1118 沈倩                 会计                     3319 06-5月 -10     2100                    50

已选择6行。

4.将空值转换为实际值
SQL> select coalesce(comm,0) from emp;
COALESCE(COMM,0)
----------------
               0
        5.0E+002
        8.0E+002
               0
               0
        1.4E+003
               0
               0
               0

已选择9行。

注:
COALESCE (expression_1, expression_2, ...,expression_n)依次参考各参数表达式,遇到非null值即停止并返回该值。如果所有的表达式都是空值,最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。
还有一个类似的函数NVL(E1, E2):
NVL(E1, E2)的功能为:如果E1为NULL,则函数返回E2,但此函数有一定局限,所以就有了NVL2函数。
拓展:NVL2函数:Oracle/PLSQL中的一个函数,Oracle在NVL函数的功能上扩展,提供了NVL2函数。NVL2(E1, E2, E3)的功能为:如果E1为NULL,则函数返回E3,若E1不为null,则返回E2。
比较:相对于nvl来说,coalesce支持多个参数,能很方便地返回第一个不为空的值。如果上面使用nvl,就要嵌套很多层。

5.查找满足多个条件的行
我们要查询部门20中的所有员工、所有得到提成的员工,以及部门40中工资低于3000元的员工
我们把这三个条件整理成逻辑表达式的形式:(部门20中的员工 OR 所有得到提成的员工 OR (工资<3000 and 部门号=40))
SQL> select * 
         from emp 
         where (deptno=20 
                or comm is not null /*千万不要写成comm <> null*/
                or (sal < 3000 and deptno = 40));

    EMPNO ENAME                JOB                       MGR HIREDATE        SAL        COMM       DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      1110 张三                 主管                     3322 12-3月 -14      5200                    20


      1111 李四                 销售                     3321 03-11月-15      3400        500         30


      1112 王五                 销售                     3321 25-4月 -12      4400        800         30


      1115 张少丽               销售                     3321 11-3月 -16      3400       1400         30


      1116 林建国               主管                     3322 22-1月 -16      5700                    20


      1117 马富邦               后勤                     3320 22-7月 -13      2800                    40


6.从表中检索部分列
明确指定查询哪些列即可。
SQL> select empno,ename,hiredate,sal from emp where deptno = 30;
     EMPNO ENAME                HIREDATE              SAL
---------- -------------------- -------------- ----------
      1111 李四                 03-11月-15           3400
      1112 王五                 25-4月 -12           4400
      1115 张少丽               11-3月 -16           3400

7.为列取有意义的名称
可以给列取别名
SQL> select ename as 姓名,deptno as 部门编号,sal as 工资,comm as 提成 from emp;
姓名                   部门编号       工资       提成
-------------------- ---------- ---------- ----------
张三                         20       5200
李四                         30       3400        500
王五                         30       4400        800
赵二                         40       3450
李磊磊                       50       2500
张少丽                       30       3400       1400
林建国                       20       5700
马富邦                       40       2800
沈倩                         50       2100

已选择9行。

8.在Where子句中引用取别名的列
下例是寻找工资低于3000元的人
SQL> select * from (select sal as 工资,comm as 提成 from emp) where 工资 < 3000;
      工资       提成
---------- ----------
      2500
      2800
      2100

9.拼接列
用字符串连接符“||”来把各列拼在一起
SQL> select ename || '的工作是' || job as msg from emp where deptno = 20;
MSG
--------------------------------------------------------------
张三的工作是主管
林建国的工作是主管


10.在Select语句中使用条件逻辑
如:当员工信息小于或等于2500元时,就返回消息“过低”,大于或等于4000元时,就返回消息“过高”,如果在两者之间,就返回“OK”。
SQL> select ename,sal,
     case
       when sal <= 2500 then '过低'
       when sal >= 4000 then '过高'
     else 'OK'
     end as status
     from emp;

ENAME                       SAL STATUS
-------------------- ---------- ------------
张三                       5200 过高
李四                       3400 OK
王五                       4400 过高
赵二                       3450 OK
李磊磊                     2500 过低
张少丽                     3400 OK
林建国                     5700 过高
马富邦                     2800 OK
沈倩                       2100 过低

已选择9行。

这种方式还常用在报表中,比如要按工资分档次统计人数
SQL> select 档次,count(*) as 人数
     from (select (case
                      when sal <= 2500 then '1000-2500'
                      when sal <= 3000 then '2500-3000'
                      when sal <= 4000 then '3000-4000'
                      when sal <= 5000 then '4000-5000'
                   else '好高'
                   end) as 档次,
                   ename,sal
                from emp)
     group by 档次
     order by 1;

档次                     人数
------------------ ----------
1000-2500                   2
2500-3000                   1
3000-4000                   3
4000-5000                   1
好高                        2

11.限制返回的行数
并不要求每次都要返回所有的数据,比如,进行抽查的时候会只要求返回两条数据。
我们可以用伪列rownum来过滤,rownum一次对返回的每一条数据做一个标识。

SQL> select * from emp where rownum <= 2;

    EMPNO ENAME                JOB                       MGR HIREDATE        SAL        COMM       DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      1110 张三                 主管                     3322 12-3月 -14      5200                    20


      1111 李四                 销售                     3321 03-11月-15      3400        500         30

12.从表中随机返回n条记录
我们先用dbms_random来对数据进行随机排序,然后取其中三行。
SQL> select empno,ename
         from (select empno,ename from emp order by dbms_random.value())
       where rownum <= 3;
     EMPNO ENAME
---------- --------------------
      1118 沈倩
      1110 张三
      1111 李四

SQL> select empno,ename
              from (select empno,ename from emp order by dbms_random.value())
            where rownum <= 3;

     EMPNO ENAME
---------- --------------------
      1115 张少丽
      1118 沈倩
      1117 马富邦

可以看到两次取的随机值不同
注意:
正确的做法是:先随机排序,再取数据
错误的做法是:先取数据,再随机排序,这样每次执行语句得到的都是相同的结果。

13.模糊查询
先建立如下view:
SQL> create or replace view v as
     select 'ABCDEF' as vname from dual
     union all
     select '_BCDEF' as vname from dual
     union all
     select '_BCEDF' as vname from dual
     union all
     select '_\BCEFG' as vname from dual
     union all
    select 'XYCEG' as vname from dual;

创建之后的表为:
SQL> select * from v;
VNAME
--------------
ABCDEF
_BCDEF
_BCEDF
_\BCEFG
XYCEG

要求一:查出vname中包含字符串"CDE"的
SQL> select * from v where vname like '%CDE%';

VNAME
--------------
ABCDEF
_BCDEF

要求二:查出vname中包含字符串“_BCE”的
SQL> select * from v where vname like '%_BCE%';

VNAME
--------------
_BCEDF
_\BCEFG

发现多了一个_\BCEFG。因为在LIKE子句中有两个通配符:“%”(替代一个或多个字符)、“_”被当做通配符了,怎么办呢?我们可以用转义字符:
SQL> select * from v where vname like '\_BCE%' escape '\';

VNAME
--------------
_BCEDF
其中escape 把‘\’标识为转义字符,而‘\’把‘_’转义为字符,而非其原意(通配符)。

想查到_\BCE可以用双转义:
SQL> select * from v where vname like '_\\BCE%' escape '\';

VNAME
--------------
_\BCEFG

对于字符串中包含“%”的情况,与上面的处理方法一样。

注:
(1)union all
SQL UNION 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
SQL UNION 语法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

(2)Dual
在Oracle数据库内有一种特殊的表Dual。Dual表是Oracle中的一个实际存在的表,任何用户均可读取,常用在没有目标表的Select中。Dual表由Oracle连同数据字典一同创建,所有的用户都可以用名称DUAL访问该表。这个表里只有一列DUMMY,该列定义为VARCHAR2(1)类型,有一行值X。从DUAL表选择数据常被用来通过SELECT语句计算常数表达式,由于DUAL只有一行数据,所以常数只返回一次。
转载请注明出处:http://blog.csdn.net/acmman/article/details/51026148

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章