SQL编程精粹：Joe Celko's SQL for Smarties第四版无水印PDF

需积分: 10 113 浏览量更新于2024-07-19 收藏 4.66MB PDF 举报

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

领优惠券(最高得80元）

"Joe Celko's SQL for Smarties Advanced SQL Programming(4th) 是一本由知名数据库专家Joe Celko编写的高级SQL编程书籍，旨在帮助读者深入理解并精通SQL语言。这本书是第4版，确保了内容的更新和与时俱进。资源为无水印的英文原版PDF，可在多种PDF阅读器上正常打开。如果存在版权问题，可以联系上传者或相关平台进行处理。欲了解更多信息，建议访问美国亚马逊官网查询该书详情。" 《Joe Celko's SQL for Smarties》系列是SQL学习者和数据库专业人士的经典参考书籍，尤其适合已经有一定SQL基础并寻求提升的读者。第四版涵盖了作者的其他著作，如《Joe Celko's Data, Measurements and Standards in SQL》、《Joe Celko's Information Modeling and Relational Databases, 2nd Edition》等，这些书籍共同构建了一个全面的SQL知识体系。在《Joe Celko's SQL for Smarties: Advanced SQL Programming, 4th》中，作者深入探讨了SQL的高级概念和技巧，包括但不限于： 1. **高级查询技术**：书中可能包含了复杂的联接操作、子查询、窗口函数以及自定义聚合函数等，这些都是在大数据分析和复杂业务逻辑中经常遇到的。 2. **信息建模与关系数据库设计**：Celko可能详细阐述了如何创建有效且适应性强的信息模型，以及如何根据这些模型来设计和优化关系数据库。 3. **性能优化**：讨论了SQL查询的性能瓶颈和优化策略，帮助读者写出更高效的查询语句。 4. **OLAP和数据分析**：可能涵盖了在SQL环境中进行在线分析处理（OLAP）的方法，以及如何利用SQL进行数据挖掘和分析。 5. **标准与合规性**：强调了SQL标准的重要性，并可能讲解了如何遵循SQL92、SQL99、SQL2003等不同版本的标准。 6. **元数据和业务知识**：可能探讨了元数据在数据库管理中的角色，以及如何将业务知识融入到数据库设计中。 7. **问题解答与实践**：书中可能包含了许多SQL谜题和实际案例，帮助读者通过解决具体问题来提高技能。 8. **XML和XQuery**：可能涉及了XML数据的查询，以及如何结合SQL进行XML数据的处理。 9. **数据挖掘与预处理**：可能提到了数据预处理对于数据挖掘的重要性，以及如何使用SQL进行预处理工作。 10. **多维和度量数据结构**：对于数据仓库和商务智能应用，可能讨论了多维数据模型和其在SQL中的实现。通过阅读《Joe Celko's SQL for Smarties》，读者不仅可以提升SQL编程能力，还能深入理解数据库理论和最佳实践，从而在实际工作中更好地利用SQL解决复杂的数据问题。

资源详情

资源推荐

Chapter 1 DATABASES VERSUS FILE SYSTEMS 5

1.3 Rows versus Records

Rows are not records. A record is deﬁned in the application

program that reads it; a row is deﬁned in the database schema

and not by a program at all. The name of the ﬁeld is in the

READ or INPUT statements of the application; a row is named

in the database schema. Likewise, the PHYSICAL order of the

ﬁeld names in the READ statement is vital (READ a, b, c is not

the same as READ c, a, b; but SELECT a, b, c is the same data as

SELECT c, a, b).

All empty files look alike; they are a directory entry in

the operating system with a name and a length of zero bytes

of storage. Empty tables still have columns, constraints, secu-

rity privileges, and other structures, even though they have

no rows.

This is in keeping with the set theoretical model, in which the

empty set is a perfectly good set. The difference between SQL’s

set model and standard mathematical set theory is that set the-

ory has only one empty set, but in SQL each table has a different

structure, so they cannot be used in places where nonempty ver-

sions of themselves could not be used.

Another characteristic of rows in a table is that they are all

alike in structure and they are all the “same kind of thing” in the

model. In a ﬁle system, records can vary in size, data types, and

structure by having ﬂags in the data stream that tell the program

reading the data how to interpret it. The most common exam-

ples are Pascal’s variant record, C’s struct syntax, and COBOL’s

OCCURS clause.

The OCCURS keyword in COBOL and the VARIANT records in

Pascal have a number that tells the program how many times a

subrecord structure is to be repeated in the current record.

Unions in C are not variant records, but variant mappings for

the same physical memory. For example:

union x {int ival; char j[4];} mystuff;

deﬁnes mystuff to be either an integer (which is 4 bytes on most

C compilers, but this code is nonportable) or an array of 4 bytes,

depending on whether you say mystuff.ival or mystuff.j[0];.

But even more than that, ﬁles often contained records that

were summaries of subsets of the other records—so-called

control break reports. There is no requirement that the records

in a ﬁle be related in any way—they are literally a stream

of binary data whose meaning is assigned by the program

reading them.

6 Chapter 1 DATABASES VERSUS FILE SYSTEMS

1.4 Columns versus Fields

A ﬁeld within a record is deﬁned by the application program that

reads it. A column in a row in a table is deﬁned by the database

schema. The data types in a column are always scalar.

The order of the application program variables in the READ

or INPUT statements is important because the values are read

into the program variables in that order. In SQL, columns are ref-

erenced only by their names. Yes, there are shorthands like the

SELECT * clause and INSERT INTO <table name> statements,

which expand into a list of column names in the physical order in

which the column names appear within their table declaration,

but these are shorthands that resolve to named lists.

The use of NULLs in SQL is also unique to the language.

Fields do not support a missing data marker as part of the ﬁeld,

record, or ﬁle itself. Nor do ﬁelds have constraints that can be

added to them in the record, like the DEFAULT and CHECK()

clauses in SQL.

Files are pretty passive creatures and will take whatever an

application program throws at them without much objection.

Files are also independent of each other simply because they are

connected to one application program at a time and therefore

have no idea what other ﬁles look like.

A database actively seeks to maintain the correctness of all its

data. The methods used are triggers, constraints, and declarative

referential integrity.

Declarative referential integrity (DRI) says, in effect, that data

in one table has a particular relationship with data in a second

(possibly the same) table. It is also possible to have the database

change itself via referential actions associated with the DRI. For

example, a business rule might be that we do not sell products

that are not in inventory.

This rule would be enforced by a REFERENCES clause on the

Orders table that references the Inventory table, and a referen-

tial action of ON DELETE CASCADE. Triggers are a more general

way of doing much the same thing as DRI. A trigger is a block of

procedural code that is executed before, after, or instead of an

INSERT INTO or UPDATE statement. You can do anything with a

trigger that you can do with DRI and more.

However, there are problems with TRIGGERs. Although there

is a standard syntax for them since the SQL-92 standard, most

vendors have not implemented it. What they have is very propri-

etary syntax instead. Second, a trigger cannot pass information to

the optimizer like DRI. In the example in this section, I know that

for every product number in the Orders table, I have that same

Chapter 1 DATABASES VERSUS FILE SYSTEMS 7

product number in the Inventory table. The optimizer can use

that information in setting up EXISTS() predicates and JOINs in

the queries. There is no reasonable way to parse procedural trig-

ger code to determine this relationship.

The CREATE ASSERTION statement in SQL-92 will allow the

database to enforce conditions on the entire database as a whole.

An ASSERTION is not like a CHECK() clause, but the difference is

subtle. A CHECK() clause is executed when there are rows in the

table to which it is attached.

If the table is empty then all CHECK() clauses are effectively

TRUE. Thus, if we wanted to be sure that the Inventory table is

never empty, and we wrote:

CREATE TABLE Inventory

( . . .

CONSTRAINT inventory_not_empty

CHECK ((SELECT COUNT(*) FROM Inventory) > 0),

. . . );

but it would not work. However, we could write:

CREATE ASSERTION Inventory_not_empty

CHECK ((SELECT COUNT(*) FROM Inventory) > 0);

and we would get the desired results. The assertion is checked at

the schema level and not at the table level.

1.5 Schema Objects

A database is not just a bunch of tables, even though that is where

most of the work is done. There are stored procedures, user-deﬁned

functions, and cursors that the users create. Then there are indexes

and other access methods that the user cannot access directly.

This chapter is a very quick overview of some of the schema

objects that a user can create. Standard SQL divides the database

users into USER and ADMIN roles. These objects require ADMIN

privileges to be created, altered, or dropped. Those with USER

privileges can invoke them and access the results.

1.6 CREATE SCHEMA Statement

The CREATE SCHEMA statement deﬁned in the standards brings

an entire schema into existence all at once. In practice, each

product has very different utility programs to allocate physical

storage and deﬁne a schema. Much of the proprietary syntax is

concerned with physical storage allocations.

8 Chapter 1 DATABASES VERSUS FILE SYSTEMS

A schema must have a name and a default character set.

Years ago, the default character set would have been ASCII or

a local alphabet (8 bits) as deﬁned in the ISO standards. Today,

you are more likely to see Unicode (16 bits). There is an optional

AUTHORIZATION clause that holds a <schema authorization

identiﬁer> for security. After that the schema is a list of schema

elements:

<schema element> ::=

| <grant statement> | <assertion deﬁnition>

| <character set deﬁnition>

| <collation deﬁnition> | <translation deﬁnition>

A schema is the skeleton of an SQL database; it deﬁnes the

structures of the schema objects and the rules under which they

operate. The data is the meat on that skeleton.

The only data structure in SQL is the table. Tables can be per-

sistent (base tables), used for working storage (temporary tables),

or virtual (VIEWs, common table expressions and derived tables).

The differences among these types are in implementation, not

performance. One advantage of having only one data structure is

that the results of all operations are also tables—you never have

to convert structures, write special operators, or deal with any

irregularity in the language.

The <grant statement> has to do with limiting access by users

to only certain schema elements. The <assertion deﬁnition> is still

not widely implemented yet, but it is like constraint that applies

to the schema as a whole. Finally, the <character set deﬁnition>,

< collation deﬁnition>, and <translation deﬁnition> deal with

the display of data. We are not really concerned with any of these

schema objects; they are usually set in place by the database

administrator (DBA) for the users and we mere programmers do

not get to change them.

Conceptually, a table is a set of zero or more rows, and a row

is a set of one or more columns. This hierarchy is important;

actions apply at the schema, table, row, or column level. For

example the DELETE FROM statement removes rows, not col-

umns, and leaves the base table in the schema. You cannot delete

a column from a row.

Each column has a speciﬁc data type and constraints that

make up an implementation of an abstract domain. The way a

table is physically implemented does not matter, because you

access it only with SQL. The database engine handles all the

details for you and you never worry about the internals as you

would with a physical ﬁle. In fact, almost no two SQL products

use the same internal structures.

Chapter 1 DATABASES VERSUS FILE SYSTEMS 9

There are two common conceptual errors made by program-

mers who are accustomed to ﬁle systems or PCs. The ﬁrst is

thinking that a table is a ﬁle; the second is thinking that a table

is a spreadsheet. Tables do not behave like either one of these,

and you will get surprises if you do not understand the basic

concepts.

It is easy to imagine that a table is a ﬁle, a row is a record, and

a column is a ﬁeld. This is familiar and when data moves from

SQL to the host language, it has to be converted into host lan-

guage data types and data structures to be displayed and used.

The host languages have ﬁle systems built into them.

The big differences between working with a ﬁle system and

working with SQL are in the way SQL ﬁts into a host program.

Using a ﬁle system, your programs must open and close ﬁles

individually. In SQL, the whole schema is connected to or dis-

connected from the program as a single unit. The host program

might not be authorized to see or manipulate all the tables

and other schema objects, but that is established as part of the

connection.

The program deﬁnes ﬁelds within a ﬁle, whereas SQL deﬁnes

its columns in the schema. FORTRAN uses the FORMAT and

READ statements to get data from a ﬁle. Likewise, a COBOL pro-

gram uses a Data Division to deﬁne the ﬁelds and a READ to

fetch it. And so on for every 3GL’s programming; the concept is

the same, though the syntax and options vary.

A ﬁle system lets you reference the same data by a differ-

ent name in each program. If a ﬁle’s layout changes, you must

rewrite all the programs that use that ﬁle. When a ﬁle is empty,

it looks exactly like all other empty ﬁles. When you try to read an

empty ﬁle, the EOF (end of ﬁle) ﬂag pops up and the program

takes some action. Column names and data types in a table are

deﬁned within the database schema. Within reasonable limits,

the tables can be changed without the knowledge of the host

program.

The host program only worries about transferring the values

to its own variables from the database. Remember the empty

set from your high school math class? It is still a valid set. When

a table is empty, it still has columns, but has zero rows. There

is no EOF ﬂag to signal an exception, because there is no ﬁnal

record.

Another major difference is that tables and columns can have

constraints attached to them. A constraint is a rule that deﬁnes

what must be true about the database after each transaction. In

this sense, a database is more like a collection of objects than a

traditional passive ﬁle system.

剩余797页未读，继续阅读

yinkaisheng-nj

粉丝: 762
资源: 6231

SQL编程精粹：Joe Celko's SQL for Smarties第四版无水印PDF

Oracle PL SQL Programming 5th Edition(含源码)

MK.Joe.Celkos.Trees.and.Hierarchies.in.SQL.for.Smarties

Joe Celko’s Trees and Hierarchies in SQL for Smarties

数据库课程设计参考文献

纯数据库设计的参考文献

arraylist线程冲突案例

sqlite3数据库学习参考书

Advanced SQL Programming

Joe Celko's Trees and Hierarchies in SQL for Smarties 电子书

Sql 经典作品，Joe Celko 大牛

非常领先的分布式数据库Oceanbase OBCP 3.0培训教材

GCN海面目标检测.zip

算法模板，最科学的刷题方式，最快速的刷题路径，你值得拥有~.zip

全球与中国绿色螯合剂市场现状及未来发展趋势（2024版）.docx

中秋主题动态网页，学习css动画与js动画技术

Avatar_Utils-1.2.2-py3-none-any.whl.zip

2024-2030全球与中国企业计费解决方案市场现状及未来发展趋势 Sample-lv.pdf

Avatar_Utils-1.3.5-py3-none-any.whl.zip

Avatar_Utils-1.8.5-py3-none-any.whl.zip

AwesomeTkinter-2021.4.1-py3-none-any.whl.zip

最新资源