SQL笔记（二） SQL的查询的基本结构

SQL笔记（二） SQL查询的基本结构

本文中用到的所有数据库文件在相关材料下载中可以找到

大学模式

1. 大学数据库模式

---

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)

---

SQL查询的基本结构由三个字句构成：
select, from , where .

单关系查询

查询1.找到所有教师的名字

select name
from instrucotr;

查询2.找出所有教师所在的系名

select dept_name
from instructor;

因为一个系中存在多个教师，所以查询2中可能出现重复的数据。可以使用distinct强行去重。

distinct 去除重复数据

查询3.找出所有教师所在的系名

select distinct dept_name
from instructor;

all 不去除重复（默认）

查询4.找出所有教师所在的系名（等价于查询2）

select all dept_name
from instructor;

带有运算符算术表达式的查询

查询5.

select ID, name, dept_name, salary*1.1
from instructor;

该操作不会导致instructor关系的任何改变。

where 条件查询

查询6.找出所有的在Computer Science系，并且工资超过70 000美元的教师的姓名。

select name
from instructor
where dept_name = 'Comp. Sci.' and salary > 70000;

where子句中的逻辑连词

逻辑连词有：and、or、not
逻辑连词的运算对象可以是包含比较运算符< 、>、<= 、>=、 <>的表达式。

多关系查询

查询7. 找出所有的教师的姓名，以及他们所在系的名称和系所在建筑的名称。

select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name = department.dept_name;

注意: dept_name既出现在instructor中，也出现在department中，所以在查询时，需要指定dept_name的前缀。

一个典型的SQL查询

查询8.一个典型的SQL查询

select A1, A2, ... , An
from r1, r2, ... , rn
where P;

• select 子句：用于列出查询结果中所需要的属性。
• from子句：是一个查询求值中需要访问的关系列表。
• where子句：是一个作用在from子句中关系属性的谓词。
  在查询8中，每个Ai表示一个属性，每个ri表示一个关系。P是一个谓词。如果省略where子句，则谓词P为true。

运算顺序：
1. 为from子句中列出的关系产生笛卡尔积
2. 在步骤1的结果上应用where子句中指定的谓词。
3. 对于步骤2结果中的每个元组，输出select子句中指定的属性（或表达式的结果）。

自然连接 ——natural join

自然连接作用于两个关系，并产生一个关系作为结果。
自然连接只考虑哪些关系模式中都出现的属性上取值相同的元组对。

查询9. 对于大学中所有讲授课程的教师，找出他们的姓名以及所讲述的所有课程标识。

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID;

查询10. 使用自然连接实现查询9

select name, course_id
from instructor natural join teaches;

查询9和查询10的结果一致。

自然连接多个关系

查询11. 使用自然连接将多个关系连接在一起。

select A1, A2, ... ,An
from r1 natural join r2 natural ... natural join rn
where P;

更为一般的情况，from子句如下：

from E1, E2, ... , En

每个Ei可以是单个关系或一个包含自然连接的表达式。

查询12. 列出教师名字以及他们所讲授课程的名称。

查询12.1

select name, title
from instructor natural join teaches, course
where teaches.course_id = course.course_id;

查询12.2

select name, title
from instructor natural join teaches natural join course

12.1 和12.2 的结果不同。
instructor 和teaches的自然连接包括属性：
(ID, name, dept_name, salary, course_id, sec_id)

course 包含属性：
(course_id, title, dept_ name, credits)

两者自然连接的结果需要满足：
course_id 相等， dept_name相等

查询12.2 中会将教师讲授的课程不是他所在的系这种情况排除在外。

查询12.3 join… using…

select name, title
from(instructor natural join teaches) join coures using(course_id);

用户指定需要相等的属性。等价于查询12.1。