第19学时修改数据

第19学时修改数据
在大多数商业应用程序中，你的用户将通过应用程序自身来修改数据库的表。然而，你
必须知道如何手工修改表中包含的数据，这一点是非常重要的。当我们谈论修改数据时，实
际上是指插入数据、更改数据和删除数据。这一点之所以重要，是因为你可以清除误输入的
数据、更改过时的数据，或是简单地删除旧数据。
本学时的要点包括：
• 从何入手
• 插入数据
• 更新数据
• 锁定
19.1 从何入手
当我们考虑修改数据的方方面面时，从何处入手是一个大问题。你拥有数据库的理由是
为了给你的公司收集有用的数据。这类数据可以是订单数据、产品性能数据或者用户动态数
据。过去，大量类似的数据被收集、存档，然后被遗忘。现在，随着电子数据库的使用，你
可以存储这些数据并用于为管理分析趋势和创建报表。就这一点而言，你可能会考虑最好从
哪儿开始？现在该记住在你的所有用户数据库中的数据都是极重要的，你必须分外小心并尽
最大努力保护这些数据。
本学时中我们涉及的许多主题是很有用的，但是注意不要在实际使用的成品数据库中试
用。如果必要的话，系统安装的两个用户数据库( p u b s和N o r t h w i n d )可供试用和破坏。你想要
重建它们也很简单。但是重建一个成品数据库或在成形的系统中重新创建数据却很难。提出
这点警告之后，下面我们进入数据修改的学习。
19.2 插入数据
顾名思义，这部分讲述的是如何在数据库中插入数据。虽然这不是真的数据修改，而更
像创建数据，但它却是其他所有数据修改过程的第一步。毕竟，表中的数据总是从某处产生
的。通过SQL INSERT语句可以把数据加到表里。这条语句比较容易，使用规则很少。
必须记住最基本的规则是，当你对表执行I N S E RT操作时，实际上是向表内插入了一个行。
如果你没有为表的某一字段指定值， SQL Server将提供该字段已定义的缺省值或插入该列允
许的空值。如果你没有为特定的字段指定值并且定义了该字段不允许空值输入， SQL Server
将产生一个错误并终止语句执行。换句话说，将没有值输入到表里。
正如前面我们所提到的， I N S E RT语句相对来说比较简单。它仅由两部分组成，如程序清
单1 9 - 1所示。第一部分是I N S E RT关键字本身，后面紧跟着被插入数据的表的名称。你也可以
在I N S E RT关键字与表名称之间插入I N TO关键字。虽然这不是必须的，但是如果它更有助于
理解，你不妨使用此关键字。如果你不打算向表的所有字段都插入值，可以在表名后的圆括
弧内，将要输入数据的列列出来。第一部分有时也被称为I N S E RT子句。该语句的第二部分是
VA L U E S关键字，紧跟着将被存入行的所有数值的列表。这常被称作VA L U E S子句或值列表。
程序清单19-1 INSERT 语法
如果没有指定欲插入数值的列的名称，就必须给表中的每一列都准备一个插入值。例如，
如果你的表中有五个列，你就必须列出将要插入表的五个值，否则就会出错。如果列出将要
被插入的列，你还须确保在值列表中准备了同样数量的值，否则也会出错。
你可能会遇到的一个问题是如何判定表中指定列是否可以进行空值操作。当你在S Q L
Query Analyzer中工作时，将会有一个很简单的方法来完成此操作。s p h e l p系统存储过程可
被用来判定各种有关你将用到的表的有用信息。程序清单1 9 - 2显示了s p _ h e l p存储过程对于
p u b s数据库的a u t h o r s表的用法。
程序清单19-2 sp_help语法
当执行这个命令时， SQL Server将返回大量关于表和表结构的信息。你可以找到所有的
列名称，列的数据类型，是否可为空值，表上有哪些索引，等等。该数据以很宽的格式返回
( 4 2 6个字符宽)，这样你为了找到所需的信息就必须进行大量滚动工作，不过也很值得。你可
以在图1 9 - 1中看到程序清单1 9 - 2的部分运行结果。
现在你知道了I N S E RT语句是什么样子，并知道从哪里找有关表的信息。让我们看看如何
往数据库里装一些数据。我们的许多例子用到了p u b s数据库。记住，不要在成品数据库中试
用这些语句。
第一个例子如程序清单1 9 - 3所示，我们将向a u t h o r s表执行一条I N S E RT语句。假设新作者
可以及时地向需要的人提供她的全部信息，所以当我们要将这些数据录入数据库时，我们需
第1 9学时修改数据1 8 5
下载
图19-1 程序清单1 9 - 2的运行
结果
要的一切都已齐备。这时不必列出所有的列，因为我们要向所有列进行插入。
程序清单19-3 向数目有限的列中插入
如果键入这条语句并执行它，你将得到一条信息说明仅有一行受到影响。目前来看，这
并不重要，但是当我们进一步使用I N S E RT和D E L E T E语句时，如果只想修改两行，它却告诉
你修改了两百行的话，就需要好好研究一下了。
程序清单1 9 - 3运行的查询很直接。我们告诉SQL Server按照表原有的排列顺序将数值列插
入表。注意，如果插入列的数据为字符串类型，如C H A R或VA R C H A R，要将其放在单引号中。
任何插入数值型列的数据都不能放在单引号中。这里，我们来看一个向a u t h o r s表再次插入数
据查询。但是这一次我们并未从作者那儿获得所需的全部信息，这次我们只知道作者的名字。
如果你查看表，你将看到a u i d列和c o n t r a c t列也是不能为空值的。作者标识( a u i d )是用来区分
作者的唯一编码。在此表的数据中，我们使用的数据是作者的社会保障号码。但是，如果我
们没有那个号码，也可以用一个同样长的唯一编码替补。c o n t r a c t列是合同号。现在，我们不
得不写I N S E RT语句将数据插入这些列中，如程序清单1 9 - 4所示。
程序清单19-4 插入需要的列
我们在程序清单1 9 - 4中所做的就是列出一些列，同时在VA L U E S子句中列出了将要添加到
这些列中的值。因为表中的其他列允许空值，所以这条I N S E RT语句可以工作。我们仅将值插
入了上述五个列中，而其他列插入的是什么呢？你运行一下程序清单1 9 - 5中的查询就能明白
了。
如果你看到了如图1 9 - 2所示的查询运行结果，就会发现大多数你没有输入值的列中都包
1 8 6 SQL Server 7 24学时教程
下载
图19-2 程序清单1 9 - 5的查询
结果
含了空值。尽管这有点怪。如果你查看p h o n e列，会发现单词U N K N O W N作为值列出来。这
不是SQL Server决定的，而是因为创建表的人已经定义了一个缺省值U N K N O W N，输入空值
时这个值会插入到表中。
程序清单19-5 检查缺省值
19.2.1 缺省值
在没有别的数据，又必须向数据库插入值时，由数据库的开发者来决定某些值的情况很
普遍。这些值可以是任何值，从刚才我们看到的插入到a u t h o r s表p h o n e列中的U N K N O W N，
到用户名和插入到一个列的日期。我们已经知道怎样在进行插入操作之后得到缺省值的显示。
你只要将不需要的列从列清单中去掉，剩下的工作SQL Server将替你完成。但是如果你没有
列出所有要插入的列，而又要进行I N S E RT 操作，如程序清单1 9 - 3所示，那将会发生什么情况
呢？这种情况下，你可能要使用D E FA U LT关键字了。例如，让我们回顾一下程序清单1 9 - 3中
的I N S E RT语句。此时我们知道除电话号码以外将要插入的所有值。你可能不想列出所有的列
名，除了必要的那一个以外—那会增加许多不必要的打字工作。取而代之的是，你可以按
照程序清单1 9 - 6来做。注意我们已经在此例中改动过a u i d，否则你可能无法运行它。
程序清单19-6 使用D E FA U LT关键字
我们已经告诉SQL Server将缺省值插入该列。当使用缺省值时，请注意以下事项：
• 如果该列定义了缺省值的话，缺省值将被插入该列。
• 如果该列没有定义缺省值，但是允许空值的话，空值将被插入。
• 如果该列是时间戳数据类型，则下一个有效的时间戳将被插入。
• 如果该列不满足上述任何一种情况，则I N S E RT将报错而不能执行。
另有一种不多见的情况是表的所有列都包含缺省值，而你要往表里插入缺省值的话，运
行以下语句就可以了：
这里< t a b l e n a m e >是表的名称。
19.2.2 使用SELECT语句的插入
最后我们将要看到的一种插入数据的方法，是将别的表里的数据选择出来用于插入。这
种方法可以用来生成摘要数据或填充一个新的表拷贝。用于执行基于S E L E C T语句的插入的
I N S E RT语句，在语法上与标准的I N S E RT语句有所不同，其语法如下：
举个例子来说，我们在第1 7学时“数据查询”中用到了SELECT 语句。我们写了一条
第1 9学时修改数据1 8 7
下载
S E L E C T语句，用它来返回a u t h o r s表中所有作者的名字和电话号码。它可以方便地得到存储
在他们自己表中的所有名字和电话号码，对吗？程序清单1 9 - 7中的代码可以建立一个名为
p h o n e l i s t的表，其中包含名为a u t h o r s n a m e和p h o n e n u m b e r的两个列。表建好以后，就可以
插入合适的数据了，所用语句即为我们前面曾经用来收集同样数据的S E L E C T语句。最后，我
们就将新表内的所有行和列都选出来。
程序清单19-7 基于S E L E C T语句的插入
如果键入以上语句并执行，而且已经运行了所有的插入语句的话，你可以得到一条返回
信息，说有2 6行产生变化，接着便是这2 6行。
现在你应该可以很好的应用I N S E RT语句了。下面我们要转移到修改表中已有数据的问题
上了。
19.3 更新数据
商业应用要收集数据并存入数据库，数据改动是免不了的。通常，几乎所有的用户数据库
中的大部分数据都要进行某种程度的修改。以一个含有供应商信息的数据库为例，在表内有
一条名为G a t e w a y的计算机公司的记录。一年多以前，大多数人会认为该公司总部设在N o r t h
D a k o t a州。但是最近，G a t e w a y公司将总部从North Dakota迁到了San Diego。这看起来像一堂
离题的历史课，但若没办法更新数据库中的这个地址的话，我们就会遇到一些麻烦。
U P D AT E语句就是为了改变数据库中的现存数据而存在的。这条语句虽然有一些复杂的
选项，但确实是最容易学习的语句之一。这是因为在大多数情况下，这条语句的高级部分很
少使用。在用户看来， U P D AT E语句只是用来改变指定行中的数据。但实际的内部情况是，
SQL Server从表中删除旧的数据行并插入新行。
U P D AT E语句的语法如下：
选项的意义如下：
选项描述
< t a b l e n a m e > 表的名称。该表包含了要修改值的列
< c o l u m n n a m e > 要修改数据的列的名称
1 8 8 SQL Server 7 24学时教程
下载
(续)
选项描述
< v a l u e > 要输入到列中的新值
< s e a r c h c o n d i t i o n > 这是U P D AT E语句中最重要的部分。通过指定一个好的搜索条件，你能够限
定表内被修改的行数。如果你不指定搜索条件， SQL Server会用新值修改表
内的所有行
现在我们来看看如何实际修改表中的某些行。我们在表中有一列使用了唯一值，可以区
分表中的每一行。因此，我们可以轻松地写下U P D AT E语句，只改变对应某作者的那行数据。
程序清单1 9 - 8将先从表中选中那一行并更新，然后再选该行以显示前后的区别。
程序清单19-8 UPDAT E语句的使用
这个程序的运行结果如图1 9 - 3所示，可以看出第一次查询到的那个行已经被新值更新过
了。
现在，我们来回顾一下t i t l e s表。如果需要用新值来更新表中的每一行，或者将所售书的
价格每本提高1 0％，你是否有必要为每一行都写一条独立的U P D AT E语句呢？就现在的情况
而言，也许不会有很多的U P D AT E语句要写，但如果是更大的表，这就成问题了。所以回答
第1 9学时修改数据1 8 9
下载
图19-3 程序清单1 9 - 8的运
行结果
是否定的。你所要做的只是写一条不指定要更新的行的U P D AT E语句，如程序清单1 9 - 9所示。
程序清单19-9 不带W H E R E子句的U P D AT E语句
在你键入并运行这一程序后，计算机将返回说明更新完1 8行值的信息。这是一个有用的
特点，但也可以关闭。你可用它来检查你的工作，并确定你对表的修改是正确的。
删除数据
和U P D AT E语句一样， D E L E T E语句实际上也不直接在表上修改数据，而是将你不要的行
从表上移走。所以从某种意义上说，你是在通过让数据对其他用户失效的方法来修改数据的。
例如，一个包含有供应商数据的数据库，如果你公司的某个供应商突然中断与你们的往来，
就不再需要表上的这些行。你大概会将该供应商从你的数据库中删除。
与U P D AT E指令一样，使用D E L E T E语句也要分外小心。如果使用不当，很可能将表上的
所有信息都删除掉。也许你会认为这个问题我们已经谈论太多了，但是你应意识到一旦丢失
了至关重要的数据将造成无法补救的错误。现在，让我们来看看D E L E T E语句的语法：
下面是选项的意义：
选项描述
< t a b l e - n a m e > 表的名称。该表包含了你要删除的行
[ F R O M ] 这一可选关键字可以指定你要删除数据的表的名称
< s e r c h - c o n d i t i o n > 这是D E L E T E语句中最重要的部分。通过指定一个好的搜索条件，你能够限定表
内被删除的行数。如果不指定搜索条件， SQL Server会删除表内的所有行
由此可见，删除语句没有很多选项，并且每个选项的意义都是显而易见的。这里还有两
个例子。第一例如程序清单1 9 - 1 0中所示，我们删除表中所有行。
程序清单19-10 无条件删除
在程序清单1 9 - 1 0中，我们将删除表中的所有行。可以说这是一个很有份量的语句。通常，
我们不需要删除表中的所有行，而只需有目的地删除若干行。这就必须通过使用比较操作符
来指定一个搜索条件，我们在讨论S E L E C T语句时见过这些操作符。例如，你可以用下面的搜
索条件删除t i t l e s表上所有价格低于1 2美元的书：
程序清单1 9 - 11显示的代码将在a u t h o r s表中删除一行。
程序清单1 9 - 11 删除一行
程序清单1 9 - 11中，我们在a u t h o r s表中删除的那一行是依据a u i d确定的。上例中的a u i d
是表的一个标识列。也就是说，表中只有唯一一行满足a u i d = ' 5 11-12-7654' 的条件。
1 9 0 SQL Server 7 24学时教程
下载
19.4 锁定
不论数据库里的数据做何种修改， SQL Server都将会执行一些特殊操作以保证正在改动
的同一行数据无法被其他用户修改或读取。SQL Server将根据正在进行的操作需要，在不同
层次上锁定数据库对象和数据。表1 9 - 1总结了SQL Server可锁定的资源粒度。
表19-1 可锁定的资源粒度及描述
资源粒度描述
R I D R I D或行标识符是用来锁定表内个别行的。它允许多行数据的更新、插入和删除，而无须
锁定整个页
键值键值锁定是一种索引中的行锁定，该锁定功能仅当SQL Server更新索引时才需要
页页锁定是指将一个表或索引内8 K B大小的一整页锁定，包括了该页中的所有行。当用户需
要锁定一页中的多个行时，整页锁定比逐行锁定要高效得多
区域区域锁定即连续锁定8页。和锁定一页一样，它可连续锁定区域内所有页中的所有行。当
数据包含在多个页中时才需要使用这种锁定。区域锁定比逐页锁定要高效得多
表表锁定即锁定整个表，包括其中的所有数据和索引。当数据更新涉及到表中大量的数据，
并且SQL Server认为锁定整个表比锁定任何小对象更容易时，则采取这种锁定方式
库库锁定是对整个数据库及其所有表的锁定，该锁定仅在执行影响整个数据库的操作时才
使用，如数据库的恢复
SQL Server将根据当前运行的操作类型采用不同的锁定模式锁定上述资源粒度。表1 9 - 2列
出了SQL Server可采用的锁定模式。
表19-2 锁定模式及描述
锁定模式描述
共享锁( S h a r e d ) 共享锁用于不修改数据的操作，例如S E L E C T语句。当
数据一旦被读取，共享锁就被自动解除。多个用户可在
同一对象上获得共享锁
更新锁( U p d a t e ) 更新锁在发生更新操作时使用。需要用它来避免在更新
过程中的某种死锁现象。在更新操作中，SQL Server将通
过共享锁读出一条记录中，然后通过排它锁来修改这条记
录。如果两个用户都试图这样做的话，就会发生死锁。仅
一个用户可以获得对象的一个更新锁。如果实现了该数据
的修改，该锁就将被升级为排它锁
排它锁( E x c l u s i v e ) 排它锁用于诸如插入、更新、删除等修改数据的操作
中。该类型锁保证其他用户不能操作锁定对象
共享意向锁(Intent Shared) 共享意向锁表明一个事务准备在共享意向锁所锁定资
源的低层资源而非全部资源上放置共享锁。例如，表中
大多数行将被读取时，事务可能在表上放置一个共享意
向锁
排它意向锁(Intent Exclusive) 排它意向锁表明一个事务准备修改一些锁定资源的低层
资源而非全部资源
共享式排它意向锁(Shares with Intent Exclusive) 该类锁表明一个事务将读取所有更低层的资源并通过放
置排它意向锁来修改它们
19.5 课时小结
这一学时里，我们学习了数据修改。首先，我们学习了如何将新数据插入数据库中。接
第1 9学时修改数据1 9 1
下载
着，我们学习了更新数据库中现有数据，然后研究了对数据库里数据的删除。最后学习了
SQL Server如何使用锁定机制来防止其他用户操作当前用户正在修改的数据。
19.6 专家答疑
问题：能否用一条语句向表中插入多行数据，就像一条U P D AT E或一条D E L E T E可以同时
影响若干行一样？
解答：不行。向表插入多行数据的唯一方法就是写出多条I N S E RT语句。
问题：能否在运行了一条U P D AT E或一条D E L E T E命令导致不希望受影响的数据行受到了
影响后进行恢复？
解答：目前所学的办法还做不到。等你学了第2 1学时“SQL Server编程”，掌握了使用显
式事务以后，你就能使用显式事务来取消U P D AT E和D E L E T E带来的误操作，不过在你运行命
令之前必须先启用该事务。
19.7 课外作业
这些思考题和练习题是供你加深理解用的。答案可以在附录“答案”中找到。
19.7.1 思考题
1) INSERT语句的用途是什么？
2) 如果在I N S E RT语句中列出了6个列，你必须提供几个值？
3) 如果向一个没有缺省值而且也不允许空值的列中插入一个空值，结果会怎样？
4) UPDAT E语句的作用是什么？
5) 为什么在使用U P D AT E语句时提供一个W H E R E子句很重要？
6) DELETE语句的作用是什么？
7) 使用D E L E T E语句能一次删除多个行吗？
8) 锁定的作用是什么？
19.7.2 练习题
写一个I N S E RT语句，把以下值插入到p u b s数据库中的a u t h o r s表里：
列值
a u i d 9 2 5 - 1 2 - 3 4 5 6
a u l n a m e J o n e s
a u f n a m e M e l i s s a
p h o n e 9 1 3 - 7 2 2 - 0 9 0 9
a d d r e s s 94 W.163rd St.
c i t y L e n e x a
s t a t e K S
z i p 6 6 2 1 3
c o n t r a c t 1
写一条U P D AT E语句把M e l i s s a的数据改成以下内容：
1 9 2 SQL Server 7 24学时教程
下载
列值
a u l n a m e J o n e s
p h o n e 9 1 3 - 6 6 3 - 1 2 3 4
a d d r e s s 9134 E.178th St.
最后，写一条D E L E T E语句将M e l i s s a的数据从a u t h o r s表中删除。