看点、学点、写点、吃点、喝点 - MSSQL

SQL的触发器

tenysw@163.com(admin) — Wed,17 Mar 2010 09:55:55 +0800

SQL代码

[1].触发器的概念
触发器是一种特殊类型的存储过程，当指定表中的数据发生变化时触发器自动生效。它与表紧密相连，可以看做是表定义的一部分。触发器不能通过名称被直接调用，更不允许设置参数。
在SQL Server中一张表可以有多个触发器。用户可以跟据Insert、Update或Delete语句对触发器进行设置，也可以对一张表上的特定操作设置多个触发器。触发器可以包含复杂的Transact-SQL语句。不论触发器所进行的操作有多复杂，触发器都只作为一个独立的单元被执行，被看作是一个事务。如果在执行触发器的过程中发生了错误，则整个事务将会自动回滚。
[2].触发器的优点
1．触发器自动执行
对表中的数据进行修改后，触发器立即被激活。
2．可以调用存储过程
为了实现复杂的数据库更新操作，触发器可以调用一个或多个存储过程，甚至可以通过调用外部过程（不是数据库管理系统本身）完成相应的操作。
3．可以强化数据条件约束
触发器能够实现比CHECK约束更为复杂的数据完整性约束。在数据库中，为了实现数据完整性约束，可以使用CHECK约束或触发器。CHECK约束不允许引用其他表中的列来完成检查工作，而触发器可以引用其他表中的列。它更适合在大型数据库管理系统中用来约束数据的完整性。
4．触发器可以楚止或回滚违反引用完整性的更改
触发器可以检测数据库内的操作，从而取消了数据库未经许可的更新操作，使数据库修改、更新操作更安全，数据库的运行也更稳定。
5．级联、并行运行
触发器能够对数据库中的相关表实现级联更改。触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联更改。
6．同表多触发器
一个表中可以同时存在3个不同操作的触发器（Insert、Update和Delete）。
[3].触发器的种类
SQL Server支持两种类型的触发器：AFTER触发器和INSTEAD OF触发器。
AFTER触发器又称为后触发器，该类触发器是在引起触发器执行的修改语句成功完成后执行。此类触发器只有在执行某一操作（Insert、Update和Delete）之后，触发器才被触发，如果修改语句错误，触发器将不会执行。AFTER触发器只能定义在表上，不能创建在视图上，但可以为针对表的同一操作定义多个触发器。可使用 sp_settriggerorder指定表上第一个和最后一个执行的AFTER触发器，在表上只能为每个INSETR、Update和Delete操作规程指定第一个执行和最后一个执行的AFTER触发器。
INSTEAD OF触发器又称为替代触发器，当引起触发器执行的修改语句停止时，该类触发器代替触发操作执行。它可以在表上定义，也可以在视图上定义。对于每个触发操作（Insert、Update和Delete），只能定义一个INSTEAD OF触发器。
INSTEAD OF触发器与AFTER触发器的最大不同之处在于INSTEAD OF触发器并不是在执行预定义的操作（如Insert、Update和Delete）时被触发，而仅仅是执行触发器本身。
[4].触发器的创建
1．使用企业管理器创建触发器
在企业管理器中为数据库“MR_SQL”中的“MR_Stu_XX”表创建一个名为 “TRI_StuXX_Insert”的触发器，实现在“MR_Stu_XX”表中添加数据时，在“TRI_pp”表中自动添加数据。
操作步骤如下：
（1）在操作系统的任务栏中单击“开始”菜单，选择“程序”→“Microsoft SQL Server”→“企业管理器” 命令，打开控制台目录。
（2）在控制台目录中依次展开Microsoft SQL Server、SQL Server组、服务器、数据库的节点。
（3）选择指定数据库中的指定表，单击鼠标右键，在快捷菜单中选择“所有任务”→“管理触发器”命令。
（4）执行“管理触发器”命令后，打开“触发器属性”对话框，如图1所示。在“名称”文本框中输入触发器的名称，例如用触发器名“TRI_StuXX_Insert”替换[TRIGGER NAME]，如图2所示。
（5）删除FOR关键字后面的Update和Delete，使触发器具有添加（Insert）功能，如果让触发器具有更新或删除的功能，在FOR关键字后面保留Update或 Delete，Insert 、Update和Delete可以同时使用。
（6）在“文本”文本框中输入触发器文本，SQL语句如下。
Insert INTO TRI_pp (MR_id,MR_name,MR_char,MR_sum,MR_eng) VALUES ('001','张三',0.0,0.0,0.0)
（7）如果没有任何错误，单击“应用”按钮，将触发器保存到数据库中。
2．使用Transact-SQL创建触发器
Create TRIGGER语句用于创建触发器，触发器是一种特殊的存储过程，在用户试图对指定的表执行指定的数据修改语句时自动执行。 SQL Server 允许为任何给定的Insert、Update或Delete语句创建多个触发器。
语法：
Create TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ Insert ] [ , ] [ Update ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF Update ( column )
[ { AND | or } Update ( column ) ]
[ ...n ]
| IF ( COLUMNS_UpdateD ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
参数说明：
l trigger_name：触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须惟一。可以选择是否指定触发器所有者名称。
l table | view：执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。
l AFTER：指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才被激活。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。
l INSTEAD OF：指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。
l WITH APPEND：指定应该添加现有类型的其他触发器。只有当兼容级别是65或更低时，才需要使用该可选子句。
l NOT FOR REPLICATION：表示当复制进程更改触发器所涉及的表时，不应执行该触发器。
l AS：触发器要执行的操作。
l sql_statement：触发器的条件和操作。触发器条件指定其他准则，以确定Delete、Insert或Update语句是否导致执行触发器操作。
l column：要测试 Insert或Update操作的列名。该列可以是SQL Server支持的任何数据类型。
l bitwise_operator：用于比较运算的位运算符。
l updated_bitmask：整型位掩码，表示实际更新或插入的列。
l comparison_operator：比较运算符。使用等号（=）检查updated_bitmask中指定的所有列是否都实际进行了更新。使用大于号（>）检查 updated_bitmask中指定的任一列或某些列是否已更新。
l column_bitmask：要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。
本示例是建立两个相关表“MR_Stu_XX”和“MR_Stu_CJ”，它们都有“学生编号”和 “学生姓名”字段，并且类型相同。在“MR_Stu_XX”表中创建三个触发器，在对“MR_Stu_XX”表进行添加修改的同时，对 “MR_Stu_CJ”表也进行相应的操作。
操作步骤如下：
（1）在“MR_SQL”数据库中创建“MR_Stu_CJ”表和 “MR_Stu_XX”表。
（2）为“MR_Stu_XX”表创建触发器，SQL语句如下：
用Insert语句创建触发器，当在 “MR_Stu_XX”表中添加数据时，“MR_Stu_CJ”表也添加相应的数据。SQL语句如下：
USE MR_SQL
GO
Create TRIGGER TRI_StuXX_Insert ON dbo.MR_Stu_XX
FOR Insert
AS
DECLARE @id Char(4),@name Char(10)
Select @id=学生编号,@name=学生姓名 from inserted
Insert INTO MR_Stu_CJ (学生编号,学生姓名,语文,数学,英语) VALUES (@id,@name,0.0,0.0,0.0)
GO
注意：在表中添加数据时，将添加的数据存放在系统临时表“inserted”中。
用Delete语句创建触发器，当在 “MR_Stu_XX”表中删除数据时，“MR_Stu_CJ”表也删除相应的数据。SQL语句如下：
USE MR_SQL
GO
Create TRIGGER TRI_StuXX_Delete ON [dbo].[MR_Stu_XX]
FOR Delete
AS
DECLARE @id char(4),@name char(10)
select @id=学生编号,@name=学生姓名 from deleted
Delete MR_Stu_CJ where 学生编号=@id and 学生姓名=@name
GO
注意：在表中删除数据时，将删除的数据存放在系统临时表deleted中。
用Update语句创建触发器，当在“MR_Stu_XX”表中更新数据时，“MR_Stu_CJ”表也更新相应的数据。SQL语句如下：
USE MR_SQL
GO
Create TRIGGER TRI_StuXX_Uudate ON [dbo].[MR_Stu_XX]
FOR Update
AS
DECLARE @id char(4),@name char(10)
select @id=学生编号 from deleted
select @name=学生姓名 from Inserted
Update MR_Stu_CJ SET 学生姓名=@name Where 学生编号 = @id
GO
注意：在表中更新数据时，将更新前的数据存放在系统临时表“deleted”中，将更新后的数据存放在系统临时表“inserted”中。
3．创建触发器的注意事项
（1）触发器是一个数据库对象，它的命名必须符合SQL Server 2000的标识符命名规则。
（2）Create TRIGGER语句必须是批处理中的第一个语句。也就是说，如果该语句前面还有其他语句，应该使用批处理结束符GO隔开。
（3）不能在临时表或系统表上创建触发器，但是在触发器中可以引用临时表，不能引用系统表。
（4）创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。
（5）触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。
（6）在含有用 Delete或Update操作定义的外键的表中，不能定义INSTEAD OF和INSTEAD OF Update触发器。
（7）WRITETEXT 语句不会引发Insert或Update触发器。
（8）TRUNCATE TABLE语句不能引发Delete触发器，因为该语句没有记录。

SQLSEREVER测试题

tenysw@163.com(admin) — Tue,16 Mar 2010 19:58:38 +0800

SQLSEREVER测试题(上)

一 SQLSERVER管理部分

1 请讲出身份验证模式与登录帐号的关系及如何用各种帐号进行登录，并画出示意图
2 请讲出登录帐号、数据库用户及数据库角色之间的关系，并画出示意图
3 请讲出数据库用户、数据库角色与数据库对象之间的关系，并画出直接对用户授权与间接对用户授权（系统权限与对象权限）的方法
4 请讲出服务器角色、数据库角色、标准角色与应用程序角色的区别与验证其权限的方法
5 请讲出数据库还原模型对数据库的影响
6 有一个执行关键任务的数据库，请设计一个数据库备份策略
7 请使用文件与文件组恢复的方式恢复数据库
8 请使用事务日志恢复数据库到一个时间点
9 请设计作业进行周期性的备份数据库
10 如何监控数据库的阻塞，并实现数据库的死锁测试
11 如何监控数据库的活动，并能使用索引优化向导生成索引
12 理解数据库框图的作用并可以设计表与表之间的关系

SQL代码

SQLSEREVER测试题(中)
二 SQLSERVER的实现部分
1 有订单表，需要实现它的编号，格式如下：200211030001……200222039999等
2 有表T1,T2，现有一事务，在向表T1添加数据时，同时也必须向T2也添加数据，如何实现该事务
3 如何向T1中的编号字段（code varchar(20)）添加一万条记录，不充许重复，规则如下：编号的数据必须从小写的a-z之间取值
4 如何删除表中的重复数据,请使用游标与分组的办法
5 如何求表中相邻的两条记录的某字段的值之差
6 如何统计数据库中所有用户表的数据，显示格式如下：
表名记录数
sales 23
7 如何删除数据库中的所有用户表（表与表之间有外键关系）
8 表A　editor_id　　　　　　　lb2_id
　　　123　　　　　　　　　　 000
　　　123　　　　　　　　　　 003
　　　123　　　　　　　　　　 003
　　　456　　　　　　　　　　 007
　　　456　　　　　　　　　　 006
表B　 lb2_id　　　　　　　　 lb2_name
　　　 000　　　　　　　　　　 a
　　　 003　　　　　　　　　　 b
　　　 006　　　　　　　　　　 c
　　　 007　　　　　　　　　　 d
显示　a　　共1条（表A内lb2_id为000的条数）
　　　b　　共2条（表A内lb2_id为003的条数）
9 人员情况表（employee）:里面有一字段文化程度（wh）：包括四种情况（本科以上，大专，高中，初中以下）,现在我要根据年龄字段查询统计出：表中文化程度为本科以上，大专，高中，初中以下，各有多少人，占总人数多少。
Select wh AS 学历,age as 年龄, Count(*) AS 人数,
Count(*) * 100 /(Select Count(*) FROM employee) AS 百分比
FROM employee GROUP BY wh,age
学历年龄人数百分比
本科以上 20 34 14
大专 20 33 13
高中 20 33 13
初中以下 20 100 40
本科以上 21 50 20
10 现在有三个表student:（FID 学生号，FName 姓名），
subject:（FSubID 课程号，FSubName 课程名）,
Score(FScoreId 成绩记录号,FSubID 课程号,FStdID 学生号,FScore 成绩)
怎么能实现这个表：
姓名英语数学语文历史
张萨 78 67 89 76
王强 89 67 84 96
Select a.FName AS 姓名,
英语 = SUM(CASE b.FSubName WHEN '英语' THEN c.FScore END),
数学 = SUM(CASE b.FSubName WHEN '数学' THEN c.FScore END),
语文 = SUM(CASE b.FSubName WHEN '语文' THEN c.FScore END),
历史 = SUM(CASE b.FSubName WHEN '历史' THEN c.FScore END)
FROM Student a, Subject b, Score c
Where a.FID = c.FStdId AND b.FSubID = c.FsubID GROUP BY a.FName
11 原始表的数据如下：
PID PTime PNo
111111 2003-01-28 04:30:09
111111 2003-01-28 18:30:00
222222 2003-01-28 04:31:09
333333 2003-01-28 04:32:09
111111 2003-02-09 03:35:25
222222 2003-02-09 03:36:25
333333 2003-02-09 03:37:25
查询生成表
PDate 111111 222222 333333 ......
2003-01-28 04:30:09 04:31:09 04:32:09 ......
2003-01-28 18:30:00
2003-02-09 03:35:25 03:36:25 03:37:25 ......
12 表一(AAA)
商品名称mc 商品总量sl
A 100
B 120
表二(BBB)
商品名称mc 出库数量sl
A 10
A 20
B 10
B 20
B 30
用一条SQL语句算出商品A,B目前还剩多少？
一
declare @AAA table (商品名称 varchar(10), 商品总量 int)
insert into @AAA values('A',100)
insert into @AAA values('B',120)
declare @BBB table (商品名称 varchar(10), 出库数量 int)
insert into @BBB values('A', 10)
insert into @BBB values('A', 20)
insert into @BBB values('B', 10)
insert into @BBB values('B', 20)
insert into @BBB values('B', 30)
select TA.商品名称,A-B AS 剩余数量 FROM
(select 商品名称,sum(商品总量) AS A
from @AAA
group by 商品名称)TA,
(select 商品名称,sum(出库数量) AS B
from @BBB
group by 商品名称)TB
where TA.商品名称=TB.商品名称
二
select 商品名称,sum(商品总量) 剩余数量 from (select * from @aaa union all select 商品名称,-出库数量 from @bbb) a group by 商品名称
13 优化这句SQL语句
Update tblExlTempYear
SET tblExlTempYear.GDQC = tblExlTempMonth.GDQC
FROM tblExlTempYear,tblExlTempMonth
where tblExlTempMonth.GDXM=tblExlTempYear.GDXM and tblExlTempMonth.TXDZ=tblExlTempYear.TXDZ
（1）、加索引：
tblExlTempYear（GDXM，TXDZ）
tblExlTempMonth （GDXM，TXDZ）
（2）、删除无用数据
（3）、转移过时数据
（4）、加服务器内存，升级服务器
（5）、升级网络系统
Update tblExlTempYear
SET tblExlTempYear.GDQC = tblExlTempMonth.GDQC
FROM tblExlTempYear (index indexY),tblExlTempMonth (index indexM)
where tblExlTempMonth.GDXM=tblExlTempYear.GDXM and tblExlTempMonth.TXDZ=tblExlTempYear.TXDZ
14 品种日期数量
P0001 2002-1-10 10
P0001 2002-1-10 11
P0001 2002-1-10 50
P0001 2002-1-12 9
P0001 2002-1-12 8
P0001 2002-1-12 7
P0002 2002-10-10 5
P0002 2002-10-10 7
P0002 2002-10-12 0.5
P0003 2002-10-10 5
P0003 2002-10-12 7
P0003 2002-10-12 9
结果要先按照品种汇总,再按照日期汇总,结果如下:
P0001 2002-1-10 71
P0001 2002-1-12 24
P0002 2002-10-10 12
P0002 2002-10-12 0.5
P0003 2002-10-10 5
P0003 2002-10-12 16
SQL SERVER能做出这样的汇总吗…
15 在分組查循中with{cube|rollup}的區別是什么?
如:
use pangu
select firm_id,p_id,sum(o_price_quantity)as sum_values
from orders
group by firm_id,p_id
with cube
與
use pangu
select firm_id,p_id,sum(o_price_quantity)as sum_values
from orders
group by firm_id,p_id
with rollup
的區別是什么?
CUBE 和 ROLLUP 之间的区别在于：
CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
例如，简单表 Inventory 中包含：
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
下列查询将生成小计报表：
Select CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP
Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00
(7 row(s) affected)
如果查询中的 ROLLUP 关键字更改为 CUBE，那么 CUBE 结果集与上述结果相同，只是在结果集的末尾还会返回下列两行：
ALL Blue 225.00
ALL Red 433.00
CUBE 操作为 Item 和 Color 中值的可能组合生成行。例如，CUBE 不仅报告与 Item 值 Chair 相组合的 Color 值的所有可能组合（Red、Blue 和 Red + Blue），而且报告与 Color 值 Red 相组合的 Item 值的所有可能组合（Chair、Table 和 Chair + Table）。对于 GROUP BY 子句中右边的列中的每个值，ROLLUP 操作并不报告左边一列（或左边各列）中值的所有可能组合。例如，ROLLUP 并不对每个 Color 值报告 Item 值的所有可能组合。ROLLUP 操作的结果集具有类似于 COMPUTE BY 所返回结果集的功能；然而，
ROLLUP 具有下列优点： ROLLUP 返回单个结果集；COMPUTE BY 返回多个结果集，而多个结果集会增加应用程序代码的复杂性。ROLLUP 可以在服务器游标中使用；COMPUTE BY 不可以。有时，查询优化器为 ROLLUP 生成的执行计划比为 COMPUTE BY 生成的更为高效。
16 假如我有两个表
表1（电话号码，是否存在）
表2（电话号码，是否拨打）
想查找表1中的电话号码是否在表2中存在，如果存在就更新表1中的是否存在字段为1。
Update 表1 SET 是否存在=1
Where EXISTS(Select * FROM　表2 Where 表2.电话号码 = 表1.电话号码)
17 用存储过程调用外部程序.
不过要做成com控件
用sp_OACreate存储过程)
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
Select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

找些不错的sql面试题-2

tenysw@163.com(admin) — Tue,16 Mar 2010 19:55:53 +0800

SQL代码

问题描述：
本题用到下面三个关系表：
CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级
BOOKS 图书。 BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期
备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。
要求实现如下15个处理：
1．写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。
2．找出借书超过5本的读者,输出借书卡号及所借图书册数。
3．查询借阅了"水浒"一书的读者，输出姓名及班级。
4．查询过期未还图书，输出借阅者（卡号）、书号及还书日期。
5．查询书名包括"网络"关键词的图书，输出书号、书名、作者。
6．查询现有图书中价格最高的图书，输出书名及作者。
7．查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。
8．将"C01"班同学所借图书的还期都延长一周。
9．从BOOKS表中删除当前无人借阅的图书记录。
10．如果经常按书名查询图书信息，请建立合适的索引。
11．在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。
12．建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）。
13．查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。
14．假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。
15．对CARD表做如下修改：
a. 将NAME最大列宽增加到10个字符（假定原为6个字符）。
b. 为该表增加1列NAME（系名），可变长，最大20个字符。
1. 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束
--实现代码：
Create TABLE BORROW(
CNO int FOREIGN KEY REFERENCES CARD(CNO),
BNO int FOREIGN KEY REFERENCES BOOKS(BNO),
RDATE datetime,
PRIMARY KEY(CNO,BNO))
2. 找出借书超过5本的读者,输出借书卡号及所借图书册数
--实现代码：
Select CNO,借图书册数=COUNT(*)
FROM BORROW
GROUP BY CNO
HAVING COUNT(*)>5
3. 查询借阅了"水浒"一书的读者，输出姓名及班级
--实现代码：
Select * FROM CARD c
Where EXISTS(
Select * FROM BORROW a,BOOKS b
Where a.BNO=b.BNO
AND b.BNAME=N'水浒'
AND a.CNO=c.CNO)
4. 查询过期未还图书，输出借阅者（卡号）、书号及还书日期
--实现代码：
Select * FROM BORROW
Where RDATE
5. 查询书名包括"网络"关键词的图书，输出书号、书名、作者
--实现代码：
Select BNO,BNAME,AUTHOR FROM BOOKS
Where BNAME LIKE N'%网络%'
6. 查询现有图书中价格最高的图书，输出书名及作者
--实现代码：
Select BNO,BNAME,AUTHOR FROM BOOKS
Where PRICE=(
Select MAX(PRICE) FROM BOOKS)
7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出
--实现代码：
Select a.CNO
FROM BORROW a,BOOKS b
Where a.BNO=b.BNO AND b.BNAME=N'计算方法'
AND NOT EXISTS(
Select * FROM BORROW aa,BOOKS bb
Where aa.BNO=bb.BNO
AND bb.BNAME=N'计算方法习题集'
AND aa.CNO=a.CNO)
ORDER BY a.CNO DESC
8. 将"C01"班同学所借图书的还期都延长一周
--实现代码：
Update b SET RDATE=DATEADD(Day,7,b.RDATE)
FROM CARD a,BORROW b
Where a.CNO=b.CNO
AND a.CLASS=N'C01'
9. 从BOOKS表中删除当前无人借阅的图书记录
--实现代码：
Delete A FROM BOOKS a
Where NOT EXISTS(
Select * FROM BORROW
Where BNO=a.BNO)
10. 如果经常按书名查询图书信息，请建立合适的索引
--实现代码：
Create CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)
11. 在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）
--实现代码：
Create TRIGGER TR_SAVE ON BORROW
FOR Insert,Update
AS
IF @@ROWCOUNT>0
Insert BORROW_SAVE Select i.*
FROM InsertED i,BOOKS b
Where i.BNO=b.BNO
AND b.BNAME=N'数据库技术及应用'
12. 建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）
--实现代码：
Create VIEW V_VIEW
AS
Select a.NAME,b.BNAME
FROM BORROW ab,CARD a,BOOKS b
Where ab.CNO=a.CNO
AND ab.BNO=b.BNO
AND a.CLASS=N'力01'
13. 查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出
--实现代码：
Select a.CNO
FROM BORROW a,BOOKS b
Where a.BNO=b.BNO
AND b.BNAME IN(N'计算方法',N'组合数学')
GROUP BY a.CNO
HAVING COUNT(*)=2
ORDER BY a.CNO DESC
14. 假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句
--实现代码：
Alter TABLE BOOKS ADD PRIMARY KEY(BNO)
15.1 将NAME最大列宽增加到10个字符（假定原为6个字符）
--实现代码：
Alter TABLE CARD Alter COLUMN NAME varchar(10)
15.2 为该表增加1列NAME（系名），可变长，最大20个字符
--实现代码：
Alter TABLE CARD ADD 系名 varchar(20)
问题描述：
为管理岗位业务培训信息，建立3个表:
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩
要求实现如下5个处理：
1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
5. 查询选修了课程的学员人数
6. 查询选修课程超过5门的学员学号和所属单位
1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
--实现代码：
Select SN,SD FROM S
Where [S#] IN(
Select [S#] FROM C,SC
Where C.[C#]=SC.[C#]
AND CN=N'税收基础')
2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
--实现代码：
Select S.SN,S.SD FROM S,SC
Where S.[S#]=SC.[S#]
AND SC.[C#]='C2'
3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
--实现代码：
Select SN,SD FROM S
Where [S#] NOT IN(
Select [S#] FROM SC
Where [C#]='C5')
4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
--实现代码：
Select SN,SD FROM S
Where [S#] IN(
Select [S#] FROM SC
RIGHT JOIN C ON SC.[C#]=C.[C#]
GROUP BY [S#]
HAVING COUNT(*)=COUNT(DISTINCT [S#]))
5. 查询选修了课程的学员人数
--实现代码：
Select 学员人数=COUNT(DISTINCT [S#]) FROM SC
6. 查询选修课程超过5门的学员学号和所属单位
--实现代码：
Select SN,SD FROM S
Where [S#] IN(
Select [S#] FROM SC
GROUP BY [S#]
HAVING COUNT(DISTINCT [C#])>5)

找些不错的sql面试题-1

tenysw@163.com(admin) — Tue,16 Mar 2010 19:54:39 +0800

SQL代码

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
问题：
1、查询“001”课程比“002”课程成绩高的所有学生的学号；
select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
from SC where C#='002') b
where a.score>b.score and a.s#=b.s#;
2、查询平均成绩大于60分的同学的学号和平均成绩；
select S#,avg(score)
from sc
group by S# having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩；
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname
4、查询姓“李”的老师的个数；
select count(distinct(Tname))
from Teacher
where Tname like '李%';
5、查询没学过“叶平”老师课的同学的学号、姓名；
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='叶平'));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2
9、查询所有课程成绩小于60分的同学的学号、姓名；
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
10、查询没有学全所有课的同学的学号、姓名；
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；
select distinct SC.S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in (select C# from SC where S#='001');
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
select S# from SC where C# in (select C# from SC where S#='1002')
group by S# having count(*)=(select count(*) from SC where S#='1002');
15、删除学习“叶平”老师课的SC表记录；
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';
16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、
号课的平均成绩；
Insert SC select S#,'002',(Select avg(score)
from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
Select S# as 学生ID
,(Select score FROM SC Where SC.S#=t.S# AND C#='004') AS 数据库
,(Select score FROM SC Where SC.S#=t.S# AND C#='001') AS 企业管理
,(Select score FROM SC Where SC.S#=t.S# AND C#='006') AS 英语
,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
Select L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分
FROM SC L ,SC AS R
Where L.C# = R.C# and
L.score = (Select MAX(IL.score)
FROM SC AS IL,Student AS IM
Where L.C# = IL.C# and IM.S#=IL.S#
GROUP BY IL.C#)
AND
R.Score = (Select MIN(IR.score)
FROM SC AS IR
Where R.C# = IR.C#
GROUP BY IR.C#
);
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
Select t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩
,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
FROM SC T,Course
where t.C#=course.C#
GROUP BY t.C#
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）
Select SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数
,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数
,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数
,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分
,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数
FROM SC
21、查询不同老师所教不同课程平均分从高到低显示
Select max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ＩＤ,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩
FROM SC AS T,Course AS C ,Teacher AS Z
where T.C#=C.C# and C.T#=Z.T#
GROUP BY C.C#
ORDER BY AVG(Score) DESC
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
Select DISTINCT top 3
SC.S# As 学生学号,
Student.Sname AS 学生姓名 ,
T1.score AS 企业管理,
T2.score AS 马克思,
T3.score AS UML,
T4.score AS 数据库,
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分
FROM Student,SC LEFT JOIN SC AS T1
ON SC.S# = T1.S# AND T1.C# = '001'
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# = '002'
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# = '003'
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# = '004'
Where student.S#=SC.S# and
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
NOT IN
(Select
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# = 'k1'
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# = 'k2'
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# = 'k3'
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# = 'k4'
ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);
23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
Select SC.C# as 课程ID, Cname as 课程名称
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM SC,Course
where SC.C#=Course.C#
GROUP BY SC.C#,Cname;
24、查询学生平均成绩及其名次
Select 1+(Select COUNT( distinct 平均成绩)
FROM (Select S#,AVG(score) AS 平均成绩
FROM SC
GROUP BY S#
) AS T1
Where 平均成绩 > T2.平均成绩) as 名次,
S# as 学生学号,平均成绩
FROM (Select S#,AVG(score) 平均成绩
FROM SC
GROUP BY S#
) AS T2
ORDER BY 平均成绩 desc;
25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
Select t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
Where score IN (Select TOP 3 score
FROM SC
Where t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
26、查询每门课程被选修的学生数
select c#,count(S#) from sc group by C#;
27、查询出只选修了一门课程的全部学生的学号和姓名
select SC.S#,Student.Sname,count(C#) AS 选课数
from SC ,Student
where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
28、查询男生、女生人数
Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='男';
Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex='女'；
29、查询姓“张”的学生名单
Select Sname FROM Student Where Sname like '张%';
30、查询同名同性学生名单，并统计同名人数
select Sname,count(*) from Student group by Sname having count(*)>1;;
31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age
from student
where CONVERT(char(11),DATEPART(year,Sage))='1981';
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select Sname,SC.S# ,avg(score)
from Student,SC
where Student.S#=SC.S# group by SC.S#,Sname having avg(score)>85;
34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
Select Sname,isnull(score,0)
from Student,SC,Course
where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='数据库'and score <60;
35、查询所有学生的选课情况；
Select SC.S#,SC.C#,Sname,Cname
FROM SC,Student,Course
where SC.S#=Student.S# and SC.C#=Course.C# ;
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
Select distinct student.S#,student.Sname,SC.C#,SC.score
FROM student,Sc
Where SC.score>=70 AND SC.S#=student.S#;
37、查询不及格的课程，并按课程号从大到小排列
select c# from sc where scor e <60 order by C# ;
38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';
39、求选了课程的学生人数
select count(*) from sc;
40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select Student.Sname,score
from Student,SC,Course C,Teacher
where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# );
41、查询各个课程及相应的选修人数
select count(*) from sc group by C#;
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# <>B.C# ;
43、查询每门功成绩最好的前两名
Select t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
Where score IN (Select TOP 2 score
FROM SC
Where t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列
select C# as 课程号,count(*) as 人数
from sc
group by C#
order by count(*) desc,c#
45、检索至少选修两门课程的学生学号
select S#
from sc
group by s#
having count(*) > = 2
46、查询全部学生都选修的课程的课程号和课程名
select C#,Cname
from Course
where C# in (select c# from sc group by c#)
47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平');
48、查询两门以上不及格课程的同学的学号及其平均成绩
select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;
49、检索“004”课程分数小于60，按分数降序排列的同学学号
select S# from SC where C#='004'and score <60 order by score desc;
50、删除“002”同学的“001”课程的成绩
delete from Sc where S#='001'and C#='001';

整理精华帖子

tenysw@163.com(admin) — Tue,16 Mar 2010 19:52:38 +0800

总结一下，免得每次都得去查看别人的东西

tenysw@163.com(admin) — Tue,16 Mar 2010 19:51:20 +0800

SQL代码

1:replace 函数
第一个参数你的字符串，第二个参数你想替换的部分，第三个参数你要替换成什么
select replace('lihan','a','b')
-----------------------------
lihbn
（所影响的行数为 1 行）
=========================================================
2:substring函数
第一个参数你的字符串，第二个是开始替换位置，第三个结束替换位置
select substring('lihan',0,3);
-----
li
（所影响的行数为 1 行）
=========================================================
3:charindex函数
第一个参数你要查找的char，第二个参数你被查找的字符串返回参数一在参数二的位置
select charindex('a','lihan')
-----------
4
（所影响的行数为 1 行）
===========================================================
4：ASCII函数
返回字符表达式中最左侧的字符的 ASCII 代码值。
select ASCII('lihan')
-----------
108
（所影响的行数为 1 行）
================================================================
5：nchar函数
根据 Unicode 标准的定义，返回具有指定的整数代码的 Unicode 字符。
参数是介于 0 与 65535 之间的正整数。如果指定了超出此范围的值，将返回 NULL。
select nchar(3213)
----
unicode字符
（所影响的行数为 1 行）
=========================================================
6：soundex
返回一个由四个字符组成的代码 (SOUNDEX)，用于评估两个字符串的相似性。
Select SOUNDEX ('lihan'), SOUNDEX ('lihon');
----- -----
L546 L542
（所影响的行数为 1 行）
=========================================================
7：char
参数为介于 0 和 255 之间的整数。如果该整数表达式不在此范围内，将返回 NULL 值。
Select char(125)
----
}
（所影响的行数为 1 行）
==========================================================
8：str函数
第一个参数必须为数字，第二个参数表示转化成char型占的位置，小于参数一位置返回*，大于右对齐
Select str(12345,3)
----
***
（所影响的行数为 1 行）
Select str(12345,12)
------------
12345
（所影响的行数为 1 行）
===========================================================
9：difference函数
返回一个整数值，指示两个字符表达式的 SOUNDEX 值之间的差异。
返回的整数是 SOUNDEX 值中相同字符的个数。返回的值从 0 到 4 不等：0 表示几乎不同或完全不同，4 表示几乎相同或完全相同。
Select difference('lihan','liha')
-----------
3
（所影响的行数为 1 行）
==================================================================
10：stuff函数（四个参数）
函数将字符串插入另一字符串。它在第一个字符串中从开始位置删除指定长度的字符；然后将第二个字符串插入第一个字符串的开始位置。
Select stuff('lihan',2,3,'lihan')
--------
llihann
（所影响的行数为 1 行）
===============================================================
11：left函数
返回最左边N个字符，由参数决定
select left('lihan',4)
-----
liha
（所影响的行数为 1 行）
================================================================
12 right函数
返回最右边N个字符，由参数决定
select right('lihan',4)
-----
ihan
（所影响的行数为 1 行）
================================================================
13：replicate函数
我的认为是把参数一复制参数二次
select replicate('lihan',4)
--------------------
lihanlihanlihanlihan
（所影响的行数为 1 行）
================================================================
14：len函数
返回参数长度
select len('lihan')
-----------
5
（所影响的行数为 1 行）
================================================================
15：reverse函数
反转字符串
select reverse('lihan')
-----
nahil
（所影响的行数为 1 行）
=================================================================
16：lower和upper函数
参数大小写转化
select lower(upper('lihan'))
--------------------
lihan
（所影响的行数为 1 行）
====================================================================
17：ltrim和rtrim函数
删除左边空格和右面空格
select ltrim(' lihan ')
--------------------------
lihan
（所影响的行数为 1 行）
select rtrim(' lihan')
---------
lihan
（所影响的行数为 1 行）

SQL Server数据库开发的二十一条军规(SQL收藏)

tenysw@163.com(admin) — Tue,16 Mar 2010 19:46:55 +0800

如果你正在负责一个基于SQL Server的项目，或者你刚刚接触SQL Server，你都有可能要面临一些数据库性能的问题，这篇文章会为你提供一些有用的指导（其中大多数也可以用于其它的DBMS）。
在这里，我不打算介绍使用SQL Server的窍门，也不能提供一个包治百病的方案，我所做的是总结一些经验----关于如何形成一个好的设计。这些经验来自我过去几年中经受的教训，一直来，我看到许多同样的设计错误被一次又一次的重复。
一、了解你用的工具
不要轻视这一点，这是我在这篇文章中讲述的最关键的一条。也许你也看到有很多的SQL Server程序员没有掌握全部的T-SQL命令和SQL Server提供的那些有用的工具。
“什么？我要浪费一个月的时间来学习那些我永远也不会用到的SQL命令？？？”，你也许会这样说。对的，你不需要这样做。但是你应该用一个周末浏览所有的T-SQL命令。在这里，你的任务是了解，将来，当你设计一个查询时，你会记起来：“对了，这里有一个命令可以完全实现我需要的功能”，于是，到MSDN查看这个命令的确切语法。
二、不要使用游标
让我再重复一遍：不要使用游标。如果你想破坏整个系统的性能的话，它们倒是你最有效的首选办法。大多数的初学者都使用游标，而没有意识到它们对性能造成的影响。它们占用内存，还用它们那些不可思议的方式锁定表，另外，它们简直就像蜗牛。而最糟糕的是，它们可以使你的DBA所能做的一切性能优化等于没做。不知你是否知道每执行一次FETCH就等于执行一次Select命令？这意味着如果你的游标有10000条记录，它将执行10000次 Select！如果你使用一组Select、Update或者Delete来完成相应的工作，那将有效率的多。
初学者一般认为使用游标是一种比较熟悉和舒适的编程方式，可很不幸，这会导致糟糕的性能。显然，SQL的总体目的是你要实现什么，而不是怎样实现。
我曾经用T-SQL重写了一个基于游标的存储过程，那个表只有100,000条记录，原来的存储过程用了40分钟才执行完毕，而新的存储过程只用了10秒钟。在这里，我想你应该可以看到一个不称职的程序员究竟在干了什么！！！
我们可以写一个小程序来取得和处理数据并且更新数据库，这样做有时会更有效。记住：对于循环，T-SQL 无能为力。
我再重新提醒一下：使用游标没有好处。除了DBA的工作外，我从来没有看到过使用游标可以有效的完成任何工作。
三、规范化你的数据表
为什么不规范化数据库？大概有两个借口：出于性能的考虑和纯粹因为懒惰。至于第二点，你迟早得为此付出代价。而关于性能的问题，你不需要优化根本就不慢的东西。我经常看到一些程序员“反规范化”数据库，他们的理由是“原来的设计太慢了”，可结果却常常是他们让系统更慢了。DBMS被设计用来处理规范数据库的，因此，记住：按照规范化的要求设计数据库。
四、不要使用Select *
这点不太容易做到，我太了解了，因为我自己就经常这样干。可是，如果在Select中指定你所需要的列，那将会带来以下的好处：
1 减少内存耗费和网络的带宽
2 你可以得到更安全的设计
3 给查询优化器机会从索引读取所有需要的列
五、了解你将要对数据进行的操作
为你的数据库创建一个健壮的索引，那可是功德一件。可要做到这一点简直就是一门艺术。每当你为一个表添加一个索引，Select会更快了，可Insert和Delete却大大的变慢了，因为创建了维护索引需要许多额外的工作。显然，这里问题的关键是：你要对这张表进行什么样的操作。这个问题不太好把握，特别是涉及Delete和 Update时，因为这些语句经常在Where部分包含Select命令。
六、不要给“性别”列创建索引
首先，我们必须了解索引是如何加速对表的访问的。你可以将索引理解为基于一定的标准上对表进行划分的一种方式。如果你给类似于“性别”这样的列创建了一个索引，你仅仅是将表划分为两部分：男和女。你在处理一个有1,000,000条记录的表，这样的划分有什么意义？记住：维护索引是比较费时的。当你设计索引时，请遵循这样的规则：根据列可能包含不同内容的数目从多到少排列，比如：姓名+省份+性别。
七、使用事务
请使用事务，特别是当查询比较耗时。如果系统出现问题，这样做会救你一命的。一般有些经验的程序员都有体会-----你经常会碰到一些不可预料的情况会导致存储过程崩溃。
八、小心死锁
按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁。如果锁定顺序没有被预先详细的设计好，死锁是不太容易被发现的。
九、不要打开大的数据集
一个经常被提出的问题是：我怎样才能迅速的将100000条记录添加到ComboBox中？这是不对的，你不能也不需要这样做。很简单，你的用户要浏览100000条记录才能找到需要的记录，他一定会诅咒你的。在这里，你需要的是一个更好的UI，你需要为你的用户显示不超过100或200条记录。
十、不要使用服务器端游标
与服务器端游标比起来，客户端游标可以减少服务器和网络的系统开销，并且还减少锁定时间。
十一、使用参数查询
有时，我在CSDN技术论坛看到类似这样的问题：“Select * FROM a Where a.id='A'B，因为单引号查询发生异常，我该怎么办？”，而普遍的回答是：用两个单引号代替单引号。这是错误的。这样治标不治本，因为你还会在其他一些字符上遇到这样的问题，更何况这样会导致严重的bug，除此以外，这样做还会使SQL Server的缓冲系统无法发挥应有的作用。使用参数查询，釜底抽薪，这些问题统统不存在了。
十二、在程序编码时使用大数据量的数据库
程序员在开发中使用的测试数据库一般数据量都不大，可经常的是最终用户的数据量都很大。我们通常的做法是不对的，原因很简单：现在硬盘不是很贵，可为什么性能问题却要等到已经无可挽回的时候才被注意呢？
十三、不要使用Insert导入大批的数据
请不要这样做，除非那是必须的。使用UTS或者BCP，这样你可以一举而兼得灵活性和速度。
十四、注意超时问题
查询数据库时，一般数据库的缺省都比较小，比如15秒或者30秒。而有些查询运行时间要比这长，特别是当数据库的数据量不断变大时。
十五、不要忽略同时修改同一记录的问题
有时候，两个用户会同时修改同一记录，这样，后一个修改者修改了前一个修改者的操作，某些更新就会丢失。处理这种情况不是很难：创建一个timestamp字段，在写入前检查它，如果允许，就合并修改，如果存在冲突，提示用户。
十六、在细节表中插入纪录时，不要在主表执行Select MAX(ID)
这是一个普遍的错误，当两个用户在同一时间插入数据时，这会导致错误。你可以使用 SCOPE_IDENTITY，IDENT_CURRENT和IDENTITY。如果可能，不要使用IDENTITY，因为在有触发器的情况下，它会引起一些问题（详见这里的讨论）。
十七、避免将列设为NULLable
如果可能的话，你应该避免将列设为NULLable。系统会为 NULLable列的每一行分配一个额外的字节，查询时会带来更多的系统开销。另外，将列设为NULLable使编码变得复杂，因为每一次访问这些列时都必须先进行检查。
我并不是说NULLS是麻烦的根源，尽管有些人这样认为。我认为如果你的业务规则中允许“空数据”，那么，将列设为 NULLable有时会发挥很好的作用，但是，如果在类似下面的情况中使用NULLable，那简直就是自讨苦吃。
CustomerName1
CustomerAddress1
CustomerEmail1
CustomerName2
CustomerAddress2
CustomerEmail3
CustomerName1
CustomerAddress2
CustomerEmail3
如果出现这种情况，你需要规范化你的表了。
十八、尽量不要使用TEXT数据类型
除非你使用TEXT处理一个很大的数据，否则不要使用它。因为它不易于查询，速度慢，用的不好还会浪费大量的空间。一般的，VARCHAR可以更好的处理你的数据。
十九、尽量不要使用临时表
尽量不要使用临时表，除非你必须这样做。一般使用子查询可以代替临时表。使用临时表会带来系统开销，如果你是用COM+进行编程，它还会给你带来很大的麻烦，因为COM+使用数据库连接池而临时表却自始至终都存在。SQL Server提供了一些替代方案，比如Table数据类型。
二十、学会分析查询
SQL Server查询分析器是你的好伙伴，通过它你可以了解查询和索引是如何影响性能的。
二十一、使用参照完整性
定义主健、唯一性约束和外键，这样做可以节约大量的时间。

SQL注入专题--整理帖

tenysw@163.com(admin) — Tue,16 Mar 2010 19:45:06 +0800

ＳＱＬ注入是从正常的WWW端口访问，而且表面看起来跟一般的Web页面访问没什么区别，所以目前市面的防火墙都不会对ＳＱＬ注入发出警报，如果管理员没查看IIS日志的习惯，可能被入侵很长时间都不会发觉。
随着B/S模式应用开发的发展，使用这种模式编写应用程序的程序员也越来越多。但是由于这个行业的入门门槛不高，程序员的水平及经验也参差不齐，相当大一部分程序员在编写代码的时候，没有对用户输入数据的合法性进行判断，使应用程序存在安全隐患。用户可以提交一段数据库查询代码，根据程序返回的结果，获得某些他想得知的数据，这就是所谓的SQL Injection，即SQL注入。
SQL注入是从正常的WWW端口访问，而且表面看起来跟一般的Web页面访问没什么区别，所以目前市面的防火墙都不会对SQL注入发出警报，如果管理员没查看IIS日志的习惯，可能被入侵很长时间都不会发觉。
但是，SQL注入的手法相当灵活，在注入的时候会碰到很多意外的情况。能不能根据具体情况进行分析，构造巧妙的SQL语句，从而成功获取想要的数据，是高手与“菜鸟”的根本区别。
SQL注入漏洞全接触--入门篇
SQL注入是从正常的WWW端口访问，而且表面看起来跟一般的Web页面访问没什么区别，所以目前市面的防火墙都不会对SQL注入发出警报，如果管理员没查看IIS日志的习惯，可能被入侵很长时间都不会发觉。
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449834.aspx
SQL 注入漏洞全接触--进阶篇
SQL注入的一般步骤，首先，判断环境，寻找注入点，判断数据库类型，其次，根据注入参数类型，在脑海中重构SQL语句的原貌。
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449870.aspx
SQL 注入漏洞全接触--高级篇
看完入门篇和进阶篇后，稍加练习，破解一般的网站是没问题了。但如果碰到表名列名猜不到，或程序作者过滤了一些特殊字符，怎么提高注入的成功率？怎么样提高猜解效率？
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449883.aspx
SQL 注入法攻击一日通
SQL注入的简单原理和攻击一般步骤，文章作者想让人一天学会SQL注入攻击。
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449901.aspx
SQL Server应用程序中的高级SQL注入
这份文档是详细讨论SQL注入技术，它适应于比较流行的IIS+ASP+SQLSERVER平台。它讨论了哪些SQL语句能通过各种各样的方法注入到应用程序中，并且记录与攻击相关的数据确认和数据库锁定。
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449913.aspx
编写通用的ASP防SQL注入攻击程序
如果编写通用的SQL防注入程序一般的http请求不外乎get 和 post,所以只要我们在文件中过滤所有post或者get请求中的参数信息中非法字符即可，所以我们实现http 请求信息过滤就可以判断是是否受到SQL注入攻击。
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449925.aspx
利用instr()函数防止SQL注入攻击
学asp也有一段时间了，这几天一直在写自己的程序，也遇到了好多问题，我就不得不得考虑到一些现在的漏洞，比如，‘ 或 and 1=1等等的一些漏洞！别的先不管，今天我就来说说如何堵这个漏洞！详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449932.aspx
SQL 注入攻击的原理及其防范措施
ASP编程门槛很低，新手很容易上路。在一段不长的时间里，新手往往就已经能够编出看来比较完美的动态网站，在功能上，老手能做到的，新手也能够做到。
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449940.aspx
跨站式SQL注入技巧
学习如何从数据库中获取想要获得的内容。
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449948.aspx
防范Sql注入式攻击
Sql注入式攻击是指利用设计上的漏洞，在目标服务器上运行Sql 命令以及进行其他方式的攻击动态生成Sql命令时没有对用户输入的数据进行验证是Sql注入攻击得逞的主要原因。
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449951.aspx
Dreamweaver 中sql注入式攻击的防范
在安全性方面，新手最容易忽略的问题就是SQL注入漏洞的问题。用NBSI 2.0对网上的一些ASP网站稍加扫描，就能发现许多ASP网站存在SQL注入漏洞。详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449953.aspx
PHP 与SQL注入攻击
SQL注入攻击是黑客攻击网站最常用的手段。如果你的站点没有使用严格的用户输入检验，那么常容易遭到SQL注入攻击。SQL注入攻击通常通过给站点数据库提交不良的数据或查询语句来实现，很可能使数据库中的纪录遭到暴露，更改或被删除。下面来谈谈SQL注入攻击是如何实现的，又如何防范。
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449956.aspx
SQL 注入攻击零距离
一次次的SQL注射入侵，一次次的网站被黑，总是那句话，漏洞在所难免，难道一点办法都没吗？这篇文章就是为大家解析下SQL注射的原理，以及给出一些预防方法。
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449959.aspx
SQL 注入技术和跨站脚本攻击的检测
在最近两年中，安全专家应该对网络应用层的攻击更加重视。因为无论你有多强壮的防火墙规则设置或者非常勤于补漏的修补机制，如果你的网络应用程序开发者没有遵循安全代码进行开发，攻击者将通过80端口进入你的系统。
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449961.aspx
菜鸟入门级：SQL注入攻击
一般国内的小一点的新闻站点程序都有 ""&request 这种漏洞，下面我讲解攻击方法。
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449963.aspx
三步堵死SQL注入漏洞
防御SQL注入有妙法，第一步：很多新手从网上下载SQL通用防注入系统的程序，在需要防范注入的页面头部用来防止别人进行手动注入测试.
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449965.aspx
SQL 注入实战---利用“dbo”获得SQL管理权限和系统权限
如果显示“dbo” 转换数据类型为 int 的列时发生语法错误那么就可以用我下面介绍的方法来获得系统管理权限，如果是“abc” 转换数据类型为 int 的列时发生语法错误那么就用不能用我下面的介绍来获得系统权限了.
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449967.aspx
两个防SQL注入过滤代码
SQL注入防护的一种简单方法，在网页中嵌入过滤代码，基于认为的安全防护措施.
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449969.aspx
蓝雨设计整站SQL注入漏洞
以下文章中就会出现NOWA修改系统漏洞当中从未有的SQL注入漏洞！只怪蓝雨修改程序的时候没有做好注入问题了！这个可不能怪我！谁叫人家程序设计员不会注意安全死角阿？
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449972.aspx
SQL 注入渗透某网络安全公司的网站全过程
写这篇文章不是为了告诉大家如何去做入侵，而是希望提醒大家:“入侵是偶然的，但安全并不是必然的”，不要疏忽运作上的一些小细节。
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449973.aspx
sql 注入防御
SQL注入通过网页对网站数据库进行修改。它能够直接在数据库中添加具有管理员权限的用户，从而最终获得系统管理员权限。黑客可以利用获得的管理员权限任意获得网站上的文件或者在网页上加挂木马和各种恶意程序，对网站和访问该网站的网友都带来巨大危害.详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449976.aspx
终极防范SQL注入漏洞
其实SQL注入漏洞并不可怕，知道原理＋耐心仔细，就可以彻底防范！下面给出4个函数，足够你抵挡一切SQL注入漏洞！读懂代码，你就能融会贯通.详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449979.aspx
SQL 注入与ASP木马上传
SQL注入后，如何上传木马，一直是比较头疼的事，我这里提供上传木马的一种另一种方法。
1、SQL注入的时候，用xp_cmdshell 向服务器上写入一个能写文件的asp文件.
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449983.aspx
如何在SQL注入时保护数据库
SQL注入是防止数据库攻击的一个有效策略。攻击者将注入一个SQL语句到另外一个语句中，这个通常会损坏你的数据库。有数据库接口的Web站点通常在 SQL注入的时候容易受到攻击，因为它们是基于动态的SQL.
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449986.aspx
天晨设计整站SQL注入漏洞
作者从一个安全工作员的角度，对天晨设计整站SQL注入漏洞做出了详细的测试.
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449988.aspx
浅谈sql注入式(SQL injection)攻击与防范
没有系统的学习过asp或者php编程，也没有系统的学习过access、sqlserver、mysql等数据库，所以我不是一个程序员，虽然经常干一些类似程序员的事情.
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449991.aspx
用 vbs来写sql注入等80端口的攻击脚本
昨天晚上在机器里乱翻时无意打开一个vbs脚本,突然发现一个以前没有见过的对象Test.SendRequest("http://" & g_sServer & "/testfiles/browser.asp"),虽然对象没有见过,但是意思很明显:发送http请求.
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449992.aspx
C# 检查字符串,防SQL注入攻击
这些天,CSDN上讨论SQL注入攻击似乎是如火如荼啊...我也来参合一下..如下,CheckParams函数,接收参数任意,如参数中有字符串,则对字符串进行检查,如参数中有集合(如Array之类,总之是实现了ICollection的),则对集合中的字符串元素进行检查.
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449994.aspx
关于对SQL注入80004005 及其它错误消息分析
本文详细讲述了 Microsoft 数据访问组件 (MDAC) 收到 0x800040005 错误的一些常见原因，MDAC 包括 ActiveX 数据对象、OLE DB 和远程数据服务 (RDS)。同时，本文还讨论了其它一些错误消息，包括 80040e21、80040e14 和80040e10.
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3449996.aspx
SQL 注入入侵动网SQL版论坛
现在动网最新版本是7.0+SP2。应该说安全性已经是很高的了。所以从脚本本身的问题去突破它难度不小。但是我们可以从外部的一些途径间接“搞定”动网.现在IIS+ASP+SQL2000的组合是比较常见的。而一个网站运用大量的ASP脚本程序，难免不出纰漏。如果一台主机上存在某个SQL注入点，而这台主机又安装有动网SQL版的话，基本上可以得出结论：这个动网就是你的了。下面来看一下实例.详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3450001.aspx
利用SQL注入2分钟入侵网站全程实录
说起流光、溯雪、乱刀，可以说是大名鼎鼎无人不知无人不晓，这些都是小榕哥的作品。每次一提起小榕哥来，我的崇拜景仰就如滔滔江水，连绵不绝~~~~（又来了！）让我们崇拜的小榕哥最新又发布了SQL注入工具，这回喜欢利用SQL注入入侵网站的黑友们有福了。小榕哥的工具就是强！偶用它来搞定我们本地的信息港，从寻找注入漏洞到注入攻击成功，通过准确计时，总共只用了3分还差40秒，呵呵，王者风范，就是强啊！不信吗？看看我的入侵过程吧.
详细内容>>
http://blog.csdn.net/wufeng4552/archive/2008/12/05/3450005.aspx

sql server术语学习表一

tenysw@163.com(admin) — Tue,16 Mar 2010 19:44:04 +0800

“(全部)”级别 ((All) level)
“全部”成员 (All member)
ActiveX 数据对象 (ActiveX Data Objects)
ActiveX 数据对象（多维）(ADO MD) (ActiveX Data Objects (Multidimensional) (ADO MD))
ANSI 到 OEM 的转换 (ANSI to OEM conversion)
API 服务器游标 (API server cursor)
bcp 实用工具 (bcp utility)
bcp 文件 (bcp files)
bigint 数据类型 (bigint data type)
binary 数据类型 (binary data type)
bit 数据类型 (bit data type)
char 数据类型 (char data type)
CHECK 约束 (CHECK constraints)
CLR 触发器 (CLR trigger)
CLR 存储过程 (CLR stored procedure)
CLR 函数 (CLR function)
CLR 用户定义类型 (CLR user-defined type)
COM 结构化存储文件 (COM-structured storage file)
CPU 忙 (CPU busy)
DataReader
datetime 数据类型 (datetime data type)
DDL 触发器 (DDL trigger)
decimal 数据类型 (decimal data type)
DEFAULT 约束 (DEFAULT constraint)
DML 触发器 (DML trigger)
DSN
DTS 包 (DTS package)
DTS 包模板 (DTS package template)
ETL
float 数据类型 (float data type)
guest
image 数据类型 (image data type)
int (integer) 数据类型 (int (integer) data type)
master 数据库 (master database)
Microsoft 消息队列 (Microsoft Message Queuing)
model 数据库 (model database)
money 数据类型 (money data type)
MSMQ
MSX
nchar 数据类型 (nchar data type)
niladic 函数 (niladic functions)
ntext 数据类型 (ntext data type)
NULL
nvarchar 数据类型 (nvarchar data type)
ODBC 驱动程序 (ODBC driver)
ODBC 数据源 (ODBC data source)
OLE DB
OLE DB 访问接口 (OLE DB provider)
OLE DB 使用者 (OLE DB consumer)
OLE 自动化对象 (OLE Automation objects)
OLE 自动化服务器 (OLE Automation server)
OLE 自动化控制器 (OLE Automation controller)
pubs 数据库 (pubs database)
real 数据类型 (real data type)
Select
smalldatetime 数据类型 (smalldatetime data type)
smallint 数据类型 (smallint data type)
smallmoney 数据类型 (smallmoney data type)
SQL Mail
SQL Server 身份验证 (SQL Server Authentication)
SQL Server 事件转发服务器 (SQL Server Event Forwarding Server)
SQL 表达式 (SQL expression)
SQL 查询 (SQL query)
SQL 排序规则 (SQL collation)
SQL 数据库 (SQL database)
SQL 语句 (SQL statement)
sql_variant 数据类型 (sql_variant data type)
SQL-92
tempdb 数据库 (tempdb database)
text 数据类型 (text data type)
theta 联接 (theta join)
timestamp 数据类型 (timestamp data type)
tinyint 数据类型 (tinyint data type)
Transact-SQL
Transact-SQL 游标 (Transact-SQL cursor)
TSX
Unicode
Unicode 格式 (Unicode format)
Unicode 排序规则 (Unicode collation)
UNIQUE 约束 (UNIQUE constraints)
uniqueidentifier 数据类型 (uniqueidentifier data type)
uniqueifier
varbinary 数据类型 (varbinary data type)
varchar 数据类型 (varchar data type)
Web 同步 (Web synchronization)
Where 子句 (Where clause)
Windows Management Instrumentation
Windows 排序规则 (Windows collation)
WMI
WMI 查询语言 (WMI Query Language)
WQL
安全标识符 (SID) (Security Identifier (SID))
安全扩展插件 (security extension)
安全套接字层 (SSL)
安装程序初始化文件 (Setup initialization file)
按范围分区 (range partitioning)
暗示性权限 (implied permission)
半累加性度量值 (semiadditive measure)
绑定 (binding)
保存点 (savepoint)
报表处理扩展插件 (report processing extension)
报表定义 (report definition)
报表服务器管理员 (report server administrator)
报表快照 (report snapshot)
报表模型 (report model)
备份 (back up)
备份 (backup)
备份集 (backup set)
备份媒体 (backup media)
备份设备 (backup device)
备份文件 (backup file)
备用服务器 (standby server)
备用文件 (standby file)
备注 (memo)
被链接表 (linked table)
被引用键 (referenced key)
本地登录标识 (local login identification)
本地订阅 (local subscription)
本地多维数据集 (local cube)
本地分发服务器 (local Distributor)
本地分区视图 (local partitioned view)
本地服务器 (local server)
本地组 (local group)
本机格式 (native format)
编写复制脚本 (replication scripting)
变化的维度 (changing dimension)
变量 (variable)
标记 (token)
标记化 (tokenization)
标量聚合 (scalar aggregate)
标识符 (identifier)
标识列 (identity column)
标识属性 (identity property)
表 (table)
表达式 (expression)
表格格式数据流 (TDS) (tabular data stream (TDS))
表级约束 (table-level constraint)
表扫描 (table scan)
表数据类型 (table data type)
表锁 (table lock)
别名 (alias)
别名 (nickname)
别名数据类型 (alias data type)
并发 (concurrency)
不对称层次结构 (unbalanced hierarchy)
不感知游标 (insensitive cursor)
不可重复读 (nonrepeatable read)
不齐整层次结构 (ragged hierarchy)
布尔值 (Boolean)
步骤对象 (step object)
部分备份 (partial backup)
部分差异备份 (partial differential backup)
参数化 (parameterization)
参数化报表 (parameterized report)
参数化行筛选器 (parameterized row filter)
操作 (action)
层次结构 (hierarchy)
插入查询 (Insert query)
插入值查询 (Insert Values query)
查询优化器 (query optimizer)
查找表 (lookup table)
差异备份 (differential backup)
差异数据库备份 (differential database backup)
常规多维数据集 (regular cube)
常规维度 (regular dimension)
常量 (constant)
超大型维度 (very large dimension)
超文本标记语言 (HTML) (Hypertext Markup Language (HTML))
撤消 (undo)
成员 (member)
成员变量 (member variable)
成员键列 (member key column)
成员名列 (member name column)
成员委托 (member delegation)
成员属性 (member property)
成员组 (member group)
呈现的报表 (rendered report)
呈现扩展插件 (rendering extension)
程序集 (assembly)
持久化计算列 (persisted computed column)
持久性 (persistence)
初始快照 (initial snapshot)
初始同步 (initial synchronization)
触发器 (trigger)
传递查询 (pass-through query)
传递扩展插件 (delivery extension)
传递顺序 (pass order)
传递语句 (pass-through statement)
串联 (concatenation)
垂直分区 (vertical partitioning)
垂直筛选 (vertical filtering)
纯日志备份 (pure log backup)
词干分析器 (stemmer)
磁带备份 (tape backup)
存储过程 (stored procedure)
存储库 (repository)
存储库 SQL 架构 (Repository SQL schema)
存储库对象 (repository object)
存储库类型信息模型 (RTIM) (Repository Type Information Model (RTIM))
存储库引擎 (repository engine)
存档文件 (archive file)
错误日志 (error log)
错误状态号 (error state number)
大级别 (large level)
大容量操作日志备份 (bulk log backup)
大容量导出 (bulk export)
大容量导入 (bulk import)
大容量日志恢复模式 (bulk-logged recovery model)
代码页 (code page)
单用户模式 (single-user mode)
单元 (cell)
单元集 (cellset)
递归分区 (recursive partitioning)
点击链接型报表 (clickthrough report)
调用级接口 (call-level interface (CLI))
订阅 (subscribe)
订阅 (subscription)
订阅方 (Subscriber)
订阅服务器 (subscribing server)
订阅过期时间 (subscription expiration period)
订阅数据库 (subscription database)
定位更新 (positioned update)
定型数据集 (training data set)
动态 SQL 语句 (dynamic SQL statements)
动态恢复 (dynamic recovery)
动态快照 (dynamic snapshot)
动态筛选器 (dynamic filter)
动态锁定 (dynamic locking)
动态游标 (dynamic cursor)
度量值 (measure)
段落还原 (piecemeal restore)
断字 (word-breaking)
断字符 (word breaker)
队列 (queue)
对等复制 (peer-to-peer replication)
对齐 (alignment)
对象 (object)
对象变量 (object variable)
对象标识符 (object identifier)
对象依赖关系 (object dependencies)
多对多关系 (many-to-many relationship)
多对一关系 (many-to-one relationship)
多服务器管理 (multiserver administration)
多个实例 (multiple instances)
多基准差异备份 (multibase differential)
多维 OLAP (MOLAP) (multidimensional OLAP (MOLAP))
多维表达式 (MDX) (Multidimensional Expressions (MDX))
多维结构 (multidimensional structure)
多维数据集 (cube)
多维数据集角色 (cube role)
多线程服务器应用程序 (multithreaded server application)
多用户 (multiuser)
多重继承 (multiple inheritance)
二进制大型对象 (binary large object)
发布 (publication)
发布保持期 (publication retention period)
发布表 (publishing table)
发布服务器 (Publisher)
发布服务器 (publishing server)
发布数据库 (publication database)
反身关系 (reflexive relationship)
返回参数 (return parameters)
范围查询 (range query)
方法 (method)
访问接口 (provider)
非聚集索引 (nonclustered index)
非强制关系 (unenforced relationship)
非叶 (nonleaf)
非叶成员 (nonleaf member)
分布式查询 (distributed query)
分布式分区视图 (distributed partitioned view)
分布式事务 (distributed transaction)
分段 (segmentation)
分发 (distribute)
分发保持期 (distribution retention period)
分发服务器 (Distributor)
分发数据库 (distribution database)
分隔符 (delimiter)
分配单元 (allocation unit)
分区 (partition)
分区 (partitioning)
分区方案 (partition scheme)
分区函数 (partition function)
分区快照 (partitioned snapshot)
分区依据列 (partitioning column)
分析服务器 (Analysis server)
服务器订阅 (server subscription)
服务器名称 (server name)
服务器游标 (server cursor)
辅助服务器 (secondary server)
辅助数据库 (secondary database)
父级 (parent)
复制 (replication)
复制冲突查看器 (Replication Conflict Viewer)
复制的数据 (replicated data)
复制监视器 (Replication Monitor)
复制拓扑 (replication topology)
干扰词 (noise word)
隔离级别 (isolation level)
跟踪令牌 (tracer token)
跟踪文件 (trace file)
更改脚本 (change script)
更新 (update)
更新查询 (Update query)
更新锁 (update lock)
更新统计信息 (update statistics)
工具 (tool)
公共语言运行时 (common language runtime)
共享锁 (shared lock)
共享维度 (shared dimension)
固定服务器角色 (fixed server role)
固定数据库角色 (fixed database role)
关键字 (keyword)
关系 (relationship)
关系 OLAP (ROLAP) (relational OLAP (ROLAP))
关系对象 (relationship object)
关系类型 (relationship type)
关系数据库 (relational database)
关系数据库管理系统 (RDBMS) (relational database management system (RDBMS))
归置 (collocation)
规范化规则 (normalization rules)
规则 (rule)
规则 (rules)
滚动 (scroll)
国际标准化组织 (ISO) (International organization for Standardization (ISO))
国际电工技术委员会 (IEC) (International Electrotechnical Commission (IEC))
过程缓存 (procedure cache)
过度适应 (overfitting)
函数 (function)
合并 (merge)
合并复制 (merge replication)
后代 (descendant)
候选键 (candidate key)
还原 (restore)
还原顺序 (restore sequence)
缓存老化 (cache aging)
幻影 (phantom)
恢复 (recover)
恢复 (recovery)
恢复点 (recovery point)
恢复分叉点 (recovery fork point)
恢复分支 (recovery branch)
恢复间隔 (recovery interval)
恢复路径 (recovery path)
恢复模式 (recovery model)
回滚 (roll back)
混合 OLAP (HOLAP) (hybrid OLAP (HOLAP))
活动语句 (active statement)
伙伴 (partner)
基本数据类型 (base data type)
基表 (base table)
基础表 (underlying table)
基准备份 (base backup)
级别 (level)
级别 (rank)
级联更新 (cascading update)
级联删除 (cascading delete)
计划备份 (scheduled backup)
计划强制 (plan forcing)
计划指南 (plan guide)
计算成员 (calculated member)
计算传递 (calculation pass)
计算公式 (calculation formula)
计算机 DSN (machine DSN)

SQL Server的触发器

tenysw@163.com(admin) — Tue,16 Mar 2010 19:36:17 +0800

[1].触发器的概念
触发器是一种特殊类型的存储过程，当指定表中的数据发生变化时触发器自动生效。它与表紧密相连，可以看做是表定义的一部分。触发器不能通过名称被直接调用，更不允许设置参数。

在SQL Server中一张表可以有多个触发器。用户可以跟据Insert、Update或Delete语句对触发器进行设置，也可以对一张表上的特定操作设置多个触发器。触发器可以包含复杂的Transact-SQL语句。不论触发器所进行的操作有多复杂，触发器都只作为一个独立的单元被执行，被看作是一个事务。如果在执行触发器的过程中发生了错误，则整个事务将会自动回滚。

[2].触发器的优点
1．触发器自动执行
对表中的数据进行修改后，触发器立即被激活。

2．可以调用存储过程
为了实现复杂的数据库更新操作，触发器可以调用一个或多个存储过程，甚至可以通过调用外部过程（不是数据库管理系统本身）完成相应的操作。

3．可以强化数据条件约束
触发器能够实现比CHECK约束更为复杂的数据完整性约束。在数据库中，为了实现数据完整性约束，可以使用CHECK约束或触发器。CHECK约束不允许引用其他表中的列来完成检查工作，而触发器可以引用其他表中的列。它更适合在大型数据库管理系统中用来约束数据的完整性。

4．触发器可以楚止或回滚违反引用完整性的更改
触发器可以检测数据库内的操作，从而取消了数据库未经许可的更新操作，使数据库修改、更新操作更安全，数据库的运行也更稳定。

5．级联、并行运行
触发器能够对数据库中的相关表实现级联更改。触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联更改。

6．同表多触发器
一个表中可以同时存在3个不同操作的触发器（Insert、Update和Delete）。

[3].触发器的种类
SQL Server支持两种类型的触发器：AFTER触发器和INSTEAD OF触发器。

AFTER触发器又称为后触发器，该类触发器是在引起触发器执行的修改语句成功完成后执行。此类触发器只有在执行某一操作（Insert、Update和Delete）之后，触发器才被触发，如果修改语句错误，触发器将不会执行。AFTER触发器只能定义在表上，不能创建在视图上，但可以为针对表的同一操作定义多个触发器。可使用sp_settriggerorder指定表上第一个和最后一个执行的AFTER触发器，在表上只能为每个INSETR、Update和Delete操作规程指定第一个执行和最后一个执行的AFTER触发器。

INSTEAD OF触发器又称为替代触发器，当引起触发器执行的修改语句停止时，该类触发器代替触发操作执行。它可以在表上定义，也可以在视图上定义。对于每个触发操作（Insert、Update和Delete），只能定义一个INSTEAD OF触发器。

INSTEAD OF触发器与AFTER触发器的最大不同之处在于INSTEAD OF触发器并不是在执行预定义的操作（如Insert、Update和Delete）时被触发，而仅仅是执行触发器本身。

[4].触发器的创建

1．使用企业管理器创建触发器

在企业管理器中为数据库“MR_SQL”中的“MR_Stu_XX”表创建一个名为“TRI_StuXX_Insert”的触发器，实现在“MR_Stu_XX”表中添加数据时，在“TRI_pp”表中自动添加数据。

操作步骤如下：

（1）在操作系统的任务栏中单击“开始”菜单，选择“程序”→“Microsoft SQL Server”→“企业管理器” 命令，打开控制台目录。

（2）在控制台目录中依次展开Microsoft SQL Server、SQL Server组、服务器、数据库的节点。

（3）选择指定数据库中的指定表，单击鼠标右键，在快捷菜单中选择“所有任务”→“管理触发器”命令。

（4）执行“管理触发器”命令后，打开“触发器属性”对话框，如图1所示。在“名称”文本框中输入触发器的名称，例如用触发器名“TRI_StuXX_Insert”替换[TRIGGER NAME]，如图2所示。

（5）删除FOR关键字后面的Update和Delete，使触发器具有添加（Insert）功能，如果让触发器具有更新或删除的功能，在FOR关键字后面保留Update或Delete，Insert 、Update和Delete可以同时使用。

（6）在“文本”文本框中输入触发器文本，SQL语句如下。
Insert INTO TRI_pp (MR_id,MR_name,MR_char,MR_sum,MR_eng) VALUES ('001','张三',0.0,0.0,0.0)

（7）如果没有任何错误，单击“应用”按钮，将触发器保存到数据库中。

2．使用Transact-SQL创建触发器

Create TRIGGER语句用于创建触发器，触发器是一种特殊的存储过程，在用户试图对指定的表执行指定的数据修改语句时自动执行。 SQL Server 允许为任何给定的Insert、Update或Delete语句创建多个触发器。

语法：

Create TRIGGER trigger_name

ON { table | view }

[ WITH ENCRYPTION ]

{

   { { FOR | AFTER | INSTEAD OF } { [ Insert ] [ , ] [ Update ] }

      [ WITH APPEND ]

      [ NOT FOR REPLICATION ]

      AS

      [ { IF Update ( column )

      [ { AND | or } Update ( column ) ]

         [ ...n ]

      | IF ( COLUMNS_UpdateD ( ) { bitwise_operator } updated_bitmask )

        { comparison_operator } column_bitmask [ ...n ]

      } ]

      sql_statement [ ...n ]

   }

}

参数说明：

l    trigger_name：触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须惟一。可以选择是否指定触发器所有者名称。

l    table | view：执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。

l    AFTER：指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才被激活。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。

l    INSTEAD OF：指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。

l    WITH APPEND：指定应该添加现有类型的其他触发器。只有当兼容级别是65或更低时，才需要使用该可选子句。

l    NOT FOR REPLICATION：表示当复制进程更改触发器所涉及的表时，不应执行该触发器。

l    AS：触发器要执行的操作。

l    sql_statement：触发器的条件和操作。触发器条件指定其他准则，以确定Delete、Insert或Update语句是否导致执行触发器操作。

l    column：要测试 Insert或Update操作的列名。该列可以是SQL Server支持的任何数据类型。

l    bitwise_operator：用于比较运算的位运算符。

l    updated_bitmask：整型位掩码，表示实际更新或插入的列。

l    comparison_operator：比较运算符。使用等号（=）检查updated_bitmask中指定的所有列是否都实际进行了更新。使用大于号（>）检查updated_bitmask中指定的任一列或某些列是否已更新。

l    column_bitmask：要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。

本示例是建立两个相关表“MR_Stu_XX”和“MR_Stu_CJ”，它们都有“学生编号”和“学生姓名”字段，并且类型相同。在“MR_Stu_XX”表中创建三个触发器，在对“MR_Stu_XX”表进行添加修改的同时，对“MR_Stu_CJ”表也进行相应的操作。
操作步骤如下：
（1）在“MR_SQL”数据库中创建“MR_Stu_CJ”表和“MR_Stu_XX”表。
（2）为“MR_Stu_XX”表创建触发器，SQL语句如下：
用Insert语句创建触发器，当在“MR_Stu_XX”表中添加数据时，“MR_Stu_CJ”表也添加相应的数据。SQL语句如下：

USE MR_SQL
GO
Create TRIGGER TRI_StuXX_Insert ON dbo.MR_Stu_XX
FOR Insert
AS
DECLARE @id Char(4),@name Char(10)
Select @id=学生编号,@name=学生姓名  from inserted
Insert INTO MR_Stu_CJ (学生编号,学生姓名,语文,数学,英语) VALUES (@id,@name,0.0,0.0,0.0)
GO
注意：在表中添加数据时，将添加的数据存放在系统临时表“inserted”中。

用Delete语句创建触发器，当在“MR_Stu_XX”表中删除数据时，“MR_Stu_CJ”表也删除相应的数据。SQL语句如下：
USE MR_SQL
GO
Create TRIGGER TRI_StuXX_Delete ON [dbo].[MR_Stu_XX]
FOR Delete
AS
DECLARE @id char(4),@name char(10)
select @id=学生编号,@name=学生姓名 from deleted
Delete MR_Stu_CJ where 学生编号=@id and 学生姓名=@name
GO
注意：在表中删除数据时，将删除的数据存放在系统临时表deleted中。

用Update语句创建触发器，当在“MR_Stu_XX”表中更新数据时，“MR_Stu_CJ”表也更新相应的数据。SQL语句如下：
USE MR_SQL
GO
Create TRIGGER TRI_StuXX_Uudate ON [dbo].[MR_Stu_XX]
FOR Update
AS
DECLARE @id char(4),@name char(10)
select @id=学生编号 from deleted
select @name=学生姓名 from Inserted
Update MR_Stu_CJ SET 学生姓名=@name Where 学生编号 = @id
GO
注意：在表中更新数据时，将更新前的数据存放在系统临时表“deleted”中，将更新后的数据存放在系统临时表“inserted”中。

3．创建触发器的注意事项

（1）触发器是一个数据库对象，它的命名必须符合SQL Server 2000的标识符命名规则。

（2）Create TRIGGER语句必须是批处理中的第一个语句。也就是说，如果该语句前面还有其他语句，应该使用批处理结束符GO隔开。

（3）不能在临时表或系统表上创建触发器，但是在触发器中可以引用临时表，不能引用系统表。

（4）创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。

（5）触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。

（6）在含有用Delete或Update操作定义的外键的表中，不能定义INSTEAD OF和INSTEAD OF Update触发器。

（7）WRITETEXT语句不会引发Insert或Update触发器。

（8）TRUNCATE TABLE语句不能引发Delete触发器，因为该语句没有记录。