1、1主講教師 毛一梅2010.012數(shù)據(jù)庫(kù)表管理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ù)類型、長(zhǎng)度和可空性時(shí)，可以使用用戶定義的數(shù)據(jù)類型。7n創(chuàng)建用戶定義的數(shù)據(jù)類型可以使用 系統(tǒng)存儲(chǔ)過(guò)程 sp_addtype 。其語(yǔ)法形式如下： 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)引起來(lái)，如NULL、NOT NULL等。8例1n為數(shù)據(jù)庫(kù)teachingDataA創(chuàng)建一個(gè)用戶定義的數(shù)據(jù)類型Sname，該數(shù)據(jù)類型為基于系統(tǒng)數(shù)據(jù)類型變長(zhǎng)為8的字符，且不允許為空。 Exec sp_addtype Sname, Varchar(8),Not Null9用CREATE TYPE定義數(shù)據(jù)類型n在SQL-Server 2005之后的后續(xù)版本中，sp_addtype將不再可用，取而代之的是CREATE TYPE語(yǔ)句。其語(yǔ)法方式如下：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來(lái)指定總位數(shù)，用scale來(lái)指定小數(shù)位數(shù)。NULL | NOT NULL：指定此類型是否可容納空值。如果未指定，則默認(rèn)為NULL。 10例2為數(shù)據(jù)庫(kù)teachingDataB自定義一個(gè)工作部門myDept的數(shù)據(jù)類型為varchar(20)，允許為空。n方法一CREATE TYPE myDept FROM varch

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

6、器中展開(kāi)【可編程性】|【類型】|【用戶定義數(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ì)象資源管理器”窗口中展開(kāi)【數(shù)據(jù)庫(kù)】文件夾，展開(kāi)要建立表結(jié)構(gòu)的數(shù)據(jù)庫(kù)，右擊【表】，選擇【新建表】命令，打開(kāi)表設(shè)計(jì)器窗口。14列屬性n選擇不同的數(shù)據(jù)類型會(huì)有不同的列屬性，常用的列屬性說(shuō)明如下：【說(shuō)明】是用來(lái)說(shuō)明該字段的含義。【默認(rèn)值或綁定】在表中輸入新的記錄值時(shí)，如果沒(méi)有給該字段賦值，則默認(rèn)值就是該字段的值。【精度】是用來(lái)說(shuō)

7、明該字段數(shù)據(jù)類型的寬度，即位數(shù)。【小數(shù)位數(shù)】是用來(lái)說(shuō)明該字段數(shù)據(jù)類型的小數(shù)位數(shù)。【是標(biāo)識(shí)】表示該字段是表中的一個(gè)標(biāo)識(shí)列，即新增的字段值為等差數(shù)列，其類型必須為數(shù)據(jù)值數(shù)據(jù)，有此屬性的字段會(huì)自動(dòng)產(chǎn)生一個(gè)值，無(wú)需用戶輸入。【標(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ù)方便起見(jiàn)，要為其中的性別一欄即字段“sex”設(shè)置一個(gè)默認(rèn)值“男”，并且，考慮到信息輸入的正確性，他希望字段“sex”的值要么為“男”，要么為“女”，不允許有其它值。問(wèn)：他應(yīng)該如何操作？19步驟1：n在Microsoft SQL Server Management Studio的【對(duì)象資源管理器】中展開(kāi)數(shù)據(jù)庫(kù)teachingData，右擊表dbo.TchInfo，以彈出的快捷菜單中選擇【修

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

10、何操作才能符合他的要求？解決方案：【標(biāo)識(shí)規(guī)范】只對(duì)整型數(shù)據(jù)有效，所以小黃應(yīng)該先將教師編號(hào)TID的數(shù)據(jù)類型設(shè)置為int類，然后再展開(kāi)【標(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)的語(yǔ)法形式為：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用來(lái)限制字段是否允許取空值，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é)生成績(jī)表）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說(shuō)明：(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è)字段組合起來(lái)作為主鍵，只能采用表級(jí)完整性進(jìn)行定義。28三、表結(jié)構(gòu)的修改與刪除n利用菜單命令實(shí)現(xiàn)n使用Transact-SQL

16、語(yǔ)句來(lái)實(shí)現(xiàn) 29（1）利用菜單命令實(shí)現(xiàn)表結(jié)構(gòu)的修改和刪除步驟步驟1：在：在Microsoft SQL Server Management Studio 的的“對(duì)象資源管理器對(duì)象資源管理器”窗口中展開(kāi)相應(yīng)的數(shù)據(jù)窗口中展開(kāi)相應(yīng)的數(shù)據(jù)庫(kù)和表，右擊需要修改的數(shù)據(jù)表（如庫(kù)和表，右擊需要修改的數(shù)據(jù)表（如StuInfo表），表），然后選擇【修改】命令，打開(kāi)表設(shè)計(jì)器窗口。然后選擇【修改】命令，打開(kāi)表設(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ì)象資源管理器”窗口中展開(kāi)相應(yīng)窗口中展開(kāi)相應(yīng)的數(shù)據(jù)庫(kù)和表，右擊需要?jiǎng)h除的數(shù)據(jù)表，然后的數(shù)據(jù)庫(kù)和表，右擊需要?jiǎng)h除的數(shù)據(jù)表，然后選擇【刪除】命令即可刪除相應(yīng)的數(shù)

18、據(jù)表。選擇【刪除】命令即可刪除相應(yīng)的數(shù)據(jù)表。31（2）用命令語(yǔ)句修改表結(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 ；雖然從語(yǔ)法格式上來(lái)看比較復(fù)雜，但實(shí)際上該命令語(yǔ)句一次只能雖然從語(yǔ)法格式上來(lái)看比較復(fù)雜，但實(shí)際上該命令語(yǔ)句一次

19、只能修改一個(gè)參數(shù)。修改一個(gè)參數(shù)。 32例例9向向StuInfo表（學(xué)生信息表）中增加表（學(xué)生信息表）中增加“家庭住家庭住址（址（Saddr）”列，其數(shù)據(jù)類型為字符型。列，其數(shù)據(jù)類型為字符型。 ALTER TABLE StuInfo ADD Saddr varchar(20)不論基本表中原來(lái)是否已有數(shù)據(jù)，新增加的不論基本表中原來(lái)是否已有數(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命令來(lái)刪除表。最簡(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來(lái)進(jìn)行處理。完整的語(yǔ)句格式為：DROP TABLE RESTRICT| CASCADE；RESTRICT：刪除表是有限制的，它不能被其他表的約束所引用，如果存在依賴該表的對(duì)象，則此表不能被刪除；CASCADE：刪除該表沒(méi)有限制，在刪除基本表的同時(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,其命令語(yǔ)句如下：ALTER TABLE StuInfoDROP COLUMN grade42四、數(shù)據(jù)表內(nèi)容的增加與修改n表數(shù)據(jù)的操作主要有三種：插入數(shù)據(jù)修改數(shù)據(jù)刪除數(shù)據(jù) 431、插入數(shù)據(jù)n使用菜單命令來(lái)完成n用Transact-SQL的標(biāo)準(zhǔn)命令I(lǐng)NSERT語(yǔ)句來(lái)完成 44n使用Microsoft SQL Server Management Studio可以在“對(duì)象資源管理器”中右擊需要添加數(shù)據(jù)的表選擇“打開(kāi)表”命令，然后直接在表中輸入數(shù)據(jù)。 45插入數(shù)據(jù)命令n格式：IN

24、SERT INTO (column_list) VALUES (data_values)n功能：將新元組插入到指定表中。對(duì)于INTO子句中沒(méi)有出現(xiàn)的屬性列，新記錄在這些列上將取空值。n注意: 在表定義時(shí)說(shuō)明了為NOT NULL的屬性列不能取空值，否則會(huì)出錯(cuò)。如果INTO子句中沒(méi)有指明屬性列，則新插入的記錄必須在每個(gè)屬性列上均有值。屬性列的順序可與表定義中的順序不一致。可以只指定部分屬性列(未指定屬性列取值為空)。VALUES子句對(duì)新元組的各個(gè)屬性賦值，字符串常數(shù)要用單引號(hào)括起來(lái)。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來(lái)解決顯然是極

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

27、aB數(shù)據(jù)庫(kù)中建一個(gè)新表，其中一列用于保存學(xué)年信息，另一列用于保存相應(yīng)學(xué)年的學(xué)生平均成績(jī)。 CREATE TABLE Schyear_Score (Schyear CHAR(9), /* 學(xué)年*/ Avg_Score SMALLINT)； /*學(xué)年平均成績(jī)*/ 第二步：對(duì)ScoreInfo表按Schyear分組，把學(xué)年和學(xué)生的平均成績(jī)成績(jī)插入到新表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子句條件的記錄。說(shuō)明：SET子句用于指定