数据库课程设计规范.docx
《数据库课程设计规范.docx》由会员分享，可在线阅读，更多相关《数据库课程设计规范.docx（18页珍藏版）》请在冰豆网上搜索。
数据库课程设计规范
数据库开发规范
（SQLSERVER篇）
拟制：
日期：
审核：
日期：
批准：
日期：
修订历史记录
日期
版本
说明
作者
2013-3-13
V1.0
创建本文档
目录
目录3
第一章命名规范5
1.1命名标志法5
1.2数据库命名5
1.2.1数据库名要求全部使用Pascal命名法5
1.2.2数据库月份库、数据表日分库命名规则5
1.2.3分段数据库分库命名规则5
1.2.4分段分日期数据库分库命名规则5
1.3表的命名6
1.4字段命名6
1.5存储过程命名6
1.6触发器命名7
1.7索引命名7
1.7.1主键7
1.7.2外键7
1.8缺省值7
1.9视图7
1.10函数的命名7
1.11其他数据库对象命名规则7
1.12其他数据库可编程性对象命名8
第二章常用数据类型9
第三章数据库设计规范11
3.1三范式11
3.2适当的冗余11
3.3主键11
3.4索引11
3.5主键与聚集索引的关系12
第四章代码编写规范13
4.1注释13
4.2书写规范14
第五章查询设计性能优化相关14
5.1.存储过程14
5.1.1命名方面14
5.1.2使用方面14
5.2.索引14
5.2.1避免where子句列上的算术运算符14
5.2.2避免使用前置通配符15
5.2.3避免在索引列上使用notin15
5.2.4确保没有隐含的数据类型转换15
5.2.5关于提示15
5.3.查询16
5.4.其他16
5.5.事务和锁16
5.6.注意临时表和表变量的用法17
前言
良好的系统运行性能取决于硬件、网络、应用、数据库设计、SQL等多个方面，本文着重于探讨在SQL开发上怎样做取得尽可能好的性能（其它诸方面也对性能有决定性作用，因本文目标人员为SQL开发人员，所以着重于SQL）；通过规范SQL编写从而达到增强程序可读性、降低程序BUG概率、实现程序一致性、增强程序可维护性、提升程序性能的目标。
第一章命名规范
1.1命名标志法
使用下面的三种大写标识符约定。
Pascal大小写
将标识符的首字母和后面连接的每个单词的首字母都大写。
可以对三字符或更多字符的标识符使用Pascal大小写。
例如：
BackColor
Camel大小写
标识符的首字母小写，而每个后面连接的单词的首字母都大写。
例如：
backColor
大写
标识符中的所有字母都大写。
仅对于由两个或者更少字母组成的标识符使用该约定。
例如：
System.IO
System.Web.UI
可能还必须大写标识符以维持与现有非托管符号方案的兼容性，在该方案中所有大写字母经常用于枚举和常数值。
一般情况下，在使用它们的程序集之外这些字符应当是不可见的。
1.2数据库命名
1.2.1数据库名要求全部使用Pascal命名法
例如：
MFC
MFC53
DataController
1.2.2数据库月份库、数据表日分库命名规则
DatabaseName按数据库命名要求命名
TableName按数据表命名规则命名
Month,Day要求中间无任何连接符
例如
MFCLOG200301
MFC_log_ClientCheckin20030109
1.2.3分段数据库分库命名规则
DatabaseName按数据库命名要求命名
Segment是分段的编号，要求长度一致并且3位或者以上
例如NIDCHyper021
1.2.4分段分日期数据库分库命名规则
DatabaseName按数据库命名要求命名
Segment是分段的编号，要求长度一致并且3位或者以上
Day要求中间无任何连接符
例如
GatheredLog00120110227
MassLog00320110227
1.3表的命名
__
SystemName为表所属的系统名，此处要求采用Pascal命名法
TableType为数据表的类别，此处要求全部使用小写，在我们的库中有如下几种数据表类型：
tb----------数据表,
stat--------统计表,
dict--------字典表,
sys--------系统信息表,
re----------关系表,
log---------日志表
Name为数据库表的名称，此处要求使用Pascal命名法
例如：
MFCTbUnit场所信息表
MFCStatUnitDailyStatus场所状态日统计表
MFCReUserArea用户地区关系表
MFCLogCustomer顾客日志表
MFCDictSex性别字典表
1.4字段命名
字段命名统一使用Pascal标志法，单词中间不用下划线。
应尽量使用简短而又能说明字段实际意义的词组组合，为保证不与系统字段重复，应尽量至少使用两个单词。
同样含义的字段应尽量使用已有字段的物理名。
例如：
CertificateCode证件号
CertificateType证件类别
AlertClassName报警类别名
1.5存储过程命名
[]__
SystemName是系统名，此处要求使用Pascal命名法，对于跨系统使用的存储过程要求此段，其他非跨系统的存储过程不要求。
FunctionModule为功能模块名，此处要求使用Pascal标志法
TableName为数据库已有表名，命名规则同上面的表命名要求
FunctionName为存储过程的功能说明，此处要求使用Pascal标志法。
常用的功能有：
GetList取多条记录
GetModel去单条记录
GetListByCondition根据Condition条件取单条记录
Add插入或修改单条记录
Delete删除记录
Insert插入单条记录
BatchInsert批量插入多条记录
BatchUpdate批量更新多条记录
Update更新单条记录
例如：
Communication_MFC_re_UnitStatus_GetList
DataAnalysis_NIDC_tb_PersonGroup_Delete
DataAnalysis_MFC_tb_CrimeOnEsc_Add
1.6触发器命名
TR_[_]
如果只是针对单个操作类型的触发器，则要求说明操作类型：
例如：
TR_MFC_tb_Argot
TR_MFC_tb_Argot_Insert
1.7索引命名
IX__
例如：
IX_MFC_log_Customer_EndTime
1.7.1主键
PK_。
TableName同表命名规则
例如
PK_MFC_Log_Customer
1.7.2外键
FK__
例如：
FK_MFC_log_Customer_MFC_tb_Unit
1.8缺省值
DF__
例如：
DF_MFC_log_Customer_UserName
1.9视图
视图的命名用Pascal标志法，和表一致；
_view_
视图的命名除中间用’view’链接以外与表一致
例如：
MFC_view_Strategy
1.10函数的命名
采用存储过程同样的命名规则
1.11其他数据库对象命名规则
其他数据库对象，比如约定、队列、服务、路由等采用表名相同的命名法。
1.12其他数据库可编程性对象命名
其他数据库可编程性对象采用存储过程相同的命名法。
第二章常用数据类型
下面是我们再数据库设计中常用的几种数据类型：
数据类型
类型
描 述
int
整型
int数据类型可以存储从-231（-2147483648）到231（2147483647）之间的整数。
存储到数据库的几乎所有数值型的数据都可以用这种数据类型。
这种数据类型在数据库里占用4个字节
bigint
整型
从-2^63（-9223372036854775808）到2^63-1（9223372036854775807）的整型数据。
这种数据类型在数据库里占用8字节空间
numeric
精确数值型
numeric数据类型与decimal型相同（要求在存储过程或其他语句中必须表名数据长度及精度）
datetime
日期时间型
datetime数据类型用来表示日期和时间。
这种数据类型存储从1753年1月1日到9999年12月31日间所有的日期和时间数据，精确到三百分之一秒或3.33毫秒
cursor
特殊数据型
cursor数据类型是一种特殊的数据类型，它包含一个对游标的引用。
这种数据类型用在存储过程中，而且创建表时不能用
Uniqueidentifier
特殊数据型
Uniqueidentifier数据类型用来存储一个全局唯一标识符，即GUID。
GUID确实是全局唯一的。
这个数几乎没有机会在另一个系统中被重建。
可以使用NEWID函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列
char
字符型
char数据类型用来存储指定长度的定长非统一编码型的数据。
当定义一列为此类型时，你必须指定列长。
当你总能知道要存储的数据的长度时，此数据类型很有用。
例如，当你按邮政编码加4个字符格式来存储数据时，你知道总要用到10个字符。
此数据类型的列宽最大为8000个字符
varchar
字符型
varchar数据类型，同char类型一样，用来存储非统一编码型字符数据。
与char型不一样，此数据类型为变长。
当定义一列为该数据类型时，你要指定该列的最大长度。
它与char数据类型最大的区别是，存储的长度不是列长，而是数据的长度
nvarchar
统一编码字符型
nvarchar数据类型用作变长的统一编码字符型数据。
此数据类型能存储4000种字符，使用的字节空间增加了一倍
Nvarchar（max）
统一编码字符型
最多为230–1（1 073 741 823）Unicode字符，占用2×字符数＋2字节的空间
Varchar（max）
字符型
最多为231–1（2 147 483 647）字符，一般用来定义XML的入参，每字符1字节＋2字节额外开销
varbinary（max）
二进制数据类型
可变长度二进制数据。
n的取值范围为1至8,000。
max指示最大存储大小是2^31-1个字节。
存储大小为所输入数据的实际长度+2个字节。
第三章数据库设计规范
3.1三范式
数据库设计中应尽可能遵守三范式。
所谓三范式即：
∙没有重复的组或多值的列，这是数据库设计的最低要求。
∙非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。
消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。
∙一个非关键字段不能依赖于另一个非关键字段。
消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。
3.2适当的冗余
但是完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。
冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。
冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。
从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。
比如一些日志表的历史统计信息，我们可以通过作业定期在数据库负载较小的凌晨8点对数据日志数据进行统计，并建立冗余的统计表记录下来。
3.3主键
主键是必要的，SQLSERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。
聚集索引对查询的影响是比较大的，这个在下面索引的叙述。
在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。
主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。
3.4索引
索引分为聚集索引和非聚集索引。
每个数据表只能建立一个聚集索引，聚集索引决定了数据在表中的物理顺序，同时非聚集索引依赖聚集索引存在。
每一个非聚集索引B树的页节点都存有对应的聚集索引键。
因此聚集索引和非聚集索引的选择应该遵守如下规范：
1）应尽量选择符合唯一约束的字段建立聚集索引
2）尽量选择占用空间较小的字段建立聚集索引，一般要求聚集索引小于900字节
3）根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。
同时对数据量比较大的表（>1000行）应结合数据表的使用情况建立非聚集索引以提高数据库查询的反应效率。
但是过多的非聚集索引也会影响数据表记录的插入及更新速度，一般要求非聚集索引的个数不超过两位数。
因此应该针对各数据表的实际情况设计索引。
4）若某列的值大部分是a，少数是别的值（如b,c,d…），且经常以该列的其它值（如b,c,d…）为查询条件，可以考虑对（如b,c,d…）建立筛选索引。
5）把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面，同一索引中的组成列最好不要超过3列。
6）根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。
7）若表主要用来查询，则可按需要建立索引，若对表操作主要是UPDATE，则尽可能少建索引。
3.5主键与聚集索引的关系
在数据库设计中，我们经常容易混淆主键和聚集索引的关系。
因为如果我们建立主键的时候没有特别说明，SQLSERVER会默认在主键上建立聚集索引。
同时由于聚集索引同时也是唯一索引，而且主键一般为较小的键。
所以我们经常将主键作为聚集索引。
但是这并不表示主键和聚集索引等同。
第四章代码编写规范
统一和规范的代码书写风格对保证软件的开发质量、提高团队的开发效率以及将来的维护及其扩展都至关重要。
下边以存储过程为例。
4.1注释
为了增强可读性及美观性，在存储过程头部和存储过程中间应尽量按照如下演示的存储过程做好注释。
USE[MFC_HOTEL]
GO
/*------------------------------------
--用途：
根据用户ID查询辖区场所统计
--项目名称：
--说明：
这里对存储过程进行详细说明
--时间：
2012-09-24
--编写者:
--------------------------------------
--修改记录:
--编号修改时间修改人修改原因修改标注
--0012012-10-11这里说明修改原因001
------------------------------------
测试语句
EXECWeb_UnitMange_MFC_tb_Unit_GetTreeList@LocationStatus=2
*/
CREATEPROCEDURE[dbo].[Web_UnitMange_MFC_tb_Unit_GetTreeList]
（
@GuildIDXMLVARCHAR（MAX）=NULL,
@LocationStatusINT=0--0-全部;1-已标注;2-未标注
）
AS
BEGIN--存储过程应尽量保持这种缩进风格，增强美观性和可读性
SETNOCOUNTON--每个存储过程中关闭统计
--这里介绍每个代码块的功能，增强代码可读性
IFISNULL（@GuildIDXML,''）<>''
BEGIN
EXECsp_xml_preparedocument@HandleOUTPUT,@GuildIDXML
/*SELECTGuildIDINTO#TempGuildID修改前的代码段注释保留*/
INSERTINTO#TempGuildID--001这里标注相应修改的位置
SELECTGuildID
FROMOPENXML（@Handle,N'/ROOT/ROW'）WITH（GuildIDint）
EXECsp_xml_removedocument@Handle
END
END
4.2书写规范
数据库服务器端的触发器和存储过程是一类特殊的文本，为方便开发和维护，提高代码的易读性和可维护性。
规范建议按照分级缩进格式编写该文本。
1）编写存储过程时应遵守以下缩进规则，如下示例
IF1<>1
BEGIN--每个IF条件后的程序块缩进
SELECTU.[GuildID]--各字段尽量对其
U.[UnitCode]--每个查询字段要写明表别名或表名
U.[UnitID]
U.[AreaCode]
FROM[MFC_HOTEL].[dbo].[MFC_tb_Unit]UWITH（NOLOCK）
INNERJOINMFC_HOTEL.dbo.MFC_tb_AreaAWITH（NOLOCK）
ONU.AreaCode=A.AreaCode--JOIN条件缩进增强层次感
WHEREA.IsActive=0
--FROM,JOIN,WHERE对齐
END
ELSE
RETURN
第五章性能优化相关
（以下说几个我们在sql开发中常用到的几方面。
）
5.1.存储过程
5.1.1命名方面
命名最好遵循规范，命名的开头避免使用sp开头。
5.1.2使用方面
使用SETNOCOUNTON命令作为存储过程的第一条语句，以避免在每条sql语句执行后返回所影响行数相关网络的开销。
触发器以及其他批查询也是如此。
5.2.索引
5.2.1避免where子句列上的算术运算符
要始终避免不在where和join子句中的列上使用算术运算符和函数。
使用运算符和函数可阻止这些列上索引的使用。
假设以下情况。
whereCONVERT（char（10）,sa.DateCreate,120）=CONVERT（char（10）,getdate（）-2,120））
可改写为wheresa.DateCreate>=CONVERT（char（10）,getdate（）-3,120））
Andsa.DateCreate2）WhereYear（OrderDate）=2001 and Month（OrderDate）=7
可改写为whereOrderDate between '2001-07-01' and '2001-07-31'
3）SELECT…FROMDEPTWHERESAL*12>25000;
可改写为SELECT…FROMDEPTWHERESAL>25000/12;
5.2.2避免使用前置通配符
前置通配符会使索引无效。
假设以下情况：
WHEREmailaddresslike'%109204421@%';
或者like'%109204421@'
在这种情况下，SQLSERVER将使用全表扫描.避免这样的操作，尤其是大表的情况下，考虑这样设计的是否合理。
建议改为like'109204421@%'
5.2.3避免在索引列上使用NOTIN
Notin会进行全表扫描，notexists也是如此。
想办法变为in或者exists。
假设以下情况：
1）SELECTPUB_NAMEFROMPUBLISHERS
WHEREPUB_IDNOTIN
（SELECTPUB_IDFROMTITLESWHERETYPE='BUSINESS'）
可以改写成：
SELECTA.PUB_NAMEFROMPUBLISHERSALEFTJOINTITLESB
ONB.TYPE='BUSINESS'AND.PUB_ID=B.PUB_IDWHEREB.PUB_IDISNULL
2）SELECTTITLEROMTITLESHERENOTEXISTS（SELECTTITLE_ID
FROMSALESWHERETITLE_ID=TITLES.TITLE_ID）
可以改写成：
SELECTTITLEOMTITLESLEFTJOINSALESNSALES.TITLE_ID=TITLES.TITLE_ID
WHERESALES.TITLE_IDISNULL
5.2.4确保没有隐含的数据类型转换
在一个查询中创建变量时，需确保这些变量和与其比较的列的数据类型相同。
虽然sqlserver能自动转换，但是隐式转换将阻止索引的使用。
在诸如表的连接这样的情况下必须谨慎，需使一个表的主键与被连接的表的外键的数据类型相同。
比如以下。
Unicode字符和non-Unicode字符间的转换.
假设name字段为VARCHAR类型。
在查询时，创建变量@name类型为NVARCHAR类型。
这时，SQLSERVER自动对列进行隐式转换。
可通过重写SQL语句，将参数同列数据类型匹配来轻易的改进性能。
5.2.5关于提示
在开发阶段，不建议运用hint，因为开发时数据量不大，不能代表正式环境及运行一段时间后的数据环境，如果在开发阶段用hint将执行计划固定下来，可能会对后期造成严重问题；后期的维护过程中，出于性能优化的考虑，可以适当运用提示
5.3.查询
1）尽量少用格式转换。
类型转换，计算的操作尽量避免在数据库中应用。
假如不用selectCONVERT（VARCHAR（20）,b.BrandN
升级会员 