第10 天创建视图和索引
目标
今天我们将要讨论的内容对于一些有SQL 有一定了解的程序员或数据库管理人员来说
可能是新东西从第1 天到第8 天我们主要学习了如何使用SQL 在关系数据库中进行基
本的工作在第9 天我们讨论了数据库的设计表的创建以及其它的数据处理语句所有
这些内容的对象表数据库记录字段的共同之处在于— — 它们是存在于磁盘上的
物理对象今天我们来学习SQL 的两个新的特性它允许你以于数据在磁盘上的存储不同
的方式来显示数据这两个特性就是记录和索引到今天的结束你将学习以下内容
l 如何区别索引与视图
l 如何创建视图
l 如何创建索引
l 如何用视图来修改数据
l 索引可以做什么
视图常常被称为虚表它是用CREATE VIEW 语句来建立的在视图建立以后你可以
对视图采用如下命令
l SELECT
l INSERT
l INPUT
l UPDATE
l DELETE
索引是与磁盘上数据的存储方式不同的另外一种组织数据的方法索引的特例是表中
记录依据其在磁盘上的存储位置显示索引可以在表内创建一个列或列的组合当应用索
引以后数据会按照你使用CREATE INDEX 语句所定义的排序方式返回给用户通过对
正确的特定的两个表的归并字段进行索引可以获得明显的好处
注视图与索引是两个完全不同的对象但是它们有一点是相同的它们都与一个表
或数据库相关联尽管每一个对象只能与一个特定的表相关联但它们还是通过对数据的
SQL 21 日自学通(V1.0) 翻译人笨猪
194
预排序和预定义显著地提高了表的工作性能
注在今天的例子中我们使用的是PERSONAL ORACLE 7 对于你使用的解释器请参
阅它的文档与找出它们在语法上的不同之处
使用视图
你可以对封装的复合查询应用视图或虚表当对一组数据建立视图以后你可以像处
理另外一个表一样去处理视图但是在视图中修改数据时要受到一些限制当表中的数
据改变以后你将会在查询视图时发现相应的改变视图并不占用数据库或表的物理空间
CREATE VIEW 和语法如下
SYNTAX
CREATE VIEW <view_name> [(column1, column2...)] AS
SELECT <table_name column_names>
FROM <table_name>
与通常一样语法看起来不太容易使人明白但是对于今天的内容我们有许多例子来
展示视图的用法和优点该命令通知SQL 去创建一个视图用你给出的名字及其列如
果你想指定的话SQL 的SELECT 语句可以判定列所对应的字段及其数据类型没错
就是九天以来你一直使用的SELECT 语句
在你用视图进行任何有用的工作之前你需要对BILLS 数据库再添加一些数据如果
你已经用DROP DATABASE 语句对它进行了试验那么你需要重新建立它数据见表10.1
10.2 和10.3
INPUTOUTPUT
SQL> create database BILLS;
Statement processed.
INPUTOUTPUT
SQL> create table BILLS (
2 NAME CHAR(30) NOT NULL,
3 AMOUNT NUMBER,
4 ACCOUNT_ID NUMBER NOT NULL);
SQL 21 日自学通(V1.0) 翻译人笨猪
195
Table created.
INPUTOUTPUT
SQL> create table BANK_ACCOUNTS (
2 ACCOUNT_ID NUMBER NOT NULL,
3 TYPE CHAR(30),
4 BALANCE NUMBER,
5 BANK CHAR(30));
Table created.
INPUTOUTPUT
SQL> create table COMPANY (
2 NAME CHAR(30) NOT NULL,
3 ADDRESS CHAR(50),
4 CITY CHAR(30),
5 STATE CHAR(2));
Table created.
Table 10.1. Sample data for the BILLS table.
Name Amount Account_ID
Phone Company 125 1
Power Company 75 1
Record Club 25 2
Software Company 250 1
Cable TV Company 35 3
Joe's Car Palace 350 5
S.C. Student Loan 200 6
Florida Water Company 20 1
U-O-Us Insurance Company 125 5
Debtor's Credit Card 35 4
Table 10.2. Sample data for the BANK_ACCOUNTS table.
Account_ID Type Balance Bank
1 Checking 500 FirstFederal
2 MoneyMarket 1200 FirstInvestor's
3 Checking 90 CreditUnion
SQL 21 日自学通(V1.0) 翻译人笨猪
196
Account_ID Type Balance Bank
5 Checking 2500 SecondMutual
6 Business 4500 Fidelity
Table 10.3. Sample data for the COMPANY table.
Name Address City State
Phone Company 111 1st Street Atlanta GA
Power Company 222 2nd Street Jacksonville FL
Record Club 333 3rd Avenue Los Angeles CA
Software Company 444 4th Drive San Francisco CA
Cable TV Company 555 5th Drive Austin TX
Joe's Car Palace 1000 Govt. Blvd Miami FL
S.C. Student Loan 25 College Blvd Columbia SC
Florida Water Company 1883 Hwy 87 Navarre FL
U-O-Us Insurance 295 Beltline Hwy Macon GA
Company
Debtor's Credit Card 115 2nd Avenue Newark NJ
现在你已经成功地使用CREATE DATABASE CREATE TABLE 和INSERT 命令输入
了所有的这些信息现在我们开始对视图作进一步的讨论
简单视图
让我们从最简单的视图开始假设由于一些未知的原因我们需要在BILLS 中创建视
图它看上去与BILLS 表相同但是名字叫DEBTS 不相同语句如下
INPUT
SQL> CREATE VIEW DEBTS AS
SELECT * FROM BILLS;
可以用下边的语句来确认上边的结果
INPUT/OUTPUT
SQL> SELECT * FROM DEBTS;
NAME AMOUNT ACCOUNT_ID
Phone Company 125 1
Power Company 75 1
Record Club 25 2
Software Company 250 1
Cable TV Company 35 3
Joe's Car Palace 350 5
SQL 21 日自学通(V1.0) 翻译人笨猪
197
NAME AMOUNT ACCOUNT_ID
S.C. Student Loan 200 6
Florida Water Company 20 1
U-O-Us Insurance Company 125 5
Debtor's Credit Card 35 4
你甚至可以已经存在的视图来创建一个新的视图当从视图中创建视图时要仔细尽
管操作是可以接受的但是它使得维护工作变得复杂假设你的视图有三级如表的视图
的视图的视图那么当表中的第一级视图被删除时会有什么情况发生另外两个视图会仍
然存在但是在第一个视图恢复之前它们是没有用处的切记当创建一个视图后,它实际
上是一个虚表
INPUT
SQL> CREATE VIEW CREDITCARD_DEBTS AS
2 SELECT * FROM DEBTS
3 WHERE ACCOUNT_ID = 4;
SQL> SELECT * FROM CREDITCARD_DEBTS;
OUTPUT:
NAME AMOUNT ACCOUNT_ID
Debtor's Credit Card 35 4
CREATE VIEW 也允许你从表中选择特定的列到视图中下例是从COMPANY 表中选
择了NAME 和STATE 列
INPUT
SQL> CREATE VIEW COMPANY_INFO (NAME, STATE) AS
2 SELECT * FROM COMPANY;
SQL> SELECT * FROM COMPANY_INFO;
OUTPUT
NAME STATE
Phone Company GA
Power Company FL
Record Club CA
Software Company CA
Cable TV Company TX
Joe's Car Palace FL
SQL 21 日自学通(V1.0) 翻译人笨猪
198
NAME STATE
S.C. Student Loan SC
Florida Water Company FL
U-O-Us Insurance Company GA
Debtor's Credit Card NJ
注用户可以通过创建视图来查询特定的数据如果你的表有50 列且有成千上万个记
录但是你只需要其中两列的话你可以创建视图来选择这两列然后从视图中查询你会
发现查询在数据返回时间上与原来有相当大的不同

列的重命名
视图继承了已有列的名字此外视图还可以有自己的名字SQL 的CREATE VIEW 允
许你对所选择的列进行重命名它与前边的例子非常相似如果你想把COMPANY 中的
ADDRESS CITY 和STATE 字段组合起来并打印到信封上时该如何做呢请看下边的例
子它使用了SQL 的+操作符将地址字段与逗号和空格组合起来
INPUT
SQL> CREATE VIEW ENVELOPE (COMPANY, MAILING_ADDRESS) AS
2 SELECT NAME, ADDRESS + " " + CITY + ", " + STATE
3 FROM COMPANY;
SQL> SELECT * FROM ENVELOPE
OUTPUT
COMPANY MAILING_ADDRESS
Phone Company 111 1st Street Atlanta, GA
Power Company 222 2nd Street Jacksonville, FL
Record Club 333 3rd Avenue Los Angeles, CA
Software Company 444 4th Drive San Francisco, CA
Cable TV Company 555 5th Drive Austin, TX
Joe's Car Palace 1000 Govt. Blvd Miami, FL
S.C. Student Loan 25 College Blvd. Columbia, SC
Florida Water Company 1883 Hwy. 87 Navarre, FL
U-O-Us Insurance Company 295 Beltline Hwy. Macon, GA
Debtor's Credit Card 115 2nd Avenue Newark, NJ
SQL 21 日自学通(V1.0) 翻译人笨猪
199
分析
当在视图中使用SQL 的计算功能时SQL 会要求你给出一个虚字段的名字这是可以
理解的因为像COUNT(*)或AVG(PAYMENT).是不能作为名字的
注检查你的解释器看它是否支持+操作符
SQL 对视图的处理过程
视图可以以比数据在数据库表中的存储情况更为便捷的方式来返回数据当需要连续
进行几个复合的查询时例如在存储过程和应用程序中时视图也是非常方便的为了进
一步地说明视图和SELECT 语句下边的例子分别使用了SQL 的查询方法和视图方法以
作对比假设你需要经常去运行一个查询例如你需要例行公事地将BILLS 表与
BANK_ACCOUNT 表进行归并以得到支付信息
INPUT
SQL> SELECT BILLS.NAME, BILLS.AMOUNT, BANK_ACCOUNTS.BALANCE
2 BLANCE BANK_ACCOUNTS.BANK BANK FROM BILLS, BANK_ACCOUNTS
3 WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;
OUTPUT
BILLS.NAME BILLS.AMOUNT BALANCE BANK
Phone Company 125 500 First Federal
Power Company 75 500 First Federal
Record Club 25 1200 First Investor's
Software Company 250 500 First Federal
Cable TV Company 35 90 Credit Union
Joe's Car Palace 350 2500 Second Mutual
S.C. Student Loan 200 4500 Fidelity
Florida Water Company 20 500 First Federal
U-O-Us Insurance Company 125 2500 Second Mutual
这一过程用视图来表达则语句如下
INPUT/OUTPUT
SQL> CREATE VIEW BILLS_DUE (NAME, AMOUNT, ACCT_BALANCE, BANK) AS
2 SELECT BILLS.NAME, BILLS.AMOUNT, BANK_ACCOUNTS.BALANCE
3 BANK_ACCOUNTS.BANK FROM BILLS, BANK_ACCOUNTS
SQL 21 日自学通(V1.0) 翻译人笨猪
200
4 WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID
View created.
如果你对BILLS_DUE 视图执行查询是使用了一些条件如下语句所示
INPUT/OUTPUT
SQL> SELECT * FROM BILLS_DUE
2 WHERE ACCT_BALANCE > 500
NAME AMOUNT ACCT_BALANCE BANK
Record Club 25 1200 FirstInvestor's
Joe's Car Palace 350 2500 SecondMutual
S.C. Student Loan 200 4500 Fidelity
U-O-Us Insurance Company 125 2500 SecondMutual
分析
在上述语句中SQL 执行了好几步操作因为BILLS_DUE 是一个视图不是一个真实
的表SQL 首先查找一个名字叫BILLS_DUE 的表但是没有找到SQL 的过程可能会从
系统表中发现BILLS_DUE 原来是一个视图这依据你所使用的数据库而定于是它对视
图进行了诠释并形成了如下的查询语句
SQL> SELECT BILLS.NAME, BILLS.AMOUNT, BANK_ACCOUNTS.BALANCE,
2 BANK_ACCOUNTS.BANK FROM BILLS, BANK_ACCOUNTS
3 WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID
4 AND BANK_ACCOUNTS.BALANCE > 500;
例10.1
构造一个视图以显示所有需要发送账单的州同时要求显示每个州的账单金额总数和
账单的总数
首先你知道CREATE VIEW 语句看起来应该是下边的样子
CREATE VIEW EXAMPLE (STATE, TOTAL_BILLS, TOTAL_AMOUNT) AS...
现在你必须决定SELECT 语句的内容你要清楚根据需要你要选择STATE 字段并应
该使用SELECT DISTINCT 语法以显示账单需要发送的州例如
INPUT
SQL> SELECT DISTINCT STATE FROM COMPANY;
OUTPUT
STATE GA FL CA TX SC NJ 为节约宽度这里用制表符取代了段落标记
SQL 21 日自学通(V1.0) 翻译人笨猪
201
除了选择州字段以外你还需要知道发往这个州的账单总数因此你需要归并BILLS
表和COMPANY表
INPUT/OUTPUT
SQL> SELECT DISTINCT COMPANY.STATE, COUNT(BILLS.*) FROM BILLS, COMPANY
2 GROUP BY COMPANY.STATE
3 HAVING BILLS.NAME = COMPANY.NAME;
STATE COUNT(BILLS.*)
GA 2
FL 3
CA 2
TX 1
SC 1
NJ 1
现在你已经成功地返回了需要的三分之二的结果你可以用下边的语句来最终结束这
条语句加入SUM 语句以返回每个州的金额总数
INPUT/OUTPUT
SQL> SELECT DISTINCT COMPANY.STATE, COUNT(BILLS.NAME), SUM(BILLS.AMOUNT)
2 FROM BILLS, COMPANY
3 GROUP BY COMPANY.STATE
4 HAVING BILLS.NAME = COMPANY.NAME
STATE COUNT(BILLS.*) SUM(BILLS.AMOUNT)
GA 2 250
FL 3 445
CA 2 275
TX 1 35
SC 1 200
NJ 1 35
最后一步你可以将它与最初的CREATE VIEW 语句组合在一起
INPUT/OUTPUT
SQL> CREATE VIEW EXAMPLE (STATE, TOTAL_BILLS, TOTAL_AMOUNT) AS
2 SELECT DISTINCT COMPANY.STATE, COUNT(BILLS.NAME),SUM(BILLS.AMOUNT)
3 FROM BILLS, COMPANY
SQL 21 日自学通(V1.0) 翻译人笨猪
202
4 GROUP BY COMPANY.STATE
5 HAVING BILLS.NAME = COMPANY.NAME;
INPUT/OUTPUT
SQL> SELECT * FROM EXAMPLE;
STATE TOTAL_BILLS TOTAL_AMOUNT
GA 2 250
FL 3 445
CA 2 275
TX 1 35
SC 1 200
NJ 1 35
上边的例子向你展示了如何设计CREATE VIEW 语句和SELECT 语句SELECT 语句
测试代码的作用在于检查数据的返回结果是不是符合你的要求然后才将其最终组合来创
建一个视图
例10.2
假设你的债权人因为你推迟付款加收10%的服务费而且不幸的是你在这个月的每件
事都需要推迟因此你想看一下需要推迟付款的债主的账号
归并语句在这里是非常简单的因为你不需要使用像SUM 或COUNT 之类的语句
可是你会第一次发现使用视图的好处在视图中你可以将增加的10%的服务费在视图中作
为一个字段由于这一点你可以在视图中使用SELECT 语句来为你计算总计的结果语
句如下
INPUT
SQL> CREATE VIEW LATE_PAYMENT (NAME, NEW_TOTAL, ACCOUNT_TYPE) AS
2 SELECT BILLS.NAME, BILLS.AMOUNT * 1.10, BANK_ACCOUNTS.TYPE
3 FROM BILLS, BANK_ACCOUNTS
4 WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;
OUTPUT
View created.
INPUT/OUTPUT
SQL> SELECT * FROM LATE_PAYMENT
SQL 21 日自学通(V1.0) 翻译人笨猪
203
NAME NEW_TOTAL ACCOUNT_TYPE
Phone Company 137.50 Checking
Power Company 82.50 Checking
Record Club 27.50 MoneyMarket
Software Company 275 Checking
Cable TV Company 38.50 Checking
Joe's Car Palace 385 Checking
S.C. Student Loan 220 Business
Florida Water Company 22 Checking
U-O-Us Insurance Company 137.50 Business
Debtor's Credit Card 38.50 Savings

在SELECT 语句使用约束
在视图的SELECT 语句中使用约束是必然的在使用SELECT 语句中可以应用下边这
两个规则
l 你不能使用UNION 操作
l 你不能使用ORDER BY 子句但是在视图中使用GROUP BY 子句可以有ORDER
BY 子句相同的功能
在视图中修改数据
正如同你所学习过的你可以在数据库的一个或多个表中使用视图你也可以在SQL
和数据库应用程序中使用虚表在使用CREATE VIEW SELECT 创建视图以后你可以用
在第八天操作数据学习过的INSERT UPDATE 和DELETE 语句来更新插入删除
视图中的数据
我们在稍后讨论在视图中操作数据的限制下边的例子显示了如何在视图中操作数据
将例10.2 中的工作继续更新BILLS 表中的那不幸的10%的费用
INPUT/OUTPUT
SQL> CREATE VIEW LATE_PAYMENT AS
2 SELECT * FROM BILLS;
SQL> UPDATE LATE_PAYMENT
2 SET AMOUNT = AMOUNT * 1.10;
SQL 21 日自学通(V1.0) 翻译人笨猪
204
SQL> SELECT * FROM LATE_PAYMENT;
NAME NEW_TOTAL ACCOUNT_ID
Phone Company 137.50 1
Power Company 82.50 1
Record Club 27.50 2
Software Company 275 1
Cable TV Company 38.50 3
Joe's Car Palace 385 5
S.C. Student Loan 220 6
Florida Water Company 22 1
U-O-Us Insurance Company 137.50 5
Debtor's Credit Card 38.50 4
为了验证结果确实已经进行了更新我们在一次在BILLS 表中运行查询
INPUT/OUTPUT
SQL> SELECT * FROM BILLS
NAME NEW_TOTAL ACCOUNT_ID
Phone Company 137.50 1
Power Company 82.50 1
Record Club 27.50 2
Software Company 275 1
Cable TV Company 38.50 3
Joe's Car Palace 385 5
S.C. Student Loan 220 6
Florida Water Company 22 1
U-O-Us Insurance Company 137.50 5
Debtor's Credit Card 38.50 4
现在我们从视图中删除一行
INPUT/OUTPUT
SQL> DELETE FROM LATE_PAYMENT
2 WHERE ACCOUNT_ID = 4;
SQL> SELECT * FROM LATE_PAYMENT;
NAME NEW_TOTAL ACCOUNT_ID
Phone Company 137.50 1
Power Company 82.50 1
Record Club 27.50 2
SQL 21 日自学通(V1.0) 翻译人笨猪
205
Software Company 275 1
Cable TV Company 38.50 3
Joe's Car Palace 385 5
S.C. Student Loan 220 6
Florida Water Company 22 1
U-O-Us Insurance Company 137.50 5
最后一步是测试UPDATE 函数为BILLS 表中所有的NEW_TOTAL 中大于100 的增
加10
INPUT/OUTPUT
SQL> UPDATE LATE_PAYMENT
2 SET NEW_TOTAL = NEW_TOTAL + 10
3 WHERE NEW_TOTAL > 100;
SQL> SELECT * FROM LATE_PAYMENT;
NAME NEW_TOTAL ACCOUNT_ID
Phone Company 147.50 1
Power Company 82.50 1
Record Club 27.50 2
Software Company 285 1
Cable TV Company 38.50 3
Joe's Car Palace 395 5
S.C. Student Loan 230 6
Florida Water Company 22 1
U-O-Us Insurance Company 147.50 5
在视图中修改数据的几个问题
你大概已经看到了视图其实就是一组表的映射所以想要修改下层表的数据并不会
总是像上例那样直接下面给出了你在使用视图进行工作时常用会遇到的限制
l 对于多表视图你不能使用DELETE 语句
l 除非底层表的所有非空列都已经在视图中出现否则你不能使用INSERT 语句
有这个限制的原因是SQL 不知道应该将什么数据插入到NOT COLUMNS 限制列
中没有在视图中出现的
l 如果对一个归并的表格插入或更新记录那么所有被更新的记录必须属于同一个
SQL 21 日自学通(V1.0) 翻译人笨猪
206
物理表
l 如果你在创建视图时使用了DINTINCT 子句那么你就不能插入或更新这个视图
中的记录
l 你不能更新视图中的虚拟列它是用计算字段得到了
通用应用程序的视图
下边有几个视图需要完成的任务
l 提供了用户安全功能
l 可以进行单位换算
l 创建一个新的虚拟表格式
l 简单的结构化复合查询
视图与安全性
尽管我们需要在第12 天的数据库安全中才会完全地讨论数据库的安全性但是在
本题目下我们先来大致谈一谈如何使用视图的安全性功能
所有的关系型数据库在今天都有着完善的内置的安全性特性数据库系统的用户通常
会根据他们所使用的数据库来分成不同的组常用组的类型有数据库管理员database
administrators 数据库开发员database developers 数据录入人员data entry personnel
和大众用户不同的组在使用数据库时有着不同的权限数据库管理员具有系统的完全控
制权限包括更新UPDATE 插入INSERT 删除DELETE 修改ALTER 数
据库的特权而大众用户则只有使用SELECT 语句的权利— — 或许是只有对特定的数据库
使用特定的SELECT 语句的权利
视图通常用在对用户访问数据进行控制的场所例如如果你只想让用户访问BILLS
表中的NAME 字段你需要创建一个名字叫BILLS_NAME 的视图
INPUT/OUTPUT
SQL> CREATE VIEW BILLS_NAME AS SELECT NAME FROM BILLS
具有系统管理员权限的人也可以使用具有公共组SELECT 权限的BILLS_NAME 该
组没有任何对下层BILLS 表的权限如你所料SQL 也提供了可以使用的数据安全语句
现在你要知道的是视图对于实现数据库的安全有相当大的用处
SQL 21 日自学通(V1.0) 翻译人笨猪
207
在单位换算中使用视图
视图在你提供给用户的数据与数据库中的真实数据不同时也相当有用例如如果
AMOUNT 字段实际上存储于美国加拿大的用户不想频繁地进行美元与加拿大元之间的
转换工作那么你可以创建一个叫CANADA_BILLS 的视图
INPUT/OUTPUT
SQL> CREATE VIEW CANADIAN_BILLS (NAME, CAN_AMOUNT) AS
2 SELECT NAME, AMOUNT / 1.10
3 FROM BILLS
SQL> SELECT * FROM CANADIAN_BILLS
NAME CAN_AMOUNT
Phone Company 125
Power Company 75
Record Club 25
Software Company 250
Cable TV Company 35
Joe's Car Palace 350
S.C . Student Loan 200
Florida Water Company 20
U-O- Us Insurance Company 125
分析
当进行类似这样的单位转换时要注意当计算字段创建一个列时修改底层表的数据时
可能带来的问题与往常一样你应该查看你的数据库系统的相关文档看一看你的系统上
的CREATE VIEW 命令是如何执行了

在视图中使用简单的结构化复合查询
视图在你需要按次序运行一系列查询以后得到某个结果的情况下也很有用下边的例
子显示了如何在这种情况下使用视图
如果想找出所有发给德克萨斯州的账单金额少于50 美元的银行的名字你可以把这个
问题分解成如下的两个问题
l 得到所有发给德克萨斯州的账单
SQL 21 日自学通(V1.0) 翻译人笨猪
208
l 找出账单中金额小于50 美元的记录
让我们用两个分开的视图BILLS_1 和BILLS_2 来解决这两个问题
INPUT/OUTPUT
SQL> CREATE TABLE BILLS1 AS
2 SELECT * FROM BILLS
3 WHERE AMOUNT < 50;
SQL> CREATE TABLE BILLS2 (NAME, AMOUNT, ACCOUNT_ID) AS
2 SELECT BILLS.* FROM BILLS, COMPANY
3 WHERE BILLS.NAME = COMPANY.NAME AND COMPANY.STATE = "TX";
分析
因为你想找的是所有发给德州的账单和所有账单中小于50 美元的账单你现在可以使
用SQL 中的IN 子句来找出所有在BILLS1 中发往德州的账单这个信息来创建一个名字
叫BILLS3 的视图
INPUT/OUTPUT
SQL> CREATE VIEW BILLS3 AS
2 SELECT * FROM BILLS2 WHERE NAME IN
3 (SELECT * FROM BILLS1);
现在将上述查询与BANK_ACCOUNT 表进行合并以得到最初想要的结果
INPUT/OUTPUT
SQL> CREATE VIEW BANKS_IN_TEXAS (BANK) AS
2 SELECT BANK_ACCOUNTS.BANK
3 FROM BANK_ACCOUNTS, BILLS3
4 WHERE BILLS3.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID
SQL> SELECT * FROM BANK_IN_TEXAS;
BANK
Credit Union
分析
如你所见当把一个查询分解成几个视图以后最后的查询就非常简单了当然使
用一个视图也经常是必需的
SQL 21 日自学通(V1.0) 翻译人笨猪
209
删除视图语句
就像每一个CREATE 语句一样CREATE VIEW 语句对应的也与DROP VIEW 语句相
对应其语法形式如下
SQL> DROP VIEW view_name
在使用它的时候需要记住DROP VIEW 命令会使所有与DROP 视图相关联的视图不
能正常运行一些数据库系统甚至会将所有与要DROP 的视图相关联的视图也删除掉在
Personal Oracle7 中如果你将BILLS1 删除那么最终的查询将会返回下边的错误
INPUT/OUTPUT
SQL> DROP VIEW BILLS1;
View dropped.
SQL> SELECT * FROM BANKS_IN_TEXAS;
ERROR at line 1:
ORA-04063: view "PERKINS.BANKS_IN_TEXAS" has errors
注你可以删除一个视图而不影响任何一个真实的表这也就是为什么我们将视图称为虚
表的原因虚体也使用了相同的逻辑
使用索引
使用索引是另外一种让数据提供给用户的形式与它在数据库中不同的方法此外索
引可以让存储于磁盘上的数据进行重新排序这是一些视图不具有的功能
在SQL 中使用索引是其于以下几个原因
l 在使用UNIQUE 关键字时强制性地保证数据的完整性
l 可以容易地用索引字段或其它字段进行排序
l 提高查询的执行速度
什么是索引
可以用两种方法从数据库中获得数据第一种方法常被称为顺序访问方式它需要SQL
检查每一个记录以找到与之相匹配的这种查找的方法效率很低但它是使记录准确定位
的唯一方法回想一下以前图书馆的卡片档案系统假设卡片是按字母的顺序排列的那
SQL 21 日自学通(V1.0) 翻译人笨猪
210
么在将它们抽出来后在放回卡片柜时那么当你来到书柜的旁边以后那么你只能从头开
始然后一张卡片一张卡片地看直到找到你所需要的当然也许你碰巧很快就找到了
现在假设图书管理员将书的标题按字母顺序排列那么通过查看目录中的书的字母顺
序你可以很快地找到你想要的书
进一步设想如果管理员非常勤劳他不但将书按标题进行了排序而且还另外制作了
不同的卡片柜在那个卡片柜中他是按照作者的名字或其他的方式进行排序的那么这对
于你一个图书馆的读者来说检索信息就有了相当大的灵活性而且你只需要很短的时间
就能找到你所需要的内容
在数据库中使用索引可以让SQL 使用直接访问方式SQL 采用树形结构来存储和返回
索引数据用以指示的数据存储在树的最末端也就是叶子它们被称为结点也可以叫
叶子每一个结点中有一个指向其它结点的指针结点左边的值只是它的双亲结点结点
右边的值则是孩子结点或叶子
SQL 将从根结点开始直到找到所需要的数据
注当查询没有使用索引的表时查询通常是全表搜索后才会得到结果全表搜索会让数据
库服务程序遍历过表中的所有记录然后返回给定条件的记录这种方法就好比从图书
馆的第一号书架的第一本书找起直到找到了你所需要的书一样你或许会使用卡片
柜以更快地找到所需的书索引可以让数据库服务程序快速地定位到表中的确定行
幸运的是这个树结构不需要由你来制作你甚至不必去写从数据库的表中存储和读的
过程基本的SQL 索引的语法形式如下
INPUT/OUTPUT
SQL> CREATE INDEX index_name
2 ON table_name(column_name1, [column_name2], ...)
像你以前多次看到的那样索引的语法对于不同的数据库系统差别很大例如CREATE
INDEX 语句在ORACLE7 中的形式如下
SYNTAX
CREATE INDEX [schema.]index
ON { [schema.]table (column [!!under!!ASC|DESC]
[, column [!!under!!ASC|DESC]] ...)
| CLUSTER [schema.]cluster }
SQL 21 日自学通(V1.0) 翻译人笨猪
211
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[PCTFREE integer]
[NOSORT]
而它在Sybase SQL Server 中的语法形式则如下
SYNTAX
create [unique] [clustered | nonclustered]
index index_name
on [[database.]owner.]table_name (column_name
[, column_name]...)
[with {fillfactor = x, ignore_dup_key, sorted_data,
[ignore_dup_row | allow_dup_row]}]
[on segment_name]
Informix SQL 解释器的命令形式则如下
SYNTAX
CREATE [UNIQUE | DISTINCT] [CLUSTER] INDEX index_name
ON table_name (column_name [ASC | DESC],
column_name [ASC | DESC]...)
注意到所有这些解释器有几点是相同的它们的基本开始语句都是
CREATE INDEX index_name ON table_name (column_name, ...)
SQL Server 和ORACLE 允许你创建成簇的索引这将在稍后讨论ORACLE 和Informix
允许你指明列名是按升序排列还是按降序排列我们不喜欢听到被打断的声音但是请再
一次参考你的数据库管理系统以得到明确的关于CREATE INDEX 的指示
例如要对BILLS 表中的ACCOUNTID 字段创建索引其CREATE INDEX 语句如下
INPUT
SQL> SELECT * FROM BILLS
OUTPUT
SQL 21 日自学通(V1.0) 翻译人笨猪
212
NAME AMOUNT ACCOUNT_ID
Phone Company 125 1
Power Company 75 1
Record Club 25 2
Software Company 250 1
Cable TV Company 35 3
Joe's Car Palace 350 5
S.C. Student Loan 200 6
Florida Water Company 20 1
U-O-Us Insurance Company 125 5
Debtor's Credit Card 35 4
INPUT/OUTPUT
SQL> CREATE INDEX ID_INDEX ON BILLS( ACCOUNT_ID );
SQL> SELECT * FROM BILLS
NAME AMOUNT ACCOUNT_ID
Phone Company 125 1
Power Company 75 1
Software Company 250 1
Florida Water Company 20 1
Record Club 25 2
Cable TV Company 35 3
Debtor's Credit Card 35 4
Joe's Car Palace 350 5
U-O-Us Insurance Company 125 5
S.C. Student Loan 200 6
直至索引被DROP INDEX 语句删除之前BILLS 表是按照ACCOUNT_ID 的顺序进行
排序的DROP INDEX 语句是非常清楚的
SYNTAX
SQL> DROP INDEX index_name;
当索引被删除以后的结果是什么样呢
INPUT/OUTPUT
SQL> DROP INDEX ID_INDEX
Index dropped.
SQL> SELECT * FROM BILLS
SQL 21 日自学通(V1.0) 翻译人笨猪
213
NAME AMOUNT ACCOUNT_ID
Phone Company 125 1
Power Company 75 1
Record Club 25 2
Software Company 250 1
Cable TV Company 35 3
Joe's Car Palace 350 5
S.C. Student Loan 200 6
Florida Water Company 20 1
U-O-Us Insurance Company 125 5
Debtor's Credit Card 35 4
分析
现在的BILLS 表是它原本的形态使用索引不会对表中的物理存储造成影响
你也许想知道为什么数据库提供了索引而又允许你使用ORDER BY 子句吧
INPUT/OUTPUT
SQL> SELECT * FROM BILLS ORDER BY ACCOUNT_ID;
NAME AMOUNT ACCOUNT_ID
Phone Company 125 1
Power Company 75 1
Software Company 250 1
Florida Water Company 20 1
Record Club 25 2
Cable TV Company 35 3
Debtor's Credit Card 35 4
Joe's Car Palace 350 5
U-O-Us Insurance Company 125 5
S.C. Student Loan 200 6
分析
它与使用ID_INDEX 语句的结果是一样的不同之处在于当你使用ORDER BY 子句
时每次运行它都需要重新进行排序而当你使用索引的时候数据库会建立一个物理索引
对象就是前边提到的树结构而在你每次运行查询时都访问同一个索引
警告当表被删除时所有与表相关的索引也将被删除

使用索引的技巧
这里给出了几个在使用索引时需要记住的技巧
l 对于小表来说使用索引对于性能不会有任何提高
l 当你的索引列中有极多的不同的数据和空值时索引会使性能有极大的提高
l 当查询要返回的数据很少时索引可以优化你的查询比较好的情况是少于全部数据的
25% 如果你要返回的数据很多时索引会加大系统开销
l 索引可以提高数据的返回速度但是它使得数据的更新操作变慢在对记录和索引进
行更新时请不要忘记这一点如果要进行大量的更新操作在你执行更新操作时请不
要忘记先删除索引当执行完更新操作后只需要简单的恢复索引即可对于一次特
定的操作系统可以保存删除的索引18 个小时在这个时间内数据更新完后你可以恢
复它
l 索引会占用你的数据库的空间如果你的数据库管理系统允许你管理数据库的磁盘空
间那么在设计数据库的可用空间时要考虑索引所占用的空间
l 对字段的索引已经对两个表进行了归并操作这一技术可以极大地提高归并的速度
l 大多数数据库系统不允许你对视图创建索引如果你的数据库系统允许这样做那么
可以使用这种方法来在SELECT 语句中对视图的数据进行排序很不巧一些数据库
系统中也不允许在视图中使用ORDERY BY 子句
l 不要创建对经常需要更新或修改的字段创建索引更新索引的开销会降低你所期望获
得的性能
l 不要将索引与表存储在同一个驱动器上分开存储会去掉访问的冲突从而使结果返回
得更快
对更多的字段进行索引
SQL 也允许你对多个字段进行索引这种索引被称为复合索引下边的代码是一个简
单的复合索引的例子注意虽然是对两个字段进行索引但索引在物理结构上只有一个
INPUT/OUTPUT
SQL> CREATE INDEX ID_CMPD_INDEX ON BILLS( ACCOUNT_ID, AMOUNT );
Index created.
SQL 21 日自学通(V1.0) 翻译人笨猪
215
SQL> SELECT * FROM BILLS;
NAME AMOUNT ACCOUNT_ID
Florida Water Company 20 1
Power Company 75 1
Phone Company 125 1
Software Company 250 1
Record Club 25 2
Cable TV Company 35 3
Debtor's Credit Card 35 4
U-O-Us Insurance Company 125 5
Joe's Car Palace 350 5
S.C. Student Loan 200 6
SQL> DROP INDEX ID_CMPD_INDEX
Index dropped.
分析
选择唯一值最多的列建立索引可以达到你所希望的性能例如在BILLS 表中NAME
字段中的每一个值都是唯一的当使用复合索引时要把最可能选择的字段放在前边也
就是说把你最经常在查询中使用是字段放在最前边在CREATE INDEX 中列的出现次
序不必与表中的次序一致如果你经常使用下边的语句
SQL> SELECT * FROM BILLS WHERE NAME = "Cable TV Company";
为了想达到所期望的性能你必须在索引中将NAME 字段放在第一位这里有两个例
子
SQL> CREATE INDEX NAME_INDEX ON BILLS NAME, AMOUNT
或
SQL> CREATE INDEX NAME_INDEX ON BILLS NAME
在这两个例子中NAME 都在索引字段的最左边所以这两个索引可以提高对NAME
的查询的性能
复合索引也可以根据他们自己的选择性来对两个以上的字段进行索引作为一个选择
性的例子请看一下下边的这个表
ACCOUNT_ID TYPE BALANCE BANK
1 Checking 500 First Federal
2 Money Market 1200 First Investor's
SQL 21 日自学通(V1.0) 翻译人笨猪
216
ACCOUNT_ID TYPE BALANCE BANK
3 Checking 90 Credit Union
4 Savings 400 First Federal
5 Checking 2500 Second Mutual
6 Business 4500 Fidelity
请注意输出的六个记录checking 值在这里出现了三次所以它的选择性要低于
ACCOUUNT_ID 请注意每一个ACCOUNT_ID 的值都是唯一的要想提高你的索引的
选择性你可以将TYPE 字段与ACCOUNT_ID 字段组合在一起建立一个索引这将将创
建一个唯一的索引值当然这也是你所能得到的最高的选择性
注一个索引可以包含多个列通常是指复合索引复合索引的性能与单个字段的索引相比
是无法断定的以ORACLE 为例如果你在查询条件中经常指定某一特定的列那个你
可以创建这个列的索引而当你的查询需要复合条件时你可以创建复合索引当创建
多个索引的时候你需要参考你所选定的解释器的帮助信息以从中得到确定的复合索引
的用法
在创建索引时使用UNIQUE 关键字
复合索引通常使用UNIQUE 关键字来防止有相同数据的多个记录多次出现例如如
果你想要BILLS 表具有下边的规则每一个账单的交付公司都必须有不同的银行账号你
需要创建一个包括NAME 和ACCOUNT_ID 的唯一索引不幸的是ORACLE7 不支持
UNIQUE 语法它是用UNIQUE 完整性约束来达到内容唯一这一特性的下边的例子中给
出了在Sybase 的Transact-SQL 语言中UNIQUE 关键字的用法
INPUT
1> create unique index unique_id_name
2> on BILLS(ACCOUNT_ID, NAME)
3> go
1> select * from BILLS
2> go
OUTPUT
NAME AMOUNT ACCOUNT_ID
Florida Water Company 20 1
SQL 21 日自学通(V1.0) 翻译人笨猪
217
NAME AMOUNT ACCOUNT_ID
Power Company 75 1
Phone Company 125 1
Software Company 250 1
Record Club 25 2
Cable TV Company 35 3
Debtor's Credit Card 35 4
U-O-Us Insurance Company 125 5
Joe's Car Palace 350 5
S.C. Student Loan 200 6
现在我们试着向表中插入一个已经存在的记录
INPUT
1> insert BILLS (NAME, AMOUNT, ACCOUNT_ID)
2> values("Power Company", 125, 1)
3> go
分析
你会收到了个错误信息告诉你插入操作是不允许的这个错误可以为应用程序所捕获
从而告知用户他插入了一个不合法的数据
例10.3
在BILLS 表中创建一个索引以对AMOUNT 字段进行降序排列
INPUT/OUTPUT
SQL> CREATE INDEX DESC_AMOUNT
ON BILLS(AMOUNT DESC);
Index created.
分析
这是我们第一次使用DESC 操作它将告诉SQL 将索引降序排列通常情况下是升序
排列现在来看一下结果
INPUT/OUTPUT
SQL> SELECT * FROM BILLS;
NAME AMOUNT ACCOUNT_ID
Joe's Car Palace 350 5
Software Company 250 1
SQL 21 日自学通(V1.0) 翻译人笨猪
218
NAME AMOUNT ACCOUNT_ID
S.C. Student Loan 200 6
Phone Company 125 1
U-O-Us Insurance Company 125 5
Power Company 75 1
Cable TV Company 35 3
Debtor's Credit Card 35 4
Record Club 25 2
Florida Water Company 20 1
分析
这个例子对AMOUNT 列使用DESO 操作创建了一个索引注意输出的顺序是从大到
小

索引与归并
当在查询中使用了复杂的归并时你的SELECT 语句会耗用很长的时间对于大表来
说所用的时间可能会达到好几秒钟与你通常需要等待几毫秒相对比这样的性能在客
户机/服务器环境中常会令你的用户对使用你的应用程序感到不耐烦在归并时对字段创建
索引可以显著地提高你的查询反映速度但是如果你创建太多的索引就会使你的系统
的性能下降而不是提高我们推荐你在几个大表中进行索引试验对数以千计的数据排序
这样的试验可以让你更深入地理解SQL 查询的优化
注大多数的解释器有捕获查询耗用时间的机制ORACLE 将这种特性称为timing 请察
看你所使用的解释器的相关信息
下边的例子对BILS 表与BANK_ACCOUNT 表根据ACCOUNT_ID 字段创建了索引
INPUT/OUTPUT
SQL> CREATE INDEX BILLS_INDEX ON BILLS(ACCOUNT_ID);
Index created.
SQL> CREATE INDEX BILLS_INDEX2 ON BANK_ACCOUNTS(ACCOUNT_ID);
Index created.
SQL> SELECT BILLS.NAME NAME, BILLS.AMOUNT AMOUNT,
BANK_ACCOUNTS.BALANCE ACCOUNT_BALANCE
FROM BILLS, BANK_ACCOUNTS
SQL 21 日自学通(V1.0) 翻译人笨猪
219
WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID
NAME AMOUNT ACCOUNT_BALANCE
Phone Company 125 500
Power Company 75 500
Software Company 250 500
Florida Water Company 20 500
Record Club 25 1200
Cable TV Company 35 90
Debtor's Credit Card 35 400
Joe's Car Palace 350 2500
U-O-Us Insurance Company 125 2500
S.C. Student Loan 200 4500
分析
这个例子中首次在相关的表中为ACCOUNT_ID 字段创建了索引在每一个表中均对
ACCOUNT_ID 字段创建了索引以后归并就可以更快地访问特定行的数据作为一个规
则你应该对表中的唯一属性的字段或你用以归并操作的字段来创建索引
群集簇的使用
尽管在开始的时候我们曾经说过索引只是提供给用户的一种与数据的物理存在不同的
查看方式但是这话并不是绝对的在许多数据管理系统中都支持一种特殊的可以允许
数据库管理员或开发人员对数据进行群集的索引当使用群集索引时数据在表中的物理
排列方式将会被修改使用群集索引通常比传统的不使用群集的索引速度要快但是许
多数据库管理系统如Sybase 的SQL Server 只允许一个表有一个群集索引用于创建群
集索引的字段常常是主关键字用Sybase 的Transact-SQL 你可以对BANK_ACCOUNT 的
ACCOUNT_ID 字段创建一个群集的不重复的索引语法如下
SYNTAX
create unique clustered index id_index on BANK_ACCOUNTS(ACCOUNT_ID)
go
ORACLE 中群集的概念与此不同当使用ORACLE 关系数据库系统时群集就是一
个像数据或表一样的对象群集一般是存储了表的共有字段以提高对表的访问速度
这是一个ORACLE7 中创建群集的例子
SQL 21 日自学通(V1.0) 翻译人笨猪
220
SYNTAX
CREATE CLUSTER [schema.]cluster (column datatype [,column datatype] ... )
[PCTUSED integer] [PCTFREE integer] [SIZE integer [K|M] ]
[INITRANS integer] [MAXTRANS integer] [TABLESPACE tablespace]
[STORAGE storage_clause] [!!under!!INDEX | [HASH IS column] HASHKEYS integer]
你随后创建的其于该表的群集的索引会被加入到群集中然后把表也加入群集中你
应该只将经常需要归并的表加入到群集不要向群集中加入只需要用简单的SELECT 语句
进行个别访问的表
很明显群集是SQL 的第三方特性所以我们不准备详细地讨论创建和使用它的语法
的细节问题但是你要查看你的数据库系统的文档看它是否支持这一有用的特性
总结
视图是一种虚表视图是提供给用户的数据与其在数据库的真实面貌不相同的一种方
法CREATE VIEW 语法的语法使用了标准的SELECT 语法来创建了一个视图除了一些
小差别你可以将视图视为一个常规的表来执行插入删除更新和选择操作我们也简
要地提到了视图是实现数据库安全的一种重要方法有关数据的安全性将在第12 天作更详
细的讨论
基本的创建视图的语法如下
CREATE VIEW view_name AS SELECT field_name(s) FROM table_name(s)
视图主要用于以下方面
l 提高用户数据的安全性
l 进行单位换算
l 创建一个新格式的虚表
l 使复杂查询的构筑简单化
索引也是一种数据库设计和SQL 编程的工具索引是一种存储在你的数据库管理系统
中的物理对象它可以让你的查询更快地从数据库中返回数据此外索引是可以定制的
正确地在查询中使用索引可以使性能显著地提高
创建索引的基本语法如下
CREATE INDEX index_name ON table_name field_name s
SQL 21 日自学通(V1.0) 翻译人笨猪
221
在一些数据库系统中提供了一些非常有用的如UNIQUE 和CLUSTER 关键字附加选
项
问与答
问如果数据已经在我的数据库中进行了排序我是否还有必须在表中使用索引
答索引通过在树结构中查找关键值而提高你的数据库查询性能它比对数据库的顺序访
问方式快得多记住SQL 不需要知道你的数据库是否已经进行了排序
问我可以创建一个包括多个表中字段的索引吗
答你不能但是以ORACLE 为例它允许你创建一个群集你可以将表放入群集中以
根据表的共有字段创建一个群集索引这是它的一个例外所有你应该查看你所使用
的解释器的文档以找到这方法的详细解答
校练场
1 当在一个不唯一的字段中创建一个唯一值索引会有什么结果
2 下边的话是对是错
视图和索引都会占用数据库的空间所以在设计数据库空间时要考虑到这一点
如果一个从更新了一个已经创建视图的表那么视图必须进行同样的更新才会看到相同
的数据
如果你的磁盘空间够而你想加快你的查询的速度那么索引越多越好
3 下边的CREATE 语句是否正确
SQL> create view credit_debts as (select all from debts where account_id = 4)
4 下边的CREATE 语句是否正确
SQL> create unique view debts as select * from debts_tbl
5 下边的CREATE 语句是否正确
SQL> drop * from view debts
6 下边的CREATE 语句是否正确
SQL> create index id_index on bills account_id
SQL 21 日自学通(V1.0) 翻译人笨猪
222
练习
1 检查你所使用的数据库系统它是否支持视图允许你在创建视图时使用哪些选项用
它的语法来写一个简单的创建视图语句并对其进行如SELECT 和DELETE 等常规操
作后再删除视图
2 检查你所使用的数据库系统看它是否支持索引它有哪些选项在你的数据库系统中的
一些已经存在的表中试一下这些选项进一步确认在你的数据库系统中是否支持
UNIQUE 和CLUSTER 索引
3 如果可能的话在一个表中输入几千条记录用秒表或钟来测定一下你的数据库系统对
特定操作的反映时间加入索引是否使性能提升了试一下今天提到的技巧

第11 天事务处理控制
前十天中我们学习了实际上我们可以对关系数据库系统中的数据所做的每一件事例
如我们已经知道了如何使用SQL 的SELECT 语句根据用户给定的条件从一个或多个表
中获得数据我们也有机会体验了数据修改语句如INSERT DELETE UPDATE 在今天
我们将成为中级SQL 用户如果有必要我们将建立一个数据库及其相关的表每一个表
中都包括几个不同类型的字段通过合适的设计方法我们会成为从数据库到应用程序的
桥梁
目标
如果你是一个临时用户只需要偶而使用SQL 从数据库获得数据的话那么前十天的
的主题已经为你提供了足够的内容但是如果你想开发可以在使用数据库系统下运行的
专业应用程序这在当前在很普遍的那么你在今后四天中讲到的内容事务控制安全
内嵌SQL 语句数据库过程将会对你有很大的帮助我们先从事务控制开始到今天的
结束我们将学会以下内容
l 基本的事务控制
l 如何确认或终止某一项事务
l Sybase 与Oracle 在事务处理上的不同之处
注在今天的例子中我们使用PERSONAL ORACLE 7和SYBASE SQL SERVER 对于你
所使用的解释器请查看相应的帮助文档以找出它们的不同之处
事务控制
事务控制或者说事务处理是指关系数据库系统执行数据库事务的能力事务是指在逻
辑上必须完成的一命令序列的单位单元工作期是指事务的开始和结束时期如果在事务
中产生的错误那么整个过程可以根据需要被终止如果每一件事都是正确的那么结果
将会被保存到数据库中
日后你也许会运行其于网络的多用户应用程序客户/服务环境就是为它而设计的传
SQL 21 日自学通(V1.0) 翻译人笨猪
224
统上的服务器例如数据库服务器支持多个与它连接的工作站与其它技术一样新特
性提高了数据库的复杂程度下边的几段描述了一个银行所使用的应用程序
银行应用程序
假定你受雇于联邦银行并负责为他们设计一个支票管理系统你已经设计了一个非常
完美的数据库并且经常测试检验证明是正确无误的你在应用程序中调用它以后你从
账号中支取了20 元并进行验证数据库中确实已经少了20 元你又从帐号中存入了50.25
元并进行验证结果也与所期望的相同于是你骄傲在告诉你的老板系统可以运行了几
台计算机接入了程序并开始工作
几分钟以后你注意到了一个你没有预见的问题一个出纳员向帐号中存入了一张支
票而另一个出纳则从相同的帐号中提出了一部分钱在分钟之内由于多用户的同时操作
就导致的帐目无法平衡很不幸由于他们之间互相进行更新和写入操作你的应用程序
很快就因为过负荷而断线我们假定出现这个问题的数据库名字叫CHECKING 它有两个
表其内容如下所示
表11.1
Name Address City State Zip Customer_ID
Bill Turner 725 N. Deal Parkway Washington DC 20085 1
John Keith 1220 Via De Luna Dr. Jacksonville FL 33581 2
Mary Rosenberg 482 Wannamaker Avenue Williamsburg VA 23478 3
David Blanken 405 N. Davis Highway Greenville SC 29652 4
Rebecca Little 7753 Woods Lane Houston TX 38764 5
表11.2
Average_Bal Curr_Bal Account_ID
1298.53 854.22 1
5427.22 6015.96 2
211.25 190.01 3
73.79 25.87 4
1285.90 1473.75 5
1234.56 1543.67 6
345.25 348.03 7
假定你的应用程序为BILL Turner 运行了SELECT 查询并得到如下结果
OUTPUT
SQL 21 日自学通(V1.0) 翻译人笨猪
225
NAME Bill Turner
ADDRESS 725 N. Deal Parkway
CITY Washington
STATE DC
ZIP 20085
CUSTOMER_ID 1
当返回数据的时候另外一个用户连接到了数据库并更新了BILL Turner 的住址信息
INPUT
SQL> UPDATE CUSTOMERS SET Address ="11741 Kingstowne Road"
WHERE Name = "Bill Turner"
你现在看到了如果在你执行SELECT 语句当中出现的更新操作的话那么你所得到的
结果将是不正确的如果你的应用程序可以生成一个信件给Bill Turner 那么由于地址是
错误的如果信已经发送了那你是不能对地址进行修改的但是如果你使用了事务处理
机制那么你就可以对检测到错误的数据进行修改你所进行的所有操作也都可以撤消
开始事务处理
事务处理在执行上是非常简单的你需要检查你所执行的语法是Oracle RDBMS SQL
语法还是Sybase SQL Server SQL 语法
所有支持事务处理的系统都必须以一种准确的语法来告诉系统一项事务是如何开始的
不要忘记事务处理只是工作的逻辑分组它有自己的开始和结束在使用PERSONAL
ORACLE7 时它的语法形式如下
SYNTAX
SET TRANSACTION {READ ONLY | USE ROLLBACK SEGMENT segment}
SQL 标准要求每一种数据库的SQL 解释器都必须运支持语句级的读一致这也就是
说当某一条语句运行的时候数据必需保持不变但是在许多情况下在一个工作过程中
必须要求数据保持有效而不仅仅是对单个语句ORACLE 允许用户用SET TRANSACTION
来指定事务的开始如果你想检查BILL TUNER 的信息并且要保证数据在这之中是不能改
变的那么你可以使用如下语句
INPUT
SQL 21 日自学通(V1.0) 翻译人笨猪
226
SQL> SET TRANSACTION READ ONLY
SQL> SELECT * FROM CUSTOMERS
WHERE NAME = 'Bill Turner';
SQL> COMMIT
我们将在今天的晚些时候来讨论COMMIT 语句这里的SET TRANSACTION READ
ONLY 允许你锁定一个记录集直到事务结束你可以在下列语句中使用READ ONLY 选项
SELECT
LOCK TABLE
SET ROLE
ALTER SESSION
ALTER SYSTEM
选项USE ROLLBACK SEGMENT 告诉ORACLE 数据库提供数据回溯的存储空间段
这一选项是ORACLE 对标准的SQL 的扩展如果需要维护你的数据库请参见ORACLE 的
帮助文档以获得更多的帮助信息
SQL Server's Transact-SQL 语言用下边的方法来实现了开始事务处理的命令
SYNTAX
begin {transaction | tran} [transaction_name]
它的实现方法与ORACLE 的有一些不同SYBASE 不允许你指定READ ONLY 选
项但是SYBASE 允许你给出事务处理的名字从最早的事务到最近发生的事务处理都
可以一次退回
INPUT
1> begin transaction new_account
2> insert CUSTOMERS values ("Izetta Parsons", "1285 Pineapple Highway", "Greenville", "AL"
32854, 6)
3> if exists(select * from CUSTOMERS where Name = "Izetta Parsons")
4> begin
5> begin transaction
6> insert BALANCES values(1250.76, 1431.26, 8)
7> end
8> else
SQL 21 日自学通(V1.0) 翻译人笨猪
227
9> rollback transaction
10> if exists(select * from BALANCES where Account_ID = 8)
11> begin
12> begin transaction
13> insert ACCOUNTS values(8, 6)
14> end
15> else
16> rollback transaction
17> if exists (select * from ACCOUNTS where Account_ID = 8 and Customer_ID = 6)
18> commit transaction
19> else
20> rollback transaction
21> go
现在请不要担心ROLLBACK TRANSACTION 和COMMIT TRANSACTION 语句
重要的问题是这是一个内嵌的事务处理或者说是事务处理之中还有事务处理
注意最开始的事务处理在第1 行之后是插入语句你检查了插入确实已经执行了
以后第二个事务处理在第5 行开始这种在事务之中的事务在术语上称为内嵌事务
有一些数据库支持AUTOCOMMIT 选项它可以在SET 命令中使用如下例
SET AUTOCOMMIT [ON | OFF]
默认情况上SET AUTOCOMMIT ON 命令在启动时是自动运行的它告诉SQL 自动确
认你所运行的所有的语句如果你不想让这个命令自动运行那么请将它的参数设为NO
SET AUTOCOMMIT OFF
注请检查你的数据库文档确认在你的数据库系统中一项事务处理是如何开始的

结束事务处理
在ORACLE 语法中结束事务处理语句的语法如下
SYNTAX
COMMIT [WORK]
[ COMMENT 'text'
SQL 21 日自学通(V1.0) 翻译人笨猪
228
| FORCE 'text' [, integer] ] ;
它的命令语法与Sybase 的语法是相同的
语法
COMMIT (TRANSACTION | TRAN | WORK) (TRANSACTION_NAME)
COMMIT 命令将保存在一项事务中所进行的所有的改变在开始一项事务处理之前要
先运行COMMIT 命令以确保在之前没有事务未被确认
在下边的例子中如果COMMIT 没有收到任何系统错误的情况下它将会执行确认
INPUT
SQL> COMMIT;
SQL> SET TRANSACTION READ ONLY;
SQL> SELECT * FROM CUSTOMERS
WHERE NAME = 'Bill Turner';
---Do Other Operations---
SQL> COMMIT;
在ORACLE 中COMMIT 语句的使用方法如下
INPUT
SQL> SET TRANSACTION;
SQL> INSERT INTO CUSTOMERS VALUES
("John MacDowell", "2000 Lake Lunge Road", "Chicago", "IL", 42854, 7);
SQL> COMMIT;
SQL> SELECT * FROM CUSTOMERS;
CUSTOMER 表的内容如下
Name Address City State Zip Customer_ID
Bill Turner 725 N. Deal Parkway Washington DC 20085 1
John Keith 1220 Via De Luna Dr. Jacksonville FL 33581 2
Mary Rosenberg 482 Wannamaker Avenue Williamsburg VA 23478 3
David Blanken 405 N. Davis Highway Greenville SC 29652 4
Rebecca Little 7753 Woods Lane Houston TX 38764 5
Izetta Parsons 1285 Pineapple Highway Greenville AL 32854 6
John MacDowell 2000 Lake Lunge Road Chicago IL 42854 7
而Sybase SQL 使用COMMIT 的语法方式如下
SQL 21 日自学通(V1.0) 翻译人笨猪
229
INPUT
1>begin transaction
2>insert into CUSTOMERS values
("John MacDowell", "2000 Lake Lunge Road", "Chicago", "IL", 42854, 7)
3>commit transaction
4>go
1>select * from CUSTOMERS
2>go
Name Address City State Zip Customer_ID
Bill Turner 725 N. Deal Parkway Washington DC 20085 1
John Keith 1220 Via De Luna Dr. Jacksonville FL 33581 2
Mary Rosenberg 482 Wannamaker Avenue Williamsburg VA 23478 3
David Blanken 405 N. Davis Highway Greenville SC 29652 4
Rebecca Little 7753 Woods Lane Houston TX 38764 5
Izetta Parsons 1285 Pineapple Highway Greenville AL 32854 6
John MacDowell 2000 Lake Lunge Road Chicago IL 42854 7
上边的语句完成了与ORACLE7 相同的功能但是在使用COMMIT 确认事务处理
之前你应该确保在该事务中的工作是正确无误的
注COMMIT WORD 命令与COMMIT 命令的作用是相同的或Sybase 中的COMMIT
TRANSACTION 它与ANSI SQL 的语法一样的简单
切记COMMIT 语句一定要与之前的SET TRANSCATION 或BEGIN TRANSCATION
语句一致注意在下边的语句中你将会收到错误信息
Oracle SQL
INPUT
SQL> INSERT INTO BALANCES values (18765.42, 19073.06, 8);
SQL> COMMIT WORK;
Sybase SQL
INPUT
1> insert into BALANCES values (18765.42, 19073.06, 8)
2> commit work
SQL 21 日自学通(V1.0) 翻译人笨猪
230
取消事务处理
在一个事务处理的过程中常常会运行一些错误检查以确认在过程中是否语句是运行
成功你可以使用ROLLBACK 语句来撤消事务中所做的每一项工作即便工作是成功的
你也可以撤消但是这必须是在COMMIT 之前ROLLBACK 语句必须在一个事务之中
运行它可以一直撤消到事务的开始也就是说数据库会一直返回到事务处理刚开始的
状态在ORACLE 7 中它的语法形式如下
SYNTAX
ROLLBACK [WORK]
[ TO [SAVEPOINT] savepoint
| FORCE 'text' ]
如你所见该命令可以设置事务的SAVEPOINT 我们将在今天的晚些时候来讨论这
项技术
Sybase Transact-SQL's 的ROLLBACK语句与COMMIT 语句非常相似
SYNTAX
rollback {transaction | tran | work} [transaction_name | savepoint_name]
一个ORACLE 的命令序列如下
INPUT
SQL> SET TRANSACTION;
SQL> INSERT INTO CUSTOMERS VALUES
("Bubba MacDowell", "2222 Blue Lake Way", "Austin", "TX", 39874, 8);
SQL> ROLLBACK;
SQL> SELECT * FROM CUSTOMERS;
Name Address City State Zip Customer_ID
Bill Turner 725 N. Deal Parkway Washington DC 20085 1
John Keith 1220 Via De Luna Dr. Jacksonville FL 33581 2
Mary Rosenberg 482 Wannamaker Avenue Williamsburg VA 23478 3
David Blanken 405 N. Davis Highway Greenville SC 29652 4
Rebecca Little 7753 Woods Lane Houston TX 38764 5
Izetta Parsons 1285 Pineapple Highway Greenville AL 32854 6
John MacDowell 2000 Lake Lunge Road Chicago IL 42854 7
SQL 21 日自学通(V1.0) 翻译人笨猪
231
而A Sybase SQL 的命令序列则如下
INPUT
1> begin transaction
2> insert into CUSTOMERS values
("Bubba MacDowell", "2222 Blue Lake Way", "Austin", "TX", 39874, 8)
3> rollback transaction
4> go
1> SELECT * FROM CUSTOMERS
2> go
Name Address City State Zip Customer_ID
Bill Turner 725 N. Deal Parkway Washington DC 20085 1
John Keith 1220 Via De Luna Dr. Jacksonville FL 33581 2
Mary Rosenberg 482 Wannamaker Avenue Williamsburg VA 23478 3
David Blanken 405 N. Davis Highway Greenville SC 29652 4
Rebecca Little 7753 Woods Lane Houston TX 38764 5
Izetta Parsons 1285 Pineapple Highway Greenville AL 32854 6
John MacDowell 2000 Lake Lunge Road Chicago IL 42854 7
你也看到了由于使用了ROLLBACK 命令撤消了INSERT 命令新的记录并没有被
加入到表中
如果你写了一个图形用户界面的应用程序比如MICRO WINDOWS 你可以做一个
数据库查询对话框以便让用户在其中输入数值如果用户按下了确定按钮那么数据
库将会保存所做的改动如果用户按下了取消按钮那么所有的更改就会被取消显
然这种情况给予了你使用事务处理的机会
注下边的代码给出的ORACLE SQL 中的使用方法注意这里有SQL>并且有行号
在随后给出的Sybase SQL syntax 中则没有SQL>提示符
当对话框载入后这些SQL 语句将会运行
INPUT
SQL> SET TRANSACTION;
SQL> SELECT CUSTOMERS.NAME, BALANCES.CURR_BAL, BALANCES.ACCOUNT_ID
2 FROM CUSTOMERS, BALANCES
3 WHERE CUSTOMERS.NAME = "Rebecca Little"
SQL 21 日自学通(V1.0) 翻译人笨猪
232
4 AND CUSTOMERS.CUSTOMER_ID = BALANCES.ACCOUNT_ID;
该对话框允许用户更改当前的结算账号所以你需要将该数据返回给数据库
当按下OK 按钮以后UPDATE将会运行
INPUT
SQL> UPDATE BALANCES SET CURR_BAL = 'new-value' WHERE ACCOUNT_ID = 6;
SQL> COMMIT
如果用户按下了CANCEL 那么将会运行ROLLBACK命令
INPUT
SQL> ROLLBACK
当该对话框在Sybase SQL 中被载入以后将会运行下边的语句
INPUT
1> begin transaction
2> select CUSTOMERS.Name, BALANCES.Curr_Bal, BALANCES.Account_ID
3> from CUSTOMERS, BALANCES
4> where CUSTOMERS.Name = "Rebecca Little"
5> and CUSTOMERS.Customer_ID = BALANCES.Account_ID
6> go
该对话框允许用户改变当前的结算账号当你将该数据返回给数据库以后并按下OK
按钮时UPDATE语句将会运行
INPUT
1> update BALANCES set Curr_BAL = 'new-value' WHERE Account_ID = 6
2> commit transaction
3> go
如果用户选择了CANCEL 按钮那么将会执行ROLLBACK的语句
INPUT
1> rollback transaction
2> go
ROLLBACK 语句将会终止整个事务当存在嵌套事务时ROLLBACK 将会终止掉全
部事务系统将会返回到事务开始的最初状态
如果当前没有活动的事务时ROLLBACK 或COMMIT 语句将不会对数据库产生任何作用你可以认为这是一个无效的命令
在COMMIT 语句运行以后在事务中的所有动作都会得到确认这时在使用
ROLLBACK命令就太晚了

在事务中使用保存点
在事务中使用ROLLBACK 可以取消整个的事务但是你也可以在你的事务当中使用
语句进行部分地确认在Sybase 和Oracle 中都允许你在当前事务中设一个保存点从
这一点开始如果你使用了ROLLBACK 命令那么系统将会回到保存点时的状态而在
保存点之前的语句将会得到确认在ORACLE 中创建一个保存点的语法格式如下
SYNTAX
SAVEPOINT savepoint_name;
在SYBASE 中创建保存点的语法格式如下
SYNTAX
save transaction savepoint_name
下边是使用ORACLE 语法的例子
INPUT
SQL> SET TRANSACTION
SQL> UPDATE BALANCES SET CURR_BAL = 25000 WHERE ACCOUNT_ID = 5
SQL> SAVEPOINT save_it
SQL> DELETE FROM BALANCES WHERE ACCOUNT_ID = 5
SQL> ROLLBACK TO SAVEPOINT save_it
SQL> COMMIT
SQL> SELECT * FROM BALANCES
结算平衡表的内容如下
Average_Bal Curr_Bal Account_ID
1298.53 854.22 1
5427.22 6015.96 2
211.25 190.01 3
73.79 25.87 4
1285.90 25000.00 5
SQL 21 日自学通(V1.0) 翻译人笨猪
234
1234.56 1543.67 6
345.25 348.03 7
1250.76 1431.26 8
下边是使用Sybase 语法的例子
INPUT:
1> begin transaction
2> update BALANCES set Curr_Bal = 25000 where Account_ID = 5
3> save transaction save_it
4> delete from BALANCES where Account_ID = 5
5> rollback transaction save_it
6> commit transaction
7> go
1> select * from BALANCES
2> go
Average_Bal Curr_Bal Account_ID
1298.53 854.22 1
5427.22 6015.96 2
211.25 190.01 3
73.79 25.87 4
1285.90 25000.00 5
1234.56 1543.67 6
345.25 348.03 7
1250.76 1431.26 8
在上边的例子中创建了一个叫SAVE_IT 的保存点UPDATE 语句更新了结算平衡表中
的CURR_BAL 列你在其后设置了一个保存点在保存之后你又运行了DELETE 命令
系统退回到了保存点处之后你对事务用COMMIT 命令进行了确认结果所有在保存点
之前的命令得到了确认
如果你在其后又使用了ROLLBACK 命令那么将会取消当前的事务而不会有任何的
改变
在ORACLE 中的例子如下
INPUT
SQL> SET TRANSACTION
SQL 21 日自学通(V1.0) 翻译人笨猪
235
SQL> UPDATE BALANCES SET CURR_BAL = 25000 WHERE ACCOUNT_ID = 5
SQL> SAVEPOINT save_it
SQL> DELETE FROM BALANCES WHERE ACCOUNT_ID = 5
SQL> ROLLBACK TO SAVEPOINT save_it
SQL> ROLLBACK
SQL> SELECT * FROM BALANCES
BALANCE 表的内容如下
Average_Bal Curr_Bal Account_ID
1298.53 854.22 1
5427.22 6015.96 2
211.25 190.01 3
73.79 25.87 4
1285.90 1473.75 5
1234.56 1543.67 6
345.25 348.03 7
1250.76 1431.26 8
Sybase SQL 语法的例子如下
INPUT
1>begin transaction
2>update BALANCES set Curr_Bal = 25000 where Account_ID = 5
3>save transaction save_it
4>delete from BALANCES where Account_ID = 5
5>rollback transaction save_it
6>rollback transaction
7>go
1>select * from BALANCES
2>go
Average_Bal Curr_Bal Account_ID
1298.53 854.22 1
5427.22 6015.96 2
211.25 190.01 3
73.79 25.87 4
SQL 21 日自学通(V1.0) 翻译人笨猪
236
1285.90 1473.75 5
1234.56 1543.67 6
345.25 348.03 7
1250.76 1431.26 8
总结
事务可以被定义为一个有组织的工作单元事务通常会执行一系列的以前学过的操作
如果由于一些原因使得操作没有如所期望地执行那么可以在事务中取消这些操作反之
如果操作全部正确执行了那么事务中的工作可以确认
可以使用ROLLBACK 命令来取消事务确认事务的命令为COMMIT SQL 用非常相
似的语法来支持这两类过程
SYNTAX
BEGIN TRANSACTION
statement 1
statement 2
statement 3
ROLLBACK TRANSACTION
或
SYNTAX
BEGIN TRANSACTION
statement 1
statement 2
statement 3
COMMIT TRANSACTION
问与答
问如果我有一组事务其中一个是不成功的我是否可以确认其它的事务过程
答不可以必须整组的事务都是成功的才可以
问在使用的COMMIT 命令以后我发现我犯了一个错误那么我怎样才能更正这个错
SQL 21 日自学通(V1.0) 翻译人笨猪
237
误
答使用DELETE INSERT 或UPDATE 语句ROLLBACK在这时是不行的
问在怎个事务结束以后我都必须使用COMMIT 命令确认吗
答不必但是在确认没有错误而且在之前没有事务在运行时使用COMMIT 会更安全

校练场
1 在嵌套的事务中是否可以使用ROLLBACK 命令来取消当前事务并回退到上级事务
中为什么
2 使用保存点是否可以保存事务的一部分为什么
3 COMMIT 命令是否可以单独使用它一定要嵌套吗
4 如果你在COMMIT 命令后发现的错误你是否还可以使用ROLLBACK命令
4 在事务中使用保存点是否可以自动地将之前的改动自动地保存
练习
1 使用PERSONAL ORACLE7 的语法来更正下边的语法
SQL> START TRANSACTION INSERT INTO CUSTOMERS VALUES ('SMITH', 'JOHN')
SQL> COMMIT
2 使用PERSONAL ORACLE7 的语法来更正下边的语法
SQL> SET TRANSACTION
UPDATE BALANCES SET CURR_BAL = 25000
SQL> COMMIT
3 使用PERSONAL ORACLE7 的语法来更正下边的语法
SQL> SET TRANSACTION
INSERT INTO BALANCES VALUES ('567.34', '230.00', '8')
SQL> ROLLBACK