资源简介

(共49张PPT)
第10章 SQL Server 2016高级开发
本章主要介绍SQL Server 2016的高级应用，包括用户自定义函数、实现交叉表查询、事务处理以及锁。通过本章的学习，读者可以创建和管理用户自定义函数，可以使用PIVOT、UNPIVOT以及CASE实现交叉表查询，并了解事务处理机制和锁，应用事务和锁优化对数据的访问。
10.1　用户自定义函数
10.2　使用SQL Server 2016实现交叉表查询
10.3　事务处理
10.4　锁
用户自定义数据类型
图4.1 自定义数据类型
在SQL Server 2016中，使用系统数据类型sp_addtype创建用户自定义数据类型。
语法如下：
sp_addtype[@typename=]type，
[@phystype=]system_data_type
[，[@nulltype=]'null_type']
[，[@owner=]'owner_name']
10.1　用户自定义函数
SQL Server 2016可以根据用户需要来自定义函数，以便用在允许使用系统函数的任何地方。
用户自定义函数有两种方法，一种是利用Microsoft SQL Server Manager管理器直接创建，另一种是利用代码创建。
10.1.1　创建用户自定义函数
10.1.2　使用Transact-SQL语言创建用户自定义函数
（1）创建自定义函数
利用Transact-SQL创建函数的语法如下：
create function 函数名（@parameter 变量类型 [,@parameter 变量类型 ]）
returns参数as
begin
命令行或程序块
End
函数可以有0个或若干个输入参数，但必须有返回值，returns后面就是设置函数的返回值类型。
用户自定义函数为标量值函数或表值函数。
如果returns子句指定了一种标量数据类型，则函数为标量值函数；如果returns子句指定TABLE，则函数为表值函数。
根据函数主体的定义方式，表值函数可分为内联函数和多语句函数。
（2）调用自定义函数
Transact-SQL调用函数的语法格式如下：
Print dbo.函数（[实参]）
或
select dbo.函数（[实参]）
dbo是系统自带的一个公共用户名。
10.1.3　修改、删除用户自定义函数
1．修改自定义函数
利用Transact-SQL修改函数的语法如下：
alter function 函数名（@parameter 变量类型 [,@parameter 变量类型 ]）
returns参数as
begin
命令行或程序块
End
2．删除自定义函数
删除自定义函数的Transact-SQL语法如下：
Drop function 函数名
10.2　使用SQL Server 2016实现交叉表查询
10.2.1　使用PIVOT和UNPIVOT实现交叉表查询
PIVOT和UNPIVOT运算符是SQL Server 2016新增的功能。
通过PIVOT和UNPIVOT就完全可以实现交叉表的查询，用PIVOT和UNPIVOT编写更简单，更易于理解。
在查询的FROM子句中使用PIVOT和UNPIVOT，可以对一个输入表值表达式执行某种操作，以获得另一种形式的表。
PIVOT运算符将输入表的行旋转为列，并能同时对行执行聚合运算。
而UNPIVOT运算符则执行与PIVOT运算符相反的操作，它将输入表的列旋转为行。
PIVOT和UNPIVOT的语法如下：
[ FROM { ＜table_source＞ } [ ,...n ] ]
＜table_source＞ ::= {
　table_or_view_name [ [ AS ] table_alias ]
　＜pivoted_table＞ | ＜unpivoted_table＞ }
＜pivoted_table＞ ::=table_source PIVOT ＜pivot_clause＞ table_alias
＜pivot_clause＞ ::=( aggregate_function ( value_column )
　FOR pivot_column
　　IN ( ＜column_list＞
＜unpivoted_table＞ :: = table_source UNPIVOT ＜unpivot_clause＞ table_alias
＜unpivot_clause＞ :: = ( value_column FOR pivot_column IN ( ＜column_list＞
＜column_list＞ :: = column_name [ , ... ] table_source PIVOT ＜pivot_clause＞
1．用PIVOT举例
【例10-2】 使用PIVOT运算符实现交叉表查询。
SQL语句如下：
use student
select * from sp pivot(sum(销售数量) for 商品名称 in([李小葱专辑],[周木人专辑],[国产E601],[920演唱会DVD] )) as 统计
2．用UNPIVOT举例
UNPIVOT是PIVOT的逆操作。
【例10-3】 使用UNPIVOT运算符实现交叉表查询。
SQL语句如下：
use student
select * from temp1 unpivot(销售数量 for 商品名称 in([李小葱专辑],[周木人专辑],[国产E601],[920演唱会DVD] )) as a
10.2.2 CASE实现交叉表查询
利用CASE语句可以返回多个可能结果的表达式。
CASE具有简单CASE和CASE查询两种函数格式。
下面介绍简单CASE语句的语法。
简单CASE语句：将某个表达式与一组简单表达式进行比较以确定结果。
其语法形式如下：
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END
【例10-4】 使用CASE语句实现交叉表查询。
SQL语句如下：
use student
SELECT 月份,SUM(CASE 商品名称 WHEN '李小葱专辑' THEN 销售数量 ELSE NULL END)AS [李小葱专辑],SUM(CASE 商品名称 WHEN '周木人专辑' THEN 销售数量 ELSE NULL END)as [周木人专辑] ,SUM(CASE 商品名称 WHEN '国产E601' THEN 销售数量 ELSE NULL END)AS [E601],SUM(CASE 商品名称 WHEN '920演唱会DVD' THEN 销售数量 ELSE NULL END)AS [920演唱会DVD] FROM sp group by 月份
10.3　事 务 处 理
10.3.1　事务简介
1．事务概念
事务（Transaction）的作用是保证在对数据操作的过程中保证数据的完整性，不出现数据操作完成一半的未完成现象。
事务作为一个逻辑单元，它必须具备以下4个属性。
（1）原子性（Atomicity）
（2）一致性（Consistency）
（3）隔离性（Isolation）
（4）持久性（Durability）
2．事务类型
根据系统的设置，可以把事务分成两种类型。
一种是系统提供的事务，另一种是用户定义的事务。
系统提供的事务是指在执行某些语句时，一条语句就是一个事务。
这时要知道，一条语句的对象既可能是表中的一行数据，也可能是表中的多行数据，甚至是表中的全部数据。
因此，只有一条语句构成的事务也可能包含了对多行数据的处理。
事务运行的3种模式如下。
（1）自动提交事务
（2）显式事务
（3）隐性事务
10.3.2　事务处理
1．事务的起点
事务以BEGIN TRANSACTION语句开始。
BEGIN TRANSACTION语句使全局变量@@TRANCOUNT按1递增。
语法如下：
BEGIN { TRAN | TRANSACTION }
[ { transaction_name | @tran_name_variable }
[ WITH MARK [ 'description' ] ]
]
[ ; ]
2．事务的终点
事务以COMMIT TRANSACTION作为隐性事务或显式事务成功结束的标志。
语法如下：
COMMIT { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ]
[ ; ]
3．数据回滚
使用ROLLBACK TRANSACTION语句将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。
语法如下：
ROLLBACK { TRAN | TRANSACTION }
[ transaction_name | @tran_name_variable
| savepoint_name | @savepoint_variable ]
[ ; ]
4．事务保存点
使用SAVE TRANSACTION语句在事务内设置保存点。
语法如下：
SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }
[ ; ]
10.4 锁
10.4.1 锁简介
1．锁的概念
锁是保护事务和数据的方式，这种保护方式类似于日常生活中使用的锁。
锁是防止其他事务访问指定资源的手段，是实现并发控制的主要方法，是多个用户能够同时操纵同一个数据库中的数据而不发生数据不一致现象的重要保障。
在Microsoft SQL Server中可以锁定的资源有多种，这些可以锁定的资源分别是行、页、Extent、表和数据库，他们对应的锁分别是行级锁、页级锁、Extent级锁、表级锁和数据库级锁。
数据行存放在页上，页存放在Extent上，一个表有若干个Extent组成，而若干个表组成了数据库。
在这些可以锁定的资源中，最基本的资源是行、页和表，而Extent和数据库是特殊的可以锁定的资源。
2．锁的类型
（1）共享锁
（2）更新锁
【例10-5】 使用更新锁。
begin tran
save tran aaa
select * from table_1 with (UPDLOCK)
rollback tran aaa
commit tran
（3）排它锁
【例10-6】 使用排它锁。
begin tran
save tran aaa
select * from table_1 with (tablockx xlock)
rollback tran aaa
commit tran
（4）意向锁
（5）架构锁
（6）大容量更新锁
（7）键范围锁
10.4.2　死锁的产生机制
当多个用户同时访问数据库的同一资源时，叫做并发访问。
如果并发访问中有用户对数据进行修改，很可能就会对其他访问同一资源的用户产生不利影响。
可能产生的并发不利影响有：脏读、不可重复读和幻读。
为了避免并发访问产生的不利影响，SQL Server设计有两种并发访问的控制机制：锁和行版本控制。
1．脏读（dirty read）
2. 不可重复读（nonrepeatable read）
3．幻读（phantom read）
小　　结
本章介绍了关于SQL Server 2016的高级应用，如：用户自定义函数、交叉表查询、事务和锁。
读者通过创建用户自定义函数可以实现将代码封装在一个函数体内方便调用；可以使用PIVOT、UNPIVOT运算符以及CASE语句实现交叉表查询；应用事务处理保证数据完整性；使用锁保护数据，并掌握排除死锁的方法。

展开更多......

收起↑