（译）第三章 SQL介绍（二）

声明：此文章并不是转载或抄袭，是我学习英文版Database System Concepts,Sixth Edition后，写的中文理解，而且后续将会继续发布。目前国内还没有Database System Concepts,Sixth Edition的中文译本，如果所讲内容有雷同，实属巧合，不过文字语言绝对原创。

 

3.3 SQL查询的基本结构
　　SQL查询的基本结构由三个子句组成，select,from和where。from子句指定需要使用的关系列表，where子句和select子句进行运行，运算结果是一个新的关系。我们将在后面使用实例描述SQL语句并介绍SQL查询的基本结构。

3.3.1 单关系查询
　　我们来看一个查询的例子，以大学数据库为例，“查询所有教授的姓名”。教授的姓名在关系instructor中，因此我们要将关系instructor放在from子句后面。教授的姓名就是属性name，因此我们把属性name放在select子句后。
　　　　　　　　select name
　　　　　　　　from instructor;
　　查询结果是只有一个属性的关系，属性名为name。如果关系instructor如图2.1所示，上面查询的结果如图3.2。
我们看另外一个查询，“查询所有教授所在学院的名称”，查询语句如下：
　　　　　　　　select dept_name
　　　　　　　　from instructor;
　　因为一个学院不只有一个教授，所以一个学院的名称可能会多次出现在关系instructor中。上面查询的结果是一个关系，只包含学院名称，如图3.3。
在正式的数学关系模型定义中，关系是一个集合。所以，关系中不能出现重复的元组。实际情况下，消重复是非常耗费时间的，因此SQL允许在关系中出现重复的元组。所以，上面的查询语句会将关系instructor中每一个元组中的学院名称列举出来。
　　如果我们想要强行消除重复，我们可以在select后面使用distinct关键字来实现。更改后的SQL语句如下：
　　　　　　　　select distinct dept_name
　　　　　　　　from instructor;
　　如果我们消除了重复，上面查询的结果中，每个学院的名称至多出现一次。
　　SQL支持使用all关键字显示指定不消除重复。
　　　　　　　　select all dept_name
　　　　　　　　from instructor;
　　由于默认情况是不消除重复，所以在示例中我们不再使用all关键字。在我们的实例中，无论何事需要消除重复，我们都将使用distinct关键字。
　　select子句中可以对常数或者元组中的某些属性进行+,-,*,/的算术运算。例如下面的查询：
　　　　　　　　select ID,name,dept_name,salary*1.1
　　　　　　　　from instructor;
　　除了属性salary乘以了1.1以外，结果关系其他都与关系instructor相同。结果关系表明，我们给每个教授加薪10%。不过这里要注意，关系instructor是没有发生任何改变的。
　　SQL也提供特殊数据类型，例如各种形式的日期类型,并且提供几种算术函数来对这些类型进行运算，我们将在4.5.1节中进一步讨论。
　　The where clause allows us to select only those rows in the result relation of the from clause that satisfy a sepcified predicate.我们来看这个查询“找出计算机学院工资高于70000美元的所有教授的名字。”这个查询的SQL语句如下：
　　　　　　　　select name
　　　　　　　　from instructor
　　　　　　　　where dept_name='Comp.Sci.' and salary>70000;
　　假定关系instructor如图2.1所示，这个SQL语句的查询结果如图3.4所展示。
　　SQL允许在where子句中使用逻辑连接词and,or,not。逻辑连接的操作对象可以是比较运算符<,<=,>,>=和<>等的表达式。SQL允许我们使用比较运算符来比较字符串、算术表达式和特殊类型，比如日期类型。
　　稍后在这一章中，我们将继续探索where子句谓词的其他特性。

3.3.2 多关系查询
　　目前位置，我们所举例子都是单关系查询，但是查询常常需要访问多个关系的数据。现在我们来学习如果写多关系查询。
　　看一个例子，假设我们要进行如下查询“取出所有教授的名字，以及教授所在学院的名称和学院大楼。”
　　我们看看关系instructor的架构，我们发现可以通过属性dept_name获得学院的名称，但是学院所在大楼只能通过关系department的属性building获得。要解答这个查询，关系instructor中的每一个元组都要和关系department中的元组进行匹配，匹配的条件是instructor元组中的dept_name的值和关系department元组中的dept_name的值相等。
　　在SQL中，为了解答上面的查询，我们在from子句中列出了所有被访问的关系，然后在where子句中指定匹配条件。上面的查询的SQL语句如下:
　　　　　　　　select name,instructor.dept_name.building
　　　　　　　　from instructor,department
　　　　　　　　where insturctor.dept_name=department.dept_name;
　　假定关系instructor和关系department分别如图2.1和2.5所示。那么上面查询语句的结果如图3.5所示。
　　注意，属性dept_name同时出现在关系instructor和关系department中，此时我们使用关系名作为前缀来区分属性属于哪个关系（例如instructor.dept_name和　　　　　　　　department.dept_name）。反而，属性name和building仅出现在一个关系中，所以我们不需要使用关系名作为前缀来区分。
　　便捷的命名需要from子句中的关系有不同的名称，但是这个需求导致了一些问题，例如我们需要联结的是同一个关系中的两个不同元组，from子句中同一个关系将会出现两次。在3.4.1节中，我们将讲解如何使用重命名操作来避免这些问题。
　　我们现在来看看一般的多关系查询，正如我们前面所看到的，一个SQL查询语句可能包含3个类型子句，select子句、from子句和where子句。每个子句的角色如下：

• select子句列出查询语句中期望得到的属性。
• from子句列出查询中将会访问的关系。
• where子句是一个谓词，这个谓词与from子句中关系的某些属性相关。

　　典型的SQL查询语句的格式如下：
　　　　　　　　select A1,A2,...,An
　　　　　　　　from r1,r2,...,rm
　　　　　　　　where P;
　　每个Ai代表一个属性，每个ri代表一个关系，P是一个谓词。如果省略where子句，则谓词P为true;
　　虽然SQL语句中子句出现的顺序必须是select,from,where，但是理解SQL语句最容易的方式是根据其运算顺序：首先from,然后where,最后select。
　　from子句本身定义的是子句中关系的笛卡尔积。它是依据集合理论来定义的，最好的理解是，将其作为一个迭代过程，这个过程产生的是结果集中的元组。
　　　　　　　　for each tuple t1 in relation r1
　　　　　　　　　　for each tuple t2 in relation r2
　　　　　　　　　　　　...
　　　　　　　　　　　　for each tuple tm in relation rm
　　　　　　　　　　　　Concatenate t1,t2,...,tm into a single tuple t
　　　　　　　　　　　　Add t into the result relation
　　结果关系中包含了from子句中所有关系的所有属性。因为在关系ri和rj中可能会出现同样的属性名，正如我们前面多见到的，在属性名称前，我们使用属性所在关系的名称作为前缀。
例如关系instructor与关系teaches的笛卡尔积的架构如下：
　　　　　　　　(instructor.ID,instructor.name,instructor.dept_name,instructor.salary
　　　　　　　　 teaches.ID,teaches.course_id,teaches.sec_id,teaches.semester,teaches.year)
　　在这个架构中，我们能区分instructor.ID和teaches.ID。对于那些仅出现在一个关系中的属性，我们将省略关系名前缀。这种简化不会引起任何模糊不清的内容。我们可以如下编写这个关系的架构：
　　　　　　　　(insturctor.ID,name,dept_name,salary
　　　　　　　　 teaches.ID,course_id,sec_id,semester,year)
　　举例说明，假设关系instructor如图2.1，关系teaches如图2.7，那么他们的笛卡尔积如图3.6所示，由于页面大小，图3.6只列出了笛卡尔积的一部分。
笛卡尔积联结了instructor和teaches毫无关联的元组，instructor中的每个元组都与teaches中的每个元组进行联结，即使所指的是不同的教授。结果可能是一个极大的关系，产生这样一个笛卡尔积通常是没有意义的。
　　相反，where子句中的谓词是用来限制笛卡尔积中产生无意义的元组。我们希望在instructor和teaches之间有这样一个查询，把instructor中的一个特定元组，与在teaches中和这个元组引用同一个教授的元组联结起来。那就是说，我们希望通过相同ID的值来匹配instructor与teaches之间的元组。下面的SQL查询确保了这个条件，然后输出相匹配元组的教授姓名和课程标识。
　　　　　　　　select name,course_id
　　　　　　　　from instructor,teaches
　　　　　　　　where instructor.ID=teaches.ID;
　　注意上面的查询仅仅输出了有教课程的教授。没有教任何课程的教授没有列出。如果我们想要输出即使没有教任何课程的教授，我们可以使用outer join运算，将在4.1.2节中讲解。
假定关系instructor如图2.1，关系teaches如图2.7，那么差面查询的结果将如图3.7所示。很显然，教授Gold,Califieri和Singh没有教任何课程，所以就没有出现在结果中。
如果我们希望仅仅查询计算机学院的教授姓名和他们所教课程的标识，那么我们需要在where子句后面增加一个谓词，如下：
　　　　　　　　select name,course_id
　　　　　　　　from instructor,teaches
　　　　　　　　where instructor.ID=teaches.ID and instructor.dept_name='Comp.Sci.';
　　注意，因为属性dept_name仅仅出现在关系instructor中，所以在上面的查询中我们会使用dept_name,而不是instructor.dept_name。
　　通常情况下，SQL语句可以如下理解：
　　　　1.对from子句中关系产生一个笛卡尔积。
　　　　2.对第一步中的结果，应用where子句的谓词过滤。
　　　　3.对第二步产生的元组，输出select子句中指定的属性。
　　上面步骤帮助我们理解SQL语句的执行结果，但是没有说明SQL语句是如何执行的。真实的SQL应用实例中，将不会以这种方式执行查询。而是优化运算，仅仅产生笛卡尔积中满足where子句谓词的元组。在第12、13中我们将学习这种应用实现技术。
　　在写SQL查询语句的时候，请注意在where子句后跟正确的条件。如果省略掉where子句，查询会返回笛卡尔积，它是一个非常庞大的关系。例如，假设关系instructor如图2.1，关系teaches如图2.7，他们的笛卡尔积将会有12*13=156个元组，远大于前面SQL语句的结果。来看看更糟糕的情况，假设我们使用更真实的数据，教授的数量远大于我们所举例子，比如说教授人数为200，每个教授教3门课程，那么在关系teaches中将会产生600个元组，因此上面的迭代过程（笛卡尔积）将产生200*600=120000个元组。

3.3.3 自然连接
　　在我们的例子中，关联了instructor和teaches的数据，关联的条件是instructor.ID=teaches.ID。这两个关系中只有一个相同名称的属性，这只是一般情况，事实上，大部分时候我们需要关系中所有名称相同的属性都相等。
　　为了方便程序员工作，SQL支持一种名为自然连接的运算。事实上，SQL支持其他一些方式来关联两个或者多个关系的数据。我们已经知道了，怎么通过笛卡尔积和where子句谓词来关联多 个关系的数据。关联多个关系的其他方式，我们将在4.1节中进行讲解。
　　自然连接运算符操作两个关系，产生一个新的关系作为结果。不同于笛卡尔积的是，笛卡尔积将第一个关系的每一个元组与第二个关系的每一个元组联结，而自然连接是将两个关系中相同属性具有相同值的元组联结起来。那么，回到关系instructor与关系teaches的例子中，计算instructor natural join teaches时，仅仅连接instructor.ID=teaches.ID的那些元组。
结果如图3.8所示，仅有13个元组，这些元组给出了教授们实际教的课程。注意，我们没有重复列出同时出现在连个关系中的属性。还要主要到，属性排列的顺序，首先是两个关系中共同存在的属性，然后是第一个关系独有的属性，最后才是第二个关系独有的属性。
　　看看下面的查询，“查询所有有教课程的教授姓名，以及他们所教课程的ID”，按照我们之前的写法如下：
　　　　　　　　select name,course_id
　　　　　　　　from instructor,teaches
　　　　　　　　where instructor.ID=teaches.ID;
　　这个SQL语句使用natural join可以简写为
　　　　　　　　select name,course_id
　　　　　　　　from instructory natural join teaches;
　　以上两个SQL语句的查询结果一样。
　　在概念上，from子句后面的表达式"instructor natural join teaches"会替换成自然连接运算的结果关系，select子句和where子句的运算将基于这个结果关系，正如我们在3.3.2节所看到的。
　　使用自然连接，能够将from子句中的多个关系进行连接，如下所示：
　　　　　　　　select A1,A2,...,An
　　　　　　　　from r1 natural join r2 natural join ... natural join rm
　　　　　　　　where P;
　　更常见的情况是from子句会是这种形式
　　　　　　　　from E1,E2,...,En
　　这里Ei可能是单个的关系，也可能是包含如natural join的表达式。举例说明，“列出所有教授的姓名以及教授所教课程的标题。”，SQL语句如下：
　　　　　　　　select name,title
　　　　　　　　from instructor natural join teaches,course
　　　　　　　　where teaches.course_id=course.course_id;
　　首先是instructor和teaches的自然连接被计算，得到一个结果关系，接着再拿这个结果关系与关系course产生一个笛卡尔积，最后在通过where子句的条件过滤找出两个关系中（自然连接的结果关系与course）课程标识相同的元组。注意，where子句中的teaches.course_id是指自然连接结果关系中course_id的字段，since this field in turn came from the teaches relation. (original statement:Note that teaches.course_id in the where clause refers to the course_id field of natural join result,since this field in turn came from the teaches relation.)
　　但是，下面的SQL语句返回的结果却不一样：
　　　　　　　　select name,title
　　　　　　　　from instructor natural join teaches natural join course;
　　我们来看看为什么结果不一样，注意看instructor和teaches的自然连接包含属性(ID,name,dept_name,salary,course_id,sec_id),同时course包含了属性（course_id,title,dept_name,credits）。因此，这两者（第一个自然连接的结果和course）之间的自然连接要求两者的dept_name属性的值相等，并且属性course_id的值也必须相等。这个查询将会排除那些教授未在自己所在学院教课程的元组。换句话说，前一个查询给出的结果才是我们想要的。
　　为了提供自然连接的便利，同时避免错误地使用属性值相等的风险，SQL提供了一种自然连接语法允许我们指定那些列相等，看下面的查询：
　　　　　　　　select name,title
　　　　　　　　from (instructor natural join teaches) join course using(course_id);
　　join...using需要列出指定的属性名，在自然连接的两个关系中，都必须存在这些指定的属性。假设有运算r1 join r2 using(A1,A2),这类似于r1 natural join r2,但是除了r1与r2中t1.A1=t2.A1和t1.A2=t2.A2的元组，即使两者中都存在属性A3，也无须t1.A3=t2.A3。
　　因此，在SQL查询中，join结构允许teaches.dept_name与course.dept_name不同，但是SQL查询的结果是正确的。