SQLServer开发指南.docx

1、SQLServer开发指南SQL Server 开发指南SQL Server 数据库设计 一、数据库设计的必要性 二、什么是数据库设计 三、数据库设计的重要 四、数据模型 实体-关系（E-R）数据模型 实体（Entity） 属性（Attribute） 关系（Relationship） 五、数据库设计步骤 1、 需求分析阶段 2、 概要设计阶段 3、 详细设计阶段 六、数据库设计规范化 数据库设计中经常出现的问题 规范设计 规范化和性能关系SQL Server 数据库基础编程 Go批处理语句 使用、切换数据库 创建、删除数据库 基本数据类型 精确数字类型 近似数字类型 日期时间类型 字符串类型

2、Unicode字符串类型 二进制字符串类型 判断表或其他对象及列是否存在 创建、删除表 给表添加字段、修改字段、删除字段 添加、删除约束 插入数据 查询、修改、删除数据 备份数据、表 利用存储过程查询表信息SQL Server Transact-SQL 编程 变量 1、 局部变量（Local Variable） 2、 全局变量（Global Variable 输出语句 逻辑控制语句 1、 if-else判断语句 2、 whilecontinuebreak循环语句 3、 case 4、 其他语句SQL Server T-SQL高级查询 基本常用查询 嵌套子查询 # from (select ta

3、ble)示例 # in, not in子句查询示例 # exists和not exists子句查询示例 # some、any、all子句查询示例 聚合查询 1、 distinct去掉重复数据 2、 compute和compute by汇总查询 3、 cube汇总 排序函数 # row_number函数 # rank函数函数 # dense_rank函数 # partition by分组子句 # ntile平均排序函数 集合运算 1、 union和union all进行并集运算 2、 intersect进行交集运算 3、 except进行减集运算 公式表表达式 连接查询 1、 简化连接查询 2、

4、 left join左连接 3、 right join右连接 4、 inner join内连接 5、 cross join交叉连接 6、 自连接（同一张表进行连接查询） 函数 1、 聚合函数 2、 日期时间函数 3、 数学函数 4、 元数据 5、 字符串函数 6、 安全函数 7、 系统函数 8、 配置函数 9、 系统统计函数 10、 用户自定义函数SQL Server 索引和视图 索引 1、 什么是索引 2、 索引分类 3、 创建索引 4、 适合的创建索引的列 5、 不适合创建索引的列 视图 1、 什么是视图 2、 创建视图准则 3、 创建视图 4、 修改视图 5、 加密视图SQL Serve

5、r 存储过程 存储过程的概念 1、 存储过程的优点 A、 存储过程允许标准组件式编程 B、 存储过程能够实现较快的执行速度 C、 存储过程减轻网络流量 D、 存储过程可被作为一种安全机制来充分利用 系统存储过程 用户自定义存储过程 1、 创建语法 2、 创建不带参数存储过程 3、 修改存储过程 4、 带参存储过程 5、 带通配符参数存储过程 6、 带输出参数存储过程 7、 不缓存存储过程 8、 加密存储过程 9、 带游标参数存储过程 10、 分页存储过程 RaiserrorSQL Server 事务、异常和游标 事务 1、 事务的特点 原子性 一致性 隔离性 持久性 2、 事务的模式 a、 显

6、示事务 b、 自动提交事务 c、 隐式事务 3、 事务处理 a、 begin transaction语句 b、 commit transaction语句 c、 rollback transaction语句 4、 事务的示例 异常 # 示例：用异常处理错误信息 # 示例：异常能处理的错误信息 # 示例：无法提交的事务 # 示例：处理异常日志信息 游标 1、游标实际功能 2、游标的基本操作 A、 定义游标 B、 打开游标 C、 检索游标 D、 关闭游标 E、 删除游标 3、游标操作示例SQL Server 触发器 什么是触发器 DML触发器分为： 1、 after触发器（之后触发） a、 inse

7、rt触发器 b、 update触发器 c、 delete触发器 2、 instead of 触发器 （之前触发） 创建触发器 # 创建insert类型触发器 # 创建delete类型触发器 # 创建update类型触发器 # update更新列级触发器 # instead of类型触发器 # 创建instead of触发器 # 显示自定义消息raiserror # 修改触发器 # 启用、禁用触发器 # 查询创建的触发器信息 # 示例，验证插入数据 # 示例，操作日志SQL Server 数据库安全SQL Server 数据库设计一、数据库设计的必要性 在实际的软件项目中，如果系统中需要存储的数

8、据量比较大，需要设计的表比较多，表与表之间的关系比较复杂，那我们就需要进行规范的数据库设置。如果不经过数据库的设计，我们构建的数据库不合理、不恰当，那么数据库的维护、运行效率会有很大的问题。这将直接影响到项目的运行性和可靠性。二、什么是数据库设计 数据库设计实际上就是规划和结构化数据库中的数据对象以及这些数据对象之间的关系过程。三、数据库设计的重要性 不经过设计的数据库或是设计糟糕的数据库很可能导致 1、 数据库运行效率地下 2、 更新、删除、添加数据出现问题 良好设计的数据库 1、 执行效率高 2、 使应用程序更便于开发 3、 扩展性好 4、 维护性好四、数据模型 数据模型就像是数据间联系的

9、一个轮廓图，整个模型就像一个框架。 如果按照记录间联系的表示方式，对数据模型进行分类，可以分为：层次模型、网状模型、关系模型。前两种又称为格式化数据模型。数据模型的好坏直接影响到数据库的性能，所以数据模型的选择是数据库设计的首要任务。 实体-关系（E-R）数据模型 E-R数据模型（Entity-Relationship data model），即实体-关系数据模型。E-R数据模型不同于传统的关系数据模型，它不是面向实现，而是面向现实物体的。 实体（Entity） 数据是用来描述现实中的物体的，而描述的对象都是形形色色的，有具体的、也有抽象的；有物理上存在的、也有概念性的。凡是可以互相区别而且可

10、以被人们认识的事、物、概念等统统抽象为实体。多个相同的类型的实体可以称为实体集（Entity set）。因此，在E-R数据模型中，也有型与值之分；实体可以作为型来定义，每个实体可以是它的实例和值。 属性（Attribute） 实体一般具体若干特征，这些特征称为实体的属性。而每个属性都有自己的取值范围，在E-R数据模型中称为值集（value set）。在同一实体集中，每个实体的属性及其值集都是相同的，但可能取不同的值。属性对应数据库表的列。 关系（Relationship） 实体之间会有各种关系，这些关系抽象为联系。不但实体可以有属性，关系也可以有属性。五、数据库设计步骤 数据库设计可以分为以下

11、几个阶段 1、 需求分析阶段：分析客户的业务需求，特别是数据方面的需求 2、 概要设计阶段：绘制数据库的E-R图，并确认需求文档的正确性和完整性，E-R图是项目的设计人员、开发人员、测试人员，以及和客户进行沟通的重要凭据 3、 详细设计阶段：将概要设计阶段的E-R图转换为数据库表，进行逻辑设计，确定各个表之间的主外键关系，运用数据库的三范式进行审核，并进行技术评审。最后决定选哪种数据库（Oracle、SQLServer、MySQL）来建库、建表。 需求分析阶段：数据库系统分析 秀气分析阶段的重点是调查、收集、分析客户的业务数据需求以及数据的安全性、完整性需求等。 需求分析步骤： 1、 确认业务

12、需求 2、 标识关系实体 3、 标识每个实体的具有的属性 4、 确认实体之间的关系 概要设计阶段：绘制E-R图 作为数据库设计者，你需要和项目组内其他成员分享你的设计思路，共同研讨数据库设计的合理性、安全性、完整性，并确认是否符合客户的业务需求。那么使用E-R图，这种图形化的表示方式最为直观。 * E-R图中的实体、属性和关系 上面的简单E-R图可以看出用户和收支之间的关系。在上图中可以看出：用矩形表示实体，实体是一般名词；椭圆表示属性，一般也是名词；菱形表示关系，一般是动词。 * 映射基数 映射基数表示可以通过关系与该实体的个数。对于实体集A和B之间的二元关系，可能的映射基数有： 1、 一对

13、一：也就是A实体中最多只有一个B实体的关联，而B实体的最多只有一个A实体的关联。用E-R图表示： 2、 一对多：A实体可以与B实体任意数量的进行关联，B中的实体最多与A中的一个实体关联。E-R图表示： 3、 多对一：A实体最多与一个B实体进行关联，而B实体可以和任意多个A实体进行关联。E-R图表示： 4、 多对多：A实体可以有多个B实体，而B实体也可以有任意多个A实体。E-R图表示： * E-R图 E-R图可以以图形化的方式将数据库的整个逻辑结构表示出来，组成部分有： 1、 矩形表示实体集 2、 椭圆表示属性 3、 菱形表示关系、 4、 直线用来连接实体集与属性、实体集和关系 5、 直线箭头表

14、示实体集之间映射基数 详细设计阶段：将E-R图转换为表 步骤如下： 1、 将各个实体转换为对应的表，将各属性转换为对应的列 2、 标识每张表的主键 3、 将实体之间的关系转换为表与表之间的主外键关系六、数据库设计规范化 数据库设计中经常出现的问题 1、 数据冗余大 2、 插入数据异常 3、 删除异常 4、 更新异常 规范设计 一个较好的关系数据库模型，它的每个关系中的属性一定要满足某种内在的语义条件，即要按一定的规范设计关系模型，这就是设计的规范化。 在数据库设计时，有一些专门的规则，称为数据库的设计范式，遵循这些规则，就可以创建出良好的数据库，数据库著名的三大范式理论： 1、 第一范式（1N

15、F） 第一范式是满足关系数据库模型所要遵循的最基本的条件范式，几关系中的每个属性必须是不可再分的简单项，不能是属性组合，即属性的取值是不可拆分的原子值。 2、 第二范式（2NF） 第二范式是在第一范式的基础上，确保表中的每列都和主键相关。其定义是如果一个关系满足1NF，并且除了主键关系外的其他列都依赖于该主键，则满足第二范式。 3、 第三范式(3NF) 第三范式是在第二范式的基础上进行的，第三范式的目标是确保每列都和主键列直接相关，而不是间接相关的。其定义是：如果一个关系满足2NF，并且除主键外的其他列都不传递依赖于该主键。 规范化和性能关系 为了满足三大范式，数据库的性能可能会有一定程度的降

16、低。所以，在实际数据库设计中，我们既要尽量满足三大范式，从而避免数据冗余和各种数据库的操作异常，同时也要考虑数据的访问性能。有时候，为了提高数据库的访问效率，适当的允许少量数据冗余咧存在，才是最适合的数据库设计方案。SQL Server 数据库基础编程 Go批处理语句 用于同时执行多个语句 使用、切换数据库use mastergo 创建、删除数据库方法1、-判断是否存在该数据库，存在就删除if (exists (select * from sys.databases where name = testHome) drop database testHomego-创建数据库，设置数据库文件、日志

17、文件保存目录create database testHomeon( name = testHome, filename = c:datastudents.mdf )log on( name = testHome_log, filename = c:datatestHome_log.ldf)go方法2（设置文件大小）、if (exists (select * from sys.databases where name = testHome) drop database testHomegocreate database testHome-默认就属于primary主文件组，可省略on primar

18、y ( -数据文件的具体描述 name = testHome_data, -主数据文件的逻辑名 fileName = c:testHome_data.mdf, -主数据文件的物理名 size = 3MB, -主数据文件的初始大小 maxSize = 50MB, -主数据文件增长的最大值 fileGrowth = 10% -主数据文件的增长率)-日志文件的具体描述，各参数含义同上log on ( name = testHome_log, fileName = c:testHome_log.ldf, size = 1MB, fileGrowth = 1MB)go方法3（设置次数据文件）、if (e

19、xists (select * from sys.databases where name = testHome) drop database testHomegocreate database testHome-默认就属于primary主文件组，可省略on primary ( -数据文件的具体描述 name = testHome_data, -主数据文件的逻辑名 fileName = c:testHome_data.mdf, -主数据文件的物理名 size = 3MB, -主数据文件的初始大小 maxSize = 50MB, -主数据文件增长的最大值 fileGrowth = 10% -主数

20、据文件的增长率),-次数据文件的具体描述( -数据文件的具体描述 name = testHome2_data, -主数据文件的逻辑名 fileName = c:testHome2_data.mdf, -主数据文件的物理名 size = 2MB, -主数据文件的初始大小 maxSize = 50MB, -主数据文件增长的最大值 fileGrowth = 10% -主数据文件的增长率)-日志文件的具体描述，各参数含义同上log on ( name = testHome_log, fileName = c:testHome_log.ldf, size = 1MB, fileGrowth = 1MB)

21、,( name = testHome2_log, fileName = c:testHome2_log.ldf, size = 1MB, fileGrowth = 1MB)go 基本数据类型精确数字类型类型描述bigintbigint 数据类型用于整数值可能超过 int 数据类型支持范围的情况，范围：-263 到 263-1，存储空间8字节int整数数据类型，范围在-231 到 231-1，存储空间4字节smallint整数，范围在-215 到 215-1，存储空间2字节tinyint范围在0 到 255，存储空间1字节bit可以取值为 1、0 或 NULL 的整数数据类型，每8个bit占一个

22、字节，16bit就2个字节，24bit就3个字节decimal带固定精度和小数位数的数值数据类型，有效值从 - 1038 +1 到 1038 - 1numeric同上money货币或货币值的数据类型，范围在-922,337,203,685,477.5808 到 922,337,203,685,477.5807smallmoney货币类型，-214,748.3648 到 214,748.3647近似数字类型类型描述float表示浮点数值数据的大致数值数据类型。浮点数据为近似值；范围-1.79E + 308 至 -2.23E - 308、0 以及 2.23E - 308 至 1.79E + 308realreal 的 SQL-92 同义词为 float(24)，范围在-3.40E + 38 至 -1.18E - 38、0 以及 1.18E - 38 至 3.40E + 38日期时间类型类型描述datetime表示某天的日期和时间的数据类型，范围在1753 年 1 月 1 日到 9999 年 12 月 31 日smalldatetime范围在1900 年 1 月 1 日到 2079 年 6 月 6 日字符串类型类型描述char固定长度或可变长度的字符数据类型，范围在范围为 1 至 8,000字节text最大长度为 231-1varchar固定长度或可变长度的字符数据类型