前言

读《sql必知必会 第四版》随手做的笔记，写的比较乱，可读性并不好，读的是中文版，翻译过来的感觉有点怪怪的。
想要pdf的话可以留邮箱给我。

1. 使用DISTINCT关键字，它指示数据库只返回不同的值。

SELECT DISTINCT vend_id FROM Products;

警告:不能部分使用DISTINCT
DISTINCT关键字作用于所有的列，不仅仅是跟在其后的那一列。例如，你指定SELECT DISTINCT vend_id, prod_price，除非指定的两列完 全相同，否则所有的行都会被检索出来。

2. LIMIT

如果你只想返回第一行或者一定数量的行

-- LIMIT 5指示MySQL等DBMS返回不超过5行的数据
SELECT prod_name FROM Products LIMIT 5;
-- LIMIT 5 OFFSET 5指示MySQL等DBMS返回从第5行起的5行数据。
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;

3. 排序检索数据

1. ORDER BY，根据需要排序检索出的数据。

 SELECT prod_name FROM Products
 ORDER BY prod_name;

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

2. 按多个列排序

要按多个列排序，简单指定列名，列名之间用逗号分开即可(就像选择多个列
时那样)。

 --下面的代码检索3个列，并按其中两个列对结果进行排序——首先按价格，
 然后按名称排序。
 SELECT prod_id, prod_price, prod_name FROM Products
 ORDER BY prod_price, prod_name;

重要的是理解在按多个列排序时，排序的顺序完全按规定进行。换句话说，对于上述例子中的输出，仅在多个行具有相同的prod_price值时才 对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。

3. 按列位置排序

除了能用列名指出排序顺序外，ORDER BY还支持按相对列位置进行排序。

```sql
-- 只对 2， 3 列进行排序
SELECT prod_id, prod_price, prod_name FROM Products
ORDER BY 2, 3;
```

4. 指定排序方向

排序默认升序，降序的话用 DESC 关键字

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

如果打算用多个列排序

-- DESC关键字只应用到直接位于其前面的列名。
-- 在上例中，只对prod_price列指定DESC，对prod_name列不指定。
-- 因此，prod_price列以降序排 序，而prod_name列(在每个价格内)仍然按标准的升序排序。
SELECT prod_id, prod_price, prod_name FROM Products
ORDER BY prod_price DESC, prod_name;

警告:在多个列上降序排序 如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。

DESC是DESCENDING的缩写，这两个关键字都可以使用。与DESC相对的是ASC(或ASCENDING)，在升序排序时可以指定它。但实际 上，ASC没有多大用处，因为升序是默认的(如果既不指定ASC也不指定DESC，则假定为ASC)

4. 过滤数据

1. 使用WHERE子句

数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名(FROM子句)之后给出。

SELECT prod_name, prod_price FROM Products
WHERE prod_price = 3.49;

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

2. WHERE子句操作符

-- 列出所有不是供应商DLL01制造的产品
SELECT vend_id, prod_name FROM Products
WHERE vend_id <> 'DLL01';

--BETWEEN操作符可用来检索价格在5美元和10美元之间的所有产品
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

-- 检索出 prod_price 是空的字段
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

5. 高级数据过滤

NOT和IN

1. 组合WHERE子句

1.1. AND操作符

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

1.2. OR操作符
OR操作符与AND操作符正好相反，它指示DBMS检索匹配任一条件的行。事实上，许多DBMS在OR WHERE子句的第一个条件得到满足的情况下，
就不再计算第二个条件了(在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来)

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;

1.3. 求值顺序
SQL(像多数语言一样)在处理OR操作符前，优先处理AND操作符。

SELECT prod_name, prod_price FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’ AND prod_price >= 10;

提示:在WHERE子句中使用圆括号 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序，即使它确实如你希望的 那样。使用圆括号没有什么坏处，它能消除歧义。

2. IN操作符

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

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' ) ORDER BY prod_name;

--类似与

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ORDER BY prod_name;

3. NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件。因为NOT从不单独使用(它总是与其他操作符一起使用)，所以
它的语法与其他操作符有所不同。NOT关键字可以用在要过滤的列前，而不仅是在其后。

NOT WHERE子句中用来否定其后条件的关键字。

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;
- 等同于
SELECT prod_name
FROM Products
WHERE vend_id <> 'DLL01'
ORDER BY prod_name;

6. 用通配符进行过滤

为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指 示DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。
通配符搜索只能用于文本字段(串)，非文本数据类型字段不能使用通配符搜索。

1. 百分号(%)通配符

%表示任何字符出现任意次数

--找出所有以词Fish起头的产品
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，它们位于模式的两端:

--'%bean bag%'表示匹配任何位置上包含文本bean bag的值
--不论它之前或之后出现什么字符
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

2. 下划线(_)通配符

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

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

与%能匹配0个字符不同，_总是刚好匹配一个字符，不能多也不能少。

3. 方括号([ ])通配符

方括号([])通配符用来指定一个字符集，它必须匹配指定位置(通配符的位置)的一个字符

-- 找出所有名字以J或M起头的联系人
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

--查询匹配不以J或M起头的任意联系人名
--如果使用的是Microsoft Access，需要用!而不是^来否定一个集合
--因此，使用的是[!JM]而不是[^JM]
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;

--也可以使用NOT操作符得出相同的结果
SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

7. 创建计算字段

1. 拼接字段

拼接(concatenate)
将值联结到一起(将一个值附加到另一个值)构成单个值。
Access和SQL Server使用+号。
DB2、Oracle、PostgreSQL、SQLite和Open Office Base使用||。

SELECT vend_name || ' (' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;

/*
*再看看上述SELECT语句返回的输出。
*结合成一个计算字段的两个列用空格填充。
*许多数据库(不是所有)保存填充为列宽的文本值，而实际上
*你要的结果不需要这些空格。
*为正确返回格式化的数据，必须去掉这些空格。
*这可以使用SQL的RTRIM()函数来完成
*/
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name;

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

2. 使用别名

别名(alias)是一个字段或值的替换名。别名用AS关键字赋予

--它指示SQL创建一个包含指定计算结果的名 为vend_title的计算字段。
--任何客户端应用都可以按名称引用这个列，就像 它是一个实际的表列一样。
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;

3. 执行算术计算

--检索订单号20008中的所有物品
SELECT prod_id, quantity, item_price
FROM OrderItems
WHERE order_num = 20008;

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

SQL算术操作符 +、-、*、/

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

8. 使用数据处理函数

与SQL语句不一样，SQL函数不是可移植的。这表示为特定SQL实现编写的代码在其他实现中可能不正常.
例如：

1. 文本处理函数

-- UPPER()将文本转换为大写
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;

常用的文本处理函数

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发 音字符和音节，使得能对字符串进行发音比较而不是字母比较。

说明:SOUNDEX支持
Microsoft Access和PostgreSQL不支持SOUNDEX()，因此以下的例子不适用于这些DBMS。 另外，如果在创建SQLite时使用了SQLITE_SOUNDEX编译时选项，那么SOUNDEX()在SQLite中就可用。因为SQLITE_SOUNDEX不是默认的编译时
选项，所以多数SQLite实现不支持SOUNDEX()。

-- WHERE子句使用SOUNDEX()函数把cust_contact列值和搜索字符串转换为它们的SOUNDEX值。
--因为Michael Green和Michelle Green发音相似，所以它们的SOUNDEX值匹配
--因此WHERE子句正确地过滤出了所需的数据。
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

2. 日期和时间处理函数

--SQL Server中检索2012年的所有订单
SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date) = 2012;

-- SQLite
SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = 2012;

3. 数值处理函数

常用数值处理函数

9. 汇总数据

1. 聚集函数

聚集函数(aggregate function) 对某些行运行的函数，计算并返回一个值。

SQL聚集函数

1.1. AVG()函数

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

--返回值avg_price，它包含Products表中所有产品的平均价格
SELECT AVG(prod_price) AS avg_price
FROM Products;

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

警告:只用于单个列
AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。

说明:NULL值
AVG()函数忽略列值为NULL的行。

1.2. COUNT()函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。
COUNT()函数有两种使用方式:

• 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值。
• 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

SELECT COUNT(*) AS num_cust
FROM Customers;

--只对具有电子邮件地址的客户计数:
SELECT COUNT(cust_email) AS num_cust
FROM Customers;

1.3 MAX()函数

MAX()返回指定列中的最大值。MAX()要求指定列名

SELECT MAX(prod_price) AS max_price
FROM Products;

1.4 MIN()函数

返回指定列的最小值，要求指定列名

SELECT MIN(prod_price) AS min_price
FROM Products;

1.5 SUM()函数

SUM()用来返回指定列值的和(总计)

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;

--  SUM()也可以用来合计计算值
SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;

2. 聚集不同值

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

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

--在使用了DISTINCT后，此例子中的avg_price比较高
--因为有多个物品具有相同的较低价格。
--排除它们提升了平均价格
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

警告:DISTINCT不能用于COUNT()
如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT()。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

提示:将DISTINCT用于MIN()和MAX()
虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否只考虑不同值，结果都是相同
的。

说明:其他聚集参数
除了这里介绍的DISTINCT和ALL参数，有的DBMS还支持其他参数，如支持对查询结果的子集进行计算的TOP和TOP PERCENT。为了解具体的 DBMS支持哪些参数，请参阅相应的文档。

3. 组合聚集函数

-- 这里用单条SELECT语句执行了4个聚集计算
--返回4个值(Products表中物品的数目
--产品价格的最高值、最低值以及平均值)
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子句

1. 创建分组

-- 分组是使用SELECT语句的GROUP BY子句建立的。
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

在使用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子句之前。

2. 过滤分组

除了能用GROUP BY分组数据外，SQL还允许过滤分组，规定包括哪些分组，排除哪些分组。
WHERE过滤行，而HAVING过滤分组。

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

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

说明:使用HAVING和WHERE
HAVING与WHERE非常类似，如果不指定GROUP BY，则大多数DBMS会同等对待它们。不过，你自己要能区分这一点。使用HAVING时应该结
合GROUP BY子句，而WHERE子句用于标准的行级过滤。

3. 分组和排序

GROUP BY和ORDER BY经常完成相同的工作，但它们非常不同
ORDER BY与GROUP BY

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

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

4. SELECT子句顺序

11. 使用子查询

1. 子查询

-- 语句1
SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';

-- 语句2
SELECT cust_id
FROM Orders
WHERE order_num IN (20007,20008);

-- 结合 变为子查询
-- 在SELECT语句中，子查询总是从内向外处理。
-- DBMS实际上执行了两个操作。
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');

警告:只能是单列
作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误

警告:子查询和性能
这里给出的代码有效，并且获得了所需的结果。但是，使用子查询并不总是执行这类数据检索的最有效方法

2. 作为计算字段使用子查询

SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

12. 联结表

1. 联结

1.1. 关系表

1.2. 建立连接

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

内联接

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

-- 连接多个表
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;

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

13. 创建高级联结

使用表别名

SELECT cust_name, cust_contact
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';

内联结和外联结

INNER JOIN、 OUTTER JOIN

警告:SQLite外联结
SQLite支持LEFT OUTER JOIN，但不支持RIGHT OUTER JOIN。幸好，如果你确实需要在SQLite中使用RIGHT OUTER JOIN，有一种更简单的办 法，这将在下面的提示中介绍。

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

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;

14. 组合查询

利用UNION操作符将多条SELECT语句组合成一个结果集。
使用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';

UNION规则

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

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

1. 包含或取消重复的行

使用UNION时，重复的行会被自动取消。
这是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';

对组合查询结果排序

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;

15. 插入数据

INSERT

1. 数据插入
   INSERT用来将行插入(或添加)到数据库表。插入有几种方式：

• 插入完整的行;
• 插入行的一部分;
• 插入某些查询的结果。

插入完整的行

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

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

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

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

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

插入部分行

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将产生错误消息，并且相应的行插入不成功。

插入检索出的数据

/**
 *使用INSERT SELECT从CustNew中将所有数据导入Customers。
 *SELECT语句从CustNew检索出要插入的值，而不是列出它们。
 *SELECT中列 出的每一列对应于Customers表名后所跟的每一列。
 *这条语句将插入多少行呢?这依赖于CustNew表有多少行。
 *如果这个表为空，则没有行被插 入(也不产生错误，因为操作仍然是合法的)。
 *如果这个表确实有数据，则所有数据将被插入到Customers。
**/
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中的列名
为简单起见，这个例子在INSERT和SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上，DBMS一点儿也不关心SELECT返 回的列名。它使用的是列的位置，因此SELECT中的第一列(不管其列名)将用来填充表列中指定的第一列，第二列将用来填充表列中指定的 第二列，如此等等。

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

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

2. 从一个表复制到另一个表

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

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

-- MariaDB、MySQL、Oracle、PostgreSQL和SQLite使用的语法稍有不同
CREATE TABLE CustCopy AS
SELECT * FROM Customers;

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

• 任何SELECT选项和子句都可以使用，包括WHERE和GROUP BY;
• 可利用联结从多个表插入数据;
• 不管从多少个表中检索数据，数据都只能插入到一个表中。

16. 更新和删除数据

UPDATE和DELETE

有两种使用UPDATE的方式:

• 更新表中的特定行;
• 更新表中的所有行。

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

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

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

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

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

-- 要删除某个列的值，可设置它为NULL(假如表定义允许NULL值)
UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

2. 删除数据

使用DELETE语句。有两种使用DELETE的方式:

• 从表中删除特定的行;
• 从表中删除所有行。

DELETE FROM Customers
WHERE cust_id = '1000000006';

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

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

17. 创建和操纵表

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

• 新表的名字，在关键字CREATE TABLE之后给出;
• 表列的名字和定义，用逗号分隔;
• 有的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,
prod_desc VARCHAR(1000) NULL
);

指定默认值

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

获得系统日期

2. 更新表

更新表定义，可以使用ALTER TABLE语句
以下是使用ALTERTABLE时需要考虑的事情。

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

-- 给Vendors表增加一个名为vend_phone的列，其数据类型为CHAR。
ALTER TABLE Vendors
ADD vend_phone CHAR(20);

ALTER TABLE Vendors
DROP COLUMN vend_phone;

说明:ALTER TABLE和SQLite
SQLite对使用ALTER TABLE执行的操作有所限制。最重要的一个限制是，它不支持使用ALTER TABLE定义主键和外键，这些必须在最初创建表 时指定。

3 删除表

DROP TABLE CustCopy;

4 重命名表

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

18. 使用视图

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

-- 假如可以把整个查询包装成一个名为ProductCustomers的虚拟表，
-- 则可以如下轻松地检索出相同的数据:
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

视图创建和使用的一些最常见的规则和限制。

• 与表一样，视图必须唯一命名(不能给视图取与别的视图或表相同的名字)。
• 对于可以创建的视图数目没有限制。
  创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予。
• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的DBMS中有所不同(嵌套视图可能会严 重降低查询的性能，因此在产品环境中使用之前，应该对其进行全面测试)。
• 许多DBMS禁止在视图查询中使用ORDER BY子句。
• 有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名(关于列别名的更多信息，请参阅第7课)。
• 视图不能索引，也不能有关联的触发器或默认值。
• 有些DBMS把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。详情请参阅具体的DBMS文档。
• 有些DBMS允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。例如有一个视图，只检索带有电子邮件地址的顾客。如 果更新某个顾客，删除他的电子邮件地址，将使该顾客不再属于视图。这是默认行为，而且是允许的，但有的DBMS可能会防止这种情况 发生。

1. 创建视图

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

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

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

-- 检索订购了产品RGAN01的顾客
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

19. 使用存储过程

什么要使用存储过程，如何使用存储过程，以及创建和使用存储过程的基本语法。
SqlLite 不支持，而接下来又主要用到 SqlLite 故不做笔记

20. 管理事务处理

介绍什么是事务处理，如何利用COMMIT和ROLLBACK语句管理事务处理。

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

关于事务处理需要知道的几个术语:

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

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

2. 控制事务处理

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

-- SQL Server
-- BEGIN TRANSACTION和COMMIT TRANSACTION语句之间的
-- SQL必须完全执行或者完全不执行。
BEGIN TRANSACTION
...
COMMIT TRANSACTION

ROLLBACK

-- SQL的ROLLBACK命令用来回退(撤销)SQL语句
-- 执行DELETE操作，然后用ROLLBACK语句撤销。
DELETE FROM Orders;
ROLLBACK;

COMMIT
一般的SQL语句都是针对数据库表直接执行和编写的。这就是所谓的隐式提交(implicit commit)，即提交(写或保存)操作是自动进行的。
在事务处理块中，提交不会隐式进行。

-- SQL Server
-- 在这个SQL Server例子中，从系统中完全删除订单12345。
-- 因为涉及更新两个数据库表Orders和OrderItems，
-- 所以使用事务处理块来保证订单 不被部分删除。
-- 最后的COMMIT语句仅在不出错时写出更改。
-- 如果第一条DELETE起作用，
-- 但第二条失败，则DELETE不会提交。
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION

使用保留点
使用简单的ROLLBACK和COMMIT语句，就可以写入或撤销整个事务。但是，只对简单的事务才能这样做，复杂的事务可能需要部分提交或回退。
在MariaDB、MySQL和Oracle中创建占位符，可使用SAVEPOINT语句:

-- 创建保留点
SAVEPOINT delete1;

-- SQL Server 创建保留点
-- SAVE TRANSACTION delete1;

--  SQL Server例子
BEGIN TRANSACTION
INSERT INTO Customers(cust_id, cust_name)
VALUES('1000000010', 'Toys Emporium');
SAVE TRANSACTION StartOrder;                      -- 创建保留点
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20100,'2001/12/1','1000000010');
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 1, 'BR01', 100, 5.49);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 2, 'BR03', 100, 10.99);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION

21. 使用游标

什么是游标，如何使用游标

1. 游标

结果集(result set)
SQL查询所检索出的结果。

有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标(cursor)是一个存储在DBMS服务器上的数据库查询，
它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

说明:具体DBMS的支持
Microsoft Access不支持游标，所以本课的内容不适用于Microsoft Access。
MySQL 5已经支持存储过程。因此，本课的内容不适用MySQL较早的版本。
SQLite支持的游标称为步骤(step)，下面讲述的基本概念适用于SQLite的步骤，但语法可能完全不同。

不同的DBMS支持不同的游标选项和特性。常见的一些选项和特性如下。

• 能够标记游标为只读，使数据能读取，但不能更新和删除。
• 能控制可以执行的定向操作(向前、向后、第一、最后、绝对位置、相对位置等)。
• 能标记某些列为可编辑的，某些列为不可编辑的。
• 规定范围，使游标对创建它的特定请求(如存储过程)或对所有请求可访问。
• 指示DBMS对检索出的数据(而不是指出表中活动数据)进行复制，使数据在游标打开和访问期间不变化。

2. 使用游标

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

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

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

-- DECLARE语句用来定义和命名游标，
-- 这里为CustCursor。
-- SELECT语句定义一个包含没有电子邮件地址(NULL值)的所有顾客的游标。
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL

-- 打开游标
OPEN CURSOR CustCursor

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

DECLARE TYPE CustCursor IS REF CURSOR
RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
OPEN CustCursor;
FETCH CustCursor INTO CustRecord;
CLOSE CustCursor;
END;

22. 高级SQL特性

约束、索引和触发器。

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

1. 主键

主键是一种特殊的约束，用来保证一列(或一组列)中的值是唯一的，而且永不改动。换句话说，表中的一列(或 多个列)的值唯一标识表中的每一行。这方便了直接或交互地处理表中的行。没有主键，要安全地UPDATE或DELETE特定行而不影响其他行会非 常困难。

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

• 任意两行的主键值都不相同。
• 每行都具有一个主键值(即列中不允许NULL值)。
• 包含主键值的列从不修改或更新。(大多数DBMS不允许这么做，但如果你使用的DBMS允许这样做，好吧，千万别!)
• 主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

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

给表的vend_id列定义添加关键字PRIMARY KEY，使其成为主键。
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

说明:SQLite中的键
SQLite不允许使用ALTER TABLE定义键，要求在初始的CREATE TABLE语句中定义它们。

外键

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

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

--ALTER TABLE语句中用CONSTRAINT语法来定义外键
ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

唯一约束

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

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

检查约束

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

• 检查最小或最大值。例如，防止0个物品的订单(即使0是合法的数)。
• 指定范围。例如，保证发货日期大于等于今天的日期，但不超过今天起一年后的日期。
• 只允许特定的值。例如，在性别字段中只允许M或F。

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

-- 检查名为gender的列只包含M或F，可编写如下的ALTER TABLE语句:
ADD CONSTRAINT CHECK (gender LIKE '[MF]')

2. 索引

索引用来排序数据以加快搜索和排序操作的速度。
数据库索引的作用也一样。主键数据总是排序的，这是DBMS的工作。因此，按主键检索特定行总是一种快速有效的操作。
解决方法是使用索引。可以在一个或多个列上定义索引，使DBMS保存其内容的一个排过序的列表。在定义了索引后，DBMS以使用书的索引 类似的方法使用它。DBMS搜索排过序的索引，找出匹配的位置，然后检索这些行

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

• 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。
• 索引数据可能要占用大量的存储空间。
• 并非所有数据都适合做索引。取值不多的数据(如州)不如具有更多可能值的数据(如姓或名)，能通过索引得到那么多的好处。
• 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
• 可以在索引中定义多个列(例如，州加上城市)。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

索引用CREATE INDEX语句创建(不同DBMS创建索引的语句变化很大)

--索引必须唯一命名。这里的索引名prod_name_ind在关键字CREATE INDEX之后定义。
-- ON用来指定被索引的表，而索引中包含的列(此例中仅有一 列)在表名后的圆括号中给出。
CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);

3. 触发器

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

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

• INSERT操作中的所有新数据;
• UPDATE操作中的所有新数据和旧数据;
• DELETE操作中删除的数据。

-- SQL Server
-- 对所有INSERT和UPDATE操作，将Customers表中的cust_state列转换为大写
CREATE TRIGGER customer_state
ON Customers
FOR INSERT, UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id   inserted.cust_id;

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

-----------------------------------------大部分内容来自《sql必知必会》