PostgreSQL7.0手册-教程 -69. SQL-----（转载）
|
SQL 已经成为了最流行的关系查询语言。SQL”的名称是Structured Query Language（结构化查询语言）的缩写。在 1974 年，Donald Chamberlin 和其他人在 IBM 的研究所定义了语言 SEQUEL （Structured English Query Language）（结构化英语查询语言）。这种语言在 1974-75 年首先在一种叫 SEUEL-XRM 的 IBM 的原型里面实现。到了 1976-77 年，定义了一种叫 SEQUEL/2 的 SEQUEL 改进版，并且名称也因此改成 SQL。 在 1977 年，IBM 开发了一种新的叫 System R 的原型语言。 System R 实现了 SEQUEL/2 （现在的 SQL）的很大一部分子集，并且在项目过程中对SQL 做了许多修改。System R 被安装到了许多用户节点上，包括 IBM 的节点和许多经过选择的客户节点上。多亏了在 System R 那些用户节点上的成功，IBM 开始开发基于 System R 技术的 SQL 语言的商业产品。 再过了一年，IBM 和许多其他提供商宣布了许多 SQL 产品，例如 SQL/DS（IBM），DB2（IBM），ORACLE（Oracle Corp.），DG/SQL（Data General Corp.），和 SYBASE （Sybase Inc.）。 SQL 现在还是一个官方标准。在 1982 年，美国国家标准局（ANSI）公布了数据库委员会宪章（Database Committee）X3H2，建议发展一种标准的关系语言。这个建议在 1986 年被批准并且考虑了实际上是 IBM 版本的 SQL。在 1987 年，这个ANSI 标准也被国际标准化组织（ISO）接受为国际标准。这个最初的SQL 版本的标准经常非正式的被称为"SQL/86"。到了 1989 年，那个最初的标准被扩展了，并且这个新的标准被经常地非正式的称为 "SQL/89"。同样在 1989 年，一个相关的标准，发展了称之为数据库语言嵌入SQL （Database Language Embedded SQL）（ESQL）。 ISO 和 ANSI 委员会已经在一个大大地扩展了最初的标准的新版本的定义上工作了好几年，常被非正式的称之为SQL2 或 SQL/92。这个版本成为一个批准了的标准 - "International Standard ISO/IEC 9075:1992, Database Language SQL" - 在1992 年晚些时候。SQL/92 是人们常说的 "SQL 标准" 时所指的版本。SQL/92 的详细描述在 Date and Darwen, 1997 里给出。在我们写作这份文档的时候，一个非正式地称为SQL3的新的标准正在制订。这个新的标准准备把SQL 变成一种旋转完成（Turing-complete）语言，也就是说，所有可计算的查询（例如，递归查询）都将是可能的。这是一个非常复杂的任务，因而新标准的完成不会在 1999 年以前。 关系数据模型 如上所述，SQL 是一种关系语言。那意味着它是基于 E.F. Codd 在 1970 年首次发表的关系数据模型。我们将在稍后给出关系模型的正式描述（在 关系数据模型公式里），不过我们先用一个更直观的视角看看这个模型。 一个关系数据库是被其用户看作一个表的集合的数据库（而且除表之外没有其他东西）。一个表由行和列组成，每行代表一条记录，每列代表一个包含在表里面的记录的属性。供应商和部件数据库 演示了一个由三个表组成的数据库： SUPPLIER 是存储供应商数字（SNO），名称（SNAME）和城市（CITY）的表。 PART 是存储部件的数字（PNO）名称（PNAME）和价格（PRICE）的表。 SELLS 存储关于某个供应商（SNO）销售的部件（PNO）的信息。它是联接其他两个表的结点。 例 69-1. 供应商和部件数据库 SUPPLIER SNO | SNAME | CITY SELLS SNO | PNO -----+---------+-------- -----+----- 1 | Smith | London 1 | 1 2 | Jones | Paris 1 | 2 3 | Adams | Vienna 2 | 4 4 | Blake | Rome 3 | 1 3 | 3 4 | 2 PART PNO | PNAME | PRICE 4 | 3 -----+---------+--------- 4 | 4 1 | Screw | 10 2 | Nut | 8 3 | Bolt | 15 4 | Cam | 25 表 PART 和 SUPPLIER 可以看作不同的实体而 SELLS 可以看作是一种特定部件和特定供应商之间的关系。 如我们稍后将看到的，SQL 对表进行操作，就象我们刚才定义的那样，不过在这之前，我们将先学习关系模型的理论。 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 关系数据模型公式 躲在关系模型背后的数学理论是集合理论中的关系理论，它也是一列（数）域的笛卡儿乘积的子集。这样的集合理论中的关系给出其模型的名称（不要把它与来自实体关系模型的关系搞混了）。一个（数）域形式上只是一些数值的集合。例如，整数的集合是一个（数）域。同样长度为 20 的字串和实数的集合也是（数）域的例子。 （数）域 D1, D2, ... Dk 的笛卡儿乘积，标记 D1 × D2 × ... × Dk 就是所有 k元记录 v1, v2, ... vk 的集合，这里 v1 ∈ D1，v1 ∈ D1，... vk∈ Dk。 例如，假设我们有 k=2，D1={0,1} 和 D2={a,b,c} 那么 D1 × D2 是 {(0,a),(0,b),c),(1,c)}。 一个关系就是一个或者多个（数）域的笛卡儿乘积的任意子集：R &sube D1 × D2 × ... × Dk。 例如 {(0,a)} 是一个关系；它实际上是上面提到的 D1 × D2 的子集。 关系的成员叫做记录。某个笛卡儿乘积 D1 × D2 × ... × Dk 的每个关系都有 k 元，因而我们有一个 k元记录的集合。 一个关系可以看作一个表（正如我们已经说过的，还记得 供应商和部件数据库 里，每条记录被一行代表而每列对应记录里的一个元素）。赋予列（叫字段/属性）以名称导致一个关系设计的定义。 一个关系设计 R 是一个有限属性 A1，A2，... Ak的集合。对每个属性 Ai，1 <= i <= k，存在一个（数）域 Di，该属性的值是从这个（数）域里面取出来的。我们经常把关系设计写成 R(A1, A2, ... Ak)。 注意：一个关系设计只是某种模板，而一个关系是一个关系设计的实例。关系由记录组成（因而可以看作是一个表）；而关系设计可不能这样看。 （数）域与数据类型的对比 在上面的段落里我们经常谈到（数）域。记住一个（数）域（形式上）只是一个数值的集合（比如，整数或者实数的集合）。在数据库系统的用语里面，我们常用数据类型代替（数）域。当我们定义一个表的时候，我们必须决定要包括那些属性。另外我们还要决定属性数值将存储为哪种类型的数据。例如表 SUPPLIER 里的 SNAME 的值将存储为字符串，而 SNO 将被存储为整数。我们通过赋予每个属性一种数据类型来定义这些内容。 SNAME 的类型是 VARCHAR(20)（这是 SQL 用于长度 <= 20 的字符串的类型），SNO 的类型将是 INTEGER。在赋予属性数据类型的同时，我们同样也为它选择了一个（数）域。SNAME 的（数）域是所有长度 <= 20 的字符串的集合，SNO 的（数）域是所有整数的集合。 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 关系数据模型上的操作 在上一节（Relational Data Model Formalities）里，我们定义了关系模型的数学概念。现在我们知道了用关系数据模型如何存储数据，但是我们还不知道如何从这些表里面检索某些东西。例如，某人想知道销售部件 'Screw' 的所有的供应商的名称。因此定义了两种差别相当大的用于表示对关系的操作的符号： 关系代数是一种代数的符号，其中的查询是通过想关系附加特定的操作符来表示的。 关系微积分是一种逻辑符号，其中的查询是通过公式表示答案里的记录必须满足的某些逻辑约束来表示的。 关系代数 关系代数是 E. F. Codd 于 1972 首先提出的。它包括一个对表进行操作的集合： SELECT （σ）：从关系里面抽取出满足给定限制条件的记录。令 R 为一个表，包含一个属性 A。σA=a(R) = {t ∈ R &mid t(A) = a} 这里 t 表示 R 的一条记录而 t(A) 表示记录 t 的属性 A 的值。 PROJECT （π）：从一个关系里面抽取指明的属性（列）。令 R 为一个包含一个属性 X 的关系。πX(R) = {t(X) &mid t ∈R}，这里 t(X) 表示记录 t 里的属性 X 的值。 PRODUCT （×）：计算两个关系的笛卡儿乘积。令R 为含有 k1 个元的表而令 S 为含有 k2 个元的表。R × S 是所有含有 k1 + k2 个元记录的集合，其前面 k1 个元构成 R 里的一条记录而后面 k2 个元构成 S 里的一条记录。 UNION （∪）：计算两个表的集合理论上的联合。给出表 R 和 S （两者有相同元/列数）， R ∪ S 的联合就是所有在 R 里面有或 S 里面有或在两个表里面都有的记录的集合。 INTERSECT （∩）：计算两个表集合理论上的交集。给出表 R 和 S，R ∩ S 是同时在 R 和 S 里面的记录的集合。我们同样要求 R 和 S 拥有相同的元/列数。 DIFFERENCE （- 或 &setmn）：计算两个表的区别的集合。令 R 和 S 还是拥有相同元/列的表。R - S 是在 R 里面但是不在 S 里面的记录的集合。 JOIN （∏）：通过共同属性联接两个表。令 R 为一个有属性 A，B 和 C 的表，而令 S 为一个有属性 C，D 和 E 的表。两个表有一个共同的属性，属性 C。R ∏ S = πR.A,R.B,R.C,S.D,S.E（σR.C=S.C(R × S)）。我们在这里干了社呢们？首先我们计算笛卡儿乘积 R × S。然后我们选择那些公共属性 C 的数值相同(σR.C = S.C)的字段。这时我们拥有一个包含属性 C 两次的表，然后我们把重复的列去掉以后得出我们要的结果。 例子 69-2. 一个内部联接 让我们看看计算一个联合需要的各个步骤产生的表。给出下面两个表： R A | B | C S C | D | E ---+---+--- ---+---+--- 1 | 2 | 3 3 | a | b 4 | 5 | 6 6 | c | d 7 | 8 | 9 首先我们先计算笛卡儿乘积 R × S 并得到： R x S A | B | R.C | S.C | D | E ---+---+-----+-----+---+--- 1 | 2 | 3 | 3 | a | b 1 | 2 | 3 | 6 | c | d 4 | 5 | 6 | 3 | a | b 4 | 5 | 6 | 6 | c | d 7 | 8 | 9 | 3 | a | b 7 | 8 | 9 | 6 | c | d 在选择 σR.C=S.C(R × S) 后我们得到： A | B | R.C | S.C | D | E ---+---+-----+-----+---+--- 1 | 2 | 3 | 3 | a | b 4 | 5 | 6 | 6 | c | d 删除重复的列 S.C 我们用下面的操作：πR.A,S.E(σR.C=S.C(R × S)) 将其选出，得到： A | B | C | D | E ---+---+---+---+--- 1 | 2 | 3 | a | b 4 | 5 | 6 | c | d DIVIDE （÷）：令 R 是一个有属性 A，B，C 和 D 的表，并且令 S 是一个有着属性 C 和 D 的表。然后我们把除定义为：R ÷ S = {t &mid &forall ts ∈ S &exist tr ∈ R 令 tr(A,B)=t∧tr(C,D)=ts} 这里 tr(x,y) 代表表 R 里只包含元素 x 和 y 的记录。注意记录 t 只包含关系 R 里的元素 A 和 B。 给出下面的表 R A | B | C | D S C | D ---+---+---+--- ---+--- a | b | c | d c | d a | b | e | f e | f b | c | e | f e | d | c | d e | d | e | f a | b | d | e R ÷ S 生成 A | B ---+--- a | b e | d 关于关系代数的更详细的描述和定义，请参考[Ullman, 1988] 或 [Date, 1994]。 例 69-3. 使用关系代数的一个例子 回忆一下我们阐明的所有可以用语从数据库里检索数据的关系操作符。现在我们可以回到前一章的例子里（关系数据模型里的操作），那时我们需要知道销售部件 Screw 的所有供应商的名称。现在我们可以用下面的关系代数的操作回答这个问题了： πSUPPLIER.SNAME(σPART.PNAME='Screw'(SUPPLIER × SELLS × PART)) 我们称这样的操作是一次查询。如果我们对我们的例子表（The Suppliers and Parts Database）进行上述查询计算，我们将获得下面结果： SNAME ------- Smith Adams 关系微积分 关系微积分基于第一顺序逻辑（first order logic）。关系微积分有两个变种： （数）域关系微积分（Domain Relational Calculus）（DRC），其变量代表记录的元素（属性）。 记录关系微积分（Tuple Relational Calculus）（TRC），其变量代表记录。 我们只打算讨论记录关系微积分，因为它是大多数关系语言背后的数学基础。有关DRC （当然还有 TRC）的详细讨论，参阅 Date, 1994 或 Ullman, 1988。 记录关系微积分 在 TRC 里使用的查询是这样的形式：x(A) &mid F(x)，这里 x 是一个记录变量， A 是属性的集合而 F 是一个公式。生成的关系包含所有满足 F(t) 的记录 t(A)。 如果我们想用 TRC 回答例子 一个使用关系代数的查询 里的问题，我们可以写出下面查询： {x(SNAME) &mid x ∈ SUPPLIER ∧ /nonumber &exist y ∈ SELLS &exist z ∈ PART (y(SNO)=x(SNO) ∧ /nonumber z(PNO)=y(PNO) ∧ /nonumber z(PNAME)='Screw')} /nonumber 对 供应商和部件数据库 计算这个查询同样得出与 一个使用关系代数的查询相同的结果。 关系代数与关系微积分的比较 关系代数和关系微积分有着相同的表达能力；也就是说，所有可以用关系代数表达的查询都可以使用关系微积分来表达，反之亦然。这个结论首先是 E. F. Codd 在 1972 年证明的。这个证明是基于一个算法（“Codd 的归纳算法”），利用这个算法，一个任意的关系微积分的表达式可以归纳为一个语义上相等的关系代数的表达式。关于这些更详细的讨论，请参考 Date, 1994 和 Ullman, 1988。 有时候我们说基于关系微积分的语言比基于关系代数的语言是更 "高级的" 或者 "更描述性的" 语言，因为代数（部分上）说明了操作的过程而微积分把这些过程留给编译器或者解释器以决定计算的最有效顺序。 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL 语言 正如大多数现代的关系语言一样，SQL 是基于记录关系微积分的。结果是每个可以用记录关系微积分表示的查询（相等地，或者是关系代数），同样也可以用SQL 表示。不过，SQL 还有一些超出关系代数或者微积分的能力。下面是一个SQL 提供的并非关系代数或者关系微积分的内容的一个列表： 插入，删除或者更改数据的命令。 算术能力：在 SQL 里，我们可以和比较功能一样进行算术运算，例如 A < B + 3。要注意 + 或者其他算术操作符从未在关系代数或者关系微积分里面出现过。 分配和打印命令：我们可以打印一条查询构造的关系以及给一个被计算的关系分配关系名。 聚集函数：象 average，sum，max，等操作符可以应用于一个关系的列以获取单一的量。 Select（选择） SQL 里面最常用的命令是 SELECT 语句，用于检索数据。语法是： SELECT [ALL|DISTINCT] { * | expr_1 [AS c_alias_1] [, ... [, expr_k [AS c_alias_k]]]} FROM table_name_1 [t_alias_1] [, ... [, table_name_n [t_alias_n]]] [WHERE condition] [GROUP BY name_of_attr_i [,... [, name_of_attr_j]] [HAVING condition]] [{UNION [ALL] | INTERSECT | EXCEPT} SELECT ...] [ORDER BY name_of_attr_i [ASC|DESC] [, name_of_attr_j [ASC|DESC]]]]; 现在我们将通过不同的例子演示 SELECT 语句复杂的语法。用于这些例子的表在 供应商和部件数据库 里定义。 简单的 Selects 这里是一些使用 SELECT 语句的简单例子： 例 69-4. 带有条件的简单查询 要从表 PART 里面把字段 PRICE 大于 10 的所有记录找出来，我们写出下面查询： SELECT * FROM PART WHERE PRICE > 10; 然后得到表： PNO | PNAME | PRICE -----+---------+-------- 3 | Bolt | 15 4 | Cam | 25 在 SELECT语句里使用 "*" 将检索出表中的所有属性。如果我们只希望从表 PART 中检索出属性 PNAME 和 PRICE，我们使用下面的语句： SELECT PNAME, PRICE FROM PART WHERE PRICE > 10; 这回我们的结果是： PNAME | PRICE --------+-------- Bolt | 15 Cam | 25 请注意 SQL 的 SELECT 语句对应关系代数里面的 "projection" （映射），而不是 "selection"（选择）（参阅 关系代数 获取详细信息）。 WHERE 子句里的条件也可以用关键字 OR，AND，和 NOT 逻辑的连接起来： SELECT PNAME, PRICE FROM PART WHERE PNAME = 'Bolt' AND (PRICE = 0 OR PRICE < 15); 这样将生成下面的结果： PNAME | PRICE --------+-------- Bolt | 15 目标列表和 WHERE 子句里可以使用算术操作。例如，如果我们想知道如果我们买两个部件的话要多少钱，我们可以用下面的查询： SELECT PNAME, PRICE * 2 AS DOUBLE FROM PART WHERE PRICE * 2 < 50; 这样我们得到： PNAME | DOUBLE --------+--------- Screw | 20 Nut | 16 Bolt | 30 请注意在关键字 AS 后面的 DOUBLE 是第二个列的新名字。这个技巧可以用于目标列表里的每个元素，给它们赋予一个在结果列中显示的新的标题。这个新的标题通常称为别名。这个别名不能在该查询的其他地方使用。 Joins（联接） 下面的例子显示了 SQL 里是如何实现联接的。 要在共同的属性上联合三个表 SUPPLIER，PART 和 SELLS，我们通常使用下面的语句： SELECT S.SNAME, P.PNAME FROM SUPPLIER S, PART P, SELLS SE WHERE S.SNO = SE.SNO AND P.PNO = SE.PNO; 而我们得到的结果是： SNAME | PNAME -------+------- Smith | Screw Smith | Nut Jones | Cam Adams | Screw Adams | Bolt Blake | Nut Blake | Bolt Blake | Cam 在 FROM 子句里，我们为每个关系使用了一个别名，因为在这些关系间有着公共的命名属性（SNO 和 PNO）。现在我们可以区分不同表的公共命名属性，只需要简单的用每个关系的别名加上个点做前缀就行了。联合是用与 一个内部联接 里显示的同样的方法计算的。首先算出笛卡儿乘积 SUPPLIER × PART × SELLS。然后选出那些满足 WHERE 子句里给出的条件的记录（也就是说，公共命名属性的值必须相等）。最后我们映射出除 S.SNAME 和 P.PNAME 外的所有属性。 聚集操作符 SQL 提供以一个属性的名称为参数的聚集操作符（如，AVG，COUNT，SUM，MIN，MAX）。聚集操作符的结果是对整个表中的所有声明的属性（列）的值进行计算的结果。如果在一个查询里面声明了组，那么这种（聚集）计算将只发生在一个组的值上（见下节）。 例 69-5. 聚集 如果我们想知道表 PART 里面所有部件的平均价格，我们可以使用下面查询： SELECT AVG(PRICE) AS AVG_PRICE FROM PART; 结果是： AVG_PRICE ----------- 14.5 如果我们想知道在表 PART 里面存储了多少部件，我们可以使用语句： SELECT COUNT(PNO) FROM PART; 得到： COUNT ------- 4 分组聚集 SQL 允许我们把一个表里面的记录分成组。然后上面描述的聚集操作符可以应用与这些组上（也就是说，聚集操作符的值不再是对所有声明的列的值进行操作，而是对一个组的所有值进行操作。这样聚集函数是为每个组独立地进行计算的。） 对记录的分组是通过关键字 GROUP BY 实现的， GROUP BY 后面跟着一个定义组的构成的属性列表。如果我们使用语句 GROUP BY A1, &tdot, Ak，我们把关系分成了组，这样如果两条记录在（也只是在）所有属性 A1, Ak 上达成一致，它们是同一组的。 例 69-6. 聚集 如果我们想知道每个供应商销售多少个部件，我们可以这样写查询： SELECT S.SNO, S.SNAME, COUNT(SE.PNO) FROM SUPPLIER S, SELLS SE WHERE S.SNO = SE.SNO GROUP BY S.SNO, S.SNAME; 得到： SNO | SNAME | COUNT -----+-------+------- 1 | Smith | 2 2 | Jones | 1 3 | Adams | 2 4 | Blake | 3 现在我们看一看发生了什么事情。首先生成表 SUPPLIER 和 SELLS 的联接： S.SNO | S.SNAME | SE.PNO -------+---------+-------- 1 | Smith | 1 1 | Smith | 2 2 | Jones | 4 3 | Adams | 1 3 | Adams | 3 4 | Blake | 2 4 | Blake | 3 4 | Blake | 4 然后我们把那些属性 S.SNO 和 S.SNAME 相同的记录放在组中： S.SNO | S.SNAME | SE.PNO -------+---------+-------- 1 | Smith | 1 | 2 -------------------------- 2 | Jones | 4 -------------------------- 3 | Adams | 1 | 3 -------------------------- 4 | Blake | 2 | 3 | 4 在我们的例子里，我们有四个组并且现在我们可以对每个组应用聚集操作符 COUNT，生成上面给出的查询的最终结果。 请注意如果要让一个使用 GROUP BY 和聚集操作符的查询的结果有意义，那么用于分组的属性也必须出现在目标列表中。所有没有在 GROUP BY 子句里面出现的属性都只能通过使用聚集函数来选择。另一方面，你不能对出现在 GROUP BY 子句里面的属性使用聚集函数。 Having（具有） HAVING 子句工作起来非常象 WHERE 子句，只用于对那些满足 HAVING 子句里面给出的条件的组进行计算。在 HAVING 子句里面允许使用的表达式必须包括聚集函数。每个表达式只能使用属于 WHERE 子句的简单属性。另一方面，每个聚集函数里面的表达式必须放到 HAVING 子句里面。 例 69-7. Having 如果我们想知道那些销售超过一个部件的供应商，使用下面查询： SELECT S.SNO, S.SNAME HAVING COUNT(SE.PNO) > 1; 得到： SNO | SNAME | COUNT -----+-------+------- 1 | Smith | 2 3 | Adams | 2 4 | Blake | 3 子查询 在 WHERE 和 HAVING 子句里，允许在任何要产生数值的地方使用子查询（子选择）。这种情况下，该值来自首先对子查询的计算。子查询的使用扩展了SQL 的表达能力。 例 69-8. 子查询 如果我们想知道所有比名为 'Screw' 的部件贵的部件，我们可以用下面的查询： SELECT * FROM PART WHERE PRICE > (SELECT PRICE FROM PART WHERE PNAME='Screw'); 结果是： PNO | PNAME | PRICE -----+---------+-------- 3 | Bolt | 15 4 | Cam | 25 当我们检查上面的查询时会发现出现了两次 SELECT 关键字。第一个在查询的开头 - 我们将称之为外层 SELECT - 而另一个在 WHERE 子句里面，成为一个嵌入的查询 - 我们将称之为内层 SELECT。对外层 SELECT 的每条记录都必须先计算内层 SELECT。在完成所有计算之后，我们得知名为 'Screw' 部件的记录的价格，然后我们就可以检查那些价格更贵的记录了。 如果我们想知道那些不销售任何部件的供应商（比如说，我们想把这些供应商从数据库中删除），我们用： SELECT * FROM SUPPLIER S WHERE NOT EXISTS (SELECT * FROM SELLS SE WHERE SE.SNO = S.SNO); 在我们的例子里，结果列将是空的，因为每个供应商至少销售一个部件。请注意我们在 WHERE 子句的内层 SELECT 里使用了来自外层 SELECT 的 S.SNO。正如前面所说的，子查询为每个外层查询计算一次，也就是说，S.SNO 的值总是从外层 SELECT 的实际记录中取得的。 Union, Intersect, Except（联合，相交，相异） 这些操作符分别计算两个子查询产生的记录的联合，相交和集合理论里的相异。 例 69-9. Union, Except 下面的例子是 UNION 的例子： SELECT S.SNO, S.CITY FROM SUPPLIER S WHERE S.SNAME = 'Jones' UNION SELECT S.SNO, S.CITY FROM SUPPLIER S WHERE S.SNAME = 'Adams'; 产生结果： SNO | SNAME | CITY -----+-------+-------- 2 | Jones | Paris 3 | Adams | Vienna 下面是相交（ INTERSECT）的例子： SELECT S.SNO, S.CITY FROM SUPPLIER S WHERE S.SNO > 1 INTERSECT SELECT S.SNO, S.CITY FROM SUPPLIER S WHERE S.SNO > 2; 产生结果： SNO | SNAME | CITY -----+-------+-------- 2 | Jones | Paris The only tuple returned by both parts of the query is the one having $SNO=2$.（两个查询都会返回的记录是那条 $SNO=2$ 的） 最后是一个 EXCEPT 的例子： SELECT S.SNO, S.CITY FROM SUPPLIER S WHERE S.SNO > 1 EXCEPT SELECT S.SNO, S.CITY FROM SUPPLIER S WHERE S.SNO > 3; 结果是： SNO | SNAME | CITY -----+-------+-------- 2 | Jones | Paris 3 | Adams | Vienna 数据定义 在 SQL 语言里包含一套用于数据定义的命令。 Create Table 数据定义的最基本的命令是创建一个新关系（新表）的命令。CREATE TABLE 命令的语法如下： CREATE TABLE table_name (name_of_attr_1 type_of_attr_1 [, name_of_attr_2 type_of_attr_2 [, ...]]); 例 69-10. 创建表 要创建 供应商和部件数据库 里的表，要使用下面的 SQL 语句： CREATE TABLE SUPPLIER (SNO INTEGER, SNAME VARCHAR(20), CITY VARCHAR(20)); CREATE TABLE PART (PNO INTEGER, PNAME VARCHAR(20), PRICE DECIMAL(4 , 2)); CREATE TABLE SELLS (SNO INTEGER, PNO INTEGER); SQL 里的数据类型 下面是 SQL 一些数据类型的列表： INTEGER: 有符号全长二进制整数（31位精度）。 SMALLINT: 有符号半长二进制整数（15位精度）。 DECIMAL (p[,q]): 有符号封装的十进制小数，共 p 位数，并假设有 q 位数在小数点右边。（15 ≥ p ≥ qq ≥ 0）。如果省略 q，则认为是 0。 FLOAT: 有符号双字浮点数。 CHAR(n): 长度为 n 的定长字符串。 VARCHAR(n): 最大长度为 n 的变长字符串。 Create Index 索引用于加速对关系的访问。如果关系 R 有一个建立在属性 A 上的索引，那么我们检索满足 t(A) = a 的所有记录 t 用的时间和这样的记录数 t 成比例，而不是与 R 的大小成比例。 在 SQL 里创建一个索引，使用 CREATE INDEX 命令。语法是： CREATE INDEX index_name ON table_name ( name_of_attribute ); 例 69-11. 创建索引 要在关系 SUPPLIER 的属性 SNAME 上创建一个名为 I 的索引，我们使用下面语法： CREATE INDEX I ON SUPPLIER (SNAME); 所创建的索引是自动维护的，也就是说，当向关系 SUPPLIER 插入新的记录时，索引 I 相应做调节。请注意有索引存在时，用户能感觉到的唯一的变化就是速度的提升。 Create View（创建视图） 一个视图可以看作一个虚拟表，也就是说，数据库里的一个物理上不存在的，但是用户看起来却存在的表。与之相比，当我们谈到一个基本表时，则是在物理存储中的确物理地存放着表中每一行的内容。 视图没有它们自身的，物理上分开的，可区分的存储区。实际上，系统把视图的定义（也就是说，为物化试图应如何访问物理上存储在基本表中内容的规则）存放在系统表里的某个地方（参阅 系统表）。关于实现视图的不同技巧，请参考 SIM98。 在 SQL 里，使用 CREATE VIEW 命令定义视图。语法是： CREATE VIEW view_name AS select_stmt 这里 select_stmt 是一个与定义在 Select 里一样的有效选择语句。请注意在视图创建时并不执行 select_stmt。它只是存储在系统表里，当对视图进行查询时将执行。 假设给出下面的视图定义（我们再次使用来自 供应商和部件数据库 里的表）： CREATE VIEW London_Suppliers AS SELECT S.SNAME, P.PNAME FROM SUPPLIER S, SELLS SE WHERE S.SNO = SE.SNO AND P.PNO = SE.PNO AND S.CITY = 'London'; 现在我们可以把这个虚拟的关系 London_Suppliers 当作是另外一个基本表： SELECT * FROM London_Suppliers WHERE P.PNAME = 'Screw'; 将返回下面的表： SNAME | PNAME -------+------- Smith | Screw 要计算这个结果，数据库系统必须先对基本表 SUPPLIER，SELLS 和 PART 先进行一次隐藏的访问。它是通过对这些基本表运行该视图定义里面给出的查询实现隐藏访问的。然后，余下的附加条件（在查询里给出的对视图的条件）就可以应用上来，最后获取结果表。 Drop Table, Drop Index, Drop View 要删除表（包括该表存储的所有记录），使用 DROP TABLE 命令： DROP TABLE table_name; 要删除 SUPPLIER 表，使用下面语句： DROP TABLE SUPPLIER; DROP INDEX 命令用于删除一个索引： DROP INDEX index_name; 最后用 DROP VIEW 删除一个给出的视图： DROP VIEW view_name; 数据操作 Insert Into（插入） 一旦表创建完成（参阅 Create Table），就可以用命令 INSERT INTO 向里面填充记录。语法是： INSERT INTO table_name (name_of_attr_1 [, name_of_attr_2 [,...]]) VALUES (val_attr_1 [, val_attr_2 [, ...]]); 要向关系 SUPPLIER 中插入第一条记录（来自 供应商和部件数据库），我们使用下面语句： INSERT INTO SUPPLIER (SNO, SNAME, CITY) VALUES (1, 'Smith', 'London'); 要向关系 SELLS 插入第一条记录，我们用： INSERT INTO SELLS (SNO, PNO) VALUES (1, 1); Update（更新） 要修改记录中的一个或者多个属性的值，使用 UPDATE 命令。语法是： UPDATE table_name SET name_of_attr_1 = value_1 [, name_of_attr_k = value_k]] WHERE condition; 要该表关系 PART 中部件 'Screw' 的属性 PRICE 的值，我们使用： UPDATE PART SET PRICE = 15 WHERE PNAME = 'Screw'; 记录名为 'Screw' 的属性 PRICE 的新值现在是 15。 Delete 要从一个表中删除一条记录，使用 DELETE FROM 命令。语法是： DELETE FROM table_name WHERE condition; 要删除 SUPPLIER 表中名为 'Smith' 的供应商，使用下面的语句： DELETE FROM SUPPLIER WHERE SNAME = 'Smith'; 系统表 在所有 SQL 数据库系统里面，系统表都被用于跟踪表，视图索引等在数据库中定义的东西。这些系统表可以象普通关系一样检索。例如，有一个系统表用于视图的定义。这个表存储来自视图定义的查询。每当对视图进行查询时，系统在处理用户查询之前首先从该表中取出视图定义查询并且执行该查询（参阅 Simkovics, 1998 获取更详细的描述）。关于系统表的更多信息，请参考 Date, 1994 。 嵌入的 SQL 在这一节里，我们将勾勒如何把 SQL 嵌入到一个宿主语言里（例如，C）。我们需要从一种宿主语言里使用SQL 的原因主要有两个： 有一些查询不可能用纯 SQL 表示（比如，递归查询）。要执行这样的查询，我们需要一种比SQL 更有表达能力的宿主语言。 我们只是希望能从用宿主语言写的应用里面访问数据库（例如，一个图形用户界面的门票预定系统可能用 C 写，而余票状况存储在一个可以用嵌入 SQL 访问的数据库里面）。 一个在宿主语言里面使用嵌入 SQL 的程序包含宿主语言的语句和嵌入 SQL（ESQL）的语句。每条ESQL 语句以关键字 EXEC SQL 开始。ESQL 语句被一个预编译器（它通常向源文件中插入库过程的调用以执行各种SQL 命令）转换成宿主语言。 当我们观察例子 Select 时，我们认识到一个查询的结果大多是记录的集合。大多数宿主语言不是为集合而设计的，因此我们需要一个机制用来访问一条 SELECT 语句返回结果集中的每一条记录。这个机制可以通过定义一个游标来实现。之后我们就可以使用 FETCH 命令检索一条记录并把游标定义到下一条记录。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |