Oracle查询字段拼接优化,Oracle笔记之查询优化改写-单表查询

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

desc 表名

2.查找空值

select * from 表名

where 列 is

null

null 不能用 '=' 来运算，需要使用 is null判断

is

null 为空

is

not null 不为空

null 不支持 加、减、乘、除、大小比较、相等比较，否则为空；

3.将空值转换为实际值

select coalesce(comm,0)

from emp;

相对于nvl来说，coalesce支持多个参数，能很方便的返回第一个不为空的值。

4.在取where子句中引用取别名的列

select * from

(

select sal as 工资,comm as 提成

from emp

) x where 工资 <

1000

以下写法不正确：

select sal as

工资，comm as 提成 from emp where 工资 < 1000

5.拼接列

select ename || '的工作是'||job

as msg from emp where deptno = 10

拼接列可以用于SQL生成SQL，实际应用：

select 'truncate table'||

owner||'.'||table_name||';' as 清空表 from all_tables where owner =

'SCOTT';

6.在select语句中使用条件逻辑

select

ename,sal,

case when sal <= 2000

then '过低'

when sal >= 4000 then

'过高'

else 'OK'

end as status from emp

where deptno=10;

7.限制返回的行数

伪列rownum过滤返回的行数

select * from emp where

rownum <= 2;

rownum不能直接进行等于查询，如下：

select * from emp where

rownum = 2;

no rows

selected;

因为rownum是依次对数据做标识的，有第一名，才有第二名，以此类推。所以，需要把所有的数据取出来，才能确认第二名。

select * from (select

rownum as sn,emp.* from emp where rownum <= 2) where sn =

2;

8.从表中随机返回n条记录

select empno,ename from

(select empno,ename from emp order by

dbms_random.value())

where rownum <=

3;

dbms_random:

一个可以生成随机数值或者字符串的程序包;

先随机排序，再取数据；

9.模糊查询

其中关于条件，SQL提供了四种匹配模式：

1.

%：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号(%%)表示。

比如

SELECT * FROM [user] WHERE u_name LIKE '%三%'

将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。

另外，如果需要找出u_name中既有“三”又有“猫”的记录，请使用and条件

SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE

'%猫%'

若使用

SELECT * FROM [user] WHERE u_name LIKE '%三%猫%'

虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”。

2.

_： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：

比如

SELECT * FROM [user] WHERE u_name LIKE '_三_'

只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的；

再比如

SELECT * FROM [user] WHERE u_name LIKE '三__';

只找出“三脚猫”这样name为三个字且第一个字是“三”的；

3.

regexp_like 正则表达式函数查询

--查询FieldName中以1开头60结束的记录并且长度是7位

select * from fzq where FieldName like '1____60';

select * from fzq where regexp_like(FieldName,'1....60');

--查询FieldName中以1开头60结束的记录并且长度是7位并且全部是数字的记录。

--使用like就不是很好实现了。

select * from fzq where regexp_like(FieldName,'1[0-9]{4}60');

--

也可以这样实现，使用字符集。

select * from fzq where

regexp_like(FieldName,'1[[:digit:]]{4}60');

--

查询FieldName中不是纯数字的记录

select * from fzq where not

regexp_like(FieldName,'^[[:digit:]]+$');

--

查询FieldName中不包含任何数字的记录。

select * from fzq where

regexp_like(FieldName,'^[^[:digit:]]+$');

--查询以12或者1b开头的记录.不区分大小写。

select * from fzq where regexp_like(FieldName,'^1[2b]','i');

--查询以12或者1b开头的记录.区分大小写。

select * from fzq where regexp_like(FieldName,'^1[2B]');

--

查询数据中包含空白的记录。

select * from fzq where regexp_like(FieldName,'[[:space:]]');

--查询所有包含小写字母或者数字的记录。

select * from fzq where

regexp_like(FieldName,'^([a-z]+|[0-9]+)$');

--查询任何包含标点符号的记录。

select * from fzq where regexp_like(FieldName,'[[:punct:]]');

4.\特殊字符

举例说明：

select * from emp where ename like '_BCE';

在这里'_'被当成通配符了，这里就需要用到转义字符了：

select * from emp where ename like '\_BCE%' ESCAPE '\';

ESCAPE把 '\' 标识为转义字符，而 '\' 把 '_' 转义为字符，而非其原义；

10.给查询结果排序

常用排序写法：

select empno,ename from emp

where deptno = 10 order by empno asc;

除此之外还可写成 "order by 2

asc"，意思是按照第二列排序

select empno,ename from emp

where deptno = 10 order by 2 asc;

用数据来替代列位置只能用于order

by子句中。

11.按多个字段排序

select

empno,deptno,sal,ename,job from emp order by 2 asc,3

desc;

多列排序时，若前面的列有重复值，后面的排序才有用。相当于通过前面的列把数据分成了，然后每组的数据再按后边的列进行排序。

12.按字串排序

select last_name as

名称,

phone_number as

号码,

salary as 工资,

substr(phone_number,-4) as

尾号

from employees where rownum

<= 5 order by 4;

13.translate

字符串替换函数

语法格式：translate(expr,from_string,to_string)

示例如下：

select translate('ab 你好

bcadefg','abcdefg','1234567') as new_str from dual;

from_string 与

to_strig以字符为单位，对应字符一一替换；

14.按数字和字母混合字符串中的字母排序

SQL> select * from

V;

DATA

-----------------

7369 SMITN

7499 ALLEN

... ...

7900 JAMES

7902 FORD

7934 MILLER

按字母排序需要先取出其中的字母，可以用translate的替换功能，将数字与空格都替换为空。

select ename,translate(ename,'- 0123456789','-') as ean from v

order by 2;

加

'-'的目的是不让ean这一列为空；

15.处理排序空值

使用关键字 nulls first 和 nulls

last，分别表示空值在前和在后；

空值在前

select ename,sal,comm from

emp order by 3 nulls first;

空值在后

select ename,sal,comm from

emp order by 3 nulls last;

16.根据条件取不同列中的值来排序

第一种方式，可以在查询中新生成一列，用多列排序的方法处理：

select empno as

编码，

ename as 姓名，

case when sal >= 1000

and sal < 2000 then 1 else 2 end as 级别，

sal as 工资 from emp where

deptno = 30 order by 3,4;

第二种方式，在order by 中使用case

when

select empno as

编码，

ename as 姓名，

sal as 工资 from emp where

deptno = 30 order by case when sal >= 1000 and sal < 2000

then 1 else 2 end,3;