3.1 數(shù)據(jù)類型和表結(jié)構(gòu)

在表創(chuàng)建結(jié)構(gòu)前，需要熟悉SQL Server 2016的數(shù)據(jù)類型。

3.1.1 數(shù)據(jù)類型

SQL Server提供的數(shù)據(jù)類型如表3.2所示。

在討論數(shù)據(jù)類型時(shí)，使用了精度、小數(shù)位數(shù)和長度3個(gè)概念，前兩個(gè)概念是針對數(shù)值型數(shù)據(jù)的，它們的含義如下。

? 精度：指數(shù)值數(shù)據(jù)中所存儲(chǔ)的十進(jìn)制數(shù)據(jù)的總位數(shù)。

? 小數(shù)位數(shù)：指數(shù)值數(shù)據(jù)中小數(shù)點(diǎn)右邊可以有的數(shù)字位數(shù)的最大值。例如，數(shù)值數(shù)據(jù)3890.587的精度是7，小數(shù)位數(shù)是3。

? 長度：指存儲(chǔ)數(shù)據(jù)所使用的字節(jié)數(shù)。

下面分別介紹常用的系統(tǒng)數(shù)據(jù)類型。

1. 整數(shù)型

整數(shù)型包括bigint、int、smallint和tinyint，從標(biāo)識符的含義就可以看出，它們的表示數(shù)范圍逐漸縮小。

（1）bigint：大整數(shù)，數(shù)范圍為?263～263?1，其精度為19，小數(shù)位數(shù)為0，長度為8字節(jié)。

（2）int：整數(shù)，數(shù)范圍為?231～231?1，其精度為10，小數(shù)位數(shù)為0，長度為4字節(jié)。

（3）smallint：短整數(shù)，數(shù)范圍為?215～215?1，其精度為5，小數(shù)位數(shù)為0，長度為2字節(jié)。

（4）tinyint：微短整數(shù)，數(shù)范圍為0～255，其精度為3，小數(shù)位數(shù)為0，長度為1字節(jié)。

2. 精確數(shù)值型

精確數(shù)值型數(shù)據(jù)由整數(shù)部分和小數(shù)部分構(gòu)成，其所有的數(shù)字都是有效位，能夠以完整的精度存儲(chǔ)十進(jìn)制數(shù)。精確數(shù)值型包括decimal和numeric兩類。但這兩種數(shù)據(jù)類型在功能上完全等價(jià)。

聲明精確數(shù)值型數(shù)據(jù)的格式是numeric | decimal(p[,s])，其中p為精度，s為小數(shù)位數(shù)，s的默認(rèn)值為0。例如，指定某列為精確數(shù)值型，精度為6，小數(shù)位數(shù)為3，即decimal(6,3)，那么當(dāng)向某記錄的該列賦值56.342 689時(shí)，該列實(shí)際存儲(chǔ)的是56.343。

decimal和numeric可存儲(chǔ)?1038+1～1038?1的固定精度和小數(shù)位的數(shù)字?jǐn)?shù)據(jù)，它們的存儲(chǔ)長度隨精度變化而變化，最少為5字節(jié)，最多為17字節(jié)。

3. 浮點(diǎn)型

浮點(diǎn)型也稱近似數(shù)值型。顧名思義，這種類型不能提供精確表示數(shù)據(jù)的精度，使用這種類型來存儲(chǔ)某些數(shù)值時(shí)，有可能會(huì)損失一些精度，所以它可用于處理取值范圍非常大且對精確度要求不太高的數(shù)值量，如一些統(tǒng)計(jì)量。

有兩種近似數(shù)值數(shù)據(jù)類型：float[(n)]和real，兩者通常都使用科學(xué)計(jì)數(shù)法表示數(shù)據(jù)，即形為：尾數(shù)E階數(shù)，如5.6432E20，?2.98E10，1.287659E?9等。

（1）real：使用4字節(jié)存儲(chǔ)數(shù)據(jù)，數(shù)范圍為?3.40E+38～3.40E+38，數(shù)據(jù)精度為7位有效數(shù)字。

（2）float：float型數(shù)據(jù)的數(shù)范圍為?1.79E+308～1.79E+308。定義中的n取值范圍是1～53，用于指示其精度和存儲(chǔ)大小。當(dāng)n在1～24時(shí)，實(shí)際上將定義一個(gè)real型數(shù)據(jù)，存儲(chǔ)長度為4字節(jié)，精度為7位有效數(shù)字。

當(dāng)n在25～53時(shí)，存儲(chǔ)長度為8字節(jié)，精度為15位有效數(shù)字。當(dāng)省略n時(shí)，代表n在25～53。

4. 貨幣型

SQL Server提供了兩個(gè)專門用于處理貨幣的數(shù)據(jù)類型：money和smallmoney，它們用十進(jìn)制數(shù)表示貨幣值。

（1）money：數(shù)據(jù)的數(shù)范圍為?263～263?1，其精度為19，小數(shù)位數(shù)為4，長度為8字節(jié)。money的數(shù)范圍與bigint相同，不同的只是money型有4位小數(shù)。實(shí)際上，money就是按照整數(shù)進(jìn)行運(yùn)算的，只是將小數(shù)點(diǎn)固定在最后4位。

（2）smallmoney：數(shù)范圍為?231～231?1，其精度為10，小數(shù)位數(shù)為4，長度為4字節(jié)。

可見smallmoney與int的關(guān)系就如同money與bigint的關(guān)系。

當(dāng)向表中插入money或smallmoney類型的值時(shí)，必須在數(shù)據(jù)前面加上貨幣表示符號（$），并且數(shù)據(jù)中間不能有逗號（,）；若貨幣值為負(fù)數(shù)，則需要在符號$的后面加上負(fù)號（?）。例如，$15 000.32，$680，$?20 000.9088都是正確的貨幣數(shù)據(jù)表示形式。

5. 位型

SQL Server的位（bit）型數(shù)據(jù)相當(dāng)于其他語言中的邏輯型數(shù)據(jù)，它只存儲(chǔ)0和1，長度為1字節(jié)。但要注意，SQL Server對表中bit類型列的存儲(chǔ)進(jìn)行了優(yōu)化：如果一個(gè)表中有不多于8個(gè)的bit列，則這些列將作為1字節(jié)存儲(chǔ)；如果表中有9～16個(gè)bit列，則這些列將作為2字節(jié)存儲(chǔ)，更多列的情況以此類推。

當(dāng)為bit類型數(shù)據(jù)賦0時(shí)，其值為0；而賦非0（如100）時(shí)，其值為1。

字符串值TRUE和FALSE可以轉(zhuǎn)換為以下bit值：TRUE轉(zhuǎn)換為1，F(xiàn)ALSE轉(zhuǎn)換為0。

6. 字符型

字符型數(shù)據(jù)用于存儲(chǔ)字符串，字符串中可包括字母、數(shù)字和其他特殊符號（如#、@、&等）。在輸入字符串時(shí)，需將串中的符號用單引號或雙引號括起來，如'abc'、"Abc<Cde"。

SQL Server字符型包括兩類：固定長度（char）或可變長度（varchar）字符數(shù)據(jù)類型。

（1）char[(n)]：定長字符數(shù)據(jù)類型，其中n定義字符型數(shù)據(jù)的長度，n在1～8 000，默認(rèn)為1。當(dāng)表中的列定義為char(n)類型時(shí)，若實(shí)際存儲(chǔ)的串長度不足n時(shí)，則在串的尾部添加空格以達(dá)到長度n，所以char(n)的長度為n。

例如，某列的數(shù)據(jù)類型為char(20)，而輸入的字符串為"ahjm1922"，則存儲(chǔ)的是字符ahjm1922和12個(gè)空格。若輸入的字符個(gè)數(shù)超出了n，則超出的部分被截?cái)唷?/p>

（2）varchar[(n)]：變長字符數(shù)據(jù)類型，其中，n的規(guī)定與定長字符型char中的n完全相同，但這里n表示的是字符串可達(dá)到的最大長度。

varchar(n)的長度為輸入字符串的實(shí)際字符個(gè)數(shù)，而不一定是n。例如，表中某列的數(shù)據(jù)類型為varchar(100)，而輸入的字符串為"ahjm1922"，則存儲(chǔ)的就是字符ahjm1922，其長度為8字節(jié)。當(dāng)列中的字符數(shù)據(jù)值長度差不多時(shí)，如姓名，此時(shí)可使用char；當(dāng)列中的數(shù)據(jù)值長度顯著不同時(shí)，使用varchar較為恰當(dāng)，可以節(jié)省存儲(chǔ)空間。

7. unicode字符型

unicode是“統(tǒng)一字符編碼標(biāo)準(zhǔn)”，用于支持國際上非英語語種的字符數(shù)據(jù)的存儲(chǔ)和處理。SQL Server的unicode字符型可以存儲(chǔ)Unicode標(biāo)準(zhǔn)字符集定義的各種字符。

unicode字符型包括nchar[(n)]和nvarchar[(n)]兩類。nchar是固定長度unicode數(shù)據(jù)的數(shù)據(jù)類型，nvarchar是可變長度unicode數(shù)據(jù)的數(shù)據(jù)類型，二者均使用UNICODE UCS-2字符集。

（1）nchar[(n)]：nchar[(n)]為包含n個(gè)字符的固定長度unicode字符型數(shù)據(jù)，n的值在1～4 000，長度為2n字節(jié)。若輸入的字符串長度不足n，將以空白字符補(bǔ)足。

（2）nvarchar[(n)]：nvarchar[(n)]為最多包含n個(gè)字符的可變長度unicode字符型數(shù)據(jù)，n的值在1～4 000，默認(rèn)為1。長度是所輸入字符個(gè)數(shù)的兩倍。

實(shí)際上，nchar、nvarchar與char、varchar的使用非常相似，只是字符集不同（前者使用unicode字符集，后者使用ASCII字符集）。

8. 文本型

當(dāng)需要存儲(chǔ)大量的字符數(shù)據(jù)，如較長的備注、日志信息等時(shí)，字符型數(shù)據(jù)最長8 000個(gè)字符的限制可能使它們不能滿足這種應(yīng)用需求，此時(shí)可使用文本型數(shù)據(jù)。

文本型包括text和ntext兩類，分別對應(yīng)ASCII字符和unicode字符。

（1）text類型：可以表示最大長度為231?1個(gè)字符，其數(shù)據(jù)的存儲(chǔ)長度為實(shí)際字符數(shù)個(gè)字節(jié)。

（2）ntext類型：可表示最大長度為230?1個(gè)unicode字符，其數(shù)據(jù)的存儲(chǔ)長度是實(shí)際字符個(gè)數(shù)的兩倍（以字節(jié)為單位）。

9. 二進(jìn)制型

二進(jìn)制數(shù)據(jù)類型表示的是位數(shù)據(jù)流，包括binary（固定長度）和varbinary（可變長度）兩種。

（1）binary [(n)]：固定長度的n個(gè)字節(jié)二進(jìn)制數(shù)據(jù)。n的取值范圍為1～8 000，默認(rèn)為1。binary(n)數(shù)據(jù)的存儲(chǔ)長度為n+4個(gè)字節(jié)。若輸入的數(shù)據(jù)長度小于n，則不足部分用0填充；若輸入的數(shù)據(jù)長度大于n，則多余部分被截?cái)唷?/p>

（2）varbinary [(n)]：n個(gè)字節(jié)變長二進(jìn)制數(shù)據(jù)。n取值范圍為1～8 000，默認(rèn)為1。varbinary(n)數(shù)據(jù)的存儲(chǔ)長度為實(shí)際輸入數(shù)據(jù)長度+4個(gè)字節(jié)。

10. 日期時(shí)間類型

日期時(shí)間類型數(shù)據(jù)用于存儲(chǔ)日期和時(shí)間信息，日期時(shí)間數(shù)據(jù)類型包括date、time、datetime2和datetimeoffset。

（1）datetime：可表示的日期范圍從1753年1月1日到9999年12月31日，精確度為0.03s（3.33ms或0.00333s）。例如，1～3ms的值都表示為0ms，4～6ms的值都表示為4ms。

datetime類型數(shù)據(jù)長度為8字節(jié)，日期和時(shí)間分別使用4個(gè)字節(jié)存儲(chǔ)。前4字節(jié)用于存儲(chǔ)datetime類型數(shù)據(jù)中距1900年1月1日的天數(shù)。為正數(shù)表示日期在1900年1月1日之后，為負(fù)數(shù)則表示日期在1900年1月1日之前。后4字節(jié)用于存儲(chǔ)datetime類型數(shù)據(jù)中距12:00（24小時(shí)制）的毫秒數(shù)。

用戶以字符串形式輸入datetime類型數(shù)據(jù)，系統(tǒng)也以字符串形式輸出datetime類型數(shù)據(jù)。通常將用戶輸入到系統(tǒng)及系統(tǒng)輸出的datetime類型數(shù)據(jù)的字符串形式稱為datetime類型數(shù)據(jù)的“外部形式”，而將datetime在系統(tǒng)內(nèi)的存儲(chǔ)形式稱為“內(nèi)部形式”。SQL Server負(fù)責(zé)datetime類型數(shù)據(jù)的兩種表現(xiàn)形式之間的轉(zhuǎn)換，包括合法性檢查。

用戶給出datetime類型數(shù)據(jù)值時(shí)，日期部分和時(shí)間部分分別給出。

日期部分的表示形式常用的格式如下：

時(shí)間部分常用的表示格式如下：

（2）smalldatetime：可表示從1900年1月1日到2079年6月6日的日期和時(shí)間，數(shù)據(jù)精確到分鐘。即29.998s或更低的值向下舍入為最接近的分鐘，29.999s或更高的值向上舍入為最接近的分鐘。

smalldatetime類型數(shù)據(jù)的存儲(chǔ)長度為4字節(jié)，前2字節(jié)用來存儲(chǔ)smalldatetime類型數(shù)據(jù)中日期部分距1900年1月1日之后的天數(shù)。后2字節(jié)用來存儲(chǔ)smalldatetime類型數(shù)據(jù)中時(shí)間部分距中午12點(diǎn)的分鐘數(shù)。

用戶輸入smalldatetime類型數(shù)據(jù)的格式與datetime類型數(shù)據(jù)完全相同，只是它們的內(nèi)部存儲(chǔ)可能不相同。

（3）date：可以表示從公元元年1月1日到9999年12月31日的日期，date類型只存儲(chǔ)日期數(shù)據(jù)，不存儲(chǔ)時(shí)間數(shù)據(jù)，存儲(chǔ)長度為3字節(jié)，表示形式與datetime數(shù)據(jù)類型的日期部分相同。

（4）time：只存儲(chǔ)時(shí)間數(shù)據(jù)，表示格式為“hh:mm:ss[.nnnnnnn]”。hh表示小時(shí)，范圍為0～23。mm表示分鐘，范圍為0～59。ss表示秒數(shù)，范圍為0～59。n是0～7位數(shù)字，范圍為0～9 999 999，表示秒的小數(shù)部分，即微秒數(shù)。所以time數(shù)據(jù)類型的取值范圍為00:00:00.000 000 0～23:59:59.999 999 9。time類型的存儲(chǔ)大小為5字節(jié)。另外，還可以自定義time類型微秒數(shù)的位數(shù)，例如，time(1)表示小數(shù)位數(shù)為1，默認(rèn)為7。

（5）datetime2：也用于存儲(chǔ)日期和時(shí)間信息。但是datetime2類型取值范圍更廣，日期部分取值范圍從公元元年1月1日到9999年12月31日，時(shí)間部分的取值范圍為00:00:00.000 000 0～23:59:59.999 999。另外，用戶還可以自定義datetime2數(shù)據(jù)類型中微秒數(shù)的位數(shù)，例如，datetime(2)表示小數(shù)位數(shù)為2。datetime2類型的存儲(chǔ)大小隨著微秒數(shù)的位數(shù)（精度）而改變，精度小于3時(shí)為6字節(jié)，精度為4和5時(shí)為7字節(jié)，所有其他精度則需要8字節(jié)。

（6）datetimeoffset：也用于存儲(chǔ)日期和時(shí)間信息，取值范圍與datetime2類型相同。但datetimeoffset類型具有時(shí)區(qū)偏移量，此偏移量指定時(shí)間相對于協(xié)調(diào)世界時(shí)（UTC）偏移的小時(shí)和分鐘數(shù)。

datetimeoffset的格式為“YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|?}hh:mm]”，其中，hh為時(shí)區(qū)偏移量中的小時(shí)數(shù)，范圍為00～14，mm為時(shí)區(qū)偏移量中的額外分鐘數(shù)，范圍為00～59。時(shí)區(qū)偏移量中必須包含“+”（加）或“?”（減）號。這兩個(gè)符號表示是在UTC時(shí)間的基礎(chǔ)上加上還是從中減去時(shí)區(qū)偏移量以得出本地時(shí)間。時(shí)區(qū)偏移量的有效范圍為?14:00～+14:00。

11. 時(shí)間戳型

標(biāo)識符是timestamp。若創(chuàng)建表時(shí)定義一個(gè)列的數(shù)據(jù)類型為時(shí)間戳類型，那么每當(dāng)對該表加入新行或修改已有行時(shí)，都由系統(tǒng)自動(dòng)將一個(gè)計(jì)數(shù)器值加到該列，即將原來的時(shí)間戳值加上一個(gè)增量。

記錄timestamp列的值實(shí)際上反映了系統(tǒng)對該記錄修改的相對（相對于其他記錄）順序。一個(gè)表只能有一個(gè)timestamp列。timestamp類型數(shù)據(jù)的值實(shí)際上是二進(jìn)制格式數(shù)據(jù)，其長度為8字節(jié)。

12. 圖像數(shù)據(jù)類型

標(biāo)識符是image，它用于存儲(chǔ)圖片、照片等。實(shí)際存儲(chǔ)的是可變長度二進(jìn)制數(shù)據(jù)，介于0與231?1 (2 147 483 647)字節(jié)。該類型是為了向下兼容而保留的數(shù)據(jù)類型，微軟推薦用戶使用varbinary(MAX)數(shù)據(jù)類型來替代image類型。

13. 其他數(shù)據(jù)類型

其他幾種數(shù)據(jù)類型有：cursor、sql_variant、table、uniqueidentifier、xml和hierarchyid。

（1）cursor：游標(biāo)數(shù)據(jù)類型，用于創(chuàng)建游標(biāo)變量或定義存儲(chǔ)過程的輸出參數(shù)。

（2）sql_variant：一種存儲(chǔ)SQL Server支持的各種數(shù)據(jù)類型（除text、ntext、image、timestamp和sql_variant外）值的數(shù)據(jù)類型。sql_variant的最大長度可達(dá)8 016字節(jié)。

（3）table：用于存儲(chǔ)結(jié)果集的數(shù)據(jù)類型，結(jié)果集可以供后續(xù)處理。

（4）uniqueidentifier：唯一標(biāo)識符類型。系統(tǒng)將為這種類型的數(shù)據(jù)產(chǎn)生唯一標(biāo)識值，它是一個(gè)16字節(jié)長的二進(jìn)制數(shù)據(jù)。

（5）xml：用來在數(shù)據(jù)庫中保存xml文檔和片段的一種類型，但是此種類型文件的大小不能超過2 GB。

（6）hierarchyid：可表示層次結(jié)構(gòu)中的位置。

varchar、nvarchar、varbinary這3種數(shù)據(jù)類型可以使用MAX關(guān)鍵字，如varchar(MAX)、nvarchar(MAX)、varbinary(MAX)，加了MAX關(guān)鍵字的這幾種數(shù)據(jù)類型最多可存放231-1個(gè)字節(jié)的數(shù)據(jù)，分別可以用來替換text、ntext和image數(shù)據(jù)類型。

3.1.2 表結(jié)構(gòu)設(shè)計(jì)

定義表結(jié)構(gòu)就是要確定表的名字、表的屬性，同時(shí)確定表所包含的列名、列的數(shù)據(jù)類型、長度、是否可為空值、約束條件、默認(rèn)值設(shè)置、規(guī)則，以及所需索引、哪些列是主鍵、哪些列是外鍵等。這里仍以學(xué)生成績管理系統(tǒng)的3個(gè)表：學(xué)生表（xsb）、課程表（kcb）和成績表（cjb）為例介紹如何設(shè)計(jì)表的結(jié)構(gòu)。

學(xué)生表包含的列有學(xué)號、姓名、性別、出生時(shí)間、專業(yè)、總學(xué)分、備注。為了便于理解，本書基礎(chǔ)部分使用中文列標(biāo)題表示列名，后面的綜合實(shí)習(xí)使用英文字母來表示列名。設(shè)計(jì)的xsb（數(shù)據(jù)庫中學(xué)生表的名稱）的表結(jié)構(gòu)如表3.3所示。

對表中列名做如下說明。

“學(xué)號”列：學(xué)號值有一定的意義，例如，“191301”中“19”表示所屬班級，“13”表示學(xué)生的年級，“01”表示學(xué)生在班級中的序號，所以“學(xué)號”列的數(shù)據(jù)類型可以是6位的定長字符型數(shù)據(jù)。“學(xué)號”能唯一標(biāo)識一個(gè)學(xué)生，所以將“學(xué)號”列設(shè)為該表主鍵。

“姓名”列：姓名一般不超過4個(gè)中文字符，采用8字節(jié)字符型數(shù)據(jù)。

“性別”列：只有“男”“女”兩種值，所以可以使用bit型數(shù)據(jù)，值1表示“男”，值0表示“女”，默認(rèn)是1。

“出生時(shí)間”列：采用日期時(shí)間類型數(shù)據(jù)，列類型定為date，可以進(jìn)行日期運(yùn)算。

“專業(yè)”列：因?yàn)榧俣ū４鎸I(yè)不超過6個(gè)漢字，所以采用12字節(jié)字符型類型。

“總學(xué)分”列：是整數(shù)型數(shù)據(jù)，值為0～160，列類型定為int，默認(rèn)值是0。

“備注”列：需要存放學(xué)生的備注信息，備注信息的內(nèi)容為0～500個(gè)字，所以應(yīng)該使用varchar類型。

當(dāng)然，如果要包含學(xué)生的“照片”列，可以使用image或varbinary(MAX)數(shù)據(jù)類型；要包含學(xué)生的“聯(lián)系方式”列，可以使用xml數(shù)據(jù)類型。

參照xsb表結(jié)構(gòu)的設(shè)計(jì)方法，同樣可以設(shè)計(jì)出其他兩個(gè)表的結(jié)構(gòu)，表3.4所示為kcb的表結(jié)構(gòu)，表3.5所示為cjb的表結(jié)構(gòu)。

注意，cjb（成績表）需要學(xué)號和課程兩個(gè)字段合起來作為主鍵。

表結(jié)構(gòu)設(shè)計(jì)完后就可以開始在數(shù)據(jù)庫中創(chuàng)建表了，本書所用到的學(xué)生管理系統(tǒng)的表都在xscj數(shù)據(jù)庫中創(chuàng)建。創(chuàng)建和操作數(shù)據(jù)庫中的表既可以通過“SSMS”中的界面方式進(jìn)行，也可以通過T-SQL命令方式進(jìn)行。