SQLSERVER2000学习完整版
第一部分：第一部分：数据库概论
单词记忆：DBA{databaseadministrator}：数据库管理员DBMS{databasemanagementsystem}：数据库管理系统SQL{structuredquerylanguage}：结构化查询语言DQL{dataquerylanguage}：数据查询语言DML{datamanipulationlanguage}：数据操作语言DCL{datacontrollanguage}：数据控制语言DDL{datadefinitionlanguage}：数据定义语言
一、计算机数据库的优点1、降低存储数据的冗余度，也就是减少重复的数据。2、更高的数据一致性。3、存储的数据可以共享。4、可以建立数据库所遵循的标准。5、便于维护数据完整性，就是指保证数据库中数据的准确性。6、能够实现数据的安全性。
二、数据库分类单机数据库：优点：高效、简单缺点：数据无法共享实例：ACCESS、FOXPRO网络数据库：?优点：数据共享、提供并发访问?缺点：必须考虑网络压力、冲突处理、资源限制?实例：实例：SQLServer、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型大数据字段和其他常规类型字段分开存放，保证常规数据的查询高效性索引数据和表数据的分离--将索引和表分离，减少磁盘争用并提高索引的查询效率。
十一、十一、SQLServer的存储特性页（8K）--将OS中连续的8个块组成“页”，作为空间分配的最小单元，以减少空间分配时所需耗费的资源。（处理大表时，可能将8个连续的“页”组成一个“扩展”，以进一步减少分配时所耗用的资源）表记录存放在页中--表的记录尽量存放在页中，而且一个页中一般只存放同表的内容，保证取得高命中率。数据读取以页为单位
十二、十二、SQL简介SQL的分类DQL（数据查询语言）：用来从数据库中获取数据和对数据进行排序。DML（数据库操作语言）：用来插入、删除、修改数据库中的数据。
DCL（数据控制语言）：用于管理对数据库和数据库对象的权限。DDL（数据定义语言）CCL（通用命令语言）：用于在数据库中进行高效率的搜索。
第二部分：第二部分：数据库管理
单词记忆：transact：处理create：创建execute：执行、完成
一、SQLServer的特性1、安装简便：为了便于安装、使用和管理，SQLServer2000提供了一组管理和开发工具。2、伸缩性和有效性3、支持客户/服务器模式（C/S）4、多协议兼容性5、数据仓库6、遵从ANSI/ISOSQL-92标准：Micrsoft还对其进行了语言扩展并命名为：“Transact-SQL”7、支持数据复制8、全文检索9、联机丛书
二、T-SQL简介Transact-SQL（或称为T-SQL）是Micosoft的一个程序扩展集合。T-SQL为SQL增加了些功能,包括事务控制、异常错误处理和行处理。
Transact-SQL允许用户在Transact-SQL对象中声明和使用局部变量和常量，并且有数据类型。
三、SQLServer存储体系构成数据文件后缀为.MDF文件.MDF–主数据文件，一个数据库只有一个MDF文件，存放数据库中各种数据实体的定义（数据字典），也可以存放其他数据，但要注意控制其大小。
数据文件后缀为.NDF文件.–扩展数据文件，一个数据库可以有多个NDF文件，存放各种数据1.数据规划(组）2.数据扩展3.文件仿真阵列
日志文件后缀为.LDF文件.–日志文件，一个数据库可以有多个LDF文件，存放联机重做日志数据?注意：如果日志文件已经填满，SQLServer将自动停止运行并等待人工干预，所以要经常监控日志的情况保证系统的稳定运行。
文件组FileGroup–将MDF和NDF文件在逻辑上进行分组，进行表规划时候，只需要指定表所需存放的组，就可以实现数据分离。注意：系统默认的组为“PRIMARY”，不指定数据文件所隶属的组，则数据文件自动划分到“PRIMARY”组中；创建的表不指定所在的组，系统也自动分配该表到“PRIMARY”组中。四、数据库的创建示例：CreateDatabase数据库名On(Name=‘逻辑名‘，
FileName=‘物理文件完全路径(*.mdf)’,Size=n,MaxSize=n,FileGrowth=n)FileGroup组名字(…….)LogOn(Name=‘逻辑名‘，FileName=‘物理文件完全路径(*.ldf)’,Size=n,MaxSize=n,FileGroup=n)
五、创建过程中需要注意的问题：创建过程中需要注意的问题：只能为一个数据库创建一个MDF文件；如果需要进行文件分组，只需要在文件前加上FILEGROUP组名就可以了，但PRIMARY组中至少必须包含一个文件；如果Size和MaxSize不指定单位，系统默认用“M”为单位；如果不指定数据库文件的大小，系统默认为“1M”；如果创建时没有使用LogOn字句，系统自动创建一个LDF文件，大小为MDF文件的1/4；文件的增长率FILEGROWTH应该根据实际需要设置大些，以保证系统不会频繁扩容。
六、数据库性能选项配置选项设置ExecSp_Dboption‘数据库名’,‘选项‘，’值’如：ExecSp_Dboption‘Pubs’,’AutoClose’,’False’查询所有可用选项ExecSp_Dboption查询数据库已经开启的选项ExecSp_Dboption‘数据库名’如：ExecSp_Dboption‘Pubs’
七、数据库的调整1、设置数据库只读取ExecSp_dboption‘Pubs’,’Read_Only’,’True’2、设置数据库离线ExecSp_dboption‘Pubs’,’Offline’,’True’3、数据库更名（Sp_RenameDb)ExecSp_dboption‘Pubs’,’SingleUser’,’True’ExecSp_RenameDb‘Pubs’,’Pubs_1’ExecSp_dboption‘Pubs_1’,’SingleUser’,’False’
八、数据库结构调整1、调整数据库结构AlterDatabase数据库名??????注意：1、ADDFILE时如果不指定“TOFileGroup文件组”,系统自动将该文件归到“Primary”组中；2、REMOVEFILEGROUP时，如果组中有数据，将无法删除。AddFile(文件参数)ToFileGroup文件组AddLogFile(文件参数）RemoveFile‘逻辑数据库文件名字’RemoveFileGroup‘组名’AddFileGroup‘组名’ModifyFile(文件参数)
九、数据规划的实现表的数据规划的实现CreateTableStudent(Stu_CodeVarchar(10)PrimaryKeyOnG_Index,Stu_NameVarchar(10)NotNull,Stu_SexVarchar(1)NotNull,Stu_PicImage)
OnG_NormalTextImage_OnG_Big规化重点：1、将大数据（G_Big)和常规数据(G_Normal)分离;2、将索引数据（G_Index）和表数据分离；
第二部分习题样例
一、创建本班数据库（T607），根据规划原则将四大类数据分组，每组一个数据文件5－1创建本班数据库（T607），根据规划原则将四大类数据分组，），根据规划原则将四大类数据分组00M，30％增长，100M，50％增长。00M，按30％增长，日志文件2个，5－100M，按50％增长。createdatabaseT607onprimary(name='T607_mdat',filename='d:"T607_M.mdf',size=5,maxsize=100,filegrowth=30%),filegroupG_Normal(name='T607_n1dat',filename='d:"T607_N1.ndf',size=5,maxsize=100,filegrowth=30%),filegroupG_Change(name='T607_n2dat',filename='d:"T607_N2.ndf',size=5,
maxsize=100,filegrowth=30%),filegroupG_Index(name='T607_n3dat',filename='d:"T607_N3.ndf',size=5,maxsize=100,filegrowth=30%),filegroupG_Big(name='T607_n4dat',filename='d:"T607_N4.ndf',size=5,maxsize=100,filegrowth=30%)logon(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、将创建好的数据库设置成自动“文件自动关闭”和自动“文件自动收缩”方式文件自动关闭：execSp_dboption'T607','autoclose','true'文件自动收缩：execSp_dboption'T607','autoshrink','true'
2、将创建好的数据库设置成“只读方式”execSp_dboption'T607','readonly','true'
3、将创建好的数据库设置成“离线状态”后恢复execsp_dboption'T607','offline','true'
4、将创建好的数据库名称改名为“T607-2006”execsp_dboption'T607','singleuser','true'execsp_renameDb'T607','T607_2006'execsp_dboption'T607-2006','singleuser','false'注意事项：首先必须把只读属性取消
三、修改数据库文件属性将数据库中的一个数据文件属性修改为10—无限大，增长比率为40％；alterdatabase[T607_2006]modifyfile(name=T607_n1dat,size=10,maxsize=unlimited,filegrowth=40%)注意事项：修改时，请勿指定物理名称。
四、收缩数据库文件将数据库中的一个文件收缩到3M；dbccshrinkfile(‘T607_n2dat‘,3)注意事项：必须先useT607_2006才能收缩
五、组的创建和维护在数据库中创建一个新的组“G_TEST”alterdatabase[T607_2006]addfilegroupG_TEST
六、组中增加一个文件在数据库中增加一个新的文件“T607_TEST.ndf”，并自动加入到“G_TEST”组中；alterdatabase[T607-2006]addfile(name='T607_TEST',filename='d:"T607_TEST.ndf')tofilegroupG_TEST
七、向数据库中增加日志文件在数据库中增加一个新的日志文件“T607_LOG_TEST”文件；alterdatabase[T607-2006]addlogfile(name='T607_LOG_TEST',filename='d:"T607_LOG_TEST.log')
八、数据库的规划方式创建一个学生资料表，包含字段：
学生编码、学生名称不允许重复学生性别、学生年龄15—30、学生生日、学生地址、学生国籍、学生备注大量文本、学生照片JPG根据表规划及优化原则，创建该表：createtable学生(学生编码smallint,学生名称varchar(10)unique,学生性别nvarchar(1),学生年龄tinyintcheck(学生年龄>=15and学生年龄<=30),学生生日datetime,学生地址nvarchar(20),学生国籍varchar(10),学生备注text,学生照片image)ongroup1textimage_ongroup2
九、删除数据库删除本练习中创建的数据库dropdatabaseT607-2006
第三部分：第三部分：数据表管理
一、SQLServer支持的数据类型1、文本型?CHAR定长型<=8000字节
?Varchar变长型<=8000字节?Text?NCHAR大文本<=2G定长型<=4000字节
?NVarchar变长型<=4000字节NText注意：“N”型文本类型是为了表示中文、日文、韩文等大字符集的文字而产生的对应定长的字符，用CHAR比用VARCHAR的效率要高，如学生表中的“Sex”在SQLServer7.0后N型和非N型通用大文本<=2G
2、日期型?DateTimeSmallDateTime注意：系统时间格式：D-M-YYHH24:M:S系统可识别日期格式：字母日期数字日期‘April15,1998’‘4/15/1998’1753.1.1-9999.12.318字节1902.1.1-2079.12.314字节
未分隔日期‘19981221’取得系统时间：Getdate()
3、二进制型?Image?Sql_Variant注意：如果表中包含IMAGE类型字段，该表的记录将无法以一个页存放，为避免产生行链接，建议将大数据字段和常规数据字段分开存放。SQL_Variant的字段无法经常各种常规的函数操作。<=2G变体型，可以接受任何数据类型
4、扩展型?Identity?CustomerType注意：?增加自定义数据类型ExecSp_AddType类型名称,‘类型定义’,‘空标志’ExecSp_AddTypeTelephone,’Varchar(24)’,’NotNull’4字节用户自定义数据类型
5、完整性类型包括：实体完整性（加强完整性规则的SQLServer工具：主键约束、唯一约束、标识列）域完整性（加强完整性规则的SQLServer工具：默认值定义、外键约束、检查约束、NOTNULL属性）引用完整性（加强完整性规则的SQLServer工具：外键约束、检查约束）用户定义完整性（加强完整性规则的SQLServer工具：存储过程、规则、触发器）
6、创建和更改主键(1)createtabletable_name
primarykey其中，是列名(2)如果，现有表中无主键，可以添加主键：altertabletable_nameaddconstraintprimarykey()其中为约束名。
7、约束和约束对象SQLServer2000提供了以下约束：check<检查约束>、default<默认值>、primarykey<主键>、foreignkey<外键>、unique<唯一性>。
8、通配符
通配符解释示例
_
一个字符
Selectusernamefromtb_userwhereusernamelike‘T_’Selectusernamefromtb_userwhereusernamelike
%
任意长度的字符串‘%T%’括号中所指定的范围中的任意一个不在括号中所指定的范围中的任意Select*fromflightwhereaddresslike‘bt52[1-4]’
[]
[^]一个
Select*fromflightwhereaddresslike‘bt52[^1-4]’
9、逻辑运算符AND、OR和NOT
二、SQLServer对象模型化过程数据采集并属性化约束的制定域约束：NotNull,Check,Default,Unique完整性约束：PrimaryKey引用性约束：ForeignKey,Trigger规则的制定关系的创建默认值的关联索引的创建常规索引全文索引数据的规划
三、表的创建表的创建?图形方式企业管理器(EnterpriseManager)?代码方式查询分析器(QueryAnalyzer)
四、创建表createtable工资表
(职工编号varchar(10)constraintpk_工资表primarykey主键约束外键约束
constraintfk_工资表_职员表foreignkeyreferences职员表（编号）ondeletecascadeonupdatecascade,级联更新，级联删除
工资smallmoneyconstraintck_工资表check（工资>0）constraintdf_工资表_工资default1000)检查约束默认值
五、数据操作1、数据插入Insert语法：insertintovalues()2、选择性的将一个表中的数据添加到另一个表中语法：insertintoselectfrom例如：insertintoVipUserselectusernamefromuserswhererank=’VIP’3、更新数据Update语法：updateset[where条件]件可有可无4、删除数据Delete语法：deletefrom[where条件]5、使用Truncatetable删除表中的所有数据条件可有可无条
六、约束的管理1、主键约束PrimaryKey注意：约束可以在建表时创建，也可修改表结构时创建创建PrimaryKey约束时，系统讲自动为该约束字段创建索引PrimaryKey约束如果不命名，系统将自动给定约束名:“PK_表名_8位标志符”定义语法：Constraint约束名PrimaryKeyCluster/NonCluster字段集
系统默认为PK约束创建Cluster索引?2、外键约束?ForeignKey注意：约束可以在建表时创建，也可修改表结构时创建定义语法：Constraint约束名ForeignKeyReferences引用表（引用字段集）OnDeleteCascade/NoActionOnUpdateCascade/NoAction如果使用NOAction，系统不允许删除被引用的数据
七、规则的创建和管理1、创建规则?CreateRule规则名As条件表达式例：CreateRuleRule_AgeAs@T>=15And@T<=302、绑定规则/取消绑定?ExecSp_BindRule‘规则名’,‘字段’例：ExecSp_BindRule‘Rule_Age’,’Stu_Age’?ExecSp_UnBindRule‘字段’例：ExecSp_UnBindRule’Stu_Age’注意：?一个字段只可以和一个规则绑定，一个规则可以和多个字段绑定
八、默认值的创建和管理1、创建默认值?CreateDefault默认名As常数表达式例：CreateDefaultDef_StatAs‘中国’2、绑定默认值/取消默认值?ExecSp_BindDefault‘默认名’,‘字段’例：ExecSp_BindDefault‘Def_Stat’,’Stu_Stat’
?ExecSp_UnBindDefault‘字段’例：ExecSp_UnBindDefault’Stu_Stat’注意：?一个字段只可以和一个默认绑定，一个默认可以和多个字段绑定
九、表结构的调整1、字段定义的修改AlterTable表名AlterColumn列名列定义2、添加字段AlterTable表名Add列名列定义3、删除字段AlterTable表名DropColumn列名注意：?添加字段的时不允许将字段属性设置为“NotNull”?4、添加约束AlterTable表名AddConstraint约束名约束定义5、删除约束AlterTable表名DropConstraint约束名注意：在删除PrimaryKey,UniqueKey约束的时候，系统将自动删除相应的Index如果约束没有命名，要删除必须用修改字段定义的方式6、删除表DropTable表名注意：以下两种情况无法删除表：（1）表被占用（2）表有被引用关系（作为外键的主表）
第三部分习题样例
1、创建具有关系的表并进行合理的数据规划创建城市表，包含：城市编码5
城市名称
20
邮政编码：6
useT607createtableCity(CityCodevarchar(5)constraintpk_city_citycodeprimarykey,CityNamevarchar(20),Postalcodevarchar(6))onG_Normal
2、创建学生表：创建学生表：学生编码学生学生性别学生年龄学生生日学生籍贯学生国籍学生照片根据实际需要设计相应的关系和约束，同时，FK约束为同步更新方式固定13位不为空，不重复F/M15－25
createtableStudent(StuCodechar(13)constraintpk_student_stucodeprimarykey,StuNamevarchar(10)notnullconstraintuk_student_stunameunique,StuSexchar(2)check(StuSexin('F','M')),StuAgesmallintcheck(StuAge>=15andStuAge<=25),StuBirthdaysmalldatetime,StuNativePlacevarchar(5)constraintfk_student_cityforeignkeyreferencesCity(CityCode)ondeletecascadeonupdatecascade,
StuStatevarchar(20),StuImageimage)onG_Changetextimage_onG_Big
3、将学生年龄的约束制定成规则并绑定createrulerule_stu_ageas@T>15and@T<=25execsp_bindrule'rule_stu_age','Student.StuAge'
4、将学生编码的固定长度约束制定成规则并绑定droprulerule_stu_code
createrulerule_stu_codeaslen(@length)=13execsp_bindrule'rule_stu_code','Student.StuCode'
5、将国籍设置默认并绑定createdefaultdef_stu_stateas'中国'execsp_bindefault'def_stu_state','Student.StuState'
insertintoStudentvalues('4534567891234','jack','m',24,'2005-12-14','0592','','0000')
添加一个字段：6、添加一个字段：地址50不允许为空altertableStudentaddAddressvarchar(50)altertableStudentaltercolumnAddressvarchar(50)notnull
删除刚才添加的字段：7、删除刚才添加的字段：地址altertableStudentdropcolumnAddress
修改学生编码中的主键索引为非聚集索引，8、修改学生编码中的主键索引为非聚集索引，将学生所产生的索引设置成为聚集索引
altertableStudentdropconstraintpk_student_stucode--删除约束altertableStudentaddconstraintpk_student_stucodeprimarykeynonclustered(StuCode)
altertableStudentdropconstraintuk_student_stunamealtertableStudentaddconstraintuk_student_stunameuniqueclustered(StuName)
第四部分：第四部分：查询和视图管理
一、SQLServer查询语句SelectDistinctTopn(Percent)字段Into表From表集Where条件集合GroupBy分组字段Having二次筛选条件WithRollup/CubeOrderBy排序集注意：Distinct后可以加字段，也可以加“*”，表示消除完全相同的记录“Into表”子句中的目标表必须是数据库里不存在的表，如果存在，则报错“Rollup”将对GroupBy的第一个字段进行汇总，“Cube”将对GroupBy的所有字段进行汇总
二、查询语法1、使用WHERE子句查询select*fromwhere<条件>2、使用ORDERBY子句查询ORDERBY是对查询结果进行排序，ASC是升序，DESC是降序。例如：SELECT*FROMauthorsORDERBYageASC3、使用AS子句命名列SELECTu_nameAS用户名FROMuser4、使用TOP子句限制查询返回值SELECTTOPnumFROM
三、集合函数和分组查询1、聚合函数（1）SUM：求和（2）AVG：求平均值（3）COUNT：统计（4）MAX：最大值（5）MIN：最小值
2、使用GROUPBY进行分组例：统计各用户的发帖数selectusername,count(*)fromtb_Spenkgroupbyusername其中，tb_Speak是发贴表，username是用户名
3、使用HAVING子句选择行，HAVING用于筛选分组汇总后的行。例：统计用户的发帖数大于50帖的用户selectusername,count(*)fromtb_Spenkgroupbyusernamehavingcount(*)>50其中，tb_Speak是发贴表，username是用户名
4、某条件值为NULL时，应用ISNULL。
5、模糊查询：LIKE
四、约束的命名规则：约束的命名规则：1、主键：PK_表名2、外键：FK_主表名_从表名3、检查约束：CK_字段名4、默认值：DF_表名_字段名
五、SQLServer的集合操作Union方式SelectSQLStatementUnion(All)SelectSQLStatment前提条件：各SQL语句结果集的字段个数相同，并类型兼容各SQL语句有效结果集特点：列名无效，用列序号描述列结果集中自动执行DISTINCT动作取消重复?六、视图的概念1、视图命名的SQL语句，一种从多表取得数据的方法，无数据实体，定义部分存放在MASTER数据库中优点：提供列级的安全保证隐藏数据的复杂性简化查询语句保存复杂的SQL语句基表和应用程序的逻辑分离2、视图分类NonJoinView
JoinViewPartitionView
3、视图的执行步骤针对视图的查询语句和定义语句合并产生目标SQL语句通过SQLJET编译目标SQL后得到二进制信息和执行计划对列进行必要的索引替换读取数据到Data_Buffer中
4、视图的创建CreateView视图名WithEncryption/schemabindingAsSQLStatementWithCheckOption为保证向视图中插入的数据能够体现出来，强制要求向视图中插入的数据满足视图定义语句的Where子句条件。
5、视图的创建的注意事项Master中保存视图的定义建视图的用户必须具备创建视图（CreateView）和访问视图所引用的基表的访问权限视图为非独立实体，必须依赖与其他的实体，其依赖性由SQLServer自动维护视图不可以引用临时表(TEMP数据库中，以##开头）不能给视图字段绑定规则和默认值不能在视图上创建触发器不可以给视图创建全文索引，但可以创建一般索引一般不建议向视图中插入数据，因为视图中只包含表的部分数据，插入动作可能违反表的约束
6、视图的查询SysObjectsSysColumnsSysDependsSysComments存放视图的基本信息存放视图的定义列存放视图的依赖关系存放视图的定义文本
----------------------------------------------------------Sp_HelpText视图名显示视图的定义语句
Sp_Depends视图名显示视图的依赖关系
7、视图的删除DropView视图名
第四部分习题样例
/*普通会员M_CodeM_NameM_IDCarduseT607createtableMember(M_Codevarchar(10)constraintpk_member_codeprimarykeyonG_Index,M_Namevarchar(10)notnull,M_IDCardvarchar(18)notnullconstraintuk_member_idcardunique,M_Telvarchar(13))onG_NormaldroptableMember/*VIPV_CodeV_NameV_IDCard*/createtableVip(V_Codevarchar(10)constraintpk_vip_codeprimarykeyonG_Index,V_Namevarchar(10)notnull,V_TelephoneV_AddressM_Tel*/
V_IDCardvarchar(18)notnullconstraintuk_vip_idcardunique,V_Telephonevarchar(13),V_Addressvarchar(30))onG_Normal
/*书籍编号*/书名作者类型单价
createtableBook(B_Codevarchar(15)constraintpk_book_codeprimarykeyonG_Index,B_Namevarchar(30)notnull,B_Authorvarchar(10),B_Typevarchar(10),B_Pricefloat)onG_Change
/*罚单罚单号记录号会员号罚款金额*/
createtableAmerce(A_Codevarchar(10)constraintpk_amerce_codeprimarykeyonG_Index,A_NoteCodevarchar(10)notnull,A_MemberCodevarchar(10)notnull,A_Moneysmallmoney
)onG_Change
/*记录记录号会员号书籍编号*/借书日期还书日期
createtableNote(N_Codevarchar(10)constraintpk_note_codeprimarykeyonG_Index,N_MemberCodevarchar(10)notnull,N_BookCodevarchar(15)notnull,N_BorrowDatesmalldatetimenotnull,N_ReturnDatesmalldatetime)onG_Change
/*1、对书籍进行类别统计，并同时计算出每个类别书目的总数；*/insertintoBookvalues('b1','oracle','谭浩强','计算机',53)insertintoBookvalues('b2','sqlserver','谭浩强','计算机',42)insertintoBookvalues('b3','java','谭浩强','计算机',48)insertintoBookvalues('b4','一个好人','小也','文学',25)
selectB_Typeas类别,count(*)as总数fromBookgroupbyB_Type
/*2、找出借过”oracle”这本书的所有会员的联系电话？*/insertintoMembervalues('M001','kenny','123456789123456789','0592')insertintoVipvalues('V001','jacky','987654321987654321','0592','xm')
insertintoNotevalues('N001','M001','b1','2006-02-06','2006-05-26')insertintoNotevalues('N002','V001','b1','2006-05-06','2006-07-26')insertintoNotevalues('N003','V001','b1','2006-07-06','2006-08-26')insertintoNotevalues('N005','V001','b1','2006-07-06','2006-08-26')insertintoNotevalues('N004','V001','b4','2006-07-06','2006-08-26')
insertintoNotevalues('N013','V001','b1','2006-01-06','2006-08-26')insertintoNotevalues('N006','V001','b1','2006-12-06','2007-08-26')insertintoNotevalues('N007','V001','b1','2006-03-06','2006-08-26')insertintoNotevalues('N008','V001','b1','2006-04-06','2006-08-26')insertintoNotevalues('N009','V001','b1','2006-06-06','2006-08-26')insertintoNotevalues('N010','V001','b1','2006-08-06','2006-08-26')insertintoNotevalues('N011','V001','b1','2006-10-06','2007-08-26')insertintoNotevalues('N012','V001','b1','2006-11-06','2007-08-26')insertintoNotevalues('N014','V001','b1','2006-09-06','2007-08-26')
selectdistinctMember.M_Nameas,Member.M_Telas电话from(MemberinnerjoinNoteonMember.M_Code=Note.N_MemberCode)innerjoinBookonNote.N_BookCode=Book.B_CodewhereBook.B_Name='oracle'unionallselectdistinctVip.V_Name,Vip.V_Telephonefrom(VipinnerjoinNoteonVip.V_Code=Note.N_MemberCode)innerjoinBookonNote.N_BookCode=Book.B_CodewhereBook.B_Name='oracle'
/*3、查看在7月份作者“谭浩强“没被人借过的书有哪些？*/selectB_Nameas书名,B_Authoras作者fromBook
leftjoinNoteonBook.B_Code=Note.N_BookCodeandmonth(N_BorrowDate)=7whereB_Author='谭浩强'andN_Codeisnull
/*4、找出借书册数<50本的普通会员资料*/select*fromMemberinnerjoin(selectN_MemberCode,count(N_Code)asCountNumberfromNotegroupbyN_MemberCode)asBorrowBookonMember.M_Code=BorrowBook.N_MemberCodewhereBorrowBook.CountNumber<50
/*5、会员每次借书的平均时间是多少天?(借了没还的不计算)*/selectsum(datediff(day,N_BorrowDate,N_ReturnDate))/count(N_Code)as平均天数fromNotewhereN_ReturnDateisnotnull
/*6、查看每个月份每个作者的书被借的情况*/selectBorrowDateas月份,B_Authoras作者,count(*)as借阅总数from(selectBook.B_Author,Book.B_Code,Month(N_BorrowDate)asBorrowDatefromBookinnerjoinNoteonBook.B_Code=Note.N_BookCode)asBgroupbyB.BorrowDate,B.B_Author
/*7、备份书籍表中价格大于40的所有书的书名、作者和价格并注明备份日期。*/selectB_Name,B_Author,B_Price,getdate()asBackupDateintoBookBackupfromBookwhereB_Price>40
select*fromBookBackup
/*8、每个月都来借书的那些人最后一次借的最多的书是哪一本?*/select*fromBookwhereB_Code=(selecttop1N_BookCodefromNoteinnerjoin
(selectN_MemberCode,max(N_BorrowDate)asmtimefromNotewhereN_MemberCodein(selectN_MemberCodefrom(selectdistinctcast(year(N_BorrowDate)asvarchar(4))+cast(month(N_BorrowDate)asvarchar(2))asmonth_list,N_MemberCodefromNote)asBgroupbyN_MemberCodehavingcount(N_MemberCode)=(selectdatediff(month,min(N_BorrowDate),max(N_BorrowDate))asmonthcountfromNote))groupbyN_MemberCode)asxonNote.N_MemberCode=x.N_MemberCodeandNote.N_BorrowDate=x.mtimegroupbyN_BookCodeorderbycount(*)desc)/*9、查看会员”V001”平均多少天来借一次书?*/selectN_MemberCodeas会员编号,datediff(day,min(N_BorrowDate),max(N_BorrowDate))/count(N_Code)as平均借书天数fromNotegroupbyN_MemberCodehaving(N_MemberCode='V001')
/*10、找出借出的书籍数量相差超过50的月份有几对?*/select*fromNoteselectcount(A.月份)as月份总对数from((selectmonth(N_BorrowDate)as月份,count(N_Code)ascountMonthfromNotegroupbymonth(N_BorrowDate))asAleftjoin(selectmonth(N_BorrowDate)as月份,count(N_Code)ascountMonthfromNotegroupbymonth(N_BorrowDate))asBonA.月份<>B.月份andB.月份isnotnull)where(A.countMonth-B.countMonth>=50)or(B.countMonth-A.countMonth>=50)
/*11、最后一次是借计算机书的人有哪些*/selectA.N_MemberCode,max(A.N_BorrowDate)asLastTimefrom
(selectN_MemberCode,N_BorrowDatefromBook,NotewhereNote.N_BookCode=Book.B_CodeandB_Type='计算机')asAgroupbyA.N_MemberCode
/*12、查询3月借书量超过50册的所有VIP和普通会员资料*/select*fromMemberselect*fromVip
selectM_Codeas会员号,M_Nameas,M_IDCardas身份证号,M_Telas电话fromMemberinnerjoin(selectN_MemberCode,count(N_Code)asNumfromNotewhereMonth(N_BorrowDate)=3groupbyN_MemberCode)asAonMember.M_Code=A.N_MemberCodewhereA.Num>=50
union
selectV_Code,V_Name,V_IDCard,V_TelephonefromVipinnerjoin(selectN_MemberCode,count(N_Code)asNumfromNotewhereMonth(N_BorrowDate)=3groupbyN_MemberCode)asBonVip.V_Code=B.N_MemberCodewhereB.Num>=50
第五部分：第五部分：高级查询
一、select基本语法SELECTselect_list[INTOnew_table]
FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expression[ASC|DESC]]
1、distinct：消除重复记录示例：--汽车月销售记录，当某月有销售记录多次时，我们只需要一条记录即可时：selectdistinctsale_name,sale_monthfromtbl_for_distinctwheresale_name='东风'2、selectinto??SELECTINTO语句创建一个新表，并用SELECT的结果集填充该表注意事项–?Where–示例：select*intotb_newfromtb_old本示例创建一个基于现存表tb_old的新表，称为tb_new。然后用tb_old表中的内容填充新表。3、compute?为聚合函数生成汇总值–?示例select*fromtbl_for_distinctcomputesum(sale_amount)该汇总值作为附加行显示在结果集中。用新表记录中间查询结果Into后的表会自动创建
二、多表查询类型
联合：合并多个数据表中的行联接：合并多个数据表中的列子查询：将一个查询包含到另一个查询中1、联合：Union（1）Union操作符：将两个或更多个SELECT语句的结果合并为一个结果集。?语法：?select语句union[all]select语句
?使用ALL子句表示不删除重复的行。
（2）Union注意事项?每个Select必须具有相同的结构示例1：SELECTempno,ename,salFROMempUNIONSELECTempno,ename,salFROMret_emp
示例2：查询校全体师生编号、selectstu_idas编号,stu_nameasfromtbl_for_union_stuunionallselecttea_idas编号,tea_nameasfromtbl_for_union_tea
2、子查询子查询：一个SELECT语句嵌套在另一个SELECT语句中。
父查询
SelectFromTable
WHERE＝SelectFrom
WHERE=操作符子查询
（1）In(notin)：确定给定的值是否在子查询的范围内示例1：查询23岁或25岁或31岁的销售员的信息select*fromtbl_advqry_sellerwhereseller_agein(23,25,31)示例2：查询有卖出商品的销售员的所有信息select*fromtbl_advqry_sellerwhereseller_namein(selectsale_master_personfromtbl_advqry_sale_master)
（2）Exists(notexists)：指定一个子查询，检测行的存在。相录于进行一次存在测试，此时，子查询实际上不产生任何数据，它只是返回TRUE或FALSE值。示例1：查询有卖出商品的销售员的所有信息select*fromtbl_advqry_seller（销售员表）whereexists(select*
fromtbl_advqry_sale_master（销售表）wheresale_master_person=tbl_advqry_seller.seller_name)（3）子查询的分类?嵌套子查询–?先执行子(内)查询，再执行父(外)查询
相关子查询–对于父(外)查询中的每一条记录,都会执行一次子(内)查询
（1）区别嵌套查询与相关子查询区别1-形式子(内)查询中包含父(外)查询中的字段区别2-本质子(内)查询依赖父(外)查询中的数据
（2）使用比较运算符的子查询子查询可由一个比较运算符（=、<>、>、>=、<、!>,!<或<=）引入。示例：查询高于平均年龄的销售员的信息select*fromtbl_advqry_sellerwhereseller_age>(selectavg(seller_age)fromtbl_advqry_seller)
（3）any(some),all??any(some):比较结果集中的任意一个示例：查询比其中一名男性大的女性员工的信息select*fromtbl_advqry_sellerwhereseller_sex=0andseller_age>any(selectseller_agefromtbl_advqry_sellerwhereseller_sex=1)
??
All:比较结果中的所有示例：查询比所有男性都大的女性员工的信息select*fromtbl_advqry_sellerwhereseller_sex=0andseller_age>all(
selectseller_agefromtbl_advqry_sellerwhereseller_sex=1)
（7）UPDATE、DELETE和INSERT中使用子查询示例：有卖出商品的销售员的工资加200updatetbl_advqry_sellersetseller_sal=seller_sal+200whereseller_namein(selectsale_master_personfromtbl_advqry_sale_master)
（8）子查询中使用别名示例：Selectau1.au_lname,au1.au_fname,au1.cityFromauthorsASau1Whereau1.cityin(selectau2.cityFORMauthorsasau2whereau2.au_fname=’Tom’andau2.au_lname=’karsen’)
第六部分：第六部分：常规索引管理
一、SQLServer索引1、索引对数据表中一个或多个字段进行排序后，存储对应的记录实际物理存储位置的数据结构
2、索引的作用SQL语句中，使用建立了索引的列作为查询条件时，通过二分法大大加快查询效率（针对小规模数据）加速GROUPBY的执行速度UNIQUEIndex强制执行唯一性约束
3、索引的适用范围作为查询条件的列频繁按照范围查询的列表连接中频繁使用的列
4、索引的优点和缺点优点加快访问速度加强行的唯一性缺点带索引的表在数据库中需要更多的存储空间。操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新。
5、创建索引的指导原则适合创建索引情况该列频繁用于进行搜索该列用于对数据进行排序不适合创建索引情况(表较小)表中的记录数较少表中仅包含几行列中仅包含几个不同的值
二、SQLServer索引分类1、按字段个数单值索引复合索引2、按生成方式自动索引(PrimaryKey,UniqueKey)手动索引3、按索引值的可重复性唯一性索引不唯一索引4、按对表记录位置的影响聚集索引非聚集索引
三、SQLServer索引生成原则一个表只可以有一个聚集索引，可以有多个非聚集索引，最多249个复合索引最多包含16个字段，不可以包含TEXT，NTEXT，IMAGE大类型字段基数(=Count(Distinct字段))大的字段适合建立索引小表只适合建立主键索引索引字段的长度<=900B对Text,Ntext,Image类大字段可以使用全文索引
四、复合索引使用特征1、复合索引?顺序优先:第一个索引字段必须出现在Where字句的第一个条件中。?例：Student?Code?Fname?Lname?Sex?…不使用索引：Select*FromStudentWhereLname=‘中华’使用索引：Select*FromStudentWhereFName=‘张’AndLname=‘中华’使用索引：Select*FromStudentWhereFName=‘张’
五、SQL语句的优化方案1、SQL构造法?通过构造对结果无影响的条件子句充分使用复合索引不使用索引：Select*FromStudentWhereLname=‘中华’使用索引：Select*FromStudentWhereFNameLike‘%’AndLname=‘中华’使用索引：Select*FromStudentWhereFNameIsNotNullAndLname=‘中华’
六、索引的存储特性1、顺序存储?需要在存放Index的页中设置必要的空闲空间，防止Index记录顺序插入时产生行迁移。
?11?33?44?66?77?99?1010?1111?1313?1414?2020?2222
2
?11?22?33?44?66
?77?99?1010?1111?1313?1414?2020
七、索引的创建与管事1、创建索引CreateUniqueClustered/NonClusteredIndex索引名On表名（字段集Asc/Desc)WithFillFactor=N
Drop_ExistingStatistic_NoRecomputeSort_In_TempDbOn文件组注意：FillFactor=0或100，表示索引页全部填充主键索引默认为聚集索引索引的命名：Ix_表名_字段名一个字段可以创建多个索引索引不可以修改，只可以删除后重建自动索引的优先级高于手工索引
2、删除索引?DropIndex表.索引自动索引无法通过DROP删除如果表中同时有聚集索引和非聚集索引，应先删非聚集索引，后删除聚集索引3、重建索引DBCCDbReIndex(表名，索引名，填充因子）WithNo_InfomSgs适合在索引页填充过满的时候重建
第六部分习题样例第六部分习题样例
1、创建具有关系的表并进行合理的数据规划创建城市表：城市编码城市名称520
邮政编码：6createtablecity(city_IDvarchar(5),city_namevarchar(20),
city_dakvarchar(6))
创建学生表：学生编码学生学生性别学生年龄学生生日学生籍贯学生国籍学生照片createtablestudent(stu_IDchar(13)constraintPK_student_stuIDprimarykey(stu_ID),stu_namevarchar(8)notnull,stu_sexchar(1)check(stu_sexin('F','M')),stu_agetinyint,stu_birthsmallint,stu_placevarchar(10),stu_countryvarchar(10),stu_imageimage)固定13位不为空，不重复F/M15－25
需求：--1、应用系统中经常对城市编码和学生进行模糊查询，请优化--城市编码应用主键自动产生的聚集索引进行优化altertablecityaddconstraintPK_city_codeprimarykey(city_id)
--学生字段创建非聚集索引以提高查询效率createindexix_student_queryname
onstudent(stu_name)withfillfactor=70,statistics_norecompute,sort_in_tempdbongroup_index
dropindexstudent.ix_student_queryname
--2、教学主管经常需要查询一下表格：--学生编码学生名称学生性别籍贯邮政编码
--请设计相关视图像并对查询速度进行优化；dropindexvw_query_by_charge
createindexix_student_placeonstudent(stu_place)withfillfactor=70ongroup_index
createviewvw_query_by_chargewithencryptionasselectA.stu_ID,A.stu_name,A.stu_sex,B.city_name,B.city_dakfromstudentA,cityBwhereA.stu_place=B.city_ID
--3、应用程序中经常需要对学生的“姓”进行筛选（使用函数SUBSTRING），请对该查询语句进行优化；altertablestudentaddstu_Fnameassubstring(stu_name,1,2)
createindexix_student_Fnameonstudent(stu_Fname)with
fillfactor=70,statistics_norecomputeongroup_index
第七部分：全文索引管理第七部分：全文索引管理
一、SQLServer全文索引1、工作原理
应用程序
SQLServer
DB数据文件
MicrosoftSearchService
全文索引
常规索引
PK
2、全文索引的特征
全文索引不是SQLSERVER的内部功能,其数据不是存放在SQLSERVER的数据文件中,而是在操作系统中单独使用目录存储常规索引中存放的是索引值和记录所在的物理位置,而全文索引中存放的是索引值和主键值,最后进行记录定位的时候是按主键定位的常规索引可以优化文本型数据,而全文索引可以索引VARCHAR、Char、Text、Image等类型.
二、SQLServer全文目录1、全文目录?ExecSp_FullText_Catalog‘名称’,‘动作’,‘目录’?动作：CreateDropStart_IncrementalStart_FullStopRebuild例：ExecSp_FullText_Catalog‘My_T’,‘Create’,‘C:"T15’
注意：一个数据库可以包含多个全文目录一个全文目录可以存放多个全文索引一个全文索引必须存放在一个全文目录中一个表只能有一个全文索引如果表的记录很多，而且对应的全文目中包含较多的列，则建议单独使用全文目录存放，以提高访问效率。2、全文目录的查询ExecSp_Help_FullText_Catalogs目录名
3、全文目录的禁用ExecSp_Help_FullText_Catalogs‘动作’动作：
Enable–在当前数据库启用全文索引Disable–在当前数据库禁用全文索引(注意：Disable时，全文目录的物理目录会自动被删除）
三、SQLServer全文索引1、全文索引ExecSp_FullText_Table‘表名’,‘动作’,‘目录’,‘PK约束’?动作：CreateDropActivateStart_Change_TrackingStop_Change_TrackingStart_Background_UpdateIndexStop_Background_UpdateIndexUpdate_IndexStart_FullStart_Incremental?2、全文索引的查询ExecSp_Help_FullText_Table全文索引,表名例：ExecSp_Help_FullText_TableMy_T15_01,Student
3、全文索引的创建ExecSp_Help_FullText_Table‘Student’,’Create’,’My_T15_01’,’Pk_Stu_Code’
注意：创建好的全文索引中并不包含任何字段，只是一个没有填充的空的索引，需要添加字段后填充
4、全文索引中添加字段?ExecSp_FullText_Column‘表名’,‘字段名’,‘动作’?动作：
AddDrop例：ExecSp_Help_FullText_Column‘Student’,’Stu_Remark’,’Add’
5、全文索引字段的查询ExecSp_Help_FullText_Column‘表名’,‘字段名’例：ExecSp_Help_FullText_Column‘Student’,’Stu_Remark’
6、全文索引填充的特点全文索引创建完成后必须进行一次完全填充（如果使用增量填充，系统依旧自动进行完全填充）为提高日常填充速度，建议使用增量填充（必要时可以使用调度程度来自动控制）如果希望对局部时段数据进行填充，可以使用“修改跟踪”开始跟踪某时间点开始的各种动作，后将更改的结果写入全文索引中，以达到精确控制填充时间的效果
增量填充
跟踪修改
跟踪修改填充
完全填充
四、全文索引的查询1、全文索引查询子句
Containts(‘字段’,‘比对字串’)--整句查询FreeText(‘字段’,‘比对字串’)示例：SQLServer将顺序查询Select*fromStudentWhereStu_Remark只有完全包含’IsMyHome’的记录被选择Select*fromStudentWhereContaints(‘Stu_Remark’,’IsMyHome’)只要包含’Is’、’My’、’Home’中任何字符的记录被选择出来Select*fromStudentWhereFreeText(‘Stu_Remark’,’IsMyHome’)Like‘%AB%‘--单词查询
第七部分习题样例
createtablecity(city_IDvarchar(5),city_namevarchar(20),city_dakvarchar(6))createtablestudentmemo(sut_IDchar(13)constraintFK_stu_memoreferencesstudent(stu_ID)constraintPK_stu_idprimarykey,stu_memotext)
foreignkeystu_memo(stu_ID)droptablestu_memo
altertablestu_memodropconstraintPK_stu_memo
altertablestu_memoaddconstraintFK_stu_memoforeignkeystu_memo(stu_ID)referencesstudent(stu_ID)
droptablestu_memo
createtablestudent(stu_IDchar(13)constraintPK_student_stuIDprimarykey(stu_ID),stu_namevarchar(8)notnull,stu_sexchar(1)check(stu_sexin('F','M')),stu_agetinyint,stu_birthsmallint,stu_placevarchar(10),stu_countryvarchar(10),stu_imageimage)
--根据实际需要创建相应的学生表并进行数据规划后：--1、激活存放该表的数据库，允许使用全文索引EXECsp_fulltext_database'Enable'
--2、为数据库创建两个全文目录，一个为F_Normal，一个为F_Big，--用来保存学生编码，学生和学生备注的全文索引；EXECsp_fulltext_catalog'F_Normal','create','d:"yy'EXECsp_fulltext_catalog'F_Big','create','d:"yy'
--3、为学生编码，学生，学生备注创建全文索引（需要进行分类规划）；EXECsp_fulltext_table'student','create','F_Normal','PK_student_stuID'
EXECsp_fulltext_column'student','stu_ID','add'EXECsp_fulltext_column'student','stu_name','add'
EXECsp_fulltext_table'stu_memo','create','F_Big','PK_stu_id'EXECsp_fulltext_column'stu_memo','studentmemo','add'
--4、对学生备注所再的全文索引进行完全填充；EXECsp_fulltext_table'studentmemo','start_full'
--5、为学生编码、学生所在的全文索引进行增量填充；EXECsp_fulltext_table'student','start_full'
--6、为学生备注所在的全文索引进行时段跟踪并用前台方式填充；EXECsp_fulltext_table'studentmemo','start_change_Tracking'EXECsp_fulltext_table'studentmemo','stop_change_Tracking'EXECsp_fulltext_table'studentmemo','update_index'
--7、对全文目录F_Normal进行填充；EXECsp_fulltext_catalog'F_Normal','start_full'
--8、在全文目录中查询学生备注中有关“热爱计算机”字串的有关记录；select*fromstudentmemowherecontains('stu_memo','热爱计算机')
--9、在全文目录中查询学生备注中有关学习过JAVA、C++、VB的所有人员资料；select*fromstudentwherefreetext('stu_memo','JAVAC++VB')
--10、删除有关学生编码的全文索引;EXECsp_fulltext_column'student','stu_id','drop'
--11、删除全文目录F_Normal;EXECsp_fulltext_catalog'F_Normal','drop'
第八部分：第八部分：数据库的备份和恢复
一、SQLServer备份设备1、设备分类物理设备
物理设备是操作系统中实际的目录和文件逻辑设备逻辑设备是SQLServer中的虚拟设备，和某一物理设备一一对应，以保证用户和服务器目录结构的逻辑分离逻辑设备的定义被永久的保存在Master的SysDevices表中
2、设备的管理创建设备ExecSp_AddumpDevice‘设备类型’,‘逻辑名’，‘物理文件’其中：设备类型–‘Disk’：磁盘文件‘Tape’：磁带机
删除设备ExecSp_DropDevice‘逻辑名’,‘DelFile’其中：DelFile为可选参数，添加该参数后，删除逻辑设备时候将自动删除对应的数据文件
查看设备信息ExecSp_HelpDevice‘逻辑名’
3、设备的管理(示例）添加ExecSp_AddumpDevice‘Disk’,’Dev_T15’,’D:"T15_Data"T15_Backup.bak’删除ExecSp_DropDevice’Dev_T15’,’DelFile’查询ExecSp_HelpDevice’Dev_T15’查询一个备份设备里的详细信息，包括多次的备份明细LoadHeaderOnlyFrom‘逻辑名’
二、SQLServer备份策略1、设备策略
完全备份BackupDatabase库名ToDisk=‘文件名’[设备名]差异备份BackupDatabase库名To设备名WithDifferential
文件和文件组备份BackupDatabase库名File=‘逻辑文件名’To设备FileGroup=‘文件组’
日志备份BackupLog库名To设备名WithNo_Truncate注意：日志备份后，系统将自动清除备份点以前的日志资料，如果使用WithNo_Truncate参数将不清除日志资料
2、备份策略（演示）完整：BackupDatabaseClass_T15ToDev_T15差异：BackupDatabaseClass_T15ToDev_T15WithDifferential日志：BackupLogClass_T15ToDev_T15WithNo_Truncate文件：BackupDatabaseClass_T15File=‘T15_Normal’ToDev_T15文件组：BackupDatabaseClass_T15FileGroup=‘G_Normal’ToDev_T15
3、备份设备分析通过备份，系统多次将数据库的数据备份到同一个数据文件中，数据文件分区域存储每次的备份资料，恢复时必须指定相应的备份区域。
查询备份设备区域信息：LoadHeaderOnlyFrom‘设备逻辑名’BackupType:????1–数据库2–事物日志4–文件5–差异数据库
?
6–文件组
三、SQLServer数据恢复1、数据库恢复(对完全备份和差异备份）RestoreDatabase库名From设备名WithFile=N,NoRecovery/Recovery,Replace,Restore
解析：WithFile=N–指定设备中用于进行恢复的文件区域号RecoveryReplaceRestore-是否回滚未完成的事务-恢复时是否创建新库或覆盖已有数据库-是否在上次恢复过程中意外中断的位置继续开始恢复数据
2、数据库恢复(对日志）RestoreLog库名From设备名WithFile=N,NoRecovery/Recovery,Restart,Stopat=‘时间点（DateTime)’?解析：WithFile=N–指定设备中用于进行恢复的文件区域号RecoveryRestore-是否回滚未完成的事务-是否在上次恢复过程中意外中断的位置继续开始恢复数据
3、数据库恢复(对文件和文件组）RestoreDatabase库名File=‘文件名’/FileGroup=‘组名’From设备名WithFile=N,NoRecovery/Recovery,Replace
解析：WithFile=N–指定设备中用于进行恢复的文件区域号RecoveryReplace-是否回滚未完成的事务-恢复时是否创建新库或覆盖已有数据库
四、备份和恢复策略的配合1、备份的特点完全备份是其他任何一种备份策略的基础，数据库必须进行一次完全填充（如果第一次使用增量备份，系统依旧自动进行完全备份）。为提高日常备份速度，建议使用增量备份（必要时可以使用调度程度来自动控制）。恢复时候，建议使用完全恢复。经常对保存变化表的文件和文件组进行备份，保证变化数据的安全性。
完全备份
增量备份
文件/文件/文件组备份
日志备份
第八部分习题样例
数据库备份和恢复练习：数据库备份和恢复练习：--1、创建数据库文件，包含规划过的四个数据文件组；
--2、创建逻辑设备“MY_W16”；EXECsp_dropdeviceW16Backup
EXECsp_addumpdevice'DISK','W16Backup','D:"yy"yy.bak'
EXECsp_helpdeviceW16Backup--3、对数据库文件进行完全备份到设备中；backupdatabasew16toW16Backup
--4、对数据库文件进行增量备份到设备中；backupdatabasew16toW16Backupwithdifferential
--5、备份变化表数据组到设备中；backupdatabasew16FILEGroup='Group_change'toW16Backup
--6、备份日志到设备中，并删除日志信息；backuplogw16toW16Backup
--7、对数据库进行完全恢复，使用问题3中所做的备份；loadheaderonlyfromW16backuprestoredatabasew16fromW16backupwithfile=3
--8、对数据库进行恢复，使用5中所使用的备份；restoredatabasedatabasew16fromW16backupwithfile=5,Recovery
第九部分：第九部分：数据库的安全管理
一、SQLServer安全体系1、三级安全体系访问安全性逻辑安全性物理安全性--权限控制--约束机制--备份和恢复机制
2、SQLServer的身份认证方式
Windows身份认证SQLServer身份认证
3、SQLServer的安全决策过程
应用系统
身份认证模式
Windows认证模式
Win身份是否合法
帐号是否合法
是否允许连接
帐号和密码有效性
SQLServe连接成功
拒绝连接
拒绝连接
混合认证模式
二、SQLServer帐号的管理1、创建帐号ExecSp_AddLogin‘帐号’,‘密码’2、删除帐号ExecSp_DropLogin‘帐号’3、查询帐号信息ExecSp_HelpLogins‘帐号’
三、SQLServer角色的管理SQL
拒绝连接
1、角色的分类服务器角色数据库角色自定义角色?2、服务器角色?SysAdmin?SecurityAdmin?ServerAdmin?SetupAdmin?ProcessAdmin?DiskAdmin
?DbCreator?BulkAdmin
固定服务器角色sysadminserveradminsetupadmin描述可以在SQLServer中执行任何活动。可以设置服务器范围的配置选项，关闭服务器。可以管理链接服务器和启动过程。可以管理登录和CREATEDATABASE权限，还可以读取错误日志和更改密码。processadmindbcreatordiskadminbulkadmindiskadmin可以管理在SQLServer中运行的进程。可以创建、更改和除去数据库。可以管理磁盘文件。可以执行BULKINSERT语句。可以管理磁盘文件。
securityadmin
3、数据库角色?DB_Owner?DB_AccessAdmin?DB_SecurityAdmin?DB_DDLAdmin?DB_BackupAdmin?DB_DataReader?DB_DataWriter?DB_DenyDataReader?DB_DenyDataWriter
四、帐号和角色的绑定1、帐号和服务器角色的绑定绑定ExecSp_AddSrvRolemember‘帐号’,‘服务器角色名’取消绑定ExecSp_DropSrvRolemember‘帐号’,‘服务器角色名’
2、帐号和数据库角色的绑定绑定ExecSp_AddRolemember‘帐号’,‘数据库角色名’取消绑定ExecSp_DropRolemember‘帐号’,‘数据库角色名’
五、自定义角色的管理（角色的创建和绑定）自定义角色的管理（角色的创建和绑定）创建ExecSp_AddRole‘自定义角色名’删除ExecSp_DropRole‘自定义角色名’绑定ExecSp_AddRoleMember‘帐号’,‘自定义角色名’取消绑定ExecSp_DropRoleMember‘帐号’,‘自定义角色名’查询角色成员信息ExecSp_HelpRoleMember‘自定义角色名’
六、SQLServer权限的管理1、连接权限帐号连接到SQLServer上，并不能访问任何的数据库，必须授予数据库的连接权限授予连接权限Use数据库名ExecSp_GrantDbAccess‘帐号’回收连接权限Use数据库名ExecSp_RevokeDbAccess‘帐号’
2、对象权限对象分类：查询对象，执行对象Insert
UpdateDeleteSelectExecute–针对ProcedureReferenceAll
3、语句权限CreateDatabaseCreateTableCreateViewCreateRuleCreateProcedureCreateFunctionCreateDefaultBackupDatabaseBackupLog
4、对象权限的管理帐号/角色授权Grant对象权限On对象名To帐号/角色帐号/角色权限回收Revoke对象权限On对象名From帐号/角色
5、语句权限的管理帐号/角色授权Grant语句权限To帐号/角色帐号/角色权限回收Revoke语句权限From帐号/角色
七、SQLServer安全体系构造
创建登陆帐号
授予服务器角色
授予数据库角色
授予自定义角色
授予语句和对象权限
授予数据库连接权限
访问数据
第九部分习题样例
安全管理：SQL安全管理：单位新来了一个管理员，现需要让他管理一下内容，请适当授权：--允许他控制SQLSERVER性能，在必要的时候切断部分用户的连接来保证整体性能；EXECsp_addlogin'admin','admin'EXECsp_addsrvrolemember'admin','processadmin'
--允许启动或关闭SQLAGENT服务；EXECsp_addsrvrolemember'admin','serveradmin'
--允许对其他用户授权；EXECsp_addsrvrolemember'admin','securityadmin'
--后发现权利过大，收回其授权的权利；EXECsp_dropsrvrolemember'admin','securityadmin'--允许连接内部人事数据库HRuseHREXECsp_grantdbaccess'admin'
--允许读写所有的表EXECsp_addrolemember'DB_datareader','admin'EXECsp_addrolemember'DB_datawriter','admin'
--允许备份数据EXECsp_addrolemember'DB_backupoperator','admin'
--但发觉不应该由管理员来修改表，所以取消写入权限；EXECsp_droprolemember'DB_DENYdatawriter','admin'
--允许偶尔根据数据规划原则修改部分表的结构提升性能；EXECsp_addrolemember'DB_DDLAdmin','admin'
--发觉不应该让其查询到人员工资资料，所以，收回读取权限；
EXECsp_droprolemember'DB_DENYdatareader','admin'
--但给予MEMBER表、BASEINFO表的所有控制权限；grantallonmember,baseinfotoadmin
--要求其根据业务需要来做DBP程序；grantcreateproceduretoadmingrantcreatefunctiontoadmin
--要求其经常备份数据库；grantbackupdatabasetoadmin
--如果来了新的管理员，全部按照该指责来分配权限；EXECsp_addrole'Role_Admin'EXECsp_addsrvrolemember'Role_Admin','processadmin'EXECsp_addsrvrolemember'Role_Admin','serveradmin'EXECsp_addrolemember'DB_datareader','Role_Admin'EXECsp_addrolemember'DB_backupoperator','Role_Admin'EXECsp_addrolemember'DB_DDLAdmin','Role_Admin'grantallonmember,baseinfotoRole_AdmingrantcreateproceduretoRole_AdmingrantcreatefunctiontoRole_AdmingrantbackupdatabasetoRole_Admin
第十部分：第十部分：T-SQL编程基础
一、SQLServer中的批处理1、批
将多条具有相关性的语句作为一个有机整体发送到数据库中进行编译和生成执行计划，以提高整体的执行效率批处理之间以“Go”分隔，无论上一个批是否执行正确，下一个批都将自动执行批处理方式无法保证数据的同步性和一致性，可以通过带事务的批解决该问题
2、批中的错误编译错误编译过程中产生，批执行立即停止轻微错误跳过错误语句，继续执行（违反约束)致命错误中断执行，甚至可能中断连接
SQL语句1SQL语句2SQL语句3…Go
BeginTranSQL语句1SQL语句2SQL语句3…If@@Error=0CommitTranElseRollBack
3、批的调整和事务性
二、编程体系中的主要元素常量和变量（局部和全局）数据类型（系统和自定义）流程控制过程和函数
三、1、
使用变量
一个变量整体，不可分割！
变量是可以存储数据值的对象。2、局部变量的定义与赋值声明：declare@variableName[变量名称]datatype[数据类型]
赋值，可以使用SET或SELECTset赋值：set@variableName=expressionselect赋值：select@variableName=expression3、全局变量使用两个@@标记做前缀。四、T-SQL中的变量1、全局变量?命名：@@变量名?常用全局变量?@@Error?@@Fetch_Status
?@@Idle?@@Max_Connections?@@ServerName?@@TranCount?@@RowCount?@@Version?@@Cursor_Rows
2、局部变量申明定义Declare@变量名类型赋值Set@变量名=值
Select@变量名=值显示Print@变量名
Select@变量名
五、
逻辑控制语句
在SQL中，经常需要在一些行上重复执行或根据条件执行一组操作。为了实现这一点，SQLServer提供了程序设计结构。这些结构称为控制流语句。
表：控制流语句的总结
流程控制结构的语法BEGINSQL语句块ENDRETURN该语句提供了一种方法，用于从存储过程或查询中正常退出。GOTO
