《SQL必知必会》笔记

发表于 更新于 分类于
《SQL必知必会》笔记+代码片段

笔记📒

  • SQL关键字使用大写，对列名和表名使用小写。
  • 将SQL语句分成多行更容易阅读和调试。

代码片段

检索结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/* 检索单个列 */
SELECT prod_name 
FROM Products;

/* 检索多个列 */
SELECT prod_name, prod_name, prod_price
FROM Products;

/* 检索所有列 */
/* 通配符*会降低检索性能，但能检索出名字未知的列 */
SELECT *
FROM Products;

/* 检索不同的值 */
/* DISTINCT 作用于所有列 */
SELECT DISTINCT ven_id
FROM Products;

/* 限制结果 */
SELECT prod_name
FROM Products
LIMIT 5; /* LIMIT 5 OFFSET 3 表示从哪儿开始以及检索的行数，可以简化为LIMIT 3,5 */

排序检索数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/* 排序数据 */
SELECT prod_name
FROM Products
ORDER BY prod_name;  /* ORDER BY 需要保证是 SELECT 语句最后一条句子 */

/* 按多个列排序 */
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

/* 按列位置排序 */
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3; /* 2, 3指的是SELECT清单中的第二个和第三个列名 */

/* 指定排序方向 */
SELECT prod_name
FROM Products
ORDER BY prod_name DESC, prod_name;/*默认升序，降序指定DESC，多个列降序需分别指定DESC*/

过滤数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/* 使用WHERE子句 */
SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49; /* ORDER BY 应位于 WHERE 之后 */

/* WHERE操作符 */
=  -- 等于
<> -- 不等于
!= -- 不等于
<  -- 小于
<= -- 小于等于
!< -- 不小于
>  -- 大于
>= -- 大于等于
!> -- 不大于
BETWEEN -- 在指定两个值之间
IS NULL -- 为NULL值

/* 空值检查 */
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

/* 范围值检查 */
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

高级数据过滤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/* 组合WHERE子句 - AND 操作符 */
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

/* 组合WHERE子句 - OR 操作符 */
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

/* 组合WHERE子句 - 括号标明求值顺序 */
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
			AND prod_price >= 10;

/* IN 操作符 */
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ('DLL01', 'BSR01') -- 跟 OR 一个意思
ORDER BY prod_name

/* NOT 操作符 */
SELECT prod_name, prod_price
FROM Products
WHERE NOT vend_id = 'DLL01' -- 跟 <> / != 一个意思
ORDER BY prod_name

用通配符进行过滤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/* 百分号% 通配符 */
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%'; -- %表示任何字符出现任意次数，能匹配0次数，但不能匹配NULL
-- WHERE prod_name LIKE '%bean bag%';
-- WHERE prod_name LIKE 'F%y';

/* 下划线_ 通配符 */
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear'; -- _只匹配单个字符而不是多个字符

/* 方括号[] 通配符 */
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'  -- 指定字符集，匹配其中一个字符
-- WHERE cust_contact LIKE '[^JM]%' -- ^表示否定
ORDER BY cust_contact;

计算字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/* 拼接字段 */
SELECT Concat(vend_name, ' (', vend_country, ')') -- 使用 +/||/Concat 看DBMS
FROM Venders
ORDER BY vend_name;

/* RTRIM函数去掉右边空格 */
SELECT Concat(RTRIM(vend_name), ' (', RTRIM(vend_country), ')') -- 使用 +/||/Concat 看DBMS
FROM Venders
ORDER BY vend_name;

/* 使用别名 */
SELECT Concat(RTRIM(vend_name), ' (', RTRIM(vend_country), ')') AS vend_title
FROM Venders
ORDER BY vend_name;

/* 执行算术计算 */
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;

使用函数处理数据

常用函数类别：

  • 用于处理文本字符串(如删除或填充值，转换值为大写或小写)的文本函数
  • 用于在数值数据上进行算术操作(如返回绝对值，进行代数运算)的数值函数
  • 用于处理日期和时间值并从这些值中提取特定成分(如返回两个日期之差，检查日期有效性)的日期和时间函数
  • 返回 DBMS 正使用的特殊信息(如返回用户登录信息)的系统函数
1
2
3
4
5
6
7
8
9
10
11
12
/* 文本处理函数 */
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase 
FROM Vendors
ORDER BY vend_name;

/* 日期和时间处理函数 */
SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2012;

/* 数值处理函数 */
ABS / COS / EXP / PI / SIN / SQRT / TAN

汇总数据

聚集函数

  • AVG
  • COUNT
  • MAX
  • MIN
  • SUM
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
/* AVG函数 */
SELECT AVG(prod_price) AS avg_price
FROM Products;

SELECT AVG(prod_price) AS avg_price 
FROM Products
WHERE vend_id = 'DLL01'; -- AVG()函数会忽略列值为NULL的行

/* COUNT函数 */
SELECT COUNT(*) AS num_cust
FROM Customers;

SELECT COUNT(cust_email) AS num_cust -- 对cust_email列中有值的行进行计数
FROM Customers;

/* MAX函数 */
SELECT MAX(prod_price) AS max_price
FROM Products;

/* MIN函数 */
SELECT MIN(prod_price) AS min_price
FROM Products;

/* SUM函数 */
SELECT SUM(quantity) AS items_ordered 
FROM OrderItems
WHERE order_num = 20005;

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

/* 聚集不同值 */
SELECT AVG(DISTINCT prod_price) AS avg_price -- DISTINCT不能用于COUNT(*)
FROM Products
WHERE vend_id = 'DLL01';

/* 组合聚集函数 */
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;

分组数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
/* 创建分组 */
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
-- GROUP BY 子句可以包含任意数目的列，因为可以对分组进行嵌套，更细致地进行数据分组。
-- GROUP BU 必须出现在WHERE子句之后，ORDER BY子句之前。

/* 过滤分组 */
SELECT cust_id, COUNT(*) AS orders 
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
-- HAVING支持所有WHERE操作符
-- WHERE过滤行，HAVING过滤分组

SELECT vend_id, COUNT(*) AS num_prods 
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
-- 使用HAVING时应结合GROUP BY子句，WHERE子句则用于标准的行级过滤。

/* 分组和排序 */
SELECT order_num, COUNT(*) AS items 
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3;
-- 使用GROUP BY时，不要忘了使用ORDER BY，这是保证数据正确排序的唯一方法。

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

SELECT 子句顺序

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

使用子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/* 利用子查询进行过滤 */
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
										FROM OrderItems
										WHERE prod_id = 'RGAN01');
-- 包含子查询的SELECT语句难以阅读和调试，
-- 所以把子查询分解为多行并进行适当的缩进，能极大地简化子查询的使用。

FROM Customers
WHERE cust_id IN (SELECT cust_id
									FROM Orders
									WHERE order_num IN (SELECT order_num
																			FROM OrderItems
																			WHERE prod_id = 'RGAN01'));
/* 作为计算字段使用子查询 */
SELECT cust_name,
       cust_state,
			(SELECT COUNT(*)
				FROM Orders
				WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

联结表

关系表设计需要把信息分解成多个表，一类数据一个表，各表通过某些共同的值互相关联，能够更有效地存储，更方便地处理，而且可伸缩性更好。但是需要使用联结来进行跨多个表的查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
/* 创建联结 */
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
WHERE cust_id IN (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, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
	AND OrderItems.order_num = Orders.order_num 
	AND prod_id = 'RGAN01';

创建高级联结

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
/* 使用表别名 */、
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';

/* 自联结 */
-- 使用子查询
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';
-- 自联结用来代替相同表内的子查询语句，性能可能会更快。

/* 自然联结 */
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';

/* 外联结 */
SELECT Customers.cust_id, Orders.order_num 
-- 检索所有顾客及其订单
FROM Customers INNER JOIN Orders  
-- 检索包括没有订单顾客在内的所有顾客，
FROM Customers LEFT OUTER JOIN Orders  -- 左边表中选中所有行
FROM Customers RIGHT OUTER JOIN Orders  -- 右边表中选中所有行
	ON Customers.cust_id = Orders.cust_id;

/* 使用带聚集函数的联结 */
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;

组合查询

利用UNION操作符将多条SELECT语句组合成一个结果集。

  • UINION分隔SELECT语句
  • UNION中的每个查询必须包含相同的列、表达式或聚集函数
  • 列数据类型必须兼容，不必完全相同。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
/* 使用UNION */
-- 单条语句
SELECT cust_name, cust_contact, cust_email 
FROM Customers
WHERE cust_state IN ('IL','IN','MI');

SELECT cust_name, cust_contact, cust_email 
FROM Customers
WHERE cust_name = 'Fun4All';

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

-- 等价
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
	OR cust_name = 'Fun4All';

/* 使用UNION ALL */
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 ALL 不取消重复的行

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
/* 插入完整行 */
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);
-- 可以顺序不同

/* 插入部分行 */
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');
-- 省略的列，必须要么允许为NULL，要么有默认值。

/* 插入检索出的数据 */
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;

更新和删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/* 更新数据 */
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';

UPDATE Customers
SET cust_email = NULL  -- 要删除某个列的值，可设置它为NULL。
WHERE cust_id = '1000000005';

/* 删除数据 */
DELETE FROM Customers
WHERE cust_id = '1000000006';

更新和删除数据很容易，所以应当很仔细地使用它。更新和删除的指导原则如下：

  • 除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE 子句 的 UPDATE 或 DELETE 语句。
  • 保证每个表都有主键，尽可能 像 WHERE 子句那样使用它(可以指定各主键、多个值或值的范围)。
  • 在 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进 行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不正确。
  • 使用强制实施引用完整性的数据库，这样 DBMS 将不允许删除其数据与其他表相关联的行。

创建和操纵表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
/* 表创建基础 */
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
);

/* 使用NULL值 */
-- 每个表列要么是NULL列，要么是NOT NULL列
CREATE TABLE Orders
(
    order_num  INTEGER   NOT NULL,
    order_date DATETIME  NULL,
    cust_id    CHAR(10)  , -- 表示可以NULL值
);

/* 指定默认值 */
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, 
}

/* 更新表 -- 增加列 */
ALTER TABLE Vendors
ADD vend_phone CHAR(20);

/* 更新表 -- 删除列 */
ALTER TABLE Vendors
DROP COLUMN vend_phone;

/* 删除表 */
DROP TABLE CustCopy;

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

  1. 用新的列布局创建一个新表;
  2. 使用 INSERT SELECT 语句从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段；
  3. 检验包含所需数据的新表;
  4. 重命名旧表(如果确定，可以删除它);
  5. 用旧表原来的名字重命名新表;
  6. 根据需要，重新创建触发器、存储过程、索引和外键。

使用视图

视图：

  • 视图为虚拟的表。
  • 它们包含的不是数据而是根据需要检索数据的查询。
  • 视图提供了一种封装 SELECT 语句的层次，可用来简化数据处理，重新格式化或保护基础数据。

视图常见应用：

  • 重用 SQL 语句。
  • 简化复杂的 SQL 操作。在编写查询后，可以方便地重用它而不必知道 其基本查询细节。
  • 使用表的一部分而不是整个表。
  • 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
/* 举例说明 */
-- 联结方式
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';

-- 视图方式
SELECT cust_name, cust_contact 
FROM ProductCustomers   -- 视图，代表的不是数据，而是一个查询。
WHERE prod_id = 'RGAN01';

/* 创建视图 - 利用视图简化复杂的联结 */
-- 创建
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';

/* 创建视图 - 用视图重新格式化检索出的数据 */
-- SELECT时格式化
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;

/* 创建视图 - 用视图过滤不想要的数据 */
CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

/* 创建视图 - 使用视图与计算字段 */
-- 原始查询
SELECT prod_id,
       quantity,
			 item_price,
       quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
-- 创建视图
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;

使用存储过程

存储过程相对会比较复杂些，书上也没有详细的介绍。

我理解的存储过程，就是在一个业务场景中，单个的SQL语句无法完成任务，需要对业务涉及到的多条SQL语句进行封装，抽出一个函数形式的API。

好处就是封装之后执行相对简单了，而且开发人员使用同样的步骤，保证了数据一致，减少错误发生的概率。

管理事务处理

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

事务处理用来管理：INSERT语句、UPDATE语句、DELETE语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
/* 事务开始和结束 */
-- SQL Server
BEGIN TRANSACTION 
...
COMMIT TRANSACTION
-- MySQL
START TRANSACTION
...

/* ROLLBACK */
-- 用来回退(撤销)SQL语句
DELETE FROM Orders;
ROLLBACK; 

/* COMMIT */
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345 
DELETE Orders WHERE order_num = 12345 
COMMIT TRANSACTION  -- 如果其中一条失效，则COMMIT不会提交，保证不会部分删除

/* 保留点 */
-- 使用简单的 ROLLBACK 和 COMMIT 语句，就可以写入或撤销整个事务。
-- 复杂的事务可能需要部分提交或回退。

-- SQL Server
SAVE TRANSACTION delete1;
...
ROLLBACK TRANSACTION delete1;
-- MySQL
SAVEPOINT delete1;
...
ROLLBACK TO 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
-- 保留点越多越好，越多意味着能够越灵活地进行回退。

使用游标

游标就是一种处理结果集的机制，可以定位到结果集的某一行进行处理。用处主要包括：

  • 定位到结果集中的某一行；
  • 对当前位置的数据进行读写；
  • 可以对结果集中的数据单独操作，而不是整行执行相同的操作；
  • 是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
/* 创建游标 */
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL

/* 使用游标 */
OPEN CURSOR CustCursor

-- 检索第一行
DECLARE TYPE CustCursor IS REF CURSOR
    RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
    OPEN CustCursor;
    FETCH CustCursor INTO CustRecord;
    CLOSE CustCursor;
END;

-- 循环处理检索出来的每一行
DECLARE TYPE CustCursor IS REF CURSOR
    RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
		OPEN CustCursor;
		LOOP
		FETCH CustCursor INTO CustRecord; EXIT WHEN CustCursor%NOTFOUND;
		...
		END LOOP;
		CLOSE CustCursor;
END;

/* 关闭游标 */
CLOSE CustCursor

高级SQL特性

概念性的内容，不抄书了，可以看书或者看博客。主要包括：

  • 约束
    • 主键：
      主键是一种特殊的约束，用来保证一列(或一组列)中的值是唯一的，而且永不改动。
    • 外键：
      外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。
    • 唯一约束：
      唯一约束用来保证一列(或一组列)中的数据是唯一的。
    • 检查约束：
      检查约束用来保证一列(或一组列)中的数据满足一组指定的条件。
  • 索引：
    索引用来排序数据以加快搜索和排序操作的速度。
  • 触发器：
    触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的 INSERT、UPDATE 和 DELETE 操作(或组合)相关联。
  • 数据库安全：
    利用管理机制授予或限制对数据的访问。