理解SQL与关系理论：写出准确的SQL代码

需积分: 13 6 浏览量更新于2024-07-26 收藏 3.03MB PDF 举报

"SQL与关系理论，作者C.J.Date，探讨如何编写准确的SQL代码，以及相关的O'Reilly出版社的IT资源" 《SQL与关系理论》是C.J.Date撰写的一本深入探讨SQL语言与关系理论的著作。这本书的核心目标是帮助读者理解如何编写出准确、高效的SQL代码，从而在数据库管理及数据查询中实现最佳实践。C.J.Date是数据库领域的权威专家，他的作品通常以严谨的理论基础和实用的编程指导相结合，深受业内专业人士的推崇。 SQL，全称Structured Query Language，是一种用于管理关系数据库的标准语言，广泛应用于各种数据库管理系统，如MySQL、Oracle、SQL Server等。关系理论，是数据库设计的基础，它由E.F.科德提出的“关系模型”构成，强调数据的结构化存储和规范化处理，以确保数据的一致性和完整性。在书中，Date可能会讨论以下主题： 1. SQL语法基础：包括数据定义（DDL）、数据操作（DML）和数据控制（DCL）语句，如CREATE TABLE、INSERT、SELECT、UPDATE、DELETE等。 2. 关系代数和元组关系演算：这些是理论基础，用于理解SQL查询的执行机制。 3. 数据库规范化：介绍第一范式（1NF）、第二范式（2NF）、第三范式（3NF）以及BCNF（巴斯-科德范式），帮助优化数据库设计，减少数据冗余和更新异常。 4. SQL高级特性：如子查询、联接（JOIN）、视图（VIEW）、索引、事务处理等，以及如何有效利用它们来提高查询性能。 5. SQL与关系理论的关联：解释SQL语言如何体现和偏离关系理论，以及这种差异对实际应用的影响。此外，O'Reilly出版社还提供了其他与SQL和数据库相关的书籍资源，如《The Art of SQL》专注于SQL查询技巧，《Database In Depth》深入解析数据库系统内部工作原理，《Essential SQLAlchemy》介绍了流行的SQLAlchemy ORM库，以及《SQL in a Nutshell》是快速查询SQL语法的参考手册。通过访问O'Reilly的网站，读者可以获取最新的IT新闻、事件信息、文章、博客、样章和代码示例。 O'Reilly的Conferences是技术交流的重要平台，汇集了各领域的创新者，促进新思想的碰撞和新技术的普及。Safari Bookshelf在线参考图书馆则提供了一站式的编程和IT专业文献检索服务，订阅者可以即时获取问题答案，阅读完整的书籍内容，极大地提高了学习和工作效率。

xiv PREFACE

Now, a book like this wouldn’t be needed if everyone was using SQL relationally

already—but they aren’t. On the contrary, I observe much bad practice in current SQL

usage. I even observe such practice being recommended, in textbooks and similar publica-

tions, by writers who really ought to know better (no names, no pack drill); in fact, a

review of the literature in this regard is a pretty dispiriting exercise. The relational model

first saw the light of day in 1969, and yet here we are, almost 40 years on, and it still

doesn’t seem to be very well understood by the database community at large. Partly for

such reasons, this book uses the relational model itself as an organizing principle; it

explains various features of the model in depth, and shows in every case how best to use

SQL to implement the feature in question.

Prerequisites

I assume you’re a database practitioner and therefore reasonably familiar with SQL

already. To be specific, I assume you have a working knowledge of either the SQL stan-

dard or (perhaps more likely in practice) at least one SQL product. However, I don’t

assume you have a deep knowledge of relational theory as such (though I do hope you

understand that the relational model is a good thing in general, and adhering to it wher-

ever possible is a desirable goal). In order to avoid misunderstandings, therefore, I’ll be

describing various features of the relational model in detail, as well as showing how to use

SQL to conform to those features. But what I won’t do is attempt to justify all of those fea-

tures; rather, I’ll assume you’re sufficiently experienced in database matters to understand

why, e.g., the notion of a key makes sense, or why you sometimes need to do a join, or

why many to many relationships need to be supported. (If I were to include such justifica-

tions, this would be a very different book—quite apart from anything else, it would be

much bigger than it already is—and in any case, that book has already been written.)

I’ve said I expect you to be reasonably familiar with SQL. However, I should add that I’ll

be explaining certain aspects of SQL in detail anyway—especially aspects that might be

encountered less frequently in practice. (The SQL notion of “possibly nondeterministic

expressions” is a case in point here. See Chapter 12.)

Database in Depth

This book is based on, and intended to replace, an earlier one with the title Database in

Depth: Relational Theory for Practitioners (O’Reilly, 2005). My aim in that earlier book was as

follows (this is a quote from the preface):

After many years working in the database community in various capacities, I’ve come to

realize there’s a real need for a book for practitioners (not novices) that explains the

basic principles of relational theory in a way not tainted by the quirks and peculiarities

of existing products, commercial practice, or the SQL standard. I wrote this book to fill

that need. My intended audience is thus experienced database practitioners who are

honest enough to admit they don’t understand the theory underlying their own field as

PREFACE xv

well as they might, or should. That theory is, of course, the relational model—and while

it’s true that the fundamental ideas of that theory are all quite simple, it’s also true that

they’re widely misrepresented, or underappreciated, or both. Often, in fact, they don’t

seem to be understood at all. For example, here are a few relational questions

… How

many of them can you answer?

What exactly is first normal form?

What’s the connection between relations and predicates?

What’s semantic optimization?

What’s an image relation?

Why is semidifference important?

Why doesn’t deferred integrity checking make sense?

What’s a relation variable?

What’s prenex normal form?

Can a relation have an attribute whose values are relations?

Is SQL relationally complete?

Why is The Information Principle important?

How does XML fit with the relational model?

This book provides answers to these and many related questions. Overall, it’s meant to

help database practitioners understand relational theory in depth and make good use of

that understanding in their professional day-to-day activities.

As the final sentence in this extract indicates, it was my hope that readers of that book

would be able to apply its ideas for themselves, without further assistance from me as it

were. But I’ve since come to realize that, contrary to popular opinion, SQL is such a diffi-

cult language that it can be far from obvious how to use it without violating relational

principles. I therefore decided to expand the original book to include explicit, concrete

advice on exactly that issue (how to use SQL relationally, I mean). So my aim in the

present book is still the same as before (i.e., I want to help database practitioners under-

stand relational theory in depth and make good use of that understanding in their profes-

sional activities), but I’ve tried to make the material a little easier to digest, perhaps, and

certainly easier to apply. In other words, I’ve included a great deal of SQL-specific material

(and it’s this fact, more than anything else, that accounts for the increase in size over the

previous version).

* For reasons that aren’t important here, I’ve replaced a few of the questions in this list by new ones.

xvi PREFACE

Further Remarks on the Text

I need to take care of several further preliminaries. First of all, my own understanding of

the relational model has evolved over the years, and continues to do so. This book repre-

sents my very latest thinking on the subject; thus, if you detect any technical discrepan-

cies—and there are a few—between this book and other books you might have seen by

myself (including in particular the one this book is meant to replace), the present book

should be taken as superseding. Though I hasten to add that such discrepancies are mostly

of a fairly minor nature; what’s more, I’ve taken care always to relate new terms and con-

cepts to earlier ones, wherever I felt it was necessary to do so.

Second, I will, as advertised, be talking about theory—but it’s an article of faith with me

that Theory is practical. I mention this point explicitly because so many people seem to

believe the opposite: namely, that if something’s theoretical, it can’t be practical. But the

truth is that theory (at least, relational theory, which is what I’m talking about here) is

most definitely very practical indeed. The purpose of that theory is not just theory for its

own sake; the purpose of that theory is to allow us to build systems that are 100 percent

practical. Every detail of the theory is there for solid practical reasons. As one reviewer of

the earlier book, Stéphane Faroult, wrote: “When you have a bit of practice, you realize

there’s no way to avoid having to know the theory.” What’s more, that theory is not only

practical, it’s fundamental, straightforward, simple, useful, and it can be fun (as I hope to

demonstrate in the course of this book).

Of course, we really don’t have to look any further than the relational model itself to find

the most striking possible illustration of the foregoing thesis. In fact, it really shouldn’t be

necessary to have to defend the notion that theory is practical, in a context such as ours:

namely, a multibillion dollar industry totally founded on one great theoretical idea. But I

suppose the cynic’s position would be “Yes, but what has theory done for me lately?” In

other words, those of us who do think theory is important must be continually justifying

ourselves to our critics—which is another reason why I think a book like this one is

needed.

Third, as I’ve said, the book does go into a fair amount of detail regarding features of SQL

or the relational model or both. (It deliberately has little to say on topics that aren’t partic-

ularly relational; for example, there isn’t much on transactions.) Throughout, I’ve tried to

make it clear when the discussions apply to SQL specifically, when they apply to the rela-

tional model specifically, and when they apply to both. I should emphasize, however, that

the SQL discussions in particular aren’t meant to be exhaustive. SQL is such a complex

language, and provides so many different ways of doing the same thing, and is subject to

so many exceptions and special cases, that to be exhaustive—even if it were possible,

which I tend to doubt—would be counterproductive; certainly it would make the book

much too long. So I’ve tried to focus on what I think are the most important issues, and

I’ve tried to be as brief as possible on the issues I’ve chosen to cover. And I’d like to claim

that if you do everything I tell you, and don’t do anything I don’t tell you, then to a first

PREFACE xvii

approximation you’ll be safe: You’ll be using SQL relationally. But whether that claim is

justified, or to what extent it is, must be for you to judge.

To the foregoing I have to add that, unfortunately, there are some situations in which SQL

just can’t be used relationally. For example, some SQL integrity checking simply has to be

deferred (usually to commit time), even though the relational model rejects such checking

as logically flawed. The book does offer advice on what to do in such cases, but I fear it

often boils down to just Do the best you can. At least I hope you’ll understand the risks

involved in departing from the model.

I should say too that some of the recommendations offered aren’t specifically relational

anyway but are, rather, just matters of general good practice—though sometimes there

are relational implications (implications that can be a little unobvious, too, perhaps I

should add). Avoid coercions is a good example here.

Fourth, please note that I use the term SQL throughout the book to mean the standard

version of that language exclusively, not some proprietary dialect (barring explicit state-

ments to the contrary). In particular, I follow the standard in assuming the pronunciation

“ess cue ell,” not “sequel” (though this latter is common in the field), thereby saying

things like an SQL table, not a SQL table.

Fifth, the book is meant to be read in sequence, pretty much, except as noted here and

there in the text itself (most of the chapters do rely to some extent on material covered in

earlier ones, so you shouldn’t jump around too much). Also, each chapter includes a set of

exercises. You don’t have to do those exercises, of course, but I think it’s a good idea to

have a go at some of them at least. Answers, often giving more information about the sub-

ject at hand, are given in Appendix C.

Finally, I’d like to mention that I have some live seminars available based on the material

in this book. See http://www.clik.to/chris_date or http://www.thethirdmanifesto.com for further

details.

Conventions Used in This Book

The following typographical conventions are used in this book:

Italic

Used for emphasis. Indicates new terms. Also indicates when a variable, such as x, is

used in place of something else during a discussion in the main text of the book.

Constant width

Used for code examples.

Constant width italic

Marks the occurrence of a variable or user-supplied element in a code example.

剩余427页未读，继续阅读

huzhouhzy

粉丝: 83
资源: 1657

理解SQL与关系理论：写出准确的SQL代码

SQL.and.Relational.Theory.How.to.Write.Accurate.SQL.Code.3rd.Edition

SQL and Relational Theory: How to Write Accurate SQL Code

ORACLE中SQL查询语句的优化.pdf

基于SQL的二维统计分析方法研究.pdf

Android.Database.Best.Practices.0134437993

Oracle SQL高级编程(资深Oracle专家力作，OakTable团队推荐)--随书源代码

数据库与数据仓库类图书

数据库与数据仓库类图书、程序员2005合订本

SQL与关系理论：撰写精准SQL代码

理解SQL与关系理论：编写准确的SQL代码

最新资源