數(shù)據(jù)庫技術(shù)規(guī)范

數(shù)據(jù)庫技術(shù)規(guī)范XXXXXX公司二零一六年十月文檔控制頁編號章節(jié)修訂說明修訂日期修訂前版本號修訂后版本號修改人審批人目錄數(shù)據(jù)庫技術(shù)規(guī)范 41 建表規(guī)范 42 使用范圍 43 概述 44 書寫 45 注釋 56 Oracle、MySQL、SQLSever差異 57 優(yōu)化 78 索引創(chuàng)建原則 79 函數(shù)、表達式使用 8IN/OR子句使用 8!=或<>操作符子句使用 8不要對索引字段進行運算 8不要對索引字段進行格式轉(zhuǎn)換 8不要對索引字段使用函數(shù) 9不要對索引字段進行多字段連接 9Like的使用 910 設(shè)計 10數(shù)據(jù)庫邏輯設(shè)計的規(guī)范化 10合理的冗余 10主鍵的設(shè)計 10外鍵的設(shè)計 10字段的設(shè)計 11索引的設(shè)計 11數(shù)據(jù)庫技術(shù)規(guī)范建表規(guī)范表名：表的命名使用【模塊名_子模塊】的方式命名，全部大寫。字段名：字段名包含多個單詞的，使用下劃線分開，全部大寫。 日期一律使用varchar。 外鍵一律不設(shè)，交由程序處理控制。使用范圍所有需要與數(shù)據(jù)庫交互的應(yīng)用系統(tǒng)。概述大部分業(yè)務(wù)系統(tǒng)需要與數(shù)據(jù)庫進行交互，與數(shù)據(jù)庫交互的主要方式就是SQL語句，編寫規(guī)范的SQL語句不但利于閱讀，而且被數(shù)據(jù)庫重復(fù)使用的幾率也較大，執(zhí)行效率相對較高，編寫的好的SQL與編寫的差的SQL在執(zhí)行性能上可能會差幾倍甚至幾千幾萬倍，因此養(yǎng)成好的SQL編寫規(guī)范對于提高項目質(zhì)量及提高開發(fā)人員自身素質(zhì)有著潛在的極大的影響。書寫SQL書寫遵守如下規(guī)范：在同一個項目中，為了最大限度實現(xiàn)SQL的共享，要求書寫sql語句時大小寫要一致，為了閱讀方便和統(tǒng)一起見，所有SQL語句全部小寫（如SQL謂詞，字段名，表名等）,常量除外，常量可以按需要書寫。舉例：下面兩個相同的語句除常量外都要統(tǒng)一起來。1)selectnamefromemp;2)select‘NAME’fromempwhereemp_no=’QD001’SQL語句盡可能放在一行，若SQL太長放在一行中影響閱讀時可分多行，但要保持縮進一致，縮進可用TAB或者空格，但TAB數(shù)和空格數(shù)最好一致。SQL語句中，各謂詞之間以空格分割的，盡量保持空格數(shù)量一致，即若用一個空格分割，則全部都用一個空格分割，便于數(shù)據(jù)庫能夠共享。能使用綁定變量的，盡量使用綁定變量，尤其是在前臺程序中.對下面列出的情況，慎重使用綁定變量：列值傾斜嚴重，如：某一狀態(tài)列大部分值是‘1’，只有極少數(shù)值為’2’，這種情況不宜用綁定變量，而應(yīng)該用常量，便于數(shù)據(jù)庫使用柱狀圖統(tǒng)計信息。日期時間列。總之：書寫SQL的目標是若sql的用途是一樣的，則sql應(yīng)該完全一致，包括空格，大小寫。注釋1)對較為復(fù)雜的SQL語句加上注釋，說明算法、功能。注釋風格：注釋單獨成行、放在語句前面。2)應(yīng)對不易理解的分支條件表達式加注釋。3)對重要的計算應(yīng)說明其功能。4)過長的函數(shù)實現(xiàn)，應(yīng)將其語句按實現(xiàn)的功能分段加以概括性說明。5)常量及變量注釋時，應(yīng)注釋被保存值的含義(必須)，合法取值的范圍(可選)。6)可采用單行/多行注釋。（--或/**/方式）。Oracle、MySQL、SQLSever差異1.類型轉(zhuǎn)換

--Oracle

Selectto_number('123')

from

dual;

--123;

Selecto_char(33)

from

dual;

--33;

Selectto_date('2004-11-27','yyyy/mm/dd')

from

dual;--2004-11-27

--Mysql

Selectcast('123'

as

signed

integer);

--123

Selectcast(33

as

char(2));

--33；

Selectto_days('2000-01-01');

--730485

--SqlServer

Selectcast('123'

as

decimal(30,2));

--123.00

select

cast(33

as

char(2));

--33；

selectconvert(varchar(12)

,

getdate(),

120)

2.四舍五入函數(shù)區(qū)別

--Oracle

select

round(12.86*10)/10

from

dual;

--12.9

--Mysql

select

format(12.89,1);

--12.9

--SqlServer

select

round(12.89,1);

--12.9

3.日期時間函數(shù)

--Oracle

select

sysdate

from

dual;

--日期時間

--Mysql

select

sysdate();

--日期時間

select

current_date();

--日期

--SqlServer

select

getdate();

--日期時間

select

datediff(day,'2010-01-01',cast(getdate()

as

varchar(10)));--日期相差天數(shù)

4.Decode函數(shù)

--Oracle

select

decode(sign(12),1,1,0,0,-1)

from

dual;--1

--Mysql/SqlServer

select

case

when

sign(12)=1

then

1

when

sign(12)=0

then

0

else

-1

end;--1

5.判空函數(shù)

--Oracle

select

nvl(1,0)

from

dual;

--1

--Mysql

select

ifnull(1,0);

--1

--SqlServer

select

isnull(1,0);

--1

6.字符串連接函數(shù)

--Oracle

select

'1'||'2'

from

dual;

--12

select

concat('1','2');

--12

--Mysql

select

concat('1','2');

--12

--SqlServer

select

'1'+'2';

--12

7.記錄限制函數(shù)

--Oracle

select

1

from

dual

where

rownum

<=

10;

--Mysql

select

1

from

dual

limit

10;

--SqlServer

select

top

10

;

8.字符串截取函數(shù)

--Oracle

select

substr('12345',1,3)

from

dual;

--Mysql/SqlServer

select

substring('12345',1,3);

9.把多行轉(zhuǎn)換成一合并列

--Oracle

select

wm_concat(列名)

from

dual;

--多行記錄轉(zhuǎn)換成一列之間用,分割

--Mysql/SqlServer

select

group_concat(列名);

10、中文排序

--Oracle

select

*

from

dual

order

by

NLSSORT('CD.F_NAME_CH',NLS_SORT=SCHINESE_PINYIN_M)

desc;

--中文拼音排序

1）按筆畫排序

select

*

from

Table

order

by

nlssort(columnName,'NLS_SORT=SCHINESE_STROKE_M')

2）按部首排序

select

*

from

Table

order

by

nlssort(columnName,'NLS_SORT=SCHINESE_RADICAL_M')

3）按拼音排序

select

*

from

Table

order

by

nlssort(columnName,'NLS_SORT=SCHINESE_PINYIN_M');

--Mysql

select

*

from

dual

NLSSORT

order

by

CONVERT(‘F_NAME_CH'

USING

GBK)

desc

如果數(shù)據(jù)表tbl的某字段name的字符編碼是latin1_swedish_ci;

select

*

from

`tbl`

order

by

birary(name)

asc

如果數(shù)據(jù)表tbl的某字段name的字符編碼是utf8_general_ci;

SELECT

name

FROM

`tbl`

WHERE

1

ORDER

BY

CONVERT(

name

USING

gbk

)

COLLATE

gbk_chinese_ci

ASC

--SqlServer

1）中文的筆畫順序排序

Select

*

From

[Table_Name]

Order

By

[Column_Name]

Collate

Chinese_PRC_Stroke_ci_as

2）按拼音排序

Select

*

From

[Table_Name]

ORDER

BY

[Column_Name]

COLLATE

Chinese_PRC_CS_AS_KS_WS優(yōu)化WHERE子句盡量避免使用函數(shù)；避免在ORDERBY子句中使用表達式；限制在GROUPBY子句中使用表達式；慎用游標；XML文件中統(tǒng)一用大寫SQL語句；盡可能少的返回結(jié)果集行的數(shù)量避免使用SELECT*語句；減少結(jié)果集中的列的數(shù)量；視圖嵌套使用不能超過3層；不要使用沒有意義的列作為聚集索引列，例如，加1自增列；避免隱式類型轉(zhuǎn)換，例如字符型一定要用’’，數(shù)字型一定不要使用’’；查詢語句一定要有范圍的限定，避免全表掃描操作；合理對大表進行分區(qū)；慎用DISTINCT關(guān)鍵字；慎用UNION關(guān)鍵字，可以用OR替代；使用TOP1替COUNT(*)來判斷是否存在記錄；索引創(chuàng)建原則同一索引中的組成列最好不要超過3列。把經(jīng)常一起出現(xiàn)的字段組合在一起，組成組合索引，組合索引的字段順序與主鍵一樣，也需要把最常用的字段放在前面，把重復(fù)率低的字段放在前面。根據(jù)使用頻率決定哪些字段需要建立索引，選擇經(jīng)常作為連接條件、篩選條件、聚合查詢、排序的字段作為索引的候選字段。根據(jù)數(shù)據(jù)量決定哪些表需要增加索引，數(shù)據(jù)量小的可以只有主鍵。若某列中有大量的值是空值，可以建立索引。要對值分布較寬的列建立索引。若表主要用來查詢，則可按需要建立索引，若對表操作主要是UPDATE，則盡可能少建索引。不要對值較窄的列建立索引，如性別不要索引較小的表（如表不足1000行）若某列的值大部分是a，少數(shù)是別的值（如b,c,d…），且經(jīng)常以該列的其它值(如b,c,d…)為查詢條件，則可以將值a設(shè)為空值，并在此列上創(chuàng)建索引。函數(shù)、表達式使用IN/OR子句使用IN、OR、NOTINSqlServer2005數(shù)據(jù)庫可以分析出應(yīng)該根據(jù)索引查找。屬于2005版本的新特性。!=或<>操作符子句使用!=或<>操作符可以用INDEXSEEK查找的，可以正常使用。不要對索引字段進行運算例如：SELECTIDFROMTWHERENUM/2=100應(yīng)改為:SELECTIDFROMTWHERENUM=100*2SELECTIDFROMTWHERENUM/2=NUM1如果NUM有索引應(yīng)改為:SELECTIDFROMTWHERENUM=NUM1*2如果NUM1有索引則不應(yīng)該改。不要對索引字段進行格式轉(zhuǎn)換日期字段的例子：WHERECONVERT(VARCHAR(10),日期字段,120)=‘2008-08-15’應(yīng)該改為WHERE日期字段〉=‘2008-08-15’AND日期字段<‘2008-08-16’ISNULL轉(zhuǎn)換的例子：WHEREISNULL(字段,’’)<>’’應(yīng)改為:WHERE字段<>’’WHEREISNULL(字段,’’)=’’不應(yīng)修改WHEREISNULL(字段,’F’)=’T’應(yīng)改為:WHERE字段=’T’WHEREISNULL(字段,’F’)<>’T’不應(yīng)修改不要對索引字段使用函數(shù)WHERELEFT(NAME,3)='ABC'或者WHERESUBSTRING(NAME,1,3)='ABC'應(yīng)改為:WHERENAMELIKE'ABC%'日期查詢的例子：WHEREDATEDIFF(DAY,日期,'2005-11-30')=0應(yīng)改為:WHERE日期>='2005-11-30'AND日期<'2005-12-1‘WHEREDATEDIFF(DAY,日期,'2005-11-30')>0應(yīng)改為:WHERE日期<'2005-11-30‘WHEREDATEDIFF(DAY,日期,'2005-11-30')>=0應(yīng)改為:WHERE日期<'2005-12-01‘WHEREDATEDIFF(DAY,日期,'2005-11-30')<0應(yīng)改為:WHERE日期>='2005-12-01‘WHEREDATEDIFF(DAY,日期,'2005-11-30')<=0應(yīng)改為:WHERE日期>='2005-11-30‘不要對索引字段進行多字段連接例如：WHEREFAME+’.’+LNAME=‘H.Y’應(yīng)改為:WHEREFNAME=‘H’ANDLNAME=‘Y’Like的使用對索引列避免使用like‘%xx’，應(yīng)該使用like‘xx%’。設(shè)計數(shù)據(jù)結(jié)構(gòu)時就應(yīng)該考慮這個問題，不要出現(xiàn)必須要采用like‘%xx’才能滿足業(yè)務(wù)需要的情形。設(shè)計數(shù)據(jù)庫邏輯設(shè)計的規(guī)范化第1規(guī)范：沒有重復(fù)的組或多值的列，這是數(shù)據(jù)庫設(shè)計的最低要求。第2規(guī)范：每個非關(guān)鍵字段必須依賴于主關(guān)鍵字，不能依賴于一個組合式主關(guān)鍵字的某些組成部分。消除部分依賴，大部分情況下，數(shù)據(jù)庫設(shè)計都應(yīng)該達到第二范式。第3規(guī)范：一個非關(guān)鍵字段不能依賴于另一個非關(guān)鍵字段。消除傳遞依賴，達到第三范式應(yīng)該是系統(tǒng)中大部分表的要求，除非一些特殊作用的表。合理的冗余完全按照規(guī)范化設(shè)計的系統(tǒng)幾乎是不可能的，除非系統(tǒng)特別的小，在規(guī)范化設(shè)計后，有計劃地加入冗余是必要的。冗余可以是冗余數(shù)據(jù)庫、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是為了編程方便而增加，也可以是為了性能的提高而增加。從性能角度來說，冗余數(shù)據(jù)庫可以分散數(shù)據(jù)庫壓力，冗余表可以分散數(shù)據(jù)量大的表的并發(fā)壓力，也可以加快特殊查詢的速度，冗余字段可以有效減少數(shù)據(jù)庫表的連接，提高效率。主鍵的設(shè)計主鍵是必要的，SQLSERVER的主鍵同時是一個唯一索引，而且在實際應(yīng)用中，我們往往選擇最小的鍵組合作為主鍵，所以主鍵往往適合作為表的聚集索引。聚集索引對查詢的影響是比較大的，這個在下面索引的敘述。在有多個鍵的表，主鍵的選擇也比較重要，一般選擇總的長度小的鍵，小的鍵的比較速度快，同時小的鍵可以使主鍵的B樹結(jié)構(gòu)的層次更少。主鍵的選擇還要注意組合主鍵的字段次序，對于組合主鍵來說，不同的字段次序的主鍵的性能差別可能會很大，一般