Oracle查询优化-01单表查询

小小工匠

发布于 2021-08-16 15:09:17

1.1K0

发布于 2021-08-16 15:09:17

文章被收录于专栏：小工匠聊架构

概述

目标表：

SQL> desc emp;
Name Type    Nullable Default Comments 
-------- ------------ -------- ------- -------- 
EMPNO NUMBER(4)                           
ENAME VARCHAR2(10) Y                        
JOB  VARCHAR2(9) Y                        
MGR NUMBER(4) Y                        
HIREDATE DATE   Y                        
SAL  NUMBER(7,2) Y                        
COMM NUMBER(7,2) Y                        
DEPTNO NUMBER(2) Y                        

SQL>

1.1 查询表中所有的行与列

select * from emp;

1.2 从表中检索部分行

只需要加过滤条件即可

select * from emp a where a.ename='ALLEN';

1.3 查找空值

null值 “=”判断，需用is null判断

select * from emp where comm is null;

null值也不支持加减乘除大小比较相等比较，否则只能为空。对于其他函数，在使用时最好测试一下null值时会返回什么结果。

1.4 将空值转换为实际值

select coalesce(comm,0) from emp;

有人会问，为什么不是nvl 而是 coalesce呢？因为coalesce更好用。

看下面这个例子，返回多个值中第一个不为空的值。

CREATE OR REPLACE VIEW xgj AS
SELECT NULL AS C1, NULL AS C2,    1 AS C3, NULL AS C4,    2 AS C5, NULL AS C6 FROM DUAL 
UNION ALL
SELECT NULL AS C1, NULL AS C2, NULL AS C3,    3 AS C4, NULL AS C5,    2 AS C6 FROM DUAL;

SQL> select * from xgj;

C1 C2         C3         C4         C5         C6
-- -- ---------- ---------- ---------- ----------
               1                     2 
                          3                     2

SQL>

SQL> select coalesce(c1,c2,c3,c4,c5,c6) from xgj;

COALESCE(C1,C2,C3,C4,C5,C6)
---------------------------
                          1
                          3

SQL>

可以看到对于nvl来说，coalesce支持多个参数，能很方便的返回第一个不为空的值，如果上面的语句改用nvl.则需要套用很多层

SQL> select nvl(nvl(nvl(nvl(nvl(c1, c2), c3), c4), c5), c6)  from xgj;

NVL(NVL(NVL(NVL(NVL(C1,C2),C3)
----------------------------------------
1
3

SQL>

1.5 查找满足多个条件的行

对于简单的查询，操作起来比较简单，那么复杂一点儿的呢？比如，查询部门号10中的所有员工、所有得到提成的员工、以及部门20中工资不超过2000的员工。

这是三个条件的组合，符合上述任一一条即可。

注意：对于多个条件的组合，要使用括号，这样在更改维护语句时可以不必再考虑优先级问题，而且可以很容易的借助龚总工具找到组合条件的起始位置

select *
  from emp e
 where (e.deptno = 10 
       or e.comm is not null 
       or (e.sal > 2000 and e.deptno = 20));

1.6 从表中检索部分列

select empno ,ename from emp a ;

1.7 为列取有意义的名称

不是每个人都能看懂那些简写字母是什么意思，所有在必要的时候应该给列取个别名。

在as 后面跟别名，也可以不要as 直接在列名后跟别名。

SQL> select empno as 工号  ,ename   姓名 from emp a ;

   工号 姓名
----- ----------
 7369 SMITH
 ...........

1.8 在 WHERE 子句中引用取别名的列

写报表时，经常会加上各种条件，引用别名时，千万别忘了嵌套一层，因为这个别名是在select之后才有效的.

比如：

SQL> select *
  2    from (select ename 姓名, sal as 工资 from emp a)
  3   where 工资 < 1000;

姓名           工资
---------- ---------
SMITH    800.00
JAMES    950.00

SQL>

如果不嵌套，提示标识符无效

SQL> select ename 姓名, sal as 工资 from emp a   where 工资 < 1000;

select ename 姓名, sal as 工资 from emp a   where 工资 < 1000

ORA-00904: "工资": invalid identifier

SQL>

1.9 拼接列 “||”

我们可以使用字符串连接符“||”将各个列拼在一起。

SQL> select  ename || '的工资是' || sal   as msg  from emp ;

MSG
--------------------------------------------------------------
SMITH的工资是800
ALLEN的工资是1600
......

当然了对于拼接列，我们还可以如下使用

使用字符串连接符用SQL生成SQL

select 'truncate table ' || owner || '.' || table_name || '; ' from all_tables  ;

1.10 在 SELECT 语句中使用条件逻辑 case when

SQL> select ename,
       job,
       case
         when job = 'ANALYST' then
          '分析员'
         when job = 'CLERK' then
          '服务员'
         when job = 'MANAGER' then
          '经理'
         when job = 'PRESIDENT' then
          '主席'
         else
          '其他'
       end as 职位
  from emp;


ENAME JOB   职位
---------- --------- ---------
SMITH CLERK 服务员
ALLEN SALESMAN 其他
WARD SALESMAN 其他
JONES MANAGER 经理
MARTIN SALESMAN 其他
BLAKE MANAGER 经理
CLARK MANAGER 经理
SCOTT ANALYST 分析员
KING  PRESIDENT 主席
TURNER SALESMAN 其他
ADAMS CLERK 服务员
JAMES CLERK 服务员
FORD ANALYST 分析员
MILLER CLERK 服务员

14 rows selected

SQL>

1.11 限制返回的行数

在查询时，并不要求返回所有的数据，比如进行抽查的时候会要求只返回两条数据。

我们可以使用伪列rownum来过滤，rownum依次对返回的每一条数据做一个标识。

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

EMPNO ENAME JOB     MGR HIREDATE       SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH CLERK  7902 1980-12-17    800.00               20
 7499 ALLEN SALESMAN  7698 1981-02-20   1600.00    300.00     30

SQL>

如果只想取第二条呢？

SQL> select * from emp where rownum =2 ;

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

SQL>

显然是不行的。因为rownum是依次对数据做标识的，所以要先把所有的数据取出来，才能确认第二条记录。

我们可以这样

SQL> select * from (select rownum  as rn,a.* from emp a where rownum <=2) where rn=2;

        RN EMPNO ENAME JOB     MGR HIREDATE       SAL      COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
         2  7499 ALLEN SALESMAN  7698 1981-02-20   1600.00    300.00     30

SQL>

1.12 从表中随机返回 n 条记录

我们可以先用dbms_random来对数据先进行数据排序，然后取其中三列。

select *
  from (select ename, job, sal, comm from emp order by dbms_random.value())
 where rownum <= 3;

1.13 模糊查询

数据：

CREATE OR REPLACE VIEW xgj AS
SELECT 'ABCEDF' AS vname FROM dual
UNION ALL
SELECT '_BCEFG' AS vname FROM dual
UNION ALL
SELECT '_BCEDF' AS vname FROM dual
UNION ALL
SELECT '_\BCEDF' AS vname FROM dual
UNION ALL
SELECT 'XYCEG' AS vname FROM dual;

SQL> select * from xgj;

VNAME
-------
ABCEDF
_BCEFG
_BCEDF
_\BCEDF
XYCEG

SQL>

查出vname中包含ＣＥＤ的　

SQL> select * from xgj where vname like '%CED%';

VNAME
-------
ABCEDF
_BCEDF
_\BCEDF

SQL>

查出vname中包含“_BCE”的

SQL> select * from xgj where vname like '_BCE%';

VNAME
-------
ABCEDF
_BCEFG
_BCEDF

SQL>

发现突然多了一个 ABCDEF ，因为在like子句中有个两个通配符

% 代替一个或者多个字符
_ 代替一个字符

在这里“_”被当成通配符了，那怎么办呢？我们可以使用转义字符。

SQL>  select * from xgj where vname like '\_BCE%' escape '\';

VNAME
-------
_BCEFG
_BCEDF

SQL>

ESCAPE 把‘\’标识作为转义字符，而 ‘\’把‘_’转义为字符，而非其愿义（通配符）。

我们注意到我们的数据中有一列的值为 _\BCEDF ，那么加了ESCAPE ‘\’ 后怎么返回呢？

SQL> select * from xgj where vname like '_\BCE%' escape '\';

select * from xgj where vname like '_\BCE%' escape '\'

ORA-01424: missing or illegal character following the escape character

双写转义字符即可

SQL> select * from xgj where vname like '_\\BCE%' escape '\';

VNAME
-------
_\BCEDF

SQL>

对于字符串中包含’%’ 的也一样的方式处理

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2017/01/31 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

编程算法

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

编程算法

登录后参与评论

0 条评论

热度