1、1主講教師 毛一梅2010.012數據庫表管理n數據類型n數據表結構的創建n數據表結構的修改與刪除n數據表內容的增加與修改n數據表的索引3一、數據類型n系統數據類型n用戶自定義數據類型4系統數據類型n二進制數據： Binary、Varbinary 和 Imagen字符數據：Char、Varchar 和 TextnUnicode 數據：Nchar，Nvarchar 和Ntext n日期和時間數據：Datetime 和 Smalldatetimen數字數據：精確數字、近似數字5n精確數字整數：bigint、int、smallint、tinyint、bit小數：decimal 和 numeric 貨

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

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

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

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

6、器中展開【可編程性】|【類型】|【用戶定義數據類型】，右擊要刪除的數據類型，選擇“刪除”12二、表結構的創建n使用菜單命令創建n使用命令方式創建n數據表的導入13（1）使用菜單命令創建n啟動“Microsoft SQL Server Management Studio”，在“對象資源管理器”窗口中展開【數據庫】文件夾，展開要建立表結構的數據庫，右擊【表】，選擇【新建表】命令，打開表設計器窗口。14列屬性n選擇不同的數據類型會有不同的列屬性，常用的列屬性說明如下：【說明】是用來說明該字段的含義。【默認值或綁定】在表中輸入新的記錄值時，如果沒有給該字段賦值，則默認值就是該字段的值。【精度】是用來說

7、明該字段數據類型的寬度，即位數。【小數位數】是用來說明該字段數據類型的小數位數。【是標識】表示該字段是表中的一個標識列，即新增的字段值為等差數列，其類型必須為數據值數據，有此屬性的字段會自動產生一個值，無需用戶輸入。【標識種子】等差數列的第一個數字。【標識增量】等差數列的公差。15例3n在teachDataA中新建一數據表tchInfo，其表結構如下圖所示：16常用的設置n關鍵字的設置右擊要設置為關鍵字的字段左側（如果要設置一組關鍵字，則可以用Ctrl選中多個字段），選擇“設置關鍵字”。n自動編號的設置-必須是int類型的數據在列屬性中設置“標識規范”（“是標識”、“標識種子”、“標識增量”）

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

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

10、何操作才能符合他的要求？解決方案：【標識規范】只對整型數據有效，所以小黃應該先將教師編號TID的數據類型設置為int類，然后再展開【標識規范】，修改【（是標識）】值為“是”，【標識種子】為“10000001”、 【標識增量】為“1”22（2）使用命令CREATE TABLE創建表結構創建表結構的語法形式為：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是要創建表的名稱，column_name1表示第一個字段的名稱，依次類推，data_type用于指定字段的數據類型，NULL或NOT NULL用來限制字段是否允許取空值，PRIMARY KEY設置字段為主鍵，UNIQUE設置取值的唯一性。 23例5 在teachingDataB中建立StuInfo表（學生信息表）CREATE TABLE StuInfo (SID char(8) primary key, -將SID設置為主鍵 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, -設置主鍵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表（學生成績表）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),/* 表級完整性約束條件，CID是外碼，被參照表是CourseInfo */ FOREIGN KEY(SID)REFERENCES StuInfo(SID),/* 表級完整性約束條件，SID是外碼，被參照表是StuInfo */FOREIGN KEY(TID)REFERENCES TchInfo

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

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

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

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

19、只能修改一個參數。修改一個參數。 32例例9向向StuInfo表（學生信息表）中增加表（學生信息表）中增加“家庭住家庭住址（址（Saddr）”列，其數據類型為字符型。列，其數據類型為字符型。 ALTER TABLE StuInfo ADD Saddr varchar(20)不論基本表中原來是否已有數據，新增加的不論基本表中原來是否已有數據，新增加的列一律為空值。列一律為空值。33例例10將表將表StuInfo中的年齡字段中的年齡字段Sage的數據類型由的數據類型由int改為改為tinyint，設置其默認值為，設置其默認值為20，并規定年齡必，并規定年齡必須在須在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增加學生姓名必須取唯一值的約束條件。增加學生姓名必須取唯一值的約束條件。ALTER TABLE StuInfo ADD UNIQUE(Sname)35例例12刪除刪除StuInfo表（學生信息表）中屬性列表（學生信息表）中屬性列“家庭住址（家庭住址（

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

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

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

24、SERT INTO (column_list) VALUES (data_values)n功能：將新元組插入到指定表中。對于INTO子句中沒有出現的屬性列，新記錄在這些列上將取空值。n注意: 在表定義時說明了為NOT NULL的屬性列不能取空值，否則會出錯。如果INTO子句中沒有指明屬性列，則新插入的記錄必須在每個屬性列上均有值。屬性列的順序可與表定義中的順序不一致。可以只指定部分屬性列(未指定屬性列取值為空)。VALUES子句對新元組的各個屬性賦值，字符串常數要用單引號括起來。VALUES子句提供的值必須與INTO子句匹配，即值的個數相同，值的類型一致。46例15將一個課程信息元組（課程號：

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

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

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