SQL_Server_2008完全学习之第八章Transact-SQL编程

1、Transact-SQL概述

Transact-SQL

1）结构化查询语言（SQL）是由美国国家标准协会（ANSI，American National Standards Institute）和国际标准化组织（ISO，International Standards Organization）定义的标准，而Transact-SQL是Microsoft公司对标准的一个实现。

2）Transact-SQL语言是结构化查询语言（SQL）的增强版本，与多种ANSISQL标准兼容，而且在标准的基础上还进行了许多扩展。Transact-SQL代码已成为SQL Server的核心。Transact-SQL在关系数据库管理系统中实现数据的检索、操纵和添加功能。

分类

1）在Microsoft SQL Server 2008系统中，Transact-SQL可以创建、维护、保护数据库对象，并且可以操作对象中的数据，所以Transact-SQL语言是一种完整的语言。根据T-SQL语言的执行功能特点，可以将T-SQL语言分为三种基本类型

a)数据定义语言

b)数据操作语言

c)数据控制语言

2）其他常用类型

a)事务管理语言

b)流程控制语言

c)附加的语言元素

2、常量与变量

变量

1）声明

DECLARE

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

2）要给声明的局部变量赋值，可以使用SET或SETECT语句，格式如下：

a) SET @local_variable = expression

b) SETECT @local_variable=expression[,...n]

declare @userName nvarchar(20)

--set @userName='张三'

select @userName = (SELECT UserName from [User] where UserID=196)

select @userName

3、运算符

1）算术运算符：[+、-、*、/、%]

2）位运算符

a)【&】位与逻辑运算，从两个表达式中取对应的位。当且仅当输入表达式中两个位的值都为1时，结果中的位才被设置为1，否则，结果中的位被设置为0。

b)【|】位或逻辑运管，从两个表达式中取对应的位。如果输入表达式中两个只要有一个的值为1时，结果的位就被设置为1；只有当两个位的值都为0时，结果中的位才被设置为0。

c)【^】位异或运算，从两个表达式中取得对应的位。如果输入表达式中两个痊只有一个的值为1时，结果中的位就被设置为1；只有当两个位的值都为0或1时，结果中的位才被设置为。

3）比较运算符

=、<>、 >、!=、<、!<、>=、!>、<=

4）逻辑运算符

a)【ALL】如果一组的比较都为TRUE,则比较结果为TRUE。

b)【AND】如果两个布尔表达式都为TRUE,则结果为TRUE；如果其中一个表达式为FALSE,则结果为FALSE。

c)【ANY】如果一组的比较中任何一个为TRUE,则结果为TRUE。

d)【BETWEEN】如果操作数在某个范围之内，那么结果为TRUE。

e)【EXISTS】如果子查询中包含了一些行，那么结果为TRUE。

f)【IN】如果操作数据等于表达式列表中的一个，那么结果为TRUE。

g)【LIKE】如果操作数据与某种模式相匹配，那么结果为TRUE。

h)【NOT】对任何其他布尔运算符的结果值取反。

i)【OR】如果两个布尔表达式中的任何一个为TRUE，那么结果为TRUE。

j)【SOME】如果在一组比较中，有些比较为TRUE，那么结果为TRUE。

5）其他运算符

a)赋值运算符

b)连接运算符

c)一元运算符

1.【+】数值为正

2.【-】数值为负

3.【~】返回数字的逻辑非

4、表达式

DECLARE @STR NVARCHAR(50)

SELECT @STR = '用户名：' + UserName + ' 密码：'+ Password FROM [User] where UserId=196

SELECT @STR as Info

5、T-SQL语句中的注释

注释

1）注释是程序代码中不被执行的文本字符串，用于对代码进行说明或暂时用来进行诊断的部分语句。一般地，注释主要描述程序名称、作者名称、变量说明、代码更改日期、算法描述等。

2）在Microsoft SQL Server 2008系统中，支持两种注释方式，即双连字符（--）注释方式和正斜杠星号字符（/*...*/）注释方式

DECLARE @UserName Nvarchar(20) --用户名称

DECLARE @Password Nvarchar(32) --用户密码（MD5）

DECLARE @Result Bit --是否存在



SET @UserName='agent'

SET @Password='30B8CA8354B5AC6542797BFF9623B346'



/*

    METHOD:验证指定用户信息的数据是否存在

    NOTE:用户登录

    DATE:2013-08-21 13:35    

*/



if exists(SELECT * FROM [User] where UserName=@UserName and Password=@Password)

    SELECT 1 AS Result

else

    SELECT 0 AS Result

6、BEGIN...END语句

语法格式

BEGIN

{
sql_statement | statement_block
}

END

declare @userName nvarchar(20)

if @userName is not null

    BEGIN

        select * from [User] where userName=@userName

        print '查询成功'

    END

else

    print '查询失败，userName 参数不能为空'

7、IF..ELSE语句

语法格式

IF Boolean_expression

{sql_statement|statement_block}

ELSE

{sql_statement|statement_block}

declare @num decimal(18,2)

select @num = SUM(Amount) from ProfitLoss WHERE UserId=196



if @num>10000.00

    --BEGIN

        print '重要会员'

    --END

else

    --BEGIN

        print '普通会员'

    --END

8、IF...ESLE语句嵌套

declare @num decimal(18,2)

select @num = SUM(Amount) from ProfitLoss WHERE UserId=196



if @num>5000.00

    BEGIN

        IF @num<6000.00

            print '高级会员';

        ELSE IF @num<7000.00        

            print '中级会员'

        ELSE

            print '重要会员';

    END

else

    --BEGIN

        print '普通会员';

    --END

9、Case 语句

语法格式

CASE input_expression

WHEN when_expression THEN result_expression

[,...n]

[ELSE else_result_expression]

END

10、Case 语句示例

SELECT *,roleName=

case roleId

when 1 then '管理员'

when 2 then '代理'

when 3 then '普通会员'

else '未知角色'

end

from [User]

11、While语句

语法格式

WHILE Boolean_expression

{sql_statement|statement_block}

[BREAK]

{sql_statement|statement_block}

[CONTINUE]

{sql_statement|statement_block}

 

--打印1至100

declare @i int

set @i=0



while(@i<100)

Begin

    set @i=@i+1

    print @i

END

12、While语句示例（获取指定栏目的所有下属之栏目的编号）

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <cxmsky@gmail.com>

-- Create date: <2013-08-21 15:41>

-- Description:    <获取指定栏目下的所有子栏目>

-- =============================================

CREATE FUNCTION GetChild(@catId int)

RETURNS @t TABLE(CatId int,CatName nvarchar(10),ParentId int,Level int) 

AS

BEGIN

    declare @i int

    set @i = (SELECT [Level] from Category where CatId=@catId)

    insert into @t SELECT CatId,CatName,ParentId,[Level] from Category where CatId=@catId

    while @@rowcount>0

    BEGIN

        set @i = @i+1

        insert into @t

        SELECT a.CatId,a.CatName,a.ParentId,a.[Level]

        FROM Category a,@t b

        WHERE a.ParentId=b.CatId and b.[Level] = @i-1

    END

    return

END

13、While语句中使用其他语句

 

--求3-100之间的所有质数（素数）

--从小数学就不好

declare @i int

declare @j int

set @i=3



while @i<=100

begin

    declare @bol int

    set @bol = 1

    set @j=2

    while @j<=SQRT(@i)

    BEGIN

        if @i%@j = 0

        BEGIN

            set @bol = 0

            break

        END

        set @j=@j+1

    END

    if @bol = 1 print @i

    set @i=@i+1

end

14、WAITFOR延迟语句

语法格式

WAITFOR

{
DELAY time | TIME time
}

--等待指定时间段执行 sp_help 存储过程
waitfor delay '00:00:05'
exec sp_help

--等待到指定时间执行 sp_help 存储过程
waitfor time '16:05:05'
exec sp_help

select GETDATE()

15、GOTO语句

 

--来自SQL Server 2008联机丛书中的GOTO语句示例
DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10
BEGIN 
    SELECT @Counter
    SET @Counter = @Counter + 1
    IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.
    IF @Counter = 5 GOTO Branch_Two  --This will never execute.
END
Branch_One:
    SELECT 'Jumping To Branch One.'
    GOTO Branch_Three; --This will prevent Branch_Two from executing.
Branch_Two:
    SELECT 'Jumping To Branch Two.'
Branch_Three:
    SELECT 'Jumping To Branch Three.'

16、TRY...CATCH错误处理语句

语法格式

BEGIN TRY

{sql_statement|statement_block}

END TRY

BEGIN CATCH

{sql_statement|statement_block}

 END CATCH

begin try
    declare @num int
    select @num = (SELECT UserName from [User] where UserId=196)
end try
begin catch
    select ERROR_LINE() as '行数',ERROR_MESSAGE() as '消息'
end catch

17、数学函数

1）【ABS】返回数值表达式的绝对值

2）【EXP】返回指定表达式以e为底的指数

3）【CEILING】返回大于或等于数值表达式的最小整数

4）【FLOOR】返回小于或等于数值表达式的最大整数

5）【LN】返回数值表达式的自然对数

6）【LOG】返回数值表达式以10为底的对

7）【POWER】返回对数值表达式进行幂运算的结果

8）【ROUND】返回舍入到指定长度或精度的数值表达式

9）【SIGN】返回数值表达式的正号（+）、负号（-）或零（0）

10）【SQUARE】返回数值表达式的平方

11）【SQRT】返回数值表达式的平方根

declare @i float,@j int,@a decimal(18,2)

set @i = -12.2344
set @j = 100

print abs(@i) --取绝对值
print abs(round(@i,2)) --指定精度
print floor(abs(round(@i,2))) --返回小于或等于数值表达式的最大整数
print ceiling(abs(round(@i,2))) -- 返回大于或等于数值表达式的最小整数

print power(@j,0.5) --返回对数值表达式进行幂运算的结果
print square(@j) --返回数值表达式的平方
print sqrt(@j) --返回数值表达式的平方根

18、字符串函数 
 

1）【ASCII】 ASCII函数，返回字符表达式中最左侧的字符的ASCII代码值

2）【CHAR】ASCII代码转换函数，返回指定ASCII代码的字符

3）【LEFT】左子串函数，返回字符串中从左边开始指定个数的字符

4）【LEN】字符串函数，返回指定字符串表达式的字符（而不是字节）数，其中不包含尾随空格

5）【LOWER】小写字母函数，将大写字符数据转换为小写字符数据后返回字符表达式

6）【LTRIM】删除前导空格字符串，返回删除了前导空格之后的字符表达式

7）【REPLACE】替换函数，用第三个表达式替换第一个字符串表达式中出现的所有第二个指定字符串表达式的匹配项

8）【REPLICATE】复制函数，以指定的次数重复字符表达式

9）【RIGHT】右子串函数，返回字符串中从右边开始指定个数的字符

10）【RTRIM】删除尾随空格函数，删除所有尾随空格后返回一个字符串

11）【SPACE】空格函数，返回由重复的空格组成的字符串

12）【STR】数字向字符转换函数，返回由数字数据转换来的字符数据

13）【SUBSTRING】子串函数，返回字符表达式、二进制表达式、文本表达式或图像表达式的一部分

14）【UPPER】大写函数，返回小写字符数据转换为大写的字符表达式

15）【CHARINDEX】返回字符串中某个指定的子串出现的开始位置

CHARINDEX(<'substring_expression'>,<expression>)

其中substring_expression是所要查找的字符表达式，expression可为字符串也可为列名表达式。如果没有发现子串，则返回0值。

此函数不能用于TEXT和IMAGE数据类型。

16）【PATINDEX】返回字符中某个指定的子串出现的开始位置。
PATINDEX(<'%substring_expression%'>,<column_name>)其中子串表达式前后必须有百分号“%”，否则返回值为0。

与CHARINDEX函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、VARCHART和TEXT数据类型。

19、字符串函数示例

declare @str1 nvarchar(20)
declare @str2 nvarchar(20)
declare @str3 nvarchar(20)
declare @str4 nvarchar(20)
declare @str5 nvarchar(20)

set @str1 = '北京分公司经理'
set @str2 = '天津分公司经理'
set @str3 = '上海分公司经理'
set @str4 = '北京分公司财务经理'
set @str5 = '上海分公司财务经理'

print RIGHT(@str5,LEN(@str5) - CHARINDEX('分公司',@str5) + 1)

 20、聚合函数

1）AVG 获取平均值

2）COUNT 获取数量

3）MAX 获取最大值

4）MIN 获取最小值

5）SUM 获取和值

21、日期和时间函数

1）DATEADD 返回给指定日期加上一个时间间隔后的新的DateTime值。

2）DATEDIFF 返回跨两个指定日期的日期边界数和时间边界数。

3）DATENAME 返回表示指定日期的指定日期部分的字符串。

4）DATEPART 返回表示指定日期的指定日期部分的整数。

5）DAY 返回一个整数，表示指定日期的天DATEPART部分。

6）GETDATE 以DateTime值的SQL SERVER 2008标准内部格式返回当前系统日期和时间。

7）GETUTCDATE 返回表示当前的UTC时间（通用协调时间或格林尼治标准时间）的DateTime值。当前的UTC时间得自当前的本地时间和运行Microsoft Sql Server 2008实例的计算机操作系统中的时区设置。

8）MONTH 返回表示指定日期的“月”部分的整数。

9）YEAR 返回表示指定日期的年份的整数。

declare @date DateTime

set @date = GETDATE()
--set @date = GETUTCDATE()

SELECT @date,YEAR(@date) as 年份,MONTH(@date) as 月份,DAY(@date) as 日,DATEPART(year,@date) as 年份,DATEPART(MONTH,@date) as 月份, DATEPART(DAY,@date) as 日, DATEPART(HH,@date) as 小时,DATEPART(MM,@date) as 分钟,DATEPART(SS,@date) as 秒钟,DATEADD(dd,1,@date) as 明天此时

22、标量值函数

语法格式：

CREATE FUNCTION function_name

([{@parameter_name scalar_parameter_data_type[=default]}[,...n]])

RETURNS scalar_return date_type

[WITH ENCRYPTION]

[AS]

BEGIN

function_body

RETURN scalar_expression

END

--获取指定用户的平均消费数据
CREATE FUNCTION GETSUMAmount(@userId int)
Returns decimal(18,2)
AS
BEGIN
    declare @result decimal(18,2)
    SELECT @result = SUM(Amount) from Profitloss where UserId=@userId
    return @result
END
GO

SELECT [dbo].GETSUMAmount(197)

23、表值函数

语法格式：

CREATE FUNCTION function_name

([{@parameter_name scalar_parameter_data_type [=default]}[,...n]])

RETURNS TABLE

[WITH ENCRYPTION]

[AS]

RETURN (select_statement)

CREATE FUNCTION GETALLPLAYTYPE(@lotteryId int)
RETURNS TABLE
AS
RETURN
(
    select a.LotteryName,b.ModeName,c.* 
    from
    Lottery a,PlayMode b,PlayType c    
    where c.lotteryId = @lotteryId and c.lotteryId = a.lotteryId and c.modeId = b.modeId
)
GO

select * from GETALLPLAYTYPE(1)

 

24、系统与元数据函数

1）CONVERT() 将一种数据类型的数据转变为另一种数据类型的数据

2）CURRENT_USER 返回当前用户的名称

3）ISDATE() 判断它的输入是不是一个有效的日期

4）ISNULL() 用一个指定替换值替换任何空值

5）ISNUMERIC() 判断它的输入是不是一个数值。

declare @str varchar(10),@i int,@j int

set @str = '12'
set @i = 10

if ISNUMERIC(@str) = 1
begin
    print convert(int,@str) * @i
end

print ISNULL(@j,10) *@i

25、事务

在SQL Server 2008系统中主要使用下列4条语句管理事务：

1）BEGIN TRANSACTION

2）COMMIT TRANSACTION

3）ROLLBACK TRANSACTION

4）SAVE TRANSACTION

26、SQL Server 2008 事务模式

事务模式

1）自动提交事务

每条单独的语句都是一个事务（默认，如Insert / update / delete / select）

2）显示事务

每个事务均以BEGIN TRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束

3）隐式事务

在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显示完成。如：触发器

4）批处理级事务

只能应用于多个活动结果集（MARS），在MARS会话中启动的Transact-SQL显式或隐式事务变为批处理级事务。当批处理完成时没有提交或回滚的批处理级事务自动由SQL Server进行回滚。

27、XACT_ABORT选项

XACT_ABORT选项

1）XACT_ABORT选项用于指定当SQL语句出现运行时错误时，SQL Server是否自动回滚到当前事务。其语法格式如下所示：

SET XACT_ABORT {ON|OFF}

2）当SET XACT_ABORT为ON时，如果执行SQL语句产生运行时错误，则整个事务将终止并回滚。当SET XACT_ABORT为OFF时，有时只回滚产生错误的SQL语句，而事务将继续进行处理。如果错误很严重，那么即使SET XACT_ABORT为OFF,也可能回滚整个事务。

3）SET XACT_ABORT的设置是执行或运行时设置，而不是分析时设置。对于大多数OLEDB提供程序，必须将隐式或显示事务中的数据修改语句中的XACT_ABORT设置为ON。唯一不需要该选项的情况是在提供程序支持嵌套事务时。

28、嵌套事务 （略）

29、锁

概述

所谓封锁，就是一个事务可向系统提出请求，对被操作的数据加锁（Lock）。其他事务必须等到此事务解锁（Unlock）之后才能访问该数据。从而，在多个用户并发访问数据库时，确保不互相干扰。可锁的单位是：行、页、表、盘区和数据库。

锁的类型

1）共享（S）锁：用于读操作。

多个事务可封锁一个共享单位的数据。任何事务都不能修改加S锁的数据。通常是加S锁的数据被读取完毕，S锁立即被释放。

2）独占（X）锁：用于写操作。

仅允许一个事务封锁此共享数据。其他任何事务必须等到X锁被释放才能对该数据进行访问。X锁一直到事务结束才能被释放。

3）更新（U）锁

用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或锁。当被读取数据页将要被更新时，则升级为X锁。U锁一直到事务结束才能被释放。

30、死锁

死锁

1）所谓死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去，此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

2）由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象：死锁。

3）一种情形，此时执行程序中两个或多个线程发生永久堵塞（等待），每个线程都在等待被其他线程占用并堵塞了的资源，例如，如果线程A锁住了记录1，并等待记录2，而线程B锁住了记录2并等待记录1，这样两个线程就发生了死锁现象。

必要条件：

1）互斥条件：一个资源每次只能被一个进程使用

2）请求与保持条件；一个进程因请求资源而阻塞时，对已获得的资源保持不放。

3）不剥夺条件：进程已经获得的资源，在未使用完之前，不能强制剥夺。

4）循环等待条件：若干线程之间形成一种头尾相接循环等待资源关系。

这四个条件是死锁的必要条件，只要系统发生死锁， 这些条件必须成立，而只要上述条件之一不满足，就不会发生死锁。

死锁的解除和预防

1）按同一顺序访问对象

2）避免事务中的用户交互

3）保持事务的简短性并在一个批处理中

4）使用低隔离级别

5）使用绑定连接

死锁的建议

1）对于频繁使用的表使用集簇化的索引。

2）设法避免一次性影响大量记录的T-SQL语句，特别是INSERT和UPDATE语句。

3）设法让UPDTAE和DELETE语句使用索引

4）使用嵌套事务时，避免提交和回退冲突

5）对一些数据不需要及时读取更新值的表在写SQL的时候在表名称后面加上(nolock) ，如：Select * from T1(nolock)

31、游标概述

1）游标（Cursor）是一种数据访问机制，它允许用户访问单独的数据行，而不是对整个行集进行操作。用户可以通过单独处理每一行逐条收集信息并对数据逐行进行操作，这样可以降低系统开销和潜在的阻隔情况。用户也可以使用这些数据生成SQL代码并立即执行或输出。

2）游标主要包括以下两部分：

a）游标结果集：由定义游标的SELECT语句返回的行的集合。

b）游标位置：指向这个结果集中的某一行的指针。

游标的特点

1）游标返回一个完整的结果集，但允许程序设计语言只调用集合中的一行。

2）允许定位在结果集的特定行。

3）从结果集的当前位置检索一行或多行。

4）支持对结果集中当前位置的行进行数据修改。

5）可以为其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。

5）提供脚本、存储过程和触发器中使用的访问结果集中数据库的T-SQL语句。

32、游标的声明

语法格式

DECLARE cursor_name [INSENSITIVE][SCROLL] CURSOR

FOR select_statement

[OFR{READ ONLY|UPDATE[OF column_name[,...n]]}]

--声明游标
declare category_cursor SCROLL Cursor
For
SELECT * FROM CATEGORY 
for read only | update

33、使用游标

1）打开游标：OPEN{{[GLOBAL] cursor_name}|cursor_variable_name}

2）检索游标：

FETCH

[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]

FORM ]

{{[GOBAL] cursor_name}|@cursor_variable_name}

[INTO @variable_name[,...n]]

3）关闭游标：CLOSE{{[GLOBAL] cursor_name}|cursor_variable_name}

4）释放游标：DEALLOCATE{{[GLOBAL] cursor_name}|@cursor_variable_name}

--声明游标
declare category_cursor SCROLL Cursor
For
SELECT CatName FROM CATEGORY 
for read only

declare @CatName nvarchar(20)

--打开游标
--OPEN category_cursor
FETCH (NEXT|FIRST|PRIOR|LAST|ABSOLUTE 5) from category_cursor INTO @CatName

Print @CatName

--关闭游标
CLOSE category_cursor

--释放游标
DEALLOCATE category_cursor

34、判断游标提取状态

@@FETCH_STATUS

取值：

1）0 FETCH语句成功

2）-1 FETCH语句失败或行不在结果集中

3）-2 提取的行不存在

if @@FETCH_STATUS = 0
Print '数据提取成功'
else if @@FETCH_STATUS = -1
Print '语句失败或行不在结果集中'
else if @@FETCH_STATUS = -2
Print '提取的行不存在'

35、游标应用示例（略）

--游标使用示例

--声明游标
DECLARE stu_cursor scroll cursor for select 学号 from 学生信息

--创建临时表
create table #t(name nvarchar(20),sorce int)

--打开游标
open stu_cursor

--定义一个变量用于存储查询到的学号
DECLARE @NAME varhcar(20)

--读取下一条记录，并把结果存储到已声明的@NAME变量中
FETCH next from stu_cursor INTO @NAME

--判断全局变量（游标状态）
while @@FETCH_STATUS = 0
BEGIN
--把查询到的记录插入到临时表中
insert into #t 
select B.姓名,AVG(分数) as 分数 FROM 成绩信息 A JOIN 学生信息 B ON A.学生编号=B.学号 
where A.考试编号='0801' AND B.学号=@NAME 
GROUP BY B.姓名
FETCH next FROM  stu_cursor
INTO @NAME
END

--关闭游标
CLOSE stu_cursor

--释放游标
DEALLOCATE stu_cursor