第5章 數(shù)據(jù)表的管理(一)

1、1主講教師 毛一梅2010.012數(shù)據(jù)庫表管理n數(shù)據(jù)類型n數(shù)據(jù)表結(jié)構(gòu)的創(chuàng)建n數(shù)據(jù)表結(jié)構(gòu)的修改與刪除n數(shù)據(jù)表內(nèi)容的增加與修改n數(shù)據(jù)表的索引3一、數(shù)據(jù)類型n系統(tǒng)數(shù)據(jù)類型n用戶自定義數(shù)據(jù)類型4系統(tǒng)數(shù)據(jù)類型n二進(jìn)制數(shù)據(jù)： Binary、Varbinary 和 Imagen字符數(shù)據(jù)：Char、Varchar 和 TextnUnicode 數(shù)據(jù)：Nchar，Nvarchar 和Ntext n日期和時(shí)間數(shù)據(jù)：Datetime 和 Smalldatetimen數(shù)字?jǐn)?shù)據(jù)：精確數(shù)字、近似數(shù)字5n精確數(shù)字整數(shù)：bigint、int、smallint、tinyint、bit小數(shù)：decimal 和 numeric 貨

2、幣：money和smallmoneyn近似數(shù)字float、real6用戶定義的數(shù)據(jù)類型 n用戶定義的數(shù)據(jù)類型基于在 Microsoft SQL Server 中提供的數(shù)據(jù)類型。n當(dāng)幾個(gè)表中必須存儲(chǔ)同一種數(shù)據(jù)類型時(shí)，并且為保證這些列有相同的數(shù)據(jù)類型、長度和可空性時(shí)，可以使用用戶定義的數(shù)據(jù)類型。7n創(chuàng)建用戶定義的數(shù)據(jù)類型可以使用 系統(tǒng)存儲(chǔ)過程 sp_addtype 。其語法形式如下： exec sp_addtype type,system_data_bype, null_type其中，type 是用戶定義的數(shù)據(jù)類型的名稱。system_data_type 是系統(tǒng)提供的數(shù)據(jù)類型，例如 Decimal

3、、Int、Char 等。null_type 表示該數(shù)據(jù)類型是如何處理空值的，必須使用單引號(hào)引起來，如NULL、NOT NULL等。8例1n為數(shù)據(jù)庫teachingDataA創(chuàng)建一個(gè)用戶定義的數(shù)據(jù)類型Sname，該數(shù)據(jù)類型為基于系統(tǒng)數(shù)據(jù)類型變長為8的字符，且不允許為空。 Exec sp_addtype Sname, Varchar(8),Not Null9用CREATE TYPE定義數(shù)據(jù)類型n在SQL-Server 2005之后的后續(xù)版本中，sp_addtype將不再可用，取而代之的是CREATE TYPE語句。其語法方式如下：CREATE TYPE type_nameFROM base_typ

4、e ( precision , scale ) NULL | NOT NULL 其中，typename：用戶自定義的數(shù)據(jù)類型的名稱。base_type：表示建立的數(shù)據(jù)類型所基于的由SQL Server提供的數(shù)據(jù)類型。當(dāng)建立decimal或numeric類型時(shí)，需要用precision來指定總位數(shù)，用scale來指定小數(shù)位數(shù)。NULL | NOT NULL：指定此類型是否可容納空值。如果未指定，則默認(rèn)為NULL。 10例2為數(shù)據(jù)庫teachingDataB自定義一個(gè)工作部門myDept的數(shù)據(jù)類型為varchar(20)，允許為空。n方法一CREATE TYPE myDept FROM varch

5、ar(20) n方法二在Microsoft SQL Server Management Studio的對(duì)象資料管理器中展開【可編程性】|【類型】，右擊【用戶定義數(shù)據(jù)類型】，選擇【新建用戶定義數(shù)據(jù)類型】命令，打開【新建用戶定義數(shù)據(jù)類型】，輸入自定義的數(shù)據(jù)類型名，選擇數(shù)據(jù)類型，設(shè)置長度設(shè)定，確定是否允許空值（通過勾選【允許空值】進(jìn)行設(shè)置），完成后單擊【確定】。 11n當(dāng)用戶定義的數(shù)據(jù)類型不需要時(shí)，可以用命令sp_droptype將其刪除。其語法形式如下： exec sp_droptype type n也可以在Microsoft SQL Server Management Studio的對(duì)象資料管理

6、器中展開【可編程性】|【類型】|【用戶定義數(shù)據(jù)類型】，右擊要?jiǎng)h除的數(shù)據(jù)類型，選擇“刪除”12二、表結(jié)構(gòu)的創(chuàng)建n使用菜單命令創(chuàng)建n使用命令方式創(chuàng)建n數(shù)據(jù)表的導(dǎo)入13（1）使用菜單命令創(chuàng)建n啟動(dòng)“Microsoft SQL Server Management Studio”，在“對(duì)象資源管理器”窗口中展開【數(shù)據(jù)庫】文件夾，展開要建立表結(jié)構(gòu)的數(shù)據(jù)庫，右擊【表】，選擇【新建表】命令，打開表設(shè)計(jì)器窗口。14列屬性n選擇不同的數(shù)據(jù)類型會(huì)有不同的列屬性，常用的列屬性說明如下：【說明】是用來說明該字段的含義?！灸J(rèn)值或綁定】在表中輸入新的記錄值時(shí)，如果沒有給該字段賦值，則默認(rèn)值就是該字段的值?！揪取渴怯脕碚f

7、明該字段數(shù)據(jù)類型的寬度，即位數(shù)?！拘?shù)位數(shù)】是用來說明該字段數(shù)據(jù)類型的小數(shù)位數(shù)?！臼菢?biāo)識(shí)】表示該字段是表中的一個(gè)標(biāo)識(shí)列，即新增的字段值為等差數(shù)列，其類型必須為數(shù)據(jù)值數(shù)據(jù)，有此屬性的字段會(huì)自動(dòng)產(chǎn)生一個(gè)值，無需用戶輸入?！緲?biāo)識(shí)種子】等差數(shù)列的第一個(gè)數(shù)字?！緲?biāo)識(shí)增量】等差數(shù)列的公差。15例3n在teachDataA中新建一數(shù)據(jù)表tchInfo，其表結(jié)構(gòu)如下圖所示：16常用的設(shè)置n關(guān)鍵字的設(shè)置右擊要設(shè)置為關(guān)鍵字的字段左側(cè)（如果要設(shè)置一組關(guān)鍵字，則可以用Ctrl選中多個(gè)字段），選擇“設(shè)置關(guān)鍵字”。n自動(dòng)編號(hào)的設(shè)置-必須是int類型的數(shù)據(jù)在列屬性中設(shè)置“標(biāo)識(shí)規(guī)范”（“是標(biāo)識(shí)”、“標(biāo)識(shí)種子”、“標(biāo)識(shí)增量”）

8、n默認(rèn)值的設(shè)置在列屬性中設(shè)置“默認(rèn)值或綁定”17n數(shù)據(jù)取值范圍的約束右擊相應(yīng)的字段選擇“CHECK約束”n設(shè)置為惟一索引右擊相應(yīng)的字段選擇“索引/鍵”18例4n小黃在建立了數(shù)據(jù)表TchInfo以后，為了輸入數(shù)據(jù)方便起見，要為其中的性別一欄即字段“sex”設(shè)置一個(gè)默認(rèn)值“男”，并且，考慮到信息輸入的正確性，他希望字段“sex”的值要么為“男”，要么為“女”，不允許有其它值。問：他應(yīng)該如何操作？19步驟1：n在Microsoft SQL Server Management Studio的【對(duì)象資源管理器】中展開數(shù)據(jù)庫teachingData，右擊表dbo.TchInfo，以彈出的快捷菜單中選擇【修

9、改】命令，設(shè)置字段“Sex”的列屬性【默認(rèn)值或綁定】為：男，如圖所示。 20步驟2右擊列名“Sex”，在彈出的快捷菜單中選擇【CHECK約束】命令，在【CHECK約束】對(duì)話框中單【添加】按鈕，然后輸入表達(dá)式：sex=男 or sex=女，如下圖所示。21問題：小黃為了輸入方便，希望在輸入過程中教師編號(hào)TID的起始數(shù)據(jù)庫為10000001，以后每輸入一個(gè)教師，其編號(hào)TID自動(dòng)加1，因此他根據(jù)所學(xué)知識(shí)，在Microsoft SQL Server Management Studio窗口中的列屬性中展開【標(biāo)識(shí)規(guī)范】試圖修改【（是標(biāo)識(shí)）】的值為“是”，但發(fā)現(xiàn)這一欄是灰色的，他無法進(jìn)行設(shè)置，為什么？應(yīng)該如

10、何操作才能符合他的要求？解決方案：【標(biāo)識(shí)規(guī)范】只對(duì)整型數(shù)據(jù)有效，所以小黃應(yīng)該先將教師編號(hào)TID的數(shù)據(jù)類型設(shè)置為int類，然后再展開【標(biāo)識(shí)規(guī)范】，修改【（是標(biāo)識(shí)）】值為“是”，【標(biāo)識(shí)種子】為“10000001”、 【標(biāo)識(shí)增量】為“1”22（2）使用命令CREATE TABLE創(chuàng)建表結(jié)構(gòu)創(chuàng)建表結(jié)構(gòu)的語法形式為：CREATE TABLE Table_name （column_name1 data_type NULL | NOT NULL PRIMARY KEY| UNIQUEFOREIGN KEY (column_name) REFERENCES ref_table(ref_column) ，col

11、umn_name2 data_type )其中，Table_name是要?jiǎng)?chuàng)建表的名稱，column_name1表示第一個(gè)字段的名稱，依次類推，data_type用于指定字段的數(shù)據(jù)類型，NULL或NOT NULL用來限制字段是否允許取空值，PRIMARY KEY設(shè)置字段為主鍵，UNIQUE設(shè)置取值的唯一性。 23例5 在teachingDataB中建立StuInfo表（學(xué)生信息表）CREATE TABLE StuInfo (SID char(8) primary key, -將SID設(shè)置為主鍵 Sname char(10) NOT NULL,- Sname不允許取空值Sex char(2) de

12、fault 男 check(sex=男 or sex=女), BirthDay smalldatetime NULL,Dept char(20) NULL,Major char(20) NULL,Class char(10) NULL,Grade char(10) NULL,Sage int )24例6在teachingDataB中建立TchInfo表（教師信息表）CREATE TABLE TchInfo( TID char(8) primary key, -設(shè)置主鍵TName char(10) NOT NULL,Sex char(2) NULL,BirthDay smalldatetime

13、NULL,Title nchar(10) NULL,Dept char(20) NULL)25例7在teachingDataB中建立CourseInfo表（選課信息表）CREATE TABLE CourseInfo( CID char(8) primary key, CName char(20) NOT NULL,CCredit tinyint NULL,CProperty char(10) NULL )26例8在teachingDataB中建立ScoreInfo表（學(xué)生成績表）CREATE TABLE ScoreInfo( CID char(8) NOT NULL, - 不允許取空值SID

14、char(8) NOT NULL, - 不允許取空值TID char(8) NULL,Score numeric(3, 0) NULL,Schyear char(9) NULL,Term char(1) NULL, FOREIGN KEY(CID) REFERENCES CourseInfo(CID),/* 表級(jí)完整性約束條件，CID是外碼，被參照表是CourseInfo */ FOREIGN KEY(SID)REFERENCES StuInfo(SID),/* 表級(jí)完整性約束條件，SID是外碼，被參照表是StuInfo */FOREIGN KEY(TID)REFERENCES TchInfo

15、(TID),/* 表級(jí)完整性約束條件，TID是外碼，被參照表是TchInfo */Constraint scoInfo_prim PRIMARY KEY(CID,SID)/*表級(jí)完整性約束條件， 設(shè)置組合主鍵*/27說明：(1)在創(chuàng)建ScoreInfo表時(shí)，由于要將CID字段關(guān)聯(lián)CourseInfo，因此CourseInfo表必須要存在才行，這就要求在建表時(shí)先要?jiǎng)?chuàng)建ScoreInfo表。同樣，StuInfo表和TchInfo也要在ScoreInfo表之前創(chuàng)建。(2)將兩個(gè)字段組合起來作為主鍵，只能采用表級(jí)完整性進(jìn)行定義。28三、表結(jié)構(gòu)的修改與刪除n利用菜單命令實(shí)現(xiàn)n使用Transact-SQL

16、語句來實(shí)現(xiàn) 29（1）利用菜單命令實(shí)現(xiàn)表結(jié)構(gòu)的修改和刪除步驟步驟1：在：在Microsoft SQL Server Management Studio 的的“對(duì)象資源管理器對(duì)象資源管理器”窗口中展開相應(yīng)的數(shù)據(jù)窗口中展開相應(yīng)的數(shù)據(jù)庫和表，右擊需要修改的數(shù)據(jù)表（如庫和表，右擊需要修改的數(shù)據(jù)表（如StuInfo表），表），然后選擇【修改】命令，打開表設(shè)計(jì)器窗口。然后選擇【修改】命令，打開表設(shè)計(jì)器窗口。步驟步驟2：在表設(shè)計(jì)器窗口中，可以直接對(duì)已有的字段進(jìn)：在表設(shè)計(jì)器窗口中，可以直接對(duì)已有的字段進(jìn)行修改，修改列名、數(shù)據(jù)類型等。也可以右擊某個(gè)字行修改，修改列名、數(shù)據(jù)類型等。也可以右擊某個(gè)字段，如段，如“S

17、ex”，在屬性菜單中，可以選擇插入列和刪，在屬性菜單中，可以選擇插入列和刪除列，插入列的操作與建表結(jié)構(gòu)時(shí)的操作是一樣的。除列，插入列的操作與建表結(jié)構(gòu)時(shí)的操作是一樣的。步驟步驟3：表中字段修改完成后，單擊工具欄上的【保存】：表中字段修改完成后，單擊工具欄上的【保存】按鈕，即可完成表結(jié)構(gòu)的修改。按鈕，即可完成表結(jié)構(gòu)的修改。 30表的刪除可以在可以在Microsoft SQL Server Management Studio 的的“對(duì)象資源管理器對(duì)象資源管理器”窗口中展開相應(yīng)窗口中展開相應(yīng)的數(shù)據(jù)庫和表，右擊需要?jiǎng)h除的數(shù)據(jù)表，然后的數(shù)據(jù)庫和表，右擊需要?jiǎng)h除的數(shù)據(jù)表，然后選擇【刪除】命令即可刪除相應(yīng)的數(shù)

18、據(jù)表。選擇【刪除】命令即可刪除相應(yīng)的數(shù)據(jù)表。31（2）用命令語句修改表結(jié)構(gòu)ALTER TABLE Table_nameADD column_name data_type NULL | NOT NULL PRIMARY KEY| UNIQUEFOREIGN KEY (column_name) REFERENCES ref_table(ref_column) DROP CONSTRAINT constraint_name | column column_name ALTER COLUMN ；雖然從語法格式上來看比較復(fù)雜，但實(shí)際上該命令語句一次只能雖然從語法格式上來看比較復(fù)雜，但實(shí)際上該命令語句一次

19、只能修改一個(gè)參數(shù)。修改一個(gè)參數(shù)。 32例例9向向StuInfo表（學(xué)生信息表）中增加表（學(xué)生信息表）中增加“家庭住家庭住址（址（Saddr）”列，其數(shù)據(jù)類型為字符型。列，其數(shù)據(jù)類型為字符型。 ALTER TABLE StuInfo ADD Saddr varchar(20)不論基本表中原來是否已有數(shù)據(jù)，新增加的不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。列一律為空值。33例例10將表將表StuInfo中的年齡字段中的年齡字段Sage的數(shù)據(jù)類型由的數(shù)據(jù)類型由int改為改為tinyint，設(shè)置其默認(rèn)值為，設(shè)置其默認(rèn)值為20，并規(guī)定年齡必，并規(guī)定年齡必須在須在10-60之間。之間。ALTER

20、 TABLE StuInfo ALTER COLUMN Sage tinyintALTER TABLE StuInfo ADD CONSTRAINT Defa_age DEFAULT 20 FOR SageALTER TABLE StuInfo ADD CONSTRAINT CK_age CHECK(Sage10 AND Sage60)34例例11增加學(xué)生姓名必須取唯一值的約束條件。增加學(xué)生姓名必須取唯一值的約束條件。ALTER TABLE StuInfo ADD UNIQUE(Sname)35例例12刪除刪除StuInfo表（學(xué)生信息表）中屬性列表（學(xué)生信息表）中屬性列“家庭住址（家庭住址（

21、Saddr）”。 ALTER TABLE StuInfo Drop column Saddr36例13修改表tchInfo結(jié)構(gòu)，要求字段TID只能為數(shù)字。ALTER TABLE tchInfo ADD CONSTRAINT CK_TID CHECK(TID IN(00,000,000-99,999,999)37刪除表可以可以使用DROP TABLE命令來刪除表。最簡(jiǎn)單也是最常用的形式為：DROP TABLE table_name例如，要?jiǎng)h除ScoreInfo表，則可以在查詢編輯器中輸入命令：DROP TABLE ScoreInfo；38n但有時(shí)直接刪除基本表，會(huì)出現(xiàn)誤刪的情況，比如，我們已經(jīng)給

22、這個(gè)表建立的索引、視圖、觸發(fā)器等，而它們都還有用，這時(shí)我們就不能隨意刪除基本表。這種情況要用參數(shù)RESTRICT或 CASCADE來進(jìn)行處理。完整的語句格式為：DROP TABLE RESTRICT| CASCADE；RESTRICT：刪除表是有限制的，它不能被其他表的約束所引用，如果存在依賴該表的對(duì)象，則此表不能被刪除；CASCADE：刪除該表沒有限制，在刪除基本表的同時(shí)，相關(guān)的依賴對(duì)象一起刪除。 39n例如，刪除ScoreInfo表（含相關(guān)的索引、視圖、觸發(fā)器等），則可以在查詢編輯器中輸入命令： DROP TABLE ScoreInfo CASCADE；40刪除字段命令格式：ALTER T

23、ABLE table_nameDROP COLUMN column_name41例14刪除表StuInfo中的字段grade,其命令語句如下：ALTER TABLE StuInfoDROP COLUMN grade42四、數(shù)據(jù)表內(nèi)容的增加與修改n表數(shù)據(jù)的操作主要有三種：插入數(shù)據(jù)修改數(shù)據(jù)刪除數(shù)據(jù) 431、插入數(shù)據(jù)n使用菜單命令來完成n用Transact-SQL的標(biāo)準(zhǔn)命令I(lǐng)NSERT語句來完成 44n使用Microsoft SQL Server Management Studio可以在“對(duì)象資源管理器”中右擊需要添加數(shù)據(jù)的表選擇“打開表”命令，然后直接在表中輸入數(shù)據(jù)。 45插入數(shù)據(jù)命令n格式：IN

24、SERT INTO (column_list) VALUES (data_values)n功能：將新元組插入到指定表中。對(duì)于INTO子句中沒有出現(xiàn)的屬性列，新記錄在這些列上將取空值。n注意: 在表定義時(shí)說明了為NOT NULL的屬性列不能取空值，否則會(huì)出錯(cuò)。如果INTO子句中沒有指明屬性列，則新插入的記錄必須在每個(gè)屬性列上均有值。屬性列的順序可與表定義中的順序不一致?？梢灾恢付ú糠謱傩粤?未指定屬性列取值為空)。VALUES子句對(duì)新元組的各個(gè)屬性賦值，字符串常數(shù)要用單引號(hào)括起來。VALUES子句提供的值必須與INTO子句匹配，即值的個(gè)數(shù)相同，值的類型一致。46例15將一個(gè)課程信息元組（課程號(hào)：

25、00000001；課程名：高等數(shù)學(xué)；學(xué)分：3；課程類別：基礎(chǔ)課）插入到CourseInfo表中。INSERT INTO CourseInfo (CID,CName,CCredit,CProperty) VALUES (00000001,高等數(shù)學(xué),3,基礎(chǔ)課);INSERT INTO CourseInfo (CID,CName,CProperty,CCredit) VALUES (00000001，高等數(shù)學(xué)，基礎(chǔ)課，3)INSERT INTO CourseInfo VALUES (00000001,高等數(shù)學(xué),3,基礎(chǔ)課)47如果要想向表中插入成批數(shù)據(jù)，用INSERT- VALUES來解決顯然是極

26、為不方便的。在這種情況下用INSERT- SELECT語句就非常有效。它可以將一個(gè)或一組表中的數(shù)據(jù)插入到另外一個(gè)表中。插入子查詢結(jié)果的語句格式：INSERT INTO (column_list) SELECT sub-sentencen說明: (1) INTO子句與插入元組時(shí)的要求類似；子句與插入元組時(shí)的要求類似；(2）SELECT子句目標(biāo)列必須與子句目標(biāo)列必須與INTO子句匹配，即值的個(gè)數(shù)和值子句匹配，即值的個(gè)數(shù)和值的類型均要求一致。的類型均要求一致。48例16對(duì)每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。對(duì)每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。第一步：在teachingDat

27、aB數(shù)據(jù)庫中建一個(gè)新表，其中一列用于保存學(xué)年信息，另一列用于保存相應(yīng)學(xué)年的學(xué)生平均成績。 CREATE TABLE Schyear_Score (Schyear CHAR(9), /* 學(xué)年*/ Avg_Score SMALLINT)； /*學(xué)年平均成績*/ 第二步：對(duì)ScoreInfo表按Schyear分組，把學(xué)年和學(xué)生的平均成績成績插入到新表Schyear_Score中。INSERT INTO Schyear_Score(Schyear,Avg_score) SELECT Schyear,AVG(Score) FROM ScoreInfo GROUP BY Schyear 492、修改數(shù)據(jù)格式： UPDATE SET =，=, WHERE ；功能： 修改指定table_name表中滿足WHERE子句條件的記錄。說明：SET子句用于指定