Oracle SQL与SQL*Plus深度探索

需积分: 9 61 浏览量更新于2024-08-01 收藏 3.41MB PDF 举报

"Mastering Oracle SQL and SQL Plus 是一本由Lex de Haan编写的关于Oracle数据库技术的专业书籍，主要关注Oracle 10g的SQL和SQL*Plus的掌握。该书版权于2005年授予作者，旨在为读者提供Oracle 10g的基础教程，介绍其体系结构和配置技术。" 在Oracle SQL方面，这本书可能涵盖了以下几个关键知识点： 1. **SQL基础**：SQL（Structured Query Language）是用于管理关系数据库的标准语言。书中可能详细介绍了如何创建、修改和查询表，以及使用DML（Data Manipulation Language）进行插入、更新和删除操作。 2. **SQL高级特性**：包括联接（JOINs）、子查询、集合操作（UNION, INTERSECT, EXCEPT）以及窗口函数（Window Functions），这些功能在复杂数据处理和分析中非常有用。 3. **SQL*Plus工具**：SQL*Plus是Oracle数据库管理员和开发人员常用的命令行接口，用于执行SQL语句、脚本和管理数据库。书中可能教授如何使用SQL*Plus进行数据库交互，如运行SQL脚本、设置变量、格式化输出等。 4. **Oracle 10g体系结构**：这部分可能涉及Oracle数据库的组件，如数据文件、控制文件、重做日志文件、实例和进程等。读者将了解这些组件如何协同工作以保证数据库的正常运行和数据的完整性。 5. **性能优化**：Oracle 10g的查询优化器（Query Optimizer）和索引策略可能是重点，书中可能讲解如何编写高效的SQL语句，以及如何利用 Explain Plan 分析查询性能。 6. **安全性**：包括用户权限管理、角色、对象权限和系统权限的分配，以及如何通过SQL来实施安全策略。 7. **备份与恢复**：介绍Oracle的备份策略，如物理备份、逻辑备份，以及如何使用RMAN（Recovery Manager）进行数据恢复。 8. **PL/SQL编程**：PL/SQL是Oracle特有的过程式语言，与SQL结合使用，用于编写存储过程、函数、触发器等。书中可能包含编写PL/SQL代码的基本知识。 9. **故障诊断与解决**：可能包含监控工具的使用，如V$视图，以及如何识别和解决常见的数据库问题。 10. **数据库设计与建模**：介绍如何使用ER（实体关系）模型设计数据库，并转换为Oracle的物理模式。此书适合对Oracle数据库感兴趣的初学者和中级开发者，通过详细的教学内容，帮助读者深入了解Oracle 10g的使用和管理，提升SQL技能，以便在实际工作中更好地运用Oracle数据库。

Acknowledgments

Iwant to thank many friends who contributed to the quality of this book by reviewing it and

providing their feedback. Cary Millsap and Jocke Treugut, two good friends and members of the

OakTable network, were my main reviewers. Cary helped me with his constant focus on “doing

things right” from the very beginning, and Jocke helped me find the right balance between the-

ory and practice. Martin Jensen, one of my good old friends inside Oracle and an Oakie as well,

provided precisely the feedback I needed from his impressive Oracle consulting background.

Stephen Cannan, my colleague in the Dutch national body for the SQL Standardization and the

convenor of the international ISO/IEC/JTC1/SC32/WG3 committee, commented on my draft

chapters based on his vast experience in the SQL standardization area.

Kristina Youso, a former colleague and good friend from my years in Global Curriculum

Development in Oracle and one of the best content editors I have ever worked with, was so

kind to check and improve my English language.

Last, but not least, I must mention the professionalism and enthusiasm of all the Apress

folks involved in the production of this book: Tony Davis, Beckie Stones, Marilyn Smith, and

Kelly Winquist. Thanks folks . . .

My two daughters are too old to be mentioned here, the cat was not involved in any way,

and I leave it up to Mogens Nørgaard to say something nice about my wife, Juliette.

Reactions to this book are more than welcome; send your feedback or questions to the

publisher, or via e-mail to the author.

Lex de Haan

http://www.naturaljoin.nl

E-mail: lex.de.haan@naturaljoin.nl

Introduction

This book is a translation and enhancement of the third edition of a book I wrote about SQL

in Dutch. The first edition was published in February 1993, the second edition in April 1998,

and I finished the third edition to reflect Oracle Database 10g in the summer of 2004. I always

thought that there were more than enough books in English about the SQL language out there

already, but finally, some good friends convinced me to publish an English version of my book.

I hate thick books. I start reading them, put them aside on a certain pile on my desk, from

where they are purged every now and then (if the pile becomes too high), without being read

to the end. Therefore, in my own book, I have tried to be as concise as possible.

About This Book

This is not a book about advanced SQL. It is not a book about the Oracle optimizer and diagnos-

tic tools. And it is not a book about relational calculus, predicate logic, or set theory. This book is

a SQL primer. It is meant to help you learn Oracle SQL by yourself. It is ideal for self-study, but it

can also be used as a guide for SQL workshops and instructor-led classroom training.

This is a practical book; therefore, you need access to an Oracle environment for hands-

on exercises. All the software that you need to install Oracle Database 10g on Microsoft

Windows and to create an Oracle database is available from the CD-ROM included with this

book. This book is based on the following Oracle release:

• Oracle Database 10g for Windows (or Red Hat Linux) Release 10.1.0.x

Although this book assumes an Oracle Database 10g environment, you can also use it

with Oracle9i or even with Oracle8i. However, Oracle is adding new SQL syntax with every

new release; therefore, some SQL syntax examples could fail when issued against these earlier

releases. You can check this yourself by querying the online Oracle documentation. Oracle

SQL Reference offers a section titled “Oracle Database 10g New Features in the SQL Reference”

at the end of the introduction, preceding Chapter 1.

I follow the ANSI/ISO standard (SQL:2003) as much as possible. Only in cases of useful

Oracle-specific SQL extensions do I deviate from this international standard. Therefore, most

SQL examples given in this book are probably also valid for other database management sys-

tem (DBMS) implementations supporting the SQL language. By the way, Oracle SQL Reference

contains an Appendix B, “Oracle and Standard SQL,” discussing the differences between the

ANSI/ISO SQL standard and the Oracle SQL implementation.

xvii

The SQL and SQL*Plus commands are explained with concrete examples. The examples

are presented clearly in a listing format, as in the example shown here.

Listing I-1. A SQL SELECT Command

SQL> select 'Hello world!'

2 from dual;

I focus on the main points, avoiding peripheral issues and technical details as much as

possible.

This book does not intend (nor pretend) to be complete; the SQL language is too volumi-

nous and the Oracle environment is much too complex. Oracle SQL Reference contains about

1,800 pages these days, and even Oracle SQL Quick Reference is not really a small document,

with its 170 pages. Moreover, the current ANSI/ISO SQL standard documentation has grown

to a size that simply is not printable anymore.

The main objective of this book is the combination of usability and affordability. The offi-

cial Oracle documentation offers detailed information in case you need it. Therefore, it is a

good idea to have the Oracle manuals available while working through the examples and exer-

cises in this book. The Oracle documentation is available online on the Oracle Technology

Network (http://www.oracle.com/technology/documentation) and can be downloaded from

there (if you don’t want to keep an Internet connection open all the time).

The focus of this book is using SQL for data retrieval. Data definition and data manipulation

are covered in less detail. Security, authorization, and database administration are mentioned

only for the sake of completeness in the SQL overview section in Chapter 2.

Throughout the book, we use a case consisting of seven tables. These seven tables contain

information about employees, departments, and courses. As Chris Date, a well-known guru in

the professional database world (see Appendix E for references to some of the great books he

wrote), said during one of his seminars, “There are only three databases: employees and

departments, orders and line items, and suppliers and shipments.”

The cardinality of the case tables is deliberately kept low. This enables you to check the

results of your SQL commands manually, which is nice while you’re learning to master the SQL

language. In general, checking your results manually is impossible in real information systems,

due to the volume of data in such systems. It is not the data volume or query response time that

matters in this book. What’s important is the database structure complexity and SQL statement

correctness. By the way, the two case tables EMPLOYEES and DEPARTMENTS show a striking resem-

blance to good old SCOTT.EMP and SCOTT.DEPT, two of the Oracle demo tables that have been

shipped with Oracle pretty much from the very beginning.

About the Chapters of This Book

Chapter 1 provides a concise introduction to the theoretical background of information sys-

tems and some popular database terminology, and then continues with a global overview of

the Oracle software and an introduction to the seven case tables. If you really don’t like theory

and you want to get started with SQL as soon as possible, you could skip this chapter almost

■INTRODUCTIONxviii

entirely and start reading about the case tables in Section 1.9. However, I think Chapter 1 con-

tains a lot of important and useful information. If you skip it, you might want to revisit it later.

Chapter 2 starts with a high-level overview of the SQL language, followed by an introduc-

tion to SQL*Plus and iSQL*Plus, the two most obvious environments to execute SQL

statements interactively. In Chapter 11, we revisit SQL*Plus. That chapter covers some more

advanced SQL*Plus features, such as using substitution variables, stored scripts, reporting,

and working with HTML.

Data definition is covered in two nonconsecutive chapters: Chapter 3 and Chapter 7. This

is done to allow you to start with SQL retrieval as soon as possible. Therefore, Chapter 3 covers

only the most basic data-definition concepts (tables, datatypes, and the data dictionary).

Retrieval is also spread over multiple chapters—four chapters, to be precise. Chapter 4

focuses on the SELECT, WHERE, and ORDER BY clauses of the SELECT statement. The most impor-

tant SQL functions are covered in Chapter 5, which also covers null values and subqueries. In

Chapter 8, we start accessing multiple tables at the same time (joining tables) and aggregating

query results; in other words, the FROM, the GROUP BY, and the HAVING clauses get our attention

in that chapter. To finish the coverage of data retrieval with SQL, Chapter 9 revisits subqueries

to show some more advanced subquery constructs. That chapter also introduces windows

and analytical functions, hierarchical queries, and flashback features.

■Note From Chapter 4 onwards, all chapters except Chapter 6 end with a set of exercises. The answers to

these exercises are in Appendix D.

Chapter 6 discusses data manipulation with SQL. The commands INSERT, UPDATE, DELETE,

and MERGE are introduced. This chapter also pays attention to some topics related to data

manipulation: transaction processing, read consistency, and locking.

In Chapter 7, we revisit data definition, to drill down into constraints, indexes, sequences,

and performance. Synonyms are explained in the same chapter. Chapters 8 and 9 continue

coverage of data retrieval with SQL.

Chapter 10 introduces views. What are views, when should you use them, and what are

their restrictions? This chapter explores the possibilities of data manipulation via views, dis-

cusses views and performance, and introduces materialized views.

Chapter 11 is a continuation of Chapter 2, covering more advanced SQL*Plus and

iSQL*Plus features.

Oracle is an object-relational database management system. Since Oracle8, many object-

oriented features have been added to the SQL language. As an introduction to these features,

Chapter 12 provides a high-level overview of user-defined datatypes, arrays, nested tables,

and multiset operators.

The five appendices at the end of this book offer a SQL*Plus and SQL quick reference, an

overview of the Oracle data dictionary, a description of the structure and contents of the seven

case tables, the answers to the exercises, and references to other sources of information.

■INTRODUCTION

xix

剩余488页未读，继续阅读

focus_32huishou

粉丝: 0
资源: 2

Oracle SQL与SQL*Plus深度探索

Mastering Oracle SQL(2th)

《Mastering oracle SQL》

mastering oracle sql

Mastering Oracle SQL and SQL Plus

Oracle SQL*Plus Pocket Reference, 2nd Edition

Essentials.Oracle.Database.11g

老方块 ORACLE 教学笔记 整理.

oracle-essentials-database 11g

"SQLOracle-10g数据库基础教程：SQL*Plus工具介绍与常用命令

Oracle 11g精华：扩展与管理革新指南

最新资源

老方块 ORACLE 教学笔记整理.