1、第3章表與表數(shù)據(jù)操作,3.1表結(jié)構(gòu)和數(shù)據(jù)類型,3.2界面方式操作表,3.3命令方式操作表,3.4界面方式操作表數(shù)據(jù),3.5命令方式操作表數(shù)據(jù),3.1表結(jié)構(gòu)和數(shù)據(jù)類型,3.1.1表和表結(jié)構(gòu)每個數(shù)據(jù)庫包含了若干個表。表是SQLServer中最主要的數(shù)據(jù)庫對象，它是用來存儲數(shù)據(jù)的一種邏輯結(jié)構(gòu)。表由行和列組成，因此也稱為二維表。表是在日常工作和生活中經(jīng)常使用的一種表示數(shù)據(jù)及其關系的形式，表3.1就是用來表示學生情況的一個學生表。,表3.1學生表,3.1.1表和表結(jié)構(gòu),下面簡單介紹與表有關的幾個概念：（1）表結(jié)構(gòu)。組成表的各列的名稱及數(shù)據(jù)類型，統(tǒng)稱為表結(jié)構(gòu)。（2）記錄。每個表包含了若干行數(shù)據(jù)，它們是表的

2、“值”，表中的一行稱為一個記錄。因此，表是記錄的有限集合。（3）字段。每個記錄由若干個數(shù)據(jù)項構(gòu)成，將構(gòu)成記錄的每個數(shù)據(jù)項稱為字段。例如，表3.1中表結(jié)構(gòu)為（學號，姓名，性別，出生時間，專業(yè)，總學分，備注），包含7個字段，由5個記錄組成。（4）空值?？罩担∟ULL）通常表示未知、不可用或?qū)⒃谝院筇砑拥臄?shù)據(jù)。若一個列允許為空值，則向表中輸入記錄值時可不為該列給出具體值；而一個列若不允許為空值，則在輸入時必須給出具體值。,3.1.1表和表結(jié)構(gòu),（5）關鍵字。若表中記錄的某一字段或字段組合能唯一標識記錄，則稱該字段或字段組合為候選關鍵字（Candidatekey）。若一個表有多個候選關鍵字，則選定其中

3、一個為主關鍵字（Primarykey），也稱為主鍵。當一個表僅有唯一的一個候選關鍵字時，該候選關鍵字就是主關鍵字。這里的主關鍵字與第1章中的主碼所起的作用是相同的，都用來唯一標識記錄行。例如，在學生表中，2個及其以上記錄的姓名、性別、出生時間、專業(yè)、總學分和備注這6個字段的值有可能相同，但是“學號”字段的值對表中所有記錄來說一定不同，即通過“學號”字段可以將表中的不同記錄區(qū)分開來。所以，“學號”字段是唯一的候選關鍵字，學號就是主關鍵字。再例如，學生成績表記錄的候選關鍵字是（學號，課程號）字段組合，它也是唯一的候選關鍵字。,3.1.2數(shù)據(jù)類型,列的數(shù)據(jù)類型可以是SQLServer提供的系統(tǒng)數(shù)據(jù)類

4、型，也可以是用戶定義的數(shù)據(jù)類型。SQLServer2008提供了豐富的系統(tǒng)數(shù)據(jù)類型，現(xiàn)將其列于表3.2中。,表3.2系統(tǒng)數(shù)據(jù)類型表,3.1.2數(shù)據(jù)類型,在討論數(shù)據(jù)類型時，使用了精度、小數(shù)位數(shù)和長度3個概念，前兩個概念是針對數(shù)值型數(shù)據(jù)的，它們的含義如下。精度：指數(shù)值數(shù)據(jù)中所存儲的十進制數(shù)據(jù)的總位數(shù)。小數(shù)位數(shù)：指數(shù)值數(shù)據(jù)中小數(shù)點右邊可以有的數(shù)字位數(shù)的最大值。例如，數(shù)值數(shù)據(jù)3890.587的精度是7，小數(shù)位數(shù)是3。長度：指存儲數(shù)據(jù)所使用的字節(jié)數(shù)。,3.1.2數(shù)據(jù)類型,下面分別說明常用的系統(tǒng)數(shù)據(jù)類型。1整數(shù)型整數(shù)型包括bigint、int、smallint和tinyint，從標識符的含義就可以看出，它

5、們的表示數(shù)范圍逐漸縮小。bigint：大整數(shù)，數(shù)范圍為2632631，其精度為19，小數(shù)位數(shù)為0，長度為8字節(jié)。int：整數(shù)，數(shù)范圍為2312311，其精度為10，小數(shù)位數(shù)為0，長度為4字節(jié)。smallint：短整數(shù)，數(shù)范圍為2152151，其精度為5，小數(shù)位數(shù)為0，長度為2字節(jié)。tinyint：微短整數(shù)，數(shù)范圍為0255，長度為1字節(jié)，其精度為3，小數(shù)位數(shù)為0，長度為1字節(jié)。,3.1.2數(shù)據(jù)類型,2精確數(shù)值型精確數(shù)值型數(shù)據(jù)由整數(shù)部分和小數(shù)部分構(gòu)成，其所有的數(shù)字都是有效位，能夠以完整的精度存儲十進制數(shù)。精確數(shù)值型包括decimal和numeric兩類。在SQLServer2008中，這兩種數(shù)據(jù)

6、類型在功能上完全等價。聲明精確數(shù)值型數(shù)據(jù)的格式是numeric|decimal(p,s)，其中，p為精度，s為小數(shù)位數(shù)，s的默認值為0。例如，指定某列為精確數(shù)值型，精度為6，小數(shù)位數(shù)為3，即decimal(6,3)，那么當向某記錄的該列賦值56.342689時，該列實際存儲的是56.3427。decimal和numeric可存儲1038+110381的固定精度和小數(shù)位的數(shù)字數(shù)據(jù)，它們的存儲長度隨精度變化而變化，最少為5字節(jié)，最多為17字節(jié)。例如，若有聲明numeric(8,3)，則存儲該類型數(shù)據(jù)需5字節(jié)；而若有聲明numeric(22,5)，則存儲該類型數(shù)據(jù)需13字節(jié)。,3.1.2數(shù)據(jù)類型,3

7、浮點型浮點型也稱近似數(shù)值型。顧名思義，這種類型不能提供精確表示數(shù)據(jù)的精度，使用這種類型來存儲某些數(shù)值時，可能會損失一些精度，所以它可用于處理取值范圍非常大且對精確度要求不太高的數(shù)值量，如一些統(tǒng)計量。有兩種近似數(shù)值數(shù)據(jù)類型：float(n)和real，兩者通常都使用科學計數(shù)法表示數(shù)據(jù)，即形為尾數(shù)E階數(shù)，如5.6432E20，2.98E10，1.287659E-9等。real：使用4字節(jié)存儲數(shù)據(jù)，表數(shù)范圍為3.40E+383.40E+38，數(shù)據(jù)精度為7位有效數(shù)字。float：float型數(shù)據(jù)的數(shù)范圍為1.79E+3081.79E+308。定義中的n取值范圍是153，用于指示其精度和存儲大小。當n在

8、124之間時，實際上將定義一個real型數(shù)據(jù)，存儲長度為4字節(jié)，精度為7位有效數(shù)字。當n在2553之間時，存儲長度為8字節(jié)，精度為15位有效數(shù)字。當省略n時，代表n在2553之間。,3.1.2數(shù)據(jù)類型,4貨幣型SQLServer2008提供了兩個專門用于處理貨幣的數(shù)據(jù)類型：money和smallmoney，它們用十進制數(shù)表示貨幣值。money：數(shù)據(jù)的數(shù)范圍為2632631，其精度為19，小數(shù)位數(shù)為4，長度為8字節(jié)。money的數(shù)范圍與bigint相同，不同的只是money型有4位小數(shù)，實際上，money就是按照整數(shù)進行運算的，只是將小數(shù)點固定在末4位。smallmoney：數(shù)范圍為231231

9、1，其精度為10，小數(shù)位數(shù)為4，長度為4字節(jié)?？梢妔mallmoney與int的關系就如同money與bigint的關系一樣。當向表中插入money或smallmoney類型的值時，必須在數(shù)據(jù)前面加上貨幣表示符號（$），并且數(shù)據(jù)中間不能有逗號（,）；若貨幣值為負數(shù)，則需要在符號$的后面加上負號（-）。例如，$15000.32，$680，$-20000.9088都是正確的貨幣數(shù)據(jù)表示形式。,3.1.2數(shù)據(jù)類型,5位型SQLServer2008中的位（bit）型數(shù)據(jù)相當于其他語言中的邏輯型數(shù)據(jù)，它只存儲0和1，長度為1字節(jié)。但要注意，SQLServer對表中bit類型列的存儲進行了優(yōu)化：如果一個表

10、中有不多于8個的bit列，則這些列將作為1字節(jié)存儲；如果表中有916個bit列，則這些列將作為2字節(jié)存儲，更多列的情況以此類推。當為bit類型數(shù)據(jù)賦0時，其值為0，而賦非0（如100）時，其值為1。字符串值TRUE和FALSE可以轉(zhuǎn)換為以下bit值：TRUE轉(zhuǎn)換為1，F(xiàn)ALSE轉(zhuǎn)換為0。,3.1.2數(shù)據(jù)類型,6字符型字符型數(shù)據(jù)用于存儲字符串，字符串中可包括字母、數(shù)字和其他特殊符號（如#、,3.3.1創(chuàng)建表,列的定義格式如下：:=column_namedata_type/*指定列名、類型*/FILESTREAM/*指定FILESTREAM屬性*/COLLATEcollation_name/*指定

11、排序規(guī)則*/NULL|NOTNULL/*指定是否為空*/CONSTRAINTconstraint_nameDEFAULTconstant_expression/*指定默認值*/|IDENTITY(seed,increment)NOTFORREPLICATION/*指定列為標識列*/ROWGUIDCOL/*指定列為全局標識符列*/.n/*指定列的約束*/SPARSE,3.3.1創(chuàng)建表,在開始使用FILESTREAM之前，必須在SQLServer2008數(shù)據(jù)庫引擎實例中啟用FILESTREAM，操作步驟如下。第1步：在SQLServer配置管理器中打開SQLServer2008數(shù)據(jù)庫實例的“屬性”

12、窗口，選擇“FILESTREAM”選項卡，選中“針對Transact-SQL訪問啟用FILESTREAM”復選框，其他的選項是針對Windows進行讀/寫的，可以都選中，如圖3.11所示，然后單擊“確定”按鈕保存對FILESTREAM的設置。,圖3.11啟用FILESTREAM,3.3.1創(chuàng)建表,第2步：打開SSMS，在對象資源管理器中右擊已連接的服務器，選擇“屬性”菜單項。在“服務器屬性”窗口中選擇“高級”選項卡，在“文件流訪問級別”選項后的下拉列表框中選擇“已啟用完全訪問”選項，如圖3.12所示。單擊“確定”按鈕后在SQLServer配置管理器中重啟SQLServer服務，F(xiàn)ILESTRE

13、AM在數(shù)據(jù)庫實例中的設置即可完成。,圖3.12設置文件流訪問級別,3.3.1創(chuàng)建表,完成以上步驟后數(shù)據(jù)庫實例即啟用了FILESTREAM，接下來就可以創(chuàng)建FILESTREAM文件組和具有FILESTREAM數(shù)據(jù)列的表了。在創(chuàng)建了FILESTREAM數(shù)據(jù)列后，訪問的方法與訪問一般varbinary(MAX)列的方式相同。NULL|NOTNULL：NULL表示列可取空值，NOTNULL表示列不可取空值。DEFAULTconstant_expression：為所在列指定默認值，默認值constant_expression必須是一個常量值、標量函數(shù)或NULL值。DEFAULT定義可適用于除定義為tim

14、estamp或帶identity屬性的列以外的任何列。IDENTITY：指出該列為標識符列，為該列提供一個唯一的、遞增的值。seed是標識字段的起始值，默認值為1，increment是標識增量，默認值為1。如果為IDENTITY屬性指定了NOTFORREPLICATION選項，則復制代理執(zhí)行插入時，標識列中的值將不會增加。,3.3.1創(chuàng)建表,：列的完整性約束，指定主鍵、替代鍵、外鍵等。例如，指定該列為主鍵則使用PRIMARYKEY關鍵字。：表的完整性約束，有關列的約束和表的約束將在第6章中介紹。column_nameAScomputed_column_expression：用于定義計算字段，計

15、算字段是由同一表中的其他字段通過表達式計算得到的。其中，column_name為計算字段的列名，computed_column_expression是表其他字段的表達式，表達式可以是非計算字段的字段名、常量、函數(shù)、變量，也可以是一個或多個運算符連接的上述元素的任意組合。系統(tǒng)不將計算列中的數(shù)據(jù)進行物理存儲，該列只是一個虛擬列。如果需要將該列的數(shù)據(jù)物理化，則需要使用PERSISTED關鍵字。ON子句：filegroup|“default”指定存儲表的文件組。如果指定了filegroup，則表將存儲在指定的文件組中，數(shù)據(jù)庫中必須存在該文件組。如果指定“default”，或者未指定ON參數(shù)，則表存儲在

16、默認文件組中。partition_scheme_name(partition_column_name)指定分區(qū)方案來創(chuàng)建分區(qū)表，partition_scheme_name是分區(qū)方案的名稱，partition_column_name是表中的分區(qū)列。,3.3.1創(chuàng)建表,TEXTIMAGE_ONfilegroup|default：TEXTIMAGE_ON表示將text、ntext、image、xml、varchar(MAX)、nvarchar(MAX)、varbinary(MAX)和CLR用戶定義類型的列存儲在指定文件組中。如果表中沒有這些類型的列，則不能使用TEXTIMAGE_ON。如果沒有指定T

17、EXTIMAGE_ON或指定了default，則這些列將與表存儲在同一文件組中。FILESTREAM_ON子句：filegroup|default指定存儲FILESTREAM數(shù)據(jù)的文件組。如果表包含F(xiàn)ILESTREAM數(shù)據(jù)并且已分區(qū)，則必須包含F(xiàn)ILESTREAM_ON子句并指定FILESTREAM文件組的分區(qū)方案partition_scheme_name。,3.3.1創(chuàng)建表,【例3.5】設已經(jīng)創(chuàng)建了數(shù)據(jù)庫PXSCJ，現(xiàn)在該數(shù)據(jù)庫中需創(chuàng)建學生情況表XSB，該表的結(jié)構(gòu)見表3.3。創(chuàng)建表XSB的T-SQL語句如下：USEPXSCJGOCREATETABLEXSB(學號char(6)NOTNULLP

18、RIMARYKEY,姓名char(8)NOTNULL,性別bitNULLDEFAULT1,出生時間dateNULL,專業(yè)char(12)NULL,總學分intNULL,備注varchar(500)NULL),3.3.1創(chuàng)建表,【例3.6】創(chuàng)建一個帶計算列的表，表中包含課程的課程號、總成績和學習該課程的人數(shù)，以及課程的平均成績。CREATETABLEPJCJ(課程號char(3)PRIMARYKEY,總成績realNOTNULL,人數(shù)intNOTNULL,平均成績AS總成績/人數(shù)PERSISTED),3.3.2創(chuàng)建分區(qū)表,1使用T-SQL語句創(chuàng)建分區(qū)表（1）創(chuàng)建分區(qū)函數(shù)。創(chuàng)建分區(qū)函數(shù)使用CREA

19、TEPARTITIONFUNCTION命令，語法格式如下。CREATEPARTITIONFUNCTIONpartition_function_name(input_parameter_type)ASRANGELEFT|RIGHTFORVALUES(boundary_value,.n);【例3.7】對int類型的列創(chuàng)建一個名為NumberPF的分區(qū)函數(shù)，該函數(shù)把int類型的列中數(shù)據(jù)分成5個區(qū)。分為小于或等于50的區(qū)、大于50且小于或等于500的區(qū)、大于500且小于或等于1000的區(qū)、大于1000且小于或等于2000的區(qū)、大于2000的區(qū)。CREATEPARTITIONFUNCTIONNumber

20、PF(int)ASRANGELEFTFORVALUES(50,500,1000,2000)GO,3.3.2創(chuàng)建分區(qū)表,（2）創(chuàng)建分區(qū)方案。分區(qū)函數(shù)創(chuàng)建完后可以使用CREATEPARTITIONSCHEME命令創(chuàng)建分區(qū)方案，由于在創(chuàng)建分區(qū)方案時需要根據(jù)分區(qū)函數(shù)的參數(shù)定義映射分區(qū)的文件組。所以需要有文件組來容納分區(qū)數(shù)，文件組可以由一個或多個文件構(gòu)成，而每個分區(qū)必須映射到一個文件組。一個文件組可以由多個分區(qū)使用。在一般情況下，文件組數(shù)最好與分區(qū)數(shù)相同，并且這些文件組通常位于不同的磁盤上。一個分區(qū)方案只可以使用一個分區(qū)函數(shù)，而一個分區(qū)函數(shù)可以用于多個分區(qū)方案中。CREATEPARTITIONSCHEM

21、E命令的語法格式如下。CREATEPARTITIONSCHEMEpartition_scheme_nameASPARTITIONpartition_function_nameALLTO(file_group_name|PRIMARY,.n);,3.3.2創(chuàng)建分區(qū)表,【例3.8】假設文件組FGroup1、FGroup2、FGroup3、FGroup4、FGroup5已經(jīng)在數(shù)據(jù)庫PXSCJ中存在。根據(jù)例3.7中定義的分區(qū)函數(shù)創(chuàng)建一個分區(qū)方案，將分區(qū)函數(shù)中的5個分區(qū)分別存放在這5個文件組中。CREATEPARTITIONSCHEMENumberPSASPARTITIONNumberPFTO(FGro

22、up1,FGroup2,FGroup3,FGroup4,FGroup5)GO,3.3.2創(chuàng)建分區(qū)表,（3）使用分區(qū)方案創(chuàng)建分區(qū)表。分區(qū)函數(shù)和分區(qū)方案創(chuàng)建以后就可以創(chuàng)建分區(qū)表了。創(chuàng)建分區(qū)表使用CREATETABLE語句，只要在ON關鍵字后指定分區(qū)方案和分區(qū)列即可?！纠?.9】在數(shù)據(jù)庫PXSCJ中創(chuàng)建分區(qū)表，表中包含“編號”（值可以是15000）、“名稱”兩列，要求使用例3.8中的分區(qū)方案。USEPXSCJCREATETABLEsample(編號intNOTNULLPRIMARYKEY,名稱char(8)NOTNULL)ONNumberPS(編號)GO,3.3.2創(chuàng)建分區(qū)表,2使用圖形向?qū)Х绞絼?chuàng)建

23、分區(qū)表【例3.10】使用圖形向?qū)Х绞綄XSCJ數(shù)據(jù)庫中的CJB表根據(jù)“成績”列分區(qū)，成績值為0100，根據(jù)成績值分為5個區(qū)：059，6069，7079，8189，90100。操作步驟如下。第1步：啟動SQLServerManagementStudio，在對象資源管理器中展開“數(shù)據(jù)庫”，右擊“PXSCJ”數(shù)據(jù)庫菜單下的“表”選項，右擊表“dbo.CJB”選擇“存儲”菜單項，單擊“創(chuàng)建分區(qū)”子菜單項，進入分區(qū)向?qū)Т翱?，單擊“下一步”按鈕，進入“選擇分區(qū)列”界面，界面中將顯示可用的分區(qū)列，選擇“成績”列，如圖3.13所示。,圖3.13選擇分區(qū)列,3.3.2創(chuàng)建分區(qū)表,第2步：單擊“下一步”按鈕，進

24、入“選擇分區(qū)函數(shù)”界面。選擇“新建分區(qū)函數(shù)”選項，填寫要新建的分區(qū)函數(shù)的名稱，如PointsPF，如圖3.14所示。當然，也可以選擇現(xiàn)有的分區(qū)函數(shù)。,圖3.14選擇分區(qū)函數(shù),3.3.2創(chuàng)建分區(qū)表,第3步：單擊“下一步”按鈕，進入“選擇分區(qū)方案”界面。填寫要新建的分區(qū)方案的名稱，如PointsPS，如圖3.15所示，單擊“下一步”按鈕。,圖3.15選擇分區(qū)方案,3.3.2創(chuàng)建分區(qū)表,第4步：進入“映射分區(qū)”界面，在界面中選擇各個分區(qū)要映射到的文件組，如FGroup1、FGroup2等。選擇單選按鈕“左邊界”，在“52GO【例3.19】將PXSCJ數(shù)據(jù)庫的XSB表中備注為空的行刪除（實際不做操作）

25、：DELETEFROMXSBWHERE備注ISNULL刪除PXSCJ數(shù)據(jù)庫的XSB表中的所有行（實際不做操作）：DELETEXSB,3.5.2刪除記錄,2使用TRUNCATETABLE語句刪除表數(shù)據(jù)使用TRUNCATETABLE語句將刪除指定表中的所有數(shù)據(jù)，因此也稱為清除表數(shù)據(jù)語句。語法格式：TRUNCATETABLEtb_name使用TRUNCATETABLE語句刪除了指定表中的所有行，但表的結(jié)構(gòu)及其列、約束、索引等保持不變，而新行標識所用的計數(shù)值重置為該列的初始值。如果要保留標識計數(shù)值，則要使用DELETE語句。TRUNCATETABLE在功能上與不帶WHERE子句的DELETE語句相同，

26、二者均刪除表中的全部行；但TRUNCATETABLE比DELETE速度快，且使用的系統(tǒng)和事務日志資源少。DELETE語句每次刪除一行，并在事務日志中為所刪除的每一行記錄一項。而TRUNCATETABLE通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務日志中記錄頁的釋放。,3.5.3修改記錄,在T-SQL中，UPDATE語句可以用來修改表中的數(shù)據(jù)行。語法格式。據(jù)窗口，可以發(fā)現(xiàn)表中學號為“081101”的行的備注字段值已被修改，如圖3.21所示。,圖3.21修改數(shù)據(jù)以后的表,3.5.3修改記錄,【例3.21】將XSB表（數(shù)據(jù)以附錄A中XSB表的樣本數(shù)據(jù)為準）中所有學生的總學分都增加10。將姓名為“羅林琳”的同學的專業(yè)改為“軟件工程”，備注改為“提前修完學分”，學號改為081261。USEPXSCJGOUPDATEXSBSET總學分=總學分+10GOUPDATEXSBSET專業(yè)=軟件工程,備注=提前修完學分,學號=0812