Mastering_Oracle_SQL（含源码）_masteringoraclesql资源-CSDN文库

共4个文件

chm：2个

pdf：1个

zip：1个

5星 · 超过95%的资源需积分: 9 12 浏览量 2012-11-02 11:16:46 上传评论 2 收藏 1.41MB RAR 举报

《Mastering Oracle SQL》是一本深受数据库管理员和开发人员欢迎的专业书籍，主要涵盖了Oracle SQL的高级用法和技术。这本书分为第一版和第二版，提供英文版本，并以CHM（ Compiled HTML Help ）格式呈现，方便读者在线查阅和搜索。同时，资源中还包括了源码和学习笔记，为读者提供了实践和自我提升的宝贵材料。 Oracle SQL是Oracle数据库系统中的查询语言，它在数据管理和分析方面起着至关重要的作用。通过深入学习《Mastering Oracle SQL》，你可以掌握以下关键知识点： 1. **SQL基础**：了解SQL的基本语法，包括SELECT语句用于查询数据，INSERT、UPDATE和DELETE用于修改数据，以及DDL（Data Definition Language）用于创建和修改表结构。 2. **高级查询技巧**：学习如何使用子查询、联接（JOINs）、集合操作（UNION、INTERSECT、EXCEPT）来处理复杂的查询需求。理解分组（GROUP BY）和聚合函数（如COUNT、SUM、AVG、MAX、MIN）在统计分析中的应用。 3. **窗口函数**：Oracle SQL中的窗口函数（如RANK、ROW_NUMBER、LEAD、LAG等）能让你进行更复杂的行间操作，如排名、移动平均等。 4. **索引和优化**：理解不同类型的索引（如B树、位图、函数索引）以及如何创建和管理它们，以提高查询性能。学习如何使用EXPLAIN PLAN分析查询执行计划，进行性能调优。 5. **PL/SQL编程**：Oracle的存储过程、函数、触发器和包（Packages）等PL/SQL编程元素，以及异常处理和游标。 6. **数据类型和约束**：熟悉Oracle的各种数据类型，如NUMBER、VARCHAR2、DATE等，以及实体完整性约束（如NOT NULL、UNIQUE、PRIMARY KEY）和参照完整性约束（FOREIGN KEY）。 7. **事务和并发控制**：理解事务的概念，学习如何使用COMMIT、ROLLBACK和SAVEPOINT，以及了解锁定机制和乐观锁、悲观锁的区别。 8. **数据库设计**：学习关系数据库设计原则，如范式理论（1NF、2NF、3NF、BCNF），以及如何通过ER模型进行数据库设计。 9. **安全性**：理解Oracle的角色、权限和对象权限管理，以及如何创建和管理用户，确保数据安全。 10. **备份与恢复**：了解Oracle的数据保护策略，如物理备份、逻辑备份，以及RMAN（Recovery Manager）的使用。通过《Mastering Oracle SQL》的学习，你不仅可以熟练掌握Oracle SQL的语法和高级特性，还能获得在实际项目中解决问题的能力。配合源码和学习笔记，你将能够加深理解，提高解决问题的效率，成为Oracle SQL的专家。

资源推荐

资源详情

资源评论

收起资源包目录

Mastering_Oracle_SQL.rar （4个子文件）

Mastering_Oracle_SQL

Mastering_Oracle_SQL(第二版).chm 712KB

Mastering_Oracle_SQL.chm 323KB

Mastering Oracle SQL源代码.zip 73KB

Mastering Oracle SQL学习笔记.pdf 443KB

Mastering Oracle SQL 学习笔记

一、第 2 章：Where子句 ................................................................................................................................1

二、第 3 章：连接........................................................................................................................................... 2

三、第 4 章：分组操作：............................................................................................................................... 9

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

五、第 6 章：处理时间数据......................................................................................................................... 20

六、第 7 章：集合运算(Set Operation):....................................................................................................... 30

七、第 8 章：Hierachical Querys(级联查询) ............................................................................................... 37

八、第 9 章 Decode和Case........................................................................................................................... 45

九、第 13 章Advanced Analytic SQL(高级分析函数)：............................................................................. 50

十、第 14 章 SQL Best Practise(SQL最佳实践)：...................................................................................... 55

一、第 2 章：Where 子句

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

1．Between…And 子句：

When using BETWEEN, make sure the first value is the lowest of the two values provided.

While "BETWEEN 1 AND 10" and "BETWEEN 10 AND 1" might seem logically equivalent, specifying

the higher value first guarantees that your condition will always evaluate to FALSE

2．NULL 关键字：

When working with NULL, the concept of equality does not apply; a column may be NULL, but

it will never equal NULL.

Oracle doesn't complain if you mistakenly use the equality operator when evaluating for

NULL,The following query will parse and execute but will never return rows:

SELECT order_nbr, cust_nbr, sale_price, order_dt FROM cust_order WHERE ship_dt = NULL;

NVL substitutes a specified value for columns that are NULL, as in:

SELECT fname, lname, manager_emp_id FROM employee

WHERE NVL(manager_emp_id, -999) != 11;

In this example, the value -999 is substituted for all NULL values, which, since -999 is

never equal to 11, guarantees that all rows whose manager_emp_id column is NULL will be

included in the result set. Thus, all employees whose manager_emp_id column is NULL or is

not NULL and has a value other than 11 will be retrieved by the query.

3.如何充分利用 WHERE 子句：

here are a few tips to help you make the most of your WHERE clauses:

A．Check your join conditions carefully. Make sure that each data set in the FROM clause

is properly joined. Keep in mind that some joins require multiple conditions. See

Chapter 3 for more information.

B．Avoid unnecessary joins. Just because two data sets in your FROM clause contain the

same column does not necessitate a join condition be added to your WHERE clause. In

some designs, redundant data has been propagated to multiple tables through a process

called denormalization.

C．Use parentheses. Oracle maintains both operator precedence and condition precedence,

meaning there are clearly defined rules for the order in which things will be evaluated

but the safest route for you and for those who will later maintain your code is

to dictate evaluation order using parentheses. For operators, specifying

(5 * p.inventory_qty) + 2 rather than 5 * p.inventory_qty + 2 makes the order in

which the operations should be performed clear. For conditions, use parentheses any

time the OR operator is employed.

D．Handle NULLs properly. After writing your WHERE clause, inspect each condition with

respect to its ability to properly handle NULL values. Take the time to understand

the table definitions in your database so that you know which columns allow NULLs.

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

二、第 3 章：连接

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

1．JOIN 的基本概念：

A join is a SQL query that extracts information from two or more tables or views. When you

specify multiple tables or views in the FROM clause of a query, Oracle performs a join, linking

rows from multiple tables together. There are several types of joins to be aware of:

A．Inner joins

Inner joins are the regular joins. An inner join returns the rows that satisfy the join

condition. Each row returned by an inner join contains data from all tables involved

in the join.

B．Outer joins

Outer joins are an extension to the inner joins. An outer join returns the rows that

satisfy the join condition and also the rows from one table for which no corresponding

rows exist in the other table.

C．Self joins

A self join is a join of a table to itself.

2．笛卡儿积：

If you don't specify the join condition while joining two tables, Oracle combines each row

from the first table with each row of the second table. This type of result set is called

as a Cartesian product. The number of rows in a Cartesian product is the product of the number

of rows in each table.

3．JOIN 的条件：

Usually the join condition is specified on the foreign key columns of one table and the primary

key or unique key columns of another table. However, you can specify other columns as well.

Each join condition involves columns that relate two tables.

A join condition may involve more than one column. This is usually the case when a foreign

key constraint consists of multiple columns.

A join condition must involve columns with compatible datatypes. Note that the datatype of

the columns involved in a join condition need to be compatible, not the same. Oracle performs

automatic datatype conversion between the join columns, if required

4．外连接的语法：

The syntax of the outer join is a bit different from that of the inner join, because it includes

a special operator called the outer join operator. The outer join operator is a plus sign

enclosed in parentheses, i.e., (+). This operator is used in the join condition in the WHERE

clause following a field name from the table that you wish to be considered the optional

table.(如果表 A 中某条记录在表 B 中没有对应的记录存在，而又想列出表 A 中所有的记录，此时采

用内连接将无法达到目的。可以采用外连接的方法，在 WHERE 子句中，表 B 相应字段名的右方添加符

号：(+)，则 Oracle 在连接时则会自动生成一条空记录和表 A 的记录进行连接)

5．外连接的约束：

A．The outer join operator can appear on only one side of an expression in the join

condition. You get an ORA-1468 error if you attempt to use it on both sides.

B．If a join involves more than two tables, then one table can't be outer joined

with more than one other table in the query.

Wrong：SELECT E.LNAME, J.FUNCTION, D.NAME

FROM EMPLOYEE E, JOB J, DEPARTMENT D

WHERE E.JOB_ID (+) = J.JOB_ID

AND E.DEPT_ID (+) = D.DEPT_ID;

Correct：CREATE VIEW V_EMP_JOB

AS SELECT E.DEPT_ID, E.LNAME, J.FUNCTION

FROM EMPLOYEE E, JOB J

WHERE E.JOB_ID (+) = J.JOB_ID;

SELECT V.LNAME, V.FUNCTION, D.NAME

FROM V_EMP_JOB V, DEPARTMENT D

WHERE V.DEPT_ID (+) = D.DEPT_ID;

Correct Also：SELECT V.LNAME, V.FUNCTION, D.NAME

FROM (SELECT E.DEPT_ID, E.LNAME,J.FUNCTION

FROM EMPLOYEE E, JOB J

WHERE E.JOB_ID (+) = J.JOB_ID) V, DEPARTMENT D

WHERE V.DEPT_ID (+) = D.DEPT_ID;

C．An outer join condition containing the (+) operator may not use the IN operator.

D．An outer join condition containing the OR operator may not be combined with another

condition using the OR operator.

SELECT E.LNAME, D.NAME

FROM EMPLOYEE E, DEPARTMENT D

WHERE E.DEPT_ID = D.DEPT_ID (+)

OR D.DEPT_ID = 10;

E．A condition containing the (+) operator may not involve a subquery

Wrong：SELECT E.LNAME

FROM EMPLOYEE E

WHERE E.DEPT_ID (+) = (SELECT DEPT_ID

FROM DEPARTMENT WHERE NAME = ACCOUNTING');

Correct：SELECT E.LNAME

FROM EMPLOYEE E,

(SELECT DEPT_ID FROM DEPARTMENT WHERE NAME = 'ACCOUNTING') V

WHERE E.DEPT_ID (+) = V.DEPT_ID;

6. 完全外连接(Full Outer Join)：

There are situations when you may want a bidirectional outer join, i.e., you want to

include all the rows from A and B that are:

1．From the result of the inner join.

2．From A that don't have corresponding rows in B.

3．From B that don't have corresponding rows in A.

A UNION of two SELECT statements is a work around for this problem. The UNION operation

eliminates duplicate rows, and the result is a full outer join

Example：

SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP

FROM DEPARTMENT D, LOCATION L

WHERE D.LOCATION_ID (+) = L.LOCATION_ID

UNION

SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP

FROM DEPARTMENT D, LOCATION L

WHERE D.LOCATION_ID = L.LOCATION_ID (+) ;

7．自连接(Self-Join)：

There are situations in which one row of a table is related to another row of the same

table.Such as：

CREATE TABLE EMPLOYEE (

EMP_ID NUMBER (4) NOT NULL PRIMARY KEY,

FNAME VARCHAR2 (15),

LNAME VARCHAR2 (15),

DEPT_ID NUMBER (2),

MANAGER_EMP_ID NUMBER (4) REFERENCES EMPLOYEE(EMP_ID),

SALARY NUMBER (7,2),

HIRE_DATE DATE,

JOB_ID NUMBER (3));

To get information about an employee and his manager, you have to join the EMPLOYEE table

with itself.

SELECT E.NAME EMPLOYEE, M.NAME MANAGER

FROM EMPLOYEE E, EMPLOYEE M

WHERE E.MANAGER_EMP_ID = M.EMP_ID;

Even though the EMPLOYEE table has 14 rows, the previous query returned only 13 rows. This

is because there is an employee without a MANAGER_EMP_ID. Oracle excludes this row from the

result set while performing the self inner join. To include the employee(s) without a

MANAGER_EMP_ID, you need an outer join:

SELECT E.LNAME EMPLOYEE, M.LNAME MANAGER

FROM EMPLOYEE E, EMPLOYEE M

WHERE E.MANAGER_EMP_ID = M.EMP_ID (+);

8．不相等条件的自连接(Self Non-Equi-Joins)：

assume that you are in charge of organizing interdepartmental basket ball competition within

your company. It is your responsibility to draw the teams and schedule the competition.

SELECT NAME FROM DEPARTMENT;

NAME

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

ACCOUNTING

RESEARCH

SALES

OPERATIONS

Wrong：SELECT D1.NAME TEAM1, D2.NAME TEAM2

FROM DEPARTMENT D1, DEPARTMENT D2

WHERE D1.DEPT_ID != D2.DEPT_ID;

TEAM1 TEAM2

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

RESEARCH ACCOUNTING

SALES ACCOUNTING

OPERATIONS ACCOUNTING

ACCOUNTING RESEARCH

SALES RESEARCH

OPERATIONS RESEARCH

ACCOUNTING SALES

RESEARCH SALES

OPERATIONS SALES

ACCOUNTING OPERATIONS

RESEARCH OPERATIONS

SALES OPERATIONS

In this result set, you have permutations such as (RESEARCH, ACCOUNTING) and (ACCOUNTING,

RESEARCH), and so on. Therefore, each team plays against the others twice. You need to remove

these permutations, which you rightly consider to be duplicates. You think about using

DISTINCT. DISTINCT will not help here, because the row (RESEARCH, ACCOUNTING) is different

from the row (ACCOUNTING, RESEARCH) from the viewpoint of DISTINCT;

(在这个结果集中，我们对诸如(RESEARCH,ACCOUNTING)和(ACCOUNTING,RESEARCH)的记录进行了位置

调换，所以每个队都和其它的队伍进行了两次的比赛。我们必须把那些重复的记录去掉，也许我们会

考虑使用 DISTINCT，但是在这里 DISTINCT 不会起作用，因为像(RESEARCH,ACCOUNTING)和

(ACCOUNTING,RESEARCH)这样的记录以 DISTINCT 的观点来看是两条不同的记录)

Correct：SELECT D1.NAME TEAM1, D2.NAME TEAM2

FROM DEPARTMENT D1, DEPARTMENT D2

WHERE D1.DEPT_ID < D2.DEPT_ID;

TEAM1 TEAM2

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

ACCOUNTING RESEARCH

ACCOUNTING SALES

RESEARCH SALES

评论收藏

内容反馈

0挂树上的鱼

2014-04-19

相当不错的学习资料
goodscript

2013-05-11

资源与描述符合、谢谢楼主

jianzhanger

粉丝: 88
资源: 54

Mastering_Oracle_SQL（含源码）

mastering oracle sql

《Mastering oracle SQL》

Mastering.Oracle.SQL英文第二版及所用到的示例数据表创建脚本

Mastering Oracle SQL and SQL Plus

Mastering_Oracle_SQL学习笔记

Mastering Oracle SQL(2th)

mastering_ros_robot_description_pkg.tar.gz

161204_Mastering_the_FreeRTOS_Real_Time_Kernel-A_Hands-On_Tutorial_Guide

Mastering_Machine_Learning_With_scikit－learn中文、英文

Mastering_the_C 17_STL

Mastering Oracle PL SQL Practical Solutions

Mastering_Elasticsearch_5.x Third

Mastering oracle SQL

Mastering_OpenCV_第六章车牌识别word版本

Mastering_Data_Mining_with_Python_-_Find_patterns_hidden_in_your_dat

2009 Ken_Martin Mastering_CMake.pdf

asterisk_mastering_file1.rar_Mastering Linux

The_Art_of_SEO_Mastering_SEO__1st_Edition_2010.pdf

《Graphics_Java_Mastering_the_JFC》

MASTERING_APACHE_CASSANDRA_3X_THIRD_EDITION.pdf

Mastering.Oracle.SQL.2nd.Edition

Oracle-PL-SQL.zip_oracle

FreeRTOS学习资料(源码，参考资料)

最新资源