3.3.2 多关系查询

发表于 更新于 分类于 ， ， ， Waline： 本文字数： 5.3k 阅读时长 ≈ 5 分钟

3.3.2 多关系查询

找出所有教师的姓名,以及他们所在系的名称和系所在建筑的名称

考虑instructor关系的模式,我们发现可以从dept_name属性得到系名,但是系所在建筑的名称是在department关系的building属性中给出的。

教师模式:instructor(ID,name,dept_name,salary)
系模式:department(dept_name,building,budget)

为了回答上面的查询, instructor关系中的每个元组必须与department关系中的元组匹配,也就是**department在dept_name上的取值要等于 instructor元组在dept_name上的取值**。

如何查询

为了在SQL中回答上述查询,我们**把需要访问的关系都列在from子句中,并在where子句中指定匹配条件**。

对应的SQL语句

上述查询可用SQL写为:

1
2
3
select name, instructor.dept_name, building
    from instructor, department
    where instructor.dept_name = department.dept_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name = department.dept_name;
+------------+------------+----------+
| name       | dept_name  | building |
+------------+------------+----------+
| Crick      | Biology    | Watson   |
| Srinivasan | Comp. Sci. | Taylor   |
| Katz       | Comp. Sci. | Taylor   |
| Brandt     | Comp. Sci. | Taylor   |
| Kim        | Elec. Eng. | Taylor   |
| Wu         | Finance    | Painter  |
| Singh      | Finance    | Painter  |
| El Said    | History    | Painter  |
| Califieri  | History    | Painter  |
| Mozart     | Music      | Packard  |
| Einstein   | Physics    | Watson   |
| Gold       | Physics    | Watson   |
+------------+------------+----------+
12 rows in set

两个关系中的同名属性要使用关系名作为前缀

dept_name属性既出现在instructor关系中,也出现在department中,所以需要使用关系名作为前缀来说明我们使用的是哪个属性
例如:
instructor.dept_name
department.dept_name

不重名的属性不需要关系名作为前缀

属性name和building只出现在一个关系中,因而不需要把关系名作为前缀。

多个关系查询的SQL通用形式

现在我们考虑涉及多个关系的SQL查询的通用形式。

select from where三个子句的作用

一个SQL查询可以包括三种类型的子句:select子句、from子句和where子句。每种子句的作用如下:

  • select子句用于列出查询结果中所需要的属性。
  • from子句是一个查询求值中需要访问的关系列表。
  • where子句是一个作用在from子句中关系的属性上的谓词

SQL查询语句格式

一个典型的SQL查询具有如下形式:

1
2
3
select A1,A2,...An
    form R1,R2,...,Rm
    where P;

每个An,代表一个属性,每个Rm代表一个关系。P是一个谓词。如果省略where子句,则谓词P为true。
如何

查询运算各子句的执行顺序

尽管各子句必须以select、from、 where的次序写出,但理解查询所代表运算的最容易的方式是以运算的顺序来考察各子句:

  • 首先是from,
  • 然后是where,
  • 最后是select。

form子句得到笛卡尔积

**通过from子句定义了一个在该子句中所列出关系上的笛卡儿积**。

它可以用集合理论来形式化地定义,但最好通过下面的迭代过程来理解,此过程可为from子句的结果关系产生元组。

1
2
3
4
5
6
for each 元组T1 in 关系R1
    for each 元组T2 in 关系R2
        ...
        for each 元组Tn in 关系Rn
            把T1,T2,...,Tn 连接 成单个元组T
            把T加入结果关系中.

此结果关系具有来自from子句中所有关系的所有属性。由于在关系Ri和Rj中可能出现相同的属性名,此时需要在属性名前加上关系名作为前缀,表示该属性来自于哪个关系。

笛卡尔积说明

instructor(ID,name,dept_name,salary)
teaches(ID,course_id,sec_id,semester,year)

关系instructor和teaches的笛卡儿积的关系模式为:

1
(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。对于那些只出现在单个模式中的属性,我们通常去掉关系名前缀。这种简化并不会造成任何混淆。这样我们可以把关系模式写为

1
(instructor.ID, name, dept_name, salary, teaches.ID, course_id, sec_id, semester, year)

通过笛卡儿积把来自instructor和teaches中相互没有关联的元组组合起来。 instructor中的每个元组和teaches中的所有元组都要进行组合。结果可能是一个非常庞大的关系,创建这样的笛卡儿积通常是没有意义的。

where子句限制from子句得到的笛卡尔积

where子句中的谓词用来限制笛卡儿积所建立的组合,只留下那些对所需答案有意义的组合。
例如:
我们希望把teaches元组只和具有相同ID值的instructor元组进行匹配。下面的SQL查询满足这个条件,从这些匹配元组中输出教师名和课程标识。

1
2
3
select name, course_id
from instructor , teaches
where instructor.ID= teaches.ID;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> select name, course_id
from instructor , teaches
where instructor.ID= teaches.ID;
+------------+-----------+
| name       | course_id |
+------------+-----------+
| Srinivasan | CS-101    |
| Srinivasan | CS-315    |
| Srinivasan | CS-347    |
| Wu         | FIN-201   |
| Mozart     | MU-199    |
| Einstein   | PHY-101   |
| El Said    | HIS-351   |
| Katz       | CS-101    |
| Katz       | CS-319    |
| Crick      | BIO-101   |
| Crick      | BIO-301   |
| Brandt     | CS-190    |
| Brandt     | CS-190    |
| Brandt     | CS-319    |
| Kim        | EE-181    |
+------------+-----------+
15 rows in set

mysql>

注意上述查询只输出讲授了课程的教师,不会输出那些没有讲授任何课程的教师,可以使用一种被称作外连接的运算,外连接将在4.1.2节讲述。

如果我们只希望找出Computer Science系的教师名和课程标识,我们可以在where子句中增加另外的谓词,如下所示:

1
2
3
select name, course_id 
from instructor, teaches
where instructor.ID= teaches.ID and instructor.dept_name = 'Comp. Sci.';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select name, course_id
from instructor, teaches
where instructor.ID= teaches.ID and instructor.dept_name = 'Comp. Sci.';
+------------+-----------+
| name       | course_id |
+------------+-----------+
| Srinivasan | CS-101    |
| Srinivasan | CS-315    |
| Srinivasan | CS-347    |
| Katz       | CS-101    |
| Katz       | CS-319    |
| Brandt     | CS-190    |
| Brandt     | CS-190    |
| Brandt     | CS-319    |
+------------+-----------+
8 rows in set

理解SQL查询的结果应该是什么样的

通常说来,一个SQL查询的含义可以理解如下:

  1. 为from子句中列出的关系产生笛卡儿积。
  2. 在步骤1的结果上应用where子句中指定的谓词。
  3. 对于步骤2结果中的每个元组,输出select子句中指定的属性(或表达式的结果)。

上述步骤的顺序有助于**明白一个SQL查询的结果应该是什么样的,**而不是这个结果是怎样被执行的。在SQL的实际实现中不会执行这种形式的查询,它会通过(尽可能)只产生满足where子句谓词的笛卡儿积元素来进行优化执行。我们在后面第12章和第13章学习那样的实现技术。

要设置合适的where

当书写查询时,需要小心设置合适的where子句条件。如果在前述SQL查询中省略where子句条件,就会输出笛卡儿积,那是一个巨大的关系

大学数据库模式

classroom(building,room_number,capacity)
department(dept_name,building,budget)
course(course_id,title,dept_name,credits)
instructor(ID,name,dept_name,salary)
section(course_id,sec_id,semester,year,building,room_number,time_slot_id)
teaches(ID,course_id,sec_id,semester,year)
student(ID,name,dept_name,tot_cred)
takes(ID,course_id,sec_id,semester,year,grade)
advisor(s_ID,i_ID)
time_slot(time_slot_id,day,start_time,end_time)
prereq(course_id,prereq_id)