Oracle SQL高级教程(第2版)：核心至实践

5星 · 超过95%的资源需积分: 19 10 浏览量更新于2024-07-24 6 收藏 4.14MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

"Apress.Pro.Oracle.SQL.2nd.Edition.Nov.2013"是一本专业的Oracle数据库高级编程指南，第二版于2013年11月发布。该书专为Oracle SQL开发者设计，深入讲解了核心SQL概念、执行优化、查询方法、集合理解、问题导向、执行计划、高级分组、分析函数、模型子句、子查询优化、半连接与反连接、索引管理、SQL语句扩展、事务处理、测试与质量保证，以及计划稳定性、计划控制和一些其他SQL构造。在第一部分，"Core SQL"（第一章），作者探讨了SQL的基础知识，包括SQL语言的核心结构、语法要点以及如何构建基本的查询。读者将学习到如何有效地使用SQL进行数据检索和操作。第二章"SQL Execution"（SQL执行）则关注查询的执行过程，如何理解和优化SQL语句，以提升性能，减少资源消耗。这里会介绍执行计划的解读，以理解数据库如何解析和执行SQL请求。 "Access and Join Methods"（第三章）介绍了访问数据的不同方式和JOIN操作，涵盖了连接表、自连接、外连接等常见操作，帮助读者构建更复杂的查询逻辑。 "SQL Is about Sets"（第四章）强调了SQL的本质是基于集合操作，讲解如何使用集合理论来理解SQL中的操作，如UNION、INTERSECT和EXCEPT。第五章"The Question"（关于问题）着重于提问的艺术，即如何清晰地表达查询需求，确保得到准确的结果。这一章可能涉及SQL编写技巧和最佳实践。第六章至第十六章分别深入探讨了SQL执行计划、高级分组功能、分析函数、模型子句的使用、子查询优化、索引策略、SQL语句的扩展功能、事务处理流程、测试和质量保证，以及计划的稳定性与控制。最后一部分"Misellaneous SQL Constructs"（第十八章）涵盖了SQL中的各种特殊构造，如临时表、存储过程、触发器等，帮助读者应对各种复杂场景。本书不仅适合有一定基础的Oracle SQL开发者，也对希望进一步提升SQL技能或解决特定性能问题的专业人士提供了丰富的参考材料。通过阅读这本书，读者可以深入理解Oracle SQL语言，提高查询性能，以及优化数据库管理和维护。

资源详情

资源推荐

CHAPTER 1 ■ CORE SQL

Listing 1-13. Partial Query Execution through the GROUP BY Clause

SQL> select c.customer_id, count(o.order_id) as orders_ct

2 from oe.customers c

3 join oe.orders o

4 on c.customer_id = o.customer_id

5 where gender = 'F'

6 group by c.customer_id;

CUSTOMER_ID ORDERS_CT

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

156 1

123 1

166 1

154 1

169 1

105 4

103 4

107 4

104 4

147 5

146 5

11 rows selected.

Notice that the output from the query, although grouped, is not ordered. The display makes it appear as though

the rows are ordered by order_ct, but this is more coincidence and not guaranteed behavior. This is an important

item to remember; the GROUP BY clause does not ensure ordering of data. If you want the list to display in a specific

order, you have to specify an ORDER BY clause.

The HAVING Clause

The HAVING clause restricts the grouped summary rows to those for which the condition(s) in the clause are TRUE.

Unless you include a HAVING clause, all summary rows are returned. The GROUP BY and HAVING clauses are actually

interchangeable positionally (it doesn’t matter which one comes first). However, it seems to make more sense to code

them with the GROUP BY clause first, because GROUP BY is processed logically first. Essentially, the HAVING clause is

a second WHERE clause that is evaluated after GROUP BY occurs, and is used to filter on grouped values. Because the

WHERE clause is applied before the GROUP BY occurs, you cannot filter grouped results in the WHERE clause; you must

use the HAVING clause instead.

In our example query, the HAVING clause, HAVING COUNT(o.order_id) > 4, limits the grouped result data of

11 rows down to two rows. You can confirm this by reviewing the list of rows returned after GROUP BY is applied,

as shown in Listing 1-13. Note that only customers 146 and 147 have placed more than four orders. The two rows that

make up the final result set are now ready.

The SELECT List

The SELECT list is where the columns included in the final result set from your query are provided. A column can be an

actual column from a table, an expression, or even the result of a SELECT statement, as shown in Listing 1-14.

CHAPTER 1 ■ CORE SQL

Listing 1-14. Example Query Showing SELECT List Alternatives

SQL> select c.customer_id, c.cust_first_name||' '||c.cust_last_name,

2 (select e.last_name from hr.employees e where e.employee_id = c.account_mgr_id) acct_mgr)

3 from oe.customers c;

CUSTOMER_ID CUST_NAME ACCT_MGR

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

147 Ishwarya Roberts Russell

148 Gustav Steenburgen Russell

...

931 Buster Edwards Cambrault

981 Daniel Gueney Cambrault

319 rows selected.

When another SELECT statement is used to produce the value of a column, the query must return only one row

and one column value. These types of subqueries are referred to as scalar subqueries. Although this can be very useful

syntax, keep in mind that the scalar subquery is executed once for each row in the result set. There are optimizations

available that may eliminate some duplicate executions of the subquery, but the worst-case scenario is that each row

requires this scalar subquery to be executed. Imagine the possible overhead involved if your result set had thousands,

or millions, of rows! I review scalar subqueries later in the book and discuss how to use them optimally.

Another option you may need to use in the SELECT list is the DISTINCT clause. The example provided here doesn’t

use it, but I want to mention it briefly. The DISTINCT clause causes duplicate rows to be removed from the dataset

produced after the other clauses have been processed.

After the SELECT list is processed, you now have the final result set for your query. The only thing that remains to

be done, if it is included, is to sort the result set into a desired order.

The ORDER BY Clause

The ORDER BY clause is used to order the final set of rows returned by the statement. In this case, the requested sort

order was to be by orders_ct and customer_id. The orders_ct column is the value computed using the COUNT

aggregate function in the GROUP BY clause. As shown in Listing 1-13, there are two customers that each placed more

than four orders. Because each customer placed five orders, the order_ct is the same, so the second ordering column

determines the final display order. As shown in Listing 1-15, the final sorted output of the query is a two-row dataset

ordered by customer_id.

Listing 1-15. Example Query Final Output

SQL> select c.customer_id, count(o.order_id) as orders_ct

2 from oe.customers c

3 join oe.orders o

4 on c.customer_id = o.customer_id

5 where c.gender = 'F'

6 group by c.customer_id

7 having count(o.order_id) > 4

8 order by orders_ct, c.customer_id

9 /

CUSTOMER_ID ORDERS_CT

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

146 5

147 5

CHAPTER 1 ■ CORE SQL

When ordered output is requested, Oracle must take the final set of data after all other clauses have been

processed and sort them as specified. The size of the data that needs to be sorted is important. When I say size, I mean

total bytes of data in the result set. To estimate the size of the dataset, multiply the number of rows by the number of

bytes per row. The bytes per row are determined by summing the average column lengths of each of the columns in

the SELECT list.

The example query requests only the customer_id and orders_ct column values in the SELECT list. Let’s use ten

as our estimated bytes-per-row value. In Chapter 6, I show you where to find the optimizer’s estimate for this value.

So, given that we only have two rows in the result set, the sort size is actually quite small, approximately 20 bytes.

Remember, this is only an estimate, but the estimate is an important one.

Small sorts should be accomplished entirely in memory whereas large sorts may have to use temporary disk

space to complete the sort. As you may likely deduce, a sort that occurs in memory is faster than a sort that must use

disk. Therefore, when the optimizer estimates the effect of sorting data, it has to consider how big the sort is to adjust

how to accomplish getting the query result in the most efficient way. In general, consider sorts as a fairly expensive

overhead to your query processing time, particularly if the size of your result set is large.

The INSERT Statement

The INSERT statement is used to add rows to a table, partition, or view. Rows can be inserted in either a single-table

or multitable method. A single-table insert inserts values into one row of one table either by specifying the values

explicitly or by retrieving the values using a subquery. The multitable insert inserts rows into one or more tables and

computes the row values it inserts by retrieving the values using a subquery.

Single-Table Inserts

The first example in Listing 1-16 illustrates a single-table insert using the VALUES clause. Each column value is entered

explicitly. The column list is optional if you include values for each column defined in the table. However, if you only

want to provide values for a subset of the columns, you must specify the column names in the column list. A good

practice is to include the column list regardless of whether you specify values for all the columns. Doing so acts to

self-document the statement and also helps reduce possible errors that might occur in the future should someone add

a new column to the table.

Listing 1-16. Single-Table Insert

SQL> insert into hr.jobs (job_id, job_title, min_salary, max_salary)

2 values ('IT_PM', 'Project Manager', 5000, 11000) ;

1 row created.

SQL> insert into scott.bonus (ename, job, sal)

2 select ename, job, sal * .10

3 from scott.emp;

14 rows created.

The second example in Listing 1-16 illustrates an insert using a subquery, which is a very flexible option for

inserting rows. The subquery can be written to return one or more rows. Each row returned is used to supply column

values for the new rows to be inserted. The subquery can be as simple or as complex as needed to satisfy your needs.

In this example, we use the subquery to compute a 10 percent bonus for each employee based on his or her current

salary. The bonus table actually has four columns, but we only populate three of them with this insert. The comm

column isn’t populated with a value from the subquery and we do not include it in the column list. Because we don’t

include this column, the value for it is null. Note that if the comm column had a NOT NULL constraint, you would get a

constraint error and the statement would fail.

剩余567页未读，继续阅读

fzw180

粉丝: 4
资源: 29

Oracle SQL高级教程(第2版)：核心至实践

Pro SQL Server 2012 Administration, 2nd Edition.pdf

Apress.Pro.JPA.2.2nd.Edition.Oct.2013

关于springboot的英文文献

oracle中.xml sql语句批量添加

帮我找一下可以写再javaweb毕设里的英文参考文献

springboot的参考文献

springboot框架外文参考文献列表

spring boot项目英文文献

javascript相关外文文献

列举几个SpringBoot相关的技术文献,以文献引用的格式展示

推荐5本sql server的书籍，并且说明书籍的ISBN号，出版社，作者，出版日期

springboot外文文献

springboot参考文献

html5的网页设计与制作研究参考文献

最近6年的C#参考书写在此处。要求序号、作者、书名、出版社、出版时间。

关于java的外语文献_java英文参考文献（涵盖3年最新120个）

设计一个java的图书管理系统并给出具体的代码实现

springboot文献综述

数据结构实验报告(集合)

MythwareStudentHacker-main.zip

最新资源