SQLServer开发指南.docx
《SQLServer开发指南.docx》由会员分享，可在线阅读，更多相关《SQLServer开发指南.docx（77页珍藏版）》请在冰点文库上搜索。
SQLServer开发指南
SQLServer开发指南
SQLServer数据库设计
一、数据库设计的必要性
二、什么是数据库设计
三、数据库设计的重要
四、数据模型
实体-关系（E-R）数据模型
实体（Entity）
属性（Attribute）
关系（Relationship）
五、数据库设计步骤
1、需求分析阶段
2、概要设计阶段
3、详细设计阶段
六、数据库设计规范化
数据库设计中经常出现的问题
规范设计
规范化和性能关系
SQLServer数据库基础编程
Go批处理语句
使用、切换数据库
创建、删除数据库
基本数据类型
精确数字类型
近似数字类型
日期时间类型
字符串类型
Unicode字符串类型
二进制字符串类型
判断表或其他对象及列是否存在
创建、删除表
给表添加字段、修改字段、删除字段
添加、删除约束
插入数据
查询、修改、删除数据
备份数据、表
利用存储过程查询表信息
SQLServerTransact-SQL编程
变量
1、局部变量（LocalVariable）
2、全局变量（GlobalVariable
输出语句
逻辑控制语句
1、if-else判断语句
2、while…continue…break循环语句
3、case
4、其他语句
SQLServerT-SQL高级查询
基本常用查询
嵌套子查询
#from（select…table）示例
#in,notin子句查询示例
#exists和notexists子句查询示例
#some、any、all子句查询示例
聚合查询
1、distinct去掉重复数据
2、compute和computeby汇总查询
3、cube汇总
排序函数
#row_number函数
#rank函数函数
#dense_rank函数
#partitionby分组子句
#ntile平均排序函数
集合运算
1、union和unionall进行并集运算
2、intersect进行交集运算
3、except进行减集运算
公式表表达式
连接查询
1、简化连接查询
2、leftjoin左连接
3、rightjoin右连接
4、innerjoin内连接
5、crossjoin交叉连接
6、自连接（同一张表进行连接查询）
函数
1、聚合函数
2、日期时间函数
3、数学函数
4、元数据
5、字符串函数
6、安全函数
7、系统函数
8、配置函数
9、系统统计函数
10、用户自定义函数
SQLServer索引和视图
索引
1、什么是索引
2、索引分类
3、创建索引
4、适合的创建索引的列
5、不适合创建索引的列
视图
1、什么是视图
2、创建视图准则
3、创建视图
4、修改视图
5、加密视图
SQLServer存储过程
存储过程的概念
1、存储过程的优点
A、存储过程允许标准组件式编程
B、存储过程能够实现较快的执行速度
C、存储过程减轻网络流量
D、存储过程可被作为一种安全机制来充分利用
系统存储过程
用户自定义存储过程
1、创建语法
2、创建不带参数存储过程
3、修改存储过程
4、带参存储过程
5、带通配符参数存储过程
6、带输出参数存储过程
7、不缓存存储过程
8、加密存储过程
9、带游标参数存储过程
10、分页存储过程
Raiserror
SQLServer事务、异常和游标
事务
1、事务的特点
原子性
一致性
隔离性
持久性
2、事务的模式
a、显示事务
b、自动提交事务
c、隐式事务
3、事务处理
a、begintransaction语句
b、committransaction语句
c、rollbacktransaction语句
4、事务的示例
异常
#示例：
用异常处理错误信息
#示例：
异常能处理的错误信息
#示例：
无法提交的事务
#示例：
处理异常日志信息
游标
1、游标实际功能
2、游标的基本操作
A、定义游标
B、打开游标
C、检索游标
D、关闭游标
E、删除游标
3、游标操作示例
SQLServer触发器
什么是触发器
DML触发器分为：
1、after触发器（之后触发）
a、insert触发器
b、update触发器
c、delete触发器
2、insteadof触发器（之前触发）
创建触发器
#创建insert类型触发器
#创建delete类型触发器
#创建update类型触发器
#update更新列级触发器
#insteadof类型触发器
#创建insteadof触发器
#显示自定义消息raiserror
#修改触发器
#启用、禁用触发器
#查询创建的触发器信息
#示例，验证插入数据
#示例，操作日志
SQLServer数据库安全
SQLServer数据库设计
一、数据库设计的必要性
在实际的软件项目中，如果系统中需要存储的数据量比较大，需要设计的表比较多，表与表之间的关系比较复杂，那我们就需要进行规范的数据库设置。
如果不经过数据库的设计，我们构建的数据库不合理、不恰当，那么数据库的维护、运行效率会有很大的问题。
这将直接影响到项目的运行性和可靠性。
二、什么是数据库设计
数据库设计实际上就是规划和结构化数据库中的数据对象以及这些数据对象之间的关系过程。
三、数据库设计的重要性
Ø不经过设计的数据库或是设计糟糕的数据库很可能导致
1、数据库运行效率地下
2、更新、删除、添加数据出现问题
Ø良好设计的数据库
1、执行效率高
2、使应用程序更便于开发
3、扩展性好
4、维护性好
四、数据模型
数据模型就像是数据间联系的一个轮廓图，整个模型就像一个框架。
如果按照记录间联系的表示方式，对数据模型进行分类，可以分为：
层次模型、网状模型、关系模型。
前两种又称为格式化数据模型。
数据模型的好坏直接影响到数据库的性能，所以数据模型的选择是数据库设计的首要任务。
Ø实体-关系（E-R）数据模型
E-R数据模型（Entity-Relationshipdatamodel），即实体-关系数据模型。
E-R数据模型不同于传统的关系数据模型，它不是面向实现，而是面向现实物体的。
Ø实体（Entity）
数据是用来描述现实中的物体的，而描述的对象都是形形色色的，有具体的、也有抽象的；有物理上存在的、也有概念性的。
凡是可以互相区别而且可以被人们认识的事、物、概念等统统抽象为实体。
多个相同的类型的实体可以称为实体集（Entityset）。
因此，在E-R数据模型中，也有型与值之分；实体可以作为型来定义，每个实体可以是它的实例和值。
Ø属性（Attribute）
实体一般具体若干特征，这些特征称为实体的属性。
而每个属性都有自己的取值范围，在E-R数据模型中称为值集（valueset）。
在同一实体集中，每个实体的属性及其值集都是相同的，但可能取不同的值。
属性对应数据库表的列。
Ø关系（Relationship）
实体之间会有各种关系，这些关系抽象为联系。
不但实体可以有属性，关系也可以有属性。
五、数据库设计步骤
Ø数据库设计可以分为以下几个阶段
1、需求分析阶段：
分析客户的业务需求，特别是数据方面的需求
2、概要设计阶段：
绘制数据库的E-R图，并确认需求文档的正确性和完整性，E-R图是项目的设计人员、开发人员、测试人员，以及和客户进行沟通的重要凭据
3、详细设计阶段：
将概要设计阶段的E-R图转换为数据库表，进行逻辑设计，确定各个表之间的主外键关系，运用数据库的三范式进行审核，并进行技术评审。
最后决定选哪种数据库（Oracle、SQLServer、MySQL）来建库、建表。
Ø需求分析阶段：
数据库系统分析
秀气分析阶段的重点是调查、收集、分析客户的业务数据需求以及数据的安全性、完整性需求等。
需求分析步骤：
1、确认业务需求
2、标识关系实体
3、标识每个实体的具有的属性
4、确认实体之间的关系
Ø概要设计阶段：
绘制E-R图
作为数据库设计者，你需要和项目组内其他成员分享你的设计思路，共同研讨数据库设计的合理性、安全性、完整性，并确认是否符合客户的业务需求。
那么使用E-R图，这种图形化的表示方式最为直观。
*E-R图中的实体、属性和关系
上面的简单E-R图可以看出用户和收支之间的关系。
在上图中可以看出：
用矩形表示实体，实体是一般名词；椭圆表示属性，一般也是名词；菱形表示关系，一般是动词。
*映射基数
映射基数表示可以通过关系与该实体的个数。
对于实体集A和B之间的二元关系，可能的映射基数有：
1、一对一：
也就是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、直线箭头表示实体集之间映射基数
Ø详细设计阶段：
将E-R图转换为表
步骤如下：
1、将各个实体转换为对应的表，将各属性转换为对应的列
2、标识每张表的主键
3、将实体之间的关系转换为表与表之间的主外键关系
六、数据库设计规范化
Ø数据库设计中经常出现的问题
1、数据冗余大
2、插入数据异常
3、删除异常
4、更新异常
Ø规范设计
一个较好的关系数据库模型，它的每个关系中的属性一定要满足某种内在的语义条件，即要按一定的规范设计关系模型，这就是设计的规范化。
在数据库设计时，有一些专门的规则，称为数据库的设计范式，遵循这些规则，就可以创建出良好的数据库，数据库著名的三大范式理论：
1、第一范式（1NF）
第一范式是满足关系数据库模型所要遵循的最基本的条件范式，几关系中的每个属性必须是不可再分的简单项，不能是属性组合，即属性的取值是不可拆分的原子值。
2、第二范式（2NF）
第二范式是在第一范式的基础上，确保表中的每列都和主键相关。
其定义是如果一个关系满足1NF，并且除了主键关系外的其他列都依赖于该主键，则满足第二范式。
3、第三范式（3NF）
第三范式是在第二范式的基础上进行的，第三范式的目标是确保每列都和主键列直接相关，而不是间接相关的。
其定义是：
如果一个关系满足2NF，并且除主键外的其他列都不传递依赖于该主键。
Ø规范化和性能关系
为了满足三大范式，数据库的性能可能会有一定程度的降低。
所以，在实际数据库设计中，我们既要尽量满足三大范式，从而避免数据冗余和各种数据库的操作异常，同时也要考虑数据的访问性能。
有时候，为了提高数据库的访问效率，适当的允许少量数据冗余咧存在，才是最适合的数据库设计方案。
SQLServer数据库基础编程
ØGo批处理语句
用于同时执行多个语句
Ø使用、切换数据库
usemaster
go
Ø创建、删除数据库
方法1、
--判断是否存在该数据库，存在就删除
if（exists（select*fromsys.databaseswherename='testHome'））
dropdatabasetestHome
go
--创建数据库，设置数据库文件、日志文件保存目录
createdatabasetestHome
on（
name='testHome',
filename='c:
\data\students.mdf'
）
logon（
name='testHome_log',
filename='c:
\data\testHome_log.ldf'
）
go
方法2（设置文件大小）、
if（exists（select*fromsys.databaseswherename='testHome'））
dropdatabasetestHome
go
createdatabasetestHome
--默认就属于primary主文件组，可省略
onprimary（
--数据文件的具体描述
name='testHome_data',--主数据文件的逻辑名
fileName='c:
\testHome_data.mdf',--主数据文件的物理名
size=3MB,--主数据文件的初始大小
maxSize=50MB,--主数据文件增长的最大值
fileGrowth=10%--主数据文件的增长率
）
--日志文件的具体描述，各参数含义同上
logon（
name='testHome_log',
fileName='c:
\testHome_log.ldf',
size=1MB,
fileGrowth=1MB
）
go
方法3（设置次数据文件）、
if（exists（select*fromsys.databaseswherename='testHome'））
dropdatabasetestHome
go
createdatabasetestHome
--默认就属于primary主文件组，可省略
onprimary（
--数据文件的具体描述
name='testHome_data',--主数据文件的逻辑名
fileName='c:
\testHome_data.mdf',--主数据文件的物理名
size=3MB,--主数据文件的初始大小
maxSize=50MB,--主数据文件增长的最大值
fileGrowth=10%--主数据文件的增长率
）,
--次数据文件的具体描述
（
--数据文件的具体描述
name='testHome2_data',--主数据文件的逻辑名
fileName='c:
\testHome2_data.mdf',--主数据文件的物理名
size=2MB,--主数据文件的初始大小
maxSize=50MB,--主数据文件增长的最大值
fileGrowth=10%--主数据文件的增长率
）
--日志文件的具体描述，各参数含义同上
logon（
name='testHome_log',
fileName='c:
\testHome_log.ldf',
size=1MB,
fileGrowth=1MB
）,
（
name='testHome2_log',
fileName='c:
\testHome2_log.ldf',
size=1MB,
fileGrowth=1MB
）
go
Ø基本数据类型
精确数字类型
类型
描述
bigint
bigint数据类型用于整数值可能超过int数据类型支持范围的情况，范围：
-2^63到2^63-1，存储空间8字节
int
整数数据类型，范围在-2^31到2^31-1，存储空间4字节
smallint
整数，范围在-2^15到2^15-1，存储空间2字节
tinyint
范围在0到255，存储空间1字节
bit
可以取值为1、0或NULL的整数数据类型，每8个bit占一个字节，16bit就2个字节，24bit就3个字节
decimal
带固定精度和小数位数的数值数据类型，有效值从-10^38+1到10^38-1
numeric
同上
money
货币或货币值的数据类型，范围在-922,337,203,685,477.5808到922,337,203,685,477.5807
smallmoney
货币类型，-214,748.3648到214,748.3647
近似数字类型
类型
描述
float
表示浮点数值数据的大致数值数据类型。
浮点数据为近似值；范围-1.79E+308至-2.23E-308、0以及2.23E-308至1.79E+308
real
real的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
最大长度为2^31-1
varchar
固定长度或可变长度的字符数据类型
升级会员 