首页

This slide is based on Jeffrey D. Ullman's work, which can be download from his website.

目录

Why SQL?

为什么是 SQL?

• SQL 是一种非常高等级的语言
• 它表述的是“做什么(what to do)”，而不是“如何做(how to do it)”.
• 避开了许多数据操作的细节, 这在其他过程语言如 C++ 或 Java 中是必需的.
• 数据库管理系统指出执行查询的最好的方式.
• 这称之为“查询优化”.

Select-From-Where 语句

Select-From-Where 语句

SELECT 所需的属性
FROM 要查询的表(一个或多个表)
WHERE 表中元组需要满足的条件

运行的例子

这里所有查询的例子都是基于下面的数据库模式.

Beers(name, manf)
Bars(name, addr, license)
Drinkers(name, addr, phone)
Likes(drinker, beer)
Sells(bar, beer, price)
Frequents(drinker, bar)

加下划线的是组成键的属性.

例子

在关系 Beers(name, manf) 中找出哪种啤酒是由 Anheuser-Busch 生产的.

SELECT name
FROM Beers
WHERE manf = 'Anheuser-Busch';

查询的结果是一个新的关系, 仅有一个属性(name)构成. 返回的元组是所有由 Anheuser-Busch 生成的啤酒名称, 如 Bud 等.

实验

mysql> select name
     > from Beers
     > where manf='百威英博啤酒集团';

+---------------+
| name          |
+---------------+
| 百威          |
| 贝克          |
| Stella Artois |
+---------------+

单关系查询的含义

单关系查询的含义

• 从 FROM 子句声明的关系开始.
• 应用由 WHERE 子句标明的条件进行选择.
• 最后根据 SELECT 子句执行投影.

Operational Semantics

操作语义

执行查询操作过程中, 会定义一个元组变量 $t$, 它将在所有元组(即这个关系)中进行循环, 检查第二个属性的值 t.manf 是否等于 Anheuser-Busch. 在得到的结果中包含了 t.name, 最后执行投影操作.

Operational Semantics --- General

一般的操作语义

• 元组变量(tuple variable)访问 FROM 子句中提到的关系中的每个元组.
• 检查“当前”元组是否满足 WHERE 子句中的条件.
• 如果需要, 用当前元组中的值计算 SELECT 子句中的属性或表达式的值.

SELECT 语句中的 *

SELECT 语句中的 *

当 FROM 子句中仅有一个关系, 则 SELECT 语句中的 * 代表这个关系的所有属性.

例子: Beers(name, manf)

SELECT *
FROM Beers
WHERE manf = 'Anheuser-Busch';

查询的结果

查询的结果

返回的结果关系包含每个属性.

实验

mysql> select *
     > from Beers
     > where manf='百威英博啤酒集团';

+---------------+--------------------------+---------+---------+
| name          | manf                     | name_en | Country |
+---------------+--------------------------+---------+---------+
| 百威          | 百威英博啤酒集团         | NULL    | NULL    |
| 贝克          | 百威英博啤酒集团         | NULL    | NULL    |
| Stella Artois | 百威英博啤酒集团         | NULL    | NULL    |
+---------------+--------------------------+---------+---------+

重命名属性

重命名属性

对于得到的结果关系, 如果希望有不同的属性名字, 则可以使用 AS <new name>.

SELECT name AS beer, manf
FROM Beers
WHERE manf ='Anheuser-Busch';

查询的结果是

实验

mysql> select name as beer, manf
    -> from Beers
    -> where manf='百威英博啤酒集团';

+---------------+--------------------------+
| beer          | manf                     |
+---------------+--------------------------+
| 百威          | 百威英博啤酒集团         |
| 贝克          | 百威英博啤酒集团         |
| Stella Artois | 百威英博啤酒集团         |
+---------------+--------------------------+

SELECT 语句中的表达式

SELECT 语句中的表达式

任何有意义的表达式均可以作为 SELECT 语句的一个元素.

例如对于关系 Sells(bar, beer, price)

SELECT bar, beer,
	price*6.3424 AS priceInRMB
FROM Sells;

查询的结果是

实验

mysql> select bar, beer,
    -> price/6.2704 as priceInUSD
    -> from Sells;

+--------------------------+-----------------+--------------------+
| bar                      | beer            | priceInUSD         |
+--------------------------+-----------------+--------------------+
| 3DArtBar                 | 喜力            | 3.9869864761418725 |
| 3DArtBar                 | 嘉士伯          |  5.103342689461597 |
| 3DArtBar                 | 安贝夫          |  6.379178361826996 |
| 3DArtBar                 | 百威            |  4.784383771370247 |
| 3DArtBar                 | 贝克            |  5.581781066598622 |
| HardRock                 | 健力士黑啤      |  6.219698902781321 |
| HardRock                 | 喜力            |  4.465424853278898 |
| HardRock                 | 嘉士伯          |  4.784383771370247 |
| HardRock                 | 安贝夫          |  6.219698902781321 |
| HardRock                 | 百威            |  5.103342689461597 |
| HardRock                 | 科罗娜          |  6.379178361826996 |
| HardRock                 | 贝克            |  5.262822148507272 |
| Westside                 | 健力士黑啤      |  6.219698902781321 |
| Westside                 | 南非啤酒        |  5.741260525644297 |
| Westside                 | 朝日            |  4.146465935187548 |
| Westside                 | 百威            |  5.103342689461597 |
| Westside                 | 科罗娜          |  6.379178361826996 |
| Westside                 | 纯种苦啤酒      |  5.262822148507272 |
| Westside                 | 贝克            |  4.465424853278898 |
| 宝莱纳餐厅               | 健力士黑啤      |  6.219698902781321 |
| 宝莱纳餐厅               | 南非啤酒        |  5.741260525644297 |
| 宝莱纳餐厅               | 喜力            |  4.465424853278898 |
| 宝莱纳餐厅               | 朝日            |  4.146465935187548 |
| 宝莱纳餐厅               | 百威            |  5.103342689461597 |
| 宝莱纳餐厅               | 科罗娜          |  6.379178361826996 |
| 宝莱纳餐厅               | 纯种苦啤酒      |  5.262822148507272 |
| 扬州老啤酒厂酒吧         | Stella Artois   |  5.741260525644297 |
| 扬州老啤酒厂酒吧         | 健力士黑啤      |  4.146465935187548 |
| 扬州老啤酒厂酒吧         | 喜力            |  5.581781066598622 |
| 扬州老啤酒厂酒吧         | 安贝夫          |  6.379178361826996 |
| 扬州老啤酒厂酒吧         | 百威            |  5.103342689461597 |
| 扬州老啤酒厂酒吧         | 科罗娜          |  6.379178361826996 |
| 扬州老啤酒厂酒吧         | 米勒            |  7.973972952283745 |
| 扬州老啤酒厂酒吧         | 纯种苦啤酒      |  5.262822148507272 |
| 扬州老啤酒厂酒吧         | 贝克            |  6.698137279918346 |
| 扬州老啤酒厂酒吧         | 麒麟            |  6.219698902781321 |
| 木板房啤酒吧             | 嘉士伯          |  6.857616738964021 |
| 木板房啤酒吧             | 安贝夫          |  6.379178361826996 |
| 木板房啤酒吧             | 生力            | 3.9869864761418725 |
| 木板房啤酒吧             | 米勒            |  5.103342689461597 |
| 木板房啤酒吧             | 贝克            |  5.581781066598622 |
| 木板房啤酒吧             | 麒麟            |  4.784383771370247 |
| 苏荷酒吧                 | 健力士黑啤      |  6.219698902781321 |
| 苏荷酒吧                 | 南非啤酒        |  5.741260525644297 |
| 苏荷酒吧                 | 喜力            |  4.465424853278898 |
| 苏荷酒吧                 | 朝日            |  4.146465935187548 |
| 苏荷酒吧                 | 生力            | 6.0602194437356465 |
| 苏荷酒吧                 | 百威            |  5.103342689461597 |
| 苏荷酒吧                 | 科罗娜          |                  0 |
| 苏荷酒吧                 | 纯种苦啤酒      |               NULL |
+--------------------------+-----------------+--------------------+

常数作为表达式

常数作为表达式

SELECT drinker,
	'likes Bud' AS whoLikesBud
FROM Likes
WHERE beer = 'Bud';

查询的结果是

实验

mysql> select drinker, 
     > 'likes 百威' as whoLikesBudweiser
    -> from Likes
    -> where beer='百威';

+-----------------+-------------------+
| drinker         | whoLikesBudweiser |
+-----------------+-------------------+
| Charles Babbage | likes 百威        |
| John Hennessy   | likes 百威        |
+-----------------+-------------------+

Information Integration

信息集成

对许多来自各种源的数据, 我们经常建立数据仓库(data warehouses).

假设每个酒吧(bar)有自己的关系 Menu(beer, price).

为了将酒吧的信息集中到 Sells(bar, beer, price), 我们需要查询每个酒吧, 并将其名称插入到关系 Sells 中.

举个例子, 对于 Joe's Bar, 我们可以执行下面的查询:

SELECT 'Joe''s Bar', beer, price FROM Menu;

WHERE 语句中的复杂条件

WHERE 语句中的复杂条件

• 布尔算子 AND, OR, NOT.
• 比较运算符: =, <>, <, >, <=, >=.
• 以及可以生成布尔值的其他算子.

例子: 复杂条件

例子: 复杂条件

对于关系 Sells(bar, beer, price), 查询 Joe's Bar 关于啤酒 Bud 的售价:

SELECT price
FROM Sells
WHERE bar = 'Joe''s Bar' 
	AND beer = 'Bud';

实验

mysql> select price
    -> from Sells
    -> where bar='Westside'
    -> and beer='百威';

+-------+
| price |
+-------+
|    32 |
+-------+

模式(Patterns)

模式(Patterns)

模式是指用来匹配字符串的. 形如:

• <Attribute> LIKE <pattern> or
• <Attribute> NOT LIKE <pattern>

模式是用引号括起来的一个字符串, 其中 % 可以匹配任意字符串, 而 _ 匹配任意单个字符.

例子: LIKE

例子: LIKE

对于关系 Drinkers(name, addr, phone), 找出电话号码中有 555- 的顾客.

SELECT name
FROM Drinkers
WHERE phone LIKE '%555-_ _ _ _';

实验

mysql> select name
    -> from Drinkers
    -> where phone like '%723-____';

+---------------+
| name          |
+---------------+
| John Hennessy |
+---------------+

空值(NULL Values)

空值(NULL Values)

SQL 关系中的元组可以在一个或多个字段上取值 NULL.

其具体意思要取决于上下文, 比如两个常见的情形:

• Missing value: 例如, 我们知道 Joe 的酒吧应该有个地址, 但是我们不知道它的确切地址是什么.
• Inapplicable: 例如, 对于一个未婚者, 属性 spouse 的值当然是不适用的.

比较 NULL 与其他值

比较 NULL 与其他值

SQL 中的条件的逻辑值实际上有三个: TRUE, FALSE, UNKNOWN.

• MySQL 中的逻辑值也是这三个. 注意 UNKNOWN 与 NULL 的区别.

任何值(包括 NULL 自身)与 NULL 的比较均得到值 UNKNOWN.

一个元组成为查询的结果当且仅当 WHERE 子句为 TRUE (不是 FALSE, 也不是UNKNOWN).

实验

mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;

+-------+-------------+----------+
| 1 = 1 | NULL = NULL | 1 = NULL |
+-------+-------------+----------+
|     1 |        NULL |     NULL |
+-------+-------------+----------+

不过 MySQL 中有一个安全等于比较符<=>.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;

+---------+---------------+------------+
| 1 <=> 1 | NULL <=> NULL | 1 <=> NULL |
+---------+---------------+------------+
|       1 |             1 |          0 |
+---------+---------------+------------+

尝试下面的查询.

mysql > select NULL <=> unknown;

mysql > select NULL is unknown;

三个逻辑值

三个逻辑值

为理解 AND, OR, NOT 三个布尔运算是如何工作的, 我们将 TRUE 和 FALSE 分别等同于 1 和 0, UNKNOWN 等于 $\frac{1}{2}$.

AND=MIN; OR=MAX, NOT($x$)=$1-x$.

例子

TRUE AND (FALSE OR NOT(UNKNOWN))
=MIN(1, MAX(0,(1-1/2)))
=MIN(1, MAX(0,1/2))
=MIN(1, 1/2)
=1/2
=UNKNOWN

实验

mysql> select TRUE and (FALSE or NOT(NULL));

+-------------------------------+
| TRUE and (FALSE or NOT(NULL)) |
+-------------------------------+
|                          NULL |
+-------------------------------+

试一下下面的.

mysql> select TRUE and (FALSE or NOT(UNKNOWN));

一个令人惊讶的例子

一个令人惊讶的例子

对于下面的 Sells 关系

SELECT bar
FROM Sells
WHERE price < 2.00 OR price >= 2.00;

由于 Joe 的酒店中 Bud 啤酒的价格为 NULL, 因此 price 与 2.00 的比较均为 UNKNOWN, 两者取或也是 UNKNOWN.

实验

mysql> select bar, beer
    -> from Sells
    -> where price < 2.00 or price >=2.00;

发现价格为 NULL 的行不出现在结果中.

原因: 二值定律不等于三值定律

原因: 二值定律不等于三值定律

一些定律, 如 AND 的交换律, 在三值逻辑下也是成立的.

但是有的可能不成立, 例如:

p OR NOT p = TRUE

• 当 p=UNKNOWN 时, \[\text{MAX}(\frac{1}{2}, (1-\frac{1}{2}))=\frac{1}{2}\neq 1.\]

多关系查询(Multirelation queries)

多关系查询

许多有趣的查询都是将两个或两个以上关系中的数据结合起来的.

要对多个关系进行查询, 首先将它们的名字列出在 FROM 子句中.

如果不同关系有同名的属性, 则在它们的属性名前加上关系名加以区别. 如 <relation>.<attribute>

例子: 连接两个关系

例子: 连接两个关系

对于关系 Likes(drinker, beer) 和 Frequents(drinker, bar), 列出经常光顾 Joe 酒吧的顾客所喜欢的啤酒.

SELECT beer
FROM Likes, Frequents
WHERE bar='Joe''s Bar' AND
	Frequents.drinker=Likes.drinker;

实验

mysql> SELECT beer
    -> FROM Likes, Frequents
    -> WHERE bar='苏荷酒吧' AND
    -> Frequents.drinker=Likes.drinker;

+-----------------+
| beer            |
+-----------------+
| 健力士黑啤      |
+-----------------+

形式上和操作上的语义

形式上的语义

多关系查询与单关系查询的语义基本一致.

1. 首先将 FROM 语句中出现的关系作乘积.
2. 然后对于 WHERE 子句应用选择条件.
3. 最后将所得到的结果按照 SELECT 子句中列出的属性和表达式进行投影.

操作上的语义

对于 FROM 子句中的每个关系, 对应一个元组变量. 这些元组变量访问每个元组的组合.

如果这些元组变量所指向的元组组合满足 WHERE 子句中的条件, 则将这些元组送往 SELECT 子句.

例子

例子

关系的拷贝

关系的拷贝

• 有时, 某个查询需要用到同一个关系的两个拷贝.
• 区别拷贝的方法是, 在 FROM 子句中在关系名后面跟一个别名, 也就是对关系进行重命名.
• 对关系名进行重命名这个方式是经常采用的, 即使有时并不是必需的.
• 重命名时建议加上关键字 AS, 当然在不至于混淆时可以不加.

例子: Self-Join

例子: Self-Join

对于关系 Beers(name, manf), 找出产自同一个厂家的两种啤酒.

• 要求不能出现 (Bud, Bud) 这样的元组.
• 按字母顺序列出, 如 (Bud, Miller), 而不是(Miller, Bud).

SELECT b1.name, b2.name
FROM Beers b1, Beers b2
WHERE b1.manf = b2.manf AND
	b1.name < b2.name;

实验

mysql> SELECT b1.name, b2.name
    -> FROM Beers b1, Beers b2
    -> WHERE b1.manf = b2.manf AND
    -> b1.name < b2.name;

+---------------+--------+
| name          | name   |
+---------------+--------+
| Stella Artois | 百威   |
| 百威          | 贝克   |
| Stella Artois | 贝克   |
+---------------+--------+

子查询(subqueries)

子查询(subqueries)

用圆括号括起来的 SELECT-FROM-WHERE语句(也就是子查询)可以被作为值用在多个地方, 包括 FROM 子句和 WHERE子句.

例如: 在 FROM 子句中的本来放置关系的位置, 可以换上子查询. 但对子查询所得关系必须给定一个新变量名称.

例子: 子查询的形式

例子: 子查询的形式

列出经常去 Joe 的酒吧的顾客所喜爱的啤酒名称.

SELECT beer
FROM Likes, (SELECT drinker
	FROM Frequents
	WHERE bar='Joe''s Bar') JD
WHERE Likes.drinker=JD.drinker;

小括号内的就是经常去 Joe 的酒吧喝酒的酒客.

实验

mysql> SELECT beer
    -> FROM Likes, (SELECT drinker
    -> FROM Frequents
    -> WHERE bar='Westside') JD
    -> WHERE Likes.drinker=JD.drinker;

+-----------------+
| beer            |
+-----------------+
| 健力士黑啤      |
| Stella Artois   |
| 贝克            |
| 贝克            |
+-----------------+

注意所得到的结果中可能会存在重复.

返回单个元组的子查询

返回单个元组的子查询

如果子查询能够保证返回单个元组, 则子查询可以作为一个值.

• 通常的, 该元组仅有一个分量.
• 如果没有元组返回或是有多个返回, 则会发生错误.

例子: 单值子查询(single-tuple subquery)

例子: 单值子查询(single-tuple subquery)

对于关系 Sells(bar, beer, price), 找出那些卖 Miller 酒且价格与 Joe 的酒吧中 Bud 售价一致的酒吧.

下面两个查询将完成此项任务.

• 找出 Joe 的酒吧关于 Bud 啤酒的销售价格.
• 找出以同样价格销售 Miller 啤酒的酒吧.

Query+Subquery Solution

查询加子查询的解决办法

SELECT bar
FROM Sells
WHERE beer='Miller' AND
	price=(SELECT price
		From Sells
		WHERE bar='Joe''s Bar'
		AND beer='Bud');

实验

mysql> SELECT bar
    -> FROM Sells
    -> WHERE beer='贝克' AND
    -> price=(SELECT price
    -> From Sells
    -> WHERE bar='Westside'
    -> AND beer='纯种苦啤酒');

+----------+
| bar      |
+----------+
| HardRock |
+----------+

The IN Operator

In 操作符

<tuple> IN (<subquery>) 为真当且仅当这个 tuple 是这个子查询生成的关系中的成员.

如果表示不在这个关系中, 则使用 <tuple> NOT IN (<subquery>)

IN 表达式可以出现在 WHERE 子句中.

例子: IN

IN 语句的例子

对于关系 Beers(name, manf) 和 Likes(drinker, beer), 找出 Fred 喜欢的啤酒的名称和制造商.

SELECT *
FROM Beers
WHERE name IN (SELECT beer
	FROM Likes
	WHERE drinker='Fred');

实验

mysql> SELECT *
    -> FROM Beers
    -> WHERE name IN (SELECT beer
    -> FROM Likes
    -> WHERE drinker='John Hennessy');

+--------+--------------------------+
| name   | manf                     |
+--------+--------------------------+
| 百威   | 百威英博啤酒集团         |
+--------+--------------------------+

回忆 Lecture 1 中的例子

下面是 Lecture 1 中曾提到的例子

SELECT a
FROM R, S
WHERE R.b = S.b;

SELECT a
FROM R
WHERE b IN (SELECT b FROM S);

两者的区别

两者的区别

EXISTS 运算符

EXISTS 运算符

EXISTS(<subquery>) 为真当且仅当子查询的结果非空.

例子: 对于 Beers(name, manf), 找出厂家只生产一种啤酒的那些啤酒名称.

SELECT name
FROM Beers b1
WHERE NOT EXISTS (
	SELECT *
	FROM Beers
	WHERE manf=b1.manf AND
		name <> b1.name);

• 这里子查询的结果是由同一厂家生产但品牌不一样的啤酒集合.
• <> 是不等于操作符.
• 注意范围规则: 子查询中 WHERE 后面第一个 manf 是指离他最近的 FROM 中关系 Beers 的属性.

下面的查询语句是否与上面的等价?

SELECT name
FROM Beers b1
WHERE manf NOT IN (
	SELECT b1.manf
	FROM b1
	WHERE manf=b1.manf AND
		name <> b1.name);

如果上面的查询语句有问题, 请修改, 使之能在 MySQL 下运行.

实验

mysql> SELECT name
    -> FROM Beers b1
    -> WHERE NOT EXISTS (
    -> SELECT *
    -> FROM Beers
    -> WHERE manf=b1.manf AND
    -> name <> b1.name);

+-----------------+
| name            |
+-----------------+
| 喜力            |
| 嘉士伯          |
| 安贝夫          |
| 南非啤酒        |
| 米勒            |
| 科罗娜          |
| 朝日            |
| 麒麟            |
| 生力            |
| 健力士黑啤      |
| 纯种苦啤酒      |
+-----------------+
11 rows in set (0.35 sec)

下面的代码由姚凯哲(信科1501)给出.

mysql> SELECT name
    -> FROM Beers b1
    -> WHERE manf NOT IN (
    -> SELECT b2.manf
    -> FROM Beers b2
    -> WHERE b1.manf = b2.manf AND
    -> b1.name <> b2.name);

ANY 运算符

ANY 运算符

$x$=ANY(<subquery>) 是一个布尔值条件. 这里此表达式的值为真当且仅当子查询结果中至少有一个元组与 $x$ 相等. 其中 = 也可以换成任何其他的比较运算符, 比如 <, <=, >, >= 或 <> 等. 前提是能够比较.

例如: 表达式 $x$>ANY(<subquery>) 为真, 意指 $x$ 并非子查询结果中最小的元组.

注意: 为了能比较, 元组必须仅有一个字段.

ALL 运算符

ALL 运算符

$x$<>ALL(<subquery>)为真当且仅当对于子查询所得的关系中的每个元组 $t$, $x$ 都不等于 $t$. 也即, $x$ 不属于子查询的结果.

其中 <> 可以换成任何其他的比较运算符.

例如: 表达式 $x$>=ALL(<subquery>) 为真, 意指子查询结果中没有比 $x$ 来得大的元组.

例子: ALL

对于关系 Sells(bar, beer, price), 找出售价最高的啤酒.

SELECT beer
FROM Sells
WHERE price >= ALL(
	SELECT price
	FROM Sells);

使用 MAX(price) 也可以做到.

SELECT beer
FROM Sells
WHERE price = (
	SELECT MAX(price)
	FROM Sells);

实验

mysql> SELECT beer
    -> FROM Sells
    -> WHERE price >= ALL(
    -> SELECT price
    -> FROM Sells);

+--------+
| beer   |
+--------+
| 米勒   |
+--------+
1 row in set (0.00 sec)

mysql> SELECT beer
    -> FROM Sells
    -> WHERE price >= ALL(
    -> SELECT price
    -> FROM Sells
    -> WHERE price >=10 or price < 10
    ->);

并、交、差(union, intersection, and difference)

关系的并、交、差

关系的并、交、差采用如下的表达式, 每个涉及到子查询.

• (<subquery>) UNION (<subquery>)
• (<subquery>) INTERSECT (<subquery>)
• (<subquery>) EXCEPT (<subquery>)

实验

注意, MySQL 并不支持 INTERSECT, EXCEPT, MINUS. 我们需要使用其他语句来替代这些功能. 对于交, 使用连接. 对于差, 使用 NOT IN

不过 MySQL 支持 UNION

mysql> (SELECT * FROM Bars)
    -> UNION
    -> (SELECT * FROM Bars);
+--------------------------+---------------------------------+---------+
| name                     | addr                            | license |
+--------------------------+---------------------------------+---------+
| 3DArtBar                 | 上海虹梅路3338弄9－11号         | BHXGZX  |
| HardRock                 | 上海政通路315号                 | XJ8QGF  |
| Westside                 | 上海衡山路237号                 | EDS9HF  |
| 宝莱纳餐厅               | 上海徐汇区汾阳路150号           | SKAPXJ  |
| 扬州老啤酒厂酒吧         | 广陵区南通东路128号             | 8NZBSG  |
| 木板房啤酒吧             | 上海子长路350号                 | YJVI9A  |
| 苏荷酒吧                 | 扬州1912                        | LYMTD5  |
+--------------------------+---------------------------------+---------+
7 rows in set (0.00 sec)

mysql> (select 1) union (select 2) union (select 1);
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+

说明是集合运算. 如果强调结果允许重复, 则使用 UNION ALL.

例子: Intersection

例子: Intersection

对于关系 Likes(drinker, beer), Sells(bar, beer, price), 和 Frequents(drinker, bar), 找出满足下面条件的酒客和啤酒.

1. 酒客喜欢这种啤酒,
2. 酒客经常光顾卖此啤酒的酒吧(至少一家).

解决方案

(SELECT * FROM Likes)
	INTERSECT
(SELECT drinker, beer
 FROM Sells, Frequents
 WHERE Frequents.bar=Sells.bar
);

• 注意: 子查询实际上是一个临时存储的表.
• 第二个子查询表示的是酒客经常去卖这种啤酒的酒吧.

实验(under MySQL)

mysql> SELECT Likes.drinker, Likes.beer
    -> FROM Likes INNER JOIN 
    -> (SELECT drinker, beer FROM Sells, Frequents
    -> WHERE Frequents.bar=Sells.bar)
    -> AS Temp USING (beer)
    -> ORDER BY Likes.drinker;

你会发现结果中有很多是重复的. 因此使用 DISTINCT 关键字.

mysql> SELECT distinct Likes.drinker, Likes.beer 
    -> FROM Likes INNER JOIN
    -> (SELECT drinker, beer FROM Sells, Frequents WHERE Frequents.bar=Sells.bar)
    -> AS Temp USING (beer) 
    -> ORDER BY Likes.drinker;
+---------------------+-----------------+
| drinker             | beer            |
+---------------------+-----------------+
| Alan Turing         | 生力            |
| Andrew Chi-Chih Yao | 生力            |
| Charles Babbage     | 百威            |
| Charles Babbage     | 嘉士伯          |
| Charles Babbage     | 米勒            |
| Claude Shannon      | 健力士黑啤      |
| David Patterson     | 朝日            |
| Donald Ervin Knuth  | 安贝夫          |
| Edsger Dijkstra     | 南非啤酒        |
| Gerald Jay Sussman  | 纯种苦啤酒      |
| Gordon Moore        | 健力士黑啤      |
| Guy Steele Jr.      | 安贝夫          |
| Herbert A. Simon    | 健力士黑啤      |
| John Hennessy       | 百威            |
| John von Neumann    | 喜力            |
| Lynn Conway         | 贝克            |
| Lynn Conway         | Stella Artois   |
| Tim Berners-Lee     | 麒麟            |
| Tim Berners-Lee     | 科罗娜          |
| Tony Hoare          | 南非啤酒        |
+---------------------+-----------------+
20 rows in set (0.01 sec)

Bag semantics

Bag semantics

尽管 SELECT-FROM-WHERE 语句使用的是包(bag)的语义, 但对于并、交、差(union, intersection, and difference)来说, 默认使用的是集合(set)的语义.

也就是说, 当执行并、交、差操作后, 重复的元组被消除了.

Motivation: Efficiency

动机: 效率

当进行投影操作时, 避免消除重复元组会使投影操作很简单.

• 就是一次对于一个元组执行投影.

而对于交(intersection)或差(difference)运算, 先将关系进行排序会使效率大大提高.

• 从这一点来看, 此时我们是希望消除重复元组的.

Controlling Duplicate Elimination

控制重复消除

强制一个关系变成一个集合, 使用
SELECT DISTINCT ...

强制一个关系变成一个包(即, 不要消除重复的元组), 使用 ALL, 比如
... UNION ALL ...

例子: DISTINCT

例子: DISTINCT

对于关系 Sells(bar, beer, price), 列出所有啤酒的不同售价.

SELECT DISTINCT price
FROM Sells;

注意: 如果没有 DISTINCT, 则有多少 bar/beer 对, 相应的价格(price)就列出来, 就会造成重复.

实验

mysql> SELECT DISTINCT price
    -> FROM Sells;
+-------+
| price |
+-------+
|    25 |
|    32 |
|    40 |
|    30 |
|    35 |
|    39 |
|    28 |
|    33 |
|    36 |
|    26 |
|    50 |
|    42 |
|    43 |
|    38 |
+-------+
14 rows in set (0.00 sec)

例子: ALL

例子: ALL

对于关系 Frequents(drinker, bar) 和 Likes(drinker, beer):

(SELECT drinker FROM Frequents)
	EXCEPT ALL
(SELECT drinker FROM Likes);

这个查询的意思是: 找出经常光顾酒吧但不喜欢啤酒的酒客. 并且在进行差运算时不消除重复的元组.

实验(under MySQL)

mysql> select drinker from Frequents
    -> where drinker not in
    -> (select drinker from Likes);
Empty set (0.00 sec)

不妨插入一些数据:

mysql> insert into Frequents values("atzjg","宝莱纳餐厅");
mysql> insert into Frequents values("atzjg","Westside");

再次执行刚才的查询, 得到如下结果.

+---------+
| drinker |
+---------+
| atzjg |
| atzjg |
+---------+
2 rows in set (0.00 sec)

Join Expressions

Join 表达式

SQL 提供了包连接(bag join)的几个版本.

这些表达式可以是单独的查询, 或者是用于作为 FROM 子句中的关系.

乘积和自然连接

乘积和自然连接

自然连接(Natural Join):
R NATURAL JOIN S;

乘积(Product):
R CROSS JOIN S;

例子

Likes NATURAL JOIN Sells;

其中关系也可以是用圆括号界定的子查询.

实验

mysql> SELECT drinker, beer, bar, price 
    -> from Likes
    -> NATURAL JOIN Sells;

+---------------------+-----------------+--------------------------+-------+
| drinker             | beer            | bar                      | price |
+---------------------+-----------------+--------------------------+-------+
| John von Neumann    | 喜力            | 3DArtBar                 |    25 |
| Charles Babbage     | 嘉士伯          | 3DArtBar                 |    32 |
| Donald Ervin Knuth  | 安贝夫          | 3DArtBar                 |    40 |
| Guy Steele Jr.      | 安贝夫          | 3DArtBar                 |    40 |
| Charles Babbage     | 百威            | 3DArtBar                 |    30 |
| John Hennessy       | 百威            | 3DArtBar                 |    30 |
| Lynn Conway         | 贝克            | 3DArtBar                 |    35 |
| Claude Shannon      | 健力士黑啤      | HardRock                 |    39 |
| Gordon Moore        | 健力士黑啤      | HardRock                 |    39 |
| Herbert A. Simon    | 健力士黑啤      | HardRock                 |    39 |
| John von Neumann    | 喜力            | HardRock                 |    28 |
| Charles Babbage     | 嘉士伯          | HardRock                 |    30 |
| Donald Ervin Knuth  | 安贝夫          | HardRock                 |    39 |
| Guy Steele Jr.      | 安贝夫          | HardRock                 |    39 |
| Charles Babbage     | 百威            | HardRock                 |    32 |
| John Hennessy       | 百威            | HardRock                 |    32 |
| Tim Berners-Lee     | 科罗娜          | HardRock                 |    40 |
| Lynn Conway         | 贝克            | HardRock                 |    33 |
| Claude Shannon      | 健力士黑啤      | Westside                 |    39 |
| Gordon Moore        | 健力士黑啤      | Westside                 |    39 |
| Herbert A. Simon    | 健力士黑啤      | Westside                 |    39 |
| Edsger Dijkstra     | 南非啤酒        | Westside                 |    36 |
| Tony Hoare          | 南非啤酒        | Westside                 |    36 |
| David Patterson     | 朝日            | Westside                 |    26 |
| Charles Babbage     | 百威            | Westside                 |    32 |
| John Hennessy       | 百威            | Westside                 |    32 |
| Tim Berners-Lee     | 科罗娜          | Westside                 |    40 |
| Gerald Jay Sussman  | 纯种苦啤酒      | Westside                 |    33 |
| Lynn Conway         | 贝克            | Westside                 |    28 |
| Claude Shannon      | 健力士黑啤      | 宝莱纳餐厅               |    39 |
| Gordon Moore        | 健力士黑啤      | 宝莱纳餐厅               |    39 |
| Herbert A. Simon    | 健力士黑啤      | 宝莱纳餐厅               |    39 |
| Edsger Dijkstra     | 南非啤酒        | 宝莱纳餐厅               |    36 |
| Tony Hoare          | 南非啤酒        | 宝莱纳餐厅               |    36 |
| John von Neumann    | 喜力            | 宝莱纳餐厅               |    28 |
| David Patterson     | 朝日            | 宝莱纳餐厅               |    26 |
| Charles Babbage     | 百威            | 宝莱纳餐厅               |    32 |
| John Hennessy       | 百威            | 宝莱纳餐厅               |    32 |
| Tim Berners-Lee     | 科罗娜          | 宝莱纳餐厅               |    40 |
| Gerald Jay Sussman  | 纯种苦啤酒      | 宝莱纳餐厅               |    33 |
| Lynn Conway         | Stella Artois   | 扬州老啤酒厂酒吧         |    36 |
| Claude Shannon      | 健力士黑啤      | 扬州老啤酒厂酒吧         |    26 |
| Gordon Moore        | 健力士黑啤      | 扬州老啤酒厂酒吧         |    26 |
| Herbert A. Simon    | 健力士黑啤      | 扬州老啤酒厂酒吧         |    26 |
| John von Neumann    | 喜力            | 扬州老啤酒厂酒吧         |    35 |
| Donald Ervin Knuth  | 安贝夫          | 扬州老啤酒厂酒吧         |    40 |
| Guy Steele Jr.      | 安贝夫          | 扬州老啤酒厂酒吧         |    40 |
| Charles Babbage     | 百威            | 扬州老啤酒厂酒吧         |    32 |
| John Hennessy       | 百威            | 扬州老啤酒厂酒吧         |    32 |
| Tim Berners-Lee     | 科罗娜          | 扬州老啤酒厂酒吧         |    40 |
| Charles Babbage     | 米勒            | 扬州老啤酒厂酒吧         |    50 |
| Gerald Jay Sussman  | 纯种苦啤酒      | 扬州老啤酒厂酒吧         |    33 |
| Lynn Conway         | 贝克            | 扬州老啤酒厂酒吧         |    42 |
| Tim Berners-Lee     | 麒麟            | 扬州老啤酒厂酒吧         |    39 |
| Charles Babbage     | 嘉士伯          | 木板房啤酒吧             |    43 |
| Donald Ervin Knuth  | 安贝夫          | 木板房啤酒吧             |    40 |
| Guy Steele Jr.      | 安贝夫          | 木板房啤酒吧             |    40 |
| Alan Turing         | 生力            | 木板房啤酒吧             |    25 |
| Andrew Chi-Chih Yao | 生力            | 木板房啤酒吧             |    25 |
| Charles Babbage     | 米勒            | 木板房啤酒吧             |    32 |
| Lynn Conway         | 贝克            | 木板房啤酒吧             |    35 |
| Tim Berners-Lee     | 麒麟            | 木板房啤酒吧             |    30 |
| Claude Shannon      | 健力士黑啤      | 苏荷酒吧                 |    39 |
| Gordon Moore        | 健力士黑啤      | 苏荷酒吧                 |    39 |
| Herbert A. Simon    | 健力士黑啤      | 苏荷酒吧                 |    39 |
| Edsger Dijkstra     | 南非啤酒        | 苏荷酒吧                 |    36 |
| Tony Hoare          | 南非啤酒        | 苏荷酒吧                 |    36 |
| John von Neumann    | 喜力            | 苏荷酒吧                 |    28 |
| David Patterson     | 朝日            | 苏荷酒吧                 |    26 |
| Alan Turing         | 生力            | 苏荷酒吧                 |    38 |
| Andrew Chi-Chih Yao | 生力            | 苏荷酒吧                 |    38 |
| Charles Babbage     | 百威            | 苏荷酒吧                 |    32 |
| John Hennessy       | 百威            | 苏荷酒吧                 |    32 |
| Tim Berners-Lee     | 科罗娜          | 苏荷酒吧                 |    40 |
| Gerald Jay Sussman  | 纯种苦啤酒      | 苏荷酒吧                 |    33 |
+---------------------+-----------------+--------------------------+-------+
75 rows in set (0.00 sec)

mysql> SELECT * 
    -> from Likes
    -> CROSS JOIN Sells;

结果是这两张表的笛卡尔积

Theta 连接($\theta$-Join)

Theta 连接($\theta$-Join)

R JOIN S ON <condition>

例子

对于关系 Drinkers(name, addr) 和 Frequents(drinker, bar):

Drinkers JOIN Frequents ON
	name = drinker;

这个语句执行后给出了所有形如 (d,a,d,b) 的四元组, 意指酒客 d 居住在 a, 并且经常去酒吧 b 喝酒.

实验

mysql> select Drinkers.name,Drinkers.addr,
    -> Frequents.drinker,Frequents.bar
    -> from Drinkers 
    -> Join Frequents ON Drinkers.name=Frequents.drinker;

+---------------------+--------------------------------------------------------------------------------------------+---------------------+--------------------------+
| name                | addr                                                                                       | drinker             | bar                      |
+---------------------+--------------------------------------------------------------------------------------------+---------------------+--------------------------+
| Alan Turing         | 英国剑桥大学                                                                               | Alan Turing         | HardRock                 |
| Andrew Chi-Chih Yao | Institute for Interdisciplinary Information Sciences,Tsinghua University,Beijing,P.R.China | Andrew Chi-Chih Yao | 宝莱纳餐厅               |
| Charles Babbage     | 英国伦敦波特兰广场德文郡街5号                                                              | Charles Babbage     | 3DArtBar                 |
| Claude Shannon      | 密歇根州佩托斯基                                                                           | Claude Shannon      | 木板房啤酒吧             |
| David Patterson     | 美国加州柏克莱市                                                                           | David Patterson     | 扬州老啤酒厂酒吧         |
| Donald Ervin Knuth  | 美国威斯康星州密尔沃基                                                                     | Donald Ervin Knuth  | 3DArtBar                 |
| Edsger Dijkstra     | 荷兰Nuenen                                                                                 | Edsger Dijkstra     | HardRock                 |
| Gerald Jay Sussman  | 美国麻省理工学院                                                                           | Gerald Jay Sussman  | 宝莱纳餐厅               |
| Gordon Moore        | 美国加利福尼亚州旧金山                                                                     | Gordon Moore        | 苏荷酒吧                 |
| Guy Steele Jr.      | 美国密苏里州                                                                               | Guy Steele Jr.      | 3DArtBar                 |
| atzjg             | atzjg.net,atzjg.com                                                                                  | atzjg             | Westside                 |
| atzjg             | atzjg.net,atzjg.com                                                                                  | atzjg             | 宝莱纳餐厅               |
| Herbert A. Simon    | 德国达姆施塔特                                                                             | Herbert A. Simon    | Westside                 |
| John Hennessy       | Building 10, situated in the Inner Quad at the heart of the Stanford campus.               | John Hennessy       | 木板房啤酒吧             |
| John von Neumann    | 匈牙利布达佩斯                                                                             | John von Neumann    | 扬州老啤酒厂酒吧         |
| Lynn Conway         | 密歇根州安娜堡市密歇根大学3640 CSE 大楼                                                    | Lynn Conway         | Westside                 |
| Tim Berners-Lee     | 32 Vassar Street MIT Room 32-G524 Cambridge MA 02139 USA                                   | Tim Berners-Lee     | HardRock                 |
| Tony Hoare          |                                                                                            | Tony Hoare          | 宝莱纳餐厅               |
+---------------------+--------------------------------------------------------------------------------------------+---------------------+--------------------------+
18 rows in set (0.00 sec)

内连接与外连接

内连接与外连接

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

End