ZJU的第七十九天

“Oracle is interesting”

来，我们来复习一下Oracle的查询语句，基本上的记忆都在查询上了，虎哥的四字真言：Oracle无非就是增三改擦嘛！So Easy！So Interesting！So So！

1、SELECT 查询所有列资料或特定列资料

2、distinct消除查询结果的重复行

SELECT DISTINCT oeb04,oeb06 FROM oeb_file;

3、在SELECT查询资料列上使用算术表达式(+、-、*、/)及as应用使用列别名

SELECT oeb01 as "订单单号" FROM oeb_file

4、nvl函数或nvl2函数 处理算术表达式运算中栏位空值问题

如果查询的栏位参与+ - / *算术运算，只要参与运算的栏位有一个为空值，则会导致整个运算结果为空值。nvl(expr1,expr2)如果expr1不为空，则返回expr1，否则返回expr2；expr1与expr2可以是任意数据类型,但是expr1与expr2需是相同的数据类型。nvl(expr1,expr2,expr3)如果expr1不为空，则返回expr2，否则返回expr3；expr1可以是任意数据类型,但是expr2与expr3需是相同的数据类型。

SELECT oeb12-oeb24-nvl(ta_oeb013,0) as "订单未交量" FROM oeb_file

5、使用WHERE条件子句

【=】【！=】【】【】【in】【between...and】【is Null】【like】【%：匹配0个或多个字符】【 _ ：匹配单个字符】【escape：转义字符】【and】【or】【not】

--查询Tiptop GP用户账号中带有'_'下划线的用户信息，escape用'a'做转义字符且2011/03月期间开通的账号

SELECT * FROM zx_file

WHERE zx03 = '2G69' AND zx01 LIKE '%a_%' ESCAPE 'a'

AND zxdate BETWEEN to_date('2011/03/01','YYYY/MM/DD') AND to_date('2011/03/31','YYYY/MM/DD')

ZX01 ZX02 ZX03 ZX04

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

top_test 測試帳號 2G69 CLASS-A

6、ORDER BY desc (升序排序:默认)，ORDER BY asc (降序排序) 将查询出来的资料排序

7、连接查询：基于2个或2个以上的视图或表的查询

7.1、简单连接查询：FROM子句接 表或视图，WHERE子句指定连接条件

7.2、内连接INNER JOIN、左外连接LEFT JOIN、右外连接RIGHT JOIN、完全外连接FULL JOIN、+操作符

内连接INNER JOIN：用于返回满足条件的所有记录,默认情况下在执行查询如果没有指定任何连接操作,则此查询SQL即为内连接

左外连接LEFT JOIN/LEFT OUTER JOIN：不仅返回满足连接条件的结果集而且返回不满足连接条件的但是位于连接操作符左边 表或视图的结果集

右外连接RIGHT JOIN/RIGHT OUTER JOIN：不仅返回满足连接条件的结果集而且返回不满足连接条件的但是位于连接操作符右边 表或视图的结果集

完全外连接FULL JOIN/FULL OUTER JOIN：不仅返回满足连接条件的结果集而且返回不满足连接条件的位于连接操作符 左 右边表或视图的结果集

8、自连接查询：同一张表之间的连接查询，主要用在自参照表上显示同一张表不同栏位列的关系

9、子查询：也称嵌套查询，是指嵌入在其他SQL语句中的SELECT语句

注意：当在DDL语句中引用子查询时可以带有ORDER BY子句,但是当在WHERE子句、SET子句中引用子查询时则不可以带有ORDER BY子句

应用：

通过在WHERE、HAVING、START WITH子句中使用子查询可以提供条件值 通过在UPDATE语句中使用子查询可以修改一列或多列数据

通过在INSERT或CREATE TABLE语句中使用子查询可以将来源表数据插入到目标表中

通过在CREATE VIEW或是CREATE MATERIALIZED VIEW中使用子查询可以定义视图

9.1、单行子查询：只返回一行数据的子查询

9.2、多行子查询：只返回多行数据的子查询

当在WHERE子句中使用多行子查询时,必须使用多行比较符IN、ALL、ANY

IN：匹配子查询结果中的任意一个值即可

ALL：必须要符合子查询结果中的所有值, 不能单独使用,需配合单行比较符(=,>,=,,!=)使用

ANY：只要符号子查询结果中的任意值即可,不能单独使用,需配合单行比较符(=,>,=,,!=)使用

9.3、多列子查询：多列数据必须同时匹配

区别单行子查询、多行子查询、多列子查询

单行子查询：返回单列单行数据，针对 单列

多行子查询：返回单列多行数据，针对 单列

多列子查询：返回多列数据的子查询，针对 多列

可以返回 多列单行数据，此时WHERE子句可以使用单行比较符如=.....，查询返回多列成对匹配的结果

可以返回 多列多行数据，此时WHERE子句可以使用多行比较符如IN.....，查询返回多列成对匹配的结果

9.4、在FROM子句中使用子查询：FROM子句中使用的子查询，该子查询会被当做视图看待，也称内嵌视图

9.5、相关子查询EXISTS

相关子查询是指需要应用主查询列表中的子查询语句,需要通过谓词EXISTS来实现，当使用EXSISTS谓词时,如果子查询存在返回结果则条件返回TRUE,否则返回FALSE。

--查询部门存在于部门别gem_file中且部门编号为2G69的tiptop系统用户

SELECT zx01,zx02,zx03 FROM zx_file

WHERE EXISTS

(

SELECT 1 FROM gem_file WHERE zx_file.zx03 = gem_file.gem01 AND gem01 = '2G69'

) --使用EXISTS,因为gem_file有2G69部门资讯,所以子查询返回的条件为true

10、合并查询：为了合并多个查询结果

语法：

SELECT 语句1 [UNION |UNION ALL |INTERSECT | MINUS] SELECT 语句2

UNION：获取两个结果的并集，且去掉合并结果中重复的记录

NION ALL：获取两个结果的并集，但不会去掉合并结果中重复的记录

INTERSECT：取两个结果的交集

MINUS：获取两个结果集的差集，返回在第一个结果集中存在但是第二个结果集中不存在的记录

说明：

这些集合操作符具有相同的优先级,当同时使用多个操作符时会按照从左到右的方式应用这些集合操作符

使用集合操作符时必须确保不同的查询语句查询的列的个数相同且类型也必须匹配

子句不能有ORDER、GROUP，必须放在整个语句的末尾

11、数据分组查询统计

实际应用中常需要做的就统计数据库中的信息,按照指定列分组群以比较各组群的差异信息，Oracle数据分组通过使用GROUP BY子句指定要分组的列

使用分组函数如COUNT、AVG、SUM、MAX、MIN等显示统计结果

使用HAVING子句限制分组显示结果(注意:having后面必须接分组函数)

如果查询有分组函数计算,则先将WHERE条件符合的资料计算,HAVING子句只是显示分组显示的结果,不参与分组函数的计算

ROLLUP生成横向统计信息：Group by rollup（列）

GROUPING SETS 按多行多列分别统计信息：Group by grouping sets（列）

常用分组函数：【MAX】【MIN】【AVG】【SUM】【COUNT】【VARIANCE：列或运算式的方差】【STDDEV：列或运算式的偏差】等

注意：

分组函数 只能够 出现在选择列表、ORDER BY、HAVING子句中，不能出现在 WHERE和GROUP BY子句中

除了COUNT(*)之外,其他分组函数都会忽略NULL行

执行SELECT语句时，选择列必须出现在GROUP BY子句中

12、如何查询某时刻点提交的数据资料

利用在from子句中指定as of子句,在as of子句中指定时间，也可以指定SCN

前提：数据库必须采用UNDO管理模式，并且初始化了undo_retention限制undo数据保留的时间

13、CASE及递归查询等复杂应用.....

--CASE语法应用：查询ERP用户信息，判断其部门信息

SELECT zx01,zx02,zx03,

(CASE

WHEN zx03 = '2G69'

THEN '资讯部'

WHEN zx03 = '2G88' OR zx03 = '2G89'

THEN '财务部'

WHEN zx03 IS NULL

THEN '无部门'

ELSE

'其他部门'

END

)department

FROM zx_file

ORDER by zx01

递归查询，构造函数：

WITH 表名 as (SQL子查询语句)：可以将(SQL子查询语句复用)

START WITH ... CONNECT BY PRIOR ... 实现Oracle层次查询

START WITH：用于指定层次查询的起始根行，当然也可以指定末行开始查询

CONNECT BY：用于指定父子行关系，在其后必须使用PRIOR引用父行

（恩，太难了，学不会~886）

嗯~记住了多少？