Oracle SQL进阶教程：从基础到高级操作

需积分: 9 4 浏览量更新于2024-07-28 收藏 390KB PDF 举报

"Mastering Oracle SQL学习笔记是一份针对Oracle SQL初学者的详尽指南，旨在帮助读者掌握SQL语言的核心概念和实用技巧。本笔记按照章节顺序展开，从基础的Where子句开始，逐步深入到高级主题。 1. 第2章讲解了Where子句，其中提到当使用Between…And子句时，应确保最低值位于两个指定值中的第一个，这样可以避免逻辑混淆。例如，`BETWEEN 1 AND 10`与`BETWEEN 10 AND 1`的写法在逻辑上是不一致的，而前者确保了条件始终为假，除非值正好等于这两个边界。 2. NULL关键字在SQL中具有特殊含义，它表示未知或未定义的值。在处理NULL值时，等号('=')不适用于判断NULL是否相等，因为NULL永远不会等于NULL。例如，尝试使用`WHERE ship_dt = NULL`查询时，虽然语法可以解析但不会返回任何行。NVL函数在此时非常有用，可以用来为NULL值提供替代值，如`SELECT fname, lname, man = NVL(ship_dt, 'default_value') FROM cust_order`。 3. 第3章涉及连接操作，这是SQL中的关键概念，用于组合来自多个表的数据，包括内连接（INNER JOIN）、外连接（LEFT/RIGHT/FULL JOIN）和自连接（SELF JOIN）等，这些在实际应用中极其常见。 4. 第4章介绍分组操作，这是数据分析的基础，通过GROUP BY语句对数据进行分类，并结合聚合函数（如COUNT、SUM、AVG等）来获取汇总信息。 5. 子查询（Chapter 5）是SQL中的嵌套查询，允许在一个查询内部定义另一个查询，常用于筛选或计算结果，比如在WHERE子句中查找满足特定条件的子集。 6. 处理时间数据（Chapter 6）涵盖了日期和时间的格式化、计算以及如何在SQL中有效地进行日期范围的比较。 7. 集合运算（Chapter 7）包括UNION、INTERSECT和EXCEPT，这些操作用于合并或排除多个查询的结果集，提高数据处理的灵活性。 8. 级联查询（Hierarchical Queries）在Chapter 8中探讨，通常用于递归地获取层次结构数据，如组织架构或数据库关系图。 9. Decode和Case语句（Chapter 9）是条件表达式的灵活工具，用于根据不同的条件执行不同的操作，类似于其他编程语言中的if-else语句。 10. 最后，第13章和第14章分别介绍了高级分析函数（Advanced Analytic SQL）和SQL最佳实践（SQL Best Practices），帮助读者提升SQL查询性能和效率，理解如何编写更高效的查询语句。通过学习这个全面的Oracle SQL学习笔记，初学者将建立起坚实的基础，能够自如地处理各种复杂的SQL任务。"

在 Select 的列表中)

注：A.所有出现在 Select 列表中的非聚集表达式必须出现在 Group by 子句中。

B.所有出现在 Group by 子句中的表达式不一定必须出现在 Select 子句中。

例：Wrong： SELECT CUST_ID,CUST_NBR,COUNT(ORDER_NB)

FROM CUST_ORDER

GROUP BY CUST_ID

Correct：SELECT CUST_ID,CUST_NBR,COUNT(ORDER_NB)

FROM CUST_ORDER

GROUP BY CUST_ID,CUST_NBR

Correct Also：SELECT COUNT(ORDER_NB)

FROM CUST_ORDER

GROUP BY CUST_ID

If we have a constant in our SELECT list, we don't need to include it in the GROUP BY clause.

However, including the constant in the GROUP BY clause doesn't alter the result. Therefore,

both the following statements will produce the same output:

SELECT 'CUSTOMER', CUST_NBR, COUNT(ORDER_NBR)

FROM CUST_ORDER

GROUP BY CUST_NBR;

SELECT 'CUSTOMER', CUST_NBR, COUNT(ORDER_NBR)

FROM CUST_ORDER

GROUP BY 'CUSTOMER', CUST_NBR;

Finally, we can't use a group function (aggregate function) in the GROUP BY clause

When we GROUP BY a column that contains NULL values for some rows, all the rows with NULL

values are placed into a single group and presented as one summary row in the output. Since

the GROUP BY clause inherently performs an ORDER BY on the group by columns, the row containing

the NULL value is put at the end. If we want this row to be the first row in the output,

we can perform an ORDER BY on SALE_PRICE in descending order

While producing summary results using the GROUP BY clause, we can filter records from the

table based on a WHERE clause, as in the following example, which produces a count of orders

in which the sale price exceeds $25.00 for each customer:

SELECT CUST_NBR, COUNT(ORDER_NBR)

FROM CUST_ORDER

WHERE SALE_PRICE > 25

GROUP BY CUST_NBR;

CUST_NBR COUNT(ORDER_NBR)

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

231 4

244 2

264 2

288 2

While executing a SQL statement with a WHERE clause and a GROUP BY clause, Oracle first applies

the WHERE clause and filters out the rows that don't satisfy the WHERE condition. The rows

that satisfy the WHERE clause are then grouped using the GROUP BY clause.

The SQL syntax requires that the WHERE clause must come before the GROUP BY clause.

5．Having 字句：

The HAVING clause is used to put a filter on the groups created by the GROUP BY clause.

例：SELECT CUST_NBR, COUNT(ORDER_NBR)

FROM CUST_ORDER

GROUP BY CUST_NBR

HAVING CUST_NBR < 260;

CUST_NBR COUNT(ORDER_NBR)

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

201 2

231 6

244 2

The previous example is a poor use of the HAVING clause, because that clause only references

unsummarized data. It's more efficient to use WHERE CUST_NBR < 260 instead of HAVING CUST_NBR

< 260, because the WHERE clause eliminates rows prior to summarization, whereas HAVING

eliminates groups post-summarization.

例：SELECT CUST_NBR, COUNT(ORDER_NBR)

FROM CUST_ORDER

GROUP BY CUST_NBR

HAVING COUNT(ORDER_NBR) > 2;

CUST_NBR COUNT(ORDER_NBR)

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

231 6

255 6

This is an appropriate use for HAVING, because the results of the aggregate function cannot

be determined until after the grouping takes place.

注：Having 字句即可以用非聚集表达式作过滤条件，也可以用聚集表达式作过滤条件。

A.当用非聚集表达式作过滤条件时，其结果等同于使用 Where 字句，而且由于 Where 字句是在

分组前进行过滤，其效率比起分组后再进行过滤的效率更高。

B.Having 字句适用于对聚集表达式进行过滤的情况，因为此时必须依靠分组的结果进行过滤，

而 Where 字句无法进行统计。

C.Having 字句的语法格式和 Where 字句的语法格式很相似，但是对 Having 字句的语法有一个特

殊的限制：Having 字句的过滤条件必须引用自 Select 列表或者 Group by 字句

D.Having 字句和 Group by 字句的先后顺序并不重要，我们可以将 Having 字句放在 Group by 字

句之后，也可以放在 Group by 字句之前

E.Where 字句和 Having 字句可以同时出现在查询语句中，当我们这样做时，首先 Where 字句被

首先执行，所有不符合 Where 条件的记录将被过滤而不会出现在 Group by 的分组中，Group by

字句对已过滤的记录进行分组统计，最后 Having 字句对分组后的结果进行过滤

***************************************************************************************

四、第 5 章：子查询(Subquery)

***************************************************************************************

1.子查询的概念：

A subquery is a SELECT statement that is nested within another SQL statement. For the purpose

of this discussion, we will call the SQL statement that contains a subquery the containing

statement.

Subqueries are executed prior to execution of the containing SQL statementand the result

set generated by the subquery is discarded(丢弃) after the containing SQL statement has

finished execution. Thus, a subquery can be thought of as a temporary table with statement

scope

A subquery may either be correlated with its containing SQL statement, meaning that it

references one or more columns from the containing statement, or it might reference nothing

outside itself, in which case it is called a noncorrelated subquery. (子查询可以和包含它

的 SQL 语句是相关的(correlated)，就是说它可以引用到包含它的 SQL 语句的一个或多个字段，也可

以是和包含它的 SQL 毫不相关，我们称它为－非相关子查询(noncorrelated subquery)。

A less-commonly-used but powerful variety of subquery, called the inline view, occurs in

the FROM clause of a select statement. Inline views are always noncorrelated; they are

evaluated first and behave like unindexed tables cached in memory for the remainder of the

query. (有一种少用但非常有用的子查询，称为－inline view，出现在查询语句的 From 子句中。

Inline view 经常是和包含它的 SQL 语句非相关的，它们在 SQL 语句被执行时首先被执行，然后在内

存中以非索引表的形式存在)

2.非相关性子查询：(Noncorrelated Subquery)：

Noncorrelated subqueries allow each row from the containing SQL statement to be compared

to a set of values. Divide noncorrelated subqueries into the following three categories,

depending on the number of rows and columns returned in their result set:

A.Single-row, single-column subqueries

B.Multiple-row, single-column subqueries

C.Multiple-column subqueries

Depending on the category, different sets of operators may be employed by the containing

SQL statement to interact with the subquery.

A. Single-Row, Single-Column Subqueries：

A subquery that returns a single row with a single column is treated like a scalar(数值)

by the containing statement; not surprisingly, these types of subqueries are known as scalar

subqueries. The subquery may appear on either side of a condition, and the usual comparison

operators (=, <, >, !=, <=, >=) are employed.

剩余57页未读，继续阅读

byg760

粉丝: 6
资源: 4

Oracle SQL进阶教程：从基础到高级操作

Mastering_Oracle_SQL（含源码）

mastering_ros_robot_description_pkg.tar.gz

Mastering_SQL_practice:掌握PostgresSQL

语音合成代码matlab-mastering_ros:我自己对mastering_ros软件包的改编，来自：https://github.co

Mastering_SaltStack_

Mastering Oracle SQL and SQL Plus

lab_mastering_tcp_analysis_wireshark

Mastering_VMware_vSphere_4

mastering_ros_demo_pkgtan

mastering_dojo_pdf

最新资源