MS SQL Server数据库原理

MS SQL Server数据库原理

一、 数据库基本概念

1. 数据库系统（DBS）：采用数据库技术的计算机系统，由数据库（数据）、数据库管理系统（软件）、数据库管理员（人员）、硬件平台（硬件）和软件平台（软件）五部分构成的运行实体。
2. 数据库管理系统：位于用户与操作之间的一层数据管理软件。
3. 关系数据库：支持关系模型的数据库。关系模型有数据结构（二维表）、关系操作集合（关系代数、关系演算、二者结合特点的SQL语言）和完整性约束（实体完整性、参照完整性和用户定义的完整性）三部分组成。
4. SQL=Structured Query Language(结构化查询语言)

二、 数据库常用对象

1. 表：包含数据库中所有数据的数据库对象，由行和列组成，用于组织和存储数据。
2. 字段：表中每列称为一个字段，字段有有自己的属性。
3. 索引：单独的、物理的数据库结构。依赖于表建立的，是数据库程序无须对整个表进行扫描，就可以找到所 需数据。
4. 视图：从一张或多张表中导出的表（也称虚拟表），是用户查看数据表中数据的一种方式。
5. 存储过程：是一组为了完成特定功能的SQL语句集合

三、 数据库组成=文件+文件组

1. 文件：储存数据库中的所有数据和对象（如表、存储过程和触发器）
   主要数据文件.mdf，存放数据和数据库的初始化信息。
   次要数据文件.ndf，存放主要数据库以外的所有数据文件。
   事务日志文件.ldf，存放用于恢复数据库的所有日志信息。
2. 文件组
   主文件组：包含主要数据文件和任何没有明确指派给其他文件组的文件。
   用户定义文件组：在CREATE DATABASE或ALTER DATABASE语句中，使用FILEGROUP关键字指定的文件组。

四、 系统数据库

1. Master数据库：记录SQL Server实例的所有系统级信息，包括实例范围的元数据、端点、连接服务器和系统配置设置。
2. Tempdb数据库：临时数据库，用于保存临时对象或中间结界集。
3. Model数据库：用作SQL Server实例上创建的所有数据库的模板。
4. Msdb数据库：用于SQL Server代理计划警报和作业。

五、 SQL Server命名规则

1. 标识符格式
   首字母必须为：统一码（Unicode）所定义的字母或‘_’、‘@’（表示局部变量或参数）、‘#’（表示临时表或过程），‘##’开始的标识符表示全局临时对象。
2. 标识符分类
   常规标识符：符合标识符的格式规则。
   分隔标识符：包含在双引号“”或者方括号[]内的标识符。该标识符可以不符合标识符的格式规则。
3. 对象命名规则
   数据库对象的名字由1~128个字符组成，不区分大小写。完整名称应该由服务器名、数据库名、拥有者名和对象名四部分组成，格式如：
   [[[server.][database].][owner_name].]object.name
   服务器、数据库和所有者的名称即所谓对象名称限定符。
   对象名的有效格式如下：
   Server.database.owner_name.object_name
   Server.database…object_name
   Server…owner_name.object_name
   Server…object_name
   Database.owner_name.object_name
   Database…object_name
   Owner_name.object_name
   Object_name
   指定了所有四个部分的对象名称被称为完全合法名称。

六、 数据库的创建与管理（创建、修改、删除）

七、 数据表基础知识

1. 基本数据类型
   整形数据类型：BIT INT SMALLINT TINYINY
   货币数据类型：MONEY SMALLMONEY
   浮点数据类型：REAL FLOAT DECIMAL NUMERIC
   日期/时间数据类型：DATETIME SMALLDATETIME DATA DATETIME DATETIMESTAMPOFFSET
   字符数据类型：CHAR NCHAR(n) VARCHAR NVARCHAR(n)
   二进制数据类型：BINARY VARBINARY
   图像和文本数据类型：TEXT NTEXT(n) IMAGE
   用户自定义数据类型：不是真正的数据类型，提供一种加强数据库内部元素和基本数据类型之间一致性的机制。

八、学习管理数据表

1. 创建数据表
2. 修改数据表
3. 创建、删除和修改约束
   ①非空约束：确定列中是否允许空值的关键字。
   ②主键约束：能够唯一地标识表中的每一行。
   ③唯一约束：当在表中已创建主键，但又要保证其他数据列的值唯一是，可以使用唯一（UNIQUE）约束。
   ④检查约束：通过限制输入到列中的值来强制域的完整性。
   ⑤默认约束：用户可以定义一个值，每当用户没有在某一列中输入值是，则将所定义的值提供给这一列。
   ⑥外键约束：外键（FK）用于建立两个表数据之间连接的一列或多列。

九、 关系的创建与维护
SQL Server当数据库中包含多个表时，需要通过主关键字来建立表间的关系，使各表之间能够协调工作。
关系是通过匹配键列中的数据而工作的，而键列通常是两个表中具有相同名称的列，在数据表间创建关系可以显示某个表中的列连接到另一个表中的列。

1. 一对一关系
   表A中的一条记录确实在表B中有且只有一条相匹配的记录。特点：分隔一个含有许多列的表；出于安全考虑而隔离表的某一部分；存储可以很容易删除的临时数据，只需删除表即可删除这些数据；存储只应用与主表子集的信息。
2. 一对多关系
   表A中的行可以在表B中有许多匹配行，但表B中的行只能在表A中有一个匹配行。
3. 多对多关系
   两个表彼此为一对多的关系，则为多对多关系。

十、 视图
视图为数据呈现提供了多样的表现形式，用户可以通过它浏览表中感兴趣的数据。视图可分为：标准视图、索引视图、分区视图。

1. 创建视图
2. 查看视图信息
3. 在视图中插入数据记录
4. 在视图中修改数据记录
5. 在视图中删除数据记录
6. 删除视图

十一、 SQL Server 2008维护

1. 脱机与联机数据库
2. 分离和附加数据库
3. 导入导出数据
4. 备份和恢复数据库
5. 收缩数据库和文件
6. 生成与执行SQL脚本
   脚本是存储在文件中的一系列SQL语句，是可再用的模块化代码。

十二、 T-SQL概述
T-SQL是标准的SQL程序设计语言增强版，是用以程序与SQL Ssever沟通的主要语言。

1. T-SQL语言组成
   数据定义语言：DDL，用于对数据库、表、视图、索引等数据库对象进行创建和管理。
   数据控制语言：DCL，用于对数据完整性、安全性等控制。
   数据操作语言：DML，用于插入、修改、删除和查询数据。
2. T-SQL语句结构
   每条SQL语句均由一个谓词（Verb）开始，该谓词描述这条语句要产生的动作，后面紧接一个或多个子句，子句给出被谓词作用的数据或提供谓词动作的详细信息，每一条子句都由一个关键字开始。
   SELECT 子句
   INTO 子句
   FROM 子句
   WHERE 子句
   GROUP BY 子句
   HAVING 子句
   ORDER BY 子句
3. 变量
   声明局部变量需用DECLARE
   DECLARE
   {
   @varaible_name datatype [,…n ] --局部变量名必须@开头
   }
   SELECT @varaible_name = expression --SET亦能赋值
   [FROM table_name[,…n ] WHERE clause] --局部变量赋值
4. 运算符
   不等于运算符： <>
   逻辑运算符：ALL（全对）、AND（与）、ANY（有一个对）、BETWEEN（范围内）、EXISTS（存在）、IN（在其中）、LIKE（匹配）、NOT（非）、OR（或）、SOME（有一些对）
   位运算符：&（按位AND）、|（按位OR）、^（按位异或）、~（按位NOT）
5. 通配符
   匹配指定范围内或者属于方括号所指定的集合中的任意单个字符。在SQL中通常用LIKE关键字与通配符结合起来实现模拟查询。
   % ：包含零个或更多字符的任意字符。’’loving%’’可以表示：’’loving’’、’’loving you’’、’’loving?’’.
   （下划线）：任何单个字符。”loving”可以表示：”lovingc”，后面只能再接一个字符。
   [] ：指定范围（[a_{f]）或集合（[abcdef]）中的任何单字符。[0}9]123表示以0~9之间任意一个字符开头，以’123’结尾的字符。
   [^] ：不属于指定范围内的任何单个字符。[^0_{5]123表示不以0}5任意一个字符开头，却以’123’结尾的字符。
6. 流程控制
   BEGIN --用于将多个Transact-SQL语句组合成为一个逻辑块。
   {sql_statement…}
   END
   IF<条件表达式> --选择判断
   {命令行1|程序块1}
   ELSE
   {命令行2|程序块2}
   CASE input_expression --多重选择
   WHEN when_expression THEN result_expression
   […n]
   ELSE else_result_expression
   END
   WHILE<条件表达式> --循环结构
   BEGIN
   <命令行|程序块>
   END
   RETURN[整数值] --从查询或过程中无条件退出
   返回值 含义
   F 程序执行成功
   -1 找不到对象
   -2 数据类型错误
   -3 死锁
   -4 违反权限原则
   -5 语法错误
   -6 用户造成的一般错误
   -7 资源错误，如磁盘空间不足
   -8 非致命的内部错误
   -9 已达到系统的极限
   -10或-11 致命的内部不一致性错误
   -12 表或指针破坏
   -13 数据库破坏
   -14 硬件错误
7. 常用命令
   ①DBCC（数据库一致性检查程序）
   DBCC CHECKALLOC --检查指定数据库的磁盘空间分配结构的一致性。
   DBCC SHOWCONTIG --显示指定表的数据和索引的碎片信息。
   ②CHECKPOINT
   用于检查当前工作的数据库中被更改过的数据页或日志页，并将这些数据从数据缓冲器中强制写入硬盘。
   ③DECLARE
   用于声明一个或多个局部变量、游标变量或表变量。
   ④PRINT
   向客户端返回一个用户自定义的信息，显示一个字符串、局部变量或全局变量的内容。
   PRINT msg_str|@local_variable|string_expr --|为或的意思
   ⑤RAISERROR
   用于返回错误信息同时返回用户指定的信息。
   ⑥READTEXT
   用于读取text、ntext或image列中的值，从指定的位置开始读取指定的字符数。
   READTEXT {table.column（从中读取的表和列的名称） text_ptr（有效文本指针） offset（开始读取前跳过的字节数） size（要读取的字节数）} [HOLDLOCK]（是文本值一直锁定到事务结束）
   ⑦BACKUP
   用于将数据库内容或其事务处理日志备份到存储介质上。
   ⑧RESTORE
   将数据库或其事务处理日志备份文件由存储介质还原到SQL Server系统中。
   ⑨SELECT
   除了有强大的查询功能外，还可用于给变量赋值。
   ⑩SET
   一是用于给局部变量赋值，二是用于执行SQL命令时SQL Server的处理选项设定。
   ⑪SHUTDOWN
   用于立即停止SQL Server执行。
   ⑫WRITETEXT
   允许对数据类型为text、ntext、image的列进行交互式更新。
   ⑬USE
   用于在前工作区打开或关闭数据库。

十三、 Delphi中开发数据库应用程序基础

1. 数据库设计过程
   1.1 数据库的建立
   ①确定数据库的使用范围。
   ②确定支持数据库所需要的字段。
   ③将字段划分成一些合理的数据表格。
   ④确定数据表格之间的关联。
   1.2 数据表格的结构
   在数据表格结构中需要一个关键字段，数据表格中的数据就是按主关键字段的顺序存放的，而且利用主关键字段能够高效地与其他数据表格建立关联。利用索引可以加快访问速度。
   1.3 数据库应用程序的开发步骤
   ①初步设计
   ②功能实现
   ③运行和维护程序

2. SQL的基本查询功能
   2.1 SELECT语句
   从表中选择出符合条件的记录。
   Eg：SELECT Name,Age FROM Employee WHERE Age>20;
   2.2 Distinct语句
   是忽略所选字段数据重复情况。针对某个字段查询出来的记录结果是具有代表性的惟一的。
   Eg：Select Distinct(ID) FROM Employee where Age>20;
   2.3 TOP和ORDER BY语句
   TOP语句实现从第一条或最后一条开始（利用ORDER BY条件子句），返回特定记录的数据的功能。
   Eg：SELECT TOP 25 学生姓名 FROM 学生表 WHERE 毕业年份=2004 ORDER BY 毕业成绩平均分数 DESC;
   若无ORDER BY条件，所得数据则是随机的，其中排序参数：递减DESC；递增ASC。
   2.4 IN条件子句
   指定查询哪一个外部数据库的表。
   Eg：SELECT 顾客编号 FROM 顾客表 IN CUSTOMER.MDB WHERE 顾客编号 LIKE “A*”;
   2.5 HAVING条件子句
   指定一特定的分组记录，并满足HAVING所指定的条件或状态，但条件是针对分组条件设置。
   Eg：SELECT 分类编号,Sum(库存数量) FROM 产品表GROUP BY 分类编号 HAVING Sum(库存数量)>100 AND 产品名称 LIKE “纸”；
   2.6 GROUP BY 条件子句
   依据指定的字段，将有相同数值的记录合成一条。
   Eg：SELECT姓名,Count(姓名) AS 职员姓名 FROM 职员表 WHERE 部门名称=’业务部’ GROUP BY 姓名;
   2.7 BETWEEN…AND 运算符
   决定某一数值是否介于特定的范围之内。
   Eg：SELECT 姓名，年龄 BETWEEN 25 AND 30 FROM 职员表;
   2.8 LIKE操作数
   将一字符串与另一特定字符串样式比较，并将符合该字符串样式的记录过滤出来。
   Eg：LIKE “李”;
   2.9 SQL的其他应用
   ①SQL数字函数：
   AVG：算数平均数。
   COUNT：计算记录条数。
   FIRST与LAST：返回某字段第一条数据与最后一条数据。
   MAX与MIN：返回某字段的最大值与最小值。
   SUM：返回某特定字段或是运算的总和数值。
   ②多层SQL查询
   一个SQL语句中包含另一个SQL语句，形成内部嵌套。SELTCT语句构成的多层SQL查询，必须用()将该语句括起来。
   Eg：SELECT FROM 产品表 WHERE 单位价格>ANY(SELECT 单位价格 FROM 订单表 WHERE 折扣>=.25)
   ③SQL与数据库的维护
   1）表的建立CREATE TABLE语句
   CREATE TABLE 职员表 (姓名TEST,部门TEST,职员编号 INTEGER CONSTRAINT 职员字段 索引 PRIMARY KEY)
   2）表的索引建立CREATE INDEX语句
   CREATE INDEX 新索引名称 ON 职员表(姓名部门)
   3）表的删除DELETE语句
   DELETE * FROM 职员表 WHERE 姓名=’李四’;
   4）SELECT…INTO语句：利用已存在的表格查询，建立一个新表。
   SELECT 职员表.姓名,职员表.部门 INTO 训练名册 FROM 职员表 WHERE 职称=’新进人员’;
   5）INNER JOIN操作数：当某一个共同的字段数据相等时，将两个表的记录加以组合。
   SELECT 分类名称,产品名称 FROM 分类表 INNER JOIN 产品表 ON 分类表.分类编号=产品表.分类编号;
   6）UNION操作数：将两个以上的表或查询的结果组合起来。
   TABLE 新客户表 UNION ALL SELECT * FROM 顾客表 WHERE 订单数量>100;
   7）ALTER语句：修改字段设计。
   ALTER TABLE 职员表 ADD COLUMN 薪水 CURRENCY;
   8）DROP语句：删除所指定的表、字段或者索引。
   DEOP INDEX MyIndex ON Employee；
   9）INSERT INTO语句：新建一条数据到表当中。
   INSERT INTO 客户数据表 SELECT 新客户数据表.FROM 新客户数据表;
   10）UPDATE语句：通过条件的限制来修改特定的数据。
   UPDATE 订单表 SET订单数量=订单数量1.1,运费=运费1.3 WHERE 运达地点=’上海’;

3. Delphi访问数据库的机制
   3.1 ODBC体系结构
   由应用程序、驱动程序管理器、驱动程序和数据源等部件组成。
   3.2 BDE
   Borland公司自己开发的数据库引擎（Borland Database Engine）。通过别名机制实现了建立与管理和各种数据库的连接，包括本地数据库Paradox、dBASE、FoxPro和Access，远程数据库如Oracle、DB2、MS SQL SERVER、Sybase等。
   3.3 ADO
   微软提供的一项技术，能让用户快熟访问关系型或非关系型数据库以及E-Mail和文件系统。
   Delphi对ADO的支持，是为了让用户能迅速实现对终端用户用来做商业决策的数据的一致性访问，结合Delphi本身的开放式数据组件结构，用户可以很快地建立应用程序，把自己的商业数据通过Internet发送给客户、最终用户以及整个销售环节。
   3.4 dbExpress
   基于Linux操作系统而言。

十四、 存储过程和触发器

1. 存储过程的概念
   存储过程是预编译SQL语句的集合，这些语句存储在一个名称下并作一个单元来处理。它接受输入参数并以输出参数的格式向调用过程或批处理返回多个值；包含用于在数据库中执行操作（包括调用其他过程）的编程语句；向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。
   系统存储过程：用来管理SQL Server和显示有关数据库和用户信息的存储过程。
   自定义存储过程：用户在SQL Server中通过采用SQL语句创建存储过程。
   展存储过程：通过编程语言创建外部例程，并将这个例程在SQL Server中作为存储过程使用。

2. 存储过程的优点
   2.1 存储过程可以嵌套使用，支持代码重用。
   2.2 存储过程可以接受与使用参数动态执行其中的SQL语句。
   2.3 存储过程比一般的SQL语句执行速度快。存储过程在创建是已经被编译，每次执行时不需要重新编译，而SQL语句每次执行都需要。
   2.4 存储过程具有安全特性（如权限）和所有权链接，以及可以附加到它们的证书，用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。
   2.5 存储过程允许模块化设计。存储过程一旦创建，以后即可在程序中调用任意多次，这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。
   2.6 存储过程可以减少网络通信流量。
   2.7 存储过程可以增强应用程序的安全性。

3. 创建存储的过程
   3.1 使用向导创建存储过程
   3.2 使用CREATE PROC语句创建存储过程
   Create Procedure Proc_Student
   @Proc_Sno int
   As
   Select *from Student where Sro=@Proc_sno

4. 管理存储过程
   4.1 执行存储过程
   Exec Proc_Stu
   4.2 查看存储过程
   使用Sys.sql_modules查看存储过程的定义
   Select *from sys.sql_modules
   使用OBJECT_DEFINITION查看存储过程的定义
   OBJECT_DEFINITION(object_id)
   Object_id:要使用的对象的ID。
   使用sp_helptext查看存储过程的定义
   sp_helptext[@objectname=]’name’[,[@columnname=]computed _column_name]
   4.3 修改存储过程
   ALTER PROCEDURE语句
   4.4 重命名存储过程
   4.5 删除存储过程

5. 触发器概述
   5.1 触发器的概念
   Microsoft SQL Server提供两种机制强制使用业务规则和数据完整性，即约束和触发器。
   触发器是一种特殊类型的存储过程，当指定表中的数据发送变化时触发器自动生效。触发器可以看作定义表的一部分，但不能通过名称来调用，更不允许设置参数。
   在SQL Server中一张表可以有多个触发器，用户可以使用INSERT、UPDATE或DELETE语句对触发器进行设置，也可以对一张表上的特定操作设置多个触发器。不论触发器所进行的操作有多复杂，触发器都只作为一个独立的单元被执行，被看作是一个事务，如果在执行触发器的过程中发生了错误，则整个事务将会自动回滚(ROLLBACK)。
   5.2 触发器的优点
   ①触发器自动执行，对表中的数据进行修改后，触发器立即被激活。
   ②触发器可以调用一个或多个存储过程，甚至可以通过调用外部过程（不是数据库管理系统本身）完成相应的操作。
   ③触发器能够实现比CHECK约束更为复杂的数据完整性约束。CHECK约束不允许引用其他表中的列来完成检查工作，而触发器可以引用其他表中的列。触发器更适合在大型数据库管理系统中用来约束数据的完整性。
   ④触发器可以检测数据库内的操作，从而取消了数据库未经允许的更新操作，使数据库修改、更新操作更安全，数据库的运行也更稳定。
   ⑤触发器能够对数据库中的相关表实现级联更改。触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联更改。
   ⑥一个表中可以同时存在3个不同操作的触发器（INSERT、UPDATE和DELETE）.
   5.3触发器的种类
   DML触发器：当数据库中发生出具操作语言（DML）时间时调用。
   除去INSERT、UPDATE、DELETE还可以设计AFTER触发器、INSTEAD OF触发器、CLR触发器。
   DDL触发器：是一种特殊的触发器，它在响应数据定义语言（DDL）语句时触发，可以用于在数据库中执行管理任务。
   登录触发器将为响应LOGON事件而激发存储过程。

十五、 索引与数据完整性

1. 索引的概念
   索引可以让用户可以在数据库中快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。通过创建设计良好的索引以支持查询，可以显著提高数据库查询和应用程序的性能。索引可以减少为返回查询结果集而必须读取的数据量。索引还可以强制表中的行具有唯一性，从而确保数据的完整性。
   索引是一个而单独的、物理的数据结构，在SQL Server中，索引是为了加速对表中数据行的检索而创建的一种分散存储结构。一个表的存储是由两部分组成的，一部分用来存放表的数据页，另一部分存放索引页。

2. 索引的优缺点
   2.1 优点：
   （1）创建唯一性索引，保证数据库表中每一行数据的唯一性。
   （2）大大加快数据的检索速度。创建索引的主要原因。
   （3）加速表与表之间的连接，在实现数据的参看完整性方面特别有意义。
   （4）使用分组和排序子句进行数据检索时，同样可以减少查询分组和排序的时间。
   （5）通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统性能。
   2.2 缺点：
   ①创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
   ②索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果建立聚集索引，那么需要的空间就会更大。
   ③当对表中的数据进行增加、删除和修改时，索引也要动态地维护，降低了数据的维护速度。

3. 索引的分类
   主要类型有：聚集索引、非聚集索引、唯一索引、包含性列索引、索引视图、全文索引、空间索引、筛选索引和XML索引。
   3.1 聚集索引（CLUSTERED）
   聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。
   除了个别表之外，每个表都应该有聚集索引。只有当表包含聚集索引是，表中的数据行才能按排序顺序存储。
   区级索引实现方式：
   ①PRIMARY KEY和UNIQUE约束
   在创建PRIMARY KEY约束时，如果不存在聚集索引或唯一非聚集索引，则将自动对一列或多列创建唯一聚集索引。
   在创建UNIQUE约束时，默认情况下将创建唯一非聚集索引，以便强制UNIQUE约束。
   ②独立于约束的索引
   指定非聚集主键约束后，用户可以对非主键的列创建聚集索引。
   ③索引视图
   若要创建索引视图，请对一个或多个视图列定义唯一聚集索引。视图将具体化，并且结果集存储在该索引的页级别中，其存储方式与表数据存储在聚集索引中的方式相同。
   3.2 非聚集索引
   非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。
   从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中。对于堆，行的定位器是指向行的指针。。对于聚集表，行定位器是聚集索引键。
   用户可以向非聚集索引的叶级添加非键列以跳过现有的索引键限制（900字节和16键列），并执行完整范围内的索引查询。

4. 索引的操作
   4.1 索引的创建
   （1）使用企业管理器创建索引
   （2）使用Transact-SQL语句创建索引
   （3）建立原则：
   ①只有表的所有者可以在同一个表中创建索引。
   ②每个表中只能创建一个聚集索引。
   ③每个表中最多可以创建249个非聚集索引。
   ④在经常查询的字段上建立索引。
   ⑤定义text、image和bit数据类型的列上不要建立索引。
   ⑥在外键列上可以建立索引。
   ⑦在主键列上一定要建立索引。
   ⑧在那些重复值比较多、查询较少的列上不要建立索引。
   4.2 查看索引信息
   （1）使用企业管理器查看索引
   （2）使用系统存储过程查看索引
   Use db_2008
   EXEC Sp_helpindex Student
   （3）利用系统表查看索引信息
   4.3 索引的修改
   （1）使用企业管理器修改索引
   （2）使用T-SQL语句更改索引
   Use db_2008
   EXEC sp_rename ‘Student.IX_Stu_Sno’,’IX_Stu_Sno1’
   4.4 索引的删除
   （1）使用企业管理器删除索引
   （2）使用T-SQL语句删除索引
   USE db_2008
   –判断表中是否有要删除的索引
   If EXISTS(Select *from sysindexes where name=’IX_Employee_ID’)
   DROP Index Student.IX_Employee_ID
   4.5 设置索引的选项
   ①PAD_INDEX选项：设置创建索引期间中间级别页中可用空间的百分比。PAD_INDEX选项只有在指定了FILLFACTOR选项时才能有用，因为PAD_INDEX是由FILLFACTOR所指定的百分比决定。
   ②FILLFACTOR（填充因子）：每个索引页的页级别中可用空间百分比。指定索引页上所流出的额外间隙和保留一定的百分比空间。FILLFACTOR取值范围是1~100，表示用户创建索引时数据容量所占页容量的百分比。
   ③ASC/DESC选项：升序/降序排列选择。
   ④SORT_IN_TEMPDB选项：确定对创建索引期间生成的中间排序结果进行排序的位置。如果为ON，则排序结果存储在tempdb中；如果为OFF，则排序结果存储在存储结果索引的文件组或分区方案中。
   ⑤STATISTICS_NORECOMPUTE选项：是否自动计算过期的索引统计信息。
   ⑥UNIQUE选项：是否允许并发用户在索引操作期间访问基础表或聚集索引数据以及任何关联非聚集索引。
   ⑦DROP_EXISTING选项：删除和重新创建现有索引。

5. 索引的分析与维护
   5.1 索引的分析
   （1）使用SHOWPLAN语句
   显示查询语句的执行信息，包含查询过程中连接表时所采取的每个步骤以及选择哪个索引。
   Eg：Use db_2008
   GO
   SET SHOWPLAN_ALL ON
   GO
   SELECT Name,Sex,Age FROM Employee where Sex =‘男’ AND Age >10
   GO
   SET SHOWPLAN_ALL OFF
   GO
   （2）使用STATISTICS IO语句
   使SQL Server显示有关有T-SQL语句生成的磁盘活动量的信息。
   5.2 索引的维护
   （1）使用DBCC SHOWCONTIG语句
   显示指定表的数据和索引的碎片信息。
   （2）使用DBCC DBREINDEX语句
   对指定数据库中的表重新生成一个或多个索引。
   （3）使用DBCC INDEXDEFRAG语句
   指定表或视图的索引碎片整理。

6. 全文索引
   6.1 使用企业管理器启用全文索引
   6.2 使用T-SQL语句启用全文索引
   （1）指定数据库启用全文索引
   （2）指定表启用全文索引
   6.3 使用T-SQL语句删除全文索引
   6.4 全文目录
   （1）全文目录的创建、删除和重创建
   （2）向全文目录中增加、删除列
   （3）激活全文目录
   6.5 全文目录的维护
   （1）企业管理器来维护全文目录
   选项 描述
   删除全文索引 将选定的表从全文目录中删除
   启动完全填充 使表中全部行对全文目录进行初始数据填充
   启动增量填充 识别最后一次填充发生的数据变化，并利用最后一次添加、删除或修改的行对全文索引进行填充
   停止填充 种植当前正在运行的全文索引填充任务
   手动跟踪更改 手动使应用程序可以仅获取所作的更改加有关信息
   自动跟踪更改 自动。。。
   禁止跟踪更改 禁止。。。
   应用跟踪更改 应用程序获取对用户表所做的更改及更改有关信息
   （2）使用T-SQL语句维护全文目录

7. 数据完整性
   数据完整性是SQL Server用于保证数据库中数据一致性的一种机制，防止非法数据存入数据库，表现：
   ①数据类型准确无误。
   ②数据取值符合规定范围。
   ③多个数据表之间的数据不存在冲突。

7.1 域完整性
域=数据表中的列（字段），要求数据表中指定列的数据具有正确的数据类型，格式和有效的数据范围。
7.2 实体完整性
所有的记录都应该有一个唯一的标识，确保数据表中的数据唯一性。表现在：
①唯一索引。
②主键。
③唯一码。
④标识列。
7.3 引用完整性
又称参照完整性，用来维护相关数据表中数据一致的手段。当一个数据表中的记录改变后，有参照关系的数据表中的相关记录也会发生变化，以确保数据的一致性。方法：
①外键（Foregin Key）。
②检测（Check）。
③触发器（Trigger）。
④存储过程（Stored Procedure）。
7.4 用户定义完整性
使用户可以定义不属于其他任何完整性类别的特定业务规则。

十六、 游标的使用

1. 游标的概述
   由SELECT语句返回的行集包括满足该语句的WHERE子句中条件的所有行，这种由语句返回的完整行集称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行，游标就是提供这种机制的对结果集的一种扩展。
   1.1 游标通过以下方式来扩展结果处理
   允许特定在结果集的特定行。
   从结果集的当前位置检索一行或一部分行。
   支持对结果集中当前位置的行进行数据修改。
   为其他用户对显示在结果集中对的数据库数据所做的更改提供不同级别的可见性支持。
   提供脚本、存储过程和触发器中用于访问结果集中的数据的T-SQL语句。
   1.2 游标的实现
   ①声明游标
   ②打开游标
   ③从一个游标中查找信息
   ④关闭游标
   ⑤释放游标
   1.3 游标的类型
   ①静态游标
   静态游标的完整结果集在游标打开时建立在tempdb中。静态游标总是按照游标打开时的原样显示结果集。静态游标在滚动期间很少或根本检测不到变化，虽然他在tempdb中存储了整个游标，但消耗的资源很少。
   ②动态游标
   动态游标与静态游标相对。当滚动游标时，动态游标反映结果集中所作的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会改变。所有用户做的全部UPDATE、INSERT、DELETE语句均通过游标可见。尽管动态游标使用tempdb的程度最低，在滚动期间它能够检测到所有变化，但消耗的资源也更多。
   ③只进游标
   只进游标不支持滚动，它只支持游标从头到尾顺序提取。只在数据库中提取出来后才能进行检索。对所有由当前用户发出或由其他用户提交，并影响结果集中的行的INSERT、UPDATE、和DELETE语句，其效果在这些行从游标中提取时是可见的。
   ④键集驱动游标
   打开游标时，键集驱动游标中的成员和行顺序是固定的。键集驱动游标由一套被称为键集的唯一标识符（键）控制。键由以唯一方式在结果集中标识行的列构成。

2. 游标的基本操作
   1.1 声明游标（DECLARE CURSOR）
   ①ISO标准语法
   ②T-SQL扩展的语法
   1.2 打开游标
   OPEN{{[GLOBAL]cursor_name}|cursor_variable_name}
   GLOBAL:指定cursor_name为全局游标。
   cursor_name：已声明的游标名称，如果全局游标和局部游标都是用cursor_name作为其名称，那么如果指定了GLOBAL，cursor_name值得是全局游标，否则仍为局部游标。
   1.3读取游标中的数据（FETCH）
   1.4关闭游标（CLOSE）
   1.5释放游标（DEALLOCATE）

3. 使用系统过程查看游标
   1.1sp_cursor_list
   sp_cursor_list报告当前为连接打开的服务器游标的属性。
   1.2sp_describe_cursor
   sp_describe_cursor用于报告服务器游标的属性。

十七、 SQL函数的使用

1. 聚合函数
   对一组值执行计算，并返回单个值。除了COUNT以外，聚合函数都会忽略空值。聚合函数经常与SELECT语句的GROUP BY子句一起使用，并且为每一组产生一个单一值。
   DISTINCT：返回一个集合，并从指定集合中删除重复的元组。

2. 数学函数
   ABS（绝对值）函数
   PI（圆周率）函数
   POWER（乘方）函数
   RAND（随机浮点数）函数
   ROUND（四舍五入）函数
   SQUARE（平方）函数
   SQRT（平方根）函数
   三角函数（cos函数、cot函数、sin函数、tan函数）

3. 字符串函数
   函数名称 说明
   ASCII 返回字符表达式最左端字符的ASCII
   CHARINDEX 返回字符串中指定表达式的起始位置
   LEFT 从左边开始，取得字符串左边指定个数的字符
   LEN 返回指定字符串的字符（不是字节）个数
   REPLACE 将指定的字符串替换为另一指定的字符串
   REVERSE 返回字符表达式的反转
   RIGHT 从右边开始，取得字符串右边指定个数的字符
   STR 返回由数字数据转换来的字符数据
   SUBSTRING 返回指定个数的字符

4. 日期和时间函数
   函数名称 说明
   DATEADD 在向指定日期加上一段时间后返回datetime值
   DATEDIFF 返回跨两个指定日期的日期和时间边界数
   GETDATE 返回当前系统日期和时间
   DAY 返回指定日期中的天
   MONTH 返回指定日期中的月份
   YEAR 返回指定日期中的年份

5. 转换函数
   CAST和CONVERT函数，不仅可以将指定的数据类型转换为另一种数据类型，还可用来获得各种特殊的数据格式。
   隐性转换：SQL Server自动处理某些数据类型的转换。
   显性转换：利用CAST和CONVERT转换数据类型。
   1.1CAST函数
   CAST（expression AS date_type）
   1.2CONVERT函数
   CONVERT（data_type[(length)],expression[,style]）

6. 元数据函数
   返回与数据库相关的信息。其中包括：
   返回数据库中数据表或视图的个数或名称。
   返回数据表中数据字段的名称、数据类型、长度等描述信息。
   返回数据表中定义的约束、索引、主键或外键等信息。
   函数名称 说明
   COL_LENGTH 返回列的定义长度（以字节为单位）
   COL_NAME 返回数据库列的名称，该列具有表标识号和列标识号
   DB_NAME 返回数据库名
   OBJECT_ID 返回数据库对象标识号

十八、 SQL中的事务

1. 事务的概念
   事务是一系列语句构成的逻辑工作单元。与存储过程相似，为了完成一定业务逻辑而将一条或多条语句‘封装’起来，形成一个相对独立的工作单元。
   事务包含4种重要的属性：
   原子性（Atomic）：事务是一个整体的工作单元，如果某条语句执行失败，则所有语句全部回滚。
   一致性（ConDemoltent）：事务在完成时，必须使所有数据都保持一致状态。
   隔离性（Isolated）：由某一事物所作的修改必须与其他事务所作的修改隔离。
   持久性（Durability）：当事务提交后，对数据库的修改永久保留。

2. 显式事务与隐式事务
   2.1 显式事务
   用户自定义或用户指定的事务。
   ①BEGIN TRANSACTION语句：用于启动一个事务
   ②COMMIT TRANSACTION语句：标志一个成功的隐式事务或用户定义事务的结束。
   ③COMMIT WORK语句：标志事务结束，与②相同，按②接收用户定义的事务名称。
   ④ROLLBACK TRANSACTION语句：将显式事务或 隐式事务回滚到事务的起点或事务内的某个保存点。
   ⑤ROLLBACK WORK语句：将用户定义的事务回滚到事务的起点。
   2.2 隐式事务
   使用SET IMPLICIT_TRANSACTIONS ON语句将隐式事务模式设置为打开。此后执行下一条语句时自动启动一个新事务，并且每关闭一个事务时，执行下一条语句又会启动一个新事务，直到关闭了隐式事务的设置开关。

3. 使用事务
   3.1 自动提交事务
   是SQL Server默认的数据处理方式，当任何一条有效的SQL语句被执行后，它对数据库所做的修改都将会被自动提交，如果发生错误，则会自动回滚并返回错误信息。
   3.2 事务的并发问题
   ①丢失更新
   当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其他事务的存在。最后的更新将重写由其他事务所作的更新，这样就导致数据丢失。
   ②未确认的相关性（脏读）
   如果一个事务读取了另一个事务尚未提交的更新，则称脏读。
   ③不一致的分析（不可重复读）
   当事务多次访问同一行数据，并且每次读取的数据不同时，将会发生不一致分析问题。
   ④幻想读
   当一个事务的更新结果影响到另一个事务时，将会发生幻想读问题。
   3.3 事务的隔离级别
   SET TRANSACTION ISOLATION LEVEL语句设置事务的隔离级别。
   隔离级别 脏读 不可重复读 幻象读 级别
   Read Uncommitted（未提交读） 是 是 是 最低
   Read Commited（提交读） 否 是 是 默认
   Repeatable Read（可重复读） 否 否 是 增加
   Serializable（可串行读） 否 否 否 最高

4. 锁
   4.1 SQL Server锁机制
   锁是一种机制，用于防止一个过程在对象上进行操作时，同某些已经在该对象上完成的事情发生冲突。锁可以防止事务的并发问题。
   4.2 锁模式
   锁模式 描述
   共享（S） 用于不更改或不更新数据的操作（只读操作），如SELECT语句
   更新（U） 用于可更新资源中，防止多个会话在读取、锁定以及随后可能进行的更新中发生死锁
   排他（X） 用于数据修改操作（INSERT、UPDATE或DELETE），确保不会同时出现同一资源多重更新
   意向 用于建立锁的层次结构，意向锁类型为意向共享（IS）、意向排他（IX）以及与意向排他共享（SIX）
   架构 在执行依赖于表架构的操作时使用。架构锁的类型为架构修改（Sch-M）和架构稳定性（Sch-S）
   大容量更新（BU） 向表中大容量辅助数据并指定了TABLOCK提示时使用
   4.3 锁的粒度
   为了优化数据的并发性，可以使用SQL Server中锁的粒度，它可以锁定不同类型的资源。
   锁类型 描述
   行锁（RID） 行标识符。用于单独锁定表中的一行，对小的锁
   键锁 锁定索引节点。用于保护可串行事务中的键范围
   页锁 锁定8KB的数据页或索引页
   扩展盘区锁 锁定相邻的8个数据页或索引页
   表锁 锁定整个表
   数据库锁 锁定整个数据库
   4.4 查看锁
   使用sys.dm_tran_locks动态管理视图。
   4.5 死锁
   当两个或多个线程之间有循环相关性是，将会产生死锁。死锁是一种可能发生在任何多线程系统中的状态，而不仅发生在关系数据库管理系统中。多线程系统中的一个线程可能获取一个或多个资源（如锁）
   在数据库中，如果多个进程分别锁定了一个资源，并又要访问被锁定的资源，则此时就会产生死锁，同时导致多个进程都处于等待状态。在事务提交或回滚之前两个线程都不能释放资源，而且它们因为正等待对方拥有的资源而不能提交或回滚事务。

5. 分布式事务处理
   涉及一个数据库以上的事务被称为分布式事务。分布式事务跨越两个或多个称为资源管理器的服务器。
   分布式事务处理协调器（DTC）系统服务负责协调跨计算机和资源管理器分布的事务，如数据库、消息队列、文件系统和其他事务保护资源管理器。

十九、 数据库文件SQL语句分析

1. SET ANSI_NULLS ONn GOn SET QUOTED_IDENTIFIER ONn GOn SET ANSI_PADDING ON是什么意思？
   这些是 SQL-92 设置语句，使 SQL Server 2000/2005 遵从 SQL-92 规则。
   当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。当 SET QUOTED_IDENTIFIER 为 OFF 时，标识符不可加引号，且必须符合所有 Transact-SQL 标识符规则。
   SQL-92 标准要求在对空值进行等于 (=) 或不等于 (<>) 比较时取值为 FALSE。当 SET ANSI_NULLS 为 ON 时，无论 column_name 中是否包含空值，使用 WHERE column_name = NULL 或 WHERE column_name <> NULL 的 SELECT 语句仍返回零行。
   当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。
   当SET ANSI_PADDING设置为 ON 时，不剪裁字符值中插入到 varchar 列的尾随空格和二进制值中插入到 varbinary 列的尾随零。将值按列的长度进行填充。将允许空值的 Char(n) 和 binary(n) 列填充到列长。当设置为 OFF 时，剪裁 varchar 列的尾随空格和 varbinary 列的尾随零。将剪裁尾随空格和零。始终将不允许空值的 Char(n) 和 binary(n) 列填充到列长。该设置只影响新列的定义。

2. SQL 中GO关键字的作用
   每个被GO分隔的语句都是一个单独的事务，一个语句执行失败不会影响其它语句执行。

3. ON [PRIMARY]的含义
   是表示表是建立在主文件组上。PRIMARY表示主文件组。如果没有指定默认文件组，则主文件组是默认文件组，ON [PRIMARY]也可以省略掉了。

CONSTRAINT […] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
PRIMARY KEY表明创建的是主键约束，CLUSTERED表示索引类型是聚集索引，IGNORE_DUP_KEY = OFF的作用是在向表中插入数据的时候，如果遇到表中已经存在ID的值，insert语句就会失败，并且回滚整个insert语句。ALLOW_ROW_LOCKS：允许行锁ALLOW_PAGE_LOCKS：允许页锁。

最后

以上就是天真犀牛为你收集整理的MS SQL Server数据库原理的全部内容，希望文章能够帮你解决MS SQL Server数据库原理所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。