第一章 了解SQL

1.1 数据库基础

数据库

数据库（database）

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

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

表

表（table）

某种特定类型数据的结构化清单。

数据库中的每张表的名字都是唯一的。

模式（schema）

关于数据表和表的布局及特性的信息。

列和数据类型

列（column）

表中的一个字段。所有表都是由一个或多个列组成的。

数据类型（datatype）

允许什么类型的数据。每个表都有相应的数据类型，它限制（或允许）该列中存储的数据。

数据类型能帮助正确地分类数据，并起到优化磁盘的作用，因此创建表时必须特别关注数据类型。

行

行（row）

表中的一个记录

主键

主键（primary key）

一列（或几列），其值能够唯一标识表中每一行。

每个表应该都总是定义主键，以便于后续的数据操作和管理。

表中的任何列都可以作为主键，只要满足以下条件：

• 任意两行都不具有相同的主键值；
• 每一行都必须具有一个主键值（不允许空值NULL）；
• 主键列中的值不允许修改或更新；
• 主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）。

1.2 什么是SQL

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

SQL优点：

• 不是某个特定数据库厂商专有的语言，因此学习SQL几乎能与所有数据库打交道。
• 简单易学。
• 灵活，可以进行复杂和高级的数据库操作。

第二章 检索数据

2.1 SELECT语句

关键字（keyword）

作为SQL组成部分的保留字。关键字不能用作表或列的名字。

2.2 检索单个列

SELECT prod_name FROM Products;

多条SQL语句必须以分号（;）分隔。

SQL语句不区分大小写。

在 处理SQL语句时，所有空格都被忽略。

2.3 检索多个列

SELECT prod_id, prod_name, prod_price FROM Products;

2.4 检索所有列

SELECT * FROM Products;

注意：使用通配符

除非确实需要表中的每一列，否则最好别使用*通配符，检索不需要的列通常会降低检索速度和应用程序的性能。

2.5 检索不同的值

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

SELECT DISTINCT vend_id FROM Products;

注意：不能部分使用DISTINCT

DISTINCT关键字会作用于所有的列。

2.6 限制结果

不同的DBMS有不同的关键字，Mysql使用LIMIT关键字。

SELECT prod_name FROM Products LIMIT 5;

OFFSET关键字指示从第几行开始返回数据。

SELECT prod_name FROM Products LIMIT 5 OFFSET 4;

注意：数据检索的行是从第0行开始的。

缩写：逗号前面是OFFSET，后面是LIMIT。

SELECT prod_name FROM Products LIMIT 4,5;

2.7 使用注释

SELECT prod_name -- 这是一条行内注释
FROM Products;

# 这是一条注释

/*
SELECT ...
SELECT ...
这是多行注释
*/

第三章 排序检索数据

3.1 排序数据

检索的数据如果不排序，数据一般将以它在表中出现的顺序显示。

子句（clause）

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

使用ORDER BY子句进行排序。

注意：ORDER BY子句的位置

ORDER BY子句应该保证它是SELECT语句中最后一条子句。

3.2 按多个列排序

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;
-- 在上面多列排序中，仅在prod_price值相同时才会对prod_name进行排序，如果prod_price值全部唯一则不会对prod_name进行排序

3.3 按列位置排序

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;
-- 先按第二列也就是prod_price排序，再按第三列排序

3.4 指定排序方向

数据排序默认是升序排序。使用DESC进行降序排序。

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

-- 多个列排序
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC, prod_name;

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

第四章 过滤数据

4.1 使用WHERE子句

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）后面。

SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;

提示：SQL过滤和应用过滤

让SQL检索出超过实际所需的数据，然后客户端代码对数据循环提取出需要的行，这种行为极其不妥。让客户端处理数据库的工作会极大影响应用的性能，使所创建的应用不具备可伸缩性，还会造成带宽的浪费。

4.2 WHERE子句操作符

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

SELECT prod_name, prod_price FROM Products WHERE prod_price < 10;

SELECT vend_id, prod_name FROM Products WHERE vend_id != 'DLL01';

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

SELECT cust_name FROM Customers WHERE cust_email IS NULL;
SELECT cust_name FROM Customers WHERE cust_email IS NOT NULL;

当值与字符串比较需要引号''，与数值时不用引号。

NULL

在一个列不包含值时，称其包含空值NULL，无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。

第五章 高级数据过滤

5.1 组合WHERE子句

SQL允许给出多个WHERE子句，这些子句可以以AND子句或OR子句的方式使用。

AND指示DBMS只返回满足所有给定条件的行。OR指示DBMS检索匹配任一条件的行。

操作符（operator）

用来联结或改变WHERE子句中的子句的关键字，也称为逻辑操作符（logical operator）。

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

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

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

5.2 IN操作符

IN操作符用来指定条件范围。

使用IN操作符优点：

• 更清楚更直观
• 与其他AND和OR操作符组合使用IN时，求职顺序更容易管理。
• IN比一组OR执行得更快。
• IN可以包含其他SELECT语句，能够更动态地建立WHERE子句。

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

5.3 NOT操作符

NOT操作符否定其后所跟的任何条件。NOT在复杂的子句非常有用。

SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

第六章 用通配符进行过滤

6.1 LIKE操作符

LIKE操作符可以构造一个通配符搜索模式。通配符只能用于字符串。

• 百分号%表示任何字符出现任意次数，0个，1个或多个。
• 下划线_通配符匹配单个字符。
• 方括号[]通配符指定一个字符集（MySQL不支持）

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%'; -- 以Fish开头的匹配
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE'%bean bag%'; -- 匹配任何有bean bag的字符

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

6.2 使用通配符技巧

通配符搜索一般比其他搜索费时更长。使用技巧：

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
• 尽量不要把通配符置于开始处，这样搜索是最慢的。

第七章 创建计算字段

7.1 计算字段

计算字段并不实际存在于数据库表中，而是执行SELECT语句动态创建的。

7.2 拼接字段

使用||或+操作符将两个列拼接起来，在Mysql中使用Concat()函数。

TRIM()函数支持去掉空格，LTRIM()和RTRIM()分别去掉左边和右边的空格。

SELECT CONCAT(vend_name, ' (', vend_country, ')') FROM Vendors ORDER BY vend_name;
SELECT TRIM(CONCAT('     ',vend_name, ' (', vend_country, ')')) FROM Vendors ORDER BY vend_name;

使用别名

别名（alias）是一个字段或值的替换名。使用AS关键字。别名有时也称为导出列（derived column）。

任何客户端应用都可以按别名名称引用这个列，就像它是一个实际的表列一样。

SELECT CONCAT(vend_name, ' (', vend_country, ')') AS vend_title FROM Vendors ORDER BY vend_name;

说明：在很多DBMS中，AS关键字是可选的，建议最好使用它，这是一种最佳实践。

7.3 执行算术运算

操作符	说明
+	加
-	减
*	乘
/	除

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

第八章 使用函数处理数据

8.1 函数

在所有的DBMS中，只有少数的几个函数被所有DBMS支持，基本上每个DBMS都有自己特定的函数。

8.2 使用函数

大多数SQL支持以下类型的函数：

• 用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。
• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
• 用于处理日期和时间值并从这些值中提取特定成分的日期和时间函数。
• 用于生成美观好懂的输出内容的格式化函数（如用语言形式表达出日期，用货币符号和千分位表示金额）。
• 返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数。

文本处理函数

函数	说明
LEFT(str, len)	返回字符串左边的字符
LENGTH(str)	返回字符串长度
LOWER(str)	转小写
UPPER(str)	转大写
LTRIM(str)	去掉字符串左边的空格
RIGHT(str, len)	返回字符串右边的字符
SUBSTR()或SUBSTRING()	提取字符串的组成部分
SOUNDEX()	返回字符串的SOUNDEX值

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name; -- 转大写
SELECT vend_name, LENGTH(vend_name) AS vend_name_length FROM Vendors ORDER BY vend_name; -- 获取长度

日期和时间处理函数

日期和时间函数在每个DMBS可移植性比较差，不同的DBMS有不同的实现。

在MySQL中，获取日期比如获取年通过YEAR()函数，想要的月和日都有对应的函数，在其他DMBS，比如SQL Server中获取日期通过DATEPART()函数。

SELECT order_num FROM Orders WHERE YEAR(order_date) = 2020;
SELECT MONTH(order_date) as order_date_month FROM Orders;

数值处理函数

在主要DBMS的函数中，数值函数是最一致、最统一的函数。

函数	说明
ABS()	绝对值
COS()	余弦
EXP()	指数值
PI()	圆周率π
SIN()	正弦
SQRT()	平方根
TAN()	正切

第九章 汇总数据

9.1 聚集函数

聚集函数在所有DBMS得到一致的支持。

例子：

• 确定表中行数；
• 获取表中某些行的和；
• 找出表列的最大值、最小值、平均值。

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

AVG()函数只能用于单个列，它会忽略值为NULL的行。

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

• 使用COUNT(*)对表中行的数目进行计数，不会忽略NULL值。
• 使用COUNT(column)对特定列中具有值的行进行计数，会忽略NULL值。

MAX()、MIN()和SUM()函数都会忽略NULL的行。

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

SELECT COUNT(*) AS num_cust FROM Customers;
SELECT COUNT(cust_email) AS num_cust FROM Customers;

SELECT MAX(prod_price) AS max_price FROM Products;
SELECT MIN(prod_price) AS min_price FROM Products;

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

9.2 聚集不同值

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

• 对所有行执行计算，使用ALL参数（默认就是ALL，可以不填）。
• 只包含不同的值，使用DISTINCT参数。

不同的DBMS支持不同的参数，需要查阅相关的文档。

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.1 数据分组

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

10.2 创建分组

GROUP BY子句指示DBMS分组数据，对每个组而不是整个结果集进行聚集。

GROUP BY子句的一些规定：

• GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
• 如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算。
• GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（不能是聚集函数）。
• 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）。
• 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
• 如果分组列中包含有NULL值的行，则NULL将作为一个分组返回。
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;

10.3 过滤分组

WHERE过滤指定的行而不是分组，WHERE没有分组的概念。

HAVING子句类似于WHERE，WHERE过滤行，HAVING过滤分组。WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

-- 过滤分组的行数大于2的分组
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时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。

10.4 分组和排序

ORDER BY	GROUP 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;

10.5 SELECT子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否

第十一章 使用子查询

11.1 子查询

查询（query）

任何SELECT SQL语句都是查询。

子查询总是从内向外处理。子查询的SELECT语句只能查询单个列。由于性能的原因，不能嵌套太多的子查询。

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

SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01';

SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01');

SELECT cust_name, cust_contact FROM Customers WHERE cust_id IN (SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'));

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

当列名出现相同时，需使用完全限定列名。

这一章给出的代码并不是解决这种数据检索的最有效方法，可以参考后面的学习。

-- 返回客户表的信息，包括cust_name,cust_state,还有orders，orders表示客户的订单数
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 联结

相同的数据在同一张表出现多次不是好事，关系表的设计就是要把信息分解成多个表，一类数据一个表，各表通过某些共同的值互相关联。

关系数据库可以有效存储，方便地处理，它的伸缩性远比非关系数据库要好。

联结是一种机制，用来在一条SELECT语句中关联表。

12.2 创建联结

WHERE子句在联结中起到关键，它将两张表的每一行进行配对，如果没有使用WHERE子句，那么返回的数据将是两张表的乘积。

下面使用的联结称为等值联结（equijoin），它基于两个表之间的相等测试，这种联结也称为内联结（inner join）。

使用INNER JOIN需要与ON子句搭配。

-- Venndors表和Products表联结
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;
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';

不要联结不必要的表，联结的表越多，性能下降越厉害。

第十三章 创建高级联结

13.1 使用表别名

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

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

表别名只能在查询执行中使用，与列别名不一样，表别名不返回到客户端。

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

13.2 使用不同类型的联结

其他的联结：自联结（self-join）、自然联结（natural join）和外联结（outer join）。

自联结

-- 使用子查询
SELECT cust_id, cust_name, cust_contact FROM Customers WHERE cust_name = (SELECT cust_name FROM Customers WHERE cust_contact = 'Jim Jones');
-- 自联结，使用表别名
SELECT c1.cust_id, c1.cust_name, c1.cust_contact FROM Customers AS c1, Customers AS c2 WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';

自然联结

标准内联结会返回所有数据，相同的列出现多次。自然联结会排除多次出现，每一列只返回一次。

自然联结须选择唯一的列。其实，之前迄今为止建立的每个内联结都是自然联结。

外联结

联结包含了那些相关表中没有关联行的行，这种联结称为外联结。

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

检索两个表中的所有行并关联那些可以关联的行，这种联结称为全外联结（full outer join）。

-- 左外联结
SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
-- 右外联结
SELECT Customers.cust_id, Orders.order_num FROM Customers RIGHT 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;

-- 使用左外联结来包含所有顾客
SELECT Customers.cust_id, COUNT(ORders.order_num) AS num_ord FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id GROUP BY Customers.cust_id;

13.4 使用联结和联结条件

使用联结要点：

• 注意使用的联结类型。
• 熟悉确切的联结语法。
• 保证使用正确的联结条件。
• 应该总是提供联结条件，否则会得出笛卡尔积。
• 在联结前测试每个联结，方便故障排除。

第十四章 组合查询

14.1 组合查询

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

两种情况下需要用组合查询：

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

14.2 创建组合查询

UNION操作符用于将多条SELECT语句结果组成一个结果集。UNION几乎总是完成与多个WHERE条件相同的工作。

UNION默认情况下对结果集会进行去重。使用UNION ALL则不去重。

使用UNION的几条规则：

• UNION必须由两条或两条以上的SELECT语句组成。
• UNION中的每个查询必须包含相同的列、表达式或聚集函数。
• 类型不必完全相同，但必须是DMBS可以隐含转换的类型。

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必须位于最后一条SELECT语句之后
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.1 数据插入

INSERT用来将行插入到数据库表中。插入有几种方式：

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

使用INSERT语句需要有DMBS特定的安全权限。

插入完整的行

各列必须以它们在表定义中出现的次序填充。这种语法不安全，需要依赖特定列次序，不建议使用，不要使用没有明确给出列的INSERT语句，给出列能在表结构发生变化时仍能继续使用。

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

插入检索出的数据

将SELECT语句的结果插入表中。

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

INSERT通常只插入一行，要插入多行，必须执行多个INSERT语句。INSERT SELECT可以插入多行。

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

使用CREATE SELECT语句。注意事项：

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

CREATE TABLE CustCopy AS SELECT * FROM Customers;

第十六章 更新和删除数据

16.1 更新数据

UPDATE语句更新数据：

• 更新表中的特定行；
• 更新表中的所有行；

UPDATE语句需要安全权限。

-- 如果不指定WHERE子句，将会更新表中所有的行
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 = 1000000005;

如果要删除某个列的值，可以将其设置为NULL。

16.2 删除数据

DELETE语句删除数据：

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

-- 省略WHERE将删除所有数据
DELETE FROM Customers WHERE cust_id = 1000000006;

如果想删除所有行，可以使用TRUNCATE TABLE语句，它的执行速度更快。

16.3 更新和删除的指导原则

• 保证每个表都有主键，尽可能像WHERE子句那样使用它。
• 使用UPDATE或DELETE语句之前先用SELECT进行测试，确保过滤的数据是正确的。
• 使用强制实施引用完整性的数据库，这样DBMS将不允许删除其数据与其他表相关联的行。

第十七章 创建和操作表

17.1 创建表

CREATE TABLE语句创建表。

每个表要么是NULL列，要么是NOT NULL列。如果不指定NOT NULL，默认就是NULL。

使用DEFAULT指定默认值。默认值常用于日期和时间戳列。指定默认日期在MySQL中使用DEFAULT CURRENT_DATE()。

CREATE TABLE Products2
(
	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,
	prode_desc VARCHAR(1000) NULL -- 允许NULL值
);

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

CREATE TABLE OrderItems2
(
	order_num INTEGER NOT NULL,
	order_item INTEGER NOT NULL,
	prod_id CHAR(10) NOT NULL,
	quatity INTEGER NOT NULL DEFAULT 1, -- 默认值
	item_price DECIMAL(8,2) NOT NULL
);

17.2 更新表

ALTER TABLE语句更新表。注意事项：理想情况下，不要在表中包含数据时对其进行更新。应该在表设计过程考虑兼容性。

ALTER TABLE Vendors ADD vend_phone CHAR(20);

ALTER TABLE Vendors DROP COLUMN vend_phone;

使用ALTER TABLE需要极其小心，数据库的更改不能撤销，一旦删除就无法复原。

17.3 删除表

建议启用防止删除与其他表相关联的表功能，可以防止意外删除有用的表。

DROP TABLE Products2;

17.4 重命名表

每个DBMS对表重命名的支持有所不同，可以查阅相关的DBMS文档。

第十八章 使用视图

18.1 视图

视图是虚拟的表，视图只包含使用时动态检索数据的查询。作为视图，它不包含任何列或数据，包含的是一个查询。

视图应用：

• 重用SQL语句。
• 简化复杂的SQL操作。
• 使用表的一部分而不是整个表。
• 保护数据。可以授权用户访问表的特定部分的权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图限制：

• 视图必须唯一命名。
• 创建的视图数目没有限制。
• 创建视图，必须具有足够的访问权限。
• 视图可以嵌套，可以利用从其他视图中检索数据的查询来构造视图（嵌套视图可能会降低性能）。
• 禁止在视图查询使用ORDER BY子句。
• 视图不能索引，也不能有关联的触发器或默认值。
• 有些DBMS把视图作为只读的查询。

18.2 创建视图

视图极大地简化复杂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;

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

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

CREATE VIEW VendorLocations AS SELECT CONCAT(vend_name, ' (', vend_country, ')') AS vend_title FROM Vendors;

SELECT * FROM VendorLocations;

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

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

SELECT * FROM CustomerEmailList;

使用视图与计算字段

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;

第十九章 使用存储过程

19.1 存储过程

存储过程就是为以后使用而保存的一条或多条SQL语句，可将其视为批文件。

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

19.4 创建存储过程

Oracle版

CREATE PROCEDURE MailingListCount(
ListCount OUT INTEGER
)
IS v_rows INTEGER
BEGIN
	SELECT COUNT(*) INFO v_rows
	FROM Customers
	WHERE NOT cust_email IS NULL;
	ListCount := v_rows;
END;

具体的存储过程请查阅相关的DBMS文档。

第二十章 管理事务处理

20.1 事务处理

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

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

事务可以回退INSERT、UPDATE和DELETE语句。

20.2 控制事务处理

MySQL中启用事务：

START TRANSACTION
...
COMMIT TRANSACTION

使用ROLLBACK命令回退SQL语句。

DELETE FROM OrdersCopy;
ROLLBACK;

一般的SQL语句都是针对数据库表直接执行和编写的，这就是所谓的隐式提交（implicit commit），即提交（或保存）是自动进行的。

在事务处理块中，提交不会隐式进行。使用COMMIT进行提交。

-- SQL Server中使用COMMIT
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION;

事务支持回退部分，在事务处理块中的合适位置放置占位符，这些占位符称为保留点。

SAVEPOINT delete1; -- 放置保留点
ROLLBACK TO delete1; -- 回退到保留点

第二十一章 使用游标

21.1 游标

SQL检索操作返回一组称为结果集的行，简单的使用SELECT语句，没有办法得到第一行、下一行或前10行。需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。

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

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

21.2 使用游标

FETCH ... INTO ...使用游标

-- 创建游标
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL;

-- 打开游标
OPEN CURSOR CustCursor;

-- 使用游标
FETCH CustCursor INTO ...;

-- 关闭游标
CLOSE CustCursor;

第二十二章 高级SQL特性

22.1 约束

约束（constraint），管理如何插入或处理数据库数据的规则。DBMS通过在数据库表上施加约束来实施引用完整性。

主键

主键是一种特殊的约束，用来保证一列中的值是唯一的。

CREATE TABLE Products2
(
	prod_id CHAR(10) NOT NULL PRIMARY KEY,
	vend_id CHAR(10) NOT NULL,
	prod_name CHAR(254) NOT NULL,
	prod_price DECIMAL(8,2) NOT NULL,
	prode_desc VARCHAR(1000) NULL -- 允许NULL值
);

外键

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

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  Orders ADD CONSTRAINT FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);

唯一约束

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

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

使用UNIQUE约束定义。

CREATE TABLE OrderItems2
(
	order_num INTEGER NOT NULL,
	order_item INTEGER NOT NULL,
	prod_id CHAR(10) NOT NULL,
	quatity INTEGER NOT NULL CHECK (quatity > 0), -- 必须大于0
	item_price DECIMAL(8,2) NOT NULL
);

-- 修改表方式
ALTER TABLE Customers ADD CONSTRAINT CHECK ( gender LIKE '[MF]');

自定义数据类型：有些DBMS支持自定义数据类型，只需施加约束一次，后面都可以通过这个类型直接复用。

22.2 索引

索引用来排序数据以加快搜索和排序操作的速度。可以在一个或多个列上定义索引，使DBMS保存其内容是一个排过序的列表。

• 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。
• 索引数据可能要占用大量的存储空间。
• 并非所有数据都适合做索引。
• 索引用于数据过滤和数据排序。
• 可以在索引中定义多个列。

CREATE INDEX语句创建索引。

CREATE INDEX prod_name_id ON Products (prod_name);

22.3 触发器

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

与存储过程不一样，触发器与单个的表相关联。触发器内的代码具有以下数据的访问权：

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

触发器可在特定操作执行之前或之后执行。

触发器的用途：

• 保证数据一致。
• 基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。
• 进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。
• 计算计算列的值或更新时间戳。

-- SQL Server版本

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;

约束比触发器更快

尽量使用约束。

22.4 数据库安全

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

一般需要保护的操作有：

• 对数据库管理功能的访问；
• 对特定数据库或表的访问；
• 访问的类型；
• 仅通过视图或存储过程对表进行访问；
• 创建多层次的安全措施，从而允许多种基于登录的访问和控制；
• 限制管理用户账号的能力。

使用GRANT和REVOKE语句来管理。

附录B SQL语句的语法

ALTER TABLE

修改表结构。

ALTER TABLE tablename
(
	ADD|DROP column datatype [NULL|NOT NULL] [CONSTRAINTS],
  ADD|DROP column datatype [NULL|NOT NULL] [CONSTRAINTS],
);

COMMIT

提交事务。

COMMIT [TRANSACTION];

CREATE INDEX

创建索引。

CREATE INDEX indexname
ON tablename (column, ...);

CREATE PROCEDURE

创建存储过程。

CREATE PROCEDURE procedurename [parameters] [options]
AS
SQL statement;

CREATE TABLE

创建表。

CREATE TABLE tablename
(
	column datatype [NULL|NOT NULL] [CONSTRAINTS],
	column datatype [NULL|NOT NULL] [CONSTRAINTS],
  ...
)

CREATE VIEW

创建视图。

CREATE VIEW viewname AS
SELECT columns, ...
FROM tables, ...
[WHERE ...]
[GROUP BY ...]
[HAVING ...];

DELETE

删除一行或多行。

DELETE FROM tablename
[WHERE ...];

DROP

删除数据库对象（表、视图、索引等）。

DROP INDEX|PROCEDURE|TABLE|VIEW indexname|procedurename|tablename|viewname;

INSERT

插入一行数据。

INSERT INTO tablename [(columns, ...)]
VALUES(values, ...);

INSERT SELECT

将SELECT结果插入到一个表中。

INSERT INTO tablename [(columns, ...)]
SELECT columns, ... FROM tablename, ...
[WHERE ...];

ROLLBACK

撤销一个事务。

ROLLBACK [TO savepointname];

ROLLBACK TRANSACTION;

SELECT

查询。

SELECT columnname, ...
FROM tablename, ...
[WHERE ...]
[UNION ...]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...];

UPDATE

更新表中的一行或多行。

UPDATE tablename
SET columnname = value, ...
[WHERE ...];

附录C SQL数据类型

数据类型用于以下目的：

• 数据类型允许限制可存储在列中的数据。
• 数据类型允许在内部更有效地存储数据。
• 数据类型允许变换排序顺序。

在设计表时，应该特别重视所用的数据 类型。使用错误的数据类型可能会严重影响应用程序的功能和性能。

不同的DBMS的数据类型有可能不同。

字符串数据类型

两种基本的字符串类型，定长字符串和变长字符串。

数据类型	说明
CHAR	1~255个字符的定长字符串。它的长度必须在创建时规定
NCHAR	CHAR的特殊类型，用来支持多字节或Unicode字符
NVARCHAR	TEXT的特殊形式，用来支持多字节或Unicode字符
TEXT（也称为LOGN、MEMO或VARCHAR）	变长文本

定长字符会根据接受固定的长度存储，及时没有所存储的字符串没有达到该长度，则会通过空格填充。

变长字符串只有指定的数据会得以保存。

DBMS处理定长列远比处理变长列性能要快得多，而且变长列不允许被索引。

数值类型注意

如果数值是计算（求和、平均等）中使用的数值，则应该存储在数值数据类型中；如果作为字符串（可能只包含数字）使用，则应该保存在字符串数据类型中。

数值数据类型

数据类型	说明
BIT	单个二进制位值，或者为0或者为1，主要用于开/关标志
DECIMAL（或NUMERIC）	定点或精度可变的浮点值
FLOAT（或NUMBER）	浮点值
INT或（INTEGER）	4字节整数值，支持-2147483648~2147483648的数
REAL	4字节浮点值
SMALLINT	2字节整数值，支持-32768~32768的数
TINYINT	1字节整数值，支持0~255的数

货币数据类型

多数DBMS支持一种用来存储货币值的特殊数值数据类型。一般记为MONEY或CURRENCT，这些数据类型有特定取值范围的DECIMAL数据类型，更适合存储货币值。

日期和时间数据类型

数据类型	说明
DATE	日期值
DATETIME（或TIMESTAMP）	日期时间值
SMALLDATETIME	日期时间值，精确到分（无秒或毫秒）
TIME	时间值

二进制数据类型

二进制数据类型可包含任何数据，甚至可包含二进制信息，如图像、多媒体、字处理文档等。

数据类型	说明
BINARY	定长二进制数据（最大长度从255 B到8000 B，有赖于具体的实现）
LOGN RAW	变长二进制数据，最长2GB
RAW	定长二进制数据，最大255B
VARBINARY	变长二进制数据