Mysql

第1课 了解SQL

1.1 数据库基础

数据库(database) 是保存有组织的数据的容器，通常是一个文件或一组文件。

数据库软件是数据库管理系统（DBMS），数据库是通过 DBMS 创建和操纵的容器。

RDBMS 指关系型数据库管理系统。RDBMS 是 SQL 的基础，同样也是所有现代数据库系统的基础。

RDBMS 中的数据存储在被称为表（tables）的数据库对象中。

表

表是一种结构化的文件，存储在表中的数据是同一种类型的数据或清单。

数据库中每一个表都有一个唯一的名字。

表具有一些特性，包括数据在表中如何存储、存储什么样的数据、数据如何分解等。模式描述了表或数据库的这些特性信息。

列

表由列组成，列是表中的一个字段。使用表存储数据时要正确地将数据分解为多个列。

每个列都有相应的数据类型，限定了可存储在该列中地数据种类。

注意：数据类型及其名称的不同是 SQL 不兼容的一个主要原因。

行

行是表中的一个记录，表中的数据是按行存储的。

主键

表中每一行都应该有一列或几列可以唯一地标识自己，这一列或几列就是表的主键。

表中应该总是有一个主键。

作为主键的列需要满足的条件：

1. 任意两行都不具有相同的主键值

2. 每一行都必须有一个主键值（主键列不允许有NULL值）

3. 主键列中的值不允许修改或更新

4. 主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）

当多个列作为主键时，只需要所有的列值的组合是唯一的，单个列的值可以不唯一。

1.2 什么是SQL

SQL(Structured Query Language)：结构化查询语言。SQL 是一种专门用来与数据库沟通的语言。

SQL 不是某个特定数据库供应商专用的语言，几乎所有重要的 DBMS 都支持 SQL，但是不同厂商进行了不同的扩展。

标准 SQL 由 ANSI 标准委员会管理，因此称为 ANSI SQL。

可以把 SQL 分为两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)。

第2课 检索数据

SQL 语句不区分大小写，但是一般 SQL 关键字使用大写，列名和表名使用小写。

2.1 SELECT语句

SQL 语句由简单的英语单词组成，这些单词称为关键字。

每个 SQL 语句由一个或多个关键字构成，关键字不能作为表或列的名字。

多条 SQL 语句必须以分号分割，单条 SQL 语句一般不需要（有的 DBMS 也需要）

处理 SQL 语句时所有的空格都会被忽略，SQL 语句可以写成一行，也可以写成多行。

SQL 语句由子句组成，有些子句是必须的，有些是可选的。一个子句通常由一个关键字加上所提供的数据组成。比如 FROM 子句，WHRER 子句。

2.2 检索单个列

SELECT prod_name FROM Products;

SELECT 语句返回的数据是未排序的，可能是记录的添加顺序也可能不是。

2.3 检索多个列

SELECT prod_id,prod_name,prod_price FROM Products;

不同列名间以逗号分隔

2.4 检索所有列

SELECT * FROM Products;

一般除非确实需要每一列，否则不要使用 * 通配符，会降低性能。

2.5 检索不同的值

SELECT DISTINCT vend_id FROM Products;

关键字 DISTINCT 用来区分不同的值。

如果有多行 vend_id 列值相同，只返回其中一行

SELECT DISTINCT vend_id, prod_price FROM Products;

DISTINCT 作用于跟在其后的所有的列，而不是一列。

如果有多行的 vend_id 与 prod_price 两列的列值都相同，则只返回一行。

2.6 限制结果

只返回前 n 行

'MySQL, MariaDB, PostgreSQL, SQLite' SELECT prod_name FROM Products LIMIT 3;

不同数据库中的这一实现差异很大，上面的适用于 MySQL, MariaDB, PostgreSQL, SQLite

ONLY; 'Oracle' SELECT prod_name FROM Products WHRER ROWNUM <= 5;

只返回从 m 行开始的前 n 行

'MySQL, MariaDB, PostgreSQL, SQLite' SELECT prod_name FROM Products LIMIT 4 OFFSET 2;

返回从第 2 行（从 0 开始编号）开始的 4 行。

简化版写法

'MySQL, MariaDB' SELECT prod_name FROM Products LIMIT 4,2;

2.7 使用注释

--一条注释 #一条注释 //这种使用的较少 /*多行注释*/ //这是多行注释

第3课 排序检索数据

3.1 排序数据

SELECT prod_name，prod_id FROM Products ORDER BY prod_name;

按字母顺序进行排序。

通常 ORDER BY 子句必须是 SELECT 语句的最后一个子句。

ORDER BY 子句中使用的列一般是将要显示的列，但是也可以不是。

当有 LIMIT 时，LIMIT 应位于 ORDER BY 的后面，且 DBMS 会先执行 ORDER BY 后执行 LIMIT。

3.2 按多个列排序

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_id, prod_price;

首先按 prod_id 排序，对于 prod_id 相同的多行，按 prod_price 排序。

3.3 按列位置排序

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2,3;

按相对列位置进行排序。

ORDER BY 2,3 表示按照 SELECT 后跟的列名中的第二个和第三个进行排序

当根据不出现在 SELECT 清单中的列进行排序时，不能采用这种方法。但是可以混合使用实际列名和相对列位置。

3.4 指定排序方向

`SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2 DESC,3;`

DESC 是 DESCENDING 的缩写，两个都可以用。对应的升序是 ASC(ASCENDING)，可以省略。

第4课 过滤数据

4.1 使用WHERE子句

SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;

使用 WHERE 子句指定过滤条件。

字符串要用单引号括起来。

4.2 WHERE子句操作符

这里列出的操作符是冗余的，不同的 DBMS 可能支持不同的操作符。

MySQL支持的操作符如下：

此外还有 IS NULL，IN，NOT。

BETWEEN 操作符

SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;

BETWEEN 匹配的值包括两个边界值 5 和 10。

空值检查

SELECT prod_name, prod_price FROM Products WHERE prod_price IS NULL;

在使用不等于作为过滤条件时，并不会匹配到 NULL 值的行。

第5课 高级数据过滤

5.1 组合WHERE子句

可以使用 AND 操作符和 OR 操作符来组合多个条件。

AND 的优先级高于 OR，可以使用圆括号来控制运算顺序。建议当在一个子句种同时使用 AND 和 OR 时加上圆括号。

SELECT prod_name, prod_price FROM products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

5.2 IN操作符

SELECT prod_name, prod_price, vend_id FROM products WHERE vend_id IN ('DLL01', 'BRS01');

IN 后跟一个圆括号列表，用来指定要匹配值的清单。

IN 的作用和 OR 类似，可以使用 OR 完成同样的操作。IN 操作符一般比一组 OR 操作符执行的更快。

IN 可以包含其他 SELECT 语句。

5.3 NOT操作符

SELECT prod_name, prod_price FROM products WHERE NOT vend_id IN ('DLL01', 'BRS01');

NOT 否定其后所跟的任何条件。

第6课 用通配符进行过滤

6.1 LIKE操作符

使用 LIKE 来匹配具有特定模式的字符串。类似正则

通配符：用来匹配值的一部分的特殊字符。

搜索模式：由字面值、通配符或两者组合构成的搜索条件。通配符可以在搜索模式中的任意位置使用，并可以使用多个通配符。

LIKE 后一般跟一个使用了通配符的搜索模式。

根据 DBMS 的设置，搜索可以区分大小写。

几种常用的通配符：

5. % ：百分号通配符匹配任意数量的任意字符（0 个及 0 个以上）。（Microsoft Access 中对应的字符是 *）

6. _ ：下划线通配符用来匹配一个任意字符（Microsoft Access 中对应的字符是 ？，DB2 不支持 _）。

7. [ ] ：方括号通配符用来指定一个字符集，匹配一个属于字符集的字符（只有 Microsoft Access 和 SQL Server 支持它）。可以在方括号内的开始位置加一个 ^ （ Microsoft Access 中对应字符是 !）来表示否定（像正则表达式一样）

MySQL 仅支持 % 和 _ 两种通配符

注意字符串后面所跟的空格

许多 DBMA 会用空格填补字段的内容。比如某列有 50 个字符，而某一条记录只有 17 个字符，那它会在文本后附加 33 个空格。这可能会对搜索产生负面影响。可以使用函数去掉空格。

百分号通配符

SELECT prod_name FROM products WHERE prod_name LIKE 'F%y'; //匹配以 F 开头以 y 结尾的字符串

下划线通配符

SELECT prod_name FROM products WHERE prod_name LIKE '__ inch %';

方括号通配符

SELECT prod_name FROM products WHERE prod_name LIKE '[1-9][0-9] inch %';

6.2 使用通配符的技巧

通配符搜索比其他搜索耗费时间更多，因此：

8. 不要过度使用通配符，如果其他操作符能达到目的，就使用其他操作符。

9. 必须使用通配符时，尽量不要把它放在搜索模式的开始位置。

第7课 创建计算字段

7.1 计算字段

有时存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据。比如数据库中一列保存了姓，另一列保存了名，而用户想要将姓名一块输出。

在 SQL 语句内可完成的许多转换和格式化工作都可以直接在客户端应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户端中完成要快得多。

字段：基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常与计算字段一起使用

计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。

7.2 拼接字段

拼接：将值连接到一起构成单个值。

MySQL、MariaDB 使用 Concat 函数来拼接字段

'MySQL、MariaDB ' SELECT Concat(vend_name, ' (', RTRIM(vend_country), ')') FROM vendors ORDER BY vend_name;

其他 DBMS 使用 + 或 || 来拼接字段

'Access、SQL Server' SELECT vend_name + '(' + vend_country + ')' FROM vendors ORDER BY vend_name; 'DB2、Oracle、PostgreSQL、SQLite、Open Office Base' SELECT vend_name || '(' || vend_country || ')' FROM vendors ORDER BY vend_name;

TRIM 函数

大多数 DBMS 都支持 RTRIM()（去掉字符串右边的空格）、LTRIM()（去掉字符串左边的空格）以及 TRIM()（去掉字符串左右两边的空格）。

使用别名

上面 SELECT 拼接得到的字段没有名字，可以使用别名（alias）来作为一个字段或值的替换名。

SELECT Concat(vend_name, '(', vend_country, ')') AS vend_title FROM vendors ORDER BY vend_name;

第8课 使用数据处理函数

8.1 函数

几乎所有的 DBMS 都等同地支持 SQL 语句，但是每个 DBMS 都有特定的函数，只有少数几个函数被所有主要的 DBMS 等同地支持。

因为函数不可移植，所以如果使用函数编写 SQL 程序，要做好注释，以便理解。

'提取字符串的组成部分' SUBSTRING(); // MySQL、SQL Server SUBSTR(); // DB2、Oracle、PostgreSQL、SQLite MID(); // Access '数据类型转换' CONVERT(); // MySQL、MariaDB、SQL Server CAST(); // PostgreSQL、SQLite '取当前日期' CURDATE(); // MySQL、MariaDB 。MySQL 中 NOW() 是同时取日期和时间。 NOW(); // Access DATE(); // SQLite GETDATE(); // SQL Server CURRENT_DATE // DB2、PostgreSQL

8.2 使用函数

大多数 SQL 都支持以下函数：

10. 用于处理文本字符串的函数。如删除或填充值、转换大小写。

11. 用于在数值数据上进行算术操作的数值函数。如返回绝对值、进行算术运算。

12. 用于处理日期和是兼职并从中提取特定成分的日期和时间函数。如返回两个日期之差、检查日期有效性。

13. 返回 DBMS 正使用的特殊信息的系统函数。如返回用户登录信息。

下面只列出了 MySQL 中的函数。

文本处理函数

常用的文本处理函数

LEFT(); RIGHT(); // 返回字符串左边/右边的字符 LENGTH() // 返回字符串的长度 LOWER(); UPPER(); // 将字符串转换为小写/大写 LTRIM(); RTRIM(); TRIM(); // 去掉字符串左边/右边/两边的空格 SOUNDEX(); // 返回字符串的 SOUNDEX 值 LOCATE(); // 找出串的一个子串 SUBSTRING(); // 返回子串的字符

例子

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

SOUNDEX

SOUNDEX 值是描述一个文本串的语音表示的字母数字模式，他考虑了类似的发音字符和字节，使得能对字符串进行发音比较。

'下面这个语句未查询到符合条件的返回值' SELECT cust_contact FROM customers WHERE cust_contact = 'Michael Green'; '下面这个语句返回 Michelle Green，因为两者发音相似' SELECT cust_contact FROM customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

日期和时间处理函数

日期和时间采用相应的数据类型存储在表中，每种 DBMS 都有自己的特殊形式。日期和时间值以特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。

大多数 DBMS 具有比较日期、执行基于日期的运算、选择日期格式等的函数。

日期和时间函数在 SQL 中具有重要的作用，但是他们可移植性最差。

AddData(); // 增加一个日期（天、周等） AddTime(); // 增加一个时间（时、分等） CurDate(); CurTime(); // 返回当前日期/时间 DateDiff(); // 计算两个日期之差 Date_Add(); // 高度灵活的日期计算函数 Date_Format(); // 返回一个格式化的日期或时间串 DayOfWeek(); // 对于一个日期，返回对应的星期几 Year(); Month(); Day(); // 返回一个日期的年份/月份/天数部分 Hour(); Minute(); Second(); // 返回一个时间的小时/分钟/秒部分 Now(); // 返回当前日期和时间 Date(); // 返回一个日期时间的日期部分 Time(); // 返回一个日期时间的时间部分

例子

SELECT cust_id, order_num FROM orders WHERE DATE(order_date) = '2012-05-01';

数值处理函数

数值处理函数仅处理数值数据。一般用于代数、三角或几何运算。

Cos(); Sin(); Tan(); Abs(); // 返回绝对值 Mod(); // 返回余数 Pi(); // 返回圆周率 Rand(); // 返回随机数 Exp(); // 返回一个数的指数值 Sqrt(); // 返回一个数的平方根

第9课 汇总数据

9.1 聚集函数

聚集函数用来汇总数据，而非检索数据。比如想要知道表中的总行数、某一列的最大值、平均值等就可以使用聚集函数。

SQL 提供了 5 个聚集函数，它们对某些行运行，计算并返回一个值。

AVG() //返回某列的平均值 COUNT() //返回某列的行数 MAX() //返回某列的最大值 MIN() //返回某列的最小值 SUM() //返回某列值的和

AVG() 函数

AVG() 可以用来返回所有列的平均值，也可以返回特定列或特定行的平均值。

AVG() 只能用于数值列，且列名作为函数参数。

AVG() 函数会忽略值为 NULL 的行。

返回某一列中所有行的平均值

SELECT AVG(prod_price) AS avg_price FROM Products;

返回某一列中特定行的平均值

SELECT AVG(prod_price) AS avg_price FROM Products WHRER vend_id = 'DLL01';

COUNT() 函数

COUNT() 函数有两种使用方式：

14. COUNT(*)：返回表中的行数（值为 NULL 的行也会统计在内）

15. COUNT(colum)：对特定列中具有值的行进行计数，忽略 NULL 值。

SELECT COUNT(*) AS num_cust FROM Customers

MAX() 函数

MAX() 返回指定列中的最大值，可以用于数值或日期值。但许多 DBMS 也允许它用于返回文本列中的最大值。

SELECT MAX(prod_price) AS max_price FROM Products;

MIN() 函数

类似 MAX()

SUM() 函数

SUM() 返回指定列值的和，忽略值为 NULL 的行。

SELECT SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num = 20005;

9.2 聚集不同值

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

16. 对所有行执行计算（默认或显式指定 ALL 参数）

17. 只包含不同的值，指定 DISTINCT 参数。只计算不同的值的平均数

SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

9.3 组合聚集函数

SELECT 语句可以包含多个聚集函数。

SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM Products;

第10课 分组数据

分组数据用到两个 SELECT 子句：GROUP BY 子句和 HAVING 子句。

10.1 数据分组

使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算（使用第九章中的聚集函数）。

10.2 创建分组

使用 GROUP BY 子句创建分组。GROUP BY 子句指示 DBMS 分组数据，然后对每个组而非整个结果集进行聚集。

SELECT vend_id, COUNT(*) AS num_pords FROM products GROUP BY vend_id

上面的代码将 products 中的所有项目按不同供应商（vend_id）排序，然后分类并统计每个供应商的（vend_id）的产品数。

GROUP BY 的一些规定

18. GROUP BY 可以包含任意数目的列，因此可以对分组进行嵌套，更细致的进行数据分组。

19. 如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说就是基于指定的所有的列来分组。

20. GROUP BY 子句中列出的每一列必须是检索列或有效的表达式。如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句指定相同的表达式，不能用别名。

21. 大多数 SQL 不允许 GROUP BY 列带有长度可变的数据类型，如文本或备注型字段。

22. 除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。理解：因为结果返回的是对分组后的各个组进行聚集的结果，而不是对单条记录的统计结果。

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

24. GROUP BY 子句必须在 WHRER 子句之后，ORDER BY 子句之前（ORDER BY 永远在最后）。

10.3 过滤分组

可以使用 HAVING 子句过滤分组，类似于 WHERE 子句用来过滤行。实际上所有基本的 WHERE 子句都可以用 HAVING 来替代。HAVING 支持所有的 WHERE 操作符。

可以理解为：WHERE 在数据分组前过滤，HAVING 在数据分组后过滤。如果不指定 GROUP BY，那么 HAVING 和 WHERE 对大多 DBMS 来说是一样的。

SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

上面的代码中 HAVING 子句过滤了订单在两个以上（COUNT(*)>=2）的分组。

将 WHERE 和 HAVING 一块使用

SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;

上面的代码先使用 WHERE 过滤数据，然后对过滤后的数据分组，然后过滤分组。

10.4 分组和排序

GROUP BY 分组的数据一般是以分组顺序输出的，但这不是 SQL 规范所要求的。

一般在使用 GROUP BY 时，也要用 ORDER BY 子句。

10.5 SELECT子句顺序

SELECT子句及其顺序

SELECT //选择要返回的列或表达式 FROM //从表中检索数据 WHERE //行级过滤 GROUP BY //分组 HAVING //组级过滤 ORDER BY //输出排序

第11课 使用子查询

11.1 子查询

SQL 可以使用子查询，即嵌套在其他查询内的查询。

11.2 利用子查询进行过滤

嵌套的层数没有限制，但是实际使用一般不嵌套太多层。

作为子查询的 SELECT 语句只能查询单个列。

SELECT cust_id, cust_name, cust_city, cust_email FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = "RGAN01"));

上面的程序通过三层嵌套查询获得了订购产品 RGAN01 的所有顾客的详细信息。

11.3 作为计算字段使用子查询

也可以把子查询 SELECT 语句作为外层查询的计算字段。

计算字段中的子查询为外层查询检索出的每个项目执行一次。

SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders //这里使用了完全限定列名。 FROM customers ORDER BY cust_name;

上面的代码查询了所有顾客的订单数量。

完全限定列名

表名加列名就构成了完全限定列名，使用一个句点分隔表名和列名。

如果在 SELECT 语句中操作多个表，就应该使用完全限定列名来避免歧义。

第12课 联结表

12.1 联结

SQL 可以在数据查询中联结表。联结是 SQL 中一个最重要、最强大的特性。

关系表

关系数据库设计的基础：避免相同的数据出现多次。

关系表的设计要把信息分解成多个表，一类数据一个表。各个表通过一些共同值来互相关联。这样使关系数据库的可伸缩性非常好（即能够很好地适应不断增加的工作量）。

比如有一个存储产品的表，表中包含产品描述、价格、供应商等。还有一个存储供应商的表，包括供应商名称、地址、联系方式等。这里将产品数据与供应商数据分开两个表存储的理由是：

25. 同一个供应商生产的每个产品，其供应商信息都是相同的，对每个产品都存储供应商信息会浪费存储空间。

26. 如果供应商信息发生变化，只需要在供应商表中修改一次即可。

27. 如果有重复数据，很难保证每次输入该数据的方式都相同。

联结

联结是一种机制，用来在一条 SELECT 语句中联结多个表返回一组输出。

联结是 DBMS 在查询时建立起来的，在实际的数据库表中并不存在。

引用完整性

在联结不同表时，要维护好引用的完整性。

比如 Products 表中存储了一些商品，每个商品有对应的供应商 ID，另有一个表 Vendors 存储了所有的供应商，那么应保证 Products 中的所有商品的供应商 ID 在 Vendors 中都存在，不能向 Products 中插入 Vendors 表中没有的供应商 ID。

在 DBMS 的图形界面上一般可以进行相关设置，以保证引用完整性。

12.2 创建联结

SELECT vend_name, vendors.vend_id, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id;

上面的代码中 FROM 子句列出了两个表，这两个表通过 WHERE 子句建立了联结。

WHERE 子句的重要性

在一条 SELECT 语句中联结几个表时，相应的关系是在运行中构造的。

联结两个表时，实际上就是将第一个表中的每一行与第二个表中的每一行配对。

WHERE 子句作为过滤条件（这里是联结条件），只包含那些匹配给定条件的行。没有 WHERE 子句，第一个表中的每一行将于第二个表中的每一行配对，而不管它们逻辑上是否能配在一起。

要保证所有联结都有 WHERE 子句。

笛卡尔积：没有联结条件的表关系返回的结果是笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

SELECT vend_name, vendors.vend_id, prod_name, prod_price FROM vendors, products;

内联结

上面使用的联结称为等值联结，它基于两个表之间的相等测试，也称为内联结。

也可以使用另一种语法来进行相同操作。

这种语法是 ANSI SQL 规范首选语法，使用时两种选择一个顺手的即可。

注意：当使用了 JOIN 关键字后，就不能再用 WHERE，而必须用 ON。

SELECT vend_name, vendors.vend_id, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

第13课 创建高级联结

13.1 使用表别名

SQL 除了可以对列名和计算字段使用别名，还可以给表名起别名。

给表名起别名有两个作用：

28. 缩短 SQL 语句。

29. 允许在一条 SELECT 语句中多次使用相同的表。

SELECT cust_name,cust_contact FROM customers AS C, orders AS O, orderitems AS OI //在 FROM 子句中定义表别名 WHERE C.cust_id = O.cust_id AND O.order_num = OI.order_num AND prod_id = 'RGAN01';

表的别名可以用于 WHERE 子句，SELECT 列表等各个部分。

Oracle 不支持 AS 关键字，在 Oracle 中使用别名要把 AS 去掉。

列别名会返回到客户端，而表别名只在查询执行中使用，不返回到客户端。

SELECT cust_id, cust_name, cust_contact FROM customers WHERE cust_name = (SELECT cust_name FROM customers WHERE cust_contact = 'Jim Jones');

自然联结

因为联结的多个表必然具有相同的列，因此有时可能返回多次相同的列（实际应用中，基本所有的内联结都是自然联结）。

自然联结排除多次出现，使每一列只返回一次。

自然联结并不是系统完成的，而是要求你自己完成：只能选择那些唯一的列。一般通过对一个表使用通配符(SELECT *)，而对其他表的列使用明确的子集。

SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price FROM customers AS C, orders AS O, orderitems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';

上面的例子中，通配符只对第一个表使用，所有其他列明确列出，所以没有重复的列被检索出来。

外联结

内联结是将一个表中的行与另一个表中的行（按行依次匹配）相关联，外联结可以包含没有关联行（即对应的列为 NULL）的那些行。

使用关键字 OUTER JOIN 来指定联结类型，同时用 RIGHT 或 LEFT 关键字指定包含其所有行的表（RIGHT 包含右边的表，LEFT 包含左边的表）。

下面这个 LEFT 表示要查询 customers 中所有的行。

SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

还有一种外联结是全外联结，它检索两个表中的所有行并关联那些可以关联的行。全外联结包含两个表的不关联的行（MySQL 不支持全联结）。

SELECT customers.cust_id, orders.order_num FROM customers FULL OUTER JOIN orders ON customers.cust_id = orders.cust_id;

13.3 使用带聚集函数的联结

聚集函数可以和联结一起使用。

SELECT customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;

上面的例子将检索出的行按 customers.cust_id 分组并聚集。

多数情况，组合相同表的两个查询所完成的工作与有多个 WHERE 子句条件的一个查询所完成的工作相同。

14.2 创建组合查询

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

使用 UNION 很简单，给出每条SELECT 语句，在各条语句之间放上关键字 UNION 即可。

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';

上面组合查询的结果是两个查询结果的并集。

使用多条 WHERE 子句和使用组合查询之间哪个性能更好并不确定，实现时最好测试一下。

UNION 规则

使用 UNION 时需要遵守的规则：

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

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

32. 列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。

包含或取消重复的行

默认情况下 UNION 从查询结果集中自动去除了重复的行；如果想返回所有的匹配行，可使用 UNION ALL 而不是 UNION。

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

对组合查询结果进行排序

在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须位于最后一条SELECT 语句之后。DBMS 将用它来排序所有 SELECT 语句返回的所有结果。

第15课 插入数据

15.1 数据插入

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

33. 插入完整的行；

34. 插入行的一部分；

35. 插入某些查询的结果。

插入完整的行

最简单的方式

这种方式直接按表中列的顺序列出各列的值，没有值就使用 NULL。这种方法可移植性差，容易出错，不提倡。

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

明确给出列名

这种方式在表名后的括号里明确指定列名。优点是可移植性强，且即使表的结构改变也可以工作。建议使用这种方式。

这种方式下各列的顺序可以随意调换，只要列名与列值相对于即可。

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);

插入部分行

使用明确给出列名的方式还可以省略列，只提供一部分列的值。

省略的列必须满足：该列被定义为允许 NULL 或在表的定义中给出了该列的默认值（这表示如果不提供值将使用默认值）。

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 还存在另一种形式，可以利用它将 SELECT 语句的结果插入表中，这就是所谓的 INSERT SELECT。它是由一条 INSERT 语句和一条 SELECT 语句组成的。

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;

这个例子使用 INSERT SELECT 从 CustNew 中将所有数据导入 Customers。

这个例子在 INSERT 和 SELECT 语句中使用了相同的列名。但是不一定要求列名匹配。DBMS 不关心 SELECT 返回的列名。它使用的是列的位置，因此 SELECT 中的第一列（不管其列名）将用来填充表列中指定的第一列，第二列将用来填充表列中指定的第二列，如此等等。

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

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

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

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

与 INSERT SELECT 将数据添加到一个已经存在的表不同，SELECT INTO 将数据复制到一个新表。

SELECT * //要想只复制部分的列，可以明确给出列名 INTO CustCopy FROM Customers;

MySQL、MariaDB、Oracle、PostgreSQL 和 SQLite 不使用 SELECT INTO，而使用 CREATE 语句

CREATE TABLE CustCopy AS SELECT * FROM Customers;

在使用 SELECT INTO 时，需要知道一些事情：

36. 任何 SELECT 选项和子句都可以使用，包括 WHERE 和 GROUP BY；

37. 可利用联结从多个表插入数据；

38. 不管从多少个表中检索数据，数据都只能插入到一个表中。

在使用 UPDATE 时一定要细心。因为稍不注意，就会更新表中的所有行。

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

39. 要更新的表；

40. 列名和它们的新值；

41. 确定要更新哪些行的过滤条件。如果没有 WHERE 子句，将更新所有行。

UPDATE Customers SET cust_contact = 'Sam Roberts', cust_email = 'kim@thetoystore.com' WHERE cust_id = '1000000005';

UPDATE 语句中可以使用子查询，使得能用 SELECT 语句检索出的数据更新列数据。

删除某个列的值

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

UPDATE Customers SET cust_email = NULL WHERE cust_id = '1000000005';

16.2 删除数据

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

42. 从表中删除特定的行；

43. 从表中删除所有行。

在使用 DELETE 时一定要细心。因为稍不注意，就会错误地删除表中所有行。

DELETE FROM Customers WHERE cust_id = '1000000006';

上面的例子中，如果省略 WHERE 子句，它将删除表中每个顾客。

如果某个行通过外键与其他表中的行相连接，则 DELETE 将抛出错误并中止。

44. 使用强制实施引用完整性的数据库，这样 DBMS 将不允许删除其数据与其他表相关联的行。

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

第17课 创建和操纵表

17.1 创建表

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

46. 多数 DBMS 都具有交互式创建和管理数据库表的工具（这本质上也是通过 SQL 语句创建的）。

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

CREATE TABLE 语句用来创建表。使用 CREATE TABLE 语句时要给出下列信息：

48. 新表的名字，在关键字 CREATE TABLE 之后给出；

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

50. 有的 DBMS 还要求指定表的位置。

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, pro_desc TEXT(1000) NULL );

上面的 TEXT 只适用于 MySQL，在 Oracle、PostgreSQL、SQL Server 和 SQLite 中要换成 VARCHAR。

注意创建新表时，指定的表名必须之前不存在。

使用NULL值

NULL 值就是没有值，允许 NULL 值的列也允许在插入行时不给出该列的值。

每个表列要么是 NULL 列，要么是 NOT NULL 列。这在创建时由表的定义规定，不指明的话默认为 NULL 列。

只有 NOT NULL 列才能作为主键。

指定默认值

SQL 允许通过关键字 DEFAULT 指定某一列的默认值，在插入行时如果不给出值，DBMS 将自动采用默认值。

CREATE TABLE OrderItems ( prod_id CHAR(10) NOT NULL, quantity INTEGER NOT NULL DEFAULT 1, order_time CHAR(30) NOT NULL DEFAUTL CURRENT_DATE(), //指定系统时间为默认值 );

默认值经常用于日期或时间戳列，将系统日期作为默认日志。

17.2 更新表

可以使用 ALTER TABLE 语句更新表定义。不同 DBMS 的 ALTER TABLE 的可更新范围不同。

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

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

52. 列出要做哪些更改。

ALTER TABLE Vendors ADD vend_phone CHAR(20);

上面的例子给 Vendors 增加了一列。更改或删除列、增加约束或增加键等操作也使用类似的语法。

可以在一条 ALTER 语句中对一个表进行多次更改，不同更改间用逗号分隔。

更新表时注意的事项：

53. 理想情况下，不要在表中包含数据时对其进行更新。

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

55. 许多 DBMS 不允许删除或更改表中的列

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

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

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

58. 用新的列布局创建一个新表；

59. 使用 INSERT SELECT 语句从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段；

60. 检验包含所需数据的新表；

61. 重命名旧表（如果确定，可以删除它）；

62. 用旧表原来的名字重命名新表；

63. 根据需要，重新创建触发器、存储过程、索引和外键。

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

17.3 删除表

删除表使用 DROP TABLE 语句

DROP TABLE CustCopy;

删除表没有确认，也不能撤销，执行 DROP TABLE 语句将永久删除表。

许多 DBMS 允许强制实施有关规则，防止删除与其他表相关联的表。此时如果对某个表发布一条 DROP TABLE 语句，且该表是某个关系的组成部分，则 DBMS 将阻止这条语句执行。

17.4 重命名表

DB2、MariaDB、MySQL、Oracle 和 PostgreSQL 用户使用 RENAME 语句重命名表。

MySQL 重命名表的方式如下，可以在单个 RENAME 语句中重命名多个表，不同重命名间用逗号分隔。

RENAME TABLE customers TO customers2, vendors TO vendors2;

第18课 使用视图

18.1 视图

视图是虚拟的表。视图不包含数据，只包含查询语句。

理解：视图实际上是把一个查询包装为了一个虚拟表，虚拟表的内容就是包装起来的查询的结果。

创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行 SELECT 操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据。

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

因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索。

视图的用处

64. 重用 SQL 语句。

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

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

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

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

使用视图的一些规则和限制

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

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

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

72. 许多 DBMS 禁止在视图查询中使用 ORDER BY 子句，MySQL 允许。

73. 有些 DBMS 要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名。

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

75. 有些 DBMS 把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。

18.2 创建视图

视图用 CREATE VIEW 语句来创建。

利用视图简化复杂的联结

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 cust_name, cust_contact FROM ProductCustomers WHERE prod_id = 'RGAN01';

上面的例子通过 WHERE 子句从视图中检索数据。

利用视图，可一次性编写基础的 SQL，然后根据需要多次使用。

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

视图的另一常见用途是重新格式化检索出的数据。

对于以下查询：

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;

用视图过滤不想要的数据

视图对于应用普通的 WHERE 子句也很有用。

用视图简化计算字段

在简化计算字段的使用上，视图也特别有用。

删除视图

删除视图，可以使用 DROP 语句，其语法为 DROP VIEW viewname;。

更新视图

更新视图必须先删除它，然后再重新创建。

MySQL 中更新视图还可以使用 CREATE OR REPLACE VIEW 语句。如果要更新的视图不存在，它会创建一个视图；如果要更新的视图存在，它会替换原有视图。

第19课 使用存储过程

19.1 存储过程

经常会有一些复杂的操作需要多条 SQL 语句才能完成，这时可以创建存储过程。

存储过程就是为以后使用而保存的一条或多条SQL 语句。

19.2 为什么要使用存储过程

76. 保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。也能减少错误。

77. 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员不需要知道这些变化。

78. 因为存储过程通常以编译过的形式存储，所以 DBMS 处理命令所需的工作量少，提高了性能。

79. 存在一些只能用在单个请求中的SQL 元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

简而言之，存储过程的三个优点：简单、安全、高性能。

大多数 DBMS 将编写存储过程所需的安全和访问权限与执行存储过程所需的安全和访问权限区分开来。

19.3 执行存储过程

使用 EXECUTE 语句执行存储过程。注意 MySQL 使用的是 CALL 而不是 EXCUTE。

EXECUTE 语句接受存储过程名和需要传递给它的参数。

EXECUTE AddNewProduct( 'JTS01', 'Stuffed Eiffel Tower', 6.49, 'Plush');

上面执行了一个名为 AddNewProduct 的存储过程，它将一个新产品添加到 Products 表中。

AddNewProduct 有四个参数，分别是：供应商ID、产品名、价格和描述。这 4 个参数匹配存储过程中 4 个预期变量。

上面是存储过程的基本形式，对于具体的DBMS，可能包括以下的执行选择：

80. 参数可选，具有不提供参数时的默认值；

81. 不按次序给出参数，以“参数=值”的方式给出参数值。

82. 输出参数，允许存储过程在正执行的应用程序中更新所用的参数。

83. 用 SELECT 语句检索数据。

84. 返回代码，允许存储过程返回一个值到正在执行的应用程序。

19.4 创建存储过程

Oracle版本的一个例子

Oracle 支持三种类型的参数：IN（输入参数）、OUT（输出参数）、INOUT（输入输出参数）。

CREATE PROCEDURE MailingListCount( ListCount OUT INTERGER //关键字 OUT 指定了一个名为 ListCount 的输出参数 ) IS v_rows INTEGER; BEGIN //位于 BEGIN 和 END 之间的部分是存储过程的代码 SELECT COUNT(*) INTO v_rows FROM Customers WHERE NOT cust_email IS NULL; ListCount := v_rows END;

调用存储过程

var ReturnValue NUMBER //声明了一个变量用来保存存储过程返回的值 EXEC MailingListCount(:ReturnValue); //执行存储过程 SELECT ReturnValue; //使用 SELECT 语句显示返回的值。

MySQL版本的一个例子

CREATE PROCEDURE productPricing() //如果存储过程接受参数，它们将在()中列举出来 BEGIN SELECT Avg(prod_price) AS pricaverage FROM products END; DROP PROCEDURE productPricing; //删除存储过程

第20课 管理事务处理

可以利用 COMMIT 和 ROLLBACK 语句管理事务处理。

20.1 事务处理

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

事务处理是一种机制，用来管理必须成批执行的 SQL 操作，保证数据库不包含不完整的操作结果。

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

一个例子

一个给系统添加订单的过程如下：

85. 检查数据库中是否存在相应的顾客，如果不存在，添加他；

86. 检索顾客的 ID；

87. 在 Orders 表添加一行，它与顾客 ID 相关联；

88. 检索 Orders 表中赋予的新订单 ID；

89. 为订购的每个物品在 OrderItems 表中添加一行，通过检索出来的 ID 把它与 Orders 表关联（并且通过产品 ID 与 Products 表关联）。

上面的过程中，如果在步骤 3 和 步骤 5 之间发生了故障，就会导致一个没有商品的空订单，这样的订单是错误的。

使用事务处理来解决上述问题

使用事务处理后的工作过程：

90. 检查数据库中是否存在相应的顾客，如果不存在，添加他；

91. 提交顾客信息；

92. 检索顾客的ID；

93. 在 Orders 表中添加一行，它与顾客 ID 相关联；

94. 如果向 Orders 表添加行时出现故障，回退；

95. 检索Orders 表中赋予的新订单 ID；

96. 对于订购的每项物品，添加新行到 OrderItems 表；

97. 如果向 OrderItems 添加行时出现故障，回退所有添加的 OrderItems 行和 Orders 行。

事务处理中的几个关键词

- 事务：一组 SQL 语句。

- 回退：撤销指定的 SQL 语句。

- 提交：将未存储的 SQL 语句结果写入到数据库表。

- 保留点：事务处理中设置的临时占位符，可以对它发布回退。

事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。不能回退SELECT语句（也没有必要）、CREATE 、DROP。

20.2 控制事务处理

管理事务的关键在于将 SQL 语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

有的 DBMS 要求明确标识事务处理块的开始和结束。

MySQL 和 MariaDB 中的代码为：

START TRANSACTION ... //这些语句必须完全执行或完全不执行。

SQL Server 使用的语法：

BEGIN TRANSACTION ... COMMIT TRANSACTION

Oracle 中等同的代码为：

SET TRANSACTION ...

使用ROLLBACK

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

DELETE FROM Orders; ROLLBACK; //撤销上面的 DELETE 语句

使用COMMIT

一般的 SQL 语句都是针对数据库表直接执行和编写的，属于隐式提交（implicit commit），即提交操作是自动进行的。

在事务处理块中，提交不会隐式进行。而是使用 COMMIT 语句进行明确的提交。

START TRANSACTION DELETE FROM ordersitems WHERE order_num = 20010; DELETE FROM orders WHERE order_num = 20010; COMMIT;

当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭。

使用保留点

使用简单的 ROLLBACK 和 COMMIT 语句，就可以写入或撤销整个事务。

有时只想回退部分事务，可以在事务处理块中的合适位置放置占位符，回退时可以回退到某个占位符，这些占位符称为保留点。

使用 SAVEPOINT 语句创建保留点。

在 MySQL 、MariaDB 和 Oracle 中，如下进行：

SAVEPOINT delete1; ROLLBACK TRANSACTION delete1;

保留点越多越好，可以在 MySQL 代码中设置任意多的保留点。保留点会在事务处理完成后自动释放。

第21课 使用游标

21.1 游标

SQL 检索操作返回一组称为结果集的行，这组返回的行都是与 SQL 语句相匹配的行（零行或多行）。有时需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。

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

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

不同的DBMS 支持不同的游标选项和特性：

98. 能够标记游标为只读，使数据能读取，但不能更新和删除。

99. 能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置、相对位置等）。

100. 能标记某些列为可编辑的，某些列为不可编辑的。

101. 规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问。

102. 指示DBMS 对检索出的数据（而不是指出表中活动数据）进行复制，使数据在游标打开和访问期间不变化。

MySQL 中的游标

不像多数DBMS，MySQL游标只能用于存储过程（和函数）。

21.2 使用游标

创建游标

使用 DECLARE 语句创建游标，并定义相应的 SELECT 语句，根据需要带 WHERE 和其他子句。

CREATE PROCEDURE processorders() BEGIN DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; END;

上面的存储过程定义了一个游标，存储过程结束后游标就会消失。

使用游标

定义游标后可以打开游标，使用 OPEN 语句打开游标，然后使用 FETCH 语句访问游标数据，最后使用 CLOSE 语句关闭游标。

FETCH 指出要检索哪些行，从何处检索它们以及将它们放于何处（如变量名）。

OPEN ordernumbers //处理 OPEN 语句时会执行对应的查询并存储检索出的数据以供浏览和滚动。 FETCH ordernumbers INTO var; //用 FETCH 检索当前行的 order_num 列（自动从第一行开始）并存到局部变量 var 中。 CLOSE ordernumbers //关闭游标

一个例子

CREATE PROCEDURE processorders() BEGIN //声明局部变量 DECLARE done BOOLEAN DEFAULT 0; DECLARE o INT; DECLARE t DECIMAL(8, 2); //创建游标 DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; //声明 continue handler，这是在条件出现时被执行的代码。 //SQLSTATE'02000' 是一个未找到条件，当 REPEAT 由于没有更多的行供循环而不能继续时，出现这个条件。 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; //当 SQLSTATE '02000' 出现时，SET done=1。 //创建一个表用来存储结果 CREATE TABLE IF NOT EXISTS ordertotals (order_num INT, total DECIMAL(8, 2)); //打开游标 OPEN ordernumbers; //循环所有行 REPEAT FETCH ordernumbers INTO o; CALL ordertotal(o, 1, t); INSERT INTO ordertotals(order_num, total) VALUES(o, t); UNTIL done END REPEAT; //反复执行直到 done=1 //关闭游标 CLOSE ordernumbers; END;

第22课 高级SQL特性

SQL 的几个高级数据处理特性：约束、索引、触发器。

约束是实施引用完整性的重要部分，索引可改善数据检索的性能，触发器可以用来执行运行前后的处理，安全选项可用来管理数据访问。

22.1 约束

约束是管理如何插入或处理数据库数据的规则。

关系数据库存储分解为多个表的数据，每个表存储相应的数据。利用键来建立从一个表到另一个表的引用，由此产生了引用完整性。

DBMS 通过在数据库表上施加约束来实施引用完整性。大多数约束是在表定义中定义的，用 CREATE TABLE 或 ALTER TABLE 语句。

主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。

表中任意列只要满足以下条件，都可以用于主键：

103. 任意两行的主键值都不相同。

104. 每行都具有一个主键值（即列中不允许NULL 值）。

105. 包含主键值的列从不修改或更新。主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

在表定义时使用 PREMARY KEY 关键字声明主键

CREATE TABLE Vendors ( vend_id CHAR(10) NOT NULL PRIMARY KEY, vend_name CHAR(50) NOT NULL, vend_address CHAR(50) NULL, vend_city CHAR(50) NULL, vend_state CHAR(5) NULL, vend_zip CHAR(10) NULL, vend_country CHAR(50) NULL );

使用 CONSTRAINT 语法定义主键

ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY (vend_id);

外键

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

外键除了保证引用完整性外，还能防止意外的删除。比如订单中定义了外键关联到顾客，那么要想删除顾客必须先删除所有与该顾客相关联的订单项。

使用 REFERENCES 关键字声明外键

CREATE TABLE Orders ( order_num INTEGER NOT NULL PRIMARY KEY, order_date DATETIME NOT NULL, cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id) );

上面的例子在 Orders 的 cust_id 列上定义了一个外键，这样 cust_id 中的任何值都必须是 Customers 表中的 cust_id 值。

使用 CONSTRAINT 语法声明外键

ALTER TABLE Venders ADD CONSTRAINT REFERENCES Customers(cust_id);

唯一约束

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

106. 表可包含多个唯一约束，但每个表只允许一个主键。

107. 唯一约束列可包含 NULL 值。

108. 唯一约束列可修改或更新。

109. 唯一约束列的值可重复使用。

110. 与主键不一样，唯一约束不能用来定义外键。

唯一约束的语法类似于其他约束的语法。唯一约束既可以用 UNIQUE 关键字在表定义中定义，也可以用单独的 CONSTRAINT 定义。

检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检查约束的常见用途有以下几点：

111. 检查最小或最大值。例如防止 0 个物品的订单（即使 0 是合法的数）。

112. 指定范围。例如保证发货日期大于等于今天的日期，但不超过今天起一年后的日期。

113. 只允许特定的值。例如在性别字段中只允许 M 或 F。

数据类型限制了列中可保存的数据的类型，检查约束在数据类型内又做了进一步的限制。

使用关键字 CHECK 添加检查约束

CREATE TABLE OrderItems ( order_num INTEGER NOT NULL, order_item INTEGER NOT NULL, prod_id CHAR(10) NOT NULL, quantity INTEGER NOT NULL CHECK (quantity > 0), item_price MONEY NOT NULL );

使用 CONSTRAINT 语法添加检查约束

ALTER TABLE Products ADD CONSTRAINT CHECK (gender LIKE '[MF]') //使 gender 列只包含 M 或 F。

22.2 索引

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

数据库索引的作用与书籍中索引/目录的作用一样。

主键数据总是排序的，这是 DBMS 的工作。因此，按主键检索特定行总是一种快速有效的操作。但是，搜索其他列中的值通常效率不高。

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

在开始创建索引前，应该记住以下内容：

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

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

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

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

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

索引用 CREATE INDEX 语句创建，索引必须唯一命名。

CREATE INDEX prod_name_id ON Products (prod_name);

22.3 触发器

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

与存储过程不一样（存储过程只是简单的存储 SQL 语句），触发器与单个的表相关联。与 Orders 表上的 INSERT 操作相关联的触发器在 Orders 表中插入行时执行。

触发器内的代码具有以下数据的访问权：

119. INSERT 操作中的所有新数据；

120. UPDATE 操作中的所有新数据和旧数据；

121. DELETE 操作中删除的数据。

下面是触发器的一些常见用途。

122. 保证数据的一致性。例如，在 INSERT 或 UPDATE 操作中将所有州名转换为大写。

123. 基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。

124. 进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。

125. 计算计算列的值或更新时间戳。

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

MySQL中的触发器

只有表才支持触发器，视图和临时表都不支持。

创建触发器

使用 CREATE TRIGGER 创建触发器，触发器可以在一个操作发生前或发生后执行。

在创建触发器时，需要给出4条信息：

126. 唯一的触发器名；触发器应该有一个在数据库内唯一的名字。

127. 触发器关联的表；

128. 触发器应该响应的活动（DELETE、INSERT 或 UPDATE）；

129. 触发器何时执行（处理之前（BEFORE）或之后（AFTER））。

CREATE TRIGGER newproduct AFTER INSERT ON products //触发器在表 products 的 INSERT 语句成功执行后执行。 FOR EACH ROW SELECT 'Product added';

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条 INSERT、UPDATE 和 DELETE 的之前和之后）。

触发器失败：如果 BEFORE 触发器失败，则将不执行请求的操作。如果 BEFORE 触发器或语句本身失败，将不执行 AFTER 触发器。

删除触发器

DROP TRIGGER newproduct;

使用 INSERT 触发器

INSERT触发器在 INSERT 语句执行之前或之后执行。需要知道以下几点：

130. 在 INSERT 触发器代码内，可引用一个名为 NEW 的虚拟表，访问被插入的行；

131. 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新（允许更改被插入的值）；

132. 对于 AUTO_INCREMENT 列（这种列具有 MySQL 自动赋予的值），NEW 在 INSERT 执行之前包含 0，在 INSERT 执行之后包含新的自动生成值。

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num; //每次插入新订单时都显示订单号

BEFORE 通常用于数据验证和净化。

DELETE触发器

DELETE 触发器在 DELETE 语句执行之前或之后执行。需要知道以下两点：

133. 在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行；

134. OLD 中的值全都是只读的，不能更新。

CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW BEGIN INSERT INTO archive_orders(order_num, order_date, cust_id) VALUES(OLD.order_num, OLD.order_date, OLD.cust_id) END; //将要被删除的订单保存到名为 archive_orders 的存档表中。

UPDATE触发器

UPDATE触发器在 UPDATE 语句执行之前或之后执行。需要知道以下几点：

135. 在 UPDATE 触发器代码中，可以引用一个名为 OLD 的虚拟表访问以前（UPDATE 语句前）的值，引用一个名为 NEW 的虚拟表访问新更新的值；

136. 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新（允许更改将要用于 UPDATE 语句中的值）；

137. OLD 中的值全都是只读的，不能更新。

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state); //保证每个州名都是大写。

22.4 数据库安全

大多数 DBMS 都给管理员提供了管理机制，利用管理机制授予或限制对数据的访问。

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

一般说来，需要保护的操作有：

138. 对数据库管理功能（创建表、更改或删除已存在的表等）的访问；

139. 对特定数据库或表的访问；

140. 访问的类型（只读、对特定列的访问等）；

141. 仅通过视图或存储过程对表进行访问；

142. 创建多层次的安全措施，从而允许多种基于登录的访问和控制；

143. 限制管理用户账号的能力。

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