十八、使用视图
视图
SELECT prod_name, vend_name, prod_name, prod_price, quantity
FROM OrderItems, Vendors, Products
WHERE Vendors.vend_id = Products.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;
将上面查询包装成一个名为ProductCustomers的虚拟表,则可以很轻松地检索出相同的数据
SELECT prod_name, vend_name, prod_name, prod_price, quantity
FROM ProductCustomers
WHERE order_num = 20007;
为什么使用视图
- 简化复杂的SQL操作
- 使用表的一部分而不是整张表
- 保护数据
- 更改数据格式和表示。(可返回与底层表格式不同的数据)
创建视图
CREATE VIEW ProductCustomers AS
SELECT prod_name, vend_name, prod_name, prod_price, quantity
FROM OrderItems, Vendors, Products
WHERE Vendors.vend_id = Products.vend_id
AND OrderItems.prod_id = Products.prod_id
使用视图重新格式化检索出的数据
SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name;
如果经常需要上面这个格式的结果,可转换成视图
CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
FROM Vendors
用视图过滤不想要的数据
CREATE VIEW CustomerEMailList AS
SELECT cust_id,cust_name,cust_email
FROM Customers
WHERE cust_email IS NOT NULL
SELECT * FROM CustomerEMailList
使用视图与计算字段
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity * item_price AS expanded_price
FROM OrderItems;
SELECT * FROM OrderItems
WHERE order_num = 20008;
十九、使用储存过程
储存过程就是为以后使用而保存的一条或多条AQL语句,功能类似为批文件。
执行储存过程
EXECUTE AddNewProduct( 'JTS01',
'Stuffed Eiffel Tower'
6.49
'This is a description');
这里执行一个名为AddNewProduct的储存过程,将一个新产品添加到Product表中。
创建储存过程
CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER --@cnt局部变量
SELECT @cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;
--调用
DECLARE @ReturnValue INT
EXECUTE @ReturnValue = MailingListCount
SELECT @ReturnValue;
CREATE PROCEDURE NewOrder @cust_id CHAR(10)
DECLARE @order_num INTEGER
SELECT @order_num = MAX(order_num)
FROM Orders
SELECT @order_num = @order_num + 1
INSERT INTO Orders(order_num,order_date,cust_id)
VALUES(@order_num,GETDATE(),@cust_id)
RETURN @order_num;
改储存过程在Orders表中创建了一个新订单,它只有一个参数cust_id。
首先声明一个局部变量order_num储存订单号,接着检索最大订单号并加1,然后将新订单号、当前系统时间、顾客ID插入到加1那行。最后返回订单号。
二十、管理事务处理
使用事务处理,确保成批的SQL操作要么完全执行,要么不执行,来维护数据库的完整性。
- 事务
- 回退
- 提交
- 保留点
控制事务处理
-- SQL Server版本
BEGIN TRANSACTION
...
COMMIT TRANSACTION
-- MySQL 版本
START TRANSACTION
--Oracle版本
SET TRANSACTION
使用ROLLBACK 回退
DELETE FROM Orders;
ROLLBACK;
使用 COMMIT
BEGIN TRANSACTION
DELETE OrderIetms WHERE order_num = 12345;
DELETE Orders WHERE order_num = 12345;
COMMIT TRANSACTION
使用保留点
-- oracle、MySQL版本
SAVEPOINT delete1;
ROLLBACK TO delete1;
-- SQL SERVER版本
SAVE TRANSACTION delete1;
ROLLBACK TRANSACTION delete1;
二十一、使用游标
有时,需要在检索出来的行中前进或后退一行或多行,这就是游标的作用
使用游标
-- MySQL、SQL Server版本
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL
--oracle版本
DECLARE CURSOR CustCursor
IS
SELECT * FROM Customers
WHERE cust_email IS NULL
OPEN CURSOR CustCursor
使用OPEN CURSOR打开游标,FETCH访问游标数据
DECLARE TYPE CustCursors IS REF CURSOR
RETURN Customers%ROWTPYE
DECLARE CustRecord Customer%ROWTYPE
BEGIN
OPEN CustRecord;
LOOP
FETCH CustCursor INTO CustRecord;
EXIT WHEN CustCursor%NOTFOUND; --在取不出更多行时退出
...
END LOOP;
CLOSE CustCursor;
END;
二十二、高级SQL特性
约束
主键
CREATE TABLE Vendors
(
vend_id char(10) NOT NULL PRIMARY KEY ,
vend_name char(50) NOT NULL ,
vend_address char(50) NULL ,
vend_city char(50) NULL ,
vend_state char(5) NULL ,
vend_zip char(10) NULL ,
vend_country char(50) NULL
);
-- 也可在表创建后更新
ALTER TABLE Vendors
ADD PRIMARY KEY (vend_id);
外键
外键是表中的一列,其值必须列在另一个表的主键中。
CREATE TABLE Orders
(
order_num int NOT NULL PRIMARY KEY,
order_date datetime NOT NULL ,
cust_id char(10) NOT NULL REFERENCES Customers(cust_id)
);
-- 也可在表创建后更新
ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
唯一约束
用来保证一列中数据是唯一的,如社会号。 但可以修改和更新
检查约束
- 检查最大最小值
- 指定范围
- 只允许特定的值
CREATE TABLE OrderItems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL CHECK (quantity > 0),
item_price decimal(8,2) NOT NULL
);
ADD CONSTRAINT CHECK (gender LIKE '[MF]')
索引
- 索引用来排序数据以加快搜索和排序操作的速度
- 可以在一列或者多个列定义索引
- 索引改善了搜索的性能,但降低了数据插入修改和删除的性能,因为在执行这些操作时要动态更新索引
CREATE INDEX prod_name_id
ON Products (prod_name)
索引必须唯一命名
触发器
触发器是特殊的储存过程,可以与INSERT、UPDATE、DELETE相关联
触发器常见用途
- 保证数据一致 。 例如将州名大写
- 基于某个表的变动在其他表上执行活动。 例如每当更改时写入日志表
- 进行额外的验证并根据需要回退数据
- 计算计算列的值或更新时间戳
-- SQL Server版本
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;
--oracle版本
CREATE TRIGGER customer_state
ON Customers
AFTER INSERT OR UPDATE
FOR EACH NOW
BEGIN
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = :OLD.cust_id;
END;
约束比触发器更快,在可能的时候,尽量使用约束
数据库安全
- GRANT
- REVOKE