2018.12.23
复习大纲
一．教材第2-11章的选择题、填空题
二．T-SQL编程
WITH公用表表达式、查询结果集别名表达式/TOP、COMPUTE子句、游标、嵌套子查询；自定义函数、存储过程、触发器（AFTER/INSTEAD OF）的创建及使用。
基本T-SQL语句（SELECT/UPDATE/INSERT/DELETE/CREATE/ALTER/DROP）和控制流语句（BEGIN…END、IF、CASE、WHILE、WAITFOR、GOTO、RETURN）。
三．知识点复习大纲
请按照以下知识点复习大纲精心整理出答案，以方便复习。
-
SQL Server发展史、版本分类、主要管理工具、主要服务（MSSQLSERVER、SQLSERVERAGENT等）。
-
T-SQL语言：标识符分类与定义规则、数据库对象命名规则server.database.schema.object、注释、数据类型、全局变量和局部变量、变量定义和使用、批处理、控制流语句（BEGIN…END、IF、CASE、WHILE、WAITFOR、GOTO、RETURN），以及DECLARE、EXECUTE、USE、SET、PRINT、RAISERROR等语句。
-
SQL Server数据库分类、SQL Server系统数据库的组成及各组成部分作用。
分类:系统数据库，数据库快照，用户数据库
系统数据库 说明
master 数据库
记录 SQL Server 实例的所有系统级信息。
msdb 数据库
用于 SQL Server 代理计划警报和作业。
model 数据库
用作 SQL Server 实例上创建的所有数据库的模板。对 model 数据库进行的修改（如数据库大小、排序规则、恢复模式和其它数据库选项）将应用于以后创建的所有数据库。
Resource数据库
一个只读数据库，包含SQL Server包括的系统对象。系统对象在物理上保留在Resource数据库中但在逻辑上显示在每个数据库的sys架构中。
tempdb 数据库
一个工作空间，用于保存临时对象或中间结果集。
- 数据库文件类型及用途和文件扩展名（主要文件/主文件.mdf、次要文件/辅助文件.ndf、事务日志文件.ldf）；SQL Server文件组分类；数据文件和日志文件分开存放在不同的磁盘上有什么好处；数据库的状态、数据库文件的状态。
文件 说明
主要文件/
主文件 • 主要数据文件包含数据库的启动信息，并指向数据库中的其它文件。用户数据和对象可存储在此文件中，也可以存储在次要数据文件中。每个数据库有一个主要数据文件。主要数据文件的建议文件扩展名是.mdf。
次要文件/
辅助文件 • 次要数据文件是可选的，由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上。另外，如果数据库超过了单个 Windows 文件的最大大小，可以使用次要数据文件，这样数据库就能继续增长。次要数据文件的建议文件扩展名是.ndf。
事务日志 • 事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。事务日志的建议文件扩展名是.ldf。
数据库总是处于一个特定的状态中，例如，这些状态包括 ONLINE、OFFLINE或SUSPECT
数据库文件的状态独立于数据库的状态。
文件始终处于一个特定状态，ONLINE或OFFLINE。
文件组中的文件的状态确定了整个文件组的可用性。
- 创建数据库的T-SQL命令、删除、修改、扩展、收缩、分离、附加、移动、重命名数据库，更改数据库所有者、设置数据库选项、查看数据库元数据，以及查看和设置数据库的属性等。
扩展: ALTER DATABASE SET MAXSIZE =
收缩: ALTER DATABASE [jxgl1206] SET AUTO_SHRINK ON WITH NO_WAIT
删除文件: ALTER DATABASE AdventureWorks REMOVE FILE test1dat4;
分离: sp_detach_db ‘jxgl’,‘true’,‘true’ (分离jxgl)
附加: EXECUTE(‘CREATE DATABASE jxgl ON (NAME=jxgl,FILENAME=’’’+@data_path+‘jxgl.mdf’’) FOR ATTACH’);
移动文件:
1）运行ALTER DATABASE database_name SET OFFLINE – 使某数据库脱机
2）将该文件移至新位置。
3）运行 ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = ‘new_path/os_file_name’。
– 修改数据库指定文件到新的位置
4）运行 ALTER DATABASE database_name SET ONLINE。
– 使某数据库联机
重命名: Alter database jxgl MODIFY NAME=jxgl2
更改所有者: Use jxgl; sp_changedbowner ‘sa’
把数据库jxgl的所有者由“QXZ-2\Administrator”改为“sa”
删除数据库:
drop database sales_snapshot0800 --先删除其快照。
drop database sales --再删除数据库。
- SQL Server表的分类及各类表的特点、架构、自动编号列、全局唯一标识符列、表的列数据类型，创建表CREATE TABLE语句、修改表ALTER TABLE、删除表DROP TABLE语句，查看表元数据、表的各类完整性约束的定义和修改。临时表的分类（本地临时表和全局临时表）及特点。
SQL Server 2005的表可分为：用户基本表、已分区表、临时表与系统表四类。
-
用户基本表：
是存放用户数据的标准表，是数据库中最基本、最主要的对象。 -
已分区表:
分区表主要用于： -
表中包含或可能包含以不同方式使用的许多数据；
-
临时表：
包括本地临时表和全局临时表。以单个数字符号 (#) //打头以两个数字符号 (##) 打头 -
系统表：
SQL Server将定义服务器配置及其定义所有表的数据存储在一组特殊的表中，这组表称为系统表。 -
SELECT及其子句（COMPUTE、WITH CUBE、WITH ROLLUP、OPTION、UNION、EXCEPT、INTERSECT）、WITH公用表表达式、查询结果集别名表达式、INSERT、UPDATE、DELETE增强功能。
Compute可以当成额外使用某个列的聚集函数求结果，by则按照某列分组分段显示结果
With cute : group by productid with cute order by productid 实现分组+排序的维度列
With Rollup 不将NULL看成特殊情况
With(index=0) 代表执行索引0对应的操作，更改数代表执行其他选项操作
Union和union all的区别在于union会删除重复行
EXCEPT 从左查询中返回右查询没有找到的所有非重复值；
INTERSECT 返回 INTERSECT 操作数左右两边的两个查询都返回的所有非重复值。
Insert增强功能: 使用output可以将插入的结果也返回给另一个表
例如: 将一行插入到ScrapReason表中，并使用OUTPUT子句将语句结果返回给@MyTableVar table变量
INSERT Production.ScrapReason OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar VALUES(N’操作者的错误’,GETDATE());
Update的增强功能:和with公用表表达式连用，将最后的select字句换成update字句循环更新
Delete的增强功能:可以和游标连用只删除游标指向的行，FETCH 后跟delete语句即可
- T-SQL的大值数据类型、BULK INSERT、游标（类型、作用、创建和使用）。
max说明符：增强了varchar、nvarchar和varbinary数据类型的存储能力。
varchar(max)、nvarchar(max)和varbinary(max)统称为大值数据类型。大值数据类型最大存储2^31-1个字节。
BULK INSERT命令实现以用户指定的格式将数据文件加载至数据库表或视图中。
- 视图的分类、作用、创建、修改和删除视图、查看视图元数据。
标准视图、索引视图、分区视图
2、视图的作用
视图通常用来集中、简化和自定义每个用户对数据库的不同认识。
视图可用作安全机制，方法是允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限。
视图可用于提供向后兼容接口来模拟曾经存在但其架构已更改的表。
可在向SQL Server 2005复制数据和从其中复制数据时使用视图，以便提高性能并对数据进行分区。
Create view as + select字句
Alter view name
Drop view name
- 索引的分类、作用、创建和修改索引、查看索引元数据。
聚集索引、非聚集索引、唯一索引、包含性列索引、索引视图、全文索引、XML索引
• 索引技术是为在数据库中为了迅速地从庞大的数据库中找到所需要的数据，合理使用索引技术能得到良好的查询性能。
确定最佳的创建方法。按照以下方法创建索引：
1）使用CREATE TABLE或ALTER TABLE对列定义PRIMARY KEY或UNIQUE约束；
2）使用CREATE INDEX语句
CREATE [ UNIQUE ] [CLUSTERED|NONCLUSTERED] INDEX index_name ON (column [ASC|DESC][,…n])[INCLUDE(column_name[,…n])] [ WITH (<relational_index_option>[,…n ])]
[ ON {partition_scheme_name(column_name)|filegroup_name|default}][;]
::={[database_name.[schema_name].|schema_name.]table_or_view_name}
- Management Studio对象资源管理器中的“新建索引”对话框创建独立于约束的索引。
修改:Alter index name on rebuild [with options]
- 全文索引的体系结构、创建和简单使用。
全文搜索的体系结构由下列组件构成：
1）Microsoft Full-Text Engine for SQL Server (MSFTESQL)，MSFTESQL服务负责进行下列操作：填充全文索引、管理全文索引和全文目录、帮助对SQL Server数据库中的表进行全文搜索。
2）Microsoft Full-Text Engine Filter Daemon (MSFTEFD)，它包含下列组件：筛选器、协议处理程序、断字符。
本示例创建一个针对 HumanResources.JobCandidate 表的全文索引。
USE AdventureWorks; – 均在AdventureWorks数据库上操作，以下省略本USE命令
CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX ui_ukJobCand;
-
存储过程分类、作用和特点，存储过程的创建、修改、删除和执行，参数的传入、传出。存储过程和触发器的区别联系
-
触发器分类、作用和特点，触发器的创建、修改、删除和执行。包括DML触发器、DDL触发器、AFTER触发器和INSTEAD OF触发器、INSERTED和DELETED表的特点和作用。
-
数据库执行顺序是：DML操作、INSTEAD OF触发器、完整性约束检查、AFTER触发器。
-
SQL Server的安全性：验证模式、登录名、数据库用户名、角色（固定服务器角色、数据库角色、用户定义的角色、应用程序角色），以及相应的创建、修改和删除T-SQL语句。登录名和数据库用户的区别与联系。
SQL Server提供了2种安全管理模式：
Windows 身份验证模式
混合身份验证模式。
创建登录名:
CREATE LOGIN Michael WITH PASSWORD=‘B548bmM%f6’,DEFAULT_DATABASE=AdventureWorks,
SID=0x0123456789ABCDEF0123456789ABCDEF,DEFAULT_LANGUAGE=us_english
修改举例:
ALTER LOGIN KittiLert ENABLE;
ALTER LOGIN KittiLert WITH PASSWORD=‘3948wJ698FFF7’;
删除:
DROP LOGIN login_name
登录名是访问SQL Server的通行证
数据库用户用于数据库权限的控制。
用户用一个登录名登录SQL Server，以数据库用户的身份访问服务器上的数据库。
- SQL Server的权限类型（安全管理中授权的主体分类）、授予Grant、撤销Revoke和拒绝DENY。
权限类型:
CONTROL：为被授权者授予类似所有权的功能。
ALTER：授予更改特定安全对象的属性（所有权除外）的权限。
Create / Alter / Drop role role_name
GRANT SELECT ON OBJECT::Person.Address TO RosaQdM
REVOKE SELECT ON OBJECT::Person.Address FROM RosaQdM
DENY SELECT ON OBJECT::Person.Address TO RosaQdM
- 数据库备份策略：数据库备份类型、各种备份的特点、作用和相应的T-SQL语句。
SQL Server有数据库完整备份、差异备份、事务日志文件备份、文件及文件组备份等几种形式，
备份和还原策略包含备份部分和还原部分。
设计有效的备份和还原策略需要仔细计划、实现和测试。
数据库备份包括完整备份和完整差异备份。
完整备份包含数据库中的所有数据，并且可以用作完整差异备份所基于的“基准备份”。
完整差异备份仅记录自前一完整备份后发生更改的数据扩展盘区数。
完整恢复模式和大容量日志恢复模式：都应备份事务日志。
T-SQL命令:
BACKUP DATABASE {database_name | @database_name_var} TO <backup_device> [,…n] [[MIRROR TO <backup_device >[,…n]] […next-mirror]] [WITH……(省略)][;] – 备份整个数据库
BACKUP DATABASE {database_name| @database_name_var} <file_or_filegroup> [,…n] TO <backup_device> [,…n] – 备份文件或文件组
BACKUP LOG { database_name| @database_name_var}{ TO <backup_device> [,…n][[MIRROR TO <backup_device> [,…n]][…next-mirror]] – 备份日志
BACKUP LOG {database_name|@database_name_var}{ WITH{NO_LOG|TRUNCATE_ONLY}]}–截去日志
备份到磁盘设备，本例将把整个AdventureWorks数据库备份到磁盘上，并使用FORMAT创建一个新的媒体集’C:\AdventureWorks.Bak’：
BACKUP DATABASE AdventureWorks TO DISK= ‘C:\MSSQL\BACKUP\AdventureWorks.Bak’ WITH FORMAT, NAME=‘Full Backup of AdventureWorks’
GO
- 数据库还原策略：还原的类型以及相应的T-SQL语句。
使用还原方案可以还原下列某个级别的数据：数据库、数据文件和数据页。每个级别的影响如下：
1）数据库级别：还原和恢复整个数据库，并且数据库在还原和恢复操作期间处于离线状态。
2）数据文件级别：还原和恢复一个数据文件或一组文件。在文件还原过程中，包含相应文件的文件组在还原过程中自动变为离线状态。访问离线文件组的任何尝试都会导致错误。
3）数据页级别：可以对任何数据库进行页面还原，而不管文件组数为多少。
RESTORE DATABASE FROM WITH NORECOVERY
RESTORE DATABASE FROM <full_differential_backup> WITH NORECOVERY
RESTORE LOG FROM <log_backup> WITH NORECOVERY
RESTORE LOG FROM <tail_log backup> WITH RECOVERY
备份恢复的完整例子:
USE master;
ALTER DATABASE AdventureWorks SET RECOVERY FULL;
GO – 以下为完整数据库备份创建逻辑备份设备
EXEC sp_addumpdevice ‘disk’, ‘MyAdvWorks_FullRM’,
‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\MyAdvWorks_FullRM.bak’;
GO – 以下创建完整备份
BACKUP DATABASE AdventureWorks TO MyAdvWorks_FullRM WITH FORMAT;
GO – 以下创建纯日志备份到备份文件
BACKUP LOG AdventureWorks TO MyAdvWorks_FullRM;
GO – 以下创建尾日志备份
BACKUP LOG AdventureWorks TO MyAdvWorks_FullRM WITH NORECOVERY;
GO
– 从备份集1中还原完整备份
RESTORE DATABASE AdventureWorks FROM MyAdvWorks_FullRM WITH NORECOVERY;
–从备份集2中还原纯日志备份
RESTORE LOG AdventureWorks FROM MyAdvWorks_FullRM WITH FILE=2, NORECOVERY;
–从备份集3中还原尾日志备份
RESTORE LOG AdventureWorks FROM MyAdvWorks_FullRM WITH FILE=3, NORECOVERY;
GO
RESTORE DATABASE AdventureWorks WITH RECOVERY; – 恢复整个数据库
GO
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
