SQL必知必会 CH15~22

学习笔记

CH15 插入数据

可以使用INSERT来将行插入到数据表。插入有几种方式：
1. 插入完整的行
2. 插入行的一部分
3. 插入某些查询结果

插入完整的行

使用INSERT INTO语句：

INSERT INTO Customers(cust_id,
                      cust_country,
                      cust_contact,
                      cust_email)
VALUES('1000000006',
       'USA',
       NULL,
       NULL);”

其中列名可以不用列出，但列出列名更安全（这样即使表的结构发生改变，也能正常工作）

插入部分行

可某些列允许为空/有默认值时，可以省略

插入检索出的数据

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

注意填充CustNew时，不应该使用用过的cust_id值。同时SELECT的列名不一定要一样，DBMS会根据列的次序来匹配插入。

INSERT SELECT 可以使用WHERE子句，用于过滤插入数据

从一个表复制到另一个表

即SELECT INTO语句。和INSERT SELECT的区别在于，它会动态创建一张全新的表，并把SELECT的数据导入

CREATE TABLE CustCopy AS
SELECT * FROM Customers;

使用时有几个需要注意的点：
* 任何SELECT选项和子句都可以使用（包括WHERE和GROUP BY）
* 可利用联结从多个表插入数据
* 数据只能插入到一个表中

这种操作很适合用来创建复制的数据表，来测试新SQL语句

CH16 更新和删除数据

更新数据

使用UPDATE语句更新，UPDATE语句由三部分组成：
1. 要更新的表
2. 列名及其新值
3. 确定要更新的行的过滤条件

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

在UPDATE语句中可以使用子查询

要删除某个列的值时，可设置它为NULL（假如表允许NULL值）

删除数据

使用DELETE语句删除行

DELETE FROM Customers
WHERE cust_id = '1000000006';

关于外键：

> 先前提到可以通过联结来结合两个表。DBMS还可以使用外键来严格实施关系，保证引用的完整性。从而防止删除某个关系需要用到的行

CH17 创建和操纵表

创建表

使用CREATE TABLE 关键字

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

注意各列之间用逗号分隔。

对于默认值，还可以使用系统日期的函数或变量来作默认日期。不同的DBMS之间实现不一：

[image:E3D8C3A4-134E-4542-8897-DD04D3DD49D5-355-00000FF843A11F5A/41D25A7D-B13F-4585-8B1F-D33DE38BEA5F.png]

更新表

使用ALTER TABLE语句更新表

不同的DBMS之间允许更新的内容差别很大，下面是在更新表的时候需要考虑的事情：
* 尽量不要在表中包含数据时对表进行更新
* 许多DBMS不允许删除或更改表中的列
* 多数DBMS允许重命名表中的列
* 许多DBMS限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。

其中给已有表增加列是所有DBMS都支持的操作：

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

删除列：

ALTER TABLE Vendors
DROP COLUMN vend_phone;

删除表

使用DROP TABLE语句

DROP TABLE CustCopy;

重命名表

DB2、MariaDB、MySQL、Oracle和PostgreSQL用户使用RENAME语句，SQL Server用户使用sp_rename存储过程，SQLite用户使用ALTER TABLE语句。具体查阅响应的DBMS文档

CH18使用视图

视图是虚拟的表，它只包含使用时动态检索数据的查询。

下面是视图的一些常见应用：
* 重用SQL语句
* 简化复杂的SQL操作，隐藏细节
* 使用表的一部分而不是整个表
* 保护数据
* 更改数据格式和表示

视图的规则和限制

• 视图必须唯一命名
• 可以创建的视图数目没有限制
• 创建视图需要有足够的访问权限
• 视图可以嵌套
• 许多DBMS禁止在视图查询是使用ORDER BY子句
• 有些DBMS要求对返回的所有列进行命名
• 视图不能索引，也不能有关联的触发器或默认值
• 有些DBMS把视图作为只读的查询

创建视图

使用CREATE VIEW语句创建（如果想重命名则必须先DROP VIEW再重新创建）

以利用视图简化复杂的联结为例

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;

CH19 使用存储过程

存储过程类似于编程中的函数。

优点：
* 简化复杂操作
* 防止错误，保证数据一致性
* 简化对变动的管理
* 存储过程以编译过的形式存储，性能更高

缺点：
* 不同DBMS的存储过程语法有所不同，可移植性差（但调用方式基本一致）
* 编写存储过程复杂，需要更高的技能和经验

执行存储过程

使用EXECUTE语句：

EXECUTE AddNewProduct( 'JTS01', 
                       'Stuffed Eiffel Tower', 
                       6.49,
                       'Plush stuffed toy with the text La
➥Tour Eiffel in red white and blue' );

很像函数调用。

创建存储过程

Oracle版本：

CREATE PROCEDURE MailingListCount (
  ListCount OUT INTEGER             -- ListCount 是一个存储返回结果的变量
)
IS
v_rows INTEGER;                     -- v_rows 变量
BEGIN
    SELECT COUNT(*) INTO v_rows     -- 将 SELECT 的结果赋值给 v_rows
    FROM Customers
    WHERE NOT cust_email IS NULL;
    ListCount := v_rows;                -- 结果赋值给外部的 ListCount
END;

SELECT 语句有类似于“执行语句”的功能，如SQL Server中SELECT @order_num=@order_num+1

不同DBMS提供的特性和语法都有所不同，具体需查阅响应的DBMS文档

CH20 管理事务处理

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

事务处理用来管理INSERT，UPDATE和DELETE语句，不能回退CREATE和DROP操作

管理事务处理类似于 git，我们需要将SQL语句组分解为逻辑块，明确规定何时应该回退，何时不应该回退。

事务处理块的开始和结束（SQL Server中）：

BEGIN TRANSACTION
...
COMMIT TRANSACTION

使用ROLLBACK

用来回退SQL语句：

DELETE FROM Orders;
ROLLBACK;

使用COMMIT

一般SQL使用隐式提交，直接对表进行编写。在事务处理块中，则需要进行明确的提交（使用COMMIT 语句）

使用保留点

类似于游戏的存档点，可以使用ROLLBACK回到特定的保留点：

-- MySQL
SAVEPOINT delete1;
-- 回到保留点
ROLLBACK TRANSACTION delete1;

保留点越多，越能灵活地进行回退

CH21 使用游标

有时候需要在检索出来的行中前进或者后退一行或多行，这就是游标的用途。

使用游标

步骤：
1. 声明游标（这个过程实际上没有检索数据，只是定义要使用的SELECT语句）
2. 使用前要打开游标（实际即检索定义时的SELECT语句）
3. 对于填有数据的游标，根据需要取出各行
4. 在结束游标使用时，必须关闭游标

创建游标：

使用DECLARE命名游标，并定义相应的WHERE语句：

-- MySQL
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL

使用游标

使用OPEN CURSOR语句打开游标：

OPEN CURSOR CustCursor;

打开后使用FETCH语句访问游标数据：

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

关闭游标

部分DBMS要求明确释放游标所占用的资源：

-- Oracle
CLOSE CustCursor

第二次使用时，只需用OPEN打开即可。

CH22 高级SQL特性

约束

约束时管理如何插入或处理数据库数据的规则

主键

主键的值唯一标识表中的每一行，方便处理表中的行。

满足以下条件都可以用于主键：
* 任意两行的主键值都不相同
* 不允许NULL值
* 主键值所在的列从不修改或更新
* 主键值不能重用（某值删除后不能重新分配）

CREATE TABLE Vendors 
(
vend_id         CHAR(10)       NOT NULL PRIMARY KEY, 
vend_name       CHAR(50)       NOT NULL,
)

外键

如同之前提到过的，外键时保证引用完整性的极其重要部分。

使用 REFERENCES 关键字

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

唯一约束

唯一约束用来保证一列中的数据是唯一的，和主键些微类似但有以下区别：
* 可以有多个唯一约束，只能有一个主键
* 唯一约束列可以包含NULL值
* 唯一约束列克修改或更新
* 唯一约束列的值可重复使用
* 与主键不同，唯一约束不能用来定义外键

一个常见的例子就是微博昵称（区别于微博账号）。
可以使用 UNIQUE 关键字在表定义中定义。

检查约束

即列的数据需要满足一定要求，例如性别只能是M或F。

CREATE TABLE OrderItems
(
    prod_id       CHAR(10)    NOT NULL,
    quantity      INTEGER     NOT NULL CHECK (quantity > 0),
);

索引

索引用来排序数据以加快搜索和排序操作的速度。
主键数据总是排序的，所以按主键检索特定的行总是一种快速有效的操作。

在开始创建索引时，注意：
* 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。因为必须动态地更新索引
* 索引数据可能要占用大量的存储空间
* 并非所有数据都适合做索引（取值多的数据比较适合）
* 索引用于数据过滤和数据排序

例如：

CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);

触发器

触发器是特殊的存储过程，它与特定表上的INSERT，UPDATE和DELETE操作相关联。

触发器内代码具有以下数据的访问权：
* INSERT操作中的所有新数据
* UPDATE操作中的新旧数据
* DELETE操作中删除的数据

常见用途：
* 保证数据一致（如确保大写）
* 基于某个表的变动在其他表上执行活动（如更新日志表）
* 进行数据验证，根据需要回退数据

例子（Oracle）：

CREATE TRIGGER customer_state
AFTER INSERT OR UPDATE
FOR EACH ROW
BEGIN
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = :OLD.cust_id
END;

可以发现，部分需求可以通过约束来实现，而约束的处理也往往比触发器快。因此在可能的时候，应该尽量使用约束。

数据库安全

一般来说，需要保护的操作有：
* 对数据库管理功能的访问（如创建表、更改或删除已存在的表等）
* 对特定数据库或表的访问
* 访问的类型（只读、对特定列的访问等）
* 仅通过视图或存储过程对表进行访问
* 创建多层次的安全措施，从而允许多种基于登陆的访问和控制
* 限制管理用户账号的能力

安全性使用SQL的 GRANT 和 REVOKE 语句来管理。