SQL 必知必会 - 数据更新/存储/事务

---

SQL 必知必会 - 全书目录

1. 第 01 - 10 章：SQL 必知必会 - 单表查询/过滤/汇总
2. 第 11 - 24 章：SQL 必知必会 - 多表查询/联结/组合
3. 第 15 - 24 章：SQL 必知必会 - 数据更新/存储/事务

插入数据 - INSERT

• INSERT 用来将行插入（或添加）到数据库表
  • 在某些 SQL 实现中，跟在 INSERT 之后的 INTO 关键字是可选的
• 插入完整的行
  • 不要使用没有明确给出列的 INSERT 语句
  • VALUES 的数目都必须正确
• 插入行的一部分
  • 可以在 INSERT 操作中省略某些列
    • 该列定义为允许 NULL 值（无值或空值）
    • 在表定义中给出默认值。这表示如果不给出值，将使用默认值
• 插入某些查询的结果：INSERT SELECT
  • INSERT SELECT 中 SELECT 语句可以包含 WHERE 子句，以过滤插入的数据
• INSERT 通常只插入一行。要插入多行，必须执行多个 INSERT 语句。INSERT SELECT 是个例外，它可以用一条 INSERT 插入多行，不管 SELECT 语句返回多少行，都将被 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);

-- 插入部分行
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 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;

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

-- 创建一个名为 CustCopy 的新表, 并把 Customers 表的整个内容复制到新表中
SELECT *
INTO CustCopy
FROM Customers;

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

更新和删除数据 - UPDATE/DELETE

更新数据

• 更新数据：UPDATE 语句，不要省略 WHERE 子句，否则将会更新所有行
• 基本的 UPDATE 语句
  • 要更新的表
  • 列名和它们的新值
  • 确定要更新哪些行的过滤条件
• UPDATE 语句中可以使用子查询，使得能用 SELECT 语句检索出的数据更新列数据
• 要删除某个列的值，可设置它为 NULL（假如表定义允许 NULL 值）

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

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

删除数据

• 删除数据：DELETE 语句，不要省略 WHERE 子句，否则将会删除所有行
• DELETE 不需要列名或通配符
• DELETE 删除整行而不是删除列
• DELETE 不删除表本身

-- 从 Customers 表中删除一行
DELETE FROM Customers
WHERE cust_id = '1000000006';

• 更新和删除的指导原则
  • 除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句
  • 保证每个表都有主键（如果忘记这个内容，请参阅第 12 课），尽可能像 WHERE 子句那样使用它（可以指定各主键、多个值或值的范围）
  • 在 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进行测试，保证它过滤的是正确的记录，以防编写的WHERE 子句不正确。
  • 使用强制实施引用完整性的数据库（关于这个内容，请参阅第 12 课），这样 DBMS 将不允许删除其数据与其他表相关联的行。
  • 有的 DBMS 允许数据库管理员施加约束，防止执行不带 WHERE 子句的 UPDATE 或 DELETE 语句。如果所采用的 DBMS 支持这个特性，应该使用它。

创建和操纵表 - CREATE/ALTER

创建表

• SQL 不仅用于表数据操纵，还用来执行数据库和表的所有操作，包括表本身的创建和处理。
• 用程序创建表，可以使用 SQL 的 CREATE TABLE 语句
• 在不同的 SQL 实现中，CREATE TABLE 语句的语法可能有所不同
• 在创建新的表时，指定的表名必须不存在，否则会出错。
• 利用CREATE TABLE 创建表，必须给出下列信息：
  • 新表的名字，在关键字CREATE TABLE 之后给出；
  • 表列的名字和定义，用逗号分隔；
  • 有的DBMS 还要求指定表的位置。
• 每个表列要么是 NULL 列，要么是 NOT NULL 列，这种状态在创建时由表的定义规定。NULL 为默认设置
• SQL 允许指定默认值，在插入行时如果不给出值，DBMS 将自动采用默认值。
• 默认值在 CREATE TABLE 语句的列定义中用关键字 DEFAULT 指定。
• 默认值经常用于日期或时间戳列。
  • 通过指定引用系统日期的函数或变量， 将系统日期用作默认日期

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	DEFAULT 1,
	prod_desc 	VARCHAR(1000) 	NULL
);

更新、删除表

• 更新表定义，可以使用 ALTER TABLE 语句。
  • 理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动。
  • 所有的DBMS 都允许给现有的表增加列，不过对所增加列的数据类型（以及 NULL 和 DEFAULT 的使用）有所限制。
  • 许多DBMS 不允许删除或更改表中的列。
  • 多数DBMS 允许重新命名表中的列。
  • 许多DBMS 限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。
• 使用 ALTER TABLE 更改表结构，必须给出下面的信息：
  • 在 ALTER TABLE 之后给出要更改的表名（该表必须存在，否则将
    出错）；
  • 列出要做哪些更改。
• 复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：
  1. 用新的列布局创建一个新表；
  2. 使用 INSERT SELECT 语句（关于这条语句的详细介绍，请参阅第 15
  课）从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段；
  3. 检验包含所需数据的新表；
  4. 重命名旧表（如果确定，可以删除它）；
  5. 用旧表原来的名字重命名新表；
  6. 根据需要，重新创建触发器、存储过程、索引和外键。
• 删除表：使用 DROP TABLE 语句
• 重命名表：
  • DB2、MariaDB、MySQL、Oracle 和 PostgreSQL 用户使用 RENAME
    语句
  • SQL Server 用户使用 sp_rename 存储过程
  • SQLite 用户使用 ALTER TABLE 语句

-- 给 Vendors 表增加一个名为 vend_phone 的列
ALTER TABLE Vendors
ADD vend_phone CHAR(20);

-- 删除列
ALTER TABLE Vendors
DROP COLUMN vend_phone;

-- 删除表
DROP TABLE CustCopy;

使用视图 - CREATE VIEW

• 视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
• 视图的一些常见应用：
  • 重用 SQL 语句，简化复杂的 SQL 操作
  • 使用表的一部分而不是整个表
  • 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限
  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
• 视图创建和使用的一些最常见的规则和限制
  • 与表一样，视图必须唯一命名
  • 创建视图，必须具有足够的访问权限
  • 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的 DBMS 中有所不同
  • 许多 DBMS 禁止在视图查询中使用 ORDER BY 子句
  • 有些 DBMS 要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名
  • 视图不能索引，也不能有关联的触发器或默认值
  • 有些 DBMS 把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表
  • 有些 DBMS 允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新
• 创建视图：CREATE VIEW 语句
• 删除视图：可以使用 DROP 语句，其语法为 DROP VIEW viewname

-- 检索订购了某种产品的顾客
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';  

-- 创建视图
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 的虚拟表
-- ProductCustomers 是一个视图
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

-- 重新格式化检索出的数据
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;

-- 使用视图与计算字段
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
       prod_id,
       quantity,
	   item_price,
       quantity*item_price AS expanded_price
FROM OrderItems;

使用存储过程 - EXECUTE

• 存储过程就是为以后使用而保存的一条或多条 SQL 语句。可将其视为批文件，虽然它们的作用不仅限于批处理
• 为什么要使用存储过程：简单、安全、高性能
  • 通过把处理封装在一个易用的单元中，可以简化复杂的操作
  • 由于不要求反复建立一系列处理步骤，因而保证了数据的一致性
  • 简化对变动的管理
  • 因为存储过程通常以编译过的形式存储，所以 DBMS 处理命令所需的工作量少，提高了性能
  • 存在一些只能用在单个请求中的 SQL 元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码
• 执行存储过程：EXECUTE
• 创建存储过程：CREATE

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

-- 对邮件发送清单中具有邮件地址的顾客进行计数
-- Oracle 版本
CREATE PROCEDURE MailingListCount (
	ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
	SELECT COUNT(*) INTO v_rows
	FROM Customers
	WHERE NOT cust_email IS NULL;
	ListCount := v_rows;
END;

-- 调用 Oracle 例子
var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;

管理事务处理 - COMMIT/ROLLBACK

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

• 可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）

• 如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态

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

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

• 事务处理用来管理 INSERT、UPDATE 和 DELETE 语句

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

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

    • SQL Server：BEGIN TRANSACTION COMMIT TRANSACTION

• 多数实现没有明确标识事
  务处理在何处结束。事务一直存在，直到被中断

  • MariaDB 和 MySQL：START TRANSACTION
  • Oracle：SET TRANSACTION
    • PostgreSQL：BEGIN
  • 通常，COMMIT 用于
    保存更改，ROLLBACK 用于撤销

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

  • MariaDB、MySQL 和 Oracle：SAVEPOINT

-- 执行 DELETE 操作，然后用 ROLLBACK 语句撤销
DELETE FROM Orders;
ROLLBACK;

-- 使用 COMMIT 语句进行明确的提交
-- SQL Server
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION
-- Oracle
SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345;
DELETE Orders WHERE order_num = 12345;
COMMIT;

-- 创建保留点
-- MariaDB、MySQL 和 Oracle
SAVEPOINT delete1;
-- SQL Server
SAVE TRANSACTION delete1;

-- 回退到本例给出的保留点
-- MariaDB、MySQL 和 Oracle
ROLLBACK TO delete1;
-- SQL Server
ROLLBACK TRANSACTION delete1;

使用游标 - CURSOR

• 结果集（result set）：SQL 查询所检索出的结果
• 游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条SELECT 语句，而是被该语句检索出来的结果集，应用程序可以根据需要滚动或浏览其中的数据
• 常见的一些选项和特性：
  • 能够标记游标为只读，使数据能读取，但不能更新和删除
  • 能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置、相对位置等）
  • 能标记某些列为可编辑的，某些列为不可编辑的
  • 规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问
  • 指示DBMS 对检索出的数据（而不是指出表中活动数据）进行复制，使数据在游标打开和访问期间不变化
• 游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改
• 创建游标：使用 DECLARE 语句创建游标，并定义相应的 SELECT 语句，但不进行查询
• 使用游标：使用 OPEN CURSOR 语句打开游标，并执行查询
• 访问游标：使用 FETCH 语句访问游标数据，指出要检索哪些行，从何处检索它们以及将它们放于何处、
• 关闭游标：使用 CLOSE 语句关闭游标

-- 创建游标, 找出空缺的电子邮件地址
-- DB2、MariaDB、MySQL 和 SQL Server
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL
-- Oracle 和PostgreSQL
DECLARE CURSOR CustCursor
IS
SELECT * FROM Customers
WHERE cust_email IS NULL

-- 打开游标, 此时开始执行查询
OPEN CURSOR CustCursor

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

-- 关闭游标
-- DB2、Oracle 和 PostgreSQL
CLOSE CustCursor
-- Microsoft SQL Server
CLOSE CustCursor
DEALLOCATE CURSOR CustCursor

高级SQL 特性

约束

• 管理如何插入或处理数据库数据的规则
• 主键：一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动
  • 主键值唯一且不为 NULL，从不修改和更新，不能重用
  • 定义主键的方法：PRIMARY KEY
• 外键：是表中的一列，其值必须列在另一表的主键中
  • 定义外键的方法：REFERENCES
  • 在定义外键后，DBMS 不允许删除在另一个表中具有关联行的行
  • 级联删除：从一个表中删除行时删除所有相关的数据
• 唯一约束：用来保证一列（或一组列）中的数据是唯一的
  • 类似于主键，但存在以下重要区别：
    • 表可包含多个唯一约束，但每个表只允许一个主键
    • 唯一约束列可包含 NULL 值
    • 唯一约束列可修改或更新
    • 唯一约束列的值可重复使用
    • 与主键不一样，唯一约束不能用来定义外键
  • 使用 UNIQUE 关键字定义，也可以用单独的 CONSTRAINT 定义
• 检查约束：保证一列（或一组列）中的数据满足一组指定的条件
  • 检查最小或最大值、指定范围、只允许特定的值
  • 使用 CHECK 关键词定义

-- 给表的 vend_id 列定义添加关键字 PRIMARY KEY，使其成为主键
CREATE TABLE Vendors
(
    vend_id 		CHAR(10) 	NOT NULL PRIMARY KEY,
    vend_name 		CHAR(50) 	NOT NULL,
    vend_address 	CHAR(50) 	NULL,
    vend_country 	CHAR(50) 	NULL
);
-- 使用的是 CONSTRAINT 语法
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

-- cust_id 中的任何值都必须是 Customers 表的 cust_id 中的值
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)

-- 检查约束
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 Customers
ADD CONSTRAINT CHECK (gender LIKE '[MF]')

索引

• 索引用来排序数据以加快搜索和排序操作的速度
  • 主键数据总是排序的，按主键检索特定行总是一种快速有效的操作
  • 搜索其他列中的值通常效率不高，解决方法是使用索引
  • 可以在一个或多个列上定义索引，使 DBMS 保存其内容的一个排过序的列表
• 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能
• 索引用 CREATE INDEX 语句创建，必须唯一命名
• 可能要占用大量的存储空间，并非所有数据都适合做索引

CREATE INDEX prod_name_ind
ON Products (prod_name);

触发器

• 触发器（TRIGGER）是特殊的存储过程，它在特定的数据库活动发生时自动执行
• 触发器可以与特定表上的 INSERT、UPDATE 和 DELETE 操作相关联
• 触发器内的代码具有以下数据的访问权：
  • INSERT 操作中的所有新数据
  • UPDATE 操作中的所有新数据和旧数据
  • DELETE 操作中删除的数据
• 触发器的一些常见用途
  • 保证数据一致
  • 基于某个表的变动在其他表上执行活动
  • 进行额外的验证并根据需要回退数据
  • 计算计算列的值或更新时间戳
• 约束的处理比触发器快，因此在可能的时候，应该尽量使用约束

-- 对所有 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;

数据库安全

• 大多数 DBMS 都给管理员提供了管理机制，利用管理机制授予或限制对数据的访问
• 安全性使用 SQL 的 GRANT 和 REVOKE 语句来管理

---

SQL 必知必会 - 全书目录

1. 第 01 - 10 章：SQL 必知必会 - 单表查询/过滤/汇总
2. 第 11 - 24 章：SQL 必知必会 - 多表查询/联结/组合
3. 第 15 - 24 章：SQL 必知必会 - 数据更新/存储/事务