春數(shù)據(jù)庫實驗 表的創(chuàng)建管理和數(shù)據(jù)操縱(學時).doc

實驗三 表的創(chuàng)建、管理和數(shù)據(jù)操縱(4學時)【實驗目的】了解SQL語言的使用，進一步理解關(guān)系運算，鞏固數(shù)據(jù)庫的基礎(chǔ)知識?！緦嶒炓蟆坷肧QL語言進行數(shù)據(jù)庫表的各種操作：1數(shù)據(jù)庫表的創(chuàng)建、修改和刪除操作。2向表中進行數(shù)據(jù)的插入、刪除和修改操作。【實驗內(nèi)容】 1. 利用數(shù)據(jù)定義語句在實驗一創(chuàng)建的stu_DB庫中建立學生管理系統(tǒng)的三個表：Student、Course、SC。 2利用INSERT、UPDATE和DELETE語句對上述三張表的數(shù)據(jù)進行插入、更新和刪除操作。3在stu_DB庫中添加Teacher和TC表，對已建立的學生管理系統(tǒng)中的表添加充足的數(shù)據(jù)(每個表不少于20條)，以便完成本實驗的后繼操作。【實驗步驟】一、數(shù)據(jù)定義(一)表的創(chuàng)建在實驗一創(chuàng)建的數(shù)據(jù)庫stu_DB中分別用企業(yè)管理器和查詢分析器，按下面的表結(jié)構(gòu)創(chuàng)建學生管理系統(tǒng)的表。表3-1 Student表結(jié)構(gòu) 列名說明數(shù)據(jù)類型約束Sno學號字符串，長度為8主碼Sname姓名字符串，長度為10非空Ssex性別字符串，長度為2男或 女Sage年齡整數(shù)1545Sdept所在系字符串，長度為20默認為計算機系表3-2 Course表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Cno課程號字符串，長度為8主碼Cname課程名字符串，長度為20非空Cpno先修課程號字符串，長度為8Ccredit學分整數(shù)取值：16Semster學期整數(shù)取值：18Period學時整數(shù)取值：0表3-3 SC表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Sno學號字符串，長度為8主碼，引用student的外碼Cno課程號字符串，長度為8主碼，引用course的外碼Grade成績整數(shù)取值：01001利用企業(yè)管理器創(chuàng)建表 （1）打開企業(yè)管理器。（2）選中實驗一創(chuàng)建好的數(shù)據(jù)庫stu_DB，單擊數(shù)據(jù)庫中的表對象，然后右擊窗口右側(cè)選擇新建表，彈出如圖所示窗體。圖3-1 新建表窗體（3）在這個窗體中，列名列就示表的字段名，可以在這個窗體中為字段選擇數(shù)據(jù)類型和長度以及是否可以為空值。（4）在此處可以為表選擇文件組，在表中單擊右鍵，選擇屬性，如果已經(jīng)為數(shù)據(jù)庫創(chuàng)建了一個次要組，可以在屬性窗體的表文件組中為此表選擇文件組。應該把一些競爭使用的表放于不同的文件組中，并且讓文件組屬于不同磁盤，這樣可以在表競爭讀寫時提高并發(fā)性能。（5）創(chuàng)建完成后，單擊磁盤圖標，并為表取一個名字。注意，應該為表取一個有意義的名字。2用Transact_SQL語句創(chuàng)建表 語法為： CREATE TABLE database_name.owner. table_name ( column_name data_type DEFAULT default_value| CONSTRAINT CONSTRAINT_name , n IDENTITY (seed, increment ) ) ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT 【例1】生成一個表名為student的表create table student( sno CHAR(8) primary key, sname CHAR(10) not null, ssex CHAR (2) check (ssex=男or ssex=女), sage smallint, sdept CHAR (20) default (計算機系),)圖3-2查詢分析器窗體創(chuàng)建表【例2】 創(chuàng)建一張名為Ta1的表，此表中有3列，第一列Pid定義為主鍵，并且自動增長。第二列Name默認值為Unknown，第三列定義一個約束(日期不能大于輸入當天的日期)，插入一條記錄并進行查詢，顯示如下圖。CREATE TABLE ta1 ( pid int identity(1,1) primary key, name CHAR(10) default (unknow),birthday datetime CHECK( birthday getdate() )注意該列有什么特殊性？圖3-3 查詢分析器窗體創(chuàng)建表 請參考例1和例2，完成以下操作：(1)按前面所給的表結(jié)構(gòu)創(chuàng)建表Course和SC。(2)自行設(shè)計表的結(jié)構(gòu)，用SQL語句建立第二章習題5中的4個表。 (二)表的修改1 用企業(yè)管理器修改表 右鍵點擊所要修改的學生表，選擇“設(shè)計表”，會彈出如圖3-4所示的窗體，在這個窗體中可以更改數(shù)據(jù)表的字段。在空白處點右鍵，選擇“屬性”，彈出另一窗體，在此可以更改約束，也可以增加約束。圖3-4 修改表窗體（2）利用Transact-SQL語句修改表l 向表中添加新的字段：在學生表中添加一個“班級”字段，數(shù)據(jù)類型為字符型。ALTER TABLE student ADD class CHAR(6) l 刪除表中的舊列：將學生表中的“Sdept”字段刪除。ALTER TABLE student DROP COLUMN Sdeptl 更改表以添加具有約束的列。給學生表的增加“grade”字段并加上CHECK約束，讓其不可以大于100。ALTER TABLE student ADD grade int CONSTRAINT ch_grade CHECK(grade100)EXEC sp_help ch_grade給學生表中添加“birthday”字段，并且這個日期不能在錄入當天的日期之后。ALTER TABLE student ADD birthday DATETIME NULLCONSTRAINT ch_birthday CHECK(birthdaygetdate()l 添加具有默認值的可為空的列：在學生表中加入“matriculationday”字段，并且這一字段的默認值為錄入當天的日期。ALTER TABLE student ADD matriculationday smalldatetime NULLCONSTRAINT adddateflt DEFAULT getdate() 請參考上面所給的例子：(1) 自行設(shè)計表結(jié)構(gòu)修改操作（不少于8個語句），并進行實驗驗證。(2) 修改表student，結(jié)果如表3-4。(3) 創(chuàng)建表3-5、表3-6，為后續(xù)實驗建立基礎(chǔ)。表3-4 更新后的student表結(jié)構(gòu) 列名說明數(shù)據(jù)類型約束Sno學號字符串，長度為8主碼Sname姓名字符串，長度為10非空Ssex性別字符串，長度為2男或 女Sdept所在系字符串，長度為20默認為計算機系Sbirth出生日期日期自定義sclass班級字符串，長度為20sremark評語變長字符串，長度為100saddress家庭住址變長字符串，長度為40szipcode郵編字符串，長度為6sphone 電話字符串，長度為15semail電子郵箱變長字符串，長度為40表3-5 Teacher表結(jié)構(gòu) 列名說明數(shù)據(jù)類型約束tno工號字符串，長度為8主碼tname姓名字符串，長度為10非空tsex性別字符串，長度為2男或 女tdept所在系字符串，長度為20默認為計算機系tbirth出生日期日期自定義tposition職稱字符串，長度為12tsalary工資整數(shù)取值：010000texperience簡歷變長字符串，長度為200tamount工作量整數(shù)取值：01000tphone 電話字符串，長度為15temail電子郵箱變長字符串，長度為40taddress家庭住址變長字符串，長度為40表3-6 TC表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Tno工號字符串，長度為8主碼，引用Teacher的外碼Cno課程號字符串，長度為8主碼，引用Course的外碼Classroom教室變長字符串，長度為30(三)表的刪除可以在企業(yè)管理器中選擇要刪除的表直接刪除，也可以通過Transact-SQL語句DROP 刪除表的定義及表中的所有數(shù)據(jù)、索引、出發(fā)器、約束和權(quán)限規(guī)范。如要刪除ta1表，語句如下： DROP TABLE ta1請參考上面的例子完成對表Student 、Course 、SC的刪除操作，它們刪除的次序如何？并進行實驗驗證。二、數(shù)據(jù)操縱（一）查看、重命名及刪除用戶定義的數(shù)據(jù)類型1使用企業(yè)管理器創(chuàng)建一個名為newtype1、長度為6、可變長字符、允許為空的自定義數(shù)據(jù)類型。進入企業(yè)管理器，進入stu_DB數(shù)據(jù)庫，用鼠標右鍵單擊“用戶定義的數(shù)據(jù)類型”后選擇“操作”菜單中的“新建用戶定義數(shù)據(jù)類型”或點擊“新建”按扭，將彈出下圖所示窗體：圖3-4 數(shù)據(jù)類型定義窗體輸入要定義的數(shù)據(jù)名稱newtype1，選擇數(shù)據(jù)類型varCHAR，輸入長度為6，在“允許NULL值”復選框中打個“”，單擊“確定”按鈕即可。2.使用T-SQL語句創(chuàng)建一個名為newtype2，數(shù)據(jù)長度為6，定長字符型，不允許為空的自定義數(shù)據(jù)類型。USE stu_DBEXEC sp_addtype newtype2, CHAR(6), not null3.命名用戶自定義的數(shù)據(jù)類型 使用系統(tǒng)存儲過程sp_rename將自定義數(shù)據(jù)類型newtype1重新命名為a1。 EXEC sp_rename newtype1, a1 使用企業(yè)管理器再將自定義數(shù)據(jù)類型a1重新命名為newtype1。4刪除用戶自定義數(shù)據(jù)類型 使用系統(tǒng)存儲過程sp_droptype來刪除用戶自定義的數(shù)據(jù)類型。 EXEC sp_droptype newtype1 使用企業(yè)管理器刪除用戶自定義數(shù)據(jù)類型進入企業(yè)管理器，進入stu_DB數(shù)據(jù)庫，單擊“用戶定義的數(shù)據(jù)類型”后，右邊窗口將出現(xiàn)所有用戶定義的數(shù)據(jù)類型，再用鼠標右鍵單擊newtype2，在彈出的快捷菜單上單擊“刪除”按鈕即可。注意：正在被表或其他數(shù)據(jù)庫對象使用的用戶定義類型能不能刪除？請進行實驗驗證。（二）數(shù)據(jù)的更新1. 利用企業(yè)管理器更新數(shù)據(jù)打開企業(yè)管理器，右鍵點擊所要修改的表，選擇“打開表”，單擊“返回所有行”，會彈出如圖3-5所示的窗體，在該窗體中可以修改表中的數(shù)據(jù)內(nèi)容。這里的修改內(nèi)容是自動存儲的，修改完之后，直接關(guān)閉該窗體，數(shù)據(jù)內(nèi)容即修改成功。圖3-5數(shù)據(jù)修改窗體2.利用查詢分析器更新數(shù)據(jù)在關(guān)系數(shù)據(jù)庫中，常見的數(shù)據(jù)更新的語句有三條：INSERT、UPDATE和DELETEINSERT語句向student表中插入一行數(shù)據(jù)，具體數(shù)據(jù)如下：學號：04265005, 姓名：劉輝, 性別：男,年齡：21, 系別：計算機系USE stu_DBINSERT INTO student (Sno, Sname, Ssex, Sage, Sdept) VALUES (04265005, 劉輝, 男, 21, 計算機系)INSERT INTO student (Sno, Sname, Ssex, Sage) VALUES (04265006, 李慧, 女, 21)向ta1表中插入數(shù)據(jù)如下：INSERT ta1(name,birthday) values (lan,1977-03-02)INSERT ta1 values (lan,1977-03-02)INSERT ta1(birthday) values (1977-03-02)請根據(jù)上述例子，自行設(shè)計不同情況下的數(shù)據(jù)插入語句（不少于10個語句），并進行實驗驗證，驗證要求如下：l 不滿足唯一性約束的數(shù)據(jù)的插入。l 不滿足用戶自定義約束的數(shù)據(jù)的插入。l 不滿足外鍵約束的數(shù)據(jù)的插入。l 利用缺省值的數(shù)據(jù)的插入。l 不合理的數(shù)據(jù)是否能進入數(shù)據(jù)庫。UPDATE語句更改（學生）表數(shù)據(jù)，將學號為04265005的學生的所在系改成機械工程系。UPDATE student SET Sdept=機械系 WHERE Sno=04265005請根據(jù)上述例子，自行設(shè)計數(shù)據(jù)修改語句（不少于10個語句），并進行實驗驗證，驗證要求同INSERT語句。DELETE語句刪除行刪除student表中的學號為04265005的記錄。DELETE FROM student WHERE Sno =04265005如果想清除表中的所有數(shù)據(jù)但不刪除這個表，可以使用TRUNCATE TABLE語句。該語句相當于沒有條件的DELETE語句，并且該語句不記錄日志。請根據(jù)上述例子，自行設(shè)計不同情況的數(shù)據(jù)刪除語句（不少于10條語句），并進行實驗驗證不滿足外鍵約束的數(shù)據(jù)刪除操作。對數(shù)據(jù)定義中創(chuàng)建的學生管理系統(tǒng)（Student， Course，SC，Teacher，TC表）的每個表添加充足的數(shù)據(jù)(每個表不少于20條)，以便完成本實驗的后繼操作。具體表信息可參考如下：Student表：SnosnameSbirthdaySdept04261001王冬梅1982.02.07計算機系04261002王田田1982.03.04計算機系04261003吳畏1982.06.06計算機系04261004馬麗1982.04.03計算機系04261005楊乾坤1982.03.23計算機系04262001劉樂1983.01.16計算機系04262002夏天1983.02.08計算機系04262003楊致遠1982.07.22計算機系04561001陶然1982.01.12數(shù)學