11春數(shù)據(jù)庫實(shí)驗(yàn)3表的創(chuàng)建、管理和數(shù)據(jù)操縱

1、實(shí)驗(yàn)三 表的創(chuàng)建、管理和數(shù)據(jù)操縱 （4 學(xué)時(shí) ）【實(shí)驗(yàn)?zāi)康摹苛私?SQL 語言的使用，進(jìn)一步理解關(guān)系運(yùn)算，鞏固數(shù)據(jù)庫的基礎(chǔ)知識(shí)?！緦?shí)驗(yàn)要求】 利用 SQL 語言進(jìn)行數(shù)據(jù)庫表的各種操作： 1數(shù)據(jù)庫表的創(chuàng)建、修改和刪除操作。 2向表中進(jìn)行數(shù)據(jù)的插入、刪除和修改操作?！緦?shí)驗(yàn)內(nèi)容】1. 利用數(shù)據(jù)定義語句在實(shí)驗(yàn)一創(chuàng)建的 stu_DB 庫中建立學(xué)生管理系統(tǒng)的三個(gè)表： Student、Course、SC。2利用 INSERT 、 UPDATE 和 DELETE 語句對(duì)上述三張表的數(shù)據(jù)進(jìn)行插入、更新和刪 除操作。3在 stu_DB 庫中添加 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ù)庫 stu_DB 中分別用企業(yè)管理器和查詢分析器， 按下面的表結(jié)構(gòu)創(chuàng) 建學(xué)生管理系統(tǒng)的表。表 3-1 Student 表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束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)列名說明數(shù)據(jù)類型約束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)列名說明數(shù)據(jù)類型約束Sno學(xué)號(hào)字符串，長(zhǎng)度為 8主碼，引用 student 的外碼Cno沖工口 口.課程號(hào)字符串，長(zhǎng)度為 8主碼，引用 course 的外碼Grade成績(jī)整數(shù)取值：01001 利用企業(yè)管理器創(chuàng)建表(1) 打開企業(yè)管理器。(2) 選中實(shí)驗(yàn)一創(chuàng)建好的數(shù)據(jù)庫stu_DB，單擊數(shù)據(jù)庫中的表對(duì)象，然后右擊窗口右側(cè) 選擇新建表，彈出如圖所示窗體。圖3-1新建表窗體(3) 在這個(gè)窗體中，列名列就示表的字段名，可以在這個(gè)窗體中為字段選擇數(shù)據(jù)類型 和長(zhǎng)度以及是否可

4、以為空值。(4) 在此處可以為表選擇文件組，在表中單擊右鍵，選擇屬性，如果已經(jīng)為數(shù)據(jù)庫創(chuàng)建了一個(gè)次要組，可以在屬性窗體的表文件組中為此表選擇文件組。應(yīng)該把一些競(jìng)爭(zhēng)使用的表放于不同的文件組中，并且讓文件組屬于不同磁盤，這樣可以在表競(jìng)爭(zhēng)讀寫時(shí)提高并發(fā)性能。(5) 創(chuàng)建完成后，單擊磁盤圖標(biāo)，并為表取一個(gè)名字。注意，應(yīng)該為表取一個(gè)有意義 的名字。2 .用Transact_SQL語句創(chuàng)建表語法為：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查詢分析器窗體一一創(chuàng)建表【例2】 創(chuàng)建一

6、張名為Ta1的表，此表中有 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, n ame CHAR(10) default ( unkno w), birthday datetime CHECK( birthday getdate() )圖3-3查詢分析器窗體一一創(chuàng)建表請(qǐng)參考例1和例2,完成以下操作：(1) 按前面所給的表結(jié)構(gòu)創(chuàng)建表Course和SC。(2) 自行設(shè)計(jì)

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

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

9、 “ matriculatio 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è)語句)，并進(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)列名說明數(shù)據(jù)類型約束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)語變長(zhǎng)字符串, 長(zhǎng)度為100saddress家庭住址變長(zhǎng)字符串,長(zhǎng)度為40szipcode郵編字符串,長(zhǎng)度為6sphone電話字符串,長(zhǎng)度為15semail電子郵箱變長(zhǎng)字符串,長(zhǎng)度為40表 3-5Teacher表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束tno工號(hào)字符串,長(zhǎng)度為8主碼tname姓名字符串,長(zhǎng)度為10非空tsex性別字符串,長(zhǎng)度為2男或 女tdept所在系字符串,長(zhǎng)度為20默認(rèn)為計(jì)算機(jī)系tbirth出生日期日期自定義tposi

11、tion職稱字符串,長(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-6TC 表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Tno工號(hào)字符串,長(zhǎng)度為8主碼,引用 Teacher 的外碼Cno沖工口 口.課程號(hào)字符串,長(zhǎng)度為8主碼,引用 Course 的外碼Classroom教室變長(zhǎng)字符串,長(zhǎng)度為30（三）表的刪除可以在企業(yè)管理器中選擇要?jiǎng)h除的表直接刪除，也可以通過Transact-SQL語

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

13、type1，選擇數(shù)據(jù)類型 varCHAR，輸入長(zhǎng)度為 6，在允許NULL值”復(fù)選框中打個(gè) “vf單擊確定”按鈕即可。2使用T-SQL語句創(chuàng)建一個(gè)名為 newtype2，數(shù)據(jù)長(zhǎng)度為6,定長(zhǎng)字符型，不允許為空的 自定義數(shù)據(jù)類型。USE stu_DBEXEC sp_addtype newtype2, CHAR（6） ，not null 3命名用戶自定義的數(shù)據(jù)類型使用系統(tǒng)存儲(chǔ)過程 sp_rename將自定義數(shù)據(jù)類型 newtypei重新命名為al。EXEC sp_re name n ewtypel, alai重新命名為 newtypei 。 使用企業(yè)管理器再將自定義數(shù)據(jù)類型4 刪除用戶自定義數(shù)據(jù)類型

14、使用系統(tǒng)存儲(chǔ)過程 sp_droptype來刪除用戶自定義的數(shù)據(jù)類型。EXEC sp_droptype newtypel使用企業(yè)管理器刪除用戶自定義數(shù)據(jù)類型進(jìn)入企業(yè)管理器，進(jìn)入 stu_DB數(shù)據(jù)庫，單擊 用戶定義的數(shù)據(jù)類型”后，右邊窗口將出 現(xiàn)所有用戶定義的數(shù)據(jù)類型， 再用鼠標(biāo)右鍵單擊 newtype2，在彈出的快捷菜單上單擊 刪除 按鈕即可。注意：正在被表或其他數(shù)據(jù)庫對(duì)象使用的用戶定義類型能不能刪除？請(qǐng)進(jìn)行實(shí)驗(yàn)驗(yàn)證。（二）數(shù)據(jù)的更新1.利用企業(yè)管理器更新數(shù)據(jù)打開企業(yè)管理器，右鍵點(diǎn)擊所要修改的表，選擇打開表”單擊 返回所有行”會(huì)彈出如圖3-5所示的窗體，在該窗體中可以修改表中的數(shù)據(jù)內(nèi)容。這里的修

15、改內(nèi)容是自動(dòng)存儲(chǔ)的，修改完之后，直接關(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ù)如下：學(xué)號(hào)：04265005,姓名：劉輝，性別：男，年齡：21,系別：計(jì)算機(jī)系USE stu_DBINSERT INTO stude nt (S no, Sn ame, Ssex, Sage, Sdept) VALUES ( 04265005劉輝，男21,計(jì)算機(jī)系)INSERT INTO stude nt (S no, Sn ame, Sse

16、x, Sage)VALUES ( 04265006李慧女21)向ta1表中插入數(shù)據(jù)如下：INSERT ta1(name,birthday) values (-03n02 ,) 1977INSERT ta1 values ( lan -03-0219)77INSERT ta1(birthday) values (-03-01977請(qǐng)根據(jù)上述例子，自行設(shè)計(jì)不同情況下的數(shù)據(jù)插入語句（不少于10個(gè)語句），并進(jìn)行實(shí) 驗(yàn)驗(yàn)證，驗(yàn)證要求如下:不滿足唯一性約束的數(shù)據(jù)的插入。 不滿足用戶自定義約束的數(shù)據(jù)的插入。不滿足外鍵約束的數(shù)據(jù)的插入。利用缺省值的數(shù)據(jù)的插入。不合理的數(shù)據(jù)是否能進(jìn)入數(shù)據(jù)庫。UPDATE語句更改

17、（學(xué)生）表數(shù)據(jù)，將學(xué)號(hào)為04265005 勺學(xué)生的所在系改成機(jī)械工程系UPDATE student SET Sdept=機(jī)械系WHERE Sno= 04265005請(qǐng)根據(jù)上述例子，自行設(shè)計(jì)數(shù)據(jù)修改語句（不少于10個(gè)語句），并進(jìn)行實(shí)驗(yàn)驗(yàn)證，驗(yàn)證要求同INSERT語句。DELETE語句刪除行刪除student表中的學(xué)號(hào)為 04265005 的記錄。DELETE FROM student WHERE Sno = 04265005如果想清除表中的所有數(shù)據(jù)但不刪除這個(gè)表，可以使用TRUNCATE TABLE 語句。該語句相當(dāng)于沒有條件的 DELETE語句，并且該語句不記錄日志。請(qǐng)根據(jù)上述例子，自行設(shè)計(jì)不

18、同情況的數(shù)據(jù)刪除語句（不少于10條語句），并進(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 表:Snosn ameSbirthdaySdept04261001王冬梅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劉樂1983.01.16計(jì)算機(jī)系04262002夏天1983.02.08計(jì)算機(jī)系04262003楊致遠(yuǎn)19