第五部分：高级查询
一、select 基本语法
SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
1、distinct：消除重复记录
示例：
--汽车月销售记录，当某月有销售记录多次时，我们只需要一条记录即可时：
select distinct sale_name , sale_month
from tbl_for_distinct
where sale_name='东风'
2、 select into
• SELECT INTO 语句创建一个新表，并用 SELECT 的结果集填充该表
• 注意事项
– Into 后的表会自动创建
• Where
– 用新表记录中间查询结果
示例：
select * into tb_new from tb_old
本示例创建一个基于现存表tb_old 的新表，称为tb_new。然后用tb_old表中的内容填充新表。
3、compute
• 为聚合函数生成汇总值
– 该汇总值作为附加行显示在结果集中。
• 示例
select *
from tbl_for_distinct
compute sum(sale_amount)
二、多表查询类型
n 联合：合并多个数据表中的行
n 联接：合并多个数据表中的列
n 子查询：将一个查询包含到另一个查询中
1、联合：Union
（1）Union 操作符：将两个或更多个 SELECT 语句的结果合并为一个结果集。
• 语法：
• select 语句 union[all] select 语句
• 使用 ALL 子句表示不删除重复的行。
（2）Union注意事项
• 每个Select必须具有相同的结构
示例1：
SELECT empno, ename, sal FROM emp
UNION
SELECT empno, ename, sal FROM ret_emp
示例2：查询校全体师生编号、姓名
select stu_id as 编号,stu_name as 姓名
from tbl_for_union_stu
union all
select tea_id as 编号,tea_name as 姓名
from tbl_for_union_tea
2、子查询
子查询：一个 SELECT 语句嵌套在另一个 SELECT 语句中。
父查询
Select <Column Name> From Table
WHERE <Column Name> ＝
Select <Column Name> From <Table> WHERE <Column> = <Criteria >
操作符
子查询
（1）In(not in)：确定给定的值是否在子查询的范围内
示例1：查询23岁或25岁或31岁的销售员的信息
select * from tbl_advqry_seller
where seller_age in(23,25,31)
示例2：查询有卖出商品的销售员的所有信息
select * from tbl_advqry_seller
where seller_name
in(select sale_master_person from tbl_advqry_sale_master)
（2）Exists(not exists)：指定一个子查询，检测行的存在。
相录于进行一次存在测试，此时，子查询实际上不产生任何数据，它只是返回TRUE或FALSE值。
示例1：查询有卖出商品的销售员的所有信息
select * from tbl_advqry_seller（销售员表）
where exists
(select *
from tbl_advqry_sale_master（销售表）
where sale_master_person=tbl_advqry_seller.seller_name )
（3）子查询的分类
• 嵌套子查询
– 先执行子(内)查询，再执行父(外)查询
• 相关子查询
– 对于父(外)查询中的每一条记录,都会执行一次子(内)查询
（1） 区别嵌套查询与相关子查询
区别1-形式
子(内)查询中包含父(外)查询中的字段
区别2-本质
子(内)查询依赖父(外)查询中的数据
（2） 使用比较运算符的子查询
子查询可由一个比较运算符（=、< >、>、> =、<、!>, ! < 或 < =）引入。
示例：查询高于平均年龄的销售员的信息
select * from tbl_advqry_seller
where seller_age > (
select avg(seller_age) from tbl_advqry_seller)
（3） any(some),all
• any(some):比较结果集中的任意一个
• 示例：查询比其中一名男性大的女性员工的信息
select * from tbl_advqry_seller
where seller_sex = 0 and seller_age>any(
select seller_age from tbl_advqry_seller where seller_sex = 1 )
• All:比较结果中的所有
• 示例：查询比所有男性都大的女性员工的信息
select * from tbl_advqry_seller
where seller_sex = 0 and seller_age>all(
select seller_age from tbl_advqry_seller where seller_sex = 1 )
（7）UPDATE、DELETE 和 INSERT中使用子查询
示例：有卖出商品的销售员的工资加200
update tbl_advqry_seller
set seller_sal= seller_sal + 200
where seller_name in
(select sale_master_person from tbl_advqry_sale_master)
（8）子查询中使用别名
示例：
Select au1.au_lname, au1.au_fname, au1.city
From authors AS au1
Where au1.city in
(select au2.city FORM authors as au2
where au2.au_fname=’Tom’ and au2.au_lname=’karsen’)
第六部分：常规索引管理
一、SQL Server索引
1、索引
对数据表中一个或多个字段进行排序后，存储对应的记录实际物理存储位置的数据结构
2、索引的作用
Ø SQL语句中，使用建立了索引的列作为查询条件时，通过二分法大大加快查询效率（针对小规模数据）
Ø 加速GROUP BY的执行速度
Ø UNIQUE Index强制执行唯一性约束
3、索引的适用范围
Ø 作为查询条件的列
Ø 频繁按照范围查询的列
Ø 表连接中频繁使用的列
4、索引的优点和缺点
Ø 优点
² 加快访问速度
² 加强行的唯一性
Ø 缺点
² 带索引的表在数据库中需要更多的存储空间。
² 操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新。
5、创建索引的指导原则
Ø 适合创建索引情况
n 该列频繁用于进行搜索
n 该列用于对数据进行排序
Ø 不适合创建索引情况(表较小)
n 表中的记录数较少
n 表中仅包含几行
n 列中仅包含几个不同的值
二、SQL Server索引分类
1、按字段个数
Ø 单值索引
Ø 复合索引
2、按生成方式
Ø 自动索引(Primary Key,Unique Key)
Ø 手动索引
3、按索引值的可重复性
Ø 唯一性索引
Ø 不唯一索引
4、按对表记录位置的影响
Ø 聚集索引
Ø 非聚集索引
三、SQL Server索引生成原则
Ø 一个表只可以有一个聚集索引，可以有多个非聚集索引，最多249个
Ø 复合索引最多包含16个字段，不可以包含TEXT，NTEXT，IMAGE大类型字段
Ø 基数(=Count(Distinct 字段))大的字段适合建立索引
Ø 小表只适合建立主键索引
Ø 索引字段的长度<=900B
Ø 对Text,Ntext,Image类大字段可以使用全文索引
四、复合索引使用特征
1、复合索引
• 顺序优先:第一个索引字段必须出现在Where字句的第一个条件中。
• 例：Student
• Code
• Fname
• Lname
• Sex
• …
Ø 不使用索引：Select * From Student Where Lname=‘中华’
Ø 使用索引：Select * From Student Where FName=‘张’ And Lname=‘中华’
Ø 使用索引：Select * From Student Where FName=‘张’
五、SQL语句的优化方案
1、SQL构造法
• 通过构造对结果无影响的条件子句充分使用复合索引
Ø 不使用索引：Select * From Student Where Lname=‘中华’
Ø 使用索引：Select * From Student Where FName Like ‘%’ And Lname=‘中华’
Ø 使用索引：Select * From Student Where FName Is Not Null And Lname=‘中华’
六、索引的存储特性
1、顺序存储
• 需要在存放Index的页中设置必要的空闲空间，防止Index记录顺序插入时产生行迁移。
•1
•3
•4
•6
•7
•9
•10
•11
•13
•14
•20
•22
2
•1
•2
•3
•4
•6
•7
•9
•10
•11
•13
•14
•20
七、索引的创建与管事
1、创建索引
Create Unique Clustered/NonClustered Index 索引名
On 表名（字段集 Asc/Desc)
With
FillFactor=N
Drop_Existing
Statistic_NoRecompute
Sort_In_TempDb
On 文件组
注意：
Ø FillFactor=0或100，表示索引页全部填充
Ø 主键索引默认为聚集索引
Ø 索引的命名：Ix_表名_字段名
Ø 一个字段可以创建多个索引
Ø 索引不可以修改，只可以删除后重建
Ø 自动索引的优先级高于手工索引
2、 删除索引
• Drop Index 表.索引
Ø 自动索引无法通过DROP删除
Ø 如果表中同时有聚集索引和非聚集索引，应先删非聚集索引，后删除聚集索引
3、重建索引
DBCC DbReIndex(表名，索引名，填充因子）
With No_InfomSgs
Ø 适合在索引页填充过满的时候重建
第六部分习题样例
1、 创建具有关系的表并进行合理的数据规划
创建城市表：
城市编码 5
城市名称 20
邮政编码： 6
create table city
(
city_ID varchar(5),
city_name varchar(20),
city_dak varchar(6)
)
创建学生表：
学生编码 固定13位
学生姓名 不为空，不重复
学生性别 F/M
学生年龄 15－25
学生生日
学生籍贯
学生国籍
学生照片
create table student
(
stu_ID char(13) constraint PK_student_stuID primary key(stu_ID),
stu_name varchar(8) not null,
stu_sex char(1) check(stu_sex in ('F','M')),
stu_age tinyint,
stu_birth smallint,
stu_place varchar(10),
stu_country varchar(10),
stu_image image
)
需求：
--1、应用系统中经常对城市编码和学生姓名进行模糊查询，请优化
--城市编码应用主键自动产生的聚集索引进行优化
alter table city
add constraint PK_city_code primary key (city_id)
--学生姓名字段创建非聚集索引以提高查询效率
create index ix_student_queryname
on student(stu_name)
with
fillfactor=70,
statistics_norecompute,
sort_in_tempdb
on group_index
drop index student.ix_student_queryname
--2、教学主管经常需要查询一下表格：
--学生编码 学生名称 学生性别 籍贯 邮政编码
--请设计相关视图像并对查询速度进行优化；
drop index vw_query_by_charge
create index ix_student_place
on student(stu_place)
with
fillfactor=70
on group_index
create view vw_query_by_charge with encryption as
select A.stu_ID,A.stu_name,A.stu_sex,B.city_name,B.city_dak
from student A,city B
where A.stu_place=B.city_ID
--3、应用程序中经常需要对学生的“姓”进行筛选（使用函数SUBSTRING），请对该查询语句进行优化；
alter table student
add stu_Fname as substring(stu_name,1,2)
create index ix_student_Fname on student(stu_Fname)
with
fillfactor=70,
statistics_norecompute
on group_index
第七部分：全文索引管理
一、SQL Server全文索引
1、工作原理
应用程序
SQL Server
DB数据文件
Microsoft Search
Service
全文索引
常规索引
PK
2、全文索引的特征
Ø 全文索引不是SQL SERVER的内部功能,其数据不是存放在SQL SERVER的数据文件中,而是在操作系统中单独使用目录存储
Ø 常规索引中存放的是索引值和记录所在的物理位置,而全文索引中存放的是索引值和主键值,最后进行记录定位的时候是按主键定位的
Ø 常规索引可以优化文本型数据,而全文索引可以索引VARCHAR、Char、Text、Image等类型.
二、SQL Server全文目录
1、全文目录
• Exec Sp_FullText_Catalog ‘名称’,‘动作’,‘目录’
• 动作：
² Create
² Drop
² Start_Incremental
² Start_Full
² Stop
² Rebuild
例：Exec Sp_FullText_Catalog ‘My_T’,‘Create’,‘C:"T15’
注意：
Ø 一个数据库可以包含多个全文目录
Ø 一个全文目录可以存放多个全文索引
Ø 一个全文索引必须存放在一个全文目录中
Ø 一个表只能有一个全文索引
Ø 如果表的记录很多，而且对应的全文目中包含较多的列，则建议单独使用全文目录存放，以提高访问效率。
2、全文目录的查询
Exec Sp_Help_FullText_Catalogs 目录名
3、全文目录的禁用
Exec Sp_Help_FullText_Catalogs ‘动作’
动作：
² Enable – 在当前数据库启用全文索引
² Disable – 在当前数据库禁用全文索引
(注意：Disable时，全文目录的物理目录会自动被删除）
三、SQL Server全文索引
1、全文索引
Exec Sp_FullText_Table ‘表名’,‘动作’,‘目录’,‘PK约束’
• 动作：
² Create
² Drop
² Activate
² Start_Change_Tracking
² Stop_Change_Tracking
² Start_Background_UpdateIndex
² Stop_Background_UpdateIndex
² Update_Index
² Start_Full
² Start_Incremental
•
2、全文索引的查询
Exec Sp_Help_FullText_Table 全文索引, 表名
例：Exec Sp_Help_FullText_Table My_T15_01,Student
3、全文索引的创建
Exec Sp_Help_FullText_Table ‘Student’,’Create’,’My_T15_01’,’Pk_Stu_Code’
注意：创建好的全文索引中并不包含任何字段，只是一个没有填充的空的索引，需要添加字段后填充
4、全文索引中添加字段
• Exec Sp_FullText_Column ‘表名’,‘字段名’,‘动作’
• 动作：
² Add
² Drop
例：Exec Sp_Help_FullText_Column ‘Student’,’Stu_Remark’,’Add’
5、全文索引字段的查询
Exec Sp_Help_FullText_Column ‘表名’, ‘字段名’
例：Exec Sp_Help_FullText_Column ‘Student’,’Stu_Remark’
6、全文索引填充的特点
Ø 全文索引创建完成后必须进行一次完全填充（如果使用增量填充，系统依旧自动进行完全填充）为提高日常填充速度，建议使用增量填充（必要时可以使用调度程度来自动控制）
Ø 如果希望对局部时段数据进行填充，可以使用“修改跟踪”开始跟踪某时间点开始的各种动作，后将更改的结果写入全文索引中，以达到精确控制填充时间的效果
增量填充
跟踪修改
跟踪修改填充
完全填充
四、全文索引的查询
1、 全文索引查询子句
Ø Containts(‘字段’,‘比对字串’) -- 整句查询
Ø FreeText(‘字段’,‘比对字串’) -- 单词查询
示例：
Ø SQL Server将顺序查询
Select * from Student Where Stu_Remark Like ‘%AB%‘
Ø 只有完全包含’Is My Home’的记录被选择
Select * from Student Where Containts(‘Stu_Remark’,’ Is My Home’)
Ø 只要包含’Is’、’My’、’Home’中任何字符的记录被选择出来
Select * from Student Where FreeText(‘Stu_Remark’,’ Is My Home’)
第七部分习题样例
create table city
(
city_ID varchar(5),
city_name varchar(20),
city_dak varchar(6)
)
create table studentmemo
(
sut_ID char(13) constraint FK_stu_memo references student(stu_ID)
constraint PK_stu_id primary key,
stu_memo text
)
foreign key stu_memo(stu_ID)
drop table stu_memo
alter table stu_memo
drop constraint PK_stu_memo
alter table stu_memo
add constraint FK_stu_memo foreign key stu_memo(stu_ID) references student(stu_ID)
drop table stu_memo
create table student
(
stu_ID char(13) constraint PK_student_stuID primary key(stu_ID),
stu_name varchar(8) not null,
stu_sex char(1) check(stu_sex in ('F','M')),
stu_age tinyint,
stu_birth smallint,
stu_place varchar(10),
stu_country varchar(10),
stu_image image
)
--根据实际需要创建相应的学生表并进行数据规划后：
--1、激活存放该表的数据库，允许使用全文索引
EXEC sp_fulltext_database 'Enable'
--2、为数据库创建两个全文目录，一个为F_Normal，一个为F_Big，
--用来保存学生编码，学生姓名和学生备注的全文索引；
EXEC sp_fulltext_catalog 'F_Normal','create','d:"yy'
EXEC sp_fulltext_catalog 'F_Big','create','d:"yy'
--3、 为学生编码，学生姓名，学生备注创建全文索引（需要进行分类规划）；
EXEC sp_fulltext_table 'student','create','F_Normal','PK_student_stuID'
EXEC sp_fulltext_column 'student','stu_ID','add'
EXEC sp_fulltext_column 'student','stu_name','add'
EXEC sp_fulltext_table 'stu_memo','create','F_Big','PK_stu_id'
EXEC sp_fulltext_column 'stu_memo','studentmemo','add'
--4、对学生备注所再的全文索引进行完全填充；
EXEC sp_fulltext_table 'studentmemo','start_full'
--5、为学生编码、学生姓名所在的全文索引进行增量填充；
EXEC sp_fulltext_table 'student','start_full'
--6、为学生备注所在的全文索引进行时段跟踪并用前台方式填充；
EXEC sp_fulltext_table 'studentmemo','start_change_Tracking'
EXEC sp_fulltext_table 'studentmemo','stop_change_Tracking'
EXEC sp_fulltext_table 'studentmemo','update_index'
--7、对全文目录F_Normal进行填充；
EXEC sp_fulltext_catalog 'F_Normal','start_full'
--8、在全文目录中查询学生备注中有关“热爱计算机”字串的有关记录；
select * from studentmemo where contains('stu_memo','热爱计算机')
--9、在全文目录中查询学生备注中有关学习过JAVA、 C++、 VB的所有人员资料；
select * from student where freetext('stu_memo','JAVA C++ VB')
--10、删除有关学生编码的全文索引;
EXEC sp_fulltext_column 'student','stu_id','drop'
--11、删除全文目录F_Normal;
EXEC sp_fulltext_catalog 'F_Normal','drop'
第八部分：数据库的备份和恢复
一、SQL Server备份设备
1、设备分类
Ø 物理设备
² 物理设备是操作系统中实际的目录和文件
Ø 逻辑设备
² 逻辑设备是SQL Server中的虚拟设备，和某一物理设备一一对应，以保证用户和服务器目录结构的逻辑分离
² 逻辑设备的定义被永久的保存在Master的SysDevices表中
2、设备的管理
Ø 创建设备
Exec Sp_AddumpDevice ‘设备类型’,‘逻辑名’，‘物理文件’
其中：设备类型 – ‘Disk’：磁盘文件
‘Tape’：磁带机
Ø 删除设备
Exec Sp_DropDevice ‘逻辑名’,‘DelFile’
其中：DelFile为可选参数，添加该参数后，删除逻辑设备时候将自动删除对应的数据文件
Ø 查看设备信息
Exec Sp_HelpDevice ‘逻辑名’
3、设备的管理(示例）
Ø 添加
Exec Sp_AddumpDevice ‘Disk’,’Dev_T15’,’D:"T15_Data"T15_Backup.bak’
Ø 删除
Exec Sp_DropDevice ’Dev_T15’,’DelFile’
Ø 查询
Exec Sp_HelpDevice ’Dev_T15’
Ø 查询一个备份设备里的详细信息，包括多次的备份明细
Load HeaderOnly From ‘逻辑名’
二、SQL Server备份策略
1、设备策略
Ø 完全备份
Backup Database 库名 To Disk=‘文件名’
[设备名]
Ø 差异备份
Backup Database 库名 To 设备名 With Differential
Ø 文件和文件组备份
Backup Database 库名 File=‘逻辑文件名’ To 设备 FileGroup=‘文件组’
Ø 日志备份
Backup Log 库名 To 设备名 With No_Truncate
注意：日志备份后，系统将自动清除备份点以前的日志资料，如果使用With No_Truncate参数将不清除日志资料
2、 备份策略（演示）
Ø 完整：Backup Database Class_T15 To Dev_T15
Ø 差异：Backup Database Class_T15 To Dev_T15 With Differential
Ø 日志：Backup Log Class_T15 To Dev_T15 With No_Truncate
Ø 文件：Backup Database Class_T15 File=‘T15_Normal’ To Dev_T15
Ø 文件组：Backup Database Class_T15 FileGroup=‘G_Normal’ To Dev_T15
3、备份设备分析
通过备份，系统多次将数据库的数据备份到同一个数据文件中，数据文件分区域存储每次的备份资料，恢复时必须指定相应的备份区域。
查询备份设备区域信息：
Load HeaderOnly From ‘设备逻辑名’
BackupType:
• 1 – 数据库
• 2 – 事物日志
• 4 – 文件
• 5 – 差异数据库
• 6 – 文件组
三、SQL Server数据恢复
1、数据库恢复(对完全备份和差异备份）
Restore Database 库名 From 设备名
With File=N,
NoRecovery/Recovery,
Replace,
Restore
解析：
² With File=N – 指定设备中用于进行恢复的文件区域号
² Recovery - 是否回滚未完成的事务
² Replace - 恢复时是否创建新库或覆盖已有数据库
² Restore - 是否在上次恢复过程中意外中断的位置继续开始恢复数据
2、数据库恢复(对日志）
Restore Log 库名 From 设备名
With File=N,
NoRecovery/Recovery,
Restart,
Stopat=‘时间点（DateTime)’
• 解析：
² With File=N – 指定设备中用于进行恢复的文件区域号
² Recovery - 是否回滚未完成的事务
² Restore - 是否在上次恢复过程中意外中断的位置继续开始恢复数据
3、数据库恢复(对文件和文件组）
Restore Database 库名 File=‘文件名’ / FileGroup=‘组名’ From 设备名
With File=N,
NoRecovery/Recovery,
Replace
解析：
² With File=N – 指定设备中用于进行恢复的文件区域号
² Recovery - 是否回滚未完成的事务
² Replace - 恢复时是否创建新库或覆盖已有数据库
四、备份和恢复策略的配合
1、 备份的特点
Ø 完全备份是其他任何一种备份策略的基础，数据库必须进行一次完全填充（如果第一次使用增量备份，系统依旧自动进行完全备份）。
Ø 为提高日常备份速度，建议使用增量备份（必要时可以使用调度程度来自动控制）。
Ø 恢复时候，建议使用完全恢复。
Ø 经常对保存变化表的文件和文件组进行备份，保证变化数据的安全性。
完全备份
增量备份
文件/文件组备份
日志备份
第八部分习题样例
数据库备份和恢复练习：
--1、创建数据库文件，包含规划过的四个数据文件组；
--2、创建逻辑设备“MY_W16”；
EXEC sp_dropdevice W16Backup
EXEC sp_addumpdevice 'DISK','W16Backup','D:"yy"yy.bak'
EXEC sp_helpdevice W16Backup
--3、对数据库文件进行完全备份到设备中；
backup database w16 to W16Backup
--4、对数据库文件进行增量备份到设备中；
backup database w16 to W16Backup with differential
--5、备份变化表数据组到设备中；
backup database w16 FILEGroup='Group_change' to W16Backup
--6、备份日志到设备中，并删除日志信息；
backup log w16 to W16Backup
--7、对数据库进行完全恢复，使用问题3中所做的备份；
load headeronly from W16backup
restore database w16 from W16backup with file=3
--8、对数据库进行恢复，使用5中所使用的备份；
restore database database w16 from W16backup with file=5,Recovery
第九部分：数据库的安全管理
一、SQL Server安全体系
1、三级安全体系
Ø 访问安全性 -- 权限控制
Ø 逻辑安全性 -- 约束机制
Ø 物理安全性 -- 备份和恢复机制
2、SQL Server的身份认证方式
Ø Windows 身份认证
Ø SQL Server 身份认证
3、SQL Server的安全决策过程
应用系统
身份认证模式
Windows认证模式
Win身份是否合法
帐号是否合法
是否允许连接
帐号和密码有效性
SQL Serve连接成功
拒
绝
连
接
拒
绝
连
接
混合认证模式
二、SQL Server帐号的管理
1、创建帐号
Exec Sp_AddLogin ‘帐号’,‘密码’
2、删除帐号
Exec Sp_DropLogin ‘帐号’
3、查询帐号信息
Exec Sp_HelpLogins ‘帐号’
三、SQL Server角色的管理
拒绝连接
1、角色的分类
Ø 服务器角色
Ø 数据库角色
Ø 自定义角色
•
2、服务器角色
• SysAdmin
• SecurityAdmin
• ServerAdmin
• SetupAdmin
• ProcessAdmin
• DiskAdmin
• DbCreator
• BulkAdmin
固定服务器角色
描述
sysadmin
可以在 SQL Server 中执行任何活动。
serveradmin
可以设置服务器范围的配置选项，关闭服务器。
setupadmin
可以管理链接服务器和启动过程。
securityadmin
可以管理登录和 CREATE DATABASE 权限，还可以读取错误日志和更改密码。
processadmin
可以管理在 SQL Server 中运行的进程。
dbcreator
可以创建、更改和除去数据库。
diskadmin
可以管理磁盘文件。
bulkadmin
可以执行 BULK INSERT 语句。
diskadmin
可以管理磁盘文件。
3、数据库角色
• DB_Owner
• DB_AccessAdmin
• DB_SecurityAdmin
• DB_DDLAdmin
• DB_BackupAdmin
• DB_DataReader
• DB_DataWriter
• DB_DenyDataReader
• DB_DenyDataWriter
四、帐号和角色的绑定
1、帐号和服务器角色的绑定
Ø 绑定
Exec Sp_AddSrvRolemember ‘帐号’,‘服务器角色名’
Ø 取消绑定
Exec Sp_DropSrvRolemember ‘帐号’,‘服务器角色名’
2、帐号和数据库角色的绑定
Ø 绑定
Exec Sp_AddRolemember ‘帐号’,‘数据库角色名’
Ø 取消绑定
Exec Sp_DropRolemember ‘帐号’,‘数据库角色名’
五、自定义角色的管理（角色的创建和绑定）
Ø 创建
Exec Sp_AddRole ‘自定义角色名’
Ø 删除
Exec Sp_DropRole ‘自定义角色名’
Ø 绑定
Exec Sp_AddRoleMember ‘帐号’, ‘自定义角色名’
Ø 取消绑定
Exec Sp_DropRoleMember ‘帐号’, ‘自定义角色名’
Ø 查询角色成员信息
Exec Sp_HelpRoleMember ‘自定义角色名’
六、SQL Server权限的管理
1、连接权限
帐号连接到SQL Server上，并不能访问任何的数据库，必须授予数据库的连接权限
Ø 授予连接权限
Use 数据库名
Exec Sp_GrantDbAccess ‘帐号’
Ø 回收连接权限
Use 数据库名
Exec Sp_RevokeDbAccess ‘帐号’
2、 对象权限
Ø 对象分类：查询对象，执行对象
Ø Insert
Ø Update
Ø Delete
Ø Select
Ø Execute – 针对Procedure
Ø Reference
Ø All
3、 语句权限
Ø Create Database
Ø Create Table
Ø Create View
Ø Create Rule
Ø Create Procedure
Ø Create Function
Ø Create Default
Ø Backup Database
Ø Backup Log
4、对象权限的管理
Ø 帐号/角色授权
Grant 对象权限 On 对象名 To 帐号/角色
Ø 帐号/角色权限回收
Revoke 对象权限 On 对象名 From 帐号/角色
5、语句权限的管理
Ø 帐号/角色授权
Grant 语句权限 To 帐号/角色
Ø 帐号/角色权限回收
Revoke 语句权限 From 帐号/角色
七、SQL Server安全体系构造
创建登陆帐号
授予服务器
角色
授予数据
库角色
授予自定
义角色
授予语句
和对象权限
授予数据库连接权限
访问数据
第九部分习题样例
SQL 安全管理：
单位新来了一个管理员，现需要让他管理一下内容，请适当授权：
--允许他控制SQL SERVER性能，在必要的时候切断部分用户的连接来保证整体性能；
EXEC sp_addlogin 'admin','admin'
EXEC sp_addsrvrolemember 'admin','processadmin'
--允许启动或关闭SQL AGENT服务；
EXEC sp_addsrvrolemember 'admin','serveradmin'
--允许对其他用户授权；
EXEC sp_addsrvrolemember 'admin','securityadmin'
--后发现权利过大，收回其授权的权利；
EXEC sp_dropsrvrolemember 'admin','securityadmin'
--允许连接内部人事数据库HR
use HR
EXEC sp_grantdbaccess 'admin'
--允许读写所有的表
EXEC sp_addrolemember 'DB_datareader','admin'
EXEC sp_addrolemember 'DB_datawriter','admin'
--允许备份数据
EXEC sp_addrolemember 'DB_backupoperator','admin'
--但发觉不应该由管理员来修改表，所以取消写入权限；
EXEC sp_droprolemember 'DB_DENYdatawriter','admin'
--允许偶尔根据数据规划原则修改部分表的结构提升性能；
EXEC sp_addrolemember 'DB_DDLAdmin','admin'
--发觉不应该让其查询到人员工资资料，所以，收回读取权限；
EXEC sp_droprolemember 'DB_DENYdatareader','admin'
--但给予MEMBER表、BASEINFO表的所有控制权限；
grant all on member,baseinfo to admin
--要求其根据业务需要来做DBP程序；
grant create procedure to admin
grant create function to admin
--要求其经常备份数据库；
grant backup database to admin
--如果来了新的管理员，全部按照该指责来分配权限；
EXEC sp_addrole 'Role_Admin'
EXEC sp_addsrvrolemember 'Role_Admin','processadmin'
EXEC sp_addsrvrolemember 'Role_Admin','serveradmin'
EXEC sp_addrolemember 'DB_datareader','Role_Admin'
EXEC sp_addrolemember 'DB_backupoperator','Role_Admin'
EXEC sp_addrolemember 'DB_DDLAdmin','Role_Admin'
grant all on member,baseinfo to Role_Admin
grant create procedure to Role_Admin
grant create function to Role_Admin
grant backup database to Role_Admin
第十部分：T-SQL编程基础
一、SQL Server中的批处理
1、批
Ø 将多条具有相关性的语句作为一个有机整体发送到数据库中进行编译和生成执行计划，以提高整体的执行效率
Ø 批处理之间以“Go”分隔，无论上一个批是否执行正确，下一个批都将自动执行
Ø 批处理方式无法保证数据的同步性和一致性，可以通过带事务的批解决该问题
2、批中的错误
Ø 编译错误
编译过程中产生，批执行立即停止
Ø 轻微错误
跳过错误语句，继续执行（违反约束)
Ø 致命错误
中断执行，甚至可能中断连接
SQL 语句1
SQL 语句2
SQL 语句3
…
Go
Begin Tran
SQL 语句1
SQL 语句2
SQL 语句3
…
If @@Error=0
Commit Tran
Else
RollBack
3、批的调整和事务性
二、编程体系中的主要元素
² 常量和变量（局部和全局）
² 数据类型（系统和自定义）
² 流程控制
² 过程和函数
三、 使用变量
1、 一个变量整体，不可分割！
变量是可以存储数据值的对象。
2、 局部变量的定义与赋值
声明：
declare @ variableName [变量名称] datatype[数据类型]
赋值，可以使用SET 或SELECT
set 赋值：set @variableName=expression
select赋值：select @variableName=expression
3、 全局变量
使用两个@@标记做前缀。
四、T-SQL中的变量
1、全局变量
• 命名：@@变量名
• 常用全局变量
• @@Error
• @@Fetch_Status
• @@Idle
• @@Max_Connections
• @@ServerName
• @@TranCount
• @@RowCount
• @@Version
• @@Cursor_Rows
2、局部变量
Ø 申明定义
Declare @变量名 类型
Ø 赋值
Set @变量名= 值
Select @变量名= 值
Ø 显示
Print @变量名
Select @变量名
五、 逻辑控制语句
在SQL中，经常需要在一些行上重复执行或根据条件执行一组操作。为了实现这一点，SQL Server提供了程序设计结构。这些结构称为控制流语句。
表：控制流语句的总结
流程控制结构的语法
描述
BEGIN
<sql statements>SQL语句块
END
该单元中的语句可以认为是一个块或一个单元
RETURN
该语句提供了一种方法，用于从存储过程或查询中正常退出。
GOTO<label>标签
<label>：跳至的标签处
将流程转到由标签指定的位置
IF<condition>条件
<sql statements>
ELSE
<sql statements>
该结构可以用来根据条件的真实值执行一组选中的语句。
WHILE<condition>
BEGIN
<sql statements>
<on some condition>
BREAK
<on some condition>
CONTINUE
END
该结构重复执行一组语句，直到条件的计算值为假。
BREAK
该语句将控制权转到其所在的循环之外
CONTINUE
结束本次循环
CASE <expression>表达式
WHEN <expression1> THEN
[WHEN <expression2> THEN]
ELSE< expression3>
END
示例：
select stu_id,stu_name,
case stu_sex
when 0 then '女'
when 1 then '男'
else '变态'
end as 性别
from tbl_stu_for_case
六、T-SQL中的错误管理
1、增加自定义错误
Exec Sp_AddMessage 信息编号,级别，‘信息’,‘消息语言’,‘With Log’,’Replace’
注意：
Ø With Log – 是否将消息写入操作系统日志中
Ø Replace - 如果遇到相同错误，是否覆盖
Ø 信息编号 – 必须从50001开始(前面都由系统占用）
Ø 级别 - 有效级别：1-25
² 0 – 无任何错误
² 1-10 - 状态信息，表示问题是由于用户输入产生
² 11-16 – 错误由用户产生，可以休正
² 17-18 – 严重错误，如资源不足或转换错误
² 20-25 – 系统有问题，导致程序中断
七、T-SQL中的函数
1、系统函数
• Db_Id
• Db_Name
• Host_Id
• Host_Name
• Suser_Id
• Suser_Name
• User_Name
• Col_Length
2、日期型函数
• DataAdd(单位,值，日期)
• DateDiff(单位，日期，日期）
• Day(日期）
• Month (日期）
• Year (日期）
• GetDate（）
• GetUTCDate
3、字符串函数
• AscII（S）
• Char(N）
• Left(S，N）/ Right(S,N)
• Lower(S) / Upper(S)
• Ltrim(S) / Rtrim(S)
• Replace(S1,S2,S3)
• Space(N)
• Substr(S,N1,N2)
4、数值函数
• ABS(N)
• CEILING(F) / FLOOR(F)
• RAND()
• ROUND(F,N)
• LOG(N)
• LOG10(N)
• PI()
• POWER(N1,N2)
T-SQL有三种类型的函数：
行集函数：用以返回对象，该对象可在Transact-SQL语句中用作表引用。
聚合函数：用以计算一个值，如总和或标准差。
标量函数：对单个值进行操作，并返回单个值。
以下都是标量函数：
1、 转换函数convert ( )
语法：
convert (datatype[(length)] , expression ,[style] )
参数一：datatype是指需转换成的数据类型。
参数二：expression是需转换的数据
参数三：style,可选，当要将日期形式转换成character数据时，指定日期表示方法。
2、 日期和时间函数
日期部分[datepart]
日期部分
缩写
值
日期部分
缩写
值
小时
hh
0-23
星期
wk
1-53
分钟
Mi
0-59
星期几
dw
1-7
秒
Ss
0-59
月
mm
1-12
毫秒
Ms
0-999
季度
qq
1-4
一年中的一天
Dy
1-366
年
yy
1753-9999
日
Dd
1-31
函数
返回值
示例
getdate()
当前的系统日期
select getdate ( )
dateadd (datepart, number,date )
将指定的数值添加到指定日期部分
select dateadd ( mm,4,’01/01/99’ )
返回：05/01/99
Datediff (datepart,date1,date2 )
比较日期
Select
Datediff (mm,’01/01/99’,’05/01/99’)
Datename (datepart,date)
日期指定部分字符串形式
Select datename(dw,’01/01/2000’)
返回Saturday
Datepart(datepart,date)
日期指定部分的整数形式
Select datepart(day,’01/15/2000’)
返回15
3、 数学函数
函数
返回值
示例
ABS（number）
绝对值
Select abs(-43) 返回43
Ceiling(number)
大于或等于指定数的最小整数
Select ceiling(43.5) 返回44
Floor(number)
小于或等于指定数的最大整数
Select floor(43.5) 返回43
Power(number，y)
数值的y次幂的值
Select power(5,2) 返回25
Round(number,length)
将数值四舍五入为指定精度
Select round(43.545,1) 返回43.5
Sign(number)
对于正数返回+1，对于负数返回-1，对于0则返加0
Select sign(-43) 返回-1
Sqrt(number)
求平方根
Select sqrt(9) 返回3
示例：学生编号以stu开头,然后跟四位整数，如stu0001,stu0002，自动增长
select 'stu'+right('0000'+convert(varchar(4),isnull(convert(int,substring(max(stu_id),4,6))+1 ,1)),4) from tbl_stu_for_generateNO
第九部分习题样例
--1、使用T-SQL编写代码完成一下功能；
--打印1－1000中所有能够3整除，但不能被5整除的所有整数并累计其汇总值；
declare @i smallint,@total int
set @i=1
set @total=0
while @i<=1000
begin
if @i%3=0 and @i%5<>0
begin
print @i
set @total=@total+@i
end
set @i=@i+1
end
print 'total='+ltrim(str(@total))
--2、使用T-SQL编程编写代码完成一下功能；
--1、打印PUBS数据库中的Authors表中的所有人员，并将名字中的头字母全部列举出来，并计算出相应的人数；
use pubs
select * from authors
declare @Fname char(1),@Ncount int
declare Cur_Author cursor
local
forward_only
static
read_only
For select distinct left(au_fname,1),count(*) as [count] from Authors
group by left(au_fname,1)
order by left(au_fname,1) ASC
open Cur_Author
Fetch next from Cur_Author into @Fname,@Ncount
print 'First Sum'
while @@Fetch_Status=0
begin
print @Fname+' '+str(@Ncount)
Fetch next from Cur_Author into @Fname,@Ncount
end
--2、打印出PUBS中的各人员的出书册数并计算前三名的册数之和；
use pubs
select * from titleauthor
select * from authors
declare Cur_Book cursor
local
forward_only
static
read_only
for select (A.au_lname+A.au_fname ) Au_Name,B.Total
from authors A,
(select au_id,count(*) Total from titleauthor
group by au_id) B
where A.au_id=B.au_id
order by B.Total DESC
--下面用于计算
declare @Total smallint,@Author varchar(20),@Ncount smallint
declare @i smallint
set @Total=0
set @i=1
print 'Author Ncount'
open Cur_Book
Fetch next from Cur_Book into @Author,@Ncount
while @@Fetch_Status=0
begin
select @Author,@Ncount
if @i=1
begin
select 'First'
set @Total=@Total+@Ncount
end
if @i=2
begin
select 'Second'
set @Total=@Total+@Ncount
end
if @i=3
begin
select 'Third'
set @Total=@Total+@Ncount
end
set @i=@i+1
Fetch next from Cur_Book into @Author,@Ncount
end
select @Total
close Cur_Book
dealLocate Cur_Book
第十一部分：事务和锁
一、事务
事务：是一种机制，是一个操作序列，它包含了一组数据库操作命令，并且所有的命令做为一个整体向系统提交或撤消。因此是一个整体的逻辑单元。
1、数据库事务的特性
事务具备四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。这些特性通常被称为：ACID。
原子性：是指事务的各元素是不可分的，事务中的所有元素必须做为一个整体提交或回滚。事务是一个完整的操作。
一致性：当事务完成时，数据必须处于一致状态。
隔离性：对数据存储中的数据进行修改的所有事务是彼此隔离的。
持久性：事务的持久性意味着只有当事务提交以后，操作才会完成。
2、事务的分类
事务分为：显示事务、隐性事务、自动提交事务
（4） 显示事务：显示事务是显式地定义其开始和结束的事务。
开始：begin tran(transaction)
提交：commit tran
回滚：rollback tran
（5） 隐性事务：通过Transact-SQL set implicit_transactions on 语句，将隐性事务模式设置为打开。Microsoft SQL Server将在提交或回滚当前事务后自动启动新事务。无需描述事务的开始，只需提交或回滚每个事务。
打开隐性事务：set implicit_transactions on
关闭隐性事务：set implicit_transactions off
（6） 自动提交事务：默认模式。
3、锁与死锁
锁是在多用户环境中对数据访问的限制，SQL Server 2000 使用锁定来确保事务的完整性和数据库的一致性，锁定蕴含的基本概念是用户需要对表的排它访问。
（1）SQL Server 中锁的分类
— 共享、更新、排它、意向、架构、大容量更新
（2）共享锁
• 锁定时机
– select 表
• 限制
– 其它任何事务都不能修改数据
– 只能select
• 备注
– 默认的select锁定时间段是在select过程中
– 可通过设定holdlock使锁定时间段持续整个事务
示例：
begin tran
select * from tbl_A_for_dead_lock (holdlock)
waitfor delay '0:0:30' （延长时间）
commit tran
（3）排它锁
• 锁定时机
– 对表进行DML（数据操作语言）操作(insert ,update,delete)
• 限制
– 其它任何事务不能读取或修改数据
（4）死锁
事务1请求了事务2加锁的资源，事务2又请求了事务1加锁的资源
（5）解除死锁
• SET DEADLOCK_PRIORITY
• low:将当前会话指定为首选的死锁牺牲品
• SET LOCK_TIMEOUT
• 指定等待释放锁的毫秒数
• 默认值为-1，表示无限期等待
（6）更新锁
• 为避免上述死锁，使用更新锁
• 锁定时机
– 使用select * from t(updlock)
• 限制
– 不允许其它事务读取和修改数据
• 特殊之处
– 如果当前事务以后修改资源，则转化为排它锁
– 否则，转换为共享锁
二、事务的并发问题
并发问题包括：
– 丢失或覆盖更新
– 未确认的相关性（脏读）
– 不一致的分析（非重复读）
– 幻像读
–
1、丢失或覆盖更新
– 当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题
– 每个事务都不知道其它事务的存在
– 最后的更新将重写由其它事务所做的更新，这将导致数据丢失
– 示例：两人同时往同一帐户同时存钱
2、未确认的相关性
– 当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题
– 第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改
3、不一致的分析（非重复读）
– 当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题
– 在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的
– 不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其它事务更改；因而该行被非重复读取
4、幻像读
– 当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题
– 事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除
– 同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中
三、事务的隔离和隔离级别
• 未提交读
• 提交读（默认）
• 可重复读
• 可串行读
第十二部分：游标使用基础
一、T-SQL中的游标
1、游标
Ø SQL语句执行时所占内存资源的集合，一个游标对应一个SQL语句
Ø 游标打开的过程即是SQL语句执行的过程，SQL语句执行的结果将自动写入游标区域
Ø 游标被显式调用或隐式打开
n Select --显式调用
n Update，Delete --隐式打开
2、游标的申明
Ø 传统法申明
Declare 游标名 Insensitive Scroll Cursor
For Select_Statement
For ReadOnly | Update Of Columns
Ø 扩展法申明
Declare 游标名 Cursor
Local | Global
Forward Only | Scroll
Static | KeySet | Dynamic | Fast_Forward
ReadOnly | Scroll_Locks | Optimistic
For Select_Statements
For Update Of Columns
二、T-SQL中的游标控制
Ø 打开游标
Open 游标名
Ø 关闭游标
Close 游标名
Ø 删除游标
DealLocate 游标名
Ø 读取游标
Fetch First From 游标名 Into 变量集
Next
Prior
Last
Absolute N
Relative N
Ø 游标的判断
@@Fetch_Status – 标识被FETCH的游标的指针状态
0 – Fetch成功
-1 – Fetch语句失败或此行不再结果集中
-2 – 被提取的行不存在
Ø 游标的状态
Cursor_Status(‘Local’|’Global’| ’Variable’,’游标名‘)
1 – 游标打开，有数据
0 – 游标打开，没有数据
-1 – 游标被关闭
-2 – 游标被Deallocate
-3 – 游标不存在
三、T-SQL中的游标查询
Ø 查询游标全局特征
Exec Sp_Describe_Cursor ‘游标名’
Ø 查看游标结果集特征
Exec Sp_Describe_Cursor_Columns ‘游标名’
Ø 查看游标所引用的基表
Exec Sp_Describe_Cursor_Tables ‘游标名’
1、游标优点
游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作。
2、游标的使用过程
声明(创建)游标 打开游标 遍历游标中每一条记录 关闭游标 释放游标
3、创建游标
DECLARE [declare] 语句用于创建游标。它包含SELECT语句，用来引入表中的记录。
语法：
declare <Cursor_Name> cursor
for <Select Statements>
4、在T_SQL使用游标
• 打开游标
open <Cursor_name>
• 检索记录
fetch <Cursor_name>
• 关闭游标
close <Cursor_name>
• 删除游标引用
deallocate <Cursor_name>
5、游标类型
Ø 只进游标
不支持滚动,只能从头到尾提取
Ø 静态游标
不允许修改游标对应的数据
Ø 键集驱动游标
游标所有者的更新可见，其它成员的更新不可见
Ø 动态游标
可滚动，可显示更新
6、在T_SQL中创建游标
DECLARE语句用于创建游标。它包含SELECT语句，用来引入表中的记录。
语法：
DECLARE <Cursor_Name> CURSOR
[LOCAL | GLOBAL]
[FORWARD ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC |FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR <Select Statements>
[FOR UPDATE [OF Column_name[,….N]]]
7、在T_SQL提取和滚动游标
• fetch first：提取游标的第一行。
• fetch next：提取上次提取的行的下一行。
• fetch prior：提取上次提取的行的前一行。
• fetch last：提取游标中的最后一行。
• fetch absolute n：
- 如果n 为正整数，则提取 游标中的第n行
- 如果n为负整数，则提取游标最后一行之前的第n行
- 如果n 为0，则不提取任何行
• fetch relative n ：
- 如果n为正，则提取上次提取的行之后的第n行。
- 如果n为负，则提取上提取的行之前的第n行。
- 如果n为0，则再次提取同一行。
8、FETCH 语句全局变量
• @@FETCH _STATUS：为执行的上一个游标 FETCH 语句返回一个整数值。
• @@CURSOR_ROWS：返回当前打开的游标中符合条件的行的数目。
示例：
select * from tbl_class
declare @class_id varchar(20)
declare @class_name varchar(20)
declare @class_desc varchar(20)
declare cur_class cursor scroll for select * from tbl_class
open cur_class
fetch next from cur_class
into @class_id,@class_name,@class_desc
while (@@fetch_status=0)
begin
select @class_id , @class_name , @class_desc
fetch next from cur_class
into @class_id,@class_name,@class_desc
end
fetch first from cur_class
close cur_class
deallocate cur_class
第十三部分：存储过程和函数
一、T-SQL中的存储过程
1、存储过程
命名的T-SQL语句块，编译后二进制信息保存在Master数据库中
2、作用
² 速度快
² 代码复用
² 规范设计
² 提高系统安全性
3、存储过程的定义
Create | Alter Procedure 存储过程名;序号
@Para Type Varying=Default OutPut
With Recompile | Encryption
As SQL_Statment
注：序号用来对同名过程进行分组，可以对存储过程进行适当的规划,避免因为存储过程过程过多给管理带来的不便
4、存储过程的执行
Exec @变量=存储过程名 参数
5、存储过程的查询
Ø 查询存储过程的定义
Exec Sp_HelpText ‘存储过程名’
Ø 查询存储过程的相关信息
Exec Sp_Help ‘存储过程名’
Ø 查询存储过程的依赖情况
Exec Sp_Depends ‘存储过程名’
6、存储过程的删除
Drop Procedure ‘过程名’
7、存储过程的数据返回
Ø 返回值
Return N -- 只能返回整型值
Ø Out参数
二、T-SQL中的临时存储过程
1、临时存储过程
因暂时需求而产生的进程型存储过程
2、特征
² 临时存储过程存放在TEMPDB数据库中
² 临时存储过程分局部（#）和全局（##）两种
² 局部临时存储过程以“#”开头，只有建立的人可以使用
² 全局临时存储过程以“##”开头，所有人都可以使用，当建立该过程的用户断开连接后，其他用户无法使用，已在执行中的可以继续执行到结束后删除
三、T-SQL中的函数
1、函数的定义
Create | Alter Function 函数名
（@Para Type Varying=Default）
Returns 返回类型
With SchemaBinding | Encryption
As SQL_Block
` 2、函数分类
Ø 标量函数：返回单一的数据值，该值可以是除Text,Ntext,Image,Cursor之外的数据类型
Ø 数据集函数：返回一个Table型数据集
² 行内数据集函数 – 函数的内部仅有一个SQL语句，返回值就是SQL的结果
² 多语句数据集函数 – 包含很多个语句，最后也返回一个Table型的数据集
四、T-SQL中的函数和过程的比较
比较项目
过程
函数
返回值类型
整型
各种类型(Table)
传出参数
有
无
修改数据库
允许
不允许
执行
必须用EXEC
可以直接作为表达式调用
五、T-SQL中的容错方法
事务支持
Begin Tran
Commit Tran
RollBack Tran
错误控制
物理错误
逻辑错误 Raiserror
六、在存储过程中使用参数
1、可以使用参数向存储过程中输入值，或从存储过程中输出值。
语法:
CREATE PROCEDURE procedure_name
@Parameter_name data_type
AS
:
示例1：——无任何参数
创建：
create proc proc_in
as
begin tran
insert into tbl_stockin
values('rk0002','2005-10-10','张三','伊利')
insert into tbl_stockin_detail
values('rk0002',1,'1002',0.7,10000)
commit tran
go
使用：
execute proc_in
示例2：——带输入参数
创建：
create proc proc_in_inparam
@rk_id varchar(20) ,
@rk_date datetime ,
@rk_person varchar(20),
@rk_client varchar(20)
as
begin tran
insert into tbl_stockin
values(@rk_id,@rk_date,@rk_person,@rk_client)
insert into tbl_stockin_detail
values(@rk_id,1,'1002',0.7,10000)
commit tran
go
使用：
exec proc_in_inparam 'rk0004','2006-6-6','李四','厂商X'
示例3：——带输出参数[output]
创建：
create proc proc_cal
@a int ,
@b int ,
@sum int output ,
@sub int output,
@ji int output
as
set @sum = @a +@b
set @sub = @a -@b
set @ji = @a *@b
go
参数1：输入
参数2：输入
参数3：output
参数4：output
参数5：output
使用：
declare @sum int
declare @sub int
declare @ji int
exec proc_cal 4, 1, @sum output , @sub output , @ji output
select @sum , @sub , @ji
示例4：——代码返回数据
创建：
create proc proc_cal_return
@a int ,
@b int ,
@sum int output ,
@sub int output,
@ji int output
as
set @sum = @a +@b
set @sub = @a -@b
set @ji = @a *@b
return @a/@b
代码返回值
go
使用：
declare @sum int
接收返回值
declare @sub int
declare @ji int
declare @r int
exec @r = proc_cal_return 4,1,@sum output ,@sub output ,@ji output
select @sum , @sub , @ji , @r
示例5：——指定默认值
create proc proc_cal_def
@a int = 5, @b int = 3 ,@sum int output ,@sub int output, @ji int output
as
set @sum = @a +@b
set @sub = @a -@b
set @ji = @a *@b
return @a/@b
区别所在
go
declare @sum int
declare @sub int
declare @ji int
declare @r int
exec @r=proc_cal_def @sum=@sum output ,@sub=@sub output ,@ji=@ji output
select @sum , @sub , @ji , @r
七、重新编译存储过程
SQL Server提供了三种重新编译存储过程的方法：
方法一：
使用系统存储过程sp_recompile
语法：
sp_recompile [@objectname=]’object’
如果存储过程名称被指定为object(对象)，下次运行时将对它进行重新编译。
方法二：
使用create procedure 时指定with recompile
此方法每次执行存储过程时，都会重新编译。
语法：
create procedure procedure_name
@parameter_name data_type
with recompile
as
…
方法三(常用)：
使用execute 时指定with recompile
语法：
execute procedure_name with recompile
这样只会在调用execute时，重新编译一次。
第十四部分：触发器的管理
•
一、T-SQL中的触发器
1、触发器
一种与数据表紧密结合的存储过程，当表有Insert、Update、Delete事件发生时，所设置的触发器器将自动执行，以进行完整性维护和其他的处理工作。
2、作用
² 前期的复杂约束校验，后期的数据同步
² 对违反约束的操作进行Rollback
² 比较表修改前后的数据差别
3、触发器的分类型
² After触发器
这类触发器在数据已经变化完成后，才被激活并进行善后处理工作，如果有错误，可以用ROLLBACK TRAN将已做过的操作回滚。
² Instead Of触发器
这类触发器会取代原本要进行的操作，因此会在数据改变之前就发生，而数据要如何改动完全取决于触发器
4、触发器的定义
Create | Alter Trigger 触发器名
On 表名 | 视图名
With Encryption
For After | Instead Of Insert,Update,Delete
As T_SQL_Block
4、 触发器注意事项
² 对于每个触发操作，每个表或视图只能有一个Instead Of触发器，但可以有多个After触发器。
² 触发器在TEMPDB中分别建立两个临时表Inserted和Deleted来保存操作修改前后的数据（只对单记录操作有效）。
² SQL Server的触发器为“行级触发器”，无论影响表中多少条记录（如UPDATE操作），触发器只触发一次。
² 触发器设计时必须特别小心，因为错误的触发器将导致表无法做任何操作。
二、T-SQL中的触发器和过程的比较
比较项目
过程
触发器
返回值类型
整型
无
传入/出参数
有
无
修改数据库
允许
允许
建立基础
针对数据库
针对数据表
执行
必须用EXEC
自动执行
三、T-SQL中的触发器管理
1、查询触发器信息
Exec Sp_HelpTrigger ‘触发器名’
2、查询触发器定义
Exec Sp_HelpText ‘触发器名’
3、删除触发器
Drop Trigger ‘触发器名’
第十四部分习题样例
create table student
(
sut_code varchar(10),
stu_name varchar(20),
stu_class varchar(20)
)
create table fname
(
family varchar(10),
amount varchar(10)
)
create table class
(
class_ID varchar(10) constraint PK_class_classid primary key(class_ID),
class_num varchar(20)
)
Insert into Class Values('T15',0)
Insert into Class Values('W16',0)
Insert into fname Values('张',1)
Insert into fname Values('林',1)
Insert into student Values(01,'张小小','T15')
Insert into student Values(02,'林淑芬','W16')
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'TR_UPD' AND type = 'TR')
DROP TRIGGER TR_UPD
go
create trigger TR_UPD
on student
with encryption
after update
as
declare @new_classID varchar(10)
declare @old_classID varchar(10)
declare @new_name varchar(10)
declare @old_name varchar(10)
select @new_classID=stu_class,@new_name=stu_name from inserted
select @old_classID=stu_class,@old_name=stu_name from deleted
if @new_classID<>@old_classID
begin
if exists(select * from class where class_ID=@new_classID)
begin
update class set class_num=class_num+1 where class_ID=@new_classID
update class set class_num=class_num-1 where class_ID=@old_classID
end
else
begin
print '班级有误'
rollback
print '回滚成功!'
end
end
if @new_name<>@old_name
begin
if exists(select * from fname where family=substring(@new_name,0,2))
begin
update fname set amount=amount+1 where family=substring(@new_name,0,2)
update fname set amount=amount-1 where family=substring(@old_name,0,2)
end
else
begin
print '姓名有误'
rollback
print '回滚成功!'
end
end
print '数据已经被成功UPDATE'
GO
update student set stu_class='T15' where stu_name = '张小小'
select * from student
select * from fname
select * from class
328
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
