SQL学习笔记

读《SQL必知必会（第5版）》，把一些我认为的重要的地方摘录下来。

在线的SQL学习平台

• SQLBolt :只支持英文，适合小白学习

• SQLZOO: 支持繁体中文,建议还是用英文来看

第 1 课 了解 SQL

数据库（database）：保存有组织的数据的容器（通常是一个文件或一组文件）。

数据库软件应称为数据库管理系统（DBMS）。

SQL（发音为字母 S-Q-L或 sequel）是 Structured Query Language（结构化查询语言）的缩写。SQL是一种专门用来与数据库沟通的语言。

第 2 课 检索数据

1、多条 SQL语句必须以分号（ ； ）分隔。
2、SQL语句不区分大小写，但一般习惯关键字用大写，列名和表名使用小写。
3、处理 SQL 语句时，所有空格都会被忽略。一般认为写成多行更容易维护。
4、选择多个列时，一定要在列名之间加上逗号，但最后一个列名不加。如果在最后一个列名加了逗号，会出现错误。
5、SQL 一般返回原始的、无格式的数据。

使用 SELECT 关键字检索表数据，必须给出想选择什么（SELECT）和从什么地方选择两条信息（FROM）。

检索不同的值：使用 DISTINCT （只返回不同的/唯一的值）关键字，它必须直接放在列名的前面。DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。

第一个被检索的行是第 0行，而不是第 1行。因此， LIMIT 1 OFFSET
1 会检索第 2行，而不是第 1行。

使用注释：

• 行注释
  1：注释使用 –（两个连字符）嵌在行内。
  2：在一行的开始处使用 #（但这种形式有些 DBMS不支持 ）。

• 多行注释
  注释从/*开始，到 */ 结束， /*和 */之间的任何内容都是注释。

第 3 课 排序检索数据

使用 SELECT 语句的 ORDER BY 子句，根据需要排序检索出的数据。

在指定一条 ORDER BY 子句时，应该保证它是 SELECT 语句中最后一
条子句。如果它不是最后的子句，将会出错。

按多个列排序：要按多个列排序，只须指定这些列名，列名之间用逗号分开即可（就像选择多个列时那样）。

指定排序方向：数据排序不限于升序排序（从 A到 Z），这只是默认的排序顺序。还可以使用 ORDER BY 子句进行降序（从 Z 到 A）排序。为了进行降序排序，必须指定 DESC 关键字。
如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字。DESC 关键字只应用到直接位于其前面的列名。

第 4 课 过滤数据

使用 SELECT 语句的 WHERE 子句指定搜索条件。

在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于
WHERE 之后，否则将会产生错误。

• 操 作 符

= 等于
> 大于
<> 不等于
>= 大于等于
!= 不等于
!> 不大于
<  小于
BETWEEN 在指定的两个值之间
<= 小于等于
IS NULL 为NULL值
!< 不小于

某些操作符是冗余的（如 < > 与 != 相同， !< 相当于 >= ）。
并非所有 DBMS都支持这些操作符。想确定你的 DBMS支持哪些操作
符，请参阅相应的文档。

何时使用引号：如果仔细观察上述 WHERE 子句中的条件，会看到有的值括在单引号内，而有的值未括起来。单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。

在使用 BETWEEN 时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用 AND 关键字分隔。 BETWEEN 匹配范围中所有的值，包括指定的开始值和结束值。

NULL：无值（no value），它与字段包含 0、空字符串或仅仅包含空格不同。
空值检查：这个 WHERE 子句就是 IS NULL 子句。

第 5 课 高级数据过滤

AND 操作符：要通过不止一个列进行过滤；用在 WHERE 子句中的关键字，用来指示检索满足所有给定条件的行。

OR 操作符： 用来表示检索匹配任一给定条件的行。

求值顺序：SQL（像多数语言一样）在处理 OR 操作符前，优先处理 AND 操作符。此问题的解决方法是使用圆括号对操作符进行明确分组。

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。 IN 取
一组由逗号分隔、括在圆括号中的合法值。

WHERE 子句中的 NOT 操作符有且只有一个功能，那就是否定其后所跟的
任何条件。

第 6 课 用通配符进行过滤

LIKE 操作符

当过滤中使用的值是未知的（模糊的），用简单的比较操作符肯定不行，必须使用通配符。利用通配符，可以创建比较特定数据的搜索模式。

通配符(wildcard)：用来匹配值的一部分的特殊字符。通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。
搜索模式(search pattern)：由字面值、通配符或两者组合构成的搜索条件。

百分号（%）通配符：
最常使用的通配符是百分号（ % ）。在搜索串中， % 表示任何字符出现任意次数。使用了搜索模式 ‘Fish%’ ，将检索任意以Fish 起头的词。

通配符 % 看起来像是可以匹配任何东西，但有个例外，这就是 NULL 。
子句 WHERE prod_name LIKE ‘%’ 不会匹配产品名称为 NULL 的行。

• 搜索可以是区分大小写的。

下划线（_）通配符：下划线的用途与 % 一样，但它只匹配单个字符，而不是多个字符。

使用通配符的技巧

SQL 的通配符很有用，但这种功能是有代价的，即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。这里给出一些使用通配符时要记住的技巧：

不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

第 7 课 创建计算字段

计算字段

存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化。

需要特别注意，只有数据库知道 SELECT 语句中哪些列是实际的表列，哪些列是计算字段。从客户端（如应用程序）来看，计算字段的数据与其他列的数据的返回方式相同。

拼接字段

在 SQL中的 SELECT 语句中，可使用一个特殊的操作符来拼接两个列。根据你所使用的 DBMS，此操作符可用加号（ + ）或两个竖杠（ || ）表示。在 MySQL和 MariaDB中，必须使用特殊的函数。SQL Server使用 + 号。DB2、Oracle、PostgreSQL和SQLite使用 || 。

###　TRIM 函数
RTRIM() 函数去掉值右边的所有空格。
大多数 DBMS都支持 RTRIM() （正如刚才所见，它去掉字符串右边的空格）、 LTRIM() （去掉字符串左边的空格）以及 TRIM() （去掉字符串左右两边的空格）。

使用别名

SQL 支持列别名。别名（alias）是一个字段或值的替换名。别名用 AS 关键字赋予。

别名的名字既可以是一个单词，也可以是一个字符串。如果是后者，字符串应该括在引号中。虽然这种做法是合法的，但不建议这么去做。多单词的名字可读性高，不过会给客户端应用带来各种问题。因此，别名最常见的使用是将多个单词的列名重命名为一个单词的名字。

执行算术计算

SELECT 语句为测试、检验函数和计算提供了很好的方法。虽然 SELECT通常用于从表中检索数据，但是省略了 FROM 子句后就是简单地访问和处理表达式，例如 SELECT 3 * 2; 将返回 6 ， SELECT Trim(' abc ');将返回 abc ， SELECT Curdate(); 使用 Curdate() 函数返回当前日期和时间。现在你明白了，可以根据需要使用 SELECT 语句进行检验。

第 8 课 使用函数处理数据

函数带来的问题

虽然所有类型的函数一般都可以在每个 DBMS中使用，但各个函数的名称和语法可能极其不同。

与 SQL语句不一样，SQL函数不是可移植的。这意味着为特定 SQL实现编写的代码在其他实现中可能不能用。

如果你决定使用函数，应该保证做好代码注释，以便以后你自己（或其他人）能确切地知道这些 SQL代码的含义。

文本处理函数

常用的文本处理函数

LEFT() （或使用子字符串函数） 返回字符串左边的字符

LENGTH() （也使用 DATALENGTH() 或 LEN() ） 返回字符串的长度

LOWER() 将字符串转换为小写

LTRIM() 去掉字符串左边的空格

RIGHT() （或使用子字符串函数） 返回字符串右边的字符

RTRIM() 去掉字符串右边的空格

SUBSTR()或SUBSTRING() 提取字符串的组成部分（DB2、Oracle、PostgreSQL和SQLite使用 SUBSTR() ；MariaDB、MySQL和SQL Server使用 SUBSTRING()）

SOUNDEX() 返回字符串的 SOUNDEX 值（DSOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。）

UPPER() 将字符串转换为大写

日期和时间处理函数

日期和时间采用相应的数据类型存储在表中，每种 DBMS都有自己的特殊形式。

取当前日期 : DB2和PostgreSQL使用 CURRENT_DATE ；MariaDB和MySQL使用CURDATE() ；Oracle使用SYSDATE；SQL Server使用 GETDATE() ；SQLite使用 DATE()。

DB2，MySQL和 MariaDB具有各种日期处理函数，但没有 DATEPART() 。
DB2，MySQL和 MariaDB用户可使用名为 YEAR() 的函数从日期中提取年份。

不同 DBMS的日期 − 时间处理函数可能不同。关于你的 DBMS具体支持的日期 − 时间处理函数，请参阅相应的文档。

数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此不像字符串或日期 − 时间处理函数使用那么频繁。具有讽刺意味的是，在主要 DBMS的函数中，数值函数是最一致、最统一的函数。

常用数值处理函数

ABS() 返回一个数的绝对值
COS() 返回一个角度的余弦
EXP() 返回一个数的指数值
PI() 返回圆周率 π的值
SIN() 返回一个角度的正弦
SQRT() 返回一个数的平方根
TAN() 返回一个角度的正切

第 9 课 汇总数据

聚集函数

为方便这种类型的检索，SQL给出了 5个聚集函数，SQL 的聚集函数在各种主要 SQL实现中得到了相当一致的支持。

AVG()  通过对表中行数计数并计算其列值之和，求得该列的平均值。可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。注意：只用于单个列。

COUNT() 返回某列的行数

COUNT() 函数有两种使用方式：
1. 使用 COUNT(*) 对表中行的数目进行计数，不管表列中包含的是空值
（ NULL ）还是非空值。
2. 使用 COUNT(column) 对特定列中具有值的行进行计数，忽略 NULL 值。


MAX() 返回某列的最大值。 MAX() 要求指定列名，MAX() 函数忽略列值为 NULL 的行。

MIN() 返回某列的最小值。 MIN() 要求指定列名，MIN() 函数忽略列值为 NULL 的行。

SUM() 返回某列值之和。SUM() 函数忽略列值为 NULL 的行。

以上 5个聚集函数都可以如下使用。

• 对所有行执行计算，指定 ALL 参数或不指定参数（因为 ALL 是默认行
  为）。
• 只包含不同的值，指定 DISTINCT 参数。

第 10 课 分组数据

使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

创建分组

分组是使用 SELECT 语句的 GROUP BY 子句建立的。

在使用 GROUP BY 子句前，需要知道一些重要的规定。

• GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。

• 如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。

• GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。

• 大多数 SQL实现不允许 GROUP BY 列带有长度可变的数据类型（如文本或备注型字段）。

• 除聚集计算语句外， SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。

• 如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。

• GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前。

过滤分组

HAVING 非常类似于 WHERE 。事实上，目前为止所学过的所有类型的 WHERE 子句都可以用 HAVING 来替代。唯一的差别是， WHERE过滤行，而 HAVING 过滤分组。
HAVING 和 WHERE 的差别:这里有另一种理解方法， WHERE 在数据分组前进行过滤， HAVING 在数据分组后进行过滤。

使用 HAVING 时应该结合 GROUP BY 子句，而 WHERE 子句用于标准的行级过滤。

分组和排序

差别:

ORDER BY	GROUP BY
对产生的输出排序	对行分组，但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

一般在使用 GROUP BY 子句时，应该也给出 ORDER BY 子句。这是保证数据正确排序的唯一方法。千万不要仅依赖 GROUP BY 排序数据。

第 11 课 使用子查询

创建子查询（subquery），即嵌套在其他查询中的查询。

子查询常用于 WHERE 子句的 IN 操作符中，以及用来填充计算列。

在 SELECT 语句中，子查询总是从内向外处理。作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回错误。

第 12 课 联结表

SQL最强大的功能之一就是能在数据查询的执行中联结（join）表。联结是利用 SQL的 SELECT 能执行的最重要的操作。

关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（所以才叫关系数据库）。

简单说，联结是一种机制，用来在一条 SELECT 语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

内联结或等值联结的简单联结

要记住，在一条 SELECT 语句中联结几个表时，相应的关系是在运行中构造的。在联结两个表时，实际要做的是将第一个表中的每一行与第二个表中的每一行配对。 WHERE 子句作为过滤条件，只包含那些匹配给定条件（这里是联结条件）的行。

DBMS 在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降越厉害。

第 13 课 创建高级联结

使用表别名

SQL 除了可以对列名和计算字段使用别名，还允许给表名起别名。这样做有两个主要理由：

• 缩短 SQL语句；
• 允许在一条 SELECT 语句中多次使用相同的表。

*注意 Oracle 中没有 AS：
Oracle不支持 AS 关键字。要在 Oracle中使用别名，可以不用 AS ，简单地指定列名即可（因此，应该是 Customers C ，而不是 Customers AS C ）。

###　自联结（self-join）
自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多 DBMS处理联结远比处理子查询快得多。

###　自然联结（natural join）
自然联结排除多次出现，使每一列只返回一次。
自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（ SELECT * ），而对其他表的列使用明确的子集来完成。

###　外联结（outer join）
许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。

在使用 OUTER　JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（ RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN　左边的表）。

要记住，总是有两种基本的外联结形式：左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。换句话说，调整 FROM 或 WHERE　子句中表的顺序，左外联结可以转换为右外联结。因此，这两种外联结可以互换使用，哪个方便就用哪个。

全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行。MariaDB、MySQL和 SQLite不支持 FULL OUTER JOIN 语法。

第 14 课 组合查询

SQL也允许执行多个查询（多条 SELECT 语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

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

• 在一个查询中从不同的表返回结构数据；
• 对一个表执行多个查询，按一个查询返回数据。

可用 UNION 操作符来组合数条 SQL 查询。

UNION 规则

• UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字UNION 分隔（因此，如果组合四条 SELECT 语句，将要使用三个 UNION关键字）。
• UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是 DBMS可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。

UNION 从查询结果集中自动去除了重复的行；换句话说，它的行为与一条 SELECT 语句中使用多个 WHERE 子句条件一样。

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

第 15 课 插入数据

INSERT 用来将行插入（或添加）到数据库表。

插入完整的行；

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

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

插入行的一部分；

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

• 该列定义为允许 NULL 值（无值或空值）。
• 在表定义中给出默认值。这表示如果不给出值，将使用默认值。

插入某些查询的结果。

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

从一个表复制到另一个表

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

SELECT INTO 是试验新 SQL语句前进行表复制的很好工具。先进行复制，可在复制的数据上测试 SQL代码，而不会影响实际的数据。

第 16 课 更新和删除数据

基本的 UPDATE 语句由三部分组成，分别是：

• 要更新的表；
• 列名和它们的新值；
• 确定要更新哪些行的过滤条件。

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

要删除某个列的值，可设置它为 NULL （假如表定义允许 NULL 值）。

DELETE FROM 要求指定从中删除数据的表名，WHERE 子句过滤要删除的行。

DELETE 语句从表中删除行，甚至是删除表中所有行。但是， DELETE不删除表本身。

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

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

第 17 课 创建和操纵表

创建表

利用 CREATE TABLE 创建表。

在创建新的表时，指定的表名必须不存在，否则会出错。防止意外覆盖已有的表，SQL 要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。

NULL 值就是没有值或缺值。允许 NULL 值的列也允许在插入行时不给出该列的值。不允许 NULL 值的列不接受没有列值的行，换句话说，在插入或更新行时，该列必须有值。每个表列要么是 NULL 列，要么是 NOT NULL 列，这种状态在创建时由表的定义规定。

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

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

更新表

更新表定义，可以使用 ALTER TABLE 语句。

使用 ALTER　TABLE 时需要考虑的事情。

• 理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动。
• 所有的 DBMS都允许给现有的表增加列，不过对所增加列的数据类型（以及 NULL 和 DEFAULT 的使用）有所限制。
• 许多 DBMS不允许删除或更改表中的列。
• 多数 DBMS允许重新命名表中的列。
• 许多 DBMS限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。

使用 ALTER TABLE 要极为小心，应该在进行改动前做完整的备份（表结构和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，也许无法删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

删除表

删除表（删除整个表而不是其内容）非常简单，使用 DROP TABLE 语句。
删除表没有确认步骤，也不能撤销，执行这条语句将永久删除该表。

重命名表

每个 DBMS对表重命名的支持有所不同。对于这个操作，不存在严格的标准。DB2、MariaDB、MySQL、Oracle和 PostgreSQL用户使用 RENAME语句，SQL Server用户使用 sp_rename 存储过程，SQLite用户使用 ALTERTABLE 语句。所有重命名操作的基本语法都要求指定旧表名和新表名。

第 18 课 使用视图

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

视图的一些常见应用:

• 重用 SQL语句。

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

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

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

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

视图的规则和限制

• 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的
  名字）。
• 对于可以创建的视图数目没有限制。

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

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

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

• 有些 DBMS要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名（关于列别名的更多信息，请参阅第 7课）。- 视图不能索引，也不能有关联的触发器或默认值。

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

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

创建视图

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

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

第 19 课 使用存储过程

存储过程就是为以后使用而保存的一条或多条 SQL语句。可将其视为批文件，虽然它们的作用不仅限于批处理。

SQLite不支持存储过程

使用存储过程有三个主要的好处，即简单、安全、高性能。

在将 SQL代码转换为存储过程前，也必须知道它的一些缺陷。

• 不同 DBMS中的存储过程语法有所不同。事实上，编写真正的可移植存储过程几乎是不可能的。不过，存储过程的自我调用（名字以及数据如何传递）可以相对保持可移植。因此，如果需要移植到别的DBMS，至少客户端应用代码不需要变动。

• 一般来说，编写存储过程比编写基本 SQL语句复杂，需要更高的技能，更丰富的经验。因此，许多数据库管理员把限制存储过程的创建作为安全措施（主要受上一条缺陷的影响）。

执行存储过程

执行存储过程的 SQL语句很简单，即 EXECUTE 。 EXECUTE 接受存储过程名和需要传递给它的任何参数。

创建存储过程

CREATE PROCEDURE

对代码进行注释的标准方式是在之前放置 – （两个连字符）。

第 20 课 管理事务处理

事务处理

使用事务处理（transaction processing），通过确保成批的 SQL 操作要么完全执行，要么完全不执行，来维护数据库的完整性。

事务处理是一种机制，用来管理必须成批执行的 SQL操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态。

事务处理需要知道的几个术语：

• 事务（transaction）指一组 SQL语句；
• 回退（rollback）指撤销指定 SQL语句的过程；
• 提交（commit）指将未存储的 SQL语句结果写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。

事务处理用来管理 INSERT 、 UPDATE 和 DELETE 语句。不能回退 SELECT语句（回退 SELECT 语句也没有必要），也不能回退 CREATE 或 DROP 操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

控制事务处理

管理事务的关键在于将 SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。
不同 DBMS用来实现事务处理的语法有所不同。在使用事务处理时请参阅相应的 DBMS文档。

使用 ROLLBACK

SQL的 ROLLBACK 命令用来回退（撤销）SQL语句

使用 COMMIT

一般的 SQL语句都是针对数据库表直接执行和编写的。这就是所谓的隐式提交（implicit commit），即提交（写或保存）操作是自动进行的。在事务处理块中，提交不会隐式进行。不过，不同 DBMS的做法有所不同。有的 DBMS按隐式提交处理事务端，有的则不这样。

进行明确的提交，使用 COMMIT 语句。

使用保留点

要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。 在 SQL 中，这些占位符称为保留点。在 MariaDB、MySQL 和 Oracle 中创建占位符，可使用 SAVEPOINT 语句。

每个保留点都要取能够标识它的唯一名字，以便在回退时，DBMS 知道回退到何处。

在 SQL Server 中，可检查一个名为 @@ERROR的变量，看操作是否成功。（其他 DBMS 使用不同的函数或变量返回此信息。）如果 @@ERROR 返回一个非 0 的值，表示有错误发生，事务处理回退到保留点。如果整个事务处理成功，发布 COMMIT 以保留数据。

第 21 课 使用游标

游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

使用游标涉及几个明确的步骤。

• 在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句和游标选项。
• 一旦声明，就必须打开游标以供使用。这个过程用前面定义的 SELECT语句把数据实际检索出来。
• 对于填有数据的游标，根据需要取出（检索）各行。
• 在结束游标使用时，必须关闭游标，可能的话，释放游标（有赖于具体的 DBMS）。

声明游标后，可根据需要频繁地打开和关闭游标。在游标打开时，可根据需要频繁地执行取操作。

使用 DECLARE 语句创建游标，这条语句在不同的 DBMS 中有所不同。DECLARE 命名游标，并定义相应的 SELECT 语句，根据需要带 WHERE 和其他子句。

使用 OPEN CURSOR 语句打开游标;在处理 OPEN CURSOR 语句时，执行查询，存储检索出的数据以供浏览和滚动。

现在可以用 FETCH 语句访问游标数据了。 FETCH 指出要检索哪些行，从何处检索它们以及将它们放于何处（如变量名）。

CLOSE 语句用来关闭游标。一旦游标关闭，如果不再次打开，将不能使用。第二次使用它时不需要再声明，只需用 OPEN 打开它即可。

第 22 课 高级 SQL 特性

约束（constraint）

管理如何插入或处理数据库数据的规则。
DBMS 通过在数据库表上施加约束来实施引用完整性。大多数约束是在表定义中定义的。

1. 主键

是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。

1. 外键

是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。外键有助防止意外删除

1. 唯一约束

唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。

• 表可包含多个唯一约束，但每个表只允许一个主键。
• 唯一约束列可包含 NULL 值。
• 唯一约束列可修改或更新。
• 唯一约束列的值可重复使用。
• 与主键不一样，唯一约束不能用来定义外键。

1. 检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。

检查约束在数据类型内又做了进一步的限制，这些限制极其重要，可以确保插入数据库的数据正是你想要的数据。不需要依赖于客户端应用程序或用户来保证正确获取它，DBMS本身将会拒绝任何无效的数据。

索引

索引用来排序数据以加快搜索和排序操作的速度。

可以在一个或多个列上定义索引，使 DBMS保存其内容的一个排过序的列表。在定义了索引后，DBMS 以使用书的索引类似的方法使用它。DBMS 搜索排过序的索引，找出匹配的位置，然后检索这些行。

• 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。

• 索引数据可能要占用大量的存储空间。

• 并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。

• 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。

• 可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

索引必须唯一命名。

检查索引:
索引的效率随表数据的增加或改变而变化。许多数据库管理员发现，过去创建的某个理想的索引经过几个月的数据处理后可能变得不再理想了。最好定期检查索引，并根据需要对索引进行调整。

触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的 INSERT 、 UPDATE 和 DELETE 操作（或组合）相关联。

与存储过程不一样（存储过程只是简单的存储 SQL 语句），触发器与单个的表相关联。

一般来说，约束的处理比触发器快，因此在可能的时候，应该尽量使用约束。

数据库安全

任何安全系统的基础都是用户授权和身份确认。

安全性使用 SQL的 GRANT 和 REVOKE 语句来管理，不过，大多数 DBMS提供了交互式的管理实用程序，这些实用程序在内部使用 GRANT 和REVOKE 语句。