（分享）SQLSERVER高级应用
最近正在努力钻研SQLSERVER高级应用，最主要的有存储过程和游标等。下面是好心的网友给我的一些资料，下面我贴出来一起分享。
1. Sql Server2005基础
1.1 Sql server 2005简介
1.2 Sql Server2005创建<删除>数据库
1.2.1 用工具创建<删除>数据库
1.2.2 用sql 脚本创建<删除>数据库
|
CREATE DATABASE <数据库名> ON [PRIMARY] ① ② ( --创建数据文件参数信息 [NAME=逻辑文件名,] ③ FILENAME=物理文件名, ④ [SIZE=数据库大小,] ⑤ [MAXSIZE=最大容量,] ⑥ [FILEGROWTH=增长量] ⑦ … ) [LOG ON]⑧ ( --创建日志文件参数信息 [NAME=逻辑文件名,] FILENAME=物理文件名, [SIZE=数据库大小,] [MAXSIZE=最大容量,] [FILEGROWTH=增长量] … ) |
代码解析：
① “[]”表示可选，“<>”表示必选，“--”表示注释和说明信息
② 指定将当前文件放置于主文件组（Primary）中。
③ 定义数据库文件的逻辑名称，该名称是仅SQL Server中使用的名称，在SQL Server中必须唯一
④ 定义存储数据库数据的磁盘文件，文件的路径必须与磁盘路径对应
⑤ 数据库文件的初始大小，如果没有定义，采用默认值。
⑥ 指定数据库文件的最大容量，不设置表示容量不受限值。
⑦ 设定数据库容量的增量，数值不可以超过最大容量。
⑧ 定义存储数据库日志的磁盘文件。
例：
|
CREATE DATABASE StuDB ON PRIMARY ( NAME = 'StuDB_data', --FILENAME使用的文件路径必须与磁盘路径对应 FILENAME = 'C:\DB\StuDB_data.mdf', SIZE = 3mb, MAXSIZE = 50mb, FILEGROWTH = 10% ) LOG ON ( NAME = 'StuDB_log', FILENAME = 'C:\DB\StuDB_log.ldf' , SIZE = 3mb, MAXSIZE = 50mb, FILEGROWTH = 10% ) |
1.3 Sql Server2005表操作
1.3.1 添加<删除>表
创建表语法：
|
CREATE TABLE 数据表名 （ 字段名 字段类型 [NOT NULL|NULL] [约束描述], 字段名 字段类型 [NOT NULL|NULL] [约束描述], 字段名 字段类型 [NOT NULL|NULL] [约束描述], … … … … ） |
例：
|
CREATE TABLE Infos （ StuID VARCHAR(7) NOT NULL, --学号（必填） StuName VARCHAR(10) NOT NULL, --姓名（必填） Gender VARCHAR(2) NOT NULL, --性别（必填） Age INT NOT NULL, --年龄（必填） Seat INT NOT NULL, --座位号（必填） EnrollDate DATETIME, --入学时间 StuAddress VARCHAR(50), --住址 ClassNo VARCHAR(4) NOT NULL --班级号（必填） ） |
删除表语法：
|
DROP TABLE 数据表名 |
例：
|
USE StuDB GO IF EXISTS（SELECT * FROM sysobjects WHERE name = 'Infos'） DROP TABLE Infos GO |
1.3.2 数据库基本概念
1.3.3 添加表约束
1.3.3.1 使用工具添加表约束
1.3.3.2 使用sql语句添加表约束
添加约束语法：
|
ALTER TABLE 数据表名 ADD CONSTRAINT 约束名 约束语句 |
|
约束名称 |
关键字 |
简称 |
|
主键约束 |
PRIMARY KEY |
PK |
|
外键约束 |
FOREIGN KEY |
FK |
|
默认约束 |
DEFAULT |
DF |
|
检查约束 |
CHECK |
CK |
|
唯一约束 |
UNIQUE |
UQ |
例：
|
/**添加约束方式一：一条SQL添加一个约束**/ --添加主键约束，设置StuID列为Infos表的主键 ALTER TABLE Infos ADD CONSTRAINT PK_Infos_StuID PRIMARY KEY(StuID) --添加检查约束， Gender列中的值只能为‘男’或者‘女’ ALTER TABLE Infos ADD CONSTRAINT CK_Infos_Gender CHECK(Gender = ‘男’ or Gender = ‘女’) --添加默认约束，StuAddress如果不填，默认为‘地址不详’ ALTER TABLE Infos ADD CONSTRAINT DF_Infos_StuAddress DEFAULT(‘地址不详’) FOR StuAddress, --添加唯一约束，StuName不可以重复 ALTER TABLE Infos ADD CONSTRAINT UQ_Infos_StuName UNIQUE(StuName)
/**添加约束方式二：一条SQL语句添加多个约束**/ ALTER TABLE Scores --添加主键约束，设置Id列为Scores的主键 ADD CONSTRAINT PK_Scores_Id PRIMARY KEY(Id), --添加外键约束，Scores表的StuID列连接Infos表的StuID列 CONSTRAINT FK_Scores_StuID FOREIGN KEY (StuID) REFERENCES Infos(StuID), CONSTRAINT DF_Scores_WrittenScore DEFAULT(0) FOR WrittenScore, CONSTRAINT CK_Scores_WrittenScore CHECK(WrittenScore >= 0 AND WrittenScore <= 100), CONSTRAINT DF_Scores_LabScore DEFAULT(0) FOR LabScore, CONSTRAINT CK_Scores_LabScore CHECK(LabScore >= 0 AND LabScore <= 100) |
1.3.4 添加表数据
语法:
|
INSERT [INTO] 数据表名（列名1，列名2，列名3，... …） VALUES （值1，值2，值3，… …） |
例:
|
INSERT INTO Infos (StuID,StuName,Gender) VALUES ('s100110','李国庆','男') |
1.3.5 修改表数据
语法：
|
UPDATE <表名> SET 列名1=值1[,列名2=值2,...] [WHERE 条件表达式] |
例：
|
UPDATE Infos SET StuName='张华 ' WHERE StuID='s100110' |
1.3.6 删除表数据
语法：
|
DELETE [FROM] <表名> [WHERE 条件表达式] |
例：
|
DELETE FROM Infos WHERE StuID='s200102' |
1.3.7 查询表数据
语法：
|
SELECT <列名> ① FROM <表名> ② [WHERE <条件表达式>] ③ [ORDER BY <列名>[ASC 或 DESC]] ④ |
代码解析：
① 以逗号分隔的查询结果集中要显示的列名，如果查询所有列，用*代替所有列名。
② 查询的表名。
③ 查询条件，可选项，如果省略则查询所有的数据。
④ 排序结果集，ASC是升序，DESC是降序。
1.3.7.1 查询所有列
|
SELECT * FROM 表名 |
1.3.7.2 查询部分列
|
SELECT 列名1,列名2,… FROM 表名 |
1.3.7.3 更改结果集的列名和表名
|
方法： １. SELECT 列名1 AS ‘列名1别名’,列名2 AS ‘列名２别名’ FROM 表名 AS ‘表别名’ ２. SELECT 列名1 ‘列名1别名’,列名2 ‘列名２别名’ FROM 表名‘表别名’ ３. SELECT 列名1 列名1别名, 列名2 列名２别名 FROM 表名 表别名 ４. SELECT 列名1别名=列名1 , 列名２别名=列名2 FROM 表名 表别名 ５. SELECT ‘列名1别名’=列名1 , ‘列名２别名’=列名2 FROM 表名 表别名 ６. (组合列名)SELECT 列名１+列名２ AS ‘组合别名’FROM 表名 AS 表别名 |
1.3.7.4 TOP查询
|
1.查询前Ｎ条记录 SELECT TOP N * FROM 表名 2.按照百分比查询记录（关键字PERCENT） SELECT TOP N PERCENT FROM 表名 |
1.3.7.5 单条件查询
|
１. SELECT * FROM 表名 WHERE 条件＝’条件的值’ ２. SELECT * FROM 表名 WHERE 条件>=’条件的值’ |
1.3.7.6 多条件查询
|
多条件查询二个关键字(AND OR) １. SELECT * FROM 表名WHERE 条件1 ＝‘条件1的值’AND 条件2 = ‘条件2的值’ ２. SELECT * FROM 表名 WHERE条件1 ＝‘条件1的值’OR 条件2 = ‘条件2的值’ |
1.3.7.7 模糊查询
|
模糊查询关键字是 LIKE，它有二个通配符 % 和_ ，% 表示任意零个或多个字符, _ 表示任意一个字符。 １. SELECT * FROM 表名 WHERE 条件1 LIKE ‘李%’ ２. SELECT * FROM 表名 WHERE 条件1 LIKE ‘李_’ 注意当查询的条件中出现二个通配符时，需要使用关键字ESCAPE 进行转义 要查询 以5%开头的所有信息 １. SELECT * FROM 表名 WHERE 条件1 LIKE ‘5/%%’ESCAPE ‘/’ 要查询以W_开头的所有信息 1.SELECT * FROM 表名 WHERE 条件1 LIKE ‘Ws_’ESCAPE ‘s’ |
1.3.7.8 查询NULL
|
NULL表示不存在数据，查询NULL值时使用 IS NULL 或者 IS NOT NULL １. SELECT * FROM 表名 WHERE 条件 IS NULL ２. SELECT * FROM 表名 WHERE 条件 IS NOT NULL |
1.3.7.9 使用 BETWEEN AND 查询范围值
|
SELECT * FROM 表名 WHERE 条件 BETWEEN 值1 AND 值2 |
1.3.7.10 ORDER BY 排序
|
排序的规则只有二种，升序和降序 １. SELECT * FROM 表名 WHERE [条件…] ORDER BY 列名 ASC ２. SELECT * FROM 表名 WHERE [条件…] ORDER BY 列名 DESC ３. SELECT * FROM 表名 WHERE [条件…] ORDER BY 列名1 DESC ，列名2 ASC |
1.3.7.11 聚合函数
|
名称 |
作用 |
语法 |
|
AVG |
平均值 |
AVG(表达式) |
|
SUM |
求和 |
SUM(表达式) |
|
MIN、MAX |
最小值、最大值 |
MIN(表达式)、MAX(表达式) |
|
COUNT |
数据统计 |
COUNT(表达式) |
|
聚合函数语法： SELECT <聚合函数> FROM 表名 WHERE 条件 |
1.3.7.12 分组查询（GROUP BY）
|
分组查询的语法： １. SELECT 结果字段 FROM 表名 WHERE 条件 GROUP BY 字段 例：查询1002班男学员和女学员的平均年龄 SELECT Gender AS '性别' FROM Infos WHERE ClassNo = '1002' GROUP BY Gender 例：按照班级分组，查询男学员平均年龄，并按照平均年龄降序 SELECT ClassNo AS ‘班级’,AVG(Age) AS ‘平均年龄’ FROM Infos WHERE Gender =’男’ GROUP BY ClassNo ORDER BY平均年龄 DESC |
1.3.7.13 HAVING 子句
|
例：按照班级分组，查询男学员平均年龄，按照平均年龄降序，但是只查询平均 年龄大于22岁的班级 SELECT ClassNo AS '班级',AVG(Age) AS '平均年龄' FROM Infos WHERE Gender = '男' GROUP BY ClassNo HAVING AVG(Age) > 22 ORDER BY 平均年龄 DESC |
1.3.8 联接
1.3.8.1 内联接
语法：
|
SELECT <表A.字段1|表B.字段1> [,表A.字段2,…] FROM <表A> INNER JOIN <表B> ON <表A.关联字段 = 表B.关联字段> [WHERE <条件表达式>] [ORDER BY <列名1>[ASC|DESC] , [<列名2>[ASC|DESC] , …]] |
|
１. 查询所有参加考试学生的信息和成绩 SELECT * FROM Infos , Scores WHERE Infos. StuID= Scores.StuID SELECT * FROM Infos INNER JOIN Scores ON Infos. StuID= Scores.StuID |
1.3.8.2 外联接
左外联接（left join）
|
例：查询所有在Infos表中注册的学员的成绩信息，要求查询结果集中出现的字段有StuID、StuName、Gender、WrittenScore及LabScore 答案：SELECT Infos.StuID,StuName,Gender,WrittenScore,LabScore FROM Infos LEFT JOIN Scores ON Infos.StuID = Scores.StuID |
右外联接(right join)
完全外联接(full join)
2. 数据库设计
2
2.1 数据库设计
|
需求分析 |
在本阶段主要是得到准确并且完整的用户需求信息，包括数据及其处理。在该阶段中首先要进行调查，调查的方法有：在客户的公司跟班实习、组织召开调查会、邀请专人介绍、调查问卷、查阅客户业务相关的数据。根据调查的信息进行分析，整理出客户与研发人员都能明白的文档（系统需求说明书）。该阶段是整个设计过程的基础，是最困难、最耗费时间的一步。 |
|
概念设计 |
通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型。通过系统需求说明书中的描述，标识出系统需要的各种对象（实体）、各种对象的具体属性和各种对象之间的联系。一般本阶段要形成整个系统的E-R图（实体关系图）。是整个数据库设计的关键。 |
|
逻辑设计 |
将概念结构转换为某个DBMS所支持的数据模型，包括各种数据库对象，表中数据的数据类型等。这里的表一般是由E-R转换过来的。可以借助各种辅助工具进行。比如ORACLE Designer 2000、SYBASE PowerDesigner等 |
|
物理设计 |
为逻辑数据模型选取一个最适合应用环境的物理结构（包括存储结构和存取方法、索引结构等）。 |
|
运行验证 |
在上述设计的基础上，收集数据并具体建立一个数据库，运行一些典型的应用任务来验证数据库设计的正确性和合理性。一个大型数据库的设计过程往往需要经过多次循环反复。当设计中的某一步聚发生问题时，可能就需要返回到前面去进行修改。因此，在做上述数据库设计时就应考虑到今后修改设计的可扩展性和方便性。 |
2.2 Ｅ-Ｒ图
E-R图中常用的符号如下表：
|
符号 |
说明 |
|
矩形表示数据库中的实体。 |
|
|
椭圆表示实体的属性。 |
|
|
菱形表示实体之间的关系。 |
|
|
直线用来连接实体和属性。 |
|
|
实体之间一对一连接关系（不同教材表达不同，本书约定箭头表示） |
|
|
实体之间一对多连接关系（不同教材表达不同，本书约定箭头表示） 箭头指向的对象对应的是主表 |
|
|
实体之间的多对多连接关系（不同教材表达不同，本书约定直线表示） |
2.3 数据库设计规范
|
第一范式 |
第一范式要求单个表中每个列必须是原子列（每一列都是不可再分的最小数据单元）、列不存在重复属性、每个实体的属性也不存在多个数据项。 |
|
第二范式 |
第二范式是在满足第一范式的基础上，所有的非主键列都依赖于主键列。 |
|
第三范式 |
第三范式是在满足第二范式的基础上，每一个非主键字段不传递依赖于主键。 |
3. T-SQL的基本语法
3
3.1 变量
T-SQL中的变量分为局部变量和全局变量两种。局部变量要先声明、赋值后再使用。全局变量由系统定义和维护，用户只能读取，不能赋值，也不允许用户定义全局变量。
|
局部变量 １．(申明)局部变量使用关键字DECLARE声明，变量名必须以at符号（@）作为前缀。 DECLARE @变量名 数据类型 ２．(赋值)T-SQL变量赋值有二种方法 SET @变量名 = 值 SELECT @变量名 = 值 3. （应用） |
|
全局变量 全局变量由系统定义和维护，用户只能读取，不能赋值，也不允许用户定义全局变量。全局变量是有两个at符号（@）作为前缀的
|
3.2 输出语句
|
T-Sql中输出语句有二种：SELECT 和PRINT SELECT是以“结果”窗口以网格形式显示输出结果 PRINT 是以“消息”窗口以文本形式显示输出结果
有些时候需要将SELECT输出和PRINT输出显示在一起。通过更改SQL Server Management Studio设置来完成。 打开【工具】->【选项】，在弹出对话框中点击“查询结果”节点，在右侧的“显示结果的默认方式”下拉框中选择“以文本格式显示结果”。设置后需要重新启动查询窗口。 |
3.3 数据类型转换
|
当输出的结果是一个表达式，并且操作数的数据类型不同时，就需要数据类型的转换。 转换的语法是CONVERT(需要转换的数据类型,值) 例：PRINT '一共有'+CONVERT(VARCHAR(50),@@ERROR)+'个错误' |
3.4 条件判断语句
3.4.1 IF ELSE语句
|
语法： IF (条件) BEGIN 语句块1 END ELSE BEGIN 语句块2 END |
例：查询Scores成绩表中不及格人数，如果不及格人数超过5人，则安排辅导，并按照总成绩倒序显示不及格学员的成绩
|
DECLARE @count INT SET @count = 0 SELECT @count = count(*) FROM scores WHERE WrittenScore <60 OR LabScore <60 IF(@count>5) BEGIN PRINT '不及格人数'+ CONVERT(VARCHAR(2),@count) + '人，请安排辅导。' SELECT StuName,WrittenScore,LabScore FROM infos INNER JOIN scores ON infos.StuID = scores.StuID WHERE WrittenScore <60 OR LabScore <60 ORDER BY WrittenScore + LabScore ASC END ELSE BEGIN PRINT '不及格人数'+ CONVERT(VARCHAR(2),@count) + '人。' END |
3.4.2 IF SLSE IF语句
|
语法： IF (条件) BEGIN 语句块1 END ELSE IF(条件) BEGIN 语句块2 END |
3.4.3 CASE语句
|
语法： CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE 其他结果] END |
例：采用美国的ABCDE五级打分制来显示学员笔试成绩。要求，
A级 ：90分以上（含）
B级 ：80-89分
C级 ：70-79分
D级 ：60-69分
E级 ：60分以下
|
SELECT '学号'= Scores.StuID, '姓名'=StuName,'笔试成绩'= CASE WHEN WrittenScore < 60 THEN 'E' WHEN WrittenScore BETWEEN 60 AND 69 THEN 'D' WHEN WrittenScore BETWEEN 70 AND 79 THEN 'C' WHEN WrittenScore BETWEEN 80 AND 89 THEN 'B' ELSE 'A' END FROM infos INNER JOIN Scores ON infos.StuID = Scores.StuID |
3.5 循环语句
WHILE语句用于设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。
|
语法： WHILE (条件) BEGIN 语句块 [BREAK] [CONTINUE] END |
例：本次考试笔试成绩较差，现在要为笔试不及格学员加分。加分规则是先将笔试不及格学员加2分，然后看是否都通过，如果还有未通过学员再加2分，再看是否都通过，如此循环加分直到笔试都及格为止，最后显示总共加了多少分。
|
DECLARE @count INT DECLARE @number INT SET NOCOUNT ON --不显示受影响的行数 SELECT @count = COUNT(*) FROM scores WHERE WrittenScore<60 SET @number = 0 WHILE( @count> 0 ) BEGIN UPDATE scores SET WrittenScore = WrittenScore + 2 WHERE WrittenScore < 60 SET @number = @number + 2 SELECT @count = COUNT(*) FROM scores WHERE WrittenScore<60 END PRINT '累计加分' + CONVERT(VARCHAR(2),@number) |
4. 高级查询
4.1 子查询
简单子查询
|
1.查询与林冲同龄的学员 SELECT StuName FROM infos WHERE Age = (SELECT Age FROM infos WHERE StuName = '林冲')
|
通常将子查询的查询结果作为父查询条件的值，因此，当父查询条件的关系运算符是=、<>、>、>=、<、<= 时子查询必须只返回单行单列数据。
如果子查询返回多行数据，要在子查询前加ANY、SOME 或ALL关键字
4.1.1 ALL子查询
|
1.查询1002班中比1001班中所有学员年龄都大的学员 SELECT StuName,classno FROM infos WHERE Age > ALL(SELECT age FROM infos WHERE ClassNO='1001') AND ClassNO = '1002'
Age > ALL（子查询）表示Age的值要大于子查询结果集中所有的值，也就是说大于最大的值。 |
4.1.2 ANY子查询
|
1. 查询1002班中不小于1001班中最小年龄的学员 SELECT StuName,classno FROM infos WHERE Age > ANY(SELECT age FROM infos WHERE ClassNO='1001') AND ClassNO = '1002'
Age > ANY（子查询）表示Age的值大于子查询结果集中任意一个值，也就是说大于最小值。 |
4.1.3 IN 和NOT ＩＮ 子查询
|
１. 查询参加考试的学员信息 SELECT StuName FROM infos WHERE StuID IN (SELECT StuID FROM Scores) 2. 查询没有参加考试的学员信息 SELECT StuName FROM infos WHERE StuID NOT IN (SELECT StuID FROM Scores) |
4.1.4 EXISTS和NOT EXISTS子查询
|
１. EXISTS在创建库中的语法
IF EXISTS(SELECT * FROM sysdatabases WHERE name ='StuDB') DROP DATABASE StuDB
２. 考试结束后要为机试成绩加分，规则如下，如果有超过90分的学员，低于90分的学员每人加2分，否则低于90分的学员每人加6分
IF EXISTS(SELECT * FROM Scores WHERE LabScore > 90) UPDATE Scores SET LabScore = LabScore + 2 WHERE LabScore < 90 ELSE UPDATE Scores SET LabScore = LabScore + 6 WHERE LabScore < 90
３. 考试结束后要为机试成绩加分，规则如下，如果没有超过90分的学员，每人加3分，否则不加分。
IF NOT EXISTS(SELECT * FROM Scores WHERE LabScore > 90) UPDATE Scores SET LabScore = LabScore + 3
|
4.2 多表查询
|
１. 查询考试学员的成绩信息 第一种方式（内联接）
SELECT infos.StuID,StuName,WrittenScore,LabScore FROM infos INNER JOIN Scores ON infos.StuID = Scores.StuID
第二种方式（使用from子句查询）
SELECT infos.StuID,StuName,WrittenScore,LabScore FROM infos,Scores WHERE infos.StuID = Scores.StuID
|
4.3 DISTINCT查询
|
DISTINCT 关键字可从 SELECT 语句的结果集中消除重复的行。如果没有指定 DISTINCT，将返回所有行，包括重复的行。如果使用了 DISTINCT，就可以消除重复的行。 例： SELECT DISTINCT ClassNO FROM infos
|
4.4 UNION查询
|
使用 UNION 关键字，可以在一个结果集中包含多个 SELECT 语句的结果集。任一 SELECT 语句返回的所有行都可合并到 UNION 表达式的结果中。
例： SELECT id,username FROM users UNION SELECT id,rolename FROM roles
在使用UNION时，被合并的两个查询语句必须查询相同的列数量。例如第1个语句查询了2列，那么第二个语句也必须查询2列。 多个查询语句对应列的数据类型必须相同。例如第一个查询语句第一列是整型，那么第二个查询语句的第一列也必须是整型 使用UNION查询时，结果集完全相同的记录只保留一条。若想保留所有完全相同的记录应使用UNION ALL。
|
4.5 使用 SELECT INTO 查询
SELECT INTO 语句用于创建一个新表，并用 SELECT 语句的结果集填充该表。新表的结构由查询列来决定。
语法格式：SELECT INTO
|
SELECT 列 INTO 新表 FROM 数据源表 |
|
１. 将成绩表中及格学员的信息存储到新表中，并显示。 SELECT Id,Term,StuID,ExamNo,WrittenScore,LabScore INTO NewTable FROM Scores WHERE WrittenScore >= 60 AND LabScore >= 60 SELECT * FROM NewTable |
4.6 TOP 分页查询
代码演示：TOP分页原理
|
SELECT TOP PageSize * FROM TableName WHERE ColumnName1 NOT IN ( SELECT TOP PageSize * (CurrentPage -1 ) ColumnName1 FROM TableName ORDER BY ColumnName2 ) ORDER BY ColumnName2 DESC |
5 常用函数
在T-SQL中，定义了一系列的函数，以增加T-SQL在数据查询及业务逻辑处理方面的
能力。下面我们来看一下在这方面常用的函数。
5.1 字符串函数
- CHARINDEX
用于在指定的字符串中查找另一个指定的字符串首次出现的位置（从1开始），并可
以同时指定起始的搜索位置（从1开始）。
代码演示：CHARINDEX
|
SELECT CHARINDEX('林冲','108好汉中有林冲',1) |
代码解析：
第一个字符串为要查找的字符串，第二个字符串为要操作的字符串，第三个参数搜索
的起始位置。
输出：8。
- LEFT
用于在操作字符串中从左边返回指定的数目字符子串。
代码演示：LEFT
|
SELECT LEFT('108好汉中有林冲',3) |
代码解析：
在操作字符串中从左边返回3个字符的字符串。
输出：108。
- RIGHT
用于在操作字符串中从右边返回指定的数目字符子串。
代码演示：RIGHT
|
SELECT RIGHT('108好汉中有林冲',2) |
输出：林冲。
- LEN
返回字符串中的包含的字符个数，即字符串的长度。
代码演示：LEN
|
SELECT LEN('108好汉中有林冲') |
输出：3。
- LOWER
将指定字符串转化为小写，并返回转化后的结果。
代码演示：LOWER
|
SELECT LOWER('HELLO，林冲!') |
输出：hello ，林冲。
- UPPER
将指定字符串转化为大写，并返回转化后的结果。
代码演示：UPPER
|
SELECT UPPER('hello，林冲!') |
输出：HELLO ，林冲。
- LTRIM
去除操作字符串中左边的空格，并返回操作后的结果。
代码演示：LTRIM
|
SELECT LTRIM(' hello，林冲!') |
输出：hello，林冲!。
- RTRIM
去除操作字符串中右边的空格，并返回操作后的结果。
代码演示：RTRIM
|
SELECT RTRIM('hello，林冲! ') |
输出：hello，林冲!。
- REPLACE
在操作字符串中将指定的字符串替换成另一个字符串。
代码演示：REPLACE
|
SELECT REPLACE('电视里那个唧唧歪歪的变态男好恶心','变态','bt') |
代码解析：
第一个字符串为要查找的字符串，第二个字符串为要被替换的字符串，第三个字符串
为最终要被替换为的字符串。
输出：电视里那个唧唧歪歪的bt男好恶心。
- REVERSE
得到指定字符串的逆向表达式。
代码演示：REVERSE
|
SELECT REVERSE('电视里那个唧唧歪歪的变态男好恶心') |
输出：心恶好男态变的歪歪唧唧个那里视电。
- STR
得到返回由数字数据转换来的字符数据。
代码演示：STR
|
SELECT STR(123.56,5,1) |
代码解析：
第一个参数为要转换的数字，第二个参数用于指定转换后的字符串的长度，第三个参数指定保留四舍五入后的小数位数。
输出：123.5。
- SUBSTRING
在指定的字符串中取子串操作。
代码演示：SUBSTRING
|
SELECT SUBSTRING('029-89898989',1,3) |
代码解析：
第一个参数为操作的字符串，第二个参数用于取子串的起始位置，第三个参数指定所取的子串的长度。
输出：029。
5.2 数学函数
- PI
返回常量PI的值。
代码演示：PI
|
SELECT PI() |
输出：3.14159265358979。
- FLOOR
返回小于或等于指定数值表达式的最大整数。
代码演示：FLOOR
|
SELECT FLOOR(2.46) SELECT FLOOR(3.0) |
输出：2和3。
- CEILING
返回大于或等于指定数值表达式的最小整数。
代码演示：CEILING
|
SELECT CEILING(2.46) SELECT CEILING(3.0) |
输出：3和3。
- POWER
返回指定表达式的指定幂的值。
代码演示：POWER
|
SELECT POWER(25,3) |
代码解析：
第一个参数为操作数，第二个参数为幂值。
输出：15625。
- SQRT
返回指定表达式的平方根。
代码演示：SQRT
|
SELECT SQRT(25) |
输出：5。
- RAND
返回从 0 到 1 之间的随机 float 值。
代码演示：RAND
|
SELECT RAND(212) |
代码解析：
参数为种子值。
输出：某次得到0.717523550148404。
案例1：
设有数据表Test，Test表只有ID和Data两列，其中ID为自增长标识列，而Data
为int类型的数据列，现要求在Data列中插入10000个0-1000之间的随机数。
代码演示：在Test表中随机插入1000条数据
|
DECLARE @i INT SET @i = 0 WHILE(@i<1000) BEGIN INSERT INTO Test VALUES(FLOOR(RAND()*1000)) ① SET @i=@i+1 END SELECT TOP 10 * FROM Test ② |
代码解析：
① 构造随机数据并在Test表中插入数据。
② 查询前10数据。
输出：如图1所示。
图1 在Test中插入1000条数据后前10条的结果
- ROUND
返回一个数值表达式，舍入到指定的长度或精度。
代码演示：ROUND
|
SELECT ROUND(4.256,2) |
代码解析：
第一个参数为操作数，第二个参数指定舍入到的小数位，如上述代码中舍入到2位小
数。
输出：4.260。
5.3 时间函数
- GETDATE
返回系统当前时间。
代码演示：GETDATE
|
SELECT GETDATE() |
输出如： 2009-09-02 15:19:19.920。
- DATEPART
返回当前日期中指定的日期部分。
代码演示：DATEPART
|
SELECT DATEPART(mm,GETDATE()) |
代码解析：
返回当前日期的月份，其中mm代表选取的日期部分为月份，类似这样日期部分的
缩写如下表：
|
日期部分 |
含义 |
缩写 |
|
year |
年份 |
yy，yyyy |
|
quarter |
季度 |
qq，q |
|
month |
月分 |
mm，m |
|
dayofyear |
一年中的第多少天 |
dy，y |
|
day |
当月中的第多少天 |
Dd，d |
|
week |
一年中的第多少个星期 |
wk，ww |
|
weekday |
返回与一周的某一天对应的数字，例如：Sunday = 1, Saturday = 7 |
dw |
|
hour |
小时 |
hh |
|
minute |
分钟 |
mi，n |
|
second |
秒 |
ss，s |
|
milisecond |
毫秒 |
ms |
表1 各日期部分的含义及缩写
输出如：9。
- DATEADD
返回在给指定日期加上一个时间间隔后的新 datetime 值。
代码演示：DATEADD
|
SELECT GETDATE() ① SELECT DATEADD(mm,5,getdate()) ② |
代码解析：
① 得到系统当前时间，如：2009-09-02 15:46:43.640。
② 在系统当前时间的月份日期部分上加上5，即得到系统当前时间5个月后的时间,如系统当前时间为2009-09-02 15:46:43.640情况下得到的值为：2010-02-02 15:46:43.640。
其中mm代表的操作的日期部分为月份，类似这样日期部分的缩写如表1。
- DATEDIFF
返回两个指定日期之间的指定时间部分的时间间隔数。
代码演示：DATEDIFF
|
SELECT DATEDIFF(dd,DATEADD(mm,5,GETDATE())) |
代码解析：
DATEADD(mm,5,GETDATE())得到了一个在当前系统日期5个月后的日期。
整体返回间隔的天数，其中dd为天数的缩写形式，其它的缩写形式参见表1。
输出如：当前日期为2009-09-02 16:13:13.683时为153。
5.4 系统函数
- CONVERT
用于实现数据类型的转换。
代码演示：CONVERT
|
SELECT CONVERT(VARCHAR(4),2536) |
输出：得到2536的字符串形式。
- DATALENGTH
用于返回指定的表达式的字节数。
代码演示：DATALENGTH
|
SELECT DATALENGTH('108好汉中有林冲') SELECT DATALENGTH(2569) |
输出：15和4。
- HOST_NAME
用于返回当前登录用户所在的计算机名。
代码演示：HOST_NAME
|
SELECT HOST_NAME() |
6 视图、索引、事务
6.1 视图
视图的语法
|
IF EXISTS(SELECT * FROM Sysobjects where NAME = '视图名称') ① DROP VIEW <视图名称> GO CREATE VIEW <视图名称> AS 查询语句 GO |
使用视图更新数据
|
默认情况不能利用视图实现删除和插入数据，因为会影响到多张基表，如果一定要用视图做删除操作和插入操作，则可以使用具有支持 INSERT、UPDATE 和 DELETE 语句逻辑的 INSTEAD OF 触发器。 |
6.2 索引
4
5
6
6.1
6.2
6.2.1 索引的概念
6.2.2 索引相关的概念
|
１. 数据页 索引数据和数据表中的数据一般情况下是以数据页为单位进行存储的，SQL Server 2005 中每个数据页占8KB的空间大小。 ２. 索引数据页 通过索引机制为所有存储数据表中数据的数据页（可以将其想像成图书中的一页）建立一个目录。 ３. 填充因子 在索引数据页中填充数据时，最好不要将整个数据页填满，要留有一定的空间，以便保留一定百分比的可用空间供以后扩展索引。例如，指定填充因子的值为 80 表示每个数据页上将有 20% 的空间保留为空，以便随着在基础数据表中添加数据而为扩展索引提供空间，而不会影响到其它的数据页。
填充因子值是 1 到 100 之间的一个百分比数据。当填充因子的值设为0或100时，表示由系统自动决定填充因子的大小。 ４. |
6.2.3 索引的分类
|
１. 聚集索引（clustered索引） 所谓的聚集索引，就像新华字典中的按拼音检字或图书的目录，出现在索引数据页中前面位置的索引数据，对应的数据表中的数据在物理数据页中也位于前面。即索引页中数据的存储顺序与数据页中数据的存储顺序是一致的。 ２. 非聚集索引（non clustered索引） 所谓的非聚集索引，就像新华字典中的按偏旁部首检字，出现在在索引数据页中前位置的索引数据，对应的数据表中的数据在物理数据页不一定位于前面。即索引页中数据的存储顺序与数据页中数据的存储顺序是不一致的。 每个表只能有一个聚集索引，可以同时具有多个非聚集索引，但小于249个。 ３. 唯一索引 唯一索引是指既是索引，也是唯一约束。 ４. 主键索引 主键索引是指既是索引，也是主键约束，即按照主键的值建立索引。
|
6.3 事务
|
BEGIN TRAN ① DECLARE @error int ② SET @error = 0 ③ UPDATE Bank SET CurrentMoney = CurrentMoney - 1000 WHERE CustomerID='10012213' ④ SET @error = @error +@@ERROR ⑤ UPDATE Bank SET CurrentMoney = CurrentMoney + 1000 WHERE CustomerID='13012215' SET @error = @error +@@ERROR ⑥ IF @error>0 ROLLBACK TRAN ⑦ ELSE COMMIT TRAN ⑧ |
代码解析：
① 开始事务，也可以写作BEGIN TRANSACTION，从此处开始到ROLLBACK TRAN或
COMMIT TRAN之间所有的数据操作SQL语句都会被组合成一个事务来看待，也即它们要么同时执行成功，要么同时执行失败，也即所有的语句都执行成功才提交事务，只要有一条语句执行失败，则回滚事务。
② 声明一个变量用于记录在组成事务的各SQL语句中是否会出现错误。
③ 设值@@ERROR的初值。
④ 为林冲减钱的操作。
⑤ 由于@@ERROR全局变量只是记录最近执行的SQL语句的出错信息，所以如果出错需要将错误信息的ID值记录于@error变量中。
⑥ 为宋江加钱的操作。
⑦ 如果出错则回滚事务，即撤消这之前针对数据表所做的所有的操作，类似于安装程
序过程中如果出错的回滚动作。
如果没有出错，则提交事务，即将这之前针对数据表所做的各项操作落到实处。
² 事务的分类
事务可以分为显式事务、自动提交事务、隐式事务等。
- 显式事务
有明确的开始、回滚或提交。
- 自动提交事务
默认情况下每条SQL语句即是一个自动提交事务。
- 隐式事务
一般没有明确的事务开始。
² 事务的特性
事务有4个特性，称其为ACID特性，分别是：
- 原子性(Atomicity)：事务是一个原子的单元。一个事务中的各组成操作是不可分的（原子的）。事务中的所有元素必须作为一个整体提交或者回滚。如果事务中的任何操作失败，则整个事务将失败。
- 一致性(Consistency)：当事务结束时，数据必须处于一致状态。在数据库中，事务执行结束时，所有的内部数据结构和数据（如：表中的记录）都必须是正确的，符合客观实际的。
- 隔离性(Isolation)：对数据进行操作的若干并发事务之间彼此隔离。也就是说，事务是独立的，一个事务的不会依赖和影响其他事务。
持久性(Durability)：事务提交后，对于系统的影响是持久性的。也就是说，事务一旦提交，数据库表中的数据将被更新，即便系统出现故障重新启动，数据库中被更新的数据也不会丢失，并且一般情况下不能再被自动撤消。
7 存储过程
什么是存储过程
类似于其它编程语言中的函数，在数据库中预先将多条SQL语句写好并起一个名字存储起来，在需要时调用，调用时可以带有输入或输出参数，也可以有返回值。
存储过程的优点：
- 存储过程存放在服务器端，不需要发送SQL语句，节省网络带宽。
- 存储过程在创建时编译完成，访问时不再编译直接调用，减轻服务器压力。
- 存储过程可实现重用，减轻开发人员负担，同时降低维护难度。
- 安全性高，只有被授权的用户才具有存储过程的使用权。
- 存储过程有参数和返回值概念，可以更好的控制存储过程的执行。
存储过程可以分为系统存储过程和自定义存储过程。
7.1 系统存储过程
SQL Server中为了维护和管理当前数据库，往往是通过一种特殊的存储过程执行的，这种存储过程被称为系统存储过程，大都是以sp_前缀。按照用户又可分为目录系统存储过程、任务计划系统存储过程等几类，但是系统存储过程大都是用于维护和管理数据库系统的，所以下表中列出的是最常用的几个系统存储过程：
|
存储过程 |
描述 |
|
sp_databases |
列出SQL Server实例中的数据库 |
|
sp_tables |
当前环境中查询的对象列表 |
|
sp_columns |
当前环境中查询的指定表或视图的列信息 |
|
sp_grantlogin |
创建SQL Server登录名 |
|
sp_grantdbaccess |
将数据库用户添加到当前数据库 |
表1 常见的系统存储过程
可以使用EXECUTE关键字调用存储过程。
语法结构：调用存储过程
|
EXEC[UTE] ① 存储过程名字 参数列表 |
语法解析：
① EXECUTE关键字在使用时，可以使用前4个字母代替。
|
如果调用的是无参存储过程，可以省略EXEC直接调用。 |
案例1：使用系统存储过程sp_databases
代码演示：系统存储过程
|
--指定当前数据库 USE Master GO --调用系统存储过程 EXEC sp_databases GO |
保存上面代码，使用“F5”运行，效果如下图。
图1 当前实例中的所有数据库
Sql Server中还有一种系统存储过程是以xp_作为前缀的，被称为扩展存储过程。常见的扩展存档过程如下表所示：
|
存储过程 |
描述 |
|
xp_cmdshell |
操作系统命令行解释器的方式执行给定的命令字符串 |
|
xp_logininfo |
返回Windows用户和Windows组信息 |
|
xp_grantlogin |
授予Windows组或用户对SQL Server的访问权限 |
表2 常见的扩展存储过程
案例2：使用扩展存储过程xp_cmdshell
代码演示：扩展存储过程
|
--指定当前数据库 USE Master GO --调用系统存储过程 EXEC xp_cmdshell 'md C:\test' GO |
保存上面代码，使用“F5”运行，可以盘根目录建立文件夹“test”。
|
xp_cmdshell是一个特殊的存储过程，SQL Server2005处于安全性的考虑，默认禁用xp_cmdshell如图3所示，可以使用sp_configure启动xp_cmdshell |
代码演示：启动xp_cmdshell
|
-- 启用xp_cmdshell EXEC sp_configure 'xp_cmdshell', 1 GO --重新配置 RECONFIGURE GO |
图2 xp_cmdshell默认被禁止使用
7.2 自定义存储过程
系统存储过程更多的还是进行系统数据库的管理，一般是不可以编辑的。开发人员需要编写自定义的存储过程来完成数据库操作。
² 无参存储过程
语法结构：创建无参存储过程
|
USE 数据库名 GO
CREATE PROCE[DURE] 存储过程名 AS BEGIN ... SQL语句 ① ... END GO |
语法结构：
① SQL语句可以是多条语句，但是必须符合T-SQL语法。
语法结构：修改无参存储过程
|
USE 数据库名 GO
ALTER PROCE[DURE] 存储过程名 AS BEGIN ... SQL语句 ① ... END GO |
语法结构：删除无参存储过程
|
USE 数据库名 GO
DROP PROCE[DURE] 存储过程名 GO |
|
同一个存储过程只能被创建一次，注意当前操作的数据库。 |
案例3：创建无参存储过程查询所有学生信息。
代码演示：创建无参存储过程
|
USE StuDB GO
CREATE PROC GetAllStudents AS BEGIN SELECT * FROM Infos END GO |
保存上面的代码，运行成功后，存储过程创建成功，如下图所示。
图3 自定义存储过程
存储过程的创建，相当于是一个SQL语句编译的过程，SQL语句并没有执行，编译完成后存储过程就创建成功，并保存在数据库中。当需要时，可以使用上面指定的语法调用存储过程即可。
代码演示：调用无参存储过程
|
USE StuDB GO
EXEC GetAllStudents GO |
保存上面代码，运行效果如下图：
图4 存储过程运行效果
² 带有输入参数的存储过程
往往在数据库操作过程需要根据不同的查询条件得到不同的查询结果，那么就需要在存储过程中加入参数，由参数来充当查询条件，决定最终的查询结果。由调用方向存储过程提交的参数又可称为输入参数，类似于C#中方法的参数。
语法结构：带有输入参数的存储过程
|
CREATE PROCE[DURE] 存储过程名 参数名 数据类型, ① ... ..., 参数名 数据类型 AS BEGIN ... SQL语句 ... END GO |
语法解析：
① 存储过程参数是由T-SQL变量构成的，多个参数之间用逗号隔开。
案例4：创建有输入参数的存储过程，按性别查询学生信息。
案例分析：本例中带有查询条件，所以需要带有输入参数的存储过程。
代码演示：创建有输入参数的存储过程
|
USE StuDB GO
CREATE PROC GetStudnetByGender @Gender VARCHAR(2) AS BEGIN SELECT * FROM Infos WHERE Gender = @Gender END GO |
代码演示：执行有输入参数的存储过程
|
USE StuDB GO
--定义变量 DECLARE @Gender VARCHAR(2) --初始化变量 SET @Gender = '男' --调用存储过程 EXEC GetStudnetByGender @Gender GO |
在调用有参数的存储过程时，参数必须提交，否则会出现错误。如下图所示。如果参数没有特殊制定的话，参数的个数、顺序、数据类型必须和存储过程中定义的保持一致。
图5 调用存储过程出错
² 有默认输入参数的存储过程
如果输入参数在大多数情况下保持数值不变，或者无法提供输入参数时，可以使用带有默认值的输入参数。如果调用时提供了数据，则使用该数据，否则将使用默认值代替。
语法结构：有默认输入参数的存储过程
|
CREATE PROCE[DURE] 存储过程名 参数名 数据类型 [= 默认值], ① ... ..., 参数名 数据类型 [= 默认值] AS BEGIN ... SQL语句 ... END GO |
语法解析：
默认值可以是相应类型的常量值，也可以是NULL。
案例5：创建有默认值的输入参数存储过程，查询大于等于指定年龄的学员信息，年龄默认22岁。
代码演示：创建有默认值输入参数的存储过程
|
USE StuDB GO
CREATE PROC GetStudentByAge @Age INT = 22 AS BEGIN SELECT * FROM Infos WHERE Age >= @Age END GO |
代码演示：执行有默认输入参数的存储过程
|
USE StuDB GO
DECLARE @Age INT SET @Age = 30 EXEC GetStudentByAge @Age GO |
² 带有输出参数的存储过程
上面几个例子中，存储过程查询结果是以记录集（网格）的形式返回的，在SQL高级编程中，有一些场合需要将查询结果以变量形式返回。这种情况需要使用带有输出参数的存储过程。
语法结构：有输出参数的存储过程
|
CREATE PROCE[DURE] 存储过程名 参数名 数据类型 [= 默认值], ... ..., 参数名 数据类型 OUTPUT ① AS BEGIN ... SQL语句 ... END GO |
语法解析：
① 被OUTPUT关键字修饰的参数，称为输出参数。
案例6：创建有输出参数的存储过程，查询指定班级的人数。
案例分析：需要两个参数分别是：输入参数班级名称和输出参数班级人数。
代码演示：创建有输出参数的存储过程
|
USE StuDB GO
CREATE PROC GetStudentCountByClassNo @ClassNo VARCHAR(4), @Count INT OUTPUT AS BEGIN SELECT @Count = COUNT(*) FROM Infos WHERE ClassNo = @ClassNo END GO |
代码演示：执行有输出参数的存储过程
|
USE StuDB GO
--定义变量 DECLARE @ClassNo VARCHAR(4) DECLARE @Count INT --初始化变量，输出参数不需要初始化 SET @ClassNo = '1002' --调用是必须使用OUTPUT关键字 EXEC GetStudentCountByClassNo @ClassNo,@Count OUTPUT --打印结果 PRINT @ClassNo + '班人数是：'+Convert(VARCHAR(4),@Count) GO |
保存上面的代码，运行后效果如下图所示。
图6 有输出参数存储过程
|
在调用有输出参数的存储过程时，如果没有使用OUTPUT关键字修饰输出参数，那么系统将以输入参数执行，结果将不能够返回。 |
² 带有返回值的存储过程
在C#方法中，可以指定方法的返回类型，根据方法的执行情况，使用return关键字将数据返回给调用方，在编程过程中频繁的被使用到。在存储过程也可以使用return关键字获得返回值，与OUTPUT输出参数并不冲突。下边使用返回值重新实现上例。
案例7：创建具有返回值的存储过程，返回指定班级的人数。
代码演示：创建具有返回值的存储过程
|
USE StuDB GO
CREATE PROC GetStudentCountByClassNo @ClassNo VARCHAR(4) AS DECLARE @Count INT BEGIN SELECT @Count = COUNT(*) FROM Infos WHERE ClassNo = @ClassNo RETURN @Count END GO |
代码演示：执行具有返回值的存储过程
|
USE StuDB GO
--定义变量 DECLARE @ClassNo VARCHAR(4) DECLARE @Count INT --初始化变量，返回值不需要初始化 SET @ClassNo = '1002' --调用是必须使用OUTPUT关键字 EXEC @Count = GetStudentCountByClassNo @ClassNo --打印结果 PRINT @ClassNo + '班人数是：'+Convert(VARCHAR(4),@Count) GO |
从上面的例子可以看到，返回值和输出参数在完成某些功能时非常相似，效果相同。但是每一个存储过程只允许有一个返回值，而输出参数可以出现多个。需要根据不同的情况选择不同的操作方法。
7.3 自定义错误处理
在数据库操作过程中，往往会出现各种各样的错误，这种错误是由系统在执行过程中产生的错误，在错误信息中会包含错误号、状态、级别、位置、描述等信息。根据这些错误信息提示，开发人员可以对代码进行调整，从而解决操作中出现的错误。如下图所示，错误是由数据类型转换所产生的，那么只需要将数据“粉巷”保存到VARCHAR类型即可解决当前错误。一个良好的错误信息提示，可以帮助开发人员提高开发效率。
图7 数据库错误
数据库错误处理也可以由开发人员自定义，在存储过程中RAISERROR关键字可以生成错误消息并启动会话的错误处理。例如：创建一个根据班级号获取班级人数的存储过程，需要在调用时传入班级号，由于班级号是4个字符，所以在存储过程中可以加入对班级号位数的判断，如果不等于4位可以使用RAISERROR关键字引发自定义错误处理。
语法结构：自定义错误处理
|
RAISERROR ( msg_str ①, serverity ②, state ③) |
语法解析：
① 错误的提示信息，最长可以是2047个字符。
② 错误的严重级别，普通用户可以设置级别在0~18之间，特殊用户可以设置级别为19~25，而且需要做其他配置。
③ 错误状态号，可以设置为0~255的整数。
案例8：在存储过程中自定义错误处理程序
代码演示：自定义错误处理
|
USE StuDB GO
CREATE PROC GetStudentCountByClassNo @ClassNo VARCHAR(4) AS DECLARE @Count VARCHAR(30) BEGIN IF(LEN(@ClassNo) != 4) BEGIN RAISERROR('输入的班级号有误',16,100) ① END ELSE BEGIN SELECT @Count = count(*) FROM Infos WHERE ClassNo = @ClassNo return @Count END END GO |
代码解析:
① 错误信息是'输入的班级号有误'，严重级别是16，错误状态码是100。
图8 自定义错误
什么是触发器
触发器（TRIGGER）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由某一个事件动作来自动触发，比如当对一个表进行操作（INSERT，DELETE，UPDATE）时就会激活它执行，并具有事务特征，在操作失败时可以进行回滚。
触发器的原理是利用一个动作引发另外一个动作，现实中有很多细节都会有触发器的体现，例如我们去银行取钱，表面上看，取钱就是一个UPDATE操作，将指定帐户的余额减少一定数目即可，但是一个UPDATE操作是不能满足用户需求的，为了提高数据的完整性，还需要保存用户的操作记录。所以在取钱流程图中加入一个新的环节——保存记录。
我们可以把取钱看做是一个动作，保存记录也看做一个动作，两者具有一个绑定关系，由取钱动作触发了保存记录动作。而银行操作中的其他动作，如：开户、存钱、销户等，都需要有保存记录动作，如果失败，可以回滚数据，如果成功，则操作结束。如下图所示。
图1 银行操作流程
实现上述业务，使用数据库触发器来完成比较合适。由一个动作触发另外一个动作，由银行操作动作触发保存记录动作，保存记录动作在触发器的作用下自动完成。
使用触发器完成上面案例的优点如下：
- 在每一个操作中都保存操作记录，提高数据的完整性，加强业务规则。
- 保存操作记录是自动完成的，效率高。
8 触发器
在银行操作中，开户、存钱、取钱、销户以及其他操作在数据库中我们都可以归纳为INSERT、UPDATE、DELETE操作。完成上述操作至少需要两张表：UserInfo表（用户信息表）、UserLog表（操作记录表）。UserInfo表保存用户的基本信息，在用户进行开户、存钱、取钱、销户等操作时，对UserInfo表进行数据的增删改。UserLog表保存用户的操作记录，用户开户、存钱、取钱、销户的操作，都需要在UserLog表中记录，以备查询，并且自动完成。
|
列名 |
类型 |
描述 |
|
CardID |
VARCHAR(5) |
卡号，主键 |
|
Account |
VARCHAR(20) |
帐户 |
|
Balance |
MONEY |
余额 |
表1 UserInfo表
|
列名 |
类型 |
描述 |
|
LogID |
INT |
主键，自增 |
|
CardID |
VARCHAR(5) |
卡号 |
|
Account |
VARCHAR(20) |
帐户 |
|
Type |
VARCHAR(4) |
开户、存钱、取钱、销户 |
|
Amount |
MONEY |
数额 |
|
Time |
DATETIME |
操作时间 |
表2 UserLog表
为了实现信息管理的自动化，在操作UserInfo表和UserLog表时，可以采用数据库触发器实现自动保存记录功能。
语法结构：触发器语法
|
--创建触发器 CREATE TRIGGER 触发器名 ON 表名 FOR 触发器操作类型 ① AS BEGIN ... 触发器SQL语句 ... END GO
--修改触发器 AlTER TRIGGER 触发器名 …
--删除触发器 DROP TRIGGER 触发器名 |
语法解析：
① 触发器操作可以是INSERT、UPDATE、DELETE
8.1 INSERT触发器
INSERT语句引发触发器的时候，是借助于INSERTED表来完成的。INSERTED 表用于存储 INSERT语句所影响的行的副本。在一个插入事务处理中，新建行被同时添加到INSERTED表和触发器表中。INSERTED表中的行是触发器表中新行的副本，根据INSERTED表中的信息完成触发语句，如下图所示。
图2 INSERT触发器
案例1：用户在银行开户时，保存开户信息。
案例分析：开户业务是针对UserInfo表的INSERT操作，在UserInfo表创建INSERT触发器，当INSERT语句向UserInfo表中插入数据时执行的触发器，新的数据行INSERTED表中，通过触发器语句保存开户信息。
代码演示：INSERT触发器
|
USE BankDB GO
CREATE TRIGGER trInsert ON UserInfo FOR INSERT ① AS BEGIN --定义变量 DECLARE @CardID VARCHAR(5) DECLARE @Accounts VARCHAR(20) DECLARE @Balance MONEY --从临时表中获取插入数据 SELECT @CardID = CardID,@Accounts = Accounts,@Balance = Balance FROM INSERTED ② --判断插入数据是否为空 IF @CardID IS NOT NULL INSERT INTO UserLog Values (@CardID,@Accounts,'开户',@Balance,GetDate()) ③ END GO |
代码解析：
① 在UserInfo表中创建INSERT触发器。
② INSERTED是保存插入数据的临时表，通过INSERTED可以获得用户插入的数据。
③ 从INSERTED临时表中获取的数据，保存到UserLog表中存储。
保存上面的代码，运行成功后，触发器创建成功，如图3所示。
|
数据库触发器在条件满足的情况下自动执行，无需手动调用。 |
图3 触发器
UserInfo表的INSERT触发器已经创建成功，利用下面的SQL语句测试触发器效果。
代码演示：模拟银行开户
|
--模拟开户信息。卡号：10001，帐户：林冲，开户金额：100。 INSERT INTO UserInfo Values ('10001','林冲','100') |
保存上面的代码，运行成功后，触发器自动执行，如下图所示。
图4 插入成功
上图中2行受影响，分别在UserInfo表和UserLog表中执行INSERT操作成功。
|
Q 老师，如果在当前表的INSERT触发器中，对当前表执行INSERT操作，结果会怎么样？会进入死循环吗？ A 在INSERT触发器内部，再次对当前表执行INSERT操作时，不会递归执行触发器，所以不会出现死循环。只有在触发器外部的INSERT操作才能够执行触发器。 |
8.2 DELETE触发器
DELETE语句引发触发器的时候，是借助于DELETED表来完成的。DELETED表用于存储DELETE语句所影响的行的复本。在执行DELETE语句时，行从触发器表中删除，并传输到DELETED表中，根据DELETED表中的数据，完成触发操作，如下图所示。
图5 DELETE触发器
案例2：在用户销户时，保存销户信息。
案例分析：销户在数据库中是DELETE操作，在UserInfo表创建DELETE触发器，当DELETE语句从UserInfo表中删除数据时执行触发器，删除的数据行从触发器表删除的同时也保存到DELETED表中，DELETED表和INSERTED表一样也是一个临时表，通过DELETED表实现保存销户信息。
代码演示：DELETE触发器
|
USE BankDB GO
CREATE TRIGGER trDelete ON UserInfo FOR DELETE AS BEGIN DECLARE @CardID VARCHAR(5) DECLARE @Accounts VARCHAR(20) DECLARE @Balance MONEY
SELECT @CardID = CardID,@Accounts = Accounts,@Balance = Balance FROM DELETED ① IF @CardID IS NOT NULL INSERT INTO UserLog Values (@CardID,@Accounts,'销户',@Balance,GetDate()) END GO |
代码分析：
① DELETED临时表和INSERTED临时表作用和使用方法基本一致，都可以用来做数据的转存。
保存上面的代码，运行成功后，触发器创建成功。
代码演示：模拟银行销户
|
--删除指定帐户 DELETE FROM UserInfo WHERE CardID = '10001' --查看记录信息 SELECT * FROM UserLog |
保存上面的代码，运行成功后，触发器自动执行，如下图所示。
图6 销户信息
8.3 UPDATE触发器
UPDATE语句引发触发器时，需要同时借助于DELETED表和INSERTED表，UPDATE语句使数据分为新、旧两种状态，DELETED表用于存储更新前旧数据的行的副本，INSERTED表用于存储更新后新数据的行的副本。在执行UPDATE语句时，行在触发器表中被更新，并传输到DELETED表和INSERTED表中，根据表中的数据，完成触发操作，如下图所示：
图7 UPDATE触发器
案例3：在存钱和取钱时，保存操作信息。
案例分析：存钱和取钱在数据库中是UPDATE操作，在UserInfo表创建UPDATE触发器，但是由于触发器没有UPDATED临时表，所以只能够借助于INSERTED临时表和DELETED临时表实现目标。UPDATE操作事实上是用新数据替换老数据，新数据保存在INSERTED临时表中，老数据则保存在DELETED临时表中，通过对这两个表的操作保存操作信息。
代码演示：UPDATE触发器
|
USE BankDB GO
CREATE TRIGGER trUpdate ON UserInfo FOR Update AS BEGIN --定义变量 DECLARE @CardID VARCHAR(5) DECLARE @Accounts VARCHAR(20) DECLARE @OldBalance MONEY DECLARE @NewBalance MONEY --获取更新前的数据 SELECT @CardID = CardID,@Accounts = Accounts,@OldBalance = Balance FROM DELETED --获取更新后的数据 SELECT @NewBalance = Balance FROM INSERTED WHERE CardID = @CardID --通过余额变换判断操作类型 IF @NewBalance > @OldBalance BEGIN INSERT INTO UserLog Values (@CardID,@Accounts,'存钱',(@NewBalance - @OldBalance),GetDate()) END ELSE IF @NewBalance < @OldBalance BEGIN INSERT INTO UserLog Values (@CardID,@Accounts,'取钱',(@OldBalance - @NewBalance),GetDate()) END END GO |
保存上面的代码，运行成功后，触发器创建成功。
代码演示：模拟银行销户
|
--更新指定帐户的余额 UPDATE UserInfo SET Balance = Balance + 50 WHERE CardID = '10001' UPDATE UserInfo SET Balance = Balance - 50 WHERE CardID = '10001' --查看记录 SELECT * FROM UserLog |
保存上面的代码，运行成功后，触发器自动执行，如图8所示。
图8 存取钱信息
在使用DELETE触发器、UPDATE触发器和INSERT触发器时，如果SQL语句影响多行记录，那么触发器只会执行一次。如下例：
代码演示：删除所有用户
|
DELETE FROM UserInfo |
如果UserInfo表中有5行记录，SQL语句执行完毕后，5行记录全部被删除，但是触发器只被执行1次而非5次，所以在删除和更新时尽量控制记录唯一。
8.4 ROLLBACK操作
前面小节中介绍过，触发器也是一个特殊的事务，所以在触发器中针对不同的情况可以使用ROLLBACK语句执行回滚操作。
案例4：在取钱过程中，判断用户余额，如果余额小于 0 ，则取消当前操作。
案例分析：修改上节中创建的trUpdate触发器中，加入判断语句，如果如果INSERTED表中的数据小于0时，执行数据回滚操作。
代码演示：修改UPDATE触发器
|
ALTER TRIGGER trUpdate ON UserInfo FOR Update AS BEGIN --定义变量 DECLARE @CardID VARCHAR(5) DECLARE @Accounts VARCHAR(20) DECLARE @OldBalance MONEY DECLARE @NewBalance MONEY --获取更新前的数据 SELECT @CardID = CardID,@Accounts = Accounts,@OldBalance = Balance FROM DELETED --获取更新后的数据 SELECT @NewBalance = Balance FROM INSERTED WHERE CardID = @CardID --回滚操作 IF @NewBalance < 0 BEGIN ROLLBACK END --通过余额变换判断操作类型 IF @NewBalance > @OldBalance BEGIN INSERT INTO UserLog Values (@CardID,@Accounts,'存钱',(@NewBalance - @OldBalance),GetDate()) END ELSE IF @NewBalance < @OldBalance BEGIN INSERT INTO UserLog Values (@CardID,@Accounts,'取钱',(@OldBalance - @NewBalance),GetDate()) END END GO |
保存上面的代码，运行成功后，触发器修改成功。
代码演示：模拟银行销户
|
--更新账户“10001”的余额为50 UPDATE UserInfo Set Balance = 50 WHERE CardID = '10001' --更新账户“10001”的余额 UPDATE UserInfo SET Balance = Balance - 100 WHERE CardID = '10001' |
保存上面的代码，运行成功后，触发器自动执行，如下图所示。
图9 回滚操作
9 游标
9.1 什么是游标
在数据库开发过程中，检索数据使用SELECT语句，可以一次性的读取所有满足条件的记录。如下代码：
代码演示：检索所有数据
|
USE StuDB GO
SELECT StuName,Age FROM Infos WHERE Gender = '男' AND Age > 23 GO |
但是我们常常会遇到这样的情况，即从结果集中逐一读取每条记录，如何解决这种问题？在数据库中游标给我们提供了非常优秀的解决方案。
图1 触发器
游标（CURSOR）就是一种能从包括多条数据记录的结果集中每次提取一条记录的机制，可以通过命令语句控制游标指向的位置，获取游标当前位置的相关数据。如上图所示，游标指向结果集中的第一行，获得数据StuName=阮小二，Age=26。
游标主要功能有：
- 允许定位到结果集中的特定行。
- 从结果集的当前位置检索一行或多行数据。
- 支持对结果集中当前位置的行进行修改。
9.2 使用游标
使用游标的具体步骤如下：
- 创建游标，把游标与结果集联系起来。
- 打开游标，使游标处于可操作状态。
- 使用游标，利用游标相关命名操作数据。
- 关闭游标，可重新打开游标。
- 删除游标，即销毁游标。
² 创建游标
使用游标时，第一步需要定义游标变量，定义游标变量的语法结构如下：
语法结构：创建游标变量
|
DECLARE 游标名称 [INSENSITIVE] ① [SCROLL] ② CURSOR FOR ③ SELECT语句④ [FOR{READ ONLY⑤|UPDATE[OF 列名]⑥}] |
语法解析：
① 表示该游标使用临时表，对游标的所有请求都从tempdb中得到应答，此时将不可以修改基表中的数据。
② 指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用，否则NEXT是唯一支持的提取选项。
③ 定义游标关键字。
④ 定义游标结果集的标准SELECT语句，不允许使用关键字INTO。
⑤ 定义一个只读游标，不允许通过该游标进行更新。
⑥ 定义游标中可更新的列，只允许修改所列出的列。如果指定了UPDATE，但未指定列名，则可以更新所有列。
代码演示：创建游标
|
DECLARE CR SCROLL CURSOR FOR SELECT StuName, Age FROM Infos WHERE Gender = '男' AND Age > 23 FOR UPDATE GO |
² 打开游标
打开游标，用于执行游标定义中的查询语句，查询结果存放在游标缓冲区中。并使游标指针指向游标区中的第一条记录，作为游标的缺省访问位置。查询结果的内容取决于查询语句的设置和查询条件。
语法结构：打开游标语法
|
OPEN 游标名 ① SELECT 数据行数 = @@CURSOR_ROWS ② |
语法解析：
① 打开游标，执行游标定义的查询语句，使游标处于可操作状态。
② 获得游标结果集中的行数。
代码演示：打开游标
|
OPEN CR ① PRINT @@CURSOR_ROWS ② |
代码解析：
① 游标打开后，在没有关闭前，不可以再次打开。
② 如果游标没有打开则@@CURSOR_ROWS返回0。
² 使用游标读取单行记录
使用游标可以获得结果集中的相关数据，常见的游标操作如下：
|
语句 |
描述 |
|
FETCH FIRST |
提取游标的第一行 |
|
FETCH NEXT |
提取上次提取的行的下一行 |
|
FETCH PRIOR |
提取上次提取的行的前一行 |
|
FETCH LAST |
提取游标中的最后一行 |
|
FETCH ABSOLUTE n |
如果n 为正数，则从前提取游标中的第n行 如果n为负数，则从后提取游标中的第n行 如果n 为0，则不提取任何行 |
|
FETCH RELATIVE n |
如果n为正数，则提取上次提取的行之后的第n行。 |
表1 常见的游标操作指令
语法结构：读取结果集中的单行记录
|
FETCH [[NEXT|PRIOR|FIRST|LAST| ABSOLUTE n| RELATIVE n] FROM ] 游标名 [INTO @变量1, @变量2, ….] ① |
语法解析：
① INTO子句作用是将读取的数据存放到指定的局部变量中，每一个变量的数据类型应与游标所返回的数据类型严格匹配，否则将产生错误。
|
在使用游标进行数据操作时，必须保证游标处于打开状态。 |
代码演示：读取结果集中的第一行记录
|
DECLARE @StuName VARCHAR(10) DECLARE @Age INT FETCH FIRST FROM CR INTO @Stuname,@Age ① PRINT '当前记录的姓名是：'+@StuName +'，年龄是：'+CONVERT(VARCHAR(4),@Age) |
语法解析：
① 根据不同的指令获取指定行数据，FETCH FIRST FROM CR获得结果集中的第一条记录。
运行效果如下图：
图2 读取结果集中的第一条数据
代码演示：读取结果集中的指定行记录
|
DECLARE @StuName VARCHAR(10) DECLARE @Age INT FETCH ABSOLUTE 5 FROM CR INTO @Stuname,@Age ① PRINT '当前记录的姓名是：'+@StuName +'，年龄是：'+CONVERT(VARCHAR(4),@Age) |
代码解析：
① 获得结果集中的第5行记录的数据并赋值到局部变量中。
|
在使用ABSOLUTE n和RELATIVE n的时候，n不能够超出结果集的范围，否则将获得NULL。 |
运行效果如下：
图3 读取结果集中指定行数据
² 使用游标修改和删除记录
SQL Server中的 UPDATE语句 和 DELETE语句也支持游标操作，它们可以通过游标修改或删除游标基表中的当前数据行。
语法结构：修改和删除记录
|
--利用游标修改数据 UPDATE 表名 SET 列名= 值 [,…n] WHERE CURRENT OF 游标名 ①
--利用游标删除数据 DELETE FROM 表名 WHERE CURRENT OF 游标名 ① |
语法解析：
① 表示当前游标指针所指的当前行数据，只能在UPDATE和DELETE语句中使用。
|
代码演示：修改和删除记录
|
--将游标指向当前行记录的年龄修改为30 UPDATE Infos SET Age = '30' WHERE CURRENT OF CR
--删除游标指向的当前行 DELETE FROM Infos WHERE CURRENT OF CR |
² 关闭游标
游标使用完毕后需要关闭，游标被关闭后数据不可再读，但是使用OPEN语句可再次打开该游标。
语法结构：关闭游标
|
CLOSE 游标名 |
代码演示：关闭游标
|
CLOSE CR |
在游标关闭后，使用游标操作数据就会出现如图4的错误：
图4 游标关闭状态下操作数据
² 删除游标
游标在删除之后不可再次使用。
语法结构：删除游标
|
DEALLOCATE 游标名 |
代码演示：删除游标
|
DEALLOCATE CR |
以后有好的资料也会贴出来一起分享。谢谢大家！只是对不起整理资料的人了，希望不要责怪我！
