SQL

SQL

《数据库系统概念》第三章

本章的sql脚本：https://github.com/wflyer/silberschatz-6th-university-dataset(MYSQL)

省略一部分关于DDL的内容

SQL查询的基本结构

一个SQL查询语句包含SELECT、FROM和WHERE三个基本部分，这是它们编写时的顺序，实际上执行时的顺序如下：

1. 读取FORM子句中列出的关系
2. 根据WHERE子句对关系中的属性进行过滤
3. 列出SELECT中指定的列

多关系查询

如果想要查询每一个教师以及他所在的系和建筑的名称，可以在instructor关系中查找到教师和它所在的系，而系所在的建筑则在department关系中。

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

这里的执行过程如下：

1. 通过from子句选中instructor和department这两个关系，并做笛卡尔积
2. 从笛卡尔积中的每一项中使用where子句的过滤条件过滤，当instructor.dept_name = department.dept_name不满足时，从结果中过滤
3. 列出select子句中指定的属性

注意事实上各种数据库软件的执行过程并不如此，因为要优化执行速度

自然连接

自然连接是为上面的多关系查询设计的一种简便写法。

还是上面的需求，本来的代码是这样的

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

使用自然连接的代码是这样的

select name, dept_name, building
from instructor natural join department;

这里的执行过程如下：

1. 通过from子句选中instructor这个关系
2. 通过natural join将department这个关系和instructor关系中的所有元组进行连接
3. 将department关系的元组和instructor关系的元组中共同出现的同名属性中属性值一致的元组作为一个关系为SQL中其它部分所用

注意第三条，把二者结合后的所有元组作为一个关系，这很重要，因为这样，就能像处理一个关系一样处理自然连接的结果，甚至可以嵌套自然连接

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

上面的代码把关系\(r_1\)和关系\(r_2\)自然连接后的结果作为一个关系再和\(r_3\)进行自然连接，这样一直到\(r_n\)，我们可以像处理普通关系一样处理最后得到的关系，而且我们的where子句和select子句中不用使用关系.属性的写法，因为所有关系中的同名属性已经被自然连接给合并。

示例

列出教师的名字以及它们所讲授的课程

教师名字在instructor表，课程在course表，而教师和课程之间的联系在teaches表，teaches表中的一行记录代表instructor中的一个老师教了一门course中的课程。所以可以写出如下语句。instructor和teaches进行自然连接，过滤出所有教员和他所教的课程，这样会组合出一个新的关系，但是现在还没有课程名，课程名在course表中，通过多关系查询，再把上一步自然连接得到的关系和course关系通过course_id属性进行匹配，最终得到了结果。

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

关于自然连接得到的关系名，这里用的是teaches，书上说的是因为最后的course_id属性来自teaches，在mysql中使用instructor.course_id也确实不行。可能就是以连接到的关系名为最终关系名吧，上面代码可以看作将instructor关系连接到teaches。

考虑这个能不能得到正确结果

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

这是得不到正确结果的，来看看这三张表的关系模式

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

// 这是instructor 和 teaches自然连接后的关系模式
instructor natural join teaches (ID, name, dept_name, salary, course_id, sec_id, semester, year)

course (course_id, title, dept_name, credits)

我们发现，当instructor和teaches自然连接后，得到的关系和course中有两个重名属性，course_id和dept_name，自然连接会匹配两个关系中这两个属性值都相同的元组作为最终关系，这就造成了一个教师如果他教的课程不是本系的课程，那么他不会出现在结果里。

可以使用using子句指定自然连接参考的属性，这样就能得到正确结果了。

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

集合运算

union、intersect和except对应集合运算中的并集、交集和差集。

• 2009年秋季学期的所有课程集合

  select course_id
  from section
  where semester = 'Fall' and year = 2009;
  

  结果

  +-----------+
  | course_id |
  +-----------+
  | CS-101    |
  | CS-347    |
  | PHY-101   |
  +-----------+
  3 rows in set (0.00 sec)
  

• 2010年春季学期的所有课程集合

  select course_id
  from section
  where semester = 'Spring' and year = 2010;
  

  结果

  +-----------+
  | course_id |
  +-----------+
  | CS-101    |
  | FIN-201   |
  | MU-199    |
  | HIS-351   |
  | CS-319    |
  | CS-319    |
  | CS-315    |
  +-----------+
  7 rows in set (0.00 sec)
  

并运算

(select course_id
from section
where semester = 'Fall' and year = 2009) 
union 
(select course_id
from section
where semester = 'Spring' and year = 2010);

结果

+-----------+
| course_id |
+-----------+
| CS-101    |
| CS-347    |
| PHY-101   |
| FIN-201   |
| MU-199    |
| HIS-351   |
| CS-319    |
| CS-315    |
+-----------+
8 rows in set (0.01 sec)

并集运算会自动去除结果中相同的元素，如果想保留，可以使用union all

(select course_id
from section
where semester = 'Fall' and year = 2009) 
union all
(select course_id
from section
where semester = 'Spring' and year = 2010);

+-----------+
| course_id |
+-----------+
| CS-101    |
| CS-347    |
| PHY-101   |
| CS-101    |
| FIN-201   |
| MU-199    |
| HIS-351   |
| CS-319    |
| CS-319    |
| CS-315    |
+-----------+
10 rows in set (0.00 sec)

交运算

差不多，不写了

差运算

在第一个集合中减去第二个集合中出现的数据。

比如我们想要找在2009年秋季开课却不在2010年春季开课的课。

(select course_id
from section
where semester = 'Fall' and year = 2009) 
except
(select course_id
from section
where semester = 'Spring' and year = 2010);

PS: MYSQL不支持except，应该使用not in

空值

空值会带来很多问题。

算数运算空值

当空值在算数运算中时，算数运算的结果返回null。

比较运算空值

当空值在比较运算时，如1 < null，没办法说它是真是假，这就引出了除了TRUE和FALSE之外的第三个逻辑值，UNKNOWN。

带有UNKNOWN的逻辑运算

• and： true and UNKNOWN = UNKNOWN，false and UNKNOWN = false，UNKNOWN and UNKNOWN = UNKNOWN
• or： true or UNKNOWN = true，false or UNKNOWN = UNKNOWN，UNKNOWN or UNKNOWN = UNKNOWN
• not：not UNKNOWN = UNKNOWN

如果WHERE子句针对一个元组计算出false或unknown，那么该元组都不会出现在结果中。

is unknown和is not unknown子句用来测试一个值是否是unknown。

谓词中的null = null返回unknown。

聚集函数

聚集函数指的是将所有元组中的某些属性聚集在一起的函数。

比如教员的工资和，就是把教员关系中每一个元组的工资属性进行加和，聚集在一起。

select sum(salary) from instructor;

有的时候可能想要去除重复值，比如我们想统计有多少老师正在教课，可以去检索teaches关系，使用count聚集函数去数有多少个元组。但是有的老师可能教多门课，这时我们希望把这些在前面数数时已经出现过的老师忽略。

select count(distinct ID) from teaches;

聚集函数中的空值被忽略，看起来空值在不同的子句中有不同表现，所以还是尽量少用。

分组聚集

将关系中的元组按某个属性分组，指定属性一致的元组会被分到一个组中。

比如在instructor关系中按照dept_name进行分组，那么每个系的老师会被分成一组。

这样，select子句中想要展示的属性就不能瞎写了，比如下面的sql显然是错的，老师们已经通过系名进行分组了，对一个组去要name属性，怎么能要到呢？name是单独的老师才有的属性。

select name, dept_name from instructor
group by dept_name;

所以在分组查询中，select后面的字段除了group by子句后面指定的字段外，其它的字段都必须是聚集函数，聚集函数会把该组中的所有元组的某些属性聚集成一条，比如把按系名对老师分组，再计算每组老师的平均工资。

select dept_name, avg(salary) as avg_salary 
from instructor group by dept_name;

having子句

在分组查询中我们关心的是分组所具有的状态，而不是单个元组的，比如我们关心系平均工资而不关心个人工资。having子句对分组条件进行限制。

同select一样，没在group by子句中出现的属性必须以聚集函数形式出现在having子句中。

having和where容易搞混，现在说下它们的执行流程

1. 寻找from中的关系列表
2. 通过where子句中的条件加以限制过滤
3. 通过group by语句将where过滤后的数据进行分组
4. 通过having子句对分组后的数据进行第二次限制过滤
5. select子句输出

嵌套子查询

之前找出2009年秋季和2010年春季同时开课的课程，可以用交集运算，取两个查询返回集合的交集，这是一种基于集合的思路。换一种思路，我们可以查找每一个2009年秋季的课程，看看它在不在2010年春季也同样开课。后者就是前者的子查询。

先写出子查询

(select course_id
from section
where semester = 'Spring' and year = 2010)

再写整个查询

select course_id from section
    where semester = 'Fall' and year = 2009 
    and course_id in 
        (select course_id
        from section
        where semester = 'Spring' and year = 2010);

类似的还有not in操作符，你可以用来做差集操作。

in和not in也可以用作普通的枚举集合。

集合的比较

获取一个教师，它的工资至少比生物系的一位老师工资高。

select T.* from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';

上面的代码是与集合无关的写法，看着有些迷。下面的清晰多了。

select * from instructor 
where salary > some(
    select salary from instructor
    where dept_name = 'Biology'
);

类似的还有all，应用场景比如当我们想要获取一个教师，它的工资要比生物系所有老师高时。

空关系测试

exists子句在其中的子查询返回的关系不为空时返回true。

比如查询2009年秋季学期和2010年春季学期同时开课的所有课程。

select course_id from section as S
where semester = 'Fall' and year = 2009 and 
exists (
    select * from section as T
    where semester = 'Spring' and year = 2010 and S.course_id = T.course_id
);

上面的逻辑就是查询所有2009年秋季开课的课程，并对每一个课程，判断下section关系中，这堂课是否在2010年春季也开课了。

类似的还有not exists

重复元组存在性测试

unique只有在子查询返回的关系中不包含重复元组时返回true

找出在2009年最多只开过一次的课程

select S.course_id from course as S
where unique (
    select T.course_id from section as T
    where S.course_id = T.course_id
    and T.year = 2009
);

类似的还有not unique

PS: MYSQL不支持这个操作

from子句中的子查询

select-from-where子句返回的是一个关系，所以它的返回可以插入到sql语句中任何需要关系的位置，from子句中需要关系，所以可以把子查询应用到from子句中。

找出平均教员工资超过42000的系，以前会用分组+having的方式，换一种方式，先写出子查询

select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;

编写整个查询

select dept_name, avg_salary
from (
    select dept_name, avg(salary) as avg_salary
    from instructor
    group by dept_name
) where avg_salary > 42000;

注意，MYSQL中需要把每个子查询派生出来的新关系指定一个别名，所以要加as。

标量子查询

如果一个关系中只有一个元组，这个元组只有一个属性，这个关系可以被视作一个标量，对于一个查询返回的关系也是。

标量可以放在select子句后面，所以返回标量的子查询也可以放在select子句后面。

查询所有系以及系里的教师数量。

select dept_name, (
    select count(*) from instructor
    where instructor.dept_name = department.dept_name
) as num_instructors
from department;

标量子查询还可以用在where和having子句中。

习题

1. 找出Comp. Sci.系开设的具有3个学分的课程名称

   select title from course where dept_name='Comp. Sci.' and credits = 3;
   

2. 找出名叫Einstein的教师所教的所有学生的标识，保证结果中没有重复

   select distinct S.ID
   from
       instructor natural join teaches as T,
       takes as S
   where
       name = 'Einstein'
       and T.course_id = S.course_id;
   

3. 找出教师的最高工资

   select max(salary) from instructor;
   

4. 找出工资最高的所有教师

   select name from instructor
   where salary = (
       select max(salary) from instructor
   );
   

5. 找出2009年秋季开设的每个课程的选课人数
   错误语句 #1，问题出在我们使用了course_id作为一次选课的唯一条件了，而在这两个表中，还都需要semester、year和sec_id才能唯一确定一次选课。可以简单的通过在后面追加where过滤掉冗余数据。

   select course_id, count(*) from takes
   where course_id in (
       select course_id from section
       where semester = 'Fall' and year = 2009
   ) group by course_id;
   

   这里使用自然连接，我觉得自然连接会漂亮一点

   select course_id, count(*) as cnt
   from section natural join takes
   where semester = 'Fall' and year = 2009
   group by course_id;
   

6. 2009年秋季开设的所有课程段中，找出最多的选课人数
   注意在MySQL中必须给子查询添加个别名

   select max(cnt) from (
       select count(*) as cnt
       from section natural join takes
       where semester = 'Fall' and year = 2009
       group by course_id
   );