SQL Server
基本数据类型：
1.1 简介
1.1.1 SQL分类
<!--[if !supportLists]-->l <!--[endif]-->DDL（Data Define Language）：数据定义语言：
<!--[if !supportLists]-->n <!--[endif]-->create [table,index,view,procedure]
<!--[if !supportLists]-->n <!--[endif]-->drop [table,index,view,procedure]
<!--[if !supportLists]-->n <!--[endif]-->alter [table,index,view,procedure]
<!--[if !supportLists]-->l <!--[endif]-->DML(Data Management Language)数据操作语言
<!--[if !supportLists]-->n <!--[endif]-->insert
<!--[if !supportLists]-->n <!--[endif]-->update
<!--[if !supportLists]-->n <!--[endif]-->delete
<!--[if !supportLists]-->n <!--[endif]-->select
<!--[if !supportLists]-->l <!--[endif]-->DCL(Data Control Language)数据控制语言
<!--[if !supportLists]-->n <!--[endif]-->Grant insert,select,update
On stuInfo
To zhangsan
<!--[if !supportLists]-->n <!--[endif]-->revoke all
on stuInfo
to zhangsan
1.1.2 SQL Server介绍，安装
1.2 基本知识
1.2.1 默认数据库
系统库：master(sysdatabases) model msdb tempdb
用户库：northwind
两张特殊表（视图）：
◆Sysdatabases:系统视图，存储当前数据库的详细信息，包括创建时间，创建人，主数据文件位置等信息，整个数据库只有一个，在master下。
◆Sysobjects，每个数据库一张，存储当前数据库对象信息，对象包括表，视图，约束。
特殊的函数：exists参数是一个select类别的sql语句，如果有结果返回true,否则返回false.
1.2.2 建库
create database stuDB
exists函数的使用
文件分类：
◆数据文件：
主数据文件：有且只有一个
次数据文件：0个到多个
◆日志文件：0个到多个
| use master--设置当前数据库为master，以便访问sysdatabases表 go if exists (select * from sysdatabases where name='stuDB') Drop database stuDB go create database stuDB on primary--默认就属于primary主文件组，可省略 ( name='stuDB_data',--主数据文件的逻辑名 filename='D:\我的文档\SQL Server Management Studio\Projects\stuDB_data.mdf',--主数据文件的物理名 size=5mb,--主数据文件的初始大小 maxsize=100mb,--主数据文件增长的最大值 filegrowth=15%--主数据文件的增长率 ) log on ( name='stuDB_log', filename='D:\我的文档\SQL Server Management Studio\Projects\stuDB_log.ldf', size=2mb, filegrowth=1mb ) |
1.2.3 建表
要用到一张视图sysobjects,用于查看数据库中的所有表（包括其他的存储过程，主键，外键等。）
| create table tablename ( 列名 数据类型 约束（一般为非空约束） ) |
| use stuDB go create table class ( clsId int identity not null, clsName varchar(20) not null ) go create table student ( stuId int not null, clsId int not null, stuName varchar(20) not null, stuAge int not null, stuAddress varchar(20) not null context varchar(20) not null ) go |
identity(种子,自增量)：表示从种子开始，每次自增自增量个。默认为identity(1,1)
约束类别：
◆主键约束(primary key constraint):要求主键列数据唯一，并且不允许为空。
◆唯一约束(unique constraint)：要求该列唯一，允许为空，但只能出现一个空值。
◆检查约束(check constraint)：某列取值范围、格式限制等，如有关年龄的约束。
◆默认约束(default constraint)：某列的默认值，如我们的男生学员较多，性别默认为男。
◆外键约束(foregin key constraint)：用于两表间建立关系，需要指定引用主表的那列，哪个表是外键表，就修改哪个表。
| alter table tablename add constraint 约束名 约束类型 具体的约束说明 |
| alter table class add constraint PK_clsId primary key (clsId) |
| alter table student add constraint PK_sutId primary key(stuId) alter table student add constraint FK_clsId foreign key(clsId) references class(clsId) alter table student add constraint CK_stuAge check(stuAge between 18 and 35) alter table student add constraint DF_stuAddress default('地址不详') for stuAddress alter table student add constraint UQ_stuName unique(stuName) go |
建表同时加约束：不建议使用。
| create table exam ( examId int identity primary key, stuId int foreign key references student(stuId), exam int not null check(exam between 0 and 100) ) go |
使用select * from sysobjects查看时，会出现下面这样的情况：
不清晰，不容易理解
较好的情况是：
这样清晰明了。
1.2.4 列的操作
◆增加列
| alter table student add context varchar(20) |
◆修改列
| alter table student alter column context varchar(20) |
◆删除列
| alter table student drop column context |
1.3 新增数据
1.3.1 插入单行数据
| insert into class(clsName) values('一班') insert into class(clsName) values('二班') insert into class(clsName) values('三班') go |
| insert into student(clsId,stuName,stuAge,stuAddress,context) values(1,'zhangsan1',18,default,'good') insert into student(clsId,stuName,stuAge,stuAddress,context) values(2,'zhangsan2',19,default,'good') insert into student(clsId,stuName,stuAge,stuAddress,context) values(3,'zhangsan3',20,default,'good') insert into student(clsId,stuName,stuAge,stuAddress,context) values(1,'zhangsan4',21,default,'good') insert into student(clsId,stuName,stuAge,stuAddress,context) values(1,'zhangsan5',22,default,'good') insert into student(clsId,stuName,stuAge,stuAddress,context) values(1,'zhangsan6',23,default,'good') insert into student(clsId,stuName,stuAge,stuAddress,context) values(1,'zhangsan7',24,default,'good') insert into student(clsId,stuName,stuAge,stuAddress,context) values(2,'zhangsan8',25,default,'good') insert into student(clsId,stuName,stuAge,stuAddress,context) values(2,'zhangsan9',26,default,'good') insert into student(clsId,stuName,stuAge,stuAddress,context) values(2,'zhangsan10',27,default,null) insert into student(clsId,stuName,stuAge,stuAddress,context) values(2,'zhangsan11',28,default,null) insert into student(clsId,stuName,stuAge,stuAddress,context) values(3,'zhangsan12',35,default,null) insert into student(clsId,stuName,stuAge,stuAddress,context) values(3,'zhangsan13',30,default,null) go |
1.3.2 插入多行数据
◆方式一：创建新表，把旧表中的数据放入到新表中，一般练习时使用，只复制表结构和数据（标识列规范也复制），并没有复制约束。
| select * into newtable from class select * from newtable go |
◆方式二：以查询的某个结果作为源放入表中，保证列个数，数据类型，具体长度统一。
| insert into newtable(clsId,clsName) select stuId,context from student go |
◆方式三：
| insert into class(clsName) select 'aa' union select 'aa1' union select 'aa2' union select 'aa' go |
1.4 查询
1.4.1 聚合函数
◆Count: 求总个数，不包括null列。
| select count(context),count(*) from student |
◆Sum : 求总和
| select sum(stuAge) from student |
◆Avg: 求平均值
| select avg(stuAge) from student |
◆Max: 求最大值
| select max(stuAge) from student |
◆Min: 求最小值
| select min(stuAge) from student |
1.4.2 排序
◆Order by [asc/desc] 默认asc
| select clsId,avg(stuAge) from student group by clsId order by avg(stuAge) desc |
1.4.3 分组
◆Group by
| select clsId,avg(stuAge) from student group by clsId |
1.4.4 完整查询结构
| select clsId,avg(stuAge) from student where stuAge >=20 group by clsId having avg(stuAge) >= 22 order by avg(stuAge) desc |
1.4.5 where 与 having
Where 与 having 都是条件筛选，请问他们的区别：
<!--[if !supportLists]-->l <!--[endif]-->Where是对表里原始数据进行筛选，一般最先执行
<!--[if !supportLists]-->l <!--[endif]-->Having是对表中数据分组使用聚合函数计算后（经过加工）的数据进行筛选，having在where之后，有having就一定有group by,通常两者配合使用。
1.4.6 模糊查询
◆like
| select * from student where stuName like '%1_' |
◆% : 表示匹配0-n个字符
◆_ : 表示匹配1个字符
1.4.7 几个关键字
◆top
| select top 5 * from student |
| select top 50 percent * from student |
◆District: 去掉重复值
| select distinct context from student |
◆In
| select * from student where stuName='zhangsan1' or stuName='zhangsan2' or stuName='zhangsan3' 等价于 select * from student where stuName in ('zhangsan1','zhangsan2','zhangsan3') |
◆between
1.5 子查询
1.5.1 定义
一个查询的结果作为另一查询的结果
1.5.2 要点
如果用=、!=、<、<=、>、>=等时，要保证子查询结果只返回一行一列
1.5.3 in子查询
当返回一列多行时，可以使用in子查询。
1.5.4 子查询应用，分页查询
分页思想：
第一步：子查询，找出已经显示的数据（之前的）
第二步：父查询，除去已经显示的数据，在剩下的里面使用top显示某几行。
重点：必须确保按照某个规则排序，既每次查询时每行数据的索引不变，或者说子查询与父查询采用相同的排序规则。一般简写，是因为默认是按主键升序排序。
基本语法：
| /* 一页显示几条 pageSize 当前是第几页 nowPage */ select top pageSize * from 表名 where 主键名 not in (select top (nowPage-1)*pageSize 主键名from 表名 order by 排序规则) order by 排序规则 |
比如：一页显示3条，在第二页
| select top 3 * from student where stuId not in (select top 6 stuId from student order by stuId) order by stuId |
1.6 联接查询
当在一个结果中要显示多个表的列时，使用表联接
| select stu.stuName,cls.clsName from student as stu inner join class as cls on (cls.clsId = stu.clsId) |
1.6.1 内联接（99.9%）
改变出现顺序没有影响
| select cls.clsName,stu.stuName,exam.exam from exam inner join student stu on (stu.stuId = exam.stuId) inner join class cls on (cls.clsId = stu.clsId) |
<!--[if !supportLists]-->1.6.2 <!--[endif]-->外联接（0.1%）
◆a.左外联接
改变出现的顺序有影响，因为前面出现的是左表
| select * from student stu--左表 left join exam--右表 on (stu.stuId = exam.stuId) |
<!--[if !supportLists]-->1 <!--[endif]-->首先列出内连接数据
<!--[if !supportLists]-->2 <!--[endif]-->看左表是否没有没匹配的，有的话原样列出，否则用null补齐。
<!--[if !supportLists]-->3 <!--[endif]-->总结：左联接显示左表的所有数据
列出没有参加考试的学生
| select stu.* from student stu left join exam on (stu.stuId = exam.stuId) where exam.examId is null |
◆b.右外联接
| select * from exam--左表 right join student stu--右表 on (stu.stuId = exam.stuId) |
规则和左外联接一样。
◆完全内连接 ＝ 左外联接 + 右外联接
| select * from exam--左表 right join student stu--右表 on (stu.stuId = exam.stuId) left join class cls--右表 on (stu.clsId = cls.clsId) |
1.6.3交叉联接（基本不用）
一般不使用，返回数据的行数＝左表行数*右表行数
| select * from student,class |
1.7 常用函数
1.7.1 日期和时间函数
1.7.2 字符串函数
1.7.3 其他函数
◆cast(@age as varchar(2))
◆convert(varchar(2),@age)
◆getdate()
◆newid()
1.8 T-SQL编程
T-SQL SqlServer
PL/SQL Oracle
1.8.1 变量
◆局部变量：用户自定义的变量，变量名以@开头
赋值：
<!--[if !supportLists]-->1. <!--[endif]-->set @变量名＝数值
| declare @age int set @age = 12 select @age as '年龄' print '年龄=' + cast(@age as varchar(2)) |
| declare @age int set @age = 12 select @age as '年龄' print '年龄=' + convert(varchar(2),@age) |
<!--[if !supportLists]-->2. <!--[endif]-->以查询结果为值
| declare @age int select @age=stuAge from student where stuId=13 print '年龄=' + convert(varchar(2),@age) |
| declare @stuId int select @stuId = stuId from student where stuName='zhangsan7' select * from student where stuId in (@stuId-1,@stuId+1) |
Select在网格窗口显示结果，print在消息窗口显示结果。
◆全局变量：系统提供，以@@开头，只读的，也就是由系统赋值，我们取值。
| select @@version select @@identity select @@error select @@language select @@rowcount select @@servername select @@trancount select @@max_connections |
<!--[if !supportLists]-->1.8.2 <!--[endif]-->流程控制语句
把{}换成begin end
多重判断：嵌套if，阶梯式if(可以是区间范围，也可以是定值),switch（只可以是定值）
| declare @avgExam int select @avgExam = avg(exam) from exam print '平均分为：' + cast(@avgExam as varchar(2)) if (@avgExam >= 70) begin print '成绩优秀' select top 3 * from exam order by exam asc end else begin print '成绩不好' select top 3 * from exam order by exam desc end |
| select stuId,exam,'等级'= case when exam <60 then 'E' when exam between 60 and 69 then 'D' when exam between 70 and 79 then 'C' when exam between 80 and 89 then 'B' else 'A' end from exam |
1.8.3 循环
| declare @minExam int declare @index int set @index = 1 select @minExam = min(exam) from exam while(@minExam >= 60) begin print '开始第' + cast(@index as varchar(2)) + '次减分' update exam set exam = exam - 3 select @minExam = min(exam) from exam print '第' + cast(@index as varchar(2)) + '次减分后为：' + cast(@minExam as varchar(2)) set @index = @index + 1 end |
| declare @avgExam int declare @index int set @index = 1 select @avgExam = exam from exam while(@avgExam <= 85) begin print '开始第' + cast(@index as varchar(2)) + '次加分' update exam set exam = case when exam >= 90 then exam when exam between 80 and 89 then exam + 1 when exam between 70 and 79 then exam + 2 when exam between 60 and 69 then exam + 3 else exam + 4 end select @avgExam = avg(exam) from exam print '第' + cast(@index as varchar(2)) + '次加分后为：' + cast(@avgExam as varchar(2)) set @index = @index + 1 end |
1.9 视图
1.9.1 定义
视图是一张虚拟表，它表示一张表的部分数据或多张数据，其结构和数据是建立在对表的查询基础上
视图并不存放数据，而是存放在视图所引用的原始表（基表）中，同一张原始表，根据不同用户的需求主，可以创建不同的视图。
1.9.2 优点
◆筛选表中的行
◆防止未经许可的用户访问敏感数据
| create view stuview1 as select stuId as sid,stuName as sname from student go select * from stuview1 |
◆降低数据库的复杂程序
| create view examview as select cls.clsName,stu.stuName,exam.exam from exam inner join student stu on (stu.stuId = exam.stuId) inner join class cls on (cls.clsId = stu.clsId) select * from examview |
◆将多个物理数据库抽象为一个逻辑数据库
| create view examview2 as select a.stuId,b.stuName,a.exam from fuxidb.dbo.exam as a inner join testdb.dbo.student as b on (a.stuId = b.stuId) go |
1.9.3 缺点
◆视图是为查询服务的，不能通过视图新增，修改，删除数据，同一张表可以根据需求创建不同的视图。
1.10 事务
1.10.1 简介
同时执行多个insert,update,delete等sql语句时使用。事务(transation)是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行，要么都不执行，事务是一个不可分割的工作逻辑单元。
1.10.2 事务特点
事务必须具备以下四个属性，简称ACID属性：
◆原子性（Atomicity）:事务是一个完整的操作。事务的各步操作是不可分的（原子的），要么都执行，要么都不执行。
◆一致性（Consistency）:当事务完成时，数据必须处于一致状态。
◆隔离性 (Isolation)：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
◆永久性（Durability）：事务完成后，它对数据库的修改被永久保存，事务日志能够保持事务的永久性。
1.10.3 T-SQL中的事务
◆开始事务：begin transation
◆提交事务：commit transation
◆回滚事务：rollback transation
一旦事务提交或回滚，则事务结束。
| declare @errorcount int set @errorcount = 0 begin transaction update back set currentMoney = currentMondy + 100 where cid = 1 select * from bank set @errorcount = @errorcount + @@error update bank set currentMoney = currentMondy - 100 where cid = 2 set @errorcount = @errorcount + @@error if(@errorcount <> 0) begin print '没有异常，提交' commit transaction end else begin print '发生异常，回滚' rollback transaction end |
判断某条语句执行是否出错：
◆使用全局变量@@error;
◆@@error只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计。
如：set @errorSum = @errorSum + @error
1.10.4 事务类别
◆显示事务：用begin transation 明确指定事务的开始，这是最常用的事务类型。
◆隐性事务：通过设置set implicit_transation语句，将隐性事务模式设置为打开，下一个语句自动启动一个新事务。当该事务完成时，再下一个T-SQL语句又将启动一个新事务。
◆自动提交事务：这是SQL Server的默认模式，它将每条单独的T-SQL语句视为一个事务，如果成功执行，则自动提交；如果错误，则自动回滚。
1.10.5 JAVA程序中如何做事务
在Connection对象上做事务，autoCommit的属性默认为true，可以手工修改为false，如果无异常，则调用con.commit(),否则调用con.rollback();
| package com.anllin.network; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class TransactionTEst { public static void main(String[] args) throws Exception { String sql1= "update back set currentMoney = currentMondy + 100 where cid = 1"; String sql2= "update bank set currentMoney = currentMondy - 100 where cid = 2"; Connection con = null; Statement sta = null; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=testdb","sa",""); System.out.println("autoCommit = " + con.getAutoCommit() ); con.setAutoCommit(false); sta = con.createStatement(); sta.executeUpdate(sql1); sta.executeQuery(sql2); con.commit(); } catch (Exception e) { con.rollback(); e.printStackTrace(); } finally { sta.close(); con.close(); } } } |
小问题：暂时只能在一条con上做事务，一般借助框架或容器，使用JTI(Java Transaction Api)
1.11 索引
1.11.1 简介
<!--[if !supportLists]-->l <!--[endif]-->汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等。
<!--[if !supportLists]-->l <!--[endif]-->我们可以根据拼音或偏旁部首，快速查找某个字词。
<!--[if !supportLists]-->l <!--[endif]-->SQLServer中的数据也是按页（4kb）存放的。
<!--[if !supportLists]-->l <!--[endif]-->索引：是SQLServer编排数据的内部方法，它为SQLServer提供一种方法来编排查询的数据。
<!--[if !supportLists]-->l <!--[endif]-->索引页：数据库存放的数据页，索引页类似于汉语字典中按拼音或笔画排序的目录页。
<!--[if !supportLists]-->l <!--[endif]-->索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库
性能。
1.11.2 类型
<!--[if !supportLists]-->l <!--[endif]-->唯一索引：唯一索引不允许两行具有相同的索引值。
<!--[if !supportLists]-->l <!--[endif]-->主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一的特殊类型，主键索引要求主键中的每个值是唯一的，并且不能为空。
<!--[if !supportLists]-->l <!--[endif]-->聚焦索引（clustered）：表中各行的物理顺序与健值的逻辑（索引）顺序相同，每个表只能有一个。
<!--[if !supportLists]-->l <!--[endif]-->非聚焦索引（non-clustered）：非聚焦索引指定表的逻辑顺序，数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针，可以有多个，小于249个。
1.11.3 语法
| create [unique/clustered/nonclustered] index indexName on tableName(columnName) [with fillfactor=x] |
<!--[if !supportLists]-->l <!--[endif]-->Unique：表示唯一索引，可选
<!--[if !supportLists]-->l <!--[endif]-->clustered/nonclustered：表示聚焦索引还是非聚焦索引，可选。
<!--[if !supportLists]-->l <!--[endif]-->Fillfactor：表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比。
| if exists (select name from sysindexes where name = 'IX_name') drop index bank.IX_name create nonclustered index IX_name on bank(customerName) where fillfactor=30 go |
| select * from bank with(index=IX_name) where customerName = 'zhangsan' |
1.11.4 优缺点
<!--[if !supportLists]-->l <!--[endif]-->优点：
<!--[if !supportLists]-->n <!--[endif]-->加快访问速度
<!--[if !supportLists]-->n <!--[endif]-->加强行的唯一性
<!--[if !supportLists]-->l <!--[endif]-->缺点：
<!--[if !supportLists]-->n <!--[endif]-->带索引的表在数据库中需要更多的存储空间。
<!--[if !supportLists]-->n <!--[endif]-->操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新。
1.11.5 创建索引的原则：
<!--[if !supportLists]-->l <!--[endif]-->请按照下列标准选择建立索引的列：
<!--[if !supportLists]-->n <!--[endif]-->该列用于频繁探索
<!--[if !supportLists]-->n <!--[endif]-->该列用于对数据进行排序
<!--[if !supportLists]-->l <!--[endif]-->请不要使用下面的列创建索引
<!--[if !supportLists]-->n <!--[endif]-->列中仅包含几个不同的值。
<!--[if !supportLists]-->n <!--[endif]-->表中包含几行。为小型表创建索引可能不太划算，因为SQLServer在索引中搜索数据所花的时间比在表中逐行搜索所花的时间还要长。
1.12 存储过程
1.12.1 简介
◆存储过程在业界以前常常作为衡量程序员水平的标准，大虾们喜欢吹嘘自己写过几千行的存储过，但现在作为java程序员，些功能被弱化，因为java程序讲究的是与具体的数据库相分离，但作为.net程序员，依然要掌握好此知识。
◆存储过程（Procedure）类似于C语言中的函数，用于执行管理任务或应用复杂的业务规则，存储过程可以带参数，也可以返回结果。
◆存储过程可以包含数据操纵语句、变量、逻辑、控制语句等。
1.12.2 语法
| create proc[edure] procName @param[1] dataType = value, ... @param[n] dataType = defualt value [output] as T-SQL编程，定义变量，流程控制等 |
◆和C语言的函数一样，参数可选
◆参数分为输入参数(按值传递)、输出参数(按引用传递)。
| create proc test @a int=100, @b int output as print '传递来的a = ' + cast(@a as varchar(4)) print '传递来的b = ' + cast(@b as varchar(4)) set @a = @a +10 set @b = @b + 10 print '修改后的a = ' + cast(@a as varchar(4)) print '修改后的b = ' + cast(@b as varchar(4)) go declare @c int declare @d int set @c = 100 set @d = 100 execute test @a=@c,@b=@d output print @c print @d go |
◆输入参数允许有默认值
◆创建存储过程
| create proc sp_transfer @inId int, @outId int, @money int, @inMoney int output, @outMoney int output as update back set currentMoney = currentMondy + @money where cid = @inId update back set currentMoney = currentMondy + @money where cid = @outId select @inMoney = currentMoney from bank where cid = @inId select @outMoney = currentMoney from bank where cid = @outId go |
◆执行存储过程
| declare @a int declare @b int exec sp_transfer @inId=2,@outId=1,@money=100,@inMoney=@a output,@outMoney=@b output print @a print @b |
◆在java中使用存储过程
| package com.anllin.proc; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Types; public class TestProc { public static void main(String[] args) throws Exception { int outId = 1; int inId = 2; int money = 100; Connection con = null; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); con = DriverManager.getConnection( "jdbc:sqlserver://localhost:1433;databaseName=testdb", "sa", ""); CallableStatement csta = con.prepareCall("{call sp_transfer()}"); csta.setInt(1,inId); csta.setInt(2,outId); csta.setInt(3,money); csta.registerOutParameter(4,Types.INTEGER); csta.registerOutParameter(5,Types.INTEGER); csta.execute(); System.out.println(csta.getInt(4)); System.out.println(csta.getInt(5)); } catch (Exception e) { e.printStackTrace(); } finally { con.close(); } } } |
1.12.3 优点
◆执行速度更快
◆允许模块化程序设计
◆提高系统安全性
◆减少网络流量
1.13 触发器
1.13.1 简介
◆触发器是在对表进行插入、更新或删除操作时自动执行的存储过程
◆触发器通常用于强制业务规则
◆触发器是一种高级约束，可以定义比用check约束更为复杂的约束
a)可执行复杂的SQL语句（if/while/case）
b)可引用其它表中的列
◆触发器定义在特定的表上，与表相关。
◆自动触发执行
◆不能直接调用
◆是一个事务（可回滚）
练习用的表
| create table bank ( cid int identity primary key, customerName char(10), currentMoney money ) go alter table bank add constraint CK_currentMoney check(currentMoney >= 1) go insert into bank(customerName,currentMoney) values('zhangsan',1000) insert into bank(customerName,currentMoney) values('lisi',1) go create table transInfo ( tid int identity primary key, cid int not null, tdate datetime not null, transType varchar(20) not null, transMoney money not null ) go |
1.13.2 触发器类型
◆Delete 触发器
| create trigger transInfoDeleteTrigger on transInfo for delete as if(not exists (select * from sysobjects where name='transInfoBackup')) select * into transInfoBackup from deleted else insert into transInfoBackup select * from deleted go delete from transInfo where tid = 1 go select * from transInfoBackup go |
◆Insert触发器
| create trigger backTrigger1 on bank for insert as print '新增触发器触发了，看看插入的数据' select * from inserted go insert into bank(customerName,currentMoney) values('wangwu',1000) go |
◆Update触发器
| create trigger backTrigger2 on bank for update as select * from deleted print '新增触发器触发了，看看插入的数据' select * from inserted go update bank set currentMoney = currentMoney + 150 go |
1.13.3 两张表
◆在触发器触发时：
系统自动在内存中创建两张临时表，deleted表或inserted表。
这两张表为只读，不允许修改，触发器执行完成后，自动删除。
◆Inserted表
临时保存了插入或更新后的记录行
可以从inserted表中检查插入的数据是否满足业务需求
如果不满足，则向用户报告错误，并回滚插入操作
◆Deleted表
临时保存了删除或更新前的记录行
可以从表中检查被删除的数据是否满足业务需求
如果不满足，则向用户报告错误，并回滚插入操作
| 修改操作 |
Inserted表 |
Deleted表 |
| 增加（insert）记录 |
存放新增的记录 |
---------- |
| 删除（delete）记录 |
----------- |
存放被删除的记录 |
| 修改（update）记录 |
存放更新后的记录 |
存放更新前的记录 |
1.13.4 创建触发器的语法
| create trigger triggerName on tableName [with encryption]--表示加密触发器定义的SQL文本 for [delete,insert,update]--指定触发器类型 as T-SQL语句 go |
应用示例1：
当往交易信息表中插入一条数据时，同时更新帐户表中的金额
| create trigger transInfoTrigger on transInfo for insert as declare @type varchar(20) declare @money money declare @cardId int select @type=transType,@money=transMoney,@cardId=cid from inserted if(@type = '存') update bank set currentMoney = currentMoney + @money where cid = @cardId else update bank set currentMoney = currentMoney - @money where cid = @cardId go insert into transInfo(cid,tdate,transType,transMoney) values(1,getdate(),'存',100) go select * from bank select * from transInfo |
应用示例2
当在交易信息表中删除一条数据时，备份这条数据到transInfoBackup表中
| create trigger transInfoDeleteTrigger on transInfo for delete as if(not exists (select * from sysobjects where name='transInfoBackup')) select * into transInfoBackup from deleted else insert into transInfoBackup select * from deleted go delete from transInfo where tid = 1 go select * from transInfoBackup go |
应用示例3
当用户交易金额大于20000时，不让用户操作，并提示错误
| alter trigger bankUpdateTrigger on bank for update as declare @oldMoney money declare @newMoney money declare @amount money select @newMoney=currentMoney from inserted select @oldMoney=currentMoney from deleted set @amount = @oldMoney - @newMoney if(@amount>=20000 or @amount <=-20000) begin print '交易错误，金额最多为万' select * from bank rollback transaction end go insert into transInfo(cid,tdate,transType,transMoney) values(1,getdate(),'存',10000) go select * from bank |
1.13.5 列级update触发器
Update 触发器除了跟踪数据的变化（修改）外，还可以检查是否修改了某列的数据。
使用update（列）函数检测是否修改了某列
问题：交易日期一般由系统自动产生，默认为当前日期，为了安全起见，一般禁止修改，以防舞弊。（注意：时间分DB服务器时间和Web服务器时间）
分析：
Update（列名）函数可以检测是否修改了某列
| create trigger TG_updateTransInfo on transInfo for update as if update(tdate) begin print '交易失败...' raiserror ('安全警告：交易日期不能修改，由系统自动产生',16,1) rollback transaction end go update transInfo set tdate = getdate() where tid = 1 |
=============＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝