T-SQL

插入数据
更新数据
删除数据
数据查询
查询排序
模糊查询
分组查询
多表联接查询
字符串函数
日期函数
数学函数
系统函数
聚合函数
创建数据库
删除数据库
创建表
删除表
添加约束
删除约束
创建登录帐户
创建数据库用户
向数据库用户授权
局部变量
全局变量
输出语句
IF-ELSE条件语句
WHILE循环语句
CASE多分支语句
批处理语句
简单子查询
IN和NOT IN子查询
EXISTS和NOT RXISTS子查询
T-SQL语句的综合应用
如何创建事务
如何创建索引
如何创建视图
常用的系统存储过程
创建不带参数的存储过程
创建带输入参数的存储过程
创建带输出参数的存储过程
处理错误信息
什么是触发器
创建INSERT触发器
创建DELETE触发器
创建UPDATE触发器
1.1 插入数据:
语法格式: INSERT [INTO] <表名> [列名] VALUES <值列表>

insert into students(sname,saddress,sgrade,semall,ssex)
values('张青','上海松江',6,'zqc@sohu.com',0)

使用DEFAULT(缺省)关键字来代替插入
insert into students(sname,saddress,sgrade,semail,ssex)
values('张青',default,6,'zqc@sohu.com',0)

1.2一次插入多行

a.通过INSERT SELECT语句将现有表中的数据添加到新表
insert into tongxuelu ('姓名','地址','电子邮件')    --此新表已经预先创建
select sname,saddress,semail    
from students

b.通过SELECT INTO 语句将现有表中的数据添加到新表
select students.sname,students.saddress,students.semail
into tongxuelu     --此表是在执行语句的时候创建的,不能预先存在
from students

创建新的标识列:
SELECT IDENTITY(数据类型,标识种子,标识增长量)AS 列名
INTO 新表
FROM 原始表

select students.sname,students.saddress,students.semail,indetity(int,1,1) as studentid
into tongxuelu
from students

c.通过UNION关键字合并数据行插入
insert students(sname,sgrade,ssex)
select '测试女生1',7,0, nuion
select '测试女生2',7,0, nuion
select '测试女生3',7,0, nuion
select '测试女生4',7,0, nuion
select '测试女生1',7,0, nuion
select '测试女生2',7,1, nuion
select '测试女生3',7,1, nuion
select '测试女生4',7,1, nuion
select '测试女生5',7,1
TOP


2.更新数据
语法格式:UPDATE <表名> SET <列名=更新值> [WHERE <更新条件>]

update students set ssex=0

update students
set saddress='北京女子职业技术学校家政班'
where saddress='北京女子职业技术学校刺绣班'

update scores
set scores=scores+5
where scores<=95
TOP


3.删除数据
语法格式:DELETE FROM <表名> [WHERE <删除条件>]
a.

delete from students
where sname='张青'

b.
使用TRUNCATE TABLE删除数据 删除所有行 只删除所有行,但表的结构,列,约束,索引等不会被改动

truncate table students
TOP


4.1数据查询
语法格式:SELECT <列名> FROM <表名> [WHERE <查询条件表达式>] [ORDER BY <排序的列名> [ASC或DESC]]    --默认ASC升序

a.查询所有的数据行和列
select * from students

b.查询部分行列----条件查询
select scode,sname,saddress
from students
where saddress='河南新乡'

select scode,sname,saddress
form students
where saddress<>'河南新乡'

c.在查询中使用列名
select scode as 学员编号,sname as 学员姓名,saddress as 学员地址
from students
where saddress<>'河南新乡'

select firstname+'.'+lastname as '姓名'
from employees

select '姓名'=firstname+'.'+lastname
from employees

d.查询空行  用IS NULL 或者 IS NOT NULL 来判断是否为空行
select sname from students where semail is null

e.在查询中使用常量列  将常量的科缺省信息添加到查询输出中
select 姓名=sname,地址=saddress,'河北新龙' as 学校名称
from students

f.查询返回限制的行数 TOP
select top 5 sname,saddress
from students where ssex=0

select top 20 percent sname,saddress   --按百分比
from students where ssex=0
TOP


4.2查询排序
select studentid as 学员编号,(score*0.9+5) as 综合成绩
from score
where (score+0.9+5)>60
order by score

select au_lname+'.'+au_fname as emp 
from authors union select fname+'.'+lname asemp form employee
order by emp desc

select studentid as 学员编号,score as 成绩
from score
where score>60
order by score,courseid
TOP


4.3.1字符串函数

charindex 用来寻找一个指定的字符串在另一个字符串中的起始位置
select charindex('accp','my accp course',1)  返回:4  找不到返回:0

len 返回传递给它的字符串长度
select len('sql server课程') 返回:12

lower 把传递给它的字符串转换为小写
select lower('SQL Server课程')  返回:sql server课程

upper 把传递给它的字符串转换为大写
select upper('sql server课程')  返回:SQL SERVER课程

ltrim 清除字符左边的空格
select ltrim(' 周智 ')  返回:周智 (后面的空格保留)

rtrim 清除字符右边的空格
select ltrim(' 周智 ')  返回: 周智(前面的空格保留)

right/left 从字符串右边/左边返回指定数目的字符
select right/left('买卖提.吐尔松',3)  返回:吐尔松/买卖提

replace 替换一个字符串中的字符
select replace('莫乐可切.杨可','可','兰')  返回:莫乐兰切.杨兰

stuff 在一个字符串中,删除指定长度的字符,并在该位置插入一个新的字符串
select stuff('ABCDERG',2,3,'我的音乐我的世界')  返回:A我的音乐我的世界EFG
TOP


4.3.2日期函数

getdate 取得当前的系统日期 
select getdate() 返回:今天的日期

dateadd 将指定的数值添加到指定的日期部分后的日期 mm,yy,yyyy,dd,d,hh,h,ss,s,ms
select dateadd(mm,4,'01/01/99') 返回:以当前的日期格式返回 05/01/99 

datediff 两个日期之间的指定日期部分的区别
select datediff(mm,'01/01/99','05/01/99') 返回:4

datename 日期中指定日期部分的字符串形式
select datename(dw,'01/01/2000') 返回:Saturday

datepart 日期中指定日期部分的整数形式
select datepart(day,'01/15/2000') 返回:15
TOP


4.3.3数学函数

abs 取数值表达式的绝对值
select abs(-43) 返回:43

ceiling 取大于或等于指定数值,表达式的最小整数
select ceiling(43.5) 返回:44

floor 取小于或等于指定表达式的最大整数
select floor(43.5) 返回:43

power 取数值表达式的幂值
select power(5,2) 返回:25

round 将数值表达式四舍五入为指定精度
select round(43.543,1) 返回:43.5

sign 对于正数返回+1,对于负数返回-1,对于0则返回0
select sign(-43) 返回:-1

sqrt 取浮点表达式的平方根
select sqrt(9) 返回:3
TOP


4.3.4系统函数

convert 用来转变数据类型
select convert(varchar(5),121345) 返回:字符串12345

current_user 返回当前用户的名字
select current_user 返回:你登录的用户名

datalength 返回用于指定表达式的字节数
select datalength('中国A联盟') 返回:5

host_name 返回当前用户所登录的计算机名字
select host_name() 返回:你所登录的计算机的名字

system_user 返回当前所登录的用户名称
select system_user 返回:你当前所登录的用户名

user_name 从给定的用户ID返回用户名
select user_name(1) 返回:从任意数据库中返回"dbo"
TOP


5.1.1使用LIKE进行模糊查询

select * from students where sname like '张%'

select * from card where id like '00[^8]%[A,C]%'

5.1.2使用BETWEEN在某个范围内进行查询

select * from score where score between 60 and 80

select * from sales where ord_date not between '1992-8-1' and '1993-8-1'

5.1.3 使用IN在列举值内进行查询

select sname as 学员姓名 from students where saddress in/not in ('北京','广州','上海') order by saddress
TOP


5.2聚合函数

5.2.1 SUM 返回表达式中所有数值的总和,只能用于数字类型和列

select sum(ytd_sales) from titles where type='business'
这种查询只返回一个数值,不能够直接与可能返回多行的列一起使用

5.2.2 AVG 返回表达式中所有数值的平均值,只能用于数字类型的列

select avg(score) as 平均成绩 from score where score>=60

5.2.3 MAX和MIN 返回表达式中的最大值和最小值,可用于数字型,字符型和日期/时间类型的列

select avg(score) as  平均成绩,max(score) as 最高分,min(score) as 最低分 from score where score>=60

5.2.4 COUNT 返回提供的表达式中非空值的计数,可用于数字和字符类型的列
也可使用星号(*)作为COUNT的表达式,不必指定特定的列而计算所有的行数

select count(*)  as 及格人数 from score where score>=60
TOP


5.3 分组查询

5.3.1 使用GROUP BY进行分组查询

select courseid,avg(score) as 课程平均成绩
from score
group by courseid

select studentid as 学员编号,courseid as 内部测试,avg(score) as 内部测试平均成绩
from score
group by studentid,courseid

5.3.2 使用HAVING子句进行分组筛选

select studentid as 学员编号,courseid as 内部测试,avg(score) as 内部测试平均成绩
from score
group by studentid,courseid
having count(score)>1

select 部门编号,count(*)
from 员工信息表
where 工资>=2000
having count(*)>1
TOP


5.4 多表联接查询

5.4.1 内联接

a.在WHERE子句中指定联接条件

select students.sname,score.courseid,score.score
from students,score
where student.score=score.studentid

b.在FROM子句中使用JOIN...ON

select s.sname,c.courseid,c.score
from students as s inner join score as c
on(s.score=c.studentid)

select productid,suppliers.supplierid,companyname
from suppliers inner join produces
on(suppliers.suplierid=produces.supplierid)
where unitprice>$10 and companyname like 'F%'

select s.sname as 学员姓名,cs.coursename as 课程名称,c.score as 考试成绩
from students as s
inner join score sa c on (s.scode=c.studentid)
inner join course as cs on (cs.courseid=c.coureid)


5.4.2 外联接

a.左外联接

select s.sname,c.courseid,c.score
form students as s
left outer join score as c on s.scode=c.studentid

b.右外联接

select titles.title_id,titles.title,publishers.pub_name
from titles
right outer join publishers on titles.pub_id=publishers.pub_id
TOP


2.2.1创建数据库:

创建数据库的语法:
CREATE DATABASE 数据库名
ON [PRIMARY]
(
	<数据文件参数> [,...n] [<文件组参数>]
)
[LOG ON]
(
	{<日志文件数> [,...n]}
)

文件的具体参数的语法为:
([NAME=逻辑文件名,]
FILENAME=物理文件名
[,SIZE-大小]
[,MAXSIZE={最大容量|UNLIMITED}]
[,FILEGROUTH=增长量]) [,...n]

文件组参数的语法为:
FILEGROUP 文件组名 <文件参数> [,...n]
"[]"表示可选部分,"{}"表示需要部分
数据库名:最长128字符
PRIMARY:关键字,指定主文件组中的文件
LOG ON:指明日志文件的明确定义
NAME:指定数据库的逻辑名称,这是在SQL Server系统中使用的名称,是数据库在SQL Server中的标识符
FILENAME:指定数据库在文件的操作系统文件名称和路径,该操作系统文件名和NAME的逻辑名称一一对应
SIZE:指定数据库的初始容量大小
MAXSIZE:指定操作系统文件可以增长到最大尺寸
FILEGROUTH:指定文件每次增加容量的大小,当指定数据为0时,表示文件不增长

示例1:一个数据文件和一个日志文件
create database stuDB
on primary  --默认就属于primary主文件组,可省略
(
	/*---数据文件的具体描述---*/
	name='stuDB_data',  --主数据文件的逻辑名
	filename='D:\project\stuDB_data.mdf',  --主数据文件的物理名
	size=5mb,  --主数据文件的初始大小
	maxsize=100mb,  --主数据文件增长的最大值
	filegrowth=15%  --主数据文件的增长率
)
log on
(
	/*---日志文件的具体描述---*/
	name='stuDB_log',
	filename='D:\project\stuDB_log.ldf',
	size=2mb,
	filegrowth=1mb
)
go  --和后续的SQL语句分隔开

示例2:多个数据文件和多个日志文件
create database employees
on primary
(
	/*---主数据文件的具体描述---*/
	name='employee1',
	filename='D:\project\employee1_Data.mdf',
	size=10,
	filegrowth=10%
),
(
	/*---次数据文件的具体描述---*/
	name='employee2',
	filename='D:\project\employee2_Data.ndf',
	size=20,
	maxsize=100,
	filegrowth=1
)
log on
(
	/*---日志文件1的具体描述---*/
	name='employees1',
	filename='D:\project\employeelog1_Log.ldf',
	size=10,
	filegrowth=1
)
(
	/*---日志文件2的具体描述---*/
	name='employees2',
	filename='D:\project\employeelog2_Log.ldf',
	size=10,
	maxsize=100,
	filegrowth=1
)
go
TOP


2.2.2删除数据库:

删除数据库的语法:
DROP DATABASE 数据库名

如何检测是否存在stuDB数据库?
SQL Server将数据库的清单存放在master系统数据库的sysdatabases表中,
只需要查看该表是否存在于该数据库就可以
use master  --设置当前数据库为master,以便访问systatabases表
go
if exists(select * from sysdatabases where name='stuDB')
	drop database stuDB
create database stuDB
on(
	...
)
log on
(
	...
)
go

exists(查询语句)检测某个查询是否存在.如查询返回的记录结果不为空表示存在,反之表示不存在
TOP


2.3.1创建表:


SQL Server中的数据类型

类型
数据类型
描述

整数数据类型
int
存储-2³¹(-2147483648)到2³¹(2147483647)之间的整数,占4个字节

smallint
存储-2¹⁵(-32768)到2¹⁵(32767)之间的整数,占2个字节


tinyint
存储0到255之间的整数,占1个字节


浮点数据类型
numeric
与decimal型相同


real
表示-3.40E+38到3.40E+38之间的浮点数


float
表示-1.79E+308到1.79E+308之间的任意浮点数


decimal
存储从-10³⁸-1到10³⁸-1的固定精度和范围的数值型数据,使用时必须指定范围和精度.左边是总位数,右边是小数点位数


字符数据类型
char
定长非统一编码型的数据,列长宽最大为8000个字符


varchar
变长非统一编码型的数据,与char的区别是,存储长度不是列长,而是数据的长度


text
存储大量的非统一编码型字符数据,最多可以有2³¹-1或20亿个字符


Unicode字符类型
nchar
存储定长统一编码字符型数据,统一编码用双字节来存储每个字符,能存储4000种字符


nvarchar
存储变长统一编码字符型数据,4000种


ntext
存储大量统一编码字符,可以有2³⁰-1或10亿个字符


是/否数据类型
bit
是/否数据类型,其值只能是0,1或空值


二进制数据类型
binary
存储8000字节长的定长二进制数据


varbinary
存储8000字节长的变长二进制数据


image
存储变长的二进制数据,最大可达2³¹-1或20亿字节


货币数据类型
money
用来表示钱和货币值,存储从-9220亿到9220亿之间的数据,可以精确到货币单位的万分之一


smallmoney
存储-214748.3648到214748.3647之间的钱或货币,精确到货币单位的万分之一


日期时间数据类型
datetime
表示日期和时间,存储从1753年1月1日到9999年12月31日,精确到三百分之一秒或3.33毫秒


smalldatetime
存储从1900年1月1日到2079年6月6日,精确到1分钟


特殊数据类型
timestamp
是一种特殊的数据类型,用来创建一个数据库范围内的唯一数码.一个表中只能有一个timestamp列.第次插入或修改一行时,timestamp列的值都会改变,尽管它的名字中有time,但timestamp列不是人们可识别的日期,在一个数据库里,timestamp值是唯一的


uniqueidentifier
用来存储一个全局唯一标识符,即GUID.这个数几乎没有机会在另一个系统中被重建,可以使用NEWID函数或转换一个字符串唯一标识符来初始化具有唯一标识符的列


语法:
CREATE TABLE 表名
(
	字段1 数据类型 列的特征
	字段2 数据类型 列的特征
	...
)
列的特征包括该列是否为空(NULL),是否是标识列(自动编号),是否有默认值,是否为主健等.

示例3:
use studb
go
create table stuinfo
(
	stuname varchar(20) not null,
	stuno char(6) not null,
	stuage int not null,
	stuid numeric(18,0),
	stuseat smallint identity(1,1),
	stuaddress text
)
go
IDENTITY(起始值,递增量)表示stuseat列为自动编号,也称标识列

示例4:
create table stumarks
(
	examno char(7) not null,
	stuno char(6) not null,
	writtenexam int not null,
	labexam int not null
)
go
TOP


2.3.2删除表:
语法:
DROP TABLE 表名

use stuDB
go
if exists(select * from sysobjects where name='stuinfo')
drop table stuinfo
create table stuinfo
(
	...
)
go
TOP


2.4.1添加约束:
常见的约束类型如下:

主键约束:(primary key constraint)
唯一约束:(unique constraint)
检查约束:(check constraint)
默认约束:(default constraint)
外键约束:(foreign key constraint)

添加约束的语法为:
ALTER TABLE 表名
ADD CONSTRAINT 约束名 约束类型  具体的约束说明

示例5:
----添加主键约束 (stuno作为主键)
alter table stuinfo
add constraint pk_stuno primary key (stuno)
----添加唯一约束(身份证号唯一)
alter table stuinfo
add constraint uo_stuid unique(stuid)
----添加默认约束(如果地址不填，默认为“地址不详”)
alter table stuinfo
add constraint df_stuaddress default('地址不详') for stuaddress
----添加检查约束，要求年龄只能在15~40之间
alter table sutinfo
add constraint ck_stuage check(stuage between 15 and 40)
----添加外键约束(主表stuinfo和从表stumarks建立关系，关联字段为stuno)
alter table stumarks
	address constraint fk_stuno
		foreign key(stuno) references stuinfo(stuno)
go
TOP


2.4.2删除约束:
删除约束的语法为：
ALTER TABLE 表名
DROP CONSTRAINT 约束名

例如：删除stuinfo表中地址默认约束的语句为：
alter table stuinfo
drop constraint df_stuaddress
TOP

2.5.1创建登录帐户:
添加windows身份验证登录帐户的语法为：
EXEC sp_grantlogin 'windows 域名\域帐户'

添加SQL身份验证登录帐户的语法为：
EXEC sp_ADDlogin '帐户名','密码'
删除SQL身份验证登录帐户的语法为：
EXEC sp_droplogin '帐户名'

示例6：
/*--添加windows登录帐户--*/
exec sp_grantlogin 'jbtraining\s26301'     ----windows 用户为jbtraining\s26301,jbtraining表示域
/*--添加SQL登录帐户--*/
exec sp_addlogin 'zhangsan','1234'         ----帐户名为zhangsan，密码为1234
go
TOP


2.5.2创建数据库用户:
语法为：
EXEC sp_grantdbaccess '登录帐户','数据库用户'
其中，“数据库用户”为可选参数，默认为登录帐户，即数据库用户默认和登录帐户同名。
删除数据库用户语法：
EXEC sp_revokedbaccess '数据库用户'

示例7：
/*--在studb数据库中添加两两个用户--*/
use studb
go
exec sp_grantdbaccess 'jbtraining\s26301','s36301dbuser'  --s26301dbuser为数据库用户名
exec sp_grantdbaccess 'zhangsan','zhangsandbuser'
TOP


2.5.3向数据库用户授权:
常用的权限包括：添加(insert)、删除(delete)、更新(update)、查看(select)、创建(create table)
授权的语法为：
GRANT 权限 [ON 表名] TO 数据库用户

示例8：
use studb
go
/*--为zhangsandbuser 分配对表stuinfo的select,insert,update权限--*/
grant select,insert,update on stuinfo to zhangsandbuser
/*--为s26301dbuser分配建表的权限--*/
grant create table to s26301dbuser
TOP


3.1.1局部变量:
局部变量的名称必须以标记@作为前缀.
声明局部变量的语句为: DECLARE @variable_name DataType
其中,variable_name为局部变量的名称,DataType为数据类型.

例如:
declare @name varchar(8) --声明一个存放学员姓名的变量name,最多可以存储8个字符
declare @seat int --声明一个存放学员座位号的变量seat

局部变量的赋值有两种方法:使用SET语句或SELECT语句
语法:
SET @variable_name=value 或 SELECT @variable_name=value

示例1:

  
     
    stuName
    stuNO
    stuSex
    stuAge
    stuSeat
    stuAddress
  
  
    1   
    张秋丽
    s25301
    男
    18
    1
    北京海淀
  
  
    2
    李文才
    s25302
    男
    31
    3
    地址不详
  
  
    3
    李斯文
    s25303
    女
    22
    2
    河南洛阳
  
  
    4
    欧阳俊雄
    s25304
    男
    28
    4
    新疆威武哈
  

问题:根据座位号查找李文才的左右同桌.
/*--查找李文才的信息--*/
declare @name varchar(8) --学员姓名
set @name='李文才' --使用SET赋值
select * from stuinfo where stuname=@name

/*--查找李文才的左右同桌--*/
declate @seat int --座位号
select @seat=stuseat from stuinfo where stuname=@name --使用select赋值
select *from stuinfo where (stuseat=@seta+1) or (stuseat=@seat-1)
go
批处理结果如下:


 
stuName
stuNo
stuSex
stuAge
stuSeat
stuAddress
1 
李文才 s25302 男 31 3 地址不详




 
stuName
stuNo
stuSex
stuAge
stuSeat
stuAddress
1 
李斯文 s25303 女 22 2  河南洛阳
2 
欧阳俊雄 s25304 男 28 4  新疆威武哈


TOP


3.1.2全局变量:
SQL Server中的所有全局变量都使用两个@标志作为前缀.
常用的全局变量如表3.1所示

表3.1 全局变量
变量 含义
@@ERROR 最后一个T-SQL错误的错误号
@@IDENTITY 最后一次插入的标识值
@@LANGUAGE 当前使用的语言的名称
@@MAX_CONNECTIONS 可以创建的同时连接的最大数目
@@ROWCOUNT 受上一个SQL语句影响的行数
@@SERVERNAME 本地服务器的名称
@@SERVICENAME 该计算机上的SQL服务的名称
@@TIMETICKS 当前计算机上每刻度的微秒数
@@TRANSCOUNT 当前连接打开的事务数
@@VERSION SQL Server的版本信息

TOP


3.2输出语句:
常用的输入语句有两种,它们的语法分别是:
print局部变量或字符串
SELECT局部变量AS自定义列名
其中,第二种方法就是查询语句的特殊应用

示例2:
print '服务器的名称:' + @@servername
select @@servername as '服务器名称'
用print方法输出结果将在消息窗口以文本方式显示,用select方法结果将在网格窗口以表格方式显示.

由于使用print语句要求单个局部变量或字符串表达式作为参数,所以如果这样编写SQL语句将会出错
print '当前错误号' + @@error
因为全局变量@@error返回的是整形数值,应把数值转换为字符串,如下:
print '当前错误号' + convert(varchar(5),@@error)

示例3:
insert into stuinfo(stuname,stuno,stusex,stuage) values('梅超风','s25318','女','23')
print '当前错误号'+ convert(varchar(5),@@error)  --如果大于0,表示上一条语句执行有错误
print '刚才报名的学员,座位号为:' + convert(varchar(5),@@identity)
TOP


3.3.1IF-ELSE条件语句:
语法:

IF (条件)
	语句或语句块
ELSE
	语句或语句块
语句块使用BEGIN...END表示

IF(条件)
	BEGIN
		语句1
		语句2
		...
	END
ELSE
	...
	
示例4:
declare @myavg float
select @myavg=avg(writtenexam) from stumarks
print '本班平均分' +convert(varchar(5),@myavg)
if(@myavg>70)
	begin
		print '本班笔试成绩优秀,前三名的成绩为'
		select top 3 * from stumarks order by writtenexam desc
	end
else
	begin
		print '本班笔试成绩较差,后三名的成绩为'
		select top 3 * from stumarks order by writtenexam
	end

TOP


3.3.2WHILE循环语句:
语法:

WHILE(条件)
	语句或语句块
	[BREAK]
	
示例5:
insert into sutmark(examno,stuno,writtenexam,labexam) --插入测试数据
values('s271819','s25318',56,48)
select * from stumarks
declare @n int
while(1=1)  --条件永远成立
	begin
		select @n=count(*) from stumarks where writtenexam < 60  --统计不及格人数
		if(@n>0)
			update stumarks set writtenexam=writtenexam+2
		else
			break  --退出循环
	end
print '加分后的成绩如下:'
select * from stumarks
TOP


3.3.3CASE多分支语句:
语法:

CASE
	WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	[ELSE 其他结果]
END

示例6:
select * from stumarks --原始成绩
print 'ABCDE 五级显示成绩如下:'
select stuno,成绩=case
					when writtenexam < 60 then 'E'
					when writtenexam between 60 and 69 then 'D'
					when writtenexam between 70 and 79 then 'C'
					when writtenexam between 80 and 89 then 'B'
					else 'A'
				  end
from stumarks
TOP


3.4批处理语句:

SQLServer规定:如果是建库、建表语句、以及存储过程和视图等，必须在语句末尾添加“GO”批处理标志
TOP


4.1简单子查询:
方法一：采用T-SQL变量实现
示例1：
declare @age int  --定义变量，用于存放李斯文的年龄
select @age=stuage from stuinfo where stuname='李斯文'  --求出李斯文的年龄
select * from stuinfo where stuage > @age  --筛选比李斯文年龄大的学员
go
替换为：示例2：
select * from stuinfo
	where stuage > (select stuage from stuinfo where stuname='李斯文')
go

方法二：采用表连接
示例3：
select stuname from stuinfo inner join stumarks  --inner join 内部连接
	on stuinfo.stuno=stumarks.stuno where writtenexam=60
go
替换为：示例4：
select stuname from stuinfo
	where stuno=(select stuno from stumarks where writtenexam=60)
go

一般来说，表连接都可以用子查询替换，但反过来说却不一定
TOP


4.2IN和NOT IN子查询:
使用“=”、“>”等比较运算符号时，要求子查询只能返回一条或空的记录，当子查询跟随在=、!=、<、<=、>、>=之后，不允许子查询返回多条记录。
解决方法：将“=”改为“IN”就可以了

示例5：采用IN子查询
select stuname from stuinfo
	where stuno in(select stuno from stumarks where writtenexam=60)
go

示例6：
/*--采用IN子查询参加考试的学员名单--*/
select stuname from stuinfo
	where stuno in(select stuno from stumarks)
go

示例7：
/*--采用NOT IN子查询，查看未参加考试的学员名单--*/
select stuname from stuinfo
	where stuno not in(select stuno from stumarks)
go
TOP


4.3EXISTS和NOT RXISTS子查询:
语法为：

IF EXISTS (子查询)
	语句

示例8:	
/**--采用EXISTS子查询，进行酌情加分--*/
if exists (select * from stumarks where writtenexam>80)
	benin
		print '本班有人笔试成绩高于80分，每人只加2分，加分后的成绩为：'
		update stumarks set writtenexam=writtenexam+2
		select * from stumarks
	end
else
	begin
		print '本班无人笔试成绩高于80分，第人可以加5分，加分后的成绩为：'
		update stumarks set writtenexam=writtenexam+5
		select * from stumarks
	end
go

示例9:
/*--采用NOT EXISTS子查询,根据试题难度加分--*/
if not exists(select * from stumarks where writtenexam>60 and labexam>60)
	begin
		print '本班无人通过考试,试题偏难,每人加3分,加分后的成绩为:'
		update stumarks set writtenexam=writtenexam+3,labexam=labexam+3
		select * from stumarks
	end
else
	begin
		print '本班考试成绩一般,第人只加1分,加分后的成绩为:'
		update stumarks set writtenexam=writtenexam+1,labexam=labexam+1
		select * from stumarks
	end
go
TOP


4.4T-SQL语句的综合应用:
假定目前本次考试学员信息表(stuinfo)和学员成绩表(stumarks)的原始数据为如下表所示:

    stuname  stuno stusex stuage stuseat stuaddress
1 张秋丽 s25301 男 18 1 北京海淀
2 李文才 s25302 男 31 3 地址不详
3 李斯文 s25303 女 22 2 河南洛阳
4 欧阳俊雄 s25304 男 28 4 新疆威武哈
5 梅超风 s25318 女 23 5 地址不详


   examno stuno writtenexam labexam
1 s271811 s25303 93 59
2 s271813 s25302 63 91
3 s271816 s25301 90 83
4 s271817 s25318 63 53

问题:
1.统计本次考试的缺考情况,结果如下面第一个表所示.
2.提取学员的成绩信息保存结果,包括学员姓名,学号,笔试成绩,机试成绩,是否通过.
3.比较笔试平均分和机试平均分,较低者进行循环提分,但提分后最高不能超过97分.
4.提分后,统计学员的成绩和通过情况,如下面第二个表所示.
5.提分后统计学员的通过率情况,如下面第三个表所示.


   应到人数 实到人数 缺考人数
1 5 4 1


   姓名 学号 笔试成绩 机试成绩 是否通过
1 张秋丽 s25301 90 89 是
2 李文才 s25302 63 97 是
3 李斯文 s25303 93 65 是
4 欧阳俊雄 s25304 缺考 缺考 否
5 梅超风 s25318 63 59 否


   总人数 通过人数 通过率
1 5 3 60%

示例10:
/*--本次考试的原始数据--*/
--select * from stuinfo--
--select * from stumarks--
/*--统计考试缺考情况--*/
select 应到人数=(select count(*) from stuinfo),--应到人数为子查询表达式的别名
       实到人数=(select count(*) from stumarks),
	   缺考人数=((select count(*) from stuinfo)-(select count(*) from stumarks))

/*--统计考试通过情况,并将统计结果存放在新表newtable中--*/
if exists(select * from sysobjects where name='newtable')
drop table newtable

select stuname,stuinfo.stuno,writtenexam,labexam,ispass=case
	when writtenexam>=60 andlabexam>=60 then 1
    else 0
  end
into newtable from stuinfo left join stumarks on stuinfo.stuno=stumarks.stuno

--select * from newtable--查看统计结果,可用于调试

/*--酌情加分:比较笔试和机试的平均分,哪科偏低,就给哪科提分--*/
declare @avgwritten numeric(4,1),@avglab numeric(4,1) --定义变量存放笔试和机试平均分
select @avgwritten=avg(writtenexam) from newtable where writtenexam is not null
select @avglab=avg(labexam) from newtable where labexam is not null
if @avgwritten < @avglab --比较笔试和机试平均,看哪科偏低
	while (1=1) --循环给笔试加分,最高不能超过97分
		begin
			update newtable set writtenexam=writtenexam+1
			if(select max(writtenexam) from newtable) >=97
			break
		end
else
	while(1=1) --循环给机试加分,最高不能超过97分
		begin
			update newtable set labexam=labexam+1
			if(select max(labexam) from newtable)>=97
			break
		end
		
--因为提分,所以需要更新ispass(是否通过)列的数据
update newtable
	set ispass=case
		when writtenexam>=60 and labexam>=60 then 1
		else 0
	end
	
--select * from newtable --查看更新ispass列后的成绩和通过情况,可用于调试

/*--显示考试最终通过情况--*/
select 姓名=stuname,学号=stuno,笔试成绩=case
		when writtenexam is null then '缺考'
		else convert(varchar(5),writtenexam)
		end
	,机试成绩=case
		when labexam is null then '缺考'
		else convert(varchar(5),labexam)
		end
	,是否通过=case
		when ispass=1 then '是'
		else '否'
		end
	from newtable
	
/*--显示通过率及通过人数--*/
select 总人数=count(*),通过人数=sum(ispass),通过率=(convert(varchar(5),avg(ispass*100))+'%') from newtable
go
TOP


5.1.3如何创建事务:
T-SQL使用下列语句来管理事务：

开始事务：BEGIN TRANSACTION
提交事务：COMMIT TRANSACTION
回滚（撤销）事务：ROLLBACK TRANSACTION
事务的分类：

显式事务：用BEGIN TRANSACTION明确指定事务的开始
隐性事务：通过设置SET IMPLICIT_TRANSACTIONS ON语句，将隐性事务模式设置为打开。当以隐性事务操作时，SQL Server将在提交或回滚
事务后自动启动新事务。无法描述事务的开始，只需提交或回滚每个事务。
自动提交事务：这是SQL Server的默认模式，它将每条单独的T-SQL语句视为一个事务。
如果成功执行，则自动提交。如果错误，则自动回滚。

示例3：
use studb
go
--恢复原来的数据
--update bank set currentmoney=currentmoney-1000 where customername='李四'
set nocount on  --不显示受影响的行数信息
print '查看转帐事务前的余额'
select * from bank
go
/*--开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体）--*/
begin transaction
/*--定义变量，用于累计事务执行过程中的错误--*/
declare @errorsum int
set @errorsum=0 --初始化为0，即无错误
/*--转帐：张三的帐户少1000元，李四的帐户多1000元--*/
update bank set currentmoney=currentmoney-1000
where customername='张三'
set @errorsum=@errorsum+@@error  --累计是否有错误
update bank set currentmoney=currentmoney+1000
where customername='李四'
set @errorsum=@errorsum+@@error  --累计是否有错误

print '查看转帐事务过程中的余额'
select * from bank

/*--根据是否有错误，确定事务是提交还是撤销--*/
if @errorsum<>0  --如果有错误
	begin
		print '交易失败，回滚事务'
		rollback transaction
	end
else
	begin
		print '交易成功，提交事务，写入硬盘，永久的保存'
		commit transaction
	end
go

print '查看转帐事务后的余额'
select * from bank
go
TOP


5.2.2如何创建索引:
创建索引的语法为:

CREATE [UNIOUE] [CLUSTERED|NONCLUSTERED] INDEX index_name
ON table_name (column_name[,column_name]...)
[WITH
	FILLFACTOR=x]
其中:

UNIQUE指定唯一索引,可选.
CLUSTERED,NONCLUSTERED指定是聚集索引还是非聚集索引,可选.
FILLFACTOR表示填充因子,指定一个0～100的值,该值指示索引页填满的空间
所占的百分比.

示例4:
因数成绩表stumarks中的笔试列(writtenexam)经常查询,为了加快查询速度,现创建索引.因为笔试成绩可能
会重复,索引只能创建非聚集索引,T-SQL语句如下:
use studb
go
/*--检测是否存在该索引存放在系统表sysindexed中--*/
if exists (select name from sysindexex where name='ix_stumarks_writtenexam')
	drop index stumarks.ix_stumarks_writtenexam  --删除索引
/*--笔试列创建非聚集索引:填充因子为30%--*/
create nonclustered index ix_stumarks_writtenexam
	on stumarks(writtenexam) with fillfactor=30
go
创建索引后,可以像查找字词一样,选择拼音查找方式或笔画查找方式.也可以指定SQL Server数据查询的
查询方式,如示例5所示:
示例5:
/*--指定按索引:ix_stumarks_writtenexam查询--*/
select * from stumarks
	(index=ix_stumarks_writtenexam)
		where writtenexam between 60 and 90
虽然我们可以指定SQL Server按哪个索引进行数据查询,但一般不需要人工指定.SQL Server将会根据所创建
的索引,自动优化查询.
使用索引可以加快数据检索速度,但为每个字段建立索引是没有必要的.因为索引自身也需维护,并占用一定的资源
可以按照下列标准选择建立索引的列.

该列用于频繁搜索.
该列用于对数据进行排序.
请不要使用下面的列创建索引.
列中仅包含几个不同的值.
表中仅包含几行.为小型表创建索引可能不太划算,因为SQL Server在索引中搜索数据所花
的时间比在表中逐行搜索所花的时间更长.
TOP


5.3.2如何创建视图:
创建视图的语法为:
CREATE VIEW view_name
	as
	< select 语句>
	
示例6:
use studb
to
/*--检测是否存在:视图存放在系统表sysobjects中--*/
if exists (select * from sysobjects where name='view_stuinfo_stumarks')
	drop view view_stuinfo_stumarks
go
/*--创建视图:查看学员的成绩情况--*/
create view view_stuinfo_stumarks
	as
		select 姓名=stuname,学号=stuinfo.stuno,笔试成绩=writtenexam,机试成绩labexam,平均分=(writtenexam+labexam)/2
			from stuinfo left join stumarks on stuinfo.stuno=stumarks.stuno
go
/*--使用视图:视图是一个虚拟表,可以像物理表一样打开--*/
select * from view_stuinfo_stumarks

说明:
从一个或者多个表或视图中导出的虚拟表,其结构和数据是建立在对表的查询基础上的.理论上它可以像变通的物理表一样使用,
例如增,删,改,查等,修改视图实际上是修改原始数据表.因为修改视图有许多限制,所以实际开发中一般视图仅做查询使用.
TOP


6.2常用的系统存储过程:

系统存储过程 说明
sp_databases 列出服务器上的所有数据库
sp_helpdb 报告有关指定数据库或所有数据库的信息
sp_renamedb 更改数据库的名称
sp_tables 返回当前环境下可查询的对象的列表
sp_columns 返回某个表列的信息
sp_help 查看某个表的所有信息
sp_helpconstraint 查看某个表的约束
sp_helpindex 查看某个表的索引
sp_stored_procedures 列出当前环境中的所有存储过程
sp_password 添加或修改登录帐户的密码
sp_helptext 显示默认值,未加密的存储过程,用户定义的存储过程,触发器或视图的实际文本

示例1:
--purpose:常用系统存储过程的使用
EXEC sp_databases  --列出当前系统中的数据库
EXEC sp_renamedb 'northwind','northwind1'  --改变数据库名称(单用户访问)
use studb
go
EXEC sp_tables  --当前数据库中可查询对象的列表
EXEC sp_columns stuinfo  --查看stuinfo中列的信息
EXEC sp_help stuinfo  --查看stuinfo的信息
EXEC sp_helpconstraint stuinfo  --查看表stuinfo的约束
EXEC sp_helpindex stumarks  --查看表stumarks的索引
EXEC sp_helptext 'view_stuinfo_stumarks'  --查看视图的语句文本
EXEC sp_stored_procedures  --返回当前数据库中的存储过程列表

提示:在企业管理器中一般不能修改数据库的名称,在查询分析器调用sp_renamedb,
系统存储过程修改数据库非常实用.
一个常用的扩展存储过程:xp_cmdshell,它可以完成DOS命令下的一些操作,其具体用计为:
EXEC xp_cmdshell DOS命令[NO_OUTPUT]
NO_OUTPUT为可选参数,设置执行DOS命令后是否输出返回信息,如示例2:
--purpose:xp_cmdshell扩展存储过程的使用
use master
go
/*--创建数据库bankdb,要求保存在D:\bank--*/
exec xp_cmdshell 'mkdir d:bank',no_output  --创建文件夹D:\bank
--创建库bankdb
if exists(...)
drop...
go
create database bankdb
on
(
...
)
log on
(
...
)
go

exec xp_cmdshell 'dir d:\bank\' --查看文件
TOP


6.3.1创建不带参数的存储过程:
语法:
CREATE PROC[EDURE] 	存储过程名
		[{@参数1 数据类型}[=默认值][OUTPUT],
		  ......,
		 {@参数n 数据类型}[=默认值][OUTPUT]
		]
	AS
		SQL语句
参数部分可选.
示例3:
希望查看本次考试平均分以及未通过考试的学员名单:
use studb
go
/*--检测是否存在:存储过程存放在系统表sysobjects中--*/
if exists(select * from sysobjects where name='proc_stu')
	drop procedure proc_stu
go
/*--创建存储过程--*/
create procedure proc_stu
	as
		declare @writtenavg float,@labavg float
		select @writtenavg=avg(writtenexam),@labavg=avg(labexam) from stumarks
		print '笔试平均分:'+convert(varchar(5),@writtenavg)
		print '机试平均分:'+convert(varchar(5),@labavg)
		if(@writtenavg>70 and @labavg>70)
			print '本班考试成绩:优秀'
		else
			print '本班考试成绩:较差'
		print '-----------------------------'
		print '          参加本次考试没有通过的学员:'
		select stuname,stuinfo.stuno,writtenexam,labexam from stuinfo inner join stumarks
			on stuinfo.stuno=stumarks.stuno where writtenexam < 60 or labexam < 60
go
/*--调用执行存储过程--*/
exec proc_stu  
TOP


6.3.2创建带输入参数的存储过程:
语法:
CREATE PROC[EDURE] 存储过程名
		@参数1 数据类型[=默认值][OUTPUT],
		......,
		@参数2 数据类型[=默认值][OUTPUT]
	AS
		SQL语句
其中,如果参数后面没有"OUTPUT"关键字,表示此参数为输出参数,否则视为普通的输入参数,输入参数还可以设置为默认值.

示例4:
use studb
go
if exists(...)
	drop...
go
create procedure proc_stu
	@writtenpass int,
	@labpass int
	as
		print '-----------------------------------'
		print '            参加本次考试没有通过的学员:'
		select stuname,stuinfo.stuno,writtenexam,labexam from stuinfo inner join stumarks on 
			stuinfo.stuno=stumarks.stuno
			where writtenexam < @writtenpass or labexam < @labpass
go
exec proc_stu 60,55

示例5:
...
	@writtenpass int=60,
	@labpass int=60
...
exec proc_stu  --都采用默认值:笔试和机试及格线都为60分
exec proc_stu 64  --机试采用默认值:笔试为64分,机试为60分
exec proc_stu 60,55  --都不采用默认值
--错误的调用方式: exec proc_stu  ,55  --希望笔试采用默认,机试为55分
--错误的调用方式: exec proc_stu @labpass=55  --希望笔试采用默认,机试为55分
TOP


6.3.3创建带输出参数的存储过程:
示例6:
use studb
go
if exists (select * from sysobjects where name='proc_stu')
	drop procedure proc_stu
go
create procedure proc_stu
	@notpasssum int output,  --output关键字,否则视为输入参数
	@writtenpass int=60,  --默认参数放后
	@labpass int=60  --默认参数放后
	as
		print '笔试及格线:'+convert(varchar(5),@writtenpass)+'机试及格线:'+convert(varchar(5),@labpass)
		print '------------------------------------------'
		print '                    参加本次考试没有通过的学员:'
		select stuname,stuinfo.stuno,writtenexam,labexam from stuinfo inner join sumarks on 
			stuinfo.stuno=stumarks.stuno
				where writtenexam < @writtenpass or labexam < @labpass
go

declare @sum int
exec proc_stu @sum output,64  --调用时也带OUTPUT关键字,机试及格线默认60
print '----------------------------------------'
if @sum >= 3
	print '未通过人数:'+convert(varchar(5),@sum)+'人,超过60%,及格分数线还应下调'
else
	print '未通过人数:'+convert(varchar(5),@sum)+'人,已控制在60%以下,及格分数线适中'
go
TOP


6.3.4处理错误信息:
RAISERROR语句的语法如下:
RAISERROR({msg_id|msg_str}{,severity,state}[WITH option[,...n]]
其中,msg_id:在sysmessages系统表中指定的用户定义错误信息.
msg_str:用户定义的特定信息,最长255个密切协作字符.
severity:与特定信息相关联,表示用户定义的严重级别.用户可使用级别为0～18级.19～25级是为sysadmin固定角色的成员预留的,并且需要指定WITH LOG 选项.
20～25级错误被认为是致命错误.
state:表示错误的状态,是1～127的值.
option:指示是否将错误记录到服务器错误日志中.

示例7:
use studb
go
if exists (select * from sysobjects where name='proc_stu')
	drop procedure proc_stu
go
create procedure proc_stu
	@notpasssum int output,  --output关键字,否则视为输入参数
	@writtenpass int=60,  --默认参数放后
	@labpass int=60  --默认参数放后
	as
		/*------------------------------错误处理-------------------------------*/
		if(not @writtenpass between 0 and 100) or (not @labpass between 0 and 100)
			begin
				raiserror('及格线错误,请指定0-100之间的分数,统计中断退出',16,1)
				return   ---立即返回,退出存储过程
			end
		
		print '笔试及格线:'+convert(varchar(5),@writtenpass)+'机试及格线:'+convert(varchar(5),@labpass)
		print '------------------------------------------'
		print '                    参加本次考试没有通过的学员:'
		select stuname,stuinfo.stuno,writtenexam,labexam from stuinfo inner join sumarks on 
			stuinfo.stuno=stumarks.stuno
				where writtenexam < @writtenpass or labexam < @labpass
go
/*----调用存储过程----*/
declare @sum int
exec proc_stu @sum output,604  --调用时也带OUTPUT关键字,笔试及格线输入604分
set @t=@@error  --如果出现了错误,执行了raiserror语句,系统全局@error将不等于0,表示有错
print '错误号:'+convert(varchar(5),@t)
if @t <> 0
	return  --退出批处理,后结语名不再执行
print '----------------------------------------'
if @sum >= 3
	print '未通过人数:'+convert(varchar(5),@sum)+'人,超过60%,及格分数线还应下调'
else
	print '未通过人数:'+convert(varchar(5),@sum)+'人,已控制在60%以下,及格分数线适中'
go
TOP


7.2什么是触发器:
触发器是在对表进行插入、更新或删除操作时自动执行的存储过程。
触发器通常用于强制业务规则，它是一种高级约束，可以定义比用CHECK约束更为复杂的约束。可执行复杂的SQL语句（if/while/case），可引用其他表中的列。
触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行修改，诸如UPDATE、INSERT、DELETE这些操作时，
SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。由此触发器可分为以下几种。
INSERT触发器：当向表中插入数据时触发，自动执行触发器所定义的SQL语句。
UPDATE触发器：当更新表中某列、多列时触发，自动执行触发器所定义的SQL语句。
DELETE触发器：当删除表中记录时触发，自动执行触发器所定义的SQL语句。
每个触发器有两特殊的表：插入表（inserted表）和删除表（deleted表）。这两个表是逻辑表，并且这两个表是由系统管理的，存储在内存中，不是存储在数
据库中，因此，不允许用户直接对其修改。
这两个表的结构与被该触发器作用的表在相同的表结构。这两个表是动态驻留在内存中的，当触发器工作完成，它们也被删除。这两个表主要保存用户操作
而被影响到的原数据值或新数据值。另外这两个表是只读的，即用户不能向其写入内容，但可以引用表中的数据。例如可用语句查看deleted表的中的信
息：select * from deleted。
deleted表：用于存储DELETE和UPDATE语句所影响的行的副本，即在deleted表中临时保存了被删除或被更新前的记录行。在执行DELETE或UPDATE语句时，行从触发器表中删除，
并传输到deleted表中。由此我们可以从deleted表中检查删除的数据行是否能删除。如果不能，就可以回滚撤销此操作，因为触发器本身就是一个特殊的事务单元。
inserted表：用于存储INSERT和UPDATE语句所影响的行的副本，即在inserted表中临时保存了被插入或被更新后的记录行。在执行INSERT或UPDATE语句时，新加行被同时添加到
inserted表和触发器表中。由此我们可以从inserted检查插入的数据是否满足业务需求。如果不满足，就可以向用户报告错误消息，并回滚撤销操作。
更新（UPDATE）语句类似于在删除之后执行插入：首先旧行被复制到deleted表中，然后新行被复制到触发器表和inserted表中。
综上所述，inserted表和deleted表用于临时存放对表中数据行的修改信息，它们在具体的增加、删除、更新操作时的情况如表7.1所示。

表7.1  inserted表和deleted表
修改操作 inserted表 deleted表
增加（INSERT）记录时 存放新增的记录 ......
删除（DELETE）时 ...... 存放被删除的记录
修改（UPDATE）时 存放用来更新的新记录 存放更新前的记录

触发器的主要作用是，实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性，除此之外触发器还有其他许多功能，下面简单介绍这些功能。
1.强化约束
触发器能够实现比CHECK语句更为复杂的约束。
约束和触发器在特殊情况下各有优势。触发器的主要好处在于它们可以包含使用T-SQL代码的复杂处理逻辑。因此，触发器可以支持约束的所有功能；但它在所
给出的功能并不总是最好的方法。
实体完整性总应在最低级别上通过索引进行强制，这此索引或是PRIMARY KEY和UNIQUE约束的一部分，或是在约束之外独立创建的。假设功能可以满足应用程序
的功能需求，域完整性应通过CHECK约束进行强制，而引用完整性（RI）则应通过FOREIGN KEY约束进行强制。
在约束所支持的功能无法满足应用程序的功能需求时，触发器就极为有用。例如：

除非REFERENCES子句定义了级联引用操作，否则FOREIGN KEY约束只能以与另一列中的值完全匹配的值来验证列值。
CHECK约束只能根据逻辑表达式或同一表中另一列来验证列值。如果应用程序要求根据另一个表中的列验证列值，则必须使用触发器。
约束只能通过标准的系统错误传递错误信息。如果应用程序要求使用（或能从中获益）自定义作息和较为复杂的错误处理，则必须使用触发器。
2.跟踪变化
触发器可以侦测数据库内的操作从而不允许数据库中未经许可的指定更新和变化。
3.级联运行
触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如某个表上的触发器中包含对另外一个表的数据库操作，如删除、更新、插入。
而该操作又导致该表上触发器被触发。触发器可通过数据库中的相关表实现级联更改；不过，通过级联引用完整性约束可以更有效地执行这些更改。

触发器可以禁止或回滚违反引用完整性的更改，从而取消所尝试的数据修改。当更改外键且新值与主键不匹配时，此类触发器就可能发生作用。例如，可以在stumarks表上创建
一个插入触发器，使它在新值与stuinfo中的某个值不匹配时回滚一个插入。不过，通常使用FOREIGN KEY来达到这个目的。
如果触发器表上存在约束，则在执行INSERT、UPDATE及DELETE触发器执行前检查这些约束。如果不满足约束，则不执行INSERT、UPDATE及DELETE触发器。

提示：INSERT、UPDATE、DELETE触发器在数据行已修改完成后，对修改的数据行进行必要的善后处理。若发现有错误，则用事务回滚（ROLLBACK TRANSACTION）
撤销本次操作，所以INSERT、UPDATE、DELETE触发器在约束检查之后才执行。
TOP


7.3.1创建INSERT触发器:
创建触发器的语法如下:
CREATE TRIGGER Trigger_name
ON table_name
	[WITH ENCRYPTION]
	FOR {[DELETE,INSERT,UPDATE]}
		AS SQL语句
对参数说明如下:
trigger_name:触发器名称.触发器名称必须符合标识符规则,并且在数据库中须唯一.可以选择是否指定触发器所有者名称.
table_name:是在其上执行触发器的表或视图,有时称为触发器表或触发器视图.可以选择是否指定表或视图的所有者名称.
WITH ENCRYPTION:加密syscomments表中包含CREATE TRIGGER语句文本的条目.使用WITH ENCRYPTION可防止将触发器作为SQL Server复制的一部分发布.
{[DELETE,INSERT,UPDATE]}:是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字.必须至少指定一个选项.如果指定的选项多于一个,需用逗号分隔.

示例2:
问题:当我们向交易信息表stansinfo中插入一条交易信息时,应自动更新对应帐户的余额.
use studb
go
/*--------检测是否存在,触发器存放在系统表sysobjects中--------*/
if exists (select name from sysobjects where name='trig_transinfo')
	drop trigger trig_transinfo
go
/*--------创建INSERT触发器:在交易信息表transinfo上创建插入触发器--------*/
create trigger trig_transinfo
  on transinfo
     for insert
	    as
		  /*----定义变量:用于临时存放插入的卡号,交易类型,交易金额等---*/
		  declare @type char(4),@outmoney money
		  declare @mycardid char(10),@banlance money
		  /*--从inserted临时表中获取插入的记录行信息:包括交易类型,卡号,交易金额--*/
		  select @type=transtype,@outmoney=transmoney,@mycardid=cardid from inserted
		  /*--根据交易类型是支取/存入,减少或增加帐户表band中对应卡号的余额--*/
		  if(@type='支取')
		  	update band set currentmoney=currentmoney-@outmoney where cardid=@mycardid
		  else
		  	update band set currentmoney=currentmoney+@outmoney where cardid=@mycardid
		  /*--显示交易金额及余额--*/
		  print '交易成功!交易金额:'+convert(varchar(20),@outmoney)
		  select @balance=currentmoney from band where cardid=@mycardid
		  print '卡号:'+@mycardid+'余额:'+convert(varchar(20),@balance)
go

/*--测试触发器插入测试数据:张三取钱200,李四存钱50000--*/
--delete from transinfo
set nocount on
insert into stansinfo(cardid,transtype,transmoney)
	values('10010001','支取',200)
insert into stansinfo(cardid,transtype,transmoney)
	values('10010002','存入',50000)	
--查看结果
select * from bank
select * from transinfo
TOP


7.3.2创建DELETE触发器:
示例3:
当删除交易信息表时,自动备份被删除的数据到表backuptable中.
use studb
go
if exists (select name from sysobjects where name='trig_delete_transinfo')
	drop trigger trig_delete_transinfo
go
create trigger trig_delete_transinfo
  on transinfo
     for delete
	    as
		  print '开始备份数据,请稍后...'
		  if not exists (select * from sysobjects where name='backuptable')
		  	select * into backuptable from delete  --从deleted表中获取被删除的数据
		  else
		  	insert into banckuptable select * from deleted
		  print '备份数据成功,备份表中的数据为:'
		  select * from backuptable
go
TOP


7.3.3创建UPDATE触发器:
示例4:
跟踪用户的交易,交易金额超过2000元,则取消交易,并给出错误提示.
use studb
go
if exists (select name form systojects where name='trig_update_bank')
	drop trigger trig_update_bank
go
create trigger trig_update_bank
	on bank
		for update
			as
				declare @beforemoney money,@aftermoney money
				select @beroremoney=currentmoney from deleted
				select @aftermoney=currentmoney from inserted
				if abs(@aftermoney-@beforemoney)>20000  --abs 为取绝对值
					begin
						print '交易金额:'+convert(varchar(8),abs(@aftermoney-@beforemoney))
						raiserror('每笔交易不能超过2万元,交易失败',16,1)
						rollback transaction --回滚事务,撤销交易
					end
go
/*--测试触发器:修改余额--*/
set nocount on
update bank set currentmoney=currentmoney+2500 where cartid='10010001'
go
insert into rtansinfo(cardid,transtype,transmoney) values('10010002','支取',30000)
insert into rtansinfo(cardid,transtype,transmoney) values('10010002','存入',5000)
go
/*--查看结果--*/
print '帐户信息表中的数据:'
select * from bank
print '交易作息表的数据:'
select * from transinfo

示例5:
交易日期一般由系统自动产生,默认为当前日期,为了安全起见,一般禁止修改,以防舞弊.
use studb
go
if exists (select name from sysobjects where name='trig_update_transinfo')
	drop trigger trig_update_transinfo
go
create trigger trig_update_transinfo
	on transinfo
		for update
			as
				if update(transdate)  --检查是否修改了交易日期列transdate
					begin
						print '交易失败....'
						raiserror('安全警告:交易日期不能修改,由系统自动产生',16,1)
						rollback transaction
					end
go
/*--测试触发器,修改交易日期--*/
set nocount on
update transinfo set transdate='2000-1-1'
go
TOP
类型	数据类型	描述
整数数据类型	int	存储-2³¹(-2147483648)到2³¹(2147483647)之间的整数,占4个字节
	smallint	存储-2¹⁵(-32768)到2¹⁵(32767)之间的整数,占2个字节
	tinyint	存储0到255之间的整数,占1个字节
浮点数据类型	numeric	与decimal型相同
	real	表示-3.40E+38到3.40E+38之间的浮点数
	float	表示-1.79E+308到1.79E+308之间的任意浮点数
	decimal	存储从-10³⁸-1到10³⁸-1的固定精度和范围的数值型数据,使用时必须指定范围和精度.左边是总位数,右边是小数点位数
字符数据类型	char	定长非统一编码型的数据,列长宽最大为8000个字符
	varchar	变长非统一编码型的数据,与char的区别是,存储长度不是列长,而是数据的长度
	text	存储大量的非统一编码型字符数据,最多可以有2³¹-1或20亿个字符
Unicode字符类型	nchar	存储定长统一编码字符型数据,统一编码用双字节来存储每个字符,能存储4000种字符
	nvarchar	存储变长统一编码字符型数据,4000种
	ntext	存储大量统一编码字符,可以有2³⁰-1或10亿个字符
是/否数据类型	bit	是/否数据类型,其值只能是0,1或空值
二进制数据类型	binary	存储8000字节长的定长二进制数据
	varbinary	存储8000字节长的变长二进制数据
	image	存储变长的二进制数据,最大可达2³¹-1或20亿字节
货币数据类型	money	用来表示钱和货币值,存储从-9220亿到9220亿之间的数据,可以精确到货币单位的万分之一
货币数据类型	smallmoney	存储-214748.3648到214748.3647之间的钱或货币,精确到货币单位的万分之一
日期时间数据类型	datetime	表示日期和时间,存储从1753年1月1日到9999年12月31日,精确到三百分之一秒或3.33毫秒
日期时间数据类型	smalldatetime	存储从1900年1月1日到2079年6月6日,精确到1分钟
特殊数据类型	timestamp	是一种特殊的数据类型,用来创建一个数据库范围内的唯一数码.一个表中只能有一个timestamp列.第次插入或修改一行时,timestamp列的值都会改变,尽管它的名字中有time,但timestamp列不是人们可识别的日期,在一个数据库里,timestamp值是唯一的
特殊数据类型	uniqueidentifier	用来存储一个全局唯一标识符,即GUID.这个数几乎没有机会在另一个系统中被重建,可以使用NEWID函数或转换一个字符串唯一标识符来初始化具有唯一标识符的列
	stuName	stuNO	stuSex	stuAge	stuSeat	stuAddress
1	张秋丽	s25301	男	18	1	北京海淀
2	李文才	s25302	男	31	3	地址不详
3	李斯文	s25303	女	22	2	河南洛阳
4	欧阳俊雄	s25304	男	28	4	新疆威武哈
变量	含义
@@ERROR	最后一个T-SQL错误的错误号
@@IDENTITY	最后一次插入的标识值
@@LANGUAGE	当前使用的语言的名称
@@MAX_CONNECTIONS	可以创建的同时连接的最大数目
@@ROWCOUNT	受上一个SQL语句影响的行数
@@SERVERNAME	本地服务器的名称
@@SERVICENAME	该计算机上的SQL服务的名称
@@TIMETICKS	当前计算机上每刻度的微秒数
@@TRANSCOUNT	当前连接打开的事务数
@@VERSION	SQL Server的版本信息
	examno	stuno	writtenexam	labexam
1	s271811	s25303	93	59
2	s271813	s25302	63	91
3	s271816	s25301	90	83
4	s271817	s25318	63	53
	姓名	学号	笔试成绩	机试成绩	是否通过
1	张秋丽	s25301	90	89	是
2	李文才	s25302	63	97	是
3	李斯文	s25303	93	65	是
4	欧阳俊雄	s25304	缺考	缺考	否
5	梅超风	s25318	63	59	否
系统存储过程	说明
sp_databases	列出服务器上的所有数据库
sp_helpdb	报告有关指定数据库或所有数据库的信息
sp_renamedb	更改数据库的名称
sp_tables	返回当前环境下可查询的对象的列表
sp_columns	返回某个表列的信息
sp_help	查看某个表的所有信息
sp_helpconstraint	查看某个表的约束
sp_helpindex	查看某个表的索引
sp_stored_procedures	列出当前环境中的所有存储过程
sp_password	添加或修改登录帐户的密码
sp_helptext	显示默认值,未加密的存储过程,用户定义的存储过程,触发器或视图的实际文本
修改操作	inserted表	deleted表
增加（INSERT）记录时	存放新增的记录	......
删除（DELETE）时	......	存放被删除的记录
修改（UPDATE）时	存放用来更新的新记录	存放更新前的记录