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

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