SQL必知必会阅读笔记，作者（Ben Forta）学习贴4

14.组合查询

本课讲述如何利用 UNION 操作符将多条 SELECT 语句组合成一个结 果集。

14.1组合查询

多数 SQL 查询只包含从一个或多个表中返回数据的单条 SELECT 语句。 但是，SQL 也允许执行多个查询（多条 SELECT 语句），并将结果作为一 个查询结果集返回。这些组合查询通常称为并（union）或复合查询 （compound query）

主要有两种情况需要使用组合查询：

 在一个查询中从不同的表返回结构数据；

 对一个表执行多个查询，按一个查询返回数据。

提示：组合查询和多个 WHERE 条件 多数情况下，组合相同表的两个查询所完成的工作与具有多个 WHERE 子句条件的一个查询所完成的工作相同。换句话说，任何具有多个 WHERE 子句的 SELECT 语句都可以作为一个组合查询，在下面可以看 到这一点。

14.2 创建组合查询

可用 UNION 操作符来组合数条 SQL 查询。利用 UNION，可给出多条 SELECT 语句，将它们的结果组合成一个结果集。

14.2.1 使用UNION

使用 UNION 很简单，所要做的只是给出每条 SELECT 语句，在各条语句 之间放上关键字 UNION。

举个例子，假如需要 Illinois、Indiana 和 Michigan 等美国几个州的所有顾 客的报表，还想包括不管位于哪个州的所有的 Fun4All。当然可以利用 WHERE 子句来完成此工作，不过这次我们使用 UNION。如上所述，创建 UNION 涉及编写多条 SELECT 语句。首先来看单条语句：

SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI');

SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name = 'Fun4All';

第一条 SELECT 把 Illinois、Indiana、Michigan 等州的缩写传递给 IN 子句， 检索出这些州的所有行。第二条 SELECT 利用简单的相等测试找出所有 Fun4All。你会发现有一条记录出现在两次结果里，因为它满足两次的 条件。

组合这两条语句，可以如下进行：

SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') UNION SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name = 'Fun4All';

在这个简单的例子中，使用 UNION 可能比使用 WHERE 子句更为复杂。但 对于较复杂的过滤条件，或者从多个表（而不是一个表）中检索数据的 情形，使用 UNION 可能会使处理更简单。

提示：UNION 的限制 使用 UNION 组合 SELECT 语句的数目，SQL 没有标准限制。但是，最 好是参考一下具体的 DBMS 文档，了解它是否对 UNION 能组合的最大 语句数目有限制。

注意：性能问题 多数好的 DBMS 使用内部查询优化程序，在处理各条 SELECT 语句前 组合它们。理论上讲，这意味着从性能上看使用多条 WHERE 子句条件 还是 UNION 应该没有实际的差别。不过我说的是理论上，实践中多数 查询优化程序并不能达到理想状态，所以最好测试一下这两种方法， 看哪种工作得更好。

14.2.2 UNION规则

可以看到，UNION 非常容易使用，但在进行组合时需要注意几条规则。

 UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔（因此，如果组合四条 SELECT 语句，将要使用三个 UNION 关键字）。

 UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过， 各个列不需要以相同的次序列出）

 UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过， 各个列不需要以相同的次序列出）

说明：UNION 的列名 如果结合 UNION 使用的 SELECT 语句遇到不同的列名，那么会返回什 么名字呢？比如说，如果一条语句是 SELECT prod_name，而另一条 语句是 SELECT productname，那么查询结果返回的是什么名字呢？ 答案是它会返回第一个名字，举的这个例子就会返回 prod_name，而 不管第二个不同的名字。这也意味着你可以对第一个名字使用别名， 因而返回一个你想要的名字。 这种行为带来一个有意思的副作用。由于只使用第一个名字，那么想 要排序也只能用这个名字。拿我们的例子来说，可以用 ORDER BY prod_name 对结果排序，如果写成 ORDER BY productname 就会出错， 因为查询结果里没有叫作 productname 的列。

如果遵守了这些基本规则或限制，则可以将 UNION 用于任何数据检索 操作。

14.2.3 包含或取消重复的行

UNION 从查询结果集中自动去除了重复的行；换句话说，它的行为与一 条 SELECT 语句中使用多个 WHERE 子句条件一样。因为 Indiana 州有一个 Fun4All 单位，所以两条 SELECT 语句都返回该行。使用 UNION 时，重复 的行会被自动取消。

这是 UNION 的默认行为，如果愿意也可以改变它。事实上，如果想返回 所有的匹配行，可使用 UNION ALL 而不是 UNION。

提示：UNION 与 WHERE 这一课一开始我们说过，UNION 几乎总是完成与多个 WHERE 条件相同 的工作。UNION ALL 为 UNION 的一种形式，它完成 WHERE 子句完成 不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行）， 就必须使用 UNION ALL，而不是 WHERE。

14.2.4 对组合查询结果排序

SELECT 语句的输出用 ORDER BY 子句排序。在用 UNION 组合查询时，只 能使用一条 ORDER BY 子句，它必须位于最后一条 SELECT 语句之后。对 于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一 部分的情况，因此不允许使用多条 ORDER BY 子句。

SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') UNION SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name = 'Fun4All' ORDER BY cust_name, cust_contact;

这条UNION在最后一条SELECT语句后使用了ORDER BY子句。虽然ORDER BY 子句似乎只是最后一条 SELECT 语句的组成部分，但实际上 DBMS 将 用它来排序所有 SELECT 语句返回的所有结果、

说明：其他类型的 UNION 某些 DBMS 还支持另外两种 UNION：EXCEPT（有时称为 MINUS）可用 来检索只在第一个表中存在而在第二个表中不存在的行；而 INTERSECT 可用来检索两个表中都存在的行。实际上，这些 UNION 很少使用，因 为相同的结果可利用联结得到。

提示：操作多个表 为了简单，本课中的例子都是使用 UNION 来组合针对同一表的多个查 询。实际上，UNION 在需要组合多个表的数据时也很有用，即使是有 不匹配列名的表，在这种情况下，可以将 UNION 与别名组合，检索 一个结果集。

14.3挑战题

1. 编写 SQL 语句，将两个 SELECT 语句结合起来，以便从 OrderItems 表中检索产品 ID（prod_id）和 quantity。其中，一个 SELECT 语 句过滤数量为 100 的行，另一个 SELECT 语句过滤 ID 以 BNBG 开头的 产品。按产品 ID 对结果进行排序。

 2. 重写刚刚创建的 SQL 语句，仅使用单个 SELECT 语句。

 3. 我知道这有点荒谬，但这节课中的一个注释提到过。编写 SQL 语句， 组合 Products 表中的产品名称（prod_name）和 Customers 表中的 顾客名称（cust_name）并返回，然后按产品名称对结果进行排序。

 4. 下面的 SQL 语句有问题吗？（尝试在不运行的情况下指出。）

SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state = 'MI' ORDER BY cust_name; UNION SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state = 'IL'ORDER BY cust_name;

 答;order by 只出现一次，在最后面。; 不能出现在第一个子句后，会终结语句。

15.插入数据

这一课介绍如何利用 SQL 的 INSERT 语句将数据插入表中。

15.1数据插入

毫无疑问，SELECT 是最常用的 SQL 语句了，这就是前 14 课都在讲它的 原因。但是，还有其他 3 个常用的 SQL 语句需要学习。第一个就是 INSERT （下一课介绍另外两个）。

顾名思义，INSERT 用来将行插入（或添加）到数据库表。插入有几种 方式：

 插入完整的行；

 插入行的一部分；

 插入某些查询的结果。

提示：插入及系统安全 使用 INSERT 语句可能需要客户端/服务器 DBMS 中的特定安全权限。 在你试图使用 INSERT 前，应该保证自己有足够的安全权限。

15.1.1插入完整的行

把数据插入表中的最简单方法是使用基本的 INSERT 语法，它要求指定 表名和插入到新行中的值。下面举一个例子：

INSERT INTO Customers VALUES(1000000006, 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);

这个例子将一个新顾客插入到 Customers 表中。存储到表中每一列的数 据在 VALUES 子句中给出，必须给每一列提供一个值。如果某列没有值， 如上面的 cust_contact 和 cust_email 列，则应该使用 NULL 值（假定 表允许对该列指定空值）。各列必须以它们在表定义中出现的次序填充。

提示：INTO 关键字 在某些 SQL 实现中，跟在 INSERT 之后的 INTO 关键字是可选的。但 是，即使不一定需要，最好还是提供这个关键字，这样做将保证 SQL 代码在 DBMS 之间可移植。

虽然这种语法很简单，但并不安全，应该尽量避免使用。上面的 SQL 语 句高度依赖于表中列的定义次序，还依赖于其容易获得的次序信息。即 使可以得到这种次序信息，也不能保证各列在下一次表结构变动后保持完全相同的次序。因此，编写依赖于特定列次序的 SQL 语句是很不安全 的，这样做迟早会出问题。

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES(1000000006, 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);

这个例子与前一个 INSERT 语句的工作完全相同，但在表名后的括号里 明确给出了列名。在插入行时，DBMS 将用 VALUES 列表中的相应值填 入列表中的对应项。VALUES 中的第一个值对应于第一个指定列名，第二 个值对应于第二个列名，如此等等。

因为提供了列名，VALUES 必须以其指定的次序匹配指定的列名，不一定 按各列出现在表中的实际次序。其优点是，即使表的结构改变，这条 INSERT 语句仍然能正确工作。

说明：不能插入同一条记录两次 如果你尝试了这个例子的两种方法，会发现第二次生成了一条出错消 息，说 ID 为 1000000006 的顾客已经存在。在第一课我们说过，主 键的值必须有唯一性，而 cust_id 是主键，DBMS 不允许插入相同 cust_id 值的新行。下一个例子也是同样的道理。要想再尝试另一种 插入方法，需要首先删除掉已经插入的记录（下一课会讲）。要么就别 尝试新方法了，反正记录已经插入好，你可以继续往下学习。

提示：总是使用列的列表 不要使用没有明确给出列的 INSERT 语句。给出列能使 SQL 代码继续 发挥作用，即使表结构发生了变化。

注意：小心使用 VALUES 不管使用哪种 INSERT 语法，VALUES 的数目都必须正确。如果不提供列 名，则必须给每个表列提供一个值；如果提供列名，则必须给列出的每 个列一个值。否则，就会产生一条错误消息，相应的行不能成功插入。

15.1.2插入部分行

正如所述，使用 INSERT 的推荐方法是明确给出表的列名。使用这种语 法，还可以省略列，这表示可以只给某些列提供值，给其他列不提供值。

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES(1000000006, 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA');

注意：省略列 如果表的定义允许，则可以在 INSERT 操作中省略某些列。省略的列 必须满足以下某个条件。

 该列定义为允许 NULL 值（无值或空值）。

 在表定义中给出默认值。这表示如果不给出值，将使用默认值。

注意：省略所需的值 如果表中不允许有 NULL 值或者默认值，这时却省略了表中的值， DBMS 就会产生错误消息，相应的行不能成功插入。

15.1.3 插入检索出的数据

INSERT 一般用来给表插入具有指定列值的行。INSERT 还存在另一种形 式，可以利用它将 SELECT 语句的结果插入表中，这就是所谓的 INSERT SELECT。顾名思义，它是由一条 INSERT 语句和一条 SELECT 语句组成的。

假如想把另一表中的顾客列合并到 Customers 表中，不需要每次读取一 行再将它用 INSERT 插入，可以如下进行：

INSERT INTO Customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) SELECT cust_id, cust_contact, cust_email,cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country FROM CustNew;

说明：新例子的说明 这个例子从一个名为 CustNew 的表中读出数据并插入到 Customers 表。为了试验这个例子，应该首先创建和填充 CustNew 表。CustNew 表的结构与附录 A 中描述的 Customers 表相同。在填充 CustNew 时， 不应该使用已经在 Customers 中用过的 cust_id 值（如果主键值重 复，后续的 INSERT 操作将会失败）

这个例子使用 INSERT SELECT 从 CustNew 中将所有数据导入 Customers。SELECT 语句从 CustNew 检索出要插入的值，而不是列出 它们。SELECT 中列出的每一列对应于 Customers 表名后所跟的每一列。 这条语句将插入多少行呢？这依赖于 CustNew 表有多少行。如果这个表 为空，则没有行被插入（也不产生错误，因为操作仍然是合法的）。如果 这个表确实有数据，则所有数据将被插入到 Customers。

提示：INSERT SELECT 中的列名 为简单起见，这个例子在INSERT和SELECT语句中使用了相同的列名。 但是，不一定要求列名匹配。事实上，DBMS 一点儿也不关心 SELECT 返回的列名。它使用的是列的位置，因此 SELECT 中的第一列（不管 其列名）将用来填充表列中指定的第一列，第二列将用来填充表列中 指定的第二列，如此等等。

INSERT SELECT 中 SELECT 语句可以包含 WHERE 子句，以过滤插入的数据。

提示：插入多行 INSERT 通常只插入一行。要插入多行，必须执行多个 INSERT 语句。 INSERT SELECT是个例外，它可以用一条INSERT插入多行，不管SELECT 语句返回多少行，都将被 INSERT 插入。

15.2从一个表复制到另一个表

有一种数据插入不使用 INSERT 语句。要将一个表的内容复制到一个全 新的表（运行中创建的表），可以使用 CREATE SELECT 语句（或者在 SQL Server 里也可用 SELECT INTO 语句）

说明：DB2 不支持 DB2 不支持这里描述的 CREATE SELECT

与 INSERT SELECT 将数据添加到一个已经存在的表不同，CREATE SELECT 将数据复制到一个新表（有的 DBMS 可以覆盖已经存在的表， 这依赖于所使用的具体 DBMS）。

CREATE TABLE CustCopy AS SELECT * FROM Customers;

这条 SELECT 语句创建一个名为 CustCopy 的新表，并把 Customers 表 的整个内容复制到新表中。因为这里使用的是 SELECT *，所以将在 CustCopy 表中创建（并填充）与 Customers 表的每一列相同的列。要 想只复制部分的列，可以明确给出列名，而不是使用*通配符。

15.4挑战题

1. 使用 INSERT 和指定的列，将你自己添加到 Customers 表中。明确列 出要添加哪几列，且仅需列出你需要的列。

 2. 备份 Orders 表和 OrderItems 表。

 16.更新和删除数据

这一课介绍如何利用 UPDATE 和 DELETE 语句进一步操作表数据。

16.1更新数据

更新（修改）表中的数据，可以使用 UPDATE 语句。有两种使用 UPDATE 的方式：

 更新表中的特定行；

 更新表中的所有行。

注意：不要省略 WHERE 子句 在使用 UPDATE 时一定要细心。因为稍不注意，就会更新表中的所有 行。使用这条语句前，请完整地阅读本节。

提示：UPDATE 与安全 在客户端/服务器的 DBMS 中，使用 UPDATE 语句可能需要特殊的安全 权限。在你使用 UPDATE 前，应该保证自己有足够的安全权限。

使用 UPDATE 语句非常容易，甚至可以说太容易了。基本的 UPDATE 语句 由三部分组成，分别是：

 要更新的表；

 列名和它们的新值；

 确定要更新哪些行的过滤条件。

举一个简单例子。客户 1000000005 现在有了电子邮件地址，因此他的 记录需要更新，语句如下：

UPDATE Customers SET cust_email = 'kim@thetoystore.com' WHERE cust_id = 1000000005;

UPDATE 语句总是以要更新的表名开始。在这个例子中，要更新的表名为 Customers。SET 命令用来将新值赋给被更新的列。在这里，SET 子句设 置 cust_email 列为指定的值：

SET cust_email = 'kim@thetoystore.com'

UPDATE 语句以 WHERE 子句结束，它告诉 DBMS 更新哪一行。没有 WHERE 子句，DBMS 将会用这个电子邮件地址更新 Customers 表中的所有行， 这不是我们希望的。

更新多个列的语法稍有不同：

UPDATE Customers SET cust_contact = 'Sam Roberts', cust_email = 'sam@toyland.com' WHERE cust_id = 1000000006;

在更新多个列时，只需要使用一条 SET 命令，每个“列=值”对之间用逗号分隔（最后一列之后不用逗号）

提示：在 UPDATE 语句中使用子查询 UPDATE 语句中可以使用子查询，使得能用 SELECT 语句检索出的数据 更新列数据。关于子查询及使用的更多内容，请参阅第 11 课。

（注意mysql不允许通过嵌套子查询来直接删除或者修改记录）需要给嵌套子查询的结果取一个别名，然后从这个表中再次查询出记录,然后再做删除或者修改操作。简单来说就是再嵌套一层：

update ***  set  ****=(select a.** from （select b.***from****as b where****） as a)  where ******;

提示：FROM 关键字 有的 SQL 实现支持在 UPDATE 语句中使用 FROM 子句，用一个表的数 据更新另一个表的行。如想知道你的 DBMS 是否支持这个特性，请参 阅它的文档。

要删除某个列的值，可设置它为 NULL

UPDATE Customers SET cust_email = NULL WHERE cust_id = 1000000005;

其中 NULL 用来去除 cust_email 列中的值。这与保存空字符串很不同 （空字符串用''表示，是一个值），而 NULL 表示没有值。

16.2 删除数据

从一个表中删除（去掉）数据，使用 DELETE 语句。有两种使用 DELETE 的方式：

 从表中删除特定的行

 从表中删除所有行。

注意：不要省略 WHERE 子句 在使用 DELETE 时一定要细心。因为稍不注意，就会错误地删除表中 所有行。在使用这条语句前，请完整地阅读本节。

提示：DELETE 与安全 在客户端/服务器的 DBMS 中，使用 DELETE 语句可能需要特殊的安全 权限。在你使用 DELETE 前，应该保证自己有足够的安全权限。

前面说过，UPDATE 非常容易使用，而 DELETE 更容易使用

DELETE FROM Customers WHERE cust_id = 1000000006;

这条语句很容易理解。DELETE FROM 要求指定从中删除数据的表名， WHERE 子句过滤要删除的行。在这个例子中，只删除顾客 1000000006。 如果省略 WHERE 子句，它将删除表中每个顾客。

提示：友好的外键 第 12 课介绍了联结，简单联结两个表只需要这两个表中的公用字段。 也可以让 DBMS 通过使用外键来严格实施关系（这些定义在附录 A 中）。存在外键时，DBMS 使用它们实施引用完整性。例如要向 Products 表中插入一个新产品，DBMS 不允许通过未知的供应商 id

插入它，因为 vend_id 列是作为外键连接到 Vendors 表的。那么， 这与DELETE 有什么关系呢？使用外键确保引用完整性的一个好处是， DBMS 通常可以防止删除某个关系需要用到的行。例如，要从 Products 表中删除一个产品，而这个产品用在 OrderItems 的已有订 单中，那么 DELETE 语句将抛出错误并中止。这是总要定义外键的另 一个理由。

提示：FROM 关键字 在某些 SQL 实现中，跟在 DELETE 后的关键字 FROM 是可选的。但是 即使不需要，也最好提供这个关键字。这样做将保证SQL代码在DBMS 之间可移植。

DELETE 不需要列名或通配符。DELETE 删除整行而不是删除列。要删除 指定的列，请使用 UPDATE 语句。

说明：删除表的内容而不是表 DELETE 语句从表中删除行，甚至是删除表中所有行。但是，DELETE 不删除表本身。

提示：更快的删除 如果想从表中删除所有行，不要使用 DELETE。可使用 TRUNCATE TABLE 语句，它完成相同的工作，而速度更快（因为不记录数据的变动）。truncate

16.3 更新和删除的指导原则

前两节使用的 UPDATE 和 DELETE 语句都有 WHERE 子句，这样做的理由 很充分。如果省略了 WHERE 子句，则 UPDATE 或 DELETE 将被应用到表 中所有的行。换句话说，如果执行 UPDATE 而不带 WHERE 子句，则表中每一行都将用新值更新。类似地，如果执行 DELETE 语句而不带 WHERE 子句，表的所有数据都将被删除。

下面是许多 SQL 程序员使用 UPDATE 或 DELETE 时所遵循的重要原则。

 除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE 子句 的 UPDATE 或 DELETE 语句。

 保证每个表都有主键（如果忘记这个内容，请参阅第 12 课），尽可能 像 WHERE 子句那样使用它（可以指定各主键、多个值或值的范围）。

 在 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进 行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不正确。

 使用强制实施引用完整性的数据库（关于这个内容，请参阅第 12 课）， 这样 DBMS 将不允许删除其数据与其他表相关联的行。

 有的 DBMS 允许数据库管理员施加约束，防止执行不带 WHERE 子句 的 UPDATE 或 DELETE 语句。如果所采用的 DBMS 支持这个特性，应 该使用它。

若是 SQL没有撤销（undo）按钮，应该非常小心地使用 UPDATE 和 DELETE， 否则你会发现自己更新或删除了错误的数据。

16.5挑战题

1. 美国各州的缩写应始终用大写。编写 SQL语句来更新所有美国地址，包 括供应商状态（Vendors 表中的 vend_state）和顾客状态（Customers 表中的 cust_state），使它们均为大写。

2. 第 15 课的挑战题 1 要求你将自己添加到 Customers 表中。现在请删除 自己。确保使用 WHERE 子句（在 DELETE 中使用它之前，先用 SELECT 对其进行测试），否则你会删除所有顾客！

17.创建和操纵表

 这一课讲授创建、更改和删除表的基本知识。

17.1创建表

SQL 不仅用于表数据操纵，而且还用来执行数据库和表的所有操作，包 括表本身的创建和处理

一般有两种创建表的方法：

 多数 DBMS 都具有交互式创建和管理数据库表的工具；

 表也可以直接用 SQL 语句操纵。

用程序创建表，可以使用 SQL 的 CREATE TABLE 语句。需要注意的是，使 用交互式工具时实际上就是使用 SQL 语句。这些语句不是用户编写的，界 面工具会自动生成并执行相应的 SQL 语句（更改已有的表时也是这样）。

注意：语法差别 在不同的 SQL 实现中，CREATE TABLE 语句的语法可能有所不同。对 于具体的 DBMS 支持何种语法，请参阅相应的文档。

这一课不会介绍创建表时可以使用的所有选项，那超出了本课的范围， 我只给出一些基本选项。详细的信息说明，请参阅具体的 DBMS 文档。

说明：各种 DBMS 创建表的具体例子 关于不同 DBMS 的 CREATE TABLE 语句的具体例子，请参阅附录 A 中 给出的样例表创建脚本

17.1.1 表创建基础

利用 CREATE TABLE 创建表，必须给出下列信息：

 新表的名字，在关键字 CREATE TABLE 之后给出

 表列的名字和定义，用逗号分隔；

 有的 DBMS 还要求指定表的位置

下面的 SQL 语句创建本书中所用的 Products 表

CREATE TABLE Products ( prod_id CHAR(10) NOT NULL, vend_id CHAR(10) NOT NULL, prod_name CHAR(254) NOT NULL, prod_price DECIMAL(8,2) NOT NULL, prod_desc VARCHAR(1000) NULL );

提示：语句格式化 回想一下在 SQL 语句中忽略的空格。语句可以在一个长行上输入，也 可以分成许多行，它们没有差别。这样，你就可以用最适合自己的方 式安排语句的格式。前面的 CREATE TABLE 语句就是 SQL 语句格式化 的一个好例子，代码安排在多个行上，列定义进行了恰当的缩进，更 易阅读和编辑。以何种格式安排 SQL 语句并没有规定，但我强烈推荐 采用某种缩进格式。

提示：替换现有的表 在创建新的表时，指定的表名必须不存在，否则会出错。防止意外覆 盖已有的表，SQL 要求首先手工删除该表（请参阅后面的内容），然 后再重建它，而不是简单地用创建表语句覆盖它。

17.1.2 使用NULL值

每个表列要么是 NULL 列，要么是 NOT NULL 列，这种状态在创建时由表 的定义规定。

注意：指定 NULL 在不指定 NOT NULL 时，多数 DBMS 认为指定的是 NULL，但不是所有 的 DBMS 都这样。某些 DBMS 要求指定关键字 NULL，如果不指定将 出错。关于完整的语法信息，请参阅具体的 DBMS 文档。

提示：主键和 NULL 值 第1课介绍过，主键是其值唯一标识表中每一行的列。只有不允许NULL 值的列可作为主键，允许 NULL 值的列不能作为唯一标识。

注意：理解 NULL 不要把 NULL 值与空字符串相混淆。NULL 值是没有值，不是空字符串。 如果指定''（两个单引号，其间没有字符），这在 NOT NULL 列中是允 许的。空字符串是一个有效的值，它不是无值。NULL 值用关键字 NULL 而不是空字符串指定

17.1.3 指定默认值

SQL 允许指定默认值，在插入行时如果不给出值，DBMS 将自动采用默 认值。默认值在 CREATE TABLE 语句的列定义中用关键字 DEFAULT 指定。

CREATE TABLE OrderItems ( order_num INTEGER NOT NULL, order_item INTEGER NOT NULL, prod_id CHAR(10) NOT NULL, quantity INTEGER NOT NULL DEFAULT 1, item_price DECIMAL(8,2) NOT NULL );

默认值经常用于日期或时间戳列。例如，通过指定引用系统日期的函数 或变量，将系统日期用作默认日期。MySQL 用户指定 DEFAULT CURRENT_ DATE()，Oracle 用户指定 DEFAULT SYSDATE，而 SQL Server 用户指定 DEFAULT GETDATE()。表 17-1 列出了这条命令在某些 DBMS 中的语法。 这里若未列出某个 DBMS，请参阅相应的文档。

 提示：使用 DEFAULT 而不是 NULL 值 许多数据库开发人员喜欢使用 DEFAULT 值而不是 NULL 列，对于用于 计算或数据分组的列更是如此

17.2 更新表

更新表定义，可以使用 ALTER TABLE 语句。虽然所有的 DBMS 都支持 ALTER TABLE，但它们所允许更新的内容差别很大。以下是使用 ALTER TABLE 时需要考虑的事情。

 理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计 过程中充分考虑未来可能的需求，避免今后对表的结构做大改动。

 所有的 DBMS 都允许给现有的表增加列，不过对所增加列的数据类型 （以及 NULL 和 DEFAULT 的使用）有所限制。

 许多 DBMS 不允许删除或更改表中的列。

 多数 DBMS 允许重新命名表中的列。

 许多 DBMS 限制对已经填有数据的列进行更改，对未填有数据的列几 乎没有限制。

可以看出，对已有表做更改既复杂又不统一。对表的结构能进行何种更 改，请参阅具体的 DBMS 文档。

使用 ALTER TABLE 更改表结构，必须给出下面的信息：

 在 ALTER TABLE 之后给出要更改的表名（该表必须存在，否则将出错）；

 列出要做哪些更改。

因为给已有表增加列可能是所有 DBMS 都支持的唯一操作，所以我们举 个这样的例子：

ALTER TABLE Vendors ADD vend_phone CHAR(20);

这条语句给 Vendors 表增加一个名为 vend_phone 的列，其数据类型 为 CHAR。更改或删除列、增加约束或增加键，这些操作也使用类似的语法。

注意，下面的例子并非对所有 DBMS 都有效：

ALTER TABLE Vendors DROP COLUMN vend_phone;

17.3 删除表

删除表（删除整个表而不是其内容）非常简单，使用 DROP TABLE 语句 即可：

DROP TABLE CustCopy;

这条语句删除 CustCopy 表（第 15 课中创建的）。删除表没有确认步骤， 也不能撤销，执行这条语句将永久删除该表。

提示：使用关系规则防止意外删除 许多 DBMS允许强制实施有关规则，防止删除与其他表相关联的表。在 实施这些规则时，如果对某个表发布一条 DROP TABLE 语句，且该表是 某个关系的组成部分，则 DBMS将阻止这条语句执行，直到该关系被删 除为止。如果允许，应该启用这些选项，它能防止意外删除有用的表。

17.4 重命名表

每个 DBMS 对表重命名的支持有所不同。对于这个操作，不存在严格的 标准。DB2、MariaDB、MySQL、Oracle 和 PostgreSQL 用户使用 RENAME 语句，SQL Server 用户使用 sp_rename 存储过程，SQLite 用户使用 ALTER TABLE 语句。

所有重命名操作的基本语法都要求指定旧表名和新表名。不过，存在 DBMS 实现差异。关于具体的语法，请参阅相应的 DBMS 文档。

17.6挑战题

1. 在 Vendors 表中添加一个网站列（vend_web）。你需要一个足以容纳 URL 的大文本字段。

 2. 使用 UPDATE 语句更新 Vendor 记录，以便加入网站（你可以编造任 何地址）。

 18.使用视图

这一课将介绍什么是视图，它们怎样工作，何时使用它们；还将讲述如 何利用视图简化前几课中执行的某些 SQL 操作。

18.1 视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索 数据的查询。

说明：SQLite 的视图 SQLite 仅支持只读视图，所以视图可以创建，可以读，但其内容不能 更改。

理解视图的最好方法是看例子。第 12 课用下面的 SELECT 语句从三个表 中检索数据：

SELECT cust_name, cust_contact FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num AND prod_id = 'RGAN01';

此查询用来检索订购了某种产品的顾客。任何需要这个数据的人都必须理解相关表的结构，知道如何创建查询和对表进行联结。检索其他产品 （或多个产品）的相同数据，必须修改最后的 WHERE 子句。

现在，假如可以把整个查询包装成一个名为 ProductCustomers 的虚拟 表，则可以如下轻松地检索出相同的数据：

SELECT cust_name, cust_contact FROM ProductCustomers WHERE prod_id = 'RGAN01';

这就是视图的作用。ProductCustomers 是一个视图，作为视图，它不 包含任何列或数据，包含的是一个查询（与上面用以正确联结表的查询 相同）

提示：DBMS 的一致支持 我们欣慰地了解到，所有 DBMS 非常一致地支持视图创建语法。

18.1.1为什么使用视图

我们已经看到了视图应用的一个例子。下面是视图的一些常见应用。

 重用 SQL 语句。

 简化复杂的 SQL 操作。在编写查询后，可以方便地重用它而不必知道 其基本查询细节。

 使用表的一部分而不是整个表。

 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的 访问权限。

 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行 SELECT 操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和 更新数据（添加和更新数据存在某些限制，关于这个内容稍后做介绍）

重要的是，要知道视图仅仅是用来查看存储在别处数据的一种设施。视 图本身不包含数据，因此返回的数据是从其他表中检索出来的。在添加 或更改这些表中的数据时，视图将返回改变过的数据。

注意：性能问题 因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时 需要的所有检索。如果你用多个联结和过滤创建了复杂的视图或者嵌 套了视图，性能可能会下降得很厉害。因此，在部署使用了大量视图 的应用前，应该进行测试。

18.1.2 视图的规则和限制

创建视图前，应该知道它的一些限制。不过，这些限制随不同的 DBMS 而不同，因此在创建视图时应该查看具体的 DBMS 文档。

下面是关于视图创建和使用的一些最常见的规则和限制。

 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的 名字）

 对于可以创建的视图数目没有限制。

 创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人 员授予。

 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。 所允许的嵌套层数在不同的 DBMS中有所不同（嵌套视图可能会严重降 低查询的性能，因此在产品环境中使用之前，应该对其进行全面测试）

 许多 DBMS 禁止在视图查询中使用 ORDER BY 子句。

 有些 DBMS 要求对返回的所有列进行命名，如果列是计算字段，则需 要使用别名（关于列别名的更多信息，请参阅第 7 课）

 视图不能索引，也不能有关联的触发器或默认值。

 有些 DBMS 把视图作为只读的查询，这表示可以从视图检索数据，但 不能将数据写回底层表。详情请参阅具体的 DBMS 文档。

 有些 DBMS 允许创建这样的视图，它不能进行导致行不再属于视图的 插入或更新。例如有一个视图，只检索带有电子邮件地址的顾客。 如果更新某个顾客，删除他的电子邮件地址，将使该顾客不再属于 视图。这是默认行为，而且是允许的，但有的 DBMS 可能会防止这种 情况发生。

提示：参阅具体的 DBMS 文档 上面的规则不少，而具体的 DBMS 文档很可能还包含别的规则。因此， 在创建视图前，有必要花点时间了解必须遵守的规定。

18.2 创建视图

理解了什么是视图以及管理它们的规则和约束后，我们来创建视图。

视图用 CREATE VIEW 语句来创建。与 CREATE TABLE 一样，CREATE VIEW 只能用于创建不存在的视图。

说明：视图重命名 删除视图，可以使用 DROP 语句，其语法为 DROP VIEW viewname;。 覆盖（或更新）视图，必须先删除它，然后再重新创建。

18.2.1 利用视图简化复杂的联结

一个最常见的视图应用是隐藏复杂的 SQL，这通常涉及联结。请看下面 的例子：

CREATE VIEW ProductCustomers AS SELECT cust_name, cust_contact, prod_id FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num;

这条语句创建一个名为 ProductCustomers 的视图，它联结三个表，返 回已订购了任意产品的所有顾客的列表。如果执行 SELECT * FROM ProductCustomers，将列出订购了任意产品的顾客。

检索订购了产品 RGAN01 的顾客，可如下进行：

SELECT cust_name, cust_contact FROM ProductCustomers WHERE prod_id = 'RGAN01';

这条语句通过 WHERE 子句从视图中检索特定数据。当 DBMS 处理此查询这条语句通过 WHERE 子句从视图中检索特定数据。当 DBMS 处理此查询可以看出，视图极大地简化了复杂 SQL 语句的使用。利用视图，可一次 性编写基础的 SQL，然后根据需要多次使用。

提示：创建可重用的视图 创建不绑定特定数据的视图是一种好办法。例如，上面创建的视图返 回订购所有产品而不仅仅是 RGAN01 的顾客（这个视图先创建）。扩展 视图的范围不仅使得它能被重用，而且可能更有用。这样做不需要创 建和维护多个类似视图。

18.2.2 用视图重新格式化检索出的数据

如前所述，视图的另一常见用途是重新格式化检索出的数据。下面的 SELECT 语句（来自第 7 课）在单个组合计算列中返回供应商名和位置：

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title FROM Vendors ORDER BY vend_name;

现在，假设经常需要这个格式的结果。我们不必在每次需要时执行这种拼接， 而是创建一个视图，使用它即可。把此语句转换为视图，可按如下进行：

CREATE VIEW VendorLocations AS SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title FROM Vendors;

要检索数据， 创建所有的邮件标签，可如下进行：

SELECT * FROM VendorLocations;

说明：SELECT 约束全部适用 在这一课的前面提到，各种 DBMS 中用来创建视图的语法相当一致。 那么，为什么会有多种创建视图的语句版本呢？因为视图只包含一个 SELECT 语句，而这个语句的语法必须遵循具体 DBMS 的所有规则和 约束，所以会有多个创建视图的语句版本。

18.2.3 用视图过滤不想要的数据

视图对于应用普通的 WHERE 子句也很有用。例如，可以定义 CustomerEMailList 视图，过滤没有电子邮件地址的顾客。为此，可使用下面的 语句：

CREATE VIEW CustomerEMailList AS SELECT cust_id, cust_name, cust_email FROM Customers WHERE cust_email IS NOT NULL;

显然，在将电子邮件发送到邮件列表时，需要排除没有电子邮件地址的 用户。这里的 WHERE 子句过滤了 cust_email 列中具有 NULL 值的那些 行，使它们不被检索出来。

SELECT * FROM CustomerEMailList;

说明：WHERE 子句与 WHERE 子句 从视图检索数据时如果使用了一条 WHERE 子句，则两组子句（一组在 视图中，另一组是传递给视图的）将自动组合。

18.2.4 使用视图与计算字段

在简化计算字段的使用上，视图也特别有用。下面是第 7 课中介绍的一 条 SELECT 语句，它检索某个订单中的物品，计算每种物品的总价格：

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

要将其转换为一个视图，如下进行

CREATE VIEW OrderItemsExpanded AS SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems

select * from OrderItemsExpanded where order_num=20008

可以看到，视图非常容易创建，而且很好使用。正确使用，视图可极大 地简化复杂数据的处理。

18.3 挑战题

1. 创建一个名为 CustomersWithOrders 的视图，其中包含 Customers 表中的所有列，但仅仅是那些已下订单的列。提示：可以在 Orders 表上使用 JOIN 来仅仅过滤所需的顾客，然后使用 SELECT 来确保拥 有正确的数据。

（表不能使用别名）

 2. 下面的 SQL 语句有问题吗？（尝试在不运行的情况下指出。） CREATE VIEW OrderItemsExpanded AS SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems ORDER BY order_num;

答：不能含有order by 视图中不允许ORDER BY。视图和表一样使用，如果需要排序数据，请在SELECT中使用ORDER BY从视图中检索数据。

2023/4/17 0.15