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

1、實(shí)驗(yàn)三 表的創(chuàng)建、管理和數(shù)據(jù)操縱 （4 學(xué)時(shí) ）【實(shí)驗(yàn)?zāi)康摹苛私?SQL 語(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)中的表添加充足

2、的數(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è)管理器和查詢(xún)分析器， 按下面的表結(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-2Course 表結(jié)構(gòu)列名說(shuō)明數(shù)據(jù)類(lèi)型約束Cno沖工口 口.課程號(hào)字符串，長(zhǎng)度為 8主碼Cname課程名字符串，長(zhǎng)度為 20非空Cpno先修課程號(hào)字

3、符串，長(zhǎng)度為 8Ccredit學(xué)分整數(shù)取值： 16Semster學(xué)期整數(shù)取值： 18Period學(xué)時(shí)整數(shù)取值：0表 3-3SC 表結(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、以為空值。(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 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】生成一個(gè)表名為 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 (計(jì)算機(jī)系),)|圖3-2查詢(xún)分析器窗體一一創(chuàng)建表【例2】 創(chuàng)建一

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

7、結(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ǔ)句修改表向表中添加新的字段：在學(xué)生表中添加一個(gè)班級(jí)”字段，數(shù)據(jù)類(lèi)型為字符型。ALTER TABLE student ADD class CHAR (6)刪除表中的舊列：將學(xué)生表中的“ Sdep字段刪除。ALTER TABLE stude nt DROP COLUMN Sdept

8、更改表以添加具有約束的列。給學(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 TABLEstude nt ADD birthday DATETIME NULLCONSTRAINTch_birthday CHECK (birthdaygetdate()添加具有默認(rèn)值的可為空的列：在學(xué)生表中加入 “ m

9、atriculatio nday字段，并且這一字段的默認(rèn)值為錄入當(dāng)天的日期。ALTER TABLEstude nt ADD matriculati on day 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非空

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電話(huà)字符串,長(zhǎng)度為15semail電子郵箱變長(zhǎng)字符串,長(zhǎng)度為40表 3-5Teacher表結(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職

11、稱(chēng)字符串,長(zhǎng)度為12tsalary工資整數(shù)取值： 010000texperience簡(jiǎn)歷變長(zhǎng)字符串, 長(zhǎng)度為 200tamount工作量整數(shù)取值： 01000tphone電話(huà)字符串,長(zhǎng)度為15temail電子郵箱變長(zhǎng)字符串,長(zhǎng)度為40taddress家庭住址變長(zhǎng)字符串,長(zhǎng)度為40表 3-6TC 表結(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

12、刪除表的定義及表中的所有數(shù)據(jù)、索弓I、出發(fā)器、約束和權(quán)限規(guī)范。如要?jiǎng)h除tai表，語(yǔ)句如下：DROP TABLE tai請(qǐng)參考上面的例子完成對(duì)表 Student、Course、SC的刪除操作，它們刪除的次序如何？ 并進(jìn)行實(shí)驗(yàn)驗(yàn)證。二、數(shù)據(jù)操縱（一）查看、重命名及刪除用戶(hù)定義的數(shù)據(jù)類(lèi)型1 .使用企業(yè)管理器創(chuàng)建一個(gè)名為newtypel、長(zhǎng)度為6、可變長(zhǎng)字符、允許為空的自定義數(shù)據(jù)類(lèi)型。進(jìn)入企業(yè)管理器，進(jìn)入 stu_DB數(shù)據(jù)庫(kù)，用鼠標(biāo)右鍵單擊用戶(hù)定義的數(shù)據(jù)類(lèi)型”后選擇操作”菜單中的 新建用戶(hù)定義數(shù)據(jù)類(lèi)型”或點(diǎn)擊 新建”按扭，將彈出下圖所示窗體：圖3-4數(shù)據(jù)類(lèi)型定義窗體輸入要定義的數(shù)據(jù)名稱(chēng)newtype1

13、，選擇數(shù)據(jù)類(lèi)型varCHAR，輸入長(zhǎng)度為 6，在允許NULL值”復(fù)選框中打個(gè) “vf單擊確定”按鈕即可。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 null 3命名用戶(hù)自定義的數(shù)據(jù)類(lèi)型使用系統(tǒng)存儲(chǔ)過(guò)程 sp_rename將自定義數(shù)據(jù)類(lèi)型 newtypel重新命名為al。EXEC sp_re name n ewtypel, al 使用企業(yè)管理器再將自定義數(shù)據(jù)類(lèi)型al重新命名為newtypel。4.刪除用戶(hù)自定義數(shù)據(jù)類(lèi)型 使用系統(tǒng)存儲(chǔ)過(guò)程 s

14、p_droptype來(lái)刪除用戶(hù)自定義的數(shù)據(jù)類(lèi)型。EXEC sp_droptype newtypel使用企業(yè)管理器刪除用戶(hù)自定義數(shù)據(jù)類(lèi)型進(jìn)入企業(yè)管理器，進(jìn)入 stu_DB數(shù)據(jù)庫(kù)，單擊 用戶(hù)定義的數(shù)據(jù)類(lèi)型”后，右邊窗口將出 現(xiàn)所有用戶(hù)定義的數(shù)據(jù)類(lèi)型， 再用鼠標(biāo)右鍵單擊 newtype2，在彈出的快捷菜單上單擊 刪除 按鈕即可。注意：正在被表或其他數(shù)據(jù)庫(kù)對(duì)象使用的用戶(hù)定義類(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ǔ)的，

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

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

17、 SET Sdept=機(jī)械系WHERE Sno= 請(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)為的記錄。DELETE FROM student WHERE Sno =如果想清除表中的所有數(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)證不滿(mǎn)足外鍵約束的數(shù)據(jù)刪除操作。對(duì)數(shù)據(jù)定義中創(chuàng)建的學(xué)生管理系統(tǒng)（Student, Course， S

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