SQL SERVER2000学习完整版
第一部分：数据库概论
单词记忆：
DBA {database administrator}：数据库管理员
DBMS {database management system}：数据库管理系统
SQL {structured query language}：结构化查询语言
DQL{data query language}：数据查询语言
DML{data manipulation language}：数据操作语言
DCL{data control language}：数据控制语言
DDL{data definition language}：数据定义语言
一、 计算机数据库的优点
1、 降低存储数据的冗余度，也就是减少重复的数据。
2、 更高的数据一致性。
3、 存储的数据可以共享。
4、 可以建立数据库所遵循的标准。
5、 便于维护数据完整性，就是指保证数据库中数据的准确性。
6、 能够实现数据的安全性。
二、 数据库分类
单机数据库：
优点：高效、简单
缺点：数据无法共享
实例：ACCESS、FOXPRO
网络数据库：
• 优点：数据共享、提供并发访问
• 缺点：必须考虑网络压力、冲突处理、资源限制
• 实例：实例：SQL Server、Oracle
三、 数据库系统组成
数据库系统由数据库与数据库管理系统DBMS组成
四、 数据库体系模型
1、层次型
——以结构树模型进行数据存储，由节点（实体）、连线 （关系）构成，将各种有关联的数据存储在不同层次上。
优点：层次分明、结构清晰、关联简单
缺点：数据冗余，层次间无横向关系，不便管理
2、网络型
——以网络模型进行数据存储，由节点（实体）、连线 （关系）构成，组成独立实体网络实体。
优点：消除冗余，对象关联完整
缺点：关系过于负责，无法支持大型数据
3、关系型
——以二维表格进行数据存储，由记录（实体）、字段（属性）、键（关系）构成。
优点：1、结构简单、格式唯一，在一定程度上控制冗余、并降低关系的复杂性
2、表间独立性强，可以进行异步操作。
缺点：无法完全消除冗余、数据分散
五、模型变迁原因
层次模型——网络模型——关系模型
关系型改革原则：
• 通过对象集合化减少对象个数（表）
• 通过对象关系化实现对象间复杂关系
• 通过范式化减少数据冗余
六、数据库的基本概念
1、 数据和信息：数据一些实体，在我们的工作中，数据是最重要的部分，我们将某些数据收集起来并加以分析，就生成了信息。
2、 数据库和表：表是数据库对象。而数据库是相关的多维数据集及其所共享的对象的容器。
3、 行或列的关系：表是由分别排成行和列的相关记录组成的集合。在表中，数据以行和列的形式存储，类似电子表格。
4、 数据库系统和数据库管理系统：数据库系统是一个基于计算机的记录保存系统，它最主要的用途是记录和维护信息。数据库管理系统（DBMS）包括一组相关的记录以及一组用来访问和操作这些记录的程序。
5、 数据冗余度和数据的完整性
七、关系型数据库的基本概念
1、 实体完整性和主键
主键：唯一地标识表中的记录的一个或一组列称为“主键”。每个表都应有一个主键。
实体完整性：基表主键的任何部分都不可以接受空值（null）。
2、 引用完整性和外键
外键：是一个或一组列，其中列的值与另外一个表中的主键或唯一键相匹配。两个表是通过外键联系起来的。换句话说，通过一个共同字段来连接两个表称为建立关系，而创建外键就是建立关系的手段。
引用完整性：从表引用外部数据必须在其它表中存在。
3、 关系和度
在关系模型中，每个关系或表都包含许多“元组”。元组也称为记录或行。
表中属性的数量称为该表的“度”。
4、 字段约束和字段约束的方法
通常把属性称为字段。
八、数据库规划原则
Ø 最大可重用化
--数据库重大问题时，通过备份和恢复机制最大程度上恢复数据。
Ø 最小磁盘争用
--数据库文件平均分布在不同的磁盘上避免多用户访问时候争用同一磁盘
Ø 各种数据的合理分布
--将数据库中的各种数据按特性（如基表和变化表、大数据和常规数据）存储在不同的文件中。
九、数据表规划原则
Ø 数据定义精确化
--满足要求的情况下选择占用资源最少的数据类型以提高DBMS的I/0性能
Ø 表的抽象化
--通过将具有共性的表合并，将其特性以标识字段表示。
Ø 表的范式化
--设计表时，满足1NF（原子性）、2NF（键相关性）、3NF（无函数相关性），保证表的结构无冗余。
原子性：
键相关性：
无函数相关性：
十、数据表 I / 0优化原则
Ø 变化表和基表的分离
--将变化表（递增性很强，如单据表、销售记录表）和基表（很少变化，如学生资料、班级资料等）分开存放到不同的数据文件中
Ø 大数据和常规数据的分离
--将表中的TEXT、NTEXT、IMAGE型大数据字段和其他常规类型字段分开存放，保证常规数据的查询高效性
Ø 索引数据和表数据的分离
--将索引和表分离，减少磁盘争用并提高索引的查询效率。
十一、SQL Server的存储特性
Ø 页（8K）
--将OS中连续的8个块组成“页”，作为空间分配的最小单元，以减少空间分配时所需耗费的资源。（处理大表时，可能将8个连续的“页”组成一个“扩展”，以进一步减少分配时所耗用的资源）
Ø 表记录存放在页中
--表的记录尽量存放在页中，而且一个页中一般只存放同表的内容，保证取得高命中率。
Ø 数据读取以页为单位
十二、SQL简介
SQL的分类
DQL（数据查询语言）：用来从数据库中获取数据和对数据进行排序。
DML（数据库操作语言）：用来插入、删除、修改数据库中的数据。
DCL（数据控制语言）：用于管理对数据库和数据库对象的权限。
DDL（数据定义语言）
CCL（通用命令语言）：用于在数据库中进行高效率的搜索。
第二部分：数据库管理
单词记忆：transact：处理
create：创建
execute：执行、完成
一、 SQL Server的特性
1、 安装简便：为了便于安装、使用和管理，SQL Server2000提供了一组管理和开发工具。
2、 伸缩性和有效性
3、 支持客户/服务器模式（C/S）
4、 多协议兼容性
5、 数据仓库
6、 遵从ANSI/ISO SQL-92标准：Micrsoft 还对其进行了语言扩展并命名为：“Transact-SQL”
7、 支持数据复制
8、 全文检索
9、 联机丛书
二、T-SQL简介
Transact-SQL（或称为T-SQL）是Micosoft的一个程序扩展集合。T-SQL为SQL增加了些功能,包括事务控制、异常错误处理和行处理。
Transact- SQL允许用户在Transact- SQL对象中声明和使用局部变量和常量，并且有数据类型。
三、SQL Server存储体系构成
Ø 数据文件
后缀为.MDF 文件
–主数据文件，一个数据库只有一个 MDF文件，存放数据库中各种数据实体的定义（数据字典），也可以存放其他数据，但要注意控制其大小。
Ø 数据文件
后缀为.NDF文件
–扩展数据文件，一个数据库可以有多个NDF文件，存放各种数据
1. 数据规划(组）
2. 数据扩展
3. 文件仿真阵列
Ø 日志文件
后缀为.LDF文件
–日志文件，一个数据库可以有多个LDF文件，存放联机重做日志数据
• 注意：如果日志文件已经填满，SQL Server将自动停止运行并等待人工干预，所以要经常监控日志的情况保证系统的稳定运行。
Ø 文件组
FileGroup
–将MDF和NDF文件在逻辑上进行分组，进行表规划时候，只需要指定表所需存放的组，就可以实现数据分离。
注意：系统默认的组为“PRIMARY”，不指定数据文件所隶属的组，则数据文件自动划分到“PRIMARY”组中；创建的表不指定所在的组，系统也自动分配该表到“PRIMARY”组中。
四、数据库的创建
示例：
Create Database 数据库名
On
(Name=‘逻辑名‘，
FileName=‘物理文件完全路径(*.mdf)’,
Size=n,
MaxSize=n,
FileGrowth=n)
FileGroup 组名字
(…….)
Log On
(Name=‘逻辑名‘，
FileName=‘物理文件完全路径(*.ldf)’,
Size=n,
MaxSize=n,
FileGroup=n)
五、创建过程中需要注意的问题：
Ø 只能为一个数据库创建一个MDF文件；
Ø 如果需要进行文件分组，只需要在文件前加上FILEGROUP组名就可以了，但PRIMARY组中至少必须包含一个文件；
Ø 如果Size和Max Size不指定单位，系统默认用“M”为单位；
Ø 如果不指定数据库文件的大小，系统默认为“1M”；
Ø 如果创建时没有使用Log On字句，系统自动创建一个LDF文件，大小为MDF文件的1/4；
Ø 文件的增长率FILEGROWTH应该根据实际需要设置大些，以保证系统不会频繁扩容。
六、数据库性能选项配置
Ø 选项设置
Exec Sp_Dboption ‘数据库名’,‘选项‘，’值’
如：Exec Sp_Dboption ‘Pubs’,’AutoClose’,’False’
Ø 查询所有可用选项
Exec Sp_Dboption
Ø 查询数据库已经开启的选项
Exec Sp_Dboption ‘数据库名’
如：Exec Sp_Dboption ‘Pubs’
七、数据库的调整
1、设置数据库只读取
Exec Sp_dboption ‘Pubs’,’Read_Only’,’True’
2、设置数据库离线
Exec Sp_dboption ‘Pubs’,’Offline’,’True’
3、数据库更名（Sp_RenameDb)
Exec Sp_dboption ‘Pubs’,’Single User’,’True’
Exec Sp_RenameDb ‘Pubs’,’Pubs_1’
Exec Sp_dboption ‘Pubs_1’,’Single User’,’False’
八、数据库结构调整
1、调整数据库结构
Alter Database 数据库名
• Add File (文件参数) To FileGroup 文件组
• Add Log File (文件参数）
• Remove File ‘逻辑数据库文件名字’
• Remove FileGroup ‘组名’
• Add FileGroup ‘组名’
• Modify File (文件参数)
注意：
1、ADD FILE时如果不指定“TO FileGroup 文件组”,系统自动将该文件归到“Primary”组中；
2、REMOVE FILEGROUP时，如果组中有数据，将无法删除。
九、数据规划的实现
表的数据规划的实现
Create Table Student
(Stu_Code Varchar(10) Primary Key
On G_Index,
Stu_Name Varchar(10) Not Null,
Stu_Sex Varchar(1) Not Null,
Stu_Pic Image)
On G_Normal
TextImage_On G_Big
规化重点：
1、将大数据（G_Big)和常规数据(G_Normal)分离;
2、将索引数据（G_Index）和表数据分离；
第二部分习题样例
一、创建本班数据库（T607），根据规划原则将四大类数据分组，每组一个数据文件5－100M，按30％增长，日志文件2个，5－100M，按50％增长。
create database T607
on primary
(
name = 'T607_mdat',
filename = 'd:" T607_M.mdf ',
size = 5,
maxsize = 100,
filegrowth = 30%
),
filegroup G_Normal
(
name = 'T607_n1dat',
filename = 'd:" T607_N1.ndf ',
size = 5,
maxsize = 100,
filegrowth = 30%
),
filegroup G_Change
(
name = 'T607_n2dat',
filename = 'd:" T607_N2.ndf ',
size = 5,
maxsize = 100,
filegrowth = 30%
),
filegroup G_Index
(
name = 'T607_n3dat',
filename = 'd:" T607_N3.ndf ',
size = 5,
maxsize = 100,
filegrowth = 30%
),
filegroup G_Big
(
name = 'T607_n4dat',
filename = 'd:" T607_N4.ndf ',
size = 5,
maxsize = 100,
filegrowth = 30%
)
log on
(
name = 'T607_log1',
filename = 'd:" T607_L1.ldf ',
size = 5,
maxsize = 100,
filegrowth = 50%
),
(
name = 'T607_log2',
filename = 'd:" T607_L2.ldf ',
size = 5,
maxsize = 100,
filegrowth = 50%
)
二、数据库状态属性修改练习
1、 将创建好的数据库设置成自动“文件自动关闭”和自动“文件自动收缩”方式
文件自动关闭：exec Sp_dboption ' T607',' autoclose ',' true '
文件自动收缩：exec Sp_dboption ' T607',' autoshrink ',' true '
2、 将创建好的数据库设置成“只读方式”
exec Sp_dboption ' T607',' read only ',' true '
3、 将创建好的数据库设置成“离线状态”后恢复
exec sp_dboption ' T607',' offline ',' true '
4、 将创建好的数据库名称改名为“T607-2006”
exec sp_dboption ' T607',' single user ',' true '
exec sp_renameDb ' T607',' T607_2006 '
exec sp_dboption ' T607-2006 ',' single user ',' false '
注意事项：首先必须把只读属性取消
三、修改数据库文件属性
将数据库中的一个数据文件属性修改为10—无限大，增长比率为40％；
alter database [T607_2006]
modify file
(
name = T607_n1dat,
size = 10,
maxsize = unlimited,
filegrowth = 40%
)
注意事项：修改时，请勿指定物理名称。
四、收缩数据库文件
将数据库中的一个文件收缩到3M；
dbcc shrinkfile(‘ T607_n2dat ‘, 3)
注意事项：必须先use T607_2006才能收缩
五、组的创建和维护
在数据库中创建一个新的组“G_TEST”
alter database [T607_2006]
add filegroup G_TEST
六、组中增加一个文件
在数据库中增加一个新的文件“T607_TEST.ndf”，并自动加入到“G_TEST”组中；
alter database [T607-2006]
add file
(
name = 'T607_TEST',
filename = 'd:" T607_TEST.ndf'
)
to filegroup G_ TEST
七、向数据库中增加日志文件
在数据库中增加一个新的日志文件“T607_LOG_TEST”文件；
alter database [T607-2006]
add log file
(
name = 'T607_LOG_TEST',
filename = 'd:" T607_LOG_TEST.log'
)
八、数据库的规划方式
创建一个学生资料表，包含字段：
学生编码、
学生名称不允许重复
学生性别、
学生年龄 15—30、
学生生日、
学生地址、
学生国籍、
学生备注大量文本、
学生照片 JPG
根据表规划及优化原则，创建该表：
create table 学生
(
学生编码 smallint,
学生名称 varchar(10) unique,
学生性别 nvarchar(1),
学生年龄 tinyint check(学生年龄>=15 and 学生年龄<=30),
学生生日 datetime,
学生地址 nvarchar(20),
学生国籍 varchar(10),
学生备注 text,
学生照片 image
)
on group1
textimage_on group2
九、删除数据库
删除本练习中创建的数据库
drop database T607-2006
第三部分：数据表管理
一、SQL Server支持的数据类型
1、文本型
• CHAR 定长型 <=8000字节
• Varchar 变长型 <=8000字节
• Text 大文本 <=2G
• NCHAR 定长型 <=4000字节
• NVarchar 变长型 <=4000字节
NText 大文本 <=2G
注意：
Ø “N”型文本类型是为了表示中文、日文、韩文等大字符集的文字而产生的
Ø 对应定长的字符，用CHAR比用VARCHAR的效率要高，如学生表中的“Sex”
Ø 在SQL Server 7.0后N型和非N型通用
2、日期型
• DateTime 1753.1.1-9999.12.31 8字节
SmallDateTime 1902.1.1-2079.12.31 4字节
注意：
Ø 系统时间格式：D-M-YY HH24:M:S
Ø 系统可识别日期格式：
² 字母日期 ‘April 15,1998’
² 数字日期 ‘4/15/1998’
² 未分隔日期 ‘19981221’
Ø 取得系统时间：Getdate()
3、二进制型
• Image <=2G
• Sql_Variant 变体型，可以接受任何数据类型
注意：
Ø 如果表中包含IMAGE类型字段，该表的记录将无法以一个页存放，为避免产生行链接，建议将大数据字段和常规数据字段分开存放。
Ø SQL_Variant的字段无法经常各种常规的函数操作。
4、扩展型
• Identity 4字节
• Customer Type 用户自定义数据类型
注意：
• 增加自定义数据类型
Exec Sp_AddType 类型名称,‘类型定义’,‘空标志’
Exec Sp_AddType Telephone,’Varchar(24)’,’Not Null’
5、完整性类型
包括：实体完整性（加强完整性规则的SQL Server工具：主键约束、唯一约束、标识列）
域完整性（加强完整性规则的SQL Server工具：默认值定义、外键约束、检查约束、
NOT NULL属性）
引用完整性（加强完整性规则的SQL Server工具：外键约束、检查约束）
用户定义完整性（加强完整性规则的SQL Server工具：存储过程、规则、触发器）
6、创建和更改主键
(1) create table table_name <column_definition> primary key
其中，<column_definition>是列名
(2) 如果，现有表中无主键，可以添加主键：
alter table table_name
add constraint <constraint_name>
primary key ( <column_name> )
其中<constraint_name>为约束名。
7、约束和约束对象
SQL Server2000提供了以下约束：check<检查约束>、 default<默认值> 、primary key<主键> 、
foreign key<外键> 、unique<唯一性>。
8、通配符
|
通配符 |
解释 |
示例 |
|
_ |
一个字符 |
Select username from tb_user where username like ‘T_’ |
|
% |
任意长度的字符串 |
Select username from tb_user where username like ‘%T%’ |
|
[] |
括号中所指定的范围中的任意一个 |
Select * from flight where address like ‘bt52[1-4]’ |
|
[^] |
不在括号中所指定的范围中的任意一个 |
Select * from flight where address like ‘bt52[^1-4]’ |
9、 逻辑运算符
AND、OR和NOT
二、SQL Server对象模型化过程
Ø 数据采集并属性化
Ø 约束的制定
n 域约束：Not Null,Check,Default,Unique
n 完整性约束：Primary Key
n 引用性约束：Foreign Key,Trigger
Ø 规则的制定
Ø 关系的创建
Ø 默认值的关联
Ø 索引的创建
n 常规索引
n 全文索引
Ø 数据的规划
三、表的创建
• 图形方式
n 企业管理器(Enterprise Manager)
• 代码方式
n 查询分析器(Query Analyzer)
四、创建表
create table 工资表
(
职工编号 varchar(10)
constraint pk_工资表 primary key 主键约束
constraint fk_工资表_职员表 foreign key references 职员表（编号） 外键约束
on delete cascade on update cascade, 级联更新，级联删除
工资 smallmoney
constraint ck_工资表 check（工资>0） 检查约束
constraint df_工资表_工资 default 1000 默认值
)
五、数据操作
1、数据插入Insert
语法：insert into <table_name> values ( <values>)
2、选择性的将一个表中的数据添加到另一个表中
语法：insert into <table_name1> select <column_list> from <table_name2>
例如：insert into VipUser select username from users where rank=’VIP’
3、更新数据Update
语法：update <table_name> set <column_name=value> [ where 条件 ]è条件可有可无
4、删除数据Delete
语法：delete from <table_name> [ where 条件] è条件可有可无
5、使用Truncate table 删除表中的所有数据
六、约束的管理
1、 主键约束
Primary Key
注意：
Ø 约束可以在建表时创建，也可修改表结构时创建
Ø 创建Primary Key约束时，系统讲自动为该约束字段创建索引
Ø Primary Key约束如果不命名，系统将自动给定约束名:“PK_表名_8位标志符”
Ø 定义语法：Constraint 约束名 Primary Key Cluster/NonCluster 字段集
Ø 系统默认为PK约束创建Cluster索引
•
2、外键约束
• Foreign Key
注意：
Ø 约束可以在建表时创建，也可修改表结构时创建
Ø 定义语法：
Constraint 约束名 Foreign Key
References 引用表（引用字段集）
On Delete Cascade/No Action
On Update Cascade/No Action
Ø 如果使用NO Action，系统不允许删除被引用的数据
七、规则的创建和管理
1、创建规则
• Create Rule 规则名 As 条件表达式
例：Create Rule Rule_Age As @T>=15 And @T<=30
2、绑定规则/取消绑定
• Exec Sp_BindRule ‘规则名’,‘字段’
例：Exec Sp_BindRule ‘Rule_Age’,’Stu_Age’
• Exec Sp_UnBindRule ‘字段’
例：Exec Sp_UnBindRule ’Stu_Age’
注意：
• 一个字段只可以和一个规则绑定，一个规则可以和多个字段绑定
八、默认值的创建和管理
1、创建默认值
• Create Default 默认名 As 常数表达式
例：Create Default Def_Stat As ‘中国’
2、绑定默认值/取消默认值
• Exec Sp_BindDefault ‘默认名’,‘字段’
例：Exec Sp_BindDefault ‘Def_Stat’,’Stu_Stat’
• Exec Sp_UnBindDefault ‘字段’
例：Exec Sp_UnBindDefault ’Stu_Stat’
注意：
• 一个字段只可以和一个默认绑定，一个默认可以和多个字段绑定
九、表结构的调整
1、字段定义的修改
Alter Table 表名 Alter Column 列名列定义
2、添加字段
Alter Table 表名 Add 列名 列定义
3、删除字段
Alter Table 表名 Drop Column 列名
注意：
• 添加字段的时不允许将字段属性设置为“Not Null”
•
4、添加约束
Alter Table 表名 Add Constraint 约束名 约束定义
5、删除约束
Alter Table 表名 Drop Constraint 约束名
注意：
Ø 在删除Primary Key,Unique Key约束的时候，系统将自动删除相应的Index
Ø 如果约束没有命名，要删除必须用修改字段定义的方式
6、删除表
Drop Table 表名
注意：以下两种情况无法删除表：
（1） 表被占用
（2）表有被引用关系（作为外键的主表）
第三部分习题样例
1、 创建具有关系的表并进行合理的数据规划
创建城市表，包含：
城市编码 5
城市名称 20
邮政编码： 6
use T607
create table City
(
CityCode varchar(5) constraint pk_city_citycode primary key,
CityName varchar(20),
Postalcode varchar(6)
)on G_Normal
2、创建学生表：
学生编码 固定13位
学生姓名 不为空，不重复
学生性别 F/M
学生年龄 15－25
学生生日
学生籍贯
学生国籍
学生照片
根据实际需要设计相应的关系和约束，同时，FK约束为同步更新方式
create table Student
(
StuCode char(13) constraint pk_student_stucode primary key,
StuName varchar(10) not null constraint uk_student_stuname unique,
StuSex char(2) check(StuSex in('F','M')),
StuAge smallint check(StuAge>=15 and StuAge<=25),
StuBirthday smalldatetime,
StuNativePlace varchar(5) constraint fk_student_city foreign key references City(CityCode)
on delete cascade on update cascade,
StuState varchar(20),
StuImage image
)
on G_Change
textimage_on G_Big
3、 将学生年龄的约束制定成规则并绑定
create rule rule_stu_age as @T>15 and @T<=25
exec sp_bindrule 'rule_stu_age','Student.StuAge'
4、 将学生编码的固定长度约束制定成规则并绑定
drop rule rule_stu_code
create rule rule_stu_code as len(@length)=13
exec sp_bindrule 'rule_stu_code','Student.StuCode'
5、 将国籍设置默认并绑定
create default def_stu_state as '中国'
exec sp_bindefault 'def_stu_state','Student.StuState'
insert into Student values('4534567891234','jack','m',24,'2005-12-14','0592','','0000')
6、 添加一个字段：地址 50 不允许为空
alter table Student add Address varchar(50)
alter table Student alter column Address varchar(50) not null
7、 删除刚才添加的字段：地址
alter table Student drop column Address
8、 修改学生编码中的主键索引为非聚集索引，将学生姓名所产生的索引设置成为聚集索引
alter table Student drop constraint pk_student_stucode--删除约束
alter table Student add constraint pk_student_stucode primary key nonclustered(StuCode)
alter table Student drop constraint uk_student_stuname
alter table Student add constraint uk_student_stuname unique clustered(StuName)
第四部分：查询和视图管理
一、 SQL Server查询语句
Select Distinct Top n (Percent) 字段 Into 表 From 表集 Where 条件集合
Group By 分组字段 Having 二次筛选条件
With Rollup/Cube Order By 排序集
注意：
Ø Distinct后可以加字段，也可以加“*”，表示消除完全相同的记录
Ø “Into 表”子句中的目标表必须是数据库里不存在的表，如果存在，则报错
Ø “Rollup”将对Group By的第一个字段进行汇总，“Cube”将对Group By的所有字段进行汇总
二、查询语法
1、使用WHERE 子句查询
select * from <table_name> where <条件>
2、使用ORDER BY 子句查询
ORDER BY 是对查询结果进行排序，ASC是升序，DESC是降序。
例如：SELECT * FROM authors ORDER BY age ASC
3、使用AS子句命名列
SELECT u_name AS 用户名 FROM user
4、使用TOP子句限制查询返回值
SELECT TOP num <column_name> FROM <table_name>
三、集合函数和分组查询
1、聚合函数
（1）SUM：求和
（2）AVG：求平均值
（3）COUNT：统计
（4）MAX：最大值
（5）MIN：最小值
2、使用GROUP BY进行分组
例：统计各用户的发帖数
select username, count (* ) from tb_Spenk group by username
其中，tb_Speak是发贴表，username是用户名
3、使用HAVING子句选择行，HAVING用于筛选分组汇总后的行。
例：统计用户的发帖数大于50帖的用户
select username, count (* ) from tb_Spenk group by username having count (*)>50
其中，tb_Speak是发贴表，username是用户名
4、某条件值为NULL 时，应用IS NULL。
5、模糊查询：LIKE
四、约束的命名规则：
1、 主键：PK_表名
2、 外键：FK_主表名_从表名
3、 检查约束：CK_字段名
4、 默认值：DF_表名_字段名
五、SQL Server的集合操作
Union方式
Select SQL Statement
Union (All)
Select SQL Statment
前提条件：
Ø 各SQL语句结果集的字段个数相同，并类型兼容
Ø 各SQL语句有效
结果集特点：
Ø 列名无效，用列序号描述列
Ø 结果集中自动执行DISTINCT动作取消重复
•
六、视图的概念
1、视图
命名的SQL语句，一种从多表取得数据的方法，无数据实体，定义部分存放在MASTER数据库中
优点：
Ø 提供列级的安全保证
Ø 隐藏数据的复杂性
Ø 简化查询语句
Ø 保存复杂的SQL语句
Ø 基表和应用程序的逻辑分离
2、视图分类
Ø NonJoin View
Ø Join View
Ø Partition View
3、 视图的执行步骤
Ø 针对视图的查询语句和定义语句合并产生目标SQL语句
Ø 通过SQL JET编译目标SQL后得到二进制信息和执行计划
Ø 对列进行必要的索引替换
Ø 读取数据到Data_Buffer中
4、视图的创建
Create View 视图名With Encryption/schemabinding As SQL Statement With Check Option
为保证向视图中插入的数据能够体现出来，强制要求向视图中插入的数据满足视图定义语句的Where子句条件。
5、视图的创建的注意事项
Ø Master中保存视图的定义
Ø 建视图的用户必须具备创建视图（Create View）和 访问视图所引用的基表的访问权限
Ø 视图为非独立实体，必须依赖与其他的实体，其依赖性由SQL Server自动维护
Ø 视图不可以引用临时表(TEMP数据库中，以##开头）
Ø 不能给视图字段绑定规则和默认值
Ø 不能在视图上创建触发器
Ø 不可以给视图创建全文索引，但可以创建一般索引
Ø 一般不建议向视图中插入数据，因为视图中只包含表的部分数据，插入动作可能违反表的约束
6、视图的查询
SysObjects 存放视图的基本信息
SysColumns 存放视图的定义列
SysDepends 存放视图的依赖关系
SysComments 存放视图的定义文本
-----------------------------------------------------------
Sp_HelpText 视图名 显示视图的定义语句
Sp_Depends 视图名 显示视图的依赖关系
7、视图的删除
Drop View 视图名
第四部分习题样例
/*普通会员
M_Code M_Name M_IDCard M_Tel */
use T607
create table Member
(
M_Code varchar(10) constraint pk_member_code primary key
on G_Index,
M_Name varchar(10) not null,
M_IDCard varchar(18) not null constraint uk_member_idcard unique,
M_Tel varchar(13)
)
on G_Normal
drop table Member
/*
VIP
V_Code V_Name V_IDCard V_Telephone V_Address
*/
create table Vip
(
V_Code varchar(10) constraint pk_vip_code primary key
on G_Index,
V_Name varchar(10) not null,
V_IDCard varchar(18) not null constraint uk_vip_idcard unique,
V_Telephone varchar(13),
V_Address varchar(30)
)
on G_Normal
/*
书籍
编号 书名 作者 类型 单价
*/
create table Book
(
B_Code varchar(15) constraint pk_book_code primary key
on G_Index,
B_Name varchar(30) not null,
B_Author varchar(10),
B_Type varchar(10),
B_Price float
)
on G_Change
/*罚单
罚单号 记录号 会员号 罚款金额*/
create table Amerce
(
A_Code varchar(10) constraint pk_amerce_code primary key
on G_Index,
A_NoteCode varchar(10) not null,
A_MemberCode varchar(10) not null,
A_Money smallmoney
)
on G_Change
/*
记录
记录号 会员号 书籍编号 借书日期 还书日期
*/
create table Note
(
N_Code varchar(10) constraint pk_note_code primary key
on G_Index,
N_MemberCode varchar(10) not null,
N_BookCode varchar(15) not null,
N_BorrowDate smalldatetime not null,
N_ReturnDate smalldatetime
)
on G_Change
/*
1、对书籍进行类别统计，并同时计算出每个类别书目的总数；*/
insert into Book values('b1','oracle','谭浩强','计算机',53)
insert into Book values('b2','sql server','谭浩强','计算机',42)
insert into Book values('b3','java','谭浩强','计算机',48)
insert into Book values('b4','一个好人','小也','文学',25)
select B_Type as 类别,count(*) as 总数 from Book group by B_Type
/*2、找出借过”oracle”这本书的所有会员的联系电话？*/
insert into Member values('M001','kenny','123456789123456789','0592')
insert into Vip values('V001','jacky','987654321987654321','0592','xm')
insert into Note values('N001','M001','b1','2006-02-06','2006-05-26')
insert into Note values('N002','V001','b1','2006-05-06','2006-07-26')
insert into Note values('N003','V001','b1','2006-07-06','2006-08-26')
insert into Note values('N005','V001','b1','2006-07-06','2006-08-26')
insert into Note values('N004','V001','b4','2006-07-06','2006-08-26')
insert into Note values('N013','V001','b1','2006-01-06','2006-08-26')
insert into Note values('N006','V001','b1','2006-12-06','2007-08-26')
insert into Note values('N007','V001','b1','2006-03-06','2006-08-26')
insert into Note values('N008','V001','b1','2006-04-06','2006-08-26')
insert into Note values('N009','V001','b1','2006-06-06','2006-08-26')
insert into Note values('N010','V001','b1','2006-08-06','2006-08-26')
insert into Note values('N011','V001','b1','2006-10-06','2007-08-26')
insert into Note values('N012','V001','b1','2006-11-06','2007-08-26')
insert into Note values('N014','V001','b1','2006-09-06','2007-08-26')
select distinct Member.M_Name as 姓名,Member.M_Tel as 电话 from (Member
inner join Note on Member.M_Code=Note.N_MemberCode)
inner join Book on Note.N_BookCode=Book.B_Code
where Book.B_Name='oracle'
union all
select distinct Vip.V_Name,Vip.V_Telephone from(Vip
inner join Note on Vip.V_Code=Note.N_MemberCode)
inner join Book on Note.N_BookCode=Book.B_Code
where Book.B_Name='oracle'
/*3、查看在7月份作者“谭浩强“没被人借过的书有哪些？*/
select B_Name as 书名,B_Author as 作者 from Book
left join Note on Book.B_Code=Note.N_BookCode and month(N_BorrowDate)=7
where B_Author='谭浩强' and N_Code is null
/*4、找出借书册数<50本的普通会员资料*/
select * from Member inner join
(select N_MemberCode,count(N_Code) as CountNumber from Note group by N_MemberCode) as BorrowBook
on Member.M_Code=BorrowBook.N_MemberCode
where BorrowBook.CountNumber<50
/*5、会员每次借书的平均时间是多少天? (借了没还的不计算)*/
select sum(datediff(day,N_BorrowDate,N_ReturnDate))/count(N_Code) as 平均天数
from Note where N_ReturnDate is not null
/*6、查看每个月份每个作者的书被借的情况*/
select BorrowDate as 月份,B_Author as 作者,count(*) as 借阅总数 from
(select Book.B_Author,Book.B_Code,Month(N_BorrowDate) as BorrowDate
from Book inner join Note on Book.B_Code=Note.N_BookCode) as B
group by B.BorrowDate,B.B_Author
/*7、备份书籍表中价格大于40的所有书的书名、作者和价格并注明备份日期。*/
select B_Name,B_Author,B_Price,getdate() as BackupDate into BookBackup
from Book where B_Price>40
select * from BookBackup
/*8、每个月都来借书的那些人最后一次借的最多的书是哪一本?*/
select * from Book where B_Code=(
select top 1 N_BookCode
from Note inner join
(select N_MemberCode,max(N_BorrowDate) as mtime from Note
where N_MemberCode in
(select N_MemberCode from
(select distinct cast(year(N_BorrowDate) as varchar(4))+cast(month(N_BorrowDate) as varchar(2)) as month_list,N_MemberCode
from Note) as B
group by N_MemberCode
having count(N_MemberCode)=(select datediff(month,min(N_BorrowDate),max(N_BorrowDate)) as monthcount from Note))
group by N_MemberCode) as x
on Note.N_MemberCode=x.N_MemberCode and Note.N_BorrowDate=x.mtime
group by N_BookCode
order by count(*) desc)
/*9、查看会员”V001”平均多少天来借一次书?*/
select N_MemberCode as 会员编号,datediff(day,min(N_BorrowDate),max(N_BorrowDate))/count(N_Code) as 平均借书天数
from Note
group by N_MemberCode having(N_MemberCode='V001')
/*10、找出借出的书籍数量相差超过50的月份有几对?*/
select * from Note
select count(A.月份) as 月份总对数 from
((select month(N_BorrowDate) as 月份,count(N_Code) as countMonth from Note group by month(N_BorrowDate)) as A
left join
(select month(N_BorrowDate) as 月份,count(N_Code) as countMonth from Note group by month(N_BorrowDate)) as B
on A.月份<>B.月份 and B.月份 is not null)
where (A.countMonth-B.countMonth>=50) or (B.countMonth-A.countMonth>=50)
/*11、最后一次是借计算机书的人有哪些*/
select A.N_MemberCode,max(A.N_BorrowDate) as LastTime from
(select N_MemberCode,N_BorrowDate from Book,Note
where Note.N_BookCode=Book.B_Code and B_Type='计算机') as A
group by A.N_MemberCode
/*12、查询3月借书量超过50册的所有VIP和普通会员资料*/
select * from Member
select * from Vip
select M_Code as 会员号,M_Name as 姓名,M_IDCard as 身份证号,M_Tel as 电话 from Member
inner join
(select N_MemberCode,count(N_Code) as Num from Note where Month(N_BorrowDate)=3 group by N_MemberCode)
as A
on Member.M_Code=A.N_MemberCode
where A.Num>=50
union
select V_Code,V_Name,V_IDCard,V_Telephone from Vip
inner join
(select N_MemberCode,count(N_Code) as Num from Note where Month(N_BorrowDate)=3 group by N_MemberCode)
as B
on Vip.V_Code=B.N_MemberCode
where B.Num>=50
第五部分：高级查询
一、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角色的管理
|
拒绝连接 |
Ø 服务器角色
Ø 数据库角色
Ø 自定义角色
•
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 |
二、编程体系中的主要元素
² 常量和变量（局部和全局）
² 数据类型（系统和自定义）
² 流程控制
² 过程和函数
三、 使用变量
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 |
季度 |
|
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
|
代码返回值 |
使用：
declare @sum 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
|
区别所在 |
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
