1、實驗三 表的創建、管理和數據操縱 （4 學時 ）【實驗目的】了解 SQL 語言的使用，進一步理解關系運算，鞏固數據庫的基礎知識。【實驗要求】 利用 SQL 語言進行數據庫表的各種操作： 1數據庫表的創建、修改和刪除操作。 2向表中進行數據的插入、刪除和修改操作。【實驗內容】1. 利用數據定義語句在實驗一創建的 stu_DB 庫中建立學生管理系統的三個表： Student、Course、SC。2利用 INSERT 、 UPDATE 和 DELETE 語句對上述三張表的數據進行插入、更新和刪 除操作。3在 stu_DB 庫中添加 Teacher 和 TC 表，對已建立的學生管理系統中的表添加充足

2、的數據 （每個表不少于 20 條），以便完成本實驗的后繼操作。【實驗步驟】一、數據定義（一）表的創建在實驗一創建的數據庫 stu_DB 中分別用企業管理器和查詢分析器， 按下面的表結構創 建學生管理系統的表。表 3-1 Student 表結構列名說明數據類型約束Sno學號字符串，長度為 8主碼Sname姓名字符串，長度為 10非空Ssex性別字符串，長度為 2男或 女Sage年齡整數1545Sdept所在系字符串，長度為 20默認為計算機系表 3-2Course 表結構列名說明數據類型約束Cno沖工口 口.課程號字符串，長度為 8主碼Cname課程名字符串，長度為 20非空Cpno先修課程號字

3、符串，長度為 8Ccredit學分整數取值： 16Semster學期整數取值： 18Period學時整數取值：0表 3-3SC 表結構列名說明數據類型約束Sno學號字符串，長度為 8主碼，引用 student 的外碼Cno沖工口 口.課程號字符串，長度為 8主碼，引用 course 的外碼Grade成績整數取值：01001 利用企業管理器創建表(1) 打開企業管理器。(2) 選中實驗一創建好的數據庫stu_DB，單擊數據庫中的表對象，然后右擊窗口右側 選擇新建表，彈出如圖所示窗體。圖3-1新建表窗體(3) 在這個窗體中，列名列就示表的字段名，可以在這個窗體中為字段選擇數據類型 和長度以及是否可

4、以為空值。(4) 在此處可以為表選擇文件組，在表中單擊右鍵，選擇屬性，如果已經為數據庫創建了一個次要組，可以在屬性窗體的表文件組中為此表選擇文件組。應該把一些競爭使用的表放于不同的文件組中，并且讓文件組屬于不同磁盤，這樣可以在表競爭讀寫時提高并發性能。(5) 創建完成后，單擊磁盤圖標，并為表取一個名字。注意，應該為表取一個有意義 的名字。2 .用Transact_SQL語句創建表語法為：CREATE TABLEdatabase. name.ow ner. table_ name( data_type DEFAULT default_value |CONSTRAINT CO

5、NSTRAINT, name,n IDENTITY (seed, increment)ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT 【例1】生成一個表名為 student的表create table stude nt(Isno CHAR(8) primary key, sname CHAR(10) not null,ssex CHAR (2)check (ssex=男or ssex=女)sage small int,sdept CHAR (20) default (計算機系),)|圖3-2查詢分析器窗體一一創建表【例2】 創建一

6、張名為Ta1的表，此表中有 3列，第一列Pid定義為主鍵，并且自動 增長。第二列 Name默認值為Unknown，第三列定義一個約束(日期不能大于輸入當天的日 期)，插入一條記錄并進行查詢，顯示如下圖。CREATE TABLE ta1(pid int identity (1,1) primary key, n ame CHAR(10) default ( unkno w), birthday datetime CHECK( birthday getdate()圖3-3查詢分析器窗體一一創建表請參考例1和例2,完成以下操作：(1) 按前面所給的表結構創建表Course和SC。(2) 自行設計表的

7、結構，用SQL語句建立第二章習題 5中的4個表。(二) 表的修改1. 用企業管理器修改表 右鍵點擊所要修改的學生表，選擇 設計表”會彈出如圖3-4所示的窗體，在這個 窗體中可以更改數據表的字段。在空白處點右鍵，選擇屬性”彈出另一窗體，在此可以更改約束，也可以增加約束。圖3-4修改表窗體(2)利用Transact-SQL語句修改表向表中添加新的字段：在學生表中添加一個班級”字段，數據類型為字符型。ALTER TABLE student ADD class CHAR (6)刪除表中的舊列：將學生表中的“ Sdep字段刪除。ALTER TABLE stude nt DROP COLUMN Sdept

8、更改表以添加具有約束的列。給學生表的增加 “grade字段并加上CHECK約束，讓其不可以大于100。ALTER TABLE student ADD grade int CONSTRAINT ch_grade CHECK (grade100) EXEC sp_help ch_grade給學生表中添加 “ birthday字段，并且這個日期不能在錄入當天的日期之后。ALTER TABLEstude nt ADD birthday DATETIME NULLCONSTRAINTch_birthday CHECK (birthdaygetdate()添加具有默認值的可為空的列：在學生表中加入 “ m

9、atriculatio nday字段，并且這一字段的默認值為錄入當天的日期。ALTER TABLEstude nt ADD matriculati on day smalldatetime NULLCONSTRAINT adddateflt DEFAULT getdate()請參考上面所給的例子：(1) 自行設計表結構修改操作(不少于 8 個語句)，并進行實驗驗證。(2) 修改表student,結果如表 3-4。(3) 創建表 3-5、表 3-6，為后續實驗建立基礎。表 3-4更新后的 student 表結構列名說明數據類型約束Sno學號字符串,長度為8主碼Sname姓名字符串,長度為10非空

10、Ssex性別字符串,長度為2男或 女Sdept所在系字符串,長度為20默認為計算機系Sbirth出生日期日期自定義sclass班級字符串,長度為20sremark評語變長字符串, 長度為100saddress家庭住址變長字符串,長度為40szipcode郵編字符串,長度為6sphone電話字符串,長度為15semail電子郵箱變長字符串,長度為40表 3-5Teacher表結構列名說明數據類型約束tno工號字符串,長度為8主碼tname姓名字符串,長度為10非空tsex性別字符串,長度為2男或 女tdept所在系字符串,長度為20默認為計算機系tbirth出生日期日期自定義tposition職

11、稱字符串,長度為12tsalary工資整數取值： 010000texperience簡歷變長字符串, 長度為 200tamount工作量整數取值： 01000tphone電話字符串,長度為15temail電子郵箱變長字符串,長度為40taddress家庭住址變長字符串,長度為40表 3-6TC 表結構列名說明數據類型約束Tno工號字符串,長度為8主碼,引用 Teacher 的外碼Cno沖工口 口.課程號字符串,長度為8主碼,引用 Course 的外碼Classroom教室變長字符串,長度為30（三）表的刪除可以在企業管理器中選擇要刪除的表直接刪除，也可以通過Transact-SQL語句DROP

12、刪除表的定義及表中的所有數據、索弓I、出發器、約束和權限規范。如要刪除tai表，語句如下：DROP TABLE tai請參考上面的例子完成對表 Student、Course、SC的刪除操作，它們刪除的次序如何？ 并進行實驗驗證。二、數據操縱（一）查看、重命名及刪除用戶定義的數據類型1 .使用企業管理器創建一個名為newtypel、長度為6、可變長字符、允許為空的自定義數據類型。進入企業管理器，進入 stu_DB數據庫，用鼠標右鍵單擊用戶定義的數據類型”后選擇操作”菜單中的 新建用戶定義數據類型”或點擊 新建”按扭，將彈出下圖所示窗體：圖3-4數據類型定義窗體輸入要定義的數據名稱newtype1

13、，選擇數據類型varCHAR，輸入長度為 6，在允許NULL值”復選框中打個 “vf單擊確定”按鈕即可。2使用T-SQL語句創建一個名為newtype2，數據長度為6,定長字符型，不允許為空的自定義數據類型。USE stu_DBEXEC sp_addtype newtype2, CHAR（6） ，not null 3命名用戶自定義的數據類型使用系統存儲過程 sp_rename將自定義數據類型 newtypel重新命名為al。EXEC sp_re name n ewtypel, al 使用企業管理器再將自定義數據類型al重新命名為newtypel。4.刪除用戶自定義數據類型 使用系統存儲過程 s

14、p_droptype來刪除用戶自定義的數據類型。EXEC sp_droptype newtypel使用企業管理器刪除用戶自定義數據類型進入企業管理器，進入 stu_DB數據庫，單擊 用戶定義的數據類型”后，右邊窗口將出 現所有用戶定義的數據類型， 再用鼠標右鍵單擊 newtype2，在彈出的快捷菜單上單擊 刪除 按鈕即可。注意：正在被表或其他數據庫對象使用的用戶定義類型能不能刪除？請進行實驗驗證。（二）數據的更新1.利用企業管理器更新數據打開企業管理器，右鍵點擊所要修改的表，選擇打開表”單擊 返回所有行”會彈出如圖3-5所示的窗體，在該窗體中可以修改表中的數據內容。這里的修改內容是自動存儲的，

15、修改完之后，直接關閉該窗體，數據內容即修改成功。圖3-5數據修改窗體2利用查詢分析器更新數據在關系數據庫中，常見的數據更新的語句有三條：INSERT、UPDATE和DELETEINSERT語句向student表中插入一行數據，具體數據如下：學號：，姓名：劉輝，性別：男，年齡：21,系別：計算機系USE stu_DBINSERT INTO stude nt （S no, Sn ame, Ssex, Sage, Sdept）VALUES （劉輝男21,計算機系）INSERT INTO stude nt （S no, Sn ame, Ssex, Sage）VALUES （ 李慧女21）向ta1表中插

16、入數據如下：INSERT ta1(name,birthday) values (-03n02 ,) 1977INSERT ta1 values (la n -03-0219)77INSERT ta1(birthday) values (-03-01977請根據上述例子，自行設計不同情況下的數據插入語句（不少于10個語句），并進行實驗驗證，驗證要求如下：不滿足唯一性約束的數據的插入。不滿足用戶自定義約束的數據的插入。不滿足外鍵約束的數據的插入。利用缺省值的數據的插入。不合理的數據是否能進入數據庫。UPDATE語句更改（學生）表數據，將學號為的學生的所在系改成機械工程系UPDATE student

17、 SET Sdept=機械系WHERE Sno= 請根據上述例子，自行設計數據修改語句（不少于10個語句），并進行實驗驗證，驗證要求同INSERT語句。DELETE語句刪除行刪除student表中的學號為的記錄。DELETE FROM student WHERE Sno =如果想清除表中的所有數據但不刪除這個表，可以使用TRUNCATE TABLE 語句。該語句相當于沒有條件的DELETE語句，并且該語句不記錄日志。請根據上述例子，自行設計不同情況的數據刪除語句（不少于10條語句），并進行實驗驗證不滿足外鍵約束的數據刪除操作。對數據定義中創建的學生管理系統（Student, Course， S

18、C, Teacher，TC表）的每個表添加充足的數據（每個表不少于20條），以便完成本實驗的后繼操作。具體表信息可參考 如下：Student 表:Snosn ameSbirthdaySdept王冬梅1982.02.07計算機系王田田1982.03.04計算機系早田.畏1982.06.06計算機系馬麗1982.04.03計算機系楊乾坤1982.03.23計算機系劉樂1983.01.16計算機系夏天1983.02.08計算機系楊致遠1982.07.22計算機系陶然1982.01.12數學系姚遠1982.03.21數學系course 表:CnoCn ameCpnoCreditC001數據庫原理C0053C002高等數學4C003管理信息系統C0012C004操作系統C0063C005數據結構C0073C006計算機文化基礎2C007C語言程序設計C0063score 表:SnoCnoScoreC00190C00185C00173C00198C00196C00197C00183C00185C00792C00797teacher 表: