SQL4-表結(jié)構(gòu)與數(shù)據(jù)類型

1、福建師范大學(xué)信息技術(shù)學(xué)院表結(jié)構(gòu)與數(shù)據(jù)類型數(shù)據(jù)類型1約束2表結(jié)構(gòu)設(shè)計(jì)3福建師范大學(xué)信息技術(shù)學(xué)院數(shù)據(jù)類型v關(guān)系的所有屬性都需要用數(shù)據(jù)類型加以描述，目的是為了給不同的數(shù)據(jù)分配合適的空間，確定合適的存儲(chǔ)形式。v SQL Server 2008提供了豐富的系統(tǒng)數(shù)據(jù)類型，包含字符串、Unicode字符串、二進(jìn)制數(shù)據(jù)、位、整數(shù)數(shù)據(jù)、浮點(diǎn)數(shù)、日期和時(shí)間數(shù)據(jù)、貨幣數(shù)據(jù)等。福建師范大學(xué)信息技術(shù)學(xué)院數(shù)據(jù)類型v系統(tǒng)數(shù)據(jù)類型福建師范大學(xué)信息技術(shù)學(xué)院數(shù)據(jù)類型SQL Server 2008的整數(shù)數(shù)據(jù)類型數(shù)據(jù)類型范圍存儲(chǔ)bigint-263263-18字節(jié)int-231231-14字節(jié)smallint-215215-12字節(jié)

2、tinyint02551字節(jié)bit0或11位v精確數(shù)字類型 整數(shù)數(shù)據(jù)類型福建師范大學(xué)信息技術(shù)學(xué)院數(shù)據(jù)類型v精確數(shù)字類型 精確的小數(shù)數(shù)據(jù)類型SQL Server 2008的小數(shù)數(shù)據(jù)類型數(shù)據(jù)類型說明decimal(p,s)p（精度），總位數(shù)，138，默認(rèn)為18；s（小數(shù)位數(shù)），0p，默認(rèn)為0；0spnumeric(p,s)福建師范大學(xué)信息技術(shù)學(xué)院數(shù)據(jù)類型v精確數(shù)字類型 貨幣數(shù)據(jù)類型數(shù)據(jù)類型范圍存儲(chǔ)money-263263-18字節(jié)，精確到貨幣單位的1%smallmoney-215215-14字節(jié)，精確到貨幣單位的1%福建師范大學(xué)信息技術(shù)學(xué)院數(shù)據(jù)類型v精確數(shù)字類型 近似數(shù)據(jù)類型數(shù)據(jù)類型范圍存儲(chǔ)flo

3、at(n)-1.79E+308+-1.79E+308取決于n，當(dāng)n取值為124之間，存儲(chǔ)空間為4字節(jié)；當(dāng)n取值在2553之間，存儲(chǔ)空間為8字節(jié)real-3.40E+383.40E+384字節(jié)，相當(dāng)于float(24)福建師范大學(xué)信息技術(shù)學(xué)院數(shù)據(jù)類型v精確數(shù)字類型 日期和時(shí)間數(shù)據(jù)類型數(shù)據(jù)類型范圍存儲(chǔ)date從0001-1-1到9999-12-31格式：yyyy-mm-dd3字節(jié)datetime從1753-1-1到9999-12-31格式：yyyy-mm-dd hh:mm:ss8字節(jié)smarlldatetime從1900-1-1到2079-12-31格式：yyyy-mm-dd hh:mm:ss4字

4、節(jié)福建師范大學(xué)信息技術(shù)學(xué)院數(shù)據(jù)類型v精確數(shù)字類型 字符數(shù)據(jù)類型數(shù)據(jù)類型說明char(n)定長字符串，每個(gè)字符占用1字節(jié)存儲(chǔ)空間，存儲(chǔ)空間為n字節(jié)，n介于08000之間整數(shù)varchar(n)最大長度為n的可變長字符串，存儲(chǔ)空間根據(jù)實(shí)際字符串所占字節(jié)數(shù)text專門用于存儲(chǔ)數(shù)量龐大的變長字符數(shù)據(jù)，最大存儲(chǔ)空間231-1字節(jié)福建師范大學(xué)信息技術(shù)學(xué)院數(shù)據(jù)類型v精確數(shù)字類型 unicode字符數(shù)據(jù)類型數(shù)據(jù)類型說明nchar(n)存儲(chǔ)雙字節(jié)字符，存儲(chǔ)空間為字符數(shù)*2（字節(jié)）；含義同字符數(shù)據(jù)類型nvarchar(n)ntext福建師范大學(xué)信息技術(shù)學(xué)院數(shù)據(jù)類型v精確數(shù)字類型 二進(jìn)制字符數(shù)據(jù)類型數(shù)據(jù)類型說明bi

5、nary固定長度為n字節(jié)的二進(jìn)制字符串，n介于08000之間整數(shù)varbinary最大長度為n字節(jié)的可變長二進(jìn)制字符串image用于存儲(chǔ)超過8000字節(jié)的數(shù)據(jù)，所占空間為0231-1福建師范大學(xué)信息技術(shù)學(xué)院課堂演練_數(shù)據(jù)類型v為以下信息，定制正確的數(shù)據(jù)類型 電話號(hào)碼一般使用什么數(shù)據(jù)類型存儲(chǔ)? 年齡信息一般使用什么數(shù)據(jù)類型存儲(chǔ)? 薪水一般使用什么數(shù)據(jù)類型存儲(chǔ)? 地址一般用什么數(shù)據(jù)類型？ 生日一般用什么數(shù)據(jù)類型？ 身高用什么數(shù)據(jù)類型？ 圖片用什么類型存儲(chǔ)？定長字符型 char(n)整型 int貨幣型 money可變長 varchar(n)日期型 datetime小數(shù)類型 numeric(5,1)圖

6、片存儲(chǔ)有兩種方式路徑存儲(chǔ)：選用varchar(n)圖片存儲(chǔ)：選用image（二進(jìn)制存儲(chǔ)）福建師范大學(xué)信息技術(shù)學(xué)院數(shù)據(jù)類型v用戶自定義數(shù)據(jù)類型 用戶自定義數(shù)據(jù)類型是在SQL Server系統(tǒng)數(shù)據(jù)類型基礎(chǔ)上定義的，定義時(shí)需要指定該類型的名稱、建立在其上的系統(tǒng)數(shù)據(jù)類型以及是否允許為空值(NULL)等。自定義數(shù)據(jù)類型定義以后，使用方式同系統(tǒng)數(shù)據(jù)類型。 系統(tǒng)存儲(chǔ)過程sp_addtype可用來創(chuàng)建用戶自定義數(shù)據(jù)類型。其語法格式如下：sp_addtype新數(shù)據(jù)類型,系統(tǒng)數(shù)據(jù)類型 , null_type福建師范大學(xué)信息技術(shù)學(xué)院數(shù)據(jù)類型例2-6 在學(xué)籍管理數(shù)據(jù)庫SM中創(chuàng)建用戶自定義數(shù)據(jù)類型studentNo，s

7、tudentNo基于的系統(tǒng)數(shù)據(jù)類型是固定長度字符數(shù)據(jù)類型，長度為5，不允許為空。 在Microsoft SQL Server Management Studio中執(zhí)行以下代碼： 運(yùn)行結(jié)果如下圖所示。福建師范大學(xué)信息技術(shù)學(xué)院約束v在設(shè)計(jì)表時(shí)，對(duì)于表和表中的某些列要進(jìn)行完整性約束條件定義，以實(shí)現(xiàn)實(shí)體完整性、參照完整性和用戶定義的完整性。v SQL Server提供了一些在列上強(qiáng)制完成數(shù)據(jù)完整性的約束機(jī)制，包括非空值約束（NOT NULL）、唯一性約束（UNIQUE）、主鍵約束（PRIMARY KEY）、外鍵約束（FOREIGN KEY）、檢查約束（CHECK）和默認(rèn)值約束（DEFAULT）。福建師

8、范大學(xué)信息技術(shù)學(xué)院約束v （1）非空約束（NOT NULL） 空值（NULL）是當(dāng)前不知道、不確定或無法填入的值。空值不能理解為0或空格。例如，某個(gè)學(xué)生選修某門課程的成績是0和NULL具有不同的含義。成績?yōu)?表示該學(xué)生該課程已經(jīng)有了成績，而為NULL則表明該學(xué)生此門課程的成績還沒有被填入。這是兩個(gè)不同的概念。 當(dāng)某列的取值不能為空值時(shí)，則可以在列級(jí)完整性約束定義中設(shè)置為非空值約束(NOT NULL)。其定義格式為：CONSTRAINT NOT NULLv 注意：NOT NULL約束只能定義在列級(jí)完整性約束，不允許作為一個(gè)表級(jí)完整性約束來使用。福建師范大學(xué)信息技術(shù)學(xué)院約束v （2）唯一性約束（U

9、NIQUE） 唯一性約束（UNIQUE）用于限定基本表上的某個(gè)列或某些列的組合在不同元組中的取值不能相同（空值除外）。UNIQUE約束既可以作為列級(jí)約束，也可以作為表級(jí)約束。 UNIQUE約束定義格式為：CONSTRAINT UNIQUE 使用UNIQUE約束時(shí)，要注意以下幾點(diǎn)。 如果沒有為它指定聚簇索引或非聚簇索引，默認(rèn)情況下會(huì)創(chuàng)建一個(gè)非聚簇索引。 每個(gè)UNIQUE約束自動(dòng)生成一個(gè)索引，UNIQUE約束的數(shù)量不能導(dǎo)致表中索引的數(shù)量超過249個(gè)非聚簇索引和1個(gè)聚簇索引。 可以在列級(jí)或表級(jí)定義多個(gè)UNIQUE約束。當(dāng)用作列級(jí)約束時(shí)，UNIQUE只作用于其所在的列，無須指定列名表。當(dāng)用作表級(jí)約束時(shí)

10、，可以約束多個(gè)列的組合。福建師范大學(xué)信息技術(shù)學(xué)院約束v （3）主鍵約束（PRIMARY KEY） 用于定義基本表的主鍵，以實(shí)現(xiàn)實(shí)體完整性規(guī)則。如果某個(gè)列或列組合被定義為主鍵，那么該列或列組合唯一地標(biāo)識(shí)表中的一個(gè)元組。其定義格式為：CONSTRAINT PRIMARY KEY 注意：使用PRIMARY KEY約束時(shí)，有以下幾點(diǎn)要求。 每一個(gè)表僅能定義一個(gè)PRIMARY KEY約束。 主鍵值不可為空。 主鍵值不可重復(fù)。如果PRIMARY KEY約束建立在多列上，其中某一列上的數(shù)據(jù)可以重復(fù)，但是多列的值不能重復(fù)。 image和text類型的列不能定義PRIMARY KEY約束。 同唯一性約束一樣，作

11、為列級(jí)約束時(shí)可以省略列名表。福建師范大學(xué)信息技術(shù)學(xué)院約束v（4）外鍵約束（FOREIGN KEY） 主要用來維護(hù)兩個(gè)表之間數(shù)據(jù)的一致性，它是保證系統(tǒng)參照完整性的手段。外鍵的取值，要么為空值，要么是引用表的某個(gè)主鍵或唯一性鍵的值。外鍵約束既可以作為列級(jí)約束，也可以作為表級(jí)約束。其定義格式為：CONSTRAINT FOREIGN KEY REFERENCES ON DELETE ON UPDATE 福建師范大學(xué)信息技術(shù)學(xué)院約束v（5）檢查約束（CHECK） 可以定義插入或修改某個(gè)元組時(shí)，元組應(yīng)滿足的約束條件，通常用于限定某個(gè)列的取值范圍或與其他列的關(guān)系。其定義格式為：CONSTRAINT CHEC

12、K 條件表達(dá)式是由列名、SQL所支持的運(yùn)算符和函數(shù)等構(gòu)成的邏輯表達(dá)式。CHECK約束既可以作為列級(jí)約束，也可以作為表級(jí)約束。作為列級(jí)約束，每一列只能有一個(gè)CHECK約束，但可以用邏輯運(yùn)算符AND（與）或OR（或)等構(gòu)成復(fù)合條件。福建師范大學(xué)信息技術(shù)學(xué)院約束v（5）檢查約束（CHECK）1、約束某列的取值區(qū)間（between.and ） check(a between. and .)2、約束某列的取值情況（or/in） check(a=. Or a=.) 或者 check(a in(.,.)3、約束某列的取值形式(like) check(a like 0-90-90-90-9)4、約束某字符列的

13、長度 例如：check(len(a)=18)通通 配配 符符 含含 義義 % % 可以匹配任意類型和長度的字符可以匹配任意類型和長度的字符 _ _任意單個(gè)字符任意單個(gè)字符 指定一定范圍內(nèi)的任意單個(gè)字符指定一定范圍內(nèi)的任意單個(gè)字符 不在指定范圍內(nèi)的任意單個(gè)字符不在指定范圍內(nèi)的任意單個(gè)字符 福建師范大學(xué)信息技術(shù)學(xué)院約束v（6） DEFAULT默認(rèn)值約束 作為列級(jí)約束，使用DEFAULT可以為某一列指定默認(rèn)值，當(dāng)用戶插入或修改元組時(shí)，在沒有為該列賦值的情況下，可以用指定的默認(rèn)值填入該列。其定義格式為：CONSTRAINT DEFAULT 注意： 每個(gè)列只能定義一個(gè)DEFAULT約束。 如果定義的默認(rèn)

14、值大于其允許的長度，那么輸入到列的默認(rèn)值將被截?cái)唷?DEFAULT約束表達(dá)式不能參照表中的其他列、其他表、視圖或存儲(chǔ)過程。福建師范大學(xué)信息技術(shù)學(xué)院表結(jié)構(gòu)設(shè)計(jì)v 設(shè)計(jì)表的實(shí)質(zhì)就是設(shè)計(jì)表的結(jié)構(gòu)，設(shè)置表和列的屬性。創(chuàng)建表之前，應(yīng)該考慮一下兩個(gè)問題：一是數(shù)據(jù)庫中要存放哪些數(shù)據(jù)；二是這些數(shù)據(jù)如何規(guī)劃分到表中。即先要確定表的名字、屬性，同時(shí)確定表所包含的列名、列的數(shù)據(jù)類型、長度、是否為空值、約束條件、默認(rèn)值設(shè)置、規(guī)則等，這些屬性構(gòu)成表的結(jié)構(gòu)。福建師范大學(xué)信息技術(shù)學(xué)院表結(jié)構(gòu)設(shè)計(jì)福建師范大學(xué)信息技術(shù)學(xué)院表的創(chuàng)建基本表的創(chuàng)建，用CREATE TABLE語句實(shí)現(xiàn)，其語法格式如下： CREATE TABLE （ 列

15、級(jí)完整性約束條件 , 列級(jí)完整性約束條件 ,）表結(jié)構(gòu)設(shè)計(jì)好，就可以在數(shù)據(jù)庫中創(chuàng)建表。通常一個(gè)數(shù)據(jù)庫還能有多張表，以存儲(chǔ)用戶需要的各類數(shù)據(jù)。方法一：使用SSMS對(duì)象資源管理器創(chuàng)建方法二：使用SQL語句創(chuàng)建福建師范大學(xué)信息技術(shù)學(xué)院表的創(chuàng)建v例1：用CREATE TABLE語句創(chuàng)建studb數(shù)據(jù)庫中的學(xué)生表Student，記錄學(xué)生的基本信息，要求姓名不能為NULL，并約束性別的取值范圍。福建師范大學(xué)信息技術(shù)學(xué)院表的創(chuàng)建v例2：用CREATE TABLE語句創(chuàng)建studb數(shù)據(jù)庫中的Course表，記錄課程的基本信息，要求課程號(hào)為主鍵，課程名唯一，每門課的學(xué)分默認(rèn)為4。福建師范大學(xué)信息技術(shù)學(xué)院表的創(chuàng)建v

16、 例3 用CREATE TABLE語句創(chuàng)建SC表，記錄學(xué)生選課的基本信息，并設(shè)置外鍵約束。福建師范大學(xué)信息技術(shù)學(xué)院表的修改和刪除v表建立以后，可以根據(jù)需要對(duì)它的結(jié)構(gòu)進(jìn)行修改。修改的內(nèi)容包括修改列屬性，如列名、數(shù)據(jù)類型、數(shù)據(jù)長度等，還可以在表結(jié)構(gòu)中添加或刪除列、添加或刪除約束、禁用或啟用約束等?；颈淼男薷目梢允褂肁LTER TABLE語句福建師范大學(xué)信息技術(shù)學(xué)院表的修改和刪除例4 用SQL向表Student中增加一列Email。ALTER TABLE Student ADD Email VARCHAR(40)例5 在Student表中SName列上設(shè)置唯一約束。ALTER TABLE StudentADD