《SQL必知必会》19-22章笔记

最新推荐文章于 2022-05-19 16:40:30 发布

steve_99

最新推荐文章于 2022-05-19 16:40:30 发布

阅读量881

点赞数

分类专栏： SQL 文章标签： sql 存储管理

本文链接：https://blog.csdn.net/sysu_cis/article/details/51763578

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

第19章使用存储过程

为以后的使用而保存的一条或多条SQL语句的集合
可将其视为批文件，虽然它们的作用不仅限于批处理

19.2为什么要使用存储过程

通过把处理封装在容易使用的单元中，简化复杂的操作
由于不要求反复建立一系列处理步骤，保证了数据的一致性（防止错误）
简化对变动的管理（保证安全性）
存储过程通常以编译过的形式存储，所以DBMS为处理命令所做的工作较少，提高性能
存在一些只能用在单个请求中的SQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

简单来说三个优点：简单、安全、高性能

19.3执行存储过程

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

执行一个名为AddNewProduct的存储过程，将一个新产品添加到Products表中。AddNewProduct有四个参数，分别是：供应商ID、产品名、价格和描述。这四个参数匹配存储过程中的4个预期变量。此存储过程将新行添加到Products表，并将传入的属性赋给相应的列。我们注意到，在Products表中还有另外一个需要值的列Prod_id，它是表的主键。为什么不把这个值作为属性传递给存储过程？要保证恰当地生成此ID，最好是使生成此ID的过程自动化。
以下是存储过程完成的工作：

验证传递的数据，保证所有4个参数都有值
生成用作主键的唯一ID
将新产品插入到Products表，在合适的列总存储生成的主键和传递的数据。

19.4创建存储过程

一个例子，对邮件发送清单中具有邮件地址的顾客进行计数。

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;

第20章管理事务处理

20.1事务处理

事务处理

事务处理用来维护数据库的完整性，它保证成批的SQL操作要么完全执行，要么完全不执行
事务处理是一种机制，用来管理必须成批执行的SQL操作，以保证数据库不包含不完整的操作结果（好比运行过程中突然断电了，导致批处理意外终止）

事务：指一组SQL语句

回退：rollback，撤销指定SQL语句的过程
提交：commit，将未存储的SQL语句结果写入数据库表
保留点：临时占位符

可以回退INSERT、UPDATE、DELETE语句

不能回退CREATE、DROP、SELECT（回退SELECT也没有必要）

20.2控制事务处理

通常COMMIT用于保存更改，ROLLBACK用于撤销

20.2.1使用ROLLBACK

DELETE FROM Orders；
ROLLBACK；

20.2.2使用COMMIT

在事务处理块中，提交不会隐式进行。（但是也是与不同的DBMS有关，有的就是隐式提交处理事务端）
进行明确的提交，使用COMMIT语句。下面是一个SQL Server的例子：

BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num =12345
COMMIT TRANSACTION

在这个例子中，从系统中完全删除订单12345.因为设计删除两个数据库表，用事务处理块来保证订单不被部分删除。最后COMMIT语句仅在不出错时写出更改。如果第一叫DELETE起作用，但第二条DELETE失败，则DELETE不会提交。

20.2.3使用保留点

设置占位符，每个点有唯一的名字。
完整的SQL Server例子

BEGIN TRANSACTION
INSERT INTO Customers(cust_id,cust_name)
VALUES('100000010','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,1,'BR03',100,10.99)
IF @@ERROR <>0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION

这里的事务处理块中包含4个INSERT语句，只要判断变量不等于0，那么就有错误发生，就可以进行回退。

第21章使用游标

21.1游标

返回结果集，简单的SELECT无法得到第一行、下一行或前10行。
常见的一些选项和特性：
- 能够标记游标为只读，数据不能更新和删除
- 能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置、相对位置等）
- 能标记某些列为可编辑的，某些列为不可编辑的
- 规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问
- 指示DBMS对检索出的数据（而不是指出表中活动数据）做复制，使在游标打开和访问期间数据不变化

21.2使用游标

步骤：

在能够使用游标前，必须声明（定义）它
一旦声明后，必须打开游标以供使用（数据检索出来）
对于填有数据的游标，根据需要取出（检索）各行
在结束游标使用时，必须关闭游标，而且可能的话，释放游标

21.2.1创建游标

使用DECLARE语句创建游标。
例子，找出空缺的电子邮件地址

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

21.2.2使用游标

OPEN CURSOR CustCursor
现在可以用FETCH语句访问游标数据了，FETCH指出要检索哪些行，从何处检索它们以及将它们放于何处。
例子：从游标中检索第一行

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

例子2：从第一行到最后一行，对检索出来的数据进行循环

DECLARE TYPE CustCursor IS REF CURSOR
    RETURN Custmors%ROWTYPE;
DECLARE CustRecord Customers%POWTYPE    
BEGIN
    OPEN CustCursor
    LOOP
    FETCH CustCursor INTO CustRecord;
    EXIT WHEN CustCursor%NOTFOUND;
    ...
    END LOOP;
    CLOSE CustCursor;
END;

21.2.3关闭游标

CLOSE CustCursor

第22章高级SQL特性

22.1约束

22.1.1主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。
那么主键需要什么条件呢？

任意两行的主键值都不相同
每行都具有一个主键值（即不允许NULL值）
包含主键的列从不修改或更新。
主键值不能重用。如果表中删除某一行，其主键值不分配给新行。

在定义表的时候可以创建主键

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

更新表的时候

ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

22.1.2外键

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

    Orders表将录入系统的每个订单作为一行包含其中。顾客信息存储在Customers表中。Orders表中的订单通过顾客ID与Customers表中的特定的行进行关联。顾客ID为Customers表的主键，每个顾客都有唯一的ID。订单号为Orders的主键，每个订单都有唯一的订单号。

    Orders表中的顾客ID列的值并不一定是唯一的。如果某个顾客有多个订单，则有多个行有相同的顾客ID。同时，Orders表中的顾客ID列的合法值为Customers表中的顾客ID。

    在这个例子中，在Orders的顾客ID列上定义一个外键，因此只能接受CUstomers表的主键值

下面是定义这个外键的方法

CTEATE TABLE Orders
(.......
REFERENCES Customers(cust_id)
);

其中表定义使用了REFERENCES关键字，它表示cust_id中的任何值都必须是Customers表中的cust_id的值。
相同的工作也能在ALTER TABLE与剧中使用CONSTRAINT语法来完成：

ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

22.1.3唯一约束

唯一约束用来保证一列中的数据是唯一的。它们类似主键，但是又有一些区别。

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

22.1.4检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检查约束的常见用途有以下几点

检查最小或最大值（例如防止0个物品的订单）
指定范围，例如保证发货日期大于等于今天
只允许特定的值。例如性别只允许M或F。

下面的例子就是约束检查

CREATE TABLE OrderItems
(
..........
quantity INTEGER    NOT NULL CHECK (quantity>0),
)

这样就能保证任何插入或者更新的行都会被检查，保证大于0。
检查gender只包含M或者F可以用

ADD CONSTRAINT CHECK (gender LIKE '[MF]')

22.2索引

为什么要索引？
用来排序数据，以加快搜索和排序操作的速度。

索引改善检索操作的性能，但降低数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引
索引数据可能要占用大量的存储空间
并非所有数据都适合于索引。唯一性不好的数据从索引得到的好处不如具有更多可能值的数据从索引得到的好处多
索引用于数据过滤和数据排序，如果经常以某种特定的顺序排序数据，则该数据可能是索引的备选
可以在索引中定义多个列

在Products表的产品名列上创建一个简单的索引

CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);

22.3触发器

触发器的常见用途
触发器是特殊对存储过程，它在特定的数据库活动发生时自动执行。

保证数据一致（如大小写自动转换）
基于某个表的变动在其他表上执行活动
进行额外的验证并根据需要回退数据
计算计算列的值或更新时间戳

一个简单的例子，它对所有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;

一般约束比触发器块，因此可能的时候，应该尽可能使用约束。