Oracle数据库入门：SQL与SQL Developer指南

需积分: 9 139 浏览量更新于2024-07-21 1 收藏 8.16MB PDF 举报

" Beginning Oracle SQL - For Oracle Database 12c.pdf" 这本《Beginning Oracle SQL - For Oracle Database 12c》是针对Oracle数据库初学者的一份指南，旨在介绍Oracle数据库系统以及SQL语言的基础知识。Oracle数据库是全球广泛使用的、功能强大的关系型数据库管理系统，而SQL（Structured Query Language）则是用于管理和操作数据库的标准编程语言。书中涵盖了一系列主题，从关系数据库系统和Oracle的基础概念开始，逐步深入到SQL和SQL Developer的介绍。SQL Developer是Oracle提供的一个图形化工具，方便用户进行数据库查询、开发和管理。在数据定义部分，作者分别在第3章和第7章中讨论了如何创建和管理数据库对象，如表、索引、视图等。这对于理解数据库结构和组织至关重要。第4章至第9章则涵盖了数据检索的各种技巧，包括基本查询、函数应用、多表查询、聚合函数以及一些高级特性，如子查询和连接。第10章专门讲解了视图的概念，视图是数据库中的虚拟表，能够简化复杂查询并提供数据的安全访问层。第11章介绍了SQL*Plus的基本用法和脚本编写，SQL*Plus是Oracle提供的命令行工具，用于执行SQL语句和管理数据库。在第12章，作者引入了Oracle的面向对象特性，这是Oracle数据库区别于其他传统关系型数据库的一个显著特点，它允许创建更复杂的数据类型和对象。附录部分提供了练习的答案和七个案例表格，帮助读者巩固所学知识，并通过实际操作来加深理解。这本书适合那些对Oracle数据库感兴趣，希望通过学习SQL语言来管理和操作数据的读者。无论你是数据库管理员、开发者还是数据分析人员，都能从中受益。通过本书的学习，你可以掌握Oracle数据库的基本操作，从而更好地利用这一强大的数据库平台进行数据管理和分析。

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页未读，继续阅读

kbardool

粉丝: 0
资源: 1

Oracle数据库入门：SQL与SQL Developer指南

oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm

oracle-instantclient-basic-nt-12.2.0.1.0

Matthew Morris -1Z0-071_ Oracle Database 12c SQL_ Oracle Certification.pdf

Oracle Database 11g Oracle In-Memory Database Release-4.pdf

press.RMAN.Recipes.for.Oracle.Database.12c.2nd.Edition

Oracle SQL Developer-18.2.0.183.1748

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

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

instantclient-basic-windows.x64-12.1.0.2.0-oracle12c.zip

Linux-Oracle-12c-RAC安装配置v1.0.pdf

最新资源