SQL Server 2008 高级复习大纲

一、 第一章 数据库的设计
1. 规范的数据库设计：
1) 起因：当数据量较大时，设计的表多，表与表之间的关系复杂；
2) 数据库设计：数据库设计实际上就是规划和结构化数据库中的数据对象以及这些数据对象之间关系的过程；
3) 设计数据库好处：
a) 执行效率高；
b) 使应用程序便于开发；
c) 容易进一步发展；
d) 良好的数据库结构，是数据库系统效率和可靠性的重要决定因素；

2. 实体-关系（E-R）数据模型
1) 概念：Entity-Relationship data model，即：实体-关系数据模型；在1976年由P.Chen提出；E-R数据模型不是面向现实，而是面向现实世界的；
2) 详述：数据模型只包含那些对描述现实世界有普遍意义的抽象概念；在数据模型中，抽象是必须的，模型就是抽象的产物；
3) 实体 (Entity) :
a) 概念：可以相互区别而可以被人们认识的事、物、概念等统统抽象为实体；
b) 注意：在E-R 数据模型中，也有型与值之分；实体集可以作为型来定义，而每个实体可以是它的实例或值；
4) 属性(Attribute)：
a) 概念：实体一般具有若干特征，称之为实体的属性；
b) 注意：属性的取值在E-R数据模型中称为值集；value set
c) 提示：属性对应表中的列；
5) 关系(Relationship)：
a) 理解：实体之间有各种关系，好像人与人之间的关系；实体与实体之间的关系抽象为联系；
b) 提示：实体有属性，关系也有属性；
3. PowerDesigner 中设计的数据模型：
1) 概念数据模型；CDM
2) 物理数据模型；PDM
3) 业务模型；BPM business process model
4) 面向对象模型 OOM object oriented model
4. 数据库设计步骤：
1) 主要阶段：需求分析、概要设计、详细设计、编码实现、测试、部署；
2) 提示：关系型数据库一个最显著的特点是它能够关联数据库中各个实体的相关信息；
3) E-R图中实体、属性和关系：
a) 用矩形表示实体 -----实体一般是名词；
b) 用椭圆表示属性 -----属性一般也是名词;
c) 用棱形表示关系 ---关系一般是动词；
d) 直线用来连接实体集与属性，同时也用来连接实体集与关系；
e) 直线上的箭头用来表示实体集之间的映射基数；
4) 映射基数：映射基数表示可以通过关系与该实体关联的其他实体的个数；
a) 一对一； 例如：客户与资金账户 一一对应关系
b) 一对多； 例如：操作员管理基金 一对多关系
c) 多对一； 例如：多基金被一个操作员管理 多对一
d) 多对多； 例如：学生与老师之间
5) 将E-R图转换为表：步骤如下
a) 将各实体转换为对应的表，将个属性转换为表中对应的列；
b) 标识每张表中的主键；
c) 将实体之间的关系转换为表与表之间的主外键关系；

5. 数据库设计规范化：
1) 好的数据库设计满足的条件：
a) 尽可能较少数据冗余；
b) 没有插入异常；
c) 没有删除异常；
d) 没有更新异常；
2) 规范设计：
a) 第一范式：1NF：每个属性必须是不可再分的简单项，不能是属性组合，即属性的取值必须是不可拆分的原子值；
b) 第二范式：在第一范式的基础上，确保表中的每列都和主键相关；(规则：1、满足第一范式；2、确保每列都必须依赖主键；)
c) 第三范式：满足第二范式，并且：确保每列都和主键直接相关，而不是间接相关；
3) 保持数据 ” 规范 ”
a) 提示：规范化是一个理论，仅此而已；
b) 概念：数据库设计的最终原则不是规范化。设计最合适实际应用需要的数据库才是数据库的设计原则；


二、 第二章 Transact-SQL 编程：
1. 简介：T-SQL语言用于管理SQL Sserver数据库引擎实例，创建和管理数据库对象，以及插入、检索、修改和删除数据；满足国际标准：支持：变量的定义、输出、逻辑控制语句，称为T-SQL 编程；
2. 变量：可以存储数据值的对象
1) 局部变量：
a) 概念：用户自定义的变量，作用范围仅在程序内部；
b) 声明语法：declare @变量名 变量类型,@变量名 变量类型….
c) 赋值语法：select @局部变量 = 变量值
d) 赋值语法：set @局部变量=变量值
e) 作用：保存临时数据、传递数据；
f) 提示：使用print 进行打印：print ‘dd’
2) 全局变量：
a) 概念：SQL Server 系统内部使用的变量，作用范围任何程序随时调用；不是用户的程序定义的，他们是在服务器级定义的，只能使用预先说明及定义的全局变量；
b) 使用方式：引用全局变量必须以@@开头
c) 例子：@@error ，该变量的含义：返回执行上一个T-SQL语句的错误号；
3) 输出语句：
a) 概念：T-SQL支持输出语句，用于输出处理的数据结果；
b) 语法1：print 变量或表达式
c) 语法2：select 变量或表达式
d) 提示注意：print 命令想客户端返回一个结果的字符串信息，如果变量值不是字符串的必须转换为字符串：
e) 字符串转换语法：convert(转换后的类型，变量)
f) 例如：print ‘’+convert(varchar(10),@@IDENTITY)
4) 逻辑控制语句
a) If-else:if 条件表达式 begin end else begin end ; 注：begin…end 相当于java中的{}
b) While…continue…break:
语法：while<条件表达式>
Begin
<命令行或语句块>
[Break]
[continue]
[语句块]
End
c) Case:语法如下
Case
When <条件表达式> then <运算式>
When <条件表达式> then <运算式>
Else <运算式>
End
d) 批处理：包含一个或多个T-SQL语句的组，从应用程序一次性的发送到SQL Server执行；批处理被编译成一个可执行单元，称为执行计划，执行计划中的语句每次执行一条；Go 关键字标识这批处理的结束；


三、 第三章 SQL 高级查询
1. 简介：高级查询技术包括：嵌套子查询、聚合技术、排序函数、集合运算符和公式表达式；
2. 嵌套子查询：
1) 子查询简介：任何使用表达式的地方都可以使用子查询；子查询也称为内部查询或内部选择，包含子查询的语句称为外部查询或外部选择；
2) 子查询的特点和优势：
a) 使用灵活：可以作为条件、临时表、列进行使用；
b) 降低SQL语句的复杂度，提高SQL语句的可读性；
3) 子查询作为条件使用：
a) 提示：在查询条件中使用， > < = 符号后的子查询的结果只能有一个值；
4) 子查询作为临时表使用：
5) 子查询作为列使用：
6) 使用in 和 not in 完成子查询：
a) In 和 not in 通常在where 子句中使用，在in 和 not in 后接的子查询中，可以有多个值出现；
7) 使用exists 和not exists 完成子查询：
a) 概念：exists 和 not exists 表示存在和不存在的意思；
b) 用法：在语句中判断exists 后接的子句是否存在；
8) 使用some any all 进行子查询：
a) 提示：在SQL 中 some any all 后面必须跟的是子查询;
b) All：表示的是能够满足all所接的子查询中的所有值才成立；即：大于最大值
c) Any:至少大于一个值，即：大于最小值；

3. 聚合技术：
1) 使用COMPUTE 和 COMPUTE BY 进行汇总查询
a) 优势：查看明细，查看汇总；
b) 理论：使用COMPUTE 进行汇总计算后的查询得到两个结果集，第一个结果集返回查询语句前面的明细，后一个结果集返回汇总的结果；
c) COMPUTE 子句需要的信息：
A. 可选BY 关键字，它基于每一列计算指定的行聚合；
B. 行聚合函数名称；sum avg min max count
C. 要对其执行行聚合函数的列；
d) 例如：select * from stumarks order by subject compute avg(score) by (subject) 分组汇总；
e) 注意：在使用COMPUTE BY 进行分组计算时要注意COMPUTE BY 的分组依据要与主查询的排序（ORDER BY） 对应；

4. 排序函数：
1) 起因：在应用程序中需要对查询结果进行排序并且需要给出排序的序号；
2) ROW_NUMBER函数：
a) 特点：没有并列编号，不跳空编号；
b) 例子：select ROW_NUMBER() over(order by score desc)as 排名,s1.name,s2.score from stuinfo s1,marks s2 where s1.id = s2.id and ss2.subject=’java’
3) RANK 函数：
a) 特点：有并列编号，有跳空编号；
4) DENSE_RANK 函数：
a) 特点：有并列编号，不跳空；
5) 使用PARTITION BY 分组子句：
a) 用法：和排序函数联合使用，进行分组；
b) 例子：select ROW_NUMBER() over(partition by s2.subject order by score desc)as 排名,s1.name,s2.score from stuinfo s1,marks s2 where s1.id = s2.id and ss2.subject=’java’
6) 公式表达式：CET
a) 由来：查询过程中需要一些中间表，这些表会被反复的使用；解决这一问题使用公式表达式；
b) 用法：可以将公式表达式视为临时结果集；
c) 语法：WITH expression_name(列，列)
As
(
查询语句；
)
Select * from expression_name
Go
d) 注意：只有在查询定义中为所有结果列提供了不同的名称时，列名称列表才是时可选的；


四、 第四章 索引和视图
1. 索引：
1) 概念：索引就是数据表中数据和相应存储位置的列表；
2) 好处：利用索引可以提高在表或视图中查找数据的速度；
3) 提示：SQL Server 中，可管理的最小空间是页，一个也占有8kb的存储空间；
4) 索引的类型：聚集索引和非聚集索引，他们是数据库引擎中索引的基本类型；
a) 聚集索引：
A. 概念：指表中数据行的物理存储顺序与索引顺序完全相同；
B. 特点：查找使用聚集索引组织的数据，就像汉语词典中的按拼音查找；
C. 注意：由于聚集所以根据聚集索引的引用顺序物理地重新排列用户插入的数据，那么，每个表只能创建一个聚集索引；
D. 创建列：经常被搜索的列或按顺序访问的列；默认情况下为主键约束创建聚集索引；
b) 非聚集索引：
A. 概念：不改变表中数据行的物理存储位置，数据与索引分开存储，通常索引指向的地址与表中的数据发生关系；
B. 特点：使用非聚集索引组织的数据，就像在辞典中使用偏旁部首查字法一样；
C. 注意：非聚集索引没有改变表中物理行的位置；最多可以创建249个；
D. 使用时机：一、字段的数据唯一性比较高；二、查询所得到的数据量比较少；
c) 唯一索引：索引键都不相同；提示：聚集和非聚集都是唯一索引；
d) 包含性列索引：
e) 视图索引；
f) XML索引；
g) 全文索引；

5) 创建索引：
a) 语法：create [unique][clustered | nonclustered] index index_name on table_name(column_name) [with fillfactor=x]
b) 参数说明：[unique] 创建唯一索引； clustered 聚集索引，反之；
c) 用法：select * from t_name with(index=IX_score) where……
d) 注意：其实索引创建后并不需要使用，系统会自动优化查询；
e) 适合创建索引的列：某列频繁的被用于数据搜索时，或者该列用于对数据进行排序时可以创建成索引；
f) 不适合创建索引的列：列中仅有几个不同的值，或者表中仅包含几行值；例如：性别列

2. 视图：
1) 概念：视图是一个虚拟的数据表；没有真实的数据在视图中存储；
2) 创建视图准则：
a) 遵循命名规范；
b) 视图嵌套不超过32层；
c) 规则和default定义不能与视图关联；
d) 定义视图的查询中不能包含：compute by 子句，into 关键字
e) 不能包含order by 子句，除非在select 语句中有一个top子句；
3) 语法：create view view_name(别名，查询列对应的名称) as <查询语句>
4) 加密视图：create view view_name with ENCRYPTION as 语句 go


五、 第五章 事务和游标
1. 事务的特点：
1) 由来：需要把多个步骤的指令当做一个整体来运行，这个整体要么全部成功，要么全部失败；
2) 特点：基于整体指令的特殊执行方式，可使用事务；
a) 原子性：事务内的所有工作要么全部成功，要么全部失败，不肯能部分成功或失败；
b) 一致性：事务内的任何操作不能违反约束或规则，事务完成时所有内部数据结构必须是正确的；
c) 隔离性：事务之间相互隔离；一个事务不可能遇到另一个事务的中间状态；
d) 持久性：事务完成之后，对数据库的影响是持久的，即使系统错误重启后，该事务的结果依然存在；
2. 事务的模式： 数据库系统中事务的执行方式
1) 显示模式：用于明确定义begin transaction 和结束：commit transaction或rollback transaction的事务；
2) 自动提交事务：与SQLServer 建立链接后，系统自动进入自动事务模式；
3) 隐式事务：隐式事务指当事务提交或回滚后，SQL Server 自动开始的事务；
3. 事务的处理：
1) Begin transaction 语句：标记一个本地事务的开始；
2) Commit transaction 语句：用于提交事务，标记一个显示事务或隐式事务的结束；
3) Rollback transaction 语句：用于事务回滚，在发布一条rollback transaction 语句时，SQL Server 将会抛弃自最近一条begin transaction语句以后的修改；

4. 游标：
1) 简介：游标实际上是一种能从多条数据记录的结果集中每次提取一条记录的机制；
2) 使用游标的好处：
a) 允许定位到结果集中特定行；
b) 从结果集中的当前位置检索一行或多行数据；
c) 支持对结果集中当前位置的行进行修改；
3) 游标的种类：
a) T-SQL游标：主要用在T-SQL脚本、存储过程和触发器中；(服务器游标)
b) API游标：在服务器上实现(服务器游标)
c) 客户端游标；
4) 游标的基本操作：定义游标、打开游标、循环读取游标、关闭游标、删除游标：
a) 定义游标基本语法：
Declare cursor_name 游标名
Cursor [local | global] 范围
[ forward_only | scroll] 滚动方式
[read_only | scroll_locks | optimistic] 读取方式
For select statements 查询语句
[for update[of column_name]] 可更改字段；（不设置默认所有）
b) 打开游标：游标定义后必须打开游标才能使用：open dursor_name
c) 提示：游标打开后可以使用全局变量@@cursor_rows显示游标内的记录行数；
d) 检索记录：使用fetch来检索游标中的数据
A. 例如：fetch first from cursor_name into 变量，变量
B. 解释：读取游标中第一行的数据并给变量赋值；
C. Fetch next ：提取上次提取的行的下一行；
D. Fetch prior：提取上次提取行的前一行；
E. Fetch last ：提取最后一行；
F. Fetch absolute n: 提取指定行，如果n为负数则冲最后一行为起点；
G. Fetch relative n: 提取以上次提取行为基础的第几行；
e) 关闭游标：close cursor_name;
f) 删除游标：deallocate cursor_name
g) 提示：@@fetch_status的值表示fetch执行不同的状态：
A. 0 ：fetch执行成功；
B. -1 ：fetch执行失败或此行不在结果集中；
C. 02：被提取的行不存在；


六、 第六章 存储过程
1. 存储过程的概念：
1) 存储过程(Procedure)是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过给定的存储过程的名字并给出参数来执行它；
2) 提示：存储过程中可以包含逻辑控制语句 和数据操控语句，它可以接受参数、输出参数、发挥单个或多个结果集的返回值；
3) 注意：存储过程的创建是在数据库服务器上进行编译并存储在数据库中；
4) 存储过程的优点：
a) 存储过程允许标准组件式编程；
b) 存储过程能够实现较快的执行速度；(存储过程是预编译的)
c) 存储过程能减少网络流量；
d) 存储过程可以作为一种安全机制来充分利用；

2. 系统存储过程：
1) 概念：就是系统创建的存储过程；
2) 目的：能够方便地从系统表中查询信息或完成于更新数据库表相关的管理任务或其它系统管理任务；
3) 语法：系统存储过程存储在master数据库中，并以”sp_”为前缀；
4) 案例：
a) exce sp_database
b) exce sp_tables
c) exce sp_helpindex 表 查看表的索引
5) 注意：系统存储过程中还用一些常规扩展存储过程：xp_cmdshell，它可以完成dos命令下的一些操作；
a) 语法：xp_cmdshell{‘command_string’}{,no_output}
b) Command_string为命令字符串，no_output可选参数，设置执行命令后是否输出返回信息；
c) 案例：exec xp_cmdshell ‘md d:\back’,no_output

3. 用户定义的存储过程：
1) 语法：创建不带参数的存储过程：
Create {proc | procedure} 存储过程名
[
`{@参数1 参数类型} [=默认值] [output],
{@参数2 参数类型} [=默认值] [output],

{@参数n 参数类型} [=默认值] [output]

………
]
As sql 语句
2) 创建带输入参数的存储过程：
a) 输入参数：调用向存储过程传递实际数值，用来想存储过程传入值；
b) 输出参数：如果希望参数的值可以带出方法，则使用输出参数，通过定义参数时在其后面加上output标记，表名该参数时输出参数；
c) 例如：create proc proc_user @name varchar(20) as 语句 go exec proc_proc_user ‘传递的值’
3) 创建带默认参数的存储过程：
a) 在调用存储过程时，有些参数的值并不是总是发生变化的，这个时候可以给这些参数一个默认值，方便调用；
b) 注意：参数有默认值的存储过程，调用该参数时，可以不给该参数传值，也可以给该参数传值；
c) 例如：
d) Create proc proc_user @name varchar(4), @age int=12 as 语句 go
e) 不传值调用：exec proc_user ‘李四’
f) 传值调用：exec proc_user ‘李四’,@age=28 (需要指明是那个参数的默认值传值)
4) 创建带输出参数的存储过程：
a) 输出参数必须使用变量；
b) 如果要获得输出参数的值，在调用的时候说明该参数为输出参数，在后面加output;
c) 输出参数同时也是输入参数，调用时也可以给参数赋值；
5) RAISERROR :
a) 返回用户定义的错误信息时，课指定严重级别，设置系统变量记录所发生的错误；
b) 语法：raiserror(错误消息，严重级别，状态)
c) 例如：raiserror(‘错误提示信息’,15,2)


七、 第七章 触发器
1. 问题引入：例：往订单里面插入新数据后，自动的触发一个动作：即修改库存信息；
2. 概念：触发器是对表进行插入、更新、或者删除的时候回自动执行的特殊存储过程。
3. 提示：触发器本身是一个事务；触发器是一种特殊的存储过程，它是自动执行的；并支持事务的特征，能再多个表之间执行特殊的业务规则以满足实际需要；
4. 触发器与存储过程的区别：触发器是裆对某一个表进行操作诸如：update insert delete这些操作时；
5. 分类：DML触发器 和 DDL 触发器；
6. DDL触发器：
1) Create /alter/drop 开头的语句；
7. DML 触发器：
1) After触发器：
a) Insert 触发器
b) Update 触发器
c) Delete 触发器
2) Instead of 触发器：
a) 介绍：该触发器表示并不执行其所定义的操作，而仅是执行触发器本身；
b) 使用地方：可以再表上定义，也可以在视图上定义；
3) After触发器详解：
a) After触发器中有两个非常特殊的表：这两张表称为逻辑表，也可以称虚拟表；
A. 插入表（inserted）
B. 删除表(deleted)
b) 存放位置：内存；
c) 属性：只读，不能修改数据；
d) 结构：两个表的结构总是与被该触发器作用的表有相同的表结构；
e) 数据值：因用户操作而被影响到的原数据或新数据值；
f) DML操作对应临时表的值，如下：
对表的操作 Inserted逻辑表 Deleted逻辑表
增加记录(insert) 存放增加的记录 无
删除记录delete 无 存放被删除的记录
修改记录update 存放更新后记录 存放更新前记录
g) 注意：如果创建的After触发器表上有约束，则执行insert update delete 触发器时会先检查约束，如果不满足要求则不执行触发器；
4) 如何创建触发器：
a) T-SQL 语法如下：
Create trigger trigger_name
On table_name
With encryption
For update/delete/insert
As
T-SQL语句
Go

b) 注意：创建触发器的时候必须是批处理的第一条语句；
c) Set nocount on 不显示T-SQL 语句的影响记录行数；
5) Update类型触发器：
a) 用途：主要是获取更新的数值，判断该数值是否满足一些特殊的约束；
b) 特点：系统中执行两次操作，先删除，后增加；
6) Delete 类型触发器：
a) 用途：可以用来对表删除之前进行备份；
b) 使用知识点：insert into 新表 select * from 旧表
c) Select * into 新表 from 旧表
7) 列级触发器：
a) 用途：用来检测用户是否对某些列进行了修改；
b) 用法：在update类型触发器中，使用update(列名)函数进行判断；
8) INSTEAD OF 类型的触发器：
a) 与After类型触发器的区别：After触发器是对表进行操作后触发的，而instead of 类型触发器并不执行定义的操作，而是仅执行触发器本身的内容；
b) 创建语法：
Create trigger trigger_name
On table_name
With encryption
Instead of update/delete/insert
As
T-SQL语句
Go

c) 用法：例如删除两个各表中有主外键关系的数据时；
d) 特点：使用instead of 触发器，可以直接删除主表中的数据，并且自动的删除从表中的数据，通过触发器达到级联更新的效果；