Oracle SQL入门指南

需积分: 31 92 浏览量更新于2024-07-18 收藏 8.06MB PDF 举报

" Beginning Oracle SQL 3rd 是一本适合初学者的SQL入门书籍，专注于帮助读者自学Oracle SQL。书中不涉及高级SQL、优化器或诊断工具，也不深入探讨关系代数、谓词逻辑或集合论。这是一本实践导向的指南，需要读者拥有Oracle环境进行动手练习。所有用于在Windows或Linux上安装Oracle数据库以供学习的软件都可以从Oracle Technology Network (OTN)免费获取。本书包括多个章节，涵盖了关系数据库系统、SQL基础知识、数据定义、查询基础、函数、数据操纵、视图、SQL*Plus基础和脚本以及对象关系特性等内容。此外，还有案例表格和习题答案的附录。" 本书的内容结构清晰，从基础概念开始，逐步引导读者掌握Oracle SQL。第一章"关系数据库系统和Oracle"介绍了数据库的基本原理和Oracle在其中的角色。第二章"SQL和SQL Developer的介绍"让读者熟悉SQL语言和Oracle的图形化工具。第三章和第七章分别深入讨论数据定义的第一部分和第二部分，涉及表和索引的创建与管理。第四章至第六章关注数据检索，涵盖基本查询、函数的使用以及数据的增删改操作。第八章至第十章讲解多表查询、聚合函数以及一些高级查询特性，如连接、子查询和分组。第十一章"SQL*Plus基础和脚本"介绍了命令行工具的使用和自动化脚本编写。第十二章介绍了Oracle中的对象关系特性，如索引组织表和对象类型。此外，书中的每个章节都设计了实践练习，帮助读者巩固所学知识。附录A提供了七个用于练习的案例表格，而附录B则给出了所有习题的答案，方便读者自我检查和学习。通过阅读和实践这本书，读者不仅可以掌握Oracle SQL的基础，还能获得在实际环境中应用SQL解决问题的能力。无论你是准备自我学习还是参加SQL工作坊或课堂教学，"Beginning Oracle SQL 3rd"都是一个理想的选择。

CHAPTER 1 ■ RELATIONAL DATABASE SYSTEMS AND ORACLE

1.5 Relational Data Structures

This section introduces the most important relational data structures and concepts:

Tables, columns, and rows•

The information principle•

Datatypes•

Keys•

Missing information and null values•

Tables, Columns, and Rows

The central concept in relational data structures is the table or relation (from which the relational model derives its

name). A table is defined as a set of rows, or tuples (pronounced like couples). The rows of a table share the same set

of attributes; a row consists of a set of (attribute name; attribute value) pairs. All data in a relational database is

represented as column values within table rows.

In summary, the basic relational data structures are as follows:

A database, which is a set of tables•

A table, which is a set of rows•

A row, which is a set of column values •

The definition of a row is a little imprecise. A row is not just a set of column values. A more precise definition

would be as follows:

A • row is a set of ordered pairs, where each ordered pair consists of an attribute name with an

associated attribute value.

For example, the following is a formal and precise way to represent a row from the DEPARTMENTS table:

{(deptno;40),(dname;HR),(location;Boston),(mgr;7839)}

This row represents department 40: the HR department in Boston, managed by employee 7839. It would become

irritating to represent rows like this; therefore, this book will use less formal notations as much as possible. After all,

the concept of tables, rows, and columns is rather intuitive.

In most cases, there is a rather straightforward one-to-one mapping between the entities of the data model and the

tables in a relational database. The rows represent the occurrences of the corresponding entity, and the column headings

of the table correspond with the attributes of that entity. See Figure 1-1 for an illustration of the DEPARTMENTS table.

CHAPTER 1 ■ RELATIONAL DATABASE SYSTEMS AND ORACLE

The Information Principle

The only way you can associate data in a relational database is by comparing column values. This principle, known as

the information principle, is applied very strictly, and it is at the heart of the term relational.

An important property of relational datasets is the fact that the order of their elements is meaningless. Therefore,

the order of the rows in any relational table is meaningless, too, and the order of columns is also meaningless.

Because this is both very fundamental and important, let’s rephrase this in another way: in a relational

database, there are no pointers to represent relationships. For example, the fact that an employee works for a specific

department can be derived only from the two corresponding tables by comparing column values in, for example, the

two department number columns. In other words, for every retrieval command, you must explicitly specify which

columns must be compared. As a consequence, the flexibility to formulate ad hoc queries in a relational database

has no limits. The flip side of the coin is the risk of (mental) errors and the problem of the correctness of your results.

Nearly every SQL query will return a result (as long as you don’t make syntax errors), but determining whether it is

really the answer to the question you had in mind is up to you.

Datatypes

One of the tasks during data modeling is to decide which values are allowed for each attribute. You could allow only

numbers in a certain column, or allow only dates or text. You can impose additional restrictions, such as by allowing

only positive integers or text of a certain maximum length.

A set of allowed attribute values is sometimes referred to as a domain. Another common term is datatype

(or simply type). Each attribute is defined to be of a certain type. This can be a standard (built-in) type or a

user-defined type.

Keys

Each relational table must have at least one candidate key. A candidate key consists of an attribute (or attribute

combination) that uniquely identifies each row in that table, with one additional important property: as soon as you

remove any attribute from this candidate key attribute combination, the property of unique identification is gone. In

other words, a table cannot contain two rows with the same candidate key values at any time and still maintain row

uniqueness.

For example, the attribute combination course CODE and BEGINDATE is a candidate key for a table containing

information about course offerings. If you remove the BEGINDATE attribute, the remaining course CODE attribute is not

a candidate key anymore; otherwise, you could offer courses only once. If you remove the course CODE attribute, the

remaining BEGINDATE attribute is not a candidate key anymore; otherwise, you would never be able to schedule two

different courses to start on the same day.

Figure 1-1. The DEPARTMENTS table

CHAPTER 1 ■ RELATIONAL DATABASE SYSTEMS AND ORACLE

In case a table has multiple candidate keys, it is normal practice to select one of them to become the primary key.

All components (attributes) of a primary key are mandatory; you must specify attribute values for all of them. Primary

keys enforce a very important table constraint: entity integrity.

Sometimes, the set of candidate keys doesn’t offer a convenient primary key. In such cases, you may choose a

surrogate key by adding a meaningless attribute with the sole purpose of being the primary key.

Note ■ The use of surrogate keys comes with advantages and disadvantages, as well as fierce debates between

database experts. The intent of this section is to explain the terminology, without offering an opinion on the use of

surrogate keys.

A relational table can also have one or more foreign keys. Foreign key constraints are subset requirements; the

foreign key values must always be a subset of a corresponding set of primary key values. Some typical examples of

foreign key constraints are that an employee can work for only an existing department and can report to only an

existing manager. Foreign keys implement referential integrity in a relational database.

Missing Information and Null Values

A relational DBMS is supposed to treat missing information in a systematic and context-insensitive manner. If a value

is missing for a specific attribute of a row, it is not always possible to decide whether a certain condition evaluates to

true or false. Missing information is represented by null values in the relational world.

The term null value is actually misleading, because it does not represent a value; it represents the fact that a value

is missing. For example, null marker would be more appropriate. However, null value is the term most commonly used,

so this book uses that terminology. Figure 1-2 shows how null values appear in a partial listing of the EMPLOYEES table.

Figure 1-2. Nulls represent missing values

Null values imply the need for a three-valued logic, such as implemented (more or less) in the SQL language. The

third logical value is unknown.

Note ■ Null values have had strong opponents and defenders. For example, Chris Date is a well-known opponent of

null values and three-valued logic. His articles about this subject are highly readable, entertaining, and clarifying.

CHAPTER 1 ■ RELATIONAL DATABASE SYSTEMS AND ORACLE

Constraint Checking

Although most RDBMS vendors support integrity constraint checking in the database these days (Oracle implemented

this feature a number of years ago), it is sometimes also desirable to implement constraint checking in client-side

database applications. Suppose you have a network between a client-side data-entry application and the database,

and the network connection is a bottleneck. In that case, client-side constraint checking probably results in much

better response times, because there is no need to access the database each time to check the constraints.

Code-generating tools typically allow you to specify whether constraints should be enforced at the database side,

the client side, or both sides.

Caution ■ If you implement certain constraints in your client-side applications only, you risk database users bypassing

the corresponding constraint checks by using alternative ways to connect to the database.

Predicates and Propositions

To finish this section about relational data structures, there is another interesting way to look at tables and rows

in a relational database from a completely different angle, as introduced by Hugh Darwen. This approach is more

advanced than the other topics addressed in this chapter, so you might want to revisit this section later.

You can associate each relational table with a table predicate and all rows of a table with corresponding

propositions. Predicates are logical expressions, typically containing free variables, which evaluate to true or false.

For example, this is a predicate:

There is a course with title • T and duration D, price P, frequency F, and a maximum number of

attendees M.

If we replace the five variables in this predicate (T, D, P, F, and M) with actual values, the result is a proposition.

In logic, a proposition is a predicate without free variables; in other words, a proposition is always true or false. This

means that you can consider the rows of a relational table as the set of all propositions that evaluate to true.

Relational Data Structure Terms Review

In this section, the following terms were introduced:

Tables (or relations)•

Rows (or tuples)•

Columns and domains•

Candidate, primary, and foreign keys•

Integrity checking at the database level•

Missing information, null values, and three-valued logic•

Predicates and propositions•

1.6 Relational Operators

To manipulate data, you need operators that can be applied to that data. Multiplication and addition are typical

examples of operators in mathematics; you specify two numbers as input, and the operator produces one output value

as a result. Multiplication and addition are examples of closed operators, because they produce “things” of the same

CHAPTER 1 ■ RELATIONAL DATABASE SYSTEMS AND ORACLE

type you provided as input (numbers). For example, for integers, addition is closed. Add any two integers, and you get

another integer. Try it—you can’t find two integers that add up to a noninteger. However, division over the integers is

not closed; for example, 1 divided by 2 is not an integer. Closure is a nice operator property, because it allows you to

(re)use the operator results as input for a next operator’s operation.

In a database environment, you need operators to derive information from the data stored in the database. In an

RDBMS environment, all operators should operate at a high logical level. This means, among other things, that they should

not operate on individual rows, but rather on tables, and that the results of these operations should be tables, as well.

Because tables are defined as sets of rows, relational operators should operate on sets. That’s why some operators

from the classical set theory—such as the union, the difference, and the intersection—also show up as relational

operators. See Figure 1-3 for an illustration of these three set operators.

Figure 1-3. The three most common set operators

Along with these generic operators from set theory that can be applied to any sets, there are some additional

relational operators specifically meant to operate on tables. You can define as many relational operators as you

like, but, in general, most of these operators can be reduced to (or built with) a limited number of basic relational

operators. The most common relational operators are the following:

• Restriction: This operator results in a subset of the rows of the input table, based on a

specified restriction condition. This operator is also referred to as selection.

• Projection: This operator results in a table with fewer columns, based on a specified set of

attributes you want to see in the result. In other words, the result is a vertical subset of the

input table.

• Union: This operator merges the rows of two input tables into a single output table; the result

contains all rows that occur in at least one of the input tables.

• Intersection: This operator also accepts two input tables; the result consists of all rows that

occur in both input tables.

• Minus: Again, based on two input tables, this operator produces a result that consists of those

rows that occur in the first table but do not occur in the second table. Note that this operator is not

symmetric; A MINUS B is not the same as B MINUS A. This operator is also referred to as difference.

剩余428页未读，继续阅读

wangbo_thu

粉丝: 0
资源: 2

Oracle SQL入门指南

Oracle SQL入门指南：第3版精华概览

Oracle SQL入门指南：奠定成功基石

Beginning Oracle SQL, 3rd Edition

Beginning Oracle SQL for Oracle Database 12c(3rd) mobi

Beginning Oracle SQL for Oracle Database 12c(3rd) epub

Beginning Hibernate, 3rd Edition

Beginning SQL Server 2012 for Developers, 3rd Editiona

Python项目-自动办公-56 Word_docx_格式套用.zip

《松鼠》生态性课堂体验教案.docx

Java系统源码+智慧图书管理系统

最新资源