注:以下所有sql案例均取自"oracle查询优化改写技巧与案例"丛书。
EMP表的详细:
1.查询表中所有的行与列
查询所有信息，
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.从表中检索部分行
查看公司有多少销售人员
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
1114 李磊磊 会计 3319 22-12月-15 2500 50
1118 沈倩 会计 3319 06-5月 -10 2100 50
3.查找空值
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.将空值转换为实际值
----------------
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))
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.从表中检索部分列
明确指定查询哪些列即可。
---------- -------------------- -------------- ----------
1111 李四 03-11月-15 3400
1112 王五 25-4月 -12 4400
1115 张少丽 11-3月 -16 3400
7.为列取有意义的名称
可以给列取别名
-------------------- ---------- ---------- ----------
张三 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元的人
---------- ----------
2500
2800
2100
9.拼接列
用字符串连接符“||”来把各列拼在一起
--------------------------------------------------------------
张三的工作是主管
林建国的工作是主管
10.在Select语句中使用条件逻辑
如:当员工信息小于或等于2500元时，就返回消息“过低”，大于或等于4000元时，就返回消息“过高”，如果在两者之间，就返回“OK”。
ENAME SAL STATUS
-------------------- ---------- ------------
张三 5200 过高
李四 3400 OK
王五 4400 过高
赵二 3450 OK
李磊磊 2500 过低
张少丽 3400 OK
林建国 5700 过高
马富邦 2800 OK
沈倩 2100 过低
已选择9行。
这种方式还常用在报表中，比如要按工资分档次统计人数
档次 人数
------------------ ----------
1000-2500 2
2500-3000 1
3000-4000 3
4000-5000 1
好高 2
11.限制返回的行数
并不要求每次都要返回所有的数据，比如，进行抽查的时候会只要求返回两条数据。
我们可以用伪列rownum来过滤，rownum一次对返回的每一条数据做一个标识。
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来对数据进行随机排序，然后取其中三行。
---------- --------------------
1118 沈倩
1110 张三
1111 李四
EMPNO ENAME
---------- --------------------
1115 张少丽
1118 沈倩
1117 马富邦
可以看到两次取的随机值不同
注意:
正确的做法是:先随机排序，再取数据
错误的做法是:先取数据，再随机排序，这样每次执行语句得到的都是相同的结果。
13.模糊查询
先建立如下view:
创建之后的表为:
--------------
ABCDEF
_BCDEF
_BCEDF
_\BCEFG
XYCEG
要求一：查出vname中包含字符串"CDE"的
VNAME
--------------
ABCDEF
_BCDEF
要求二：查出vname中包含字符串“_BCE”的
VNAME
--------------
_BCEDF
_\BCEFG
发现多了一个_\BCEFG。因为在LIKE子句中有两个通配符：“%”(替代一个或多个字符)、“_”被当做通配符了，怎么办呢？我们可以用转义字符：
VNAME
--------------
_BCEDF
其中escape 把‘\’标识为转义字符，而‘\’把‘_’转义为字符，而非其原意(通配符)。
想查到_\BCE可以用双转义:
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
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
