掌握SQL关系理论：编写精准代码与资源指南

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

SQL与关系理论是计算机科学和数据库管理领域的重要基石，它涉及如何通过标准的结构化查询语言（SQL）来处理和操作关系型数据。在本文中，我们将深入探讨SQL的基本概念、语法以及其在实际应用中的准确编写技巧。 SQL，全称Structured Query Language，是一种用于管理和维护关系数据库的标准语言。它允许用户执行数据检索、更新、插入和删除等操作，是数据库管理系统（DBMS）的核心组成部分。学习SQL包括理解基本的数据类型、表和视图的创建、查询构造（如SELECT、JOIN、WHERE等）、聚合函数的使用以及事务处理等内容。准确的SQL代码编写不仅要求对语法规则有清晰的理解，还要考虑到性能优化和最佳实践，以确保高效的数据访问和维护。关系理论是SQL的基础理论框架，它基于埃德加·科德（Edgar F. Codd）在20世纪70年代提出的概念，强调数据的一致性和完整性。关系模型定义了数据之间的逻辑关系，如一对一、一对多和多对多的关联，这些都是SQL设计和查询的基础。通过理解关系代数和关系模式理论，开发者能够更好地设计数据库结构，确保数据的一致性。除了上述核心内容，O'Reilly是一家知名的出版社，他们的网站oreilly.com不仅提供了丰富的SQL和关系理论相关的书籍资源，如《SQL的艺术》、《深入数据库》、《Essential SQLAlchemy》和《SQL速成》，这些书籍涵盖了从基础到进阶的全面教程。此外，网站上还有新闻、活动、文章、博客、章节样本和代码示例，为学习者提供了实用的学习路径和最新技术动态。 oreilly.net则是面向开发者，特别是对开放和新兴技术感兴趣的开发者提供信息和服务的平台，包括新的开发平台、编程语言和操作系统。这里可以找到最新的技术趋势和发展动态，对于紧跟技术前沿的从业者非常有价值。参加O'Reilly举办的会议和研讨会是进一步提升技能和扩展人脉的好机会，这些会议聚集了行业创新者，共同探讨颠覆性技术的实践和应用。在线参考图书馆SafariBookshelf，作为程序员和IT专业人员的首选，提供了超过1000本书的搜索服务，方便实时解决工作中的技术问题。 SQL与关系理论的学习是一段持续深化理解的过程，熟练掌握这门技术不仅限于书本知识，还包括实践经验的积累。通过O'Reilly提供的各种资源，开发者可以系统地提升自己的数据库管理和数据处理能力，适应不断变化的技术环境。

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

Anaw

粉丝: 0
资源: 8

掌握SQL关系理论：编写精准代码与资源指南

《SQL与关系理论》清晰版[PDF]

SQL与关系数据库理论 如何编写健壮的SQL代码

sql server关系数据理论

第1章 sql基础理论 1.1 rdbms介绍 1.1.1 rdbms介绍 1.1.2 sql和rdbms之间的关系 1

数据库系统概论 sql

sql语句数据库系统概论

SQL SERVER 导入SQL ANYWHERE数据库

设计一段代码，根据数据库系统概论的知识，设计SQL程序 要点：关系代数概念及其计算；实现数据定义(表、考虑完整性，视图)、查询(简单、复杂)、更新、控制(安全性)的SQL语句，以及数据查询的关系代数表达式

sql学习指南 pdf

最新资源

SQL与关系数据库理论如何编写健壮的SQL代码

设计一段代码，根据数据库系统概论的知识，设计SQL程序要点：关系代数概念及其计算；实现数据定义(表、考虑完整性，视图)、查询(简单、复杂)、更新、控制(安全性)的SQL语句，以及数据查询的关系代数表达式