笔记📒

  • 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 操作(或组合)相关联。
  • 数据库安全:
    利用管理机制授予或限制对数据的访问。