第七章 SQL Server高级应用

7.1 Transact-SQL程序设计

Transact-SQL语言就是在标准SQL的基础上进行扩充而退出的SQL Sever专用的结构化SQL，它最主要的用途是设计服务器端的能够在后台执行的程序块。

7.1.1 Transact-SQL程序的结构与批处理

一个Transact-SQL程序与一般的高级语言的语法要素是基本一致的，主要含有注释、变量与常量、各种运算符、函数与表达式、流程控制语句、批处理等。

Transact-SQL程序的批处理

在Transact-SQL程序内两个“GO”标记符之间的代码称为一个“批”。

SQL Server对Transact-SQL程序的编译和执行是按照“批”为单位来进行的，称为批处理。

一个Transact-SQL程序内可以包含多个“批”。

7.1.2 变量

局部变量

declare @变量名 变量类型
select @局部变量=变量值
or
set @局部变量=变量值

全局变量

全局变量由系统定义和维护的，只能使用预先说明及定义的全局变量，全局变量对于用户来说只是可读的，用户无法对他们进行修改或管理（类似于java中的static修饰的变量）。

注释符

1.--

2./**/

7.1.3 流程控制命令

begin
<命令行或程序块>
end
-------
if [not] exists(select 子查询)
<命令行或程序块>
[else <命令行或程序块>]
-------
if <条件表达式>
<命令行或程序块>
[else]
<命令行或程序块>
-------
case <表达式>
    when <表达式> then <表达式>
    when <表达式> then <表达式>
    when <表达式> then <表达式>
[else <表达式>]
end
-------
while <表达式>
begin
    <命令行或语句块>
    [break]
    [continue]
    [命令行或程序块]
end
-------
goto 标识符
return ([整数值])
-------
waitfor{delay<'时间'>|time<'时间'>|errorexit|processexit|mirrorexit}

7.1.4 常用命令

Declare

DECLARE {{ @local_variable data_type }
    | { @cursor_variable_name CURSOR }
    | { table_type_definition }
    } [, ...n]

Execute

execute命令用来执行存储过程。

Print

PRINT 'any ASCII text' | @local_variable | @@FUNCTION | string_expression 
--print用来向客户端返回一个用户自定义的信息，即显示一个字符串、局部变量或全局变量。

Select

select命令可用于给变量赋值，其语法格式如下：

SELECT {@local_variable = expression } [，...n]

Set

set既可以用于给局部变量赋值，又可以用于用户执行sql语句时，sqlserver处理选项的设定。

7.1.5 常用函数

统计函数

STDEV函数

返回表达式中所有数据的标准差。

STDEVP函数

返回表达式中所有数据的总体标准差。

VAR函数

返回表达式中所有数据的统计变异数。

VARP函数

返回表达式中所有变量的总体变异数。

算数函数

三角函数

sin() cos() tan() cot()

反三角函数

asin() acos() atan()

角度弧度转换

degrees() radians()

幂函数

exp() log() log10() sqrt()

取近似值函数

ceiling() floor() round()

符号函数

abs() sign()

其他函数

pi() rand()

字符串函数

字符转换函数

ascii() char() lower() upper() str()

去空格函数

ltrim() 用于把字符串头部的空格去掉

rtrim() 用于把字符串尾部的空格去掉

取子串函数

left() right() substring()

字符串比较函数

charindex() patindex() soundex() difference()

字符串操作函数

quotename() replicate() reverse() replace() space() stuff()

数据类型转换函数

cast() convert()

日期函数

day() mouth() year() dateadd() datediff() datename() datepart() getdate()

Text函数和Image函数

textptr() textvalid()

用户自定义函数

7.2 存储过程

7.2.1 存储过程的概念、优点及分类

存储过程是一组为了完成特定功能的sql语句集。

存储过程的优点——————

模块化的程序设计

高效率的执行

减少网络流量

可以作为安全机制使用

存储过程的分类——————

系统存储过程

用户自定义存储过程

扩展存储过程

7.2.2 创建存储过程

创建存储过程，需要确定存储过程的三个组成部分:

（1）所有的输入参数以及传给调用者的输出参数。

（2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句。

（3）返回给调用者的状态值以指明调用时成功还是失败。

用create procedure命令创建存储过程。

CREATE PROCEDURE procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]

7.3 触发器

7.3.1 触发器的概念、分类与作用

触发器是一种特殊类型的存储过程。主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。

触发器的四要素

名称：触发器有一个复合标识符命名规则的名称。

定义的的目标：触发器必须定义在表或视图上。

触发条件：是update、insert还是delete语句。

触发逻辑：触发之后如何处理。

触发器的种类

after触发器

after触发器是告诉sql语句执行了insert\update\delete操作后干什么。

instead of 触发器

告诉当要执行insert、update或delete操作时用什么别的操作来替代。

触发器的作用

强化约束、级联运行、跟踪变化、存储过程的调用。

7.3.2 触发器的工作原理

sql server在工作时为每个触发器在服务器的内存上建立两个特殊的表：插入表和删除表。

插入表的功能

一旦对该表执行了插入操作，那么对该表插入的所有行来说，都有一个相应的副本存放到Inserted表中，即Inserted表用来存储原表插入的内容。

删除表的功能

一旦对该表执行了删除操作，则将所有的删除行存放至deleted表中。这样做的目的时，一旦触发器遇到了强迫他中止的语句被执行时，删除的那些行可以从Deleted表中得以还原。

7.3.3 创建触发器

在创建触发器之前必须考虑到一下几个方面：

1. create trigger语句必须是批处理的第一个语句。
2. 表的所有者具有创建触发器的雀形权限，表的所有者不能把该权限传给其他用户。
3. 触发器时数据库对象，所以其命名必须符合命名规则。
4. 尽管在触发器的sql语句中可以参照其他数据库中的对象，但是触发器只能创建在当前数据库中。
5. 虽然触发器可以参照视图或临时表，但不能在视图或零食表上创建触发器，只能在基表或在创建视图的表上创建触发器。
6. 一个触发器只能对应一个表，这是由触发器的机制决定的。
7. 尽管truncate table语句如同没有where从句的delete语句，但是由于truncate table 语句没有被记入日志，所以该语句不能触发delete型触发器。
8. writetext语句不能触发insert或update型触发器。

create trigger changedisplay
on s
for insert
as 
select * from s

使用系统存储过程查看触发器

exec sp_help '触发器名'

通过该语句，了解触发器的一般信息，如触发器的名字、属性、类型、创造时间。

exec sp_helptext '触发器名'

通过这条语句来查看触发器的正文信息

exec sp_depends '触发器名'
exec sp_depends '表名'

查看指定触发器所引用的表或指定的表所涉及到的所有触发器。

修改触发器

sp_rename oldname,newname

通过使用sp_rename修改触发器的名字

alter trigger '触发器名'
on
as sql_sentence

通过alter trigger命令修改触发器正文

删除触发器

drop trigger '触发器名'

通过drop trigger 命令删除指定的触发器，在删除触发器所在表时，触发器将一并被删除。

7.4 备份与还原

备份与还原的定义

备份是对sql server数据库或事务日志进行复制，数据库备份记录了在进行备份这一操作时数据库中所有数据的状态，如果数据库因意外而损坏，这些备份文件将在数据库还原时用来还原数据库。

还原就是把遭受破坏、丢失的数据或出现错误的数据库还原到原来的正常状态。

备份的类型

数据库备份

数据库备份是hi对所有数据及数据库对象备份。

事务日志备份

事务日志备份是指对数据库发生的事务进行备份。

差异备份

差异备份时值将最近一次数据库备份以来发生的数据变化备份起来。

文件和文件组备份

文件或文件组备份是指对数据库文件或文件组进行备份。

备份和还原的策略

SQL server提供了几种方法来减少备份或还原操作的执行时间。

1. 使用多个备份折别来同时进行备份处理。
2. 综合使用完整数据库备份、差异备份或事务日志备份来金少每次需要备份的数据量。
3. 使用文件或文件组备份以及十五日至备份，这样可以之备份或还原那些包含相关数据的文件，而不是整个数据库。

在SQL Server2000 中由三种数据库还原模式：简单还原；完全还原；批日志还原。

创建备份设备

sp_addumpdevice [@devtype =] 'device_type'
[@logicalname =] 'logical_name'
[@physicalname =] 'physical_name'
[ {
[@cntrltype =] controller_type
|
[@devstatus =] 'device_status'
}
]

使用sp_dropdevice来删除备份设备

sp_dropdevice [@logiclname=] 'divice' [,[@delfile=] 'delfile']