《SQL SERVER 高级应用》期末复习知识点.docx
《《SQL SERVER 高级应用》期末复习知识点.docx》由会员分享，可在线阅读，更多相关《《SQL SERVER 高级应用》期末复习知识点.docx（20页珍藏版）》请在冰豆网上搜索。
《SQLSERVER高级应用》期末复习知识点
《SQLSERVER2005高级应用》期末复习知识点TruncateTable
Truncate是SQL中的一个删除数据表内容的语句，用法是：
语法
TRUNCATETABLEname
参数
name
是要截断的表的名称或要删除其全部行的表的名称。
下面是对Truncate语句在MSSQLServer2000中用法和原理的说明：
Truncatetable表名速度快,而且效率高,因为:
TRUNCATETABLE在功能上与不带WHERE子句的DELETE语句相同：
二者均删除表中的全部行。
但TRUNCATETABLE比DELETE速度快，且使用的系统和事务日志资源少。
DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项。
TRUNCATETABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATETABLE删除表中的所有行，但表结构及其列、约束、索引等保持不变。
新行标识所用的计数值重置为该列的种子。
如果想保留标识计数值，请改用DELETE。
如果要删除表定义及其数据，请使用DROPTABLE语句。
对于由FOREIGNKEY约束引用的表，不能使用TRUNCATETABLE，而应使用不带WHERE子句的DELETE语句。
由于TRUNCATETABLE不记录在日志中，所以它不能激活触发器。
TRUNCATETABLE不能用于参与了索引视图的表。
对用TRUNCATETABLE删除数据的表上增加数据时，要使用UPDATESTATISTICS来维护索引信息。
如果有ROLLBACK语句，DELETE操作将被撤销，但TRUNCATE不会撤销。
1.SQLServer2005的登录方式。
2.SQLServer2005与其它数据库或EXCEL等软件之间的数据导入与导出。
3.SQLServer2005中的系统表。
4.设计数据库的三大范式。
1）仅有好的RDBMS并不足以避免数据冗余，必须在数据库的设计中创建好的表结构
2）DrE.F.codd最初定义了规范化的三个级别，范式是具有最小冗余的表结构。
这些范式是：
a）第一范式（1stNF－FirstNormalFromate）
b）第二范式（2ndNF－SecondNormalFromate）
c）第三范式（3rdNF－ThirdNormalFromate）
第一范式的目标是确保每列的原子性
如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）
如果一个关系满足第一范式，并且除了主键以外的其他列都依赖于该主键，则满足第二范式（2NF）
如果一个关系满足第二范式，并且除了主键以外的其他列都依赖，且直接于主键列，则满足第三范式（3NF）
好处：
三范式能够使数据结构达到高内聚，低耦合，这样：
有利于维护数据的完整性
减少数据冗余，节省存储空间
有利于相关应用程序的开发
总结：
设计数据库是软件工程开发的重要步骤
}设计数据库的步骤为
◦建模：
信息收集、绘制E-R图
◦模型转换：
用数据模型建表
◦规范化：
运用三范式
}第一范式（1NF）：
确保列的原子性
}第二范式（2NF）：
使每列都和主键关联
}第三范式（3NF）：
使每列都和主键直接关联
}三范式可以使我们的数据库：
◦有利于维护数据的完整性
◦减少数据冗余，节省存储空间
◦有利于相关应用程序的开发
5.设计数据库的E-R图，及E-R的绘制标准。
1.收集到相关信息后，我们需要使用某种方式将其表示和保存起来，便于相互沟通和改进
2.E-R图是一种广泛使用的设计工具，主要用来表示事物、事物的数据和其间的关系信息
3.E-R图将信息分为三种形式：
a）实体（Entity）：
可以用我们已知的对象一词去理解，若干个具有共同特性的实体称为实体集
b）属性（Attribute）：
它是一个数据，它表现为某个实体的一个特征，实体包含其作为成员数据
c）关系（Relationship）：
实体有大有小，某些实体的存在是建立在另一些实体之上的；某些实体的属性可能是指另一个实体。
这些就是关系
6.删除表对象。
7.变量的声明
1）局部变量：
a）局部变量必须以标记@作为前缀，如@age
b）局部变量的使用是先声明，再赋值
c）局部变量只在定义它的局部范围内有效
2）全局变量：
a）全局变量必须以标记@@作为前缀，如@@version
b）全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值
c）全局变量在整个SQL环境下都可以被访问或调用
●声明局部变量
DECLARE@变量名数据类型
●赋值
SET@变量名=值
SELECT@变量名=值
变量
含义
@@ERROR
最后一个T-SQL错误的错误号
@@IDENTITY
最后一次插入的标识值
@@LANGUAGE
当前使用的语言的名称
@@MAX_CONNECTIONS
可以创建的同时连接的最大数目
@@ROWCOUNT
受上一个SQL语句影响的行数
@@SERVERNAME
本地服务器的名称
@@TRANSCOUNT
当前连接打开的事务数
@@VERSION
SQLServer的版本信息
8.视图的概念与作用。
视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上
视图中并不存放数据，而是存放在视图所引用的原始表（基表）中
同一张原始表，根据不同用户的不同需求，可以创建不同的视图
}视图名称必须遵循标识符的规则，该名称不得与该架构包含的任何表的名称相同。
}SQLServer2005允许嵌套视图。
但嵌套不得超过32层。
视图最多可包含1024个字段。
}不能将规则或DEFAULT定义与视图相关联。
}定义视图的查询不能包含COMPUTE子句、COMPUTEBY子句或INTO关键字。
}定义视图的查询不能包含ORDERBY子句，除非在SELECT语句的选择列表中还有一个TOP子句
9.索引的概念与作用。
索引：
是数据表中数据和相应存储位置的列表．
利用索引可以提高在表或视图中查找数据的速度
在MicrosoftSQLServer系统中，可管理的最小空间是页．一个页占有8KB的存储空间
}索引主要分为两类：
◦聚集索引
◦非聚集索引
}其它类型的索引：
◦唯一索引
◦索引视图
◦包含性列索引
◦全文索引
◦XML索引等
聚集索引是指表中数据行的物理存储顺序与索引顺序完全相同
非聚集索引不改变表中数据行的物理存储位置，数据与索引分开存储，通过索引指向的地址与表中的数据发生关系
使用非聚集索引的情况
◆某个字段的数据唯一性较高
◆查询所得到的数据量较少
}适合创建索引的列
◦当数据表中的某一列被频繁的用于数据搜索时，或者该列用于对数据进行排序时可以创建成索引。
}不适合创建索引的列
◦如果列中仅有几个不同的值，或者表中仅包含几行值，则不推荐为其创建索引。
为小型表创建索引可能不太划算，因为对数据量较小的表来说，在SQLServer2005中使用索引搜索数据所花的时间比直接进行表扫描所花的时间更长。
10.子查询的使用方法及作用。
11.T-SQL编程中局部变量的定义。
12.T-SQL编程中常见全局变量的作用。
13.T-SQL编程中的IF……ELSE语法。
IF（条件）
BEGIN
语句1
……
END
ELSE
BEGIN
语句1；
……
END
14.T-SQL编程中的WHILE语法。
WHILE（条件）
BEGIN
语句1
语句2
……
BREAK
END
同Java语言一样：
●BREAK表示退出循环
●如果有多条语句，需要BEGIN-END语句块
15.T-SQL编程中的CASE语法。
CASE
WHEN条件1THEN结果1
WHEN条件2THEN结果2
……
ELSE其他结果
END
16.T-SQL编程中SET、SELECT对变量赋值。
SET@变量名=值
SELECT@变量名=值
17.T-SQL编程中PRINT、SELECT输出变量值。
PRINT变量或表达式
SELECT变量或表达式
}EXISTS和NOTEXISTS表示存在和不存在的意思。
}在语句中会判断EXISTS和NOTEXISTS后接的子句是否存在和是否不存在。
}NOTEXISTS的用法与EXISTS一样，唯一的区别就是意义相反。
在SQL查询中，SOME、ANY、ALL后必须跟子查询。
在SQL查询中,SOME和ANY的作用是一样的,表示其中的任何一项。
ALL则表示其中的所有的项。
使用了COMPUTE进行汇总计算后的查询得到了两个结果集，第一个结果集返回查询语句前面的查询明细，后一个结果集返回汇总的结果，我们也可以在COMPUTE子句中添加多个汇总计算表达式。
}COMPUTE子句需要下列信息：
◦可选BY关键字。
它基于每一列计算指定的行聚合。
◦行聚合函数名称。
包括SUM、AVG、MIN、MAX或COUNT。
◦要对其执行行聚合函数的列。
}在有些场景中我们需要对结果先进行分组，然后进行汇总计算。
这种情况下我们可以使用COMPUTEBY进行分组汇总查询。
}排序函数OVER（[分组子句]排序子句[DESC][ASC]）
}排序子句：
ORDERBY排序列,排序列…
}分组子句：
PARTITIONBY分组列，分组列…
}ROW_NUMBER函数生成的排序根据排序子句给出递增连续的序号
}RANK函数生成的排序根据排序子句给出递增的序号，但是存在并列并且跳空
}DENSE_RANK函数生成的排序根据排序子句给出递增的序号，但是存在并列不跳空
}根据上面三个函数，我们可以增加一个序列，但是有时候我们需要对数据进行分组，然后对分组后的数据进行增加序列，PARTITIONBY可以与以上三个函数联合使用
18.事务的特性、语法。
事务（TRANSACTION）是作为单个逻辑工作单元执行的一系列操作访问或修改数据库内容的程序的执行称为事务
这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行
事务是一个不可分割的工作逻辑单元
事务可以分为三种类型：
显式事务、自动提交事务、隐式事务
事务的ACID特性：
原子性、一致性、隔离性、持久性
事务必须具备以下四个属性，简称ACID属性：
◦原子性（Atomicity）：
事务是一个完整的操作。
事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
◦一致性（Consistency）：
当事务完成时，数据必须处于一致状态
◦隔离性（Isolation）：
对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
◦永久性（Durability）：
事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性
--开始一个事务
BEGINTRANSACTIONtran_bank--也可简写为begintrantran_bank
--定义一个用于记录错误的变量
DECLARE@tran_errorINT
SET@tran_error=0
--在三毛的账户减去
UPDATEbankSETcurrentMoney=currentMoney-10000
WHEREcustomerName='三毛'
SET@tran_error=@tran_error+@@error
--在小毛的账户增加
UPDATEbankSETcurrentMoney=currentMoney+10000
WHEREcustomerName='小毛'
SET@tran_error=@tran_error+@@error
IF@tran_error<>0
BEGIN
--执行出错，回滚事务
ROLLBACKTRANSACTION
PRINT'转账失账，交易已取消'
END
ELSE
BEGIN
--没有发现错误，提交事务
COMMITTRANSACTION
PRINT'交易成功，已保存新数据'
END
GO
}游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制
}使用游标，可以实现以下目标：
◦允许定位到结果集中的特定行。
◦从结果集的当前位置检索一行或多行数据。
◦支持对结果集中当前位置的行进行修改
}cursor_name：
游标名称．
}LOCAL|GLOBAL：
定义游标是全局还是局部游标．
}FORWARDONLY|SCROLL：
前一个参数，游标仅能向后滚动；后一参数，游标可随意滚动
}READ_ONLY：
游标为只读游标．
}SCROLL_LOCKS：
游标锁定，设置该参数后，游标读取记录时，数据库会将该记录锁定，以便完成游标对记录的操作．
}OPTIMISTIC：
设置该参数后，游标读取记录时，不会将记录锁定．
}SELECT_statement：
查询语句．
}UPDATE：
设置可更改的字段名称，如果没有设置，则默认可更改所有字段．
}打开游标
◦OPENcursor_name
}检索记录
◦FETCHcursor_name
}关闭游标
◦CLOSEcursor_name
}删除游标
◦DEALLOCATEcursor_nam
示例：
--定义一个名为stuInfo_cursor的可随意滚动的游标
DECLAREstuInfo_cursorCURSORscrollFOR
SELECT*FROMstuInfo
--打开该游标
OPENstuInfo_cursor
--定义个变量，用于存放游标中读取出来的值
DECLARE@idINT
DECLARE@nameNVARCHAR（10）
DECLARE@ageINT
DECLARE@sexCHAR
（2）
--读取游标的第一条记录行，并存放在变量中
FETCHfirstFROMstuInfo_cursor
INTO@id,@name,@age,@sex
--循环读取游标中的记录
PRINT'读取的数据如下：
'
WHILE（@@fetch_status=0）
BEGIN
--用print输出读取的数据
PRINT'学号：
'+convert（NVARCHAR,@id）
+'姓名：
'+@name+'年龄：
'+convert（NVARCHAR,@age）
+'性别：
'+@sex
--读取下一条记录行
FETCHnextFROMstuInfo_cursor
INTO@id,@name,@age,@sex
END
--读取完成后关闭游标
CLOSEstuInfo_cursor
--删除游标
DEALLOCATEstuInfo_cursor
检索:
}FETCHFIRST：
提取游标的第一行。
}FETCHNEXT：
提取上次提取的行的下一行。
}FETCHPRIOR：
提取上次提取的行的前一行。
}FETCHLAST：
提取游标中的最后一行。
}FETCHABSOLUTEn：
◦如果n为正整数，则提取游标中的第n行
◦如果n为负整数，则提取游标最后一行之前的第n行
◦如果n为0，则不提取任何行
}FETCHRELATIVEn：
◦如果n为正，则提取上次提取的行之后的第n行
◦如果n为负，则提取上提取的行之前的第n行
◦如果n为0，则再次提取同一行
游标是一种数据对象，使用它可以按行而不是按集合操纵数据
操纵游标的几个语名：
DECLARE语句创建、OPEN语句打开、CLOSE语句关闭、DEALLOCATE可删除游标
FETCH可用于在游标中读取记录行
19.事务的生命周期。
20.批处理语句的概念、特性。
1）批处理是包含一个或多个SQL语句的组，从应用程序一次性地发送到SQLServer执行
2）SQLServer将批处理语句编译成一个可执行单元，此单元称为执行计划。
执行计划中的语句每次执行一条
●GO是批处理的标志，表示SQLServer将这些T-SQL语句编译为一个执行单元，提高执行效率
●一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定
}如果批处理语句中出现编译错误（如语法错误）可使执行计划无法编译。
因此未执行批处理中的任何语句。
}另外一种情况，假定在批处理中有10条语句，并且这些语句都没有语法上的错误，第一条语句能够顺利的完成，在执行第二条语句出现了运行时错误，这时候，第一条语句执行的结果不受影响，因为它已经完成。
21.存储过程的作用、语法。
存储过程（procedure）类似于C语言中的函数或Java中的方法
用来执行管理任务或应用复杂的业务规则
存储过程可以带参数，也可以返回结果
优点：
}允许模块化程序设计
}执行速度更快
}减少网络流通量
}提高系统安全性
分类：
}系统存储过程
◦由系统定义，存放在master数据库中
◦类似C语言中的系统函数
◦系统存储过程的名称都以“sp_”开头或”xp_”开头
•sp_开头：
用来进行系统的各项设定
•以xp_开头：
用来调用操作系统提供的功能
}用户自定义存储过程
◦由用户在自己的数据库中创建的存储过程
◦类似C语言中的用户自定义函数
系统存储过程
说明
sp_databases
列出服务器上的所有数据库。
sp_helpdb
报告有关指定数据库或所有数据库的信息
sp_renamedb
更改数据库的名称
sp_tables
返回当前环境下可查询的对象的列表
sp_columns
回某个表列的信息
sp_help
查看某个表的所有信息
sp_helpconstraint
查看某个表的约束
sp_helpindex
查看某个表的索引
sp_stored_procedures
列出当前环境中的所有存储过程。
sp_password
添加或修改登录帐户的密码。
sp_helptext
显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。
}定义存储过程的语法
CREATEPROC[EDURE]存储过程名
@参数1数据类型[=默认值|OUTPUT],
……,
@参数n数据类型[=默认值|OUTPUT]
AS
SQL语句
GO
◦和C语言的函数一样，参数可选
◦参数分为输入参数、输出参数
◦输入参数允许有默认值
22.无参数/带参数的存储过程的执行方法。
EXECUTE（执行）语句用来调用存储过程
调用的语法
EXEC过程名[参数]
不带参数示例：
IFEXISTS（SELECT*FROMSysobjectsWHERENAME='Proc_GetAllStuMark'）
DROPPROCProc_GetAllStuMark
GO
CREATEPROCProc_GetAllStuMark
AS
SELECTa.StuName,b.Subject,b.Score
FROMStuInfoa,StuMarksb
WHEREa.StuID=b.StuID
GO
EXECProc_GetAllStuMark
EXECUTEProc_GetAllStuMark--使用EXECUTE执行存储过程
带参数示例：
}存储过程的参数分两种：
◦输入参数：
用于向存储过程传入值，类似Java语言的按值传递;
◦输出参数：
用于在调用存储过程后，返回结果，类似Java语言的按引用传递;
--创建带有参数的存储过程
--根据学生姓名,获得学生成绩
IFEXISTS（SELECT*FROMsysobjectsWHERENAME='Proc_GetStuMarkByStuName'）
DROPPROCProc_GetStuMarkByStuName
GO
CREATEPROCProc_GetStuMarkByStuName
@stunameVARCHAR（20）
AS
SELECTa.StuName,b.Subject,b.Score
FROMStuInfoa,StuMarksb
WHEREa.StuID=b.StuID
ANDa.StuName=@stuname
GO
EXECProc_GetStuMarkByStuName'李四'
23.存储过程的参数的默认值。
在调用存储过程时，有些参数的值的变化很少的，这时，可以给这些参数一个默认值，即使调用时不输入值，也会在存储过程中使用默认值。
在很大程度上方便调用。
带参数的默认值示例：
--参数有默认值的存储过程
--添加学生信息表的数据
IFEXISTS（SELECT*FROMsysobjectsWHERENAME='Proc_InsertStuInfo'）
DROPPROCProc_InsertStuInfo
GO
CREATEPROCProc_InsertStuInfo
@stunamevarchar（20）,
@stusexchar
（2）='男',
@classidint=2
AS
INSERTINTOStuInfo（StuName,StuSex,ClassID）
VALUES（@stuname,@stusex,@classid）
GO
--调用参数有默认值的存储过程
EXECProc_InsertStuInfo'唐僧'
EXECProc_InsertStuInfo'猪八戒',@classid=1
24.存储过程的输出参数。
存储过程是不能直接返回任何数据的，除了数据集。
但如果希望调用存储过程后，返回一个或多个值，这就需要使用到输出参数。
使用输出参数时，需要在定义参数的时候在参数后面加上“OUTPUT”关键字。
带输出参数示例：
--带
升级会员 