吴声子夜歌的博客

为了说明，我们创建一个游标来检索没有电子邮件地址的所有顾客，作为应用程序的组成部分，帮助操作人员找出空缺的电子邮件地址。有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标（cursor）是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。SQL检索操作返回一组称为结果集的行，这组返回的行都是与SQL语句相匹配的行（零行到多行）​。在这个例子中，FETCH用来检索当前行（自动从第一行开始）​，放到声明的变量CustRecord中。

有的DBMS允许用户定义自己的数据类型。例如，你可以定义自己的名为gender的数据类型，它是单字符的文本数据类型，带限制其值为M或F（对于未知值或许还允许NULL）的检查约束。主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。有的DBMS为此结合使用了操作系统的安全措施，而有的维护自己的用户及密码列表，还有一些结合使用外部目录服务服务器。ON用来指定被索引的表，而索引中包含的列（此例中仅有一列）在表名后的圆括号中给出。

在第一条INSERT语句之后定义了一个保留点，因此，如果后面的任何一个INSERT操作失败，事务处理能够回退到这里。事务处理是一种机制，用来管理必须成批执行的SQL操作，保证数据库不包含不完整的操作结果。虽然这不是最有用的例子，但它的确能够说明，在事务处理块中，DELETE操作（与INSERT和UPDATE操作一样）并不是最终的结果。使用事务处理（transaction processing）​，通过确保成批的SQL操作要么完全执行，要么完全不执行，来维护数据库的完整性。在事务处理块中，提交不会隐式进行。

传递给此过程的参数也是一个，即下订单的顾客ID。Oracle支持IN（传递值给存储过程）​、OUT（从存储过程返回值，如这里）​、INOUT（既传递值给存储过程也从存储过程传回值）类型的参数。存储过程的代码括在BEGIN和END语句中，这里执行一条简单的SELECT语句，它检索具有邮件地址的顾客。这段代码声明了一个变量来保存存储过程返回的任何值，然后执行存储过程，再使用SELECT语句显示返回的值。这段代码声明了一个变量来保存存储过程返回的任何值，然后执行存储过程，再使用SELECT语句显示返回的值。

这条语句创建一个名为ProductCustomers的视图，它联结三个表，返回已订购了任意产品的所有顾客的列表。在添加或更改这些表中的数据时，视图将返回改变过的数据。当DBMS处理此查询时，它将指定的WHERE子句添加到视图查询中已有的WHERE子句中，以便正确过滤数据。ProductCustomers是一个视图，作为视图，它不包含任何列或数据，包含的是一个查询（与上面用以正确联结表的查询相同）​。从视图检索数据时如果使用了一条WHERE子句，则两组子句（一组在视图中，另一组是传递给视图的）将自动组合。

对于这个操作，不存在严格的标准。在实施这些规则时，如果对某个表发布一条DROP TABLE语句，且该表是某个关系的组成部分，则DBMS将阻止这条语句执行，直到该关系被删除为止。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。这些语句不是用户编写的，界面工具会自动生成并执行相应的SQL语句（更改已有的表时也是这样）​。在不指定NOT NULL时，多数DBMS认为指定的是NULL，但不是所有的DBMS都这样。不允许NULL值的列不接受没有列值的行，换句话说，在插入或更新行时，该列必须有值。

在更新多个列时，只需要使用一条SET命令，每个“列=值”对之间用逗号分隔（最后一列之后不用逗号）​。DELETE FROM要求指定从中删除数据的表名，WHERE子句过滤要删除的行。没有WHERE子句，DBMS将会用这个电子邮件地址更新Customers表中的所有行，这不是我们希望的。可使用TRUNCATE TABLE语句，它完成相同的工作，而速度更快（因为不记录数据的变动）​。若是SQL没有撤销（undo）按钮，应该非常小心地使用UPDATE和DELETE，否则你会发现自己更新或删除了错误的数据。

要将一个表的内容复制到一个全新的表（运行中创建的表）​，可以使用CREATE SELECT语句（或者在SQL Server里也可用SELECT INTO语句）​。要插入多行，必须执行多个INSERT语句。INSERT SELECT是个例外，它可以用一条INSERT插入多行，不管SELECT语句返回多少行，都将被INSERT插入。与INSERT SELECT将数据添加到一个已经存在的表不同，CREATE SELECT将数据复制到一个新表（有的DBMS可以覆盖已经存在的表，这依赖于所使用的具体DBMS）​。

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句之后。但是，SQL也允许执行多个查询（多条SELECT语句）​，并将结果作为一个查询结果集返回。虽然ORDER BY子句似乎只是最后一条SELECT语句的组成部分，但实际上DBMS将用它来排序所有SELECT语句返回的所有结果。注意到在分别执行语句时，第一条SELECT语句返回3行，第二条SELECT语句返回2行。使用UNION很简单，所要做的只是给出每条SELECT语句，在各条语句之间放上关键字UNION。

但是，与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）​。与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行。自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT＊）​，而对其他表的列使用明确的子集来完成。下面的SELECT语句给出了一个简单的内联结。

要记住，在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。在联结两个表时，实际要做的是将第一个表中的每一行与第二个表中的每一行配对。这里的FROM子句列出三个表，WHERE子句定义这两个联结条件，而第三个联结条件用来过滤出订单20007中的物品。Products表只存储产品信息，除了存储供应商ID（Vendors表的主键）外，它不存储其他有关供应商的信息。联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结及其语法是学习SQL的极为重要的部分。创建联结的基本规则也相同。

最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句。外面的子查询返回顾客ID列表，此顾客ID列表用于最外层查询的WHERE子句。可以使用SELECT COUNT(＊)对表中的行进行计数，并且通过提供一条WHERE子句来过滤某个特定的顾客ID，仅对该顾客的订单进行计数。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。现在，我们知道了哪个订单包含要检索的物品，下一步查询与订单20007和20008相关的顾客ID。SELECT语句是SQL的查询。

此外，即使特定的DBMS总是按给出的GROUP BY子句排序数据，用户也可能会要求以不同的顺序排序。WHERE子句过滤所有prod_price至少为4的行，然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。或者返回只提供一项产品的供应商的产品，或者返回提供10个以上产品的供应商的产品，怎么办？目前为止的所有计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的。这就是分组大显身手的时候了。可以看到，WHERE子句在这里不起作用，因为过滤是基于分组聚集值，而不是特定行的值。

虽然MAX()一般用来找出最大的数值或日期值，但许多（并非所有）DBMS允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。虽然MIN()一般用来找出最小的数值或日期值，但许多（并非所有）DBMS允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。可以看到，在使用了DISTINCT后，此例子中的avg_price比较高，因为有多个物品具有相同的较低价格。如果指定列名，则COUNT()函数会忽略指定列的值为NULL的行，但如果COUNT()函数中用的是星号（＊）​，则不忽略。

值得注意的是，相同的代码在SQL Server中不起作用，因为它不支持to_date()函数。PostgreSQL不支持SOUNDEX()，因此以下的例子不适用于这个DBMS另外，如果在创建SQLite时使用了SQLITE_SOUNDEX编译时选项，那么SOUNDEX()在SQLite中就可用。这个例子使用了DATEPART()函数，顾名思义，此函数返回日期的某一部分。在这个例子中，EXTRACT()函数用来提取日期的成分，year表示提取哪个部分，返回值再与2020进行比较。

解决办法是把两个列拼接起来。在SQL中的SELECT语句中，可使用一个特殊的操作符来拼接两个列。举个例子，Orders表包含收到的所有订单，OrderItems表包含每个订单中的各项物品。在上述每个例子中，存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化。假如要生成一个供应商报表，需要在格式化的名称（位置）中列出供应商的位置。从上述输出中可以看到，SELECT语句返回包含上述四个元素的一个列（计算字段）​。

简单的解决办法是给搜索模式再增加一个%号：'F%y%’还匹配y之后的字符（或空格）​。在这个例子中，如果你想找出名称包含bean bag的所有产品，可以构造一个通配符搜索模式，找出在产品名的任何位置出现bean bag的产品。​[JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。不管是匹配一个值还是多个值，检验大于还是小于已知值，或者检查某个范围的值，其共同点是过滤中使用的值都是已知的。方括号（​[​]​）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

返回的行中有4行价格小于10美元，显然，返回的行未按预期的进行过滤。当SQL看到上述WHERE子句时，它理解为：由供应商BRS01制造的价格为10美元以上的所有产品，以及由供应商DLL01制造的所有产品，而不管其价格如何。事实上，许多DBMS在OR WHERE子句的第一个条件得到满足的情况下，就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来）​。这里的NOT否定跟在其后的条件，因此，DBMS不是匹配vend_id为DLL01，而是匹配非DLL01之外的所有东西。

其语法与其他WHERE子句的操作符稍有不同，因为它需要两个值，即范围的开始值和结束值。例如，BETWEEN操作符可用来检索价格在5美元和10美元之间的所有产品，或在指定的开始日期和结束日期之间的所有日期。这条语句返回所有没有价格（空prod_price字段，不是价格为0）的产品，由于表中没有这样的行，所以没有返回数据。如果仔细观察上述WHERE子句中的条件，会看到有的值括在单引号内，而有的值未括起来。从这个例子可以看到，在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值。

如果不排序，数据一般将以它在表中出现的顺序显示，这有可能是数据最初添加到表中的顺序。重要的是理解在按多个列排序时，排序的顺序完全按规定进行。换句话说，对于上述例子中的输出，仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。例如，如果要显示雇员名单，可能希望按姓和名排序（首先按姓排序，然后在每个姓中再按名排序）​。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。下面的代码检索3个列，并按其中两个列对结果进行排序——首先按价格，然后按名称排序。

SQL语句一般返回原始的、无格式的数据，不同的DBMS和客户端显示数据的方式略有不同（如对齐格式不同、小数位数不同）​。返回数据的顺序可能是数据被添加到表中的顺序，也可能不是。例如，你指定SELECT DISTINCT vend_id,prod_price，则9行里的6行都会被检索出来，因为指定的两列组合起来有6个不同的结果。使用这个语法，逗号之前的值对应OFFSET，逗号之后的值对应LIMIT（反着的，要小心）​。那么如何检索出不同的值？这是可行的，然而遗憾的是，各种数据库中的这一SQL实现并不相同。

【代码】SQL必知必会——数据准备。