您当前位置：首页 > php开源 > 综合技术 > 《SQL必知必会》19-22章笔记

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

来源：程序员人生发布时间：2016-07-09 13:07:59 阅读次数：2719次

第19章使用存储进程

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

19.2为何要使用存储进程

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

简单来讲3个优点：简单、安全、高性能

19.3履行存储进程

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

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

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

19.4创建存储进程

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

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操作要末完全履行，要末完全不履行
事务处理是1种机制，用来管理必须成批履行的SQL操作，以保证数据库不包括不完全的操作结果（好比运行进程中突然断电了，致使批处理意外终止）

事务：指1组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语句。下面是1个SQL Server的例子：

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

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

20.2.3使用保存点

设置占位符，每一个点有唯1的名字。
完全的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没法得到第1行、下1行或前10行。
常见的1些选项和特性：
- 能够标记游标为只读，数据不能更新和删除
- 能控制可以履行的定向操作（向前、向后、第1、最后、绝对位置、相对位置等）
- 能标记某些列为可编辑的，某些列为不可编辑的
- 规定范围，使游标对创建它的特定要求（如存储进程）或对所有要求可访问
- 唆使DBMS对检索出的数据（而不是指出表中活动数据）做复制，使在游标打开和访问期间数据不变化

21.2使用游标

步骤：

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

21.2.1创建游标

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

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

21.2.2使用游标

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

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

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

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主键

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

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

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

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外键

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

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

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

    在这个例子中，在Orders的顾客ID列上定义1个外键，因此只能接受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唯1束缚

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

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

22.1.4检查束缚

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

检查最小或最大值（例如避免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必须动态地更新索引
索引数据可能要占用大量的存储空间
并不是所有数据都合适于索引。唯1性不好的数据从索引得到的好处不如具有更多可能值的数据从索引得到的好处多
索援用于数据过滤和数据排序，如果常常以某种特定的顺序排序数据，则该数据多是索引的备选
可以在索引中定义多个列

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

CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);

22.3触发器

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

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

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

1般束缚比触发器块，因此可能的时候，应当尽量使用束缚。

生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生

------分隔线----------------------------

上一篇 iOS 简单实用的音乐播放器，少年，自己做个歌单吧。。。。。。

下一篇 添物零基础到大型全栈架构师服务器架构及实战（架构篇）- 概述

分享到: