數(shù)據(jù)庫實驗-華中科技大學(xué)說課材料

數(shù)據(jù)庫實驗-華中科技大學(xué)精品文檔數(shù)據(jù)庫實驗報告Xxxx2012-6-1一、實驗?zāi)康恼莆誗QLServer2000的工具使用收集于網(wǎng)絡(luò)，如有侵權(quán)請聯(lián)系管理員刪除精品文檔掌握DDL的使用方法掌握DML的使用方法掌握SELECT命令使用方法掌握DCL的使用方法掌握數(shù)據(jù)庫的備份和恢復(fù)二、實驗原理1．SQLServer2000的工具1） 服務(wù)管理器（ServerManager）在進行任何數(shù)據(jù)庫操作前，都必須啟動服務(wù)器，ServerManager可以方便啟動、停止、暫停本地或遠程服務(wù)器。圖6是打開的ServerManager窗口。適當配置窗口中的選項，點擊“啟動”按鈕即可啟動SQLServer服務(wù)器。SQLServer2000安裝以圖6ServerManager窗口后，每次開機時，Windows都會自動啟動服務(wù)管理器。在任務(wù)欄上有一個圖標表示服務(wù)管理器的狀態(tài)。如圖7所示。2） SQLServer2000查詢分析器（QueryAnalyzer）查詢分析器是一個重要工具，實驗中的所有SQL語言命令均需在查詢分析器中輸入、編輯運行。從Program－>SQLServer->QueryAnalyzer可以打開查詢分析器，如圖11所示。它是一個多文檔程序，在其上可以同時打開多個查詢程序（窗口），在查詢分析器的工具欄中的綠色按鈕為執(zhí)行當前窗口中SQL語句按鈕。點擊它即可逐行執(zhí)行。2.DDL使用方法1）數(shù)據(jù)庫創(chuàng)建在查詢分析器中執(zhí)行下列語句即可在默認的設(shè)備上創(chuàng)建新的數(shù)據(jù)庫 sch。CREATEDATABASEdatabase_name收集于網(wǎng)絡(luò)，如有侵權(quán)請聯(lián)系管理員刪除精品文檔2）基本表的建立創(chuàng)建基本表的命令為：CREATETABLEtable_name，在該命令中定義主碼和外碼時，可以使用列約束（ColumnConstraint）或表約束（TableConstraint）子句。創(chuàng)建基本表時，應(yīng)先選擇包含表的數(shù)據(jù)庫。3）視圖的建立視圖是組成數(shù)據(jù)庫體系結(jié)構(gòu)——三級模式兩級映像結(jié)構(gòu)中的外模式的基本單元，SQL-Server的視圖定義命令為：CREATEVIEWview-nameASSELECTstatement視圖是用于定義終端用戶數(shù)據(jù)來源的。在視圖定義中可以使用復(fù)雜的SELECT命令。機視圖。3.DML使用方法SQL的DML包括插入（INSERT）、刪除(DELETE)、修改(UPDATE)等命令。DML命令的執(zhí)行是可能造成數(shù)據(jù)庫不一致的根源。因此，每一條語句在執(zhí)行前，SQL-Server都要驗證語句是否符合完整性要求，包括實體完整性、參照完整性、用戶定義完整性。1）INSERT命令SQL語言的插入命令：INSERTINTOtable-name(column-list)VALUES(values-list)，可以完成數(shù)據(jù)輸入功能。注意：在執(zhí)行過程中，如果有一條語句出錯誤，再次執(zhí)行時，在出錯語句前面的語句就會被拒絕執(zhí)行。因為，在查詢分析器中的命令在執(zhí)行時按順序逐條執(zhí)行，當執(zhí)行到出錯語句時停止執(zhí)行，此時，在出錯語句的前面各條語句的結(jié)果已經(jīng)存入表中，故當重復(fù)執(zhí)行時，它們違反了實體完整性。2） DELETE命令SQL的刪除命令為：DELETEFROMtable-name[WHEREcondition-expression];執(zhí)行DELETE命令后，系統(tǒng)會刪除滿足命令中條件表達式的所有元組。這種刪除只是邏輯的。因此，當再次插入一個與被刪除的元組具有相同關(guān)鍵字的元組時，被認為違反了實體完整性。3） UPDATE命令SQL的修改命令為：UPDATEtable-name收集于網(wǎng)絡(luò)，如有侵權(quán)請聯(lián)系管理員刪除精品文檔SETcolumn-name=expression[WHEREcondition-expression];執(zhí)行UPDATE命令后，系統(tǒng)會按照SET子句修改滿足命令中條件表達式的所有元組。如果使用修改命令更新關(guān)鍵字的值，而該關(guān)鍵字在其他表中作為外碼存在時，操作可能違反參照完整性。系統(tǒng)將拒絕執(zhí)行。4） SELECT命令SELECT命令是SQL語言中使用最頻繁的命令，其變化形式繁多，靈活運用SELECT命令可以完成任意復(fù)雜的查詢要求。SELECT命令的基本語法為:SELECTquery_expression|column-list|*FROMtable_name_list|view_name_list[WHEREcondition-expression][GROUPBY[HAVINGcondition-expression]][ORDERBYorder_expression|column[ASC|DESC]][,?n];由于SELECT命令非常復(fù)雜,上面的語法描述還無法完全表達，請參考TransactionSQL的幫助。具體查詢方法參考教材。4.DCL的使用方法1）SQLServer登錄認證新登錄的創(chuàng)建新的登錄者可以是WINDOWS的用戶或用戶組，也可以是使用SQLServer認證模式的登錄帳號，設(shè)定WINDOWS的用戶為SQLServer登錄者時使用sp_grantlogin。創(chuàng)建新的使用SQLServer認證模式的登錄帳號時使用sp_addlogins。登錄的刪除刪除基于使用SQLServer認證模式的SQLServer的登錄可以使用sp_droplogin。但是，如果與該登錄匹配的數(shù)據(jù)庫用戶仍存在 sysuser表中，則不能刪除該登錄賬號。刪除 WINDOWS的用戶或用戶組時，使用sp_revokelogin，拒絕WINDOWS的用戶或用戶組連接到 SQLServer時，使用sp_decnylogin。登陸的查看sp_helplogins可以用來顯示SQLServer的所有登錄者的信息。如圖29是顯示數(shù)據(jù)庫ems的所有登錄者信息的代碼。2）用戶管理SQLServer利用下列系統(tǒng)過程管理數(shù)據(jù)庫用戶：sp_addusersp_grangdbaccesssp_dropuserSp_dropusersp_revokedbaccesssp_helpuser。其中sp_adduserSp_dropuser是為了保持與以前版本相兼容，因此收集于網(wǎng)絡(luò)，如有侵權(quán)請聯(lián)系管理員刪除精品文檔不主張使用，在SQLServer2000中，建議使用sp_grangdbaccesssp_revokedbaccess。創(chuàng)建新數(shù)據(jù)庫用戶所有的數(shù)據(jù)庫用戶（除了guest）均必須與某一登錄賬號相匹配，因此在使用系統(tǒng)過程創(chuàng)建新數(shù)據(jù)庫用戶時，不但要指出新數(shù)據(jù)庫用戶的名稱，還必須指出一個已經(jīng)存在的登錄賬號。系統(tǒng)過程sp_grantdbaccess用于為SQLServer登錄用戶或NT用戶或用戶組建立相匹配的數(shù)據(jù)庫用戶帳號。刪除數(shù)據(jù)庫用戶系統(tǒng)過程sp_revokedbaccess用于將數(shù)據(jù)庫用戶從當前數(shù)據(jù)庫中刪除，刪除后與其匹配的登錄即無法再訪問該數(shù)據(jù)庫了。查看數(shù)據(jù)庫用戶信息sp_helpuser可用來顯示當前數(shù)據(jù)庫的指定用戶信息。3）用戶授權(quán)管理對于登錄到SQLServer的合法數(shù)據(jù)庫用戶，必須獲得對數(shù)據(jù)庫操作的授權(quán)。在SQLServer中包括兩種類型的權(quán)限，即對象權(quán)限和語句權(quán)限。對象權(quán)限總是針對表、視圖、存儲過程而言，它決定了能對這些對象執(zhí)行哪些操作（如UPDATEDELETEINSERTSELECTEXECUTE）。不同類型的對象支持不同類型的操作，表3為各種對象的可能操作列舉。表3

：對象權(quán)限總結(jié)表對象

操 作表

SELECTUPDATEDELETEINSERTREFERENCE視圖

SELECTUPDATEDELETEINSERT存儲過程 EXECUTE語句權(quán)限指數(shù)據(jù)庫用戶執(zhí)行某種語句的操作權(quán)，如創(chuàng)建數(shù)據(jù)庫、表、存儲過程等。這些語句雖然（如CREATE命令）包含有操作對象，但這些對象在操作前并不存在于數(shù)據(jù)庫中。表4為所有權(quán)語句清單。表4語句權(quán)限總結(jié)表語句含義CREATEDTATBASE創(chuàng)建數(shù)據(jù)庫CREATETABLE創(chuàng)建表CREATEVIEW創(chuàng)建視圖CREATERULE創(chuàng)建規(guī)則收集于網(wǎng)絡(luò)，如有侵權(quán)請聯(lián)系管理員刪除CREATEDEFAULT創(chuàng)建缺省CREATEPROCEDURE創(chuàng)建存儲過程精品文檔5.數(shù)據(jù)庫的備份和恢復(fù)備份是指對SQLServer數(shù)據(jù)庫或事務(wù)日志進行拷貝，如果數(shù)據(jù)庫因意外而損壞，備份文件可以用來恢復(fù)數(shù)據(jù)庫。SQLServer2000中有四種備份類型，分別是：數(shù)據(jù)庫備份、事務(wù)日志備份、差異備份、文件和文件組備份。在使用是可以根據(jù)實際情況選擇一種或幾種的混合應(yīng)用。1)備份設(shè)備在進行備份以前必須創(chuàng)建備份設(shè)備。備份設(shè)備是用來存儲數(shù)據(jù)庫、事務(wù)日志、文件和文件組備份的存儲介質(zhì)?？梢允谴疟P、磁帶或管道。SQLServer2000提供了兩種創(chuàng)建備份的途徑，即可視化方式和命令方式。用命令備份數(shù)據(jù)庫使用BACKUP命令進行備份操作。備份完整數(shù)據(jù)庫/*EXECsp_addumpdevice'DISK','backupdevice_name','d:\backupdev\ems.bak' 目標路徑*/BACKUPDATABASEdatabaseTObackupdevice_name2)恢復(fù)數(shù)據(jù)庫使用命令恢復(fù)數(shù)據(jù)庫數(shù)據(jù)庫恢復(fù)命令為：RESTOREDATABASE/LOG。二、實驗內(nèi)容實驗1基本表的創(chuàng)建、數(shù)據(jù)插入i. 建立教學(xué)管理中的三個基本表：Students（SNO，SNAME，AGE，SEX）學(xué)生（學(xué)號，姓名，年齡，性別）Courses（CNO，CNAME，SCORE，PC＃）課程（課程號，課程名，學(xué)分，先行課號）SC（SNO，CNO，GRADE） 選修（學(xué)號，課程號，成績）（2）用INSERT命令輸入數(shù)據(jù)。表6基本表Students的數(shù)據(jù)：表7基本表Courses的數(shù)據(jù)S1LU20MC1數(shù)學(xué)4MS2YIN19MC2英語8MS3XU18FC3數(shù)據(jù)結(jié)構(gòu)4FS4QU18FC4數(shù)據(jù)庫3.5FS6PAN14MC5網(wǎng)絡(luò)4MS8DONG24M收集于網(wǎng)絡(luò)，如有侵權(quán)請聯(lián)系管理員刪除精品文檔表8基本表SC的數(shù)據(jù)（空格為未選修， NULL為選修但還未有成績）：SNOS1S2S3S4S6S8CNOC1859089848887C273NULL86827585C3888090NULLC48985NULL9288C573NULL87實驗2：數(shù)據(jù)查詢列出選修課程號為C2的學(xué)生學(xué)號與姓名。ii.檢索選修課程名為“數(shù)學(xué)”的學(xué)生學(xué)號與姓名。iii.檢索沒有選修C2課程的學(xué)生姓名與年齡。iv.檢索選修全部課程的學(xué)生姓名。實驗3：數(shù)據(jù)修改、刪除（1）把C2課程的非空成績提高10％。（2）在SC表中刪除課程名為“物理”的成績所對應(yīng)的元組。（3）在S和SC表中刪除學(xué)號為S8的所有數(shù)據(jù)。實驗4：視圖的操作（1）建立男生學(xué)生的視圖，屬性包括學(xué)號，姓名，選修課程名和成績。（2）在男生視圖中查詢平均成績大于80分的學(xué)生學(xué)號和姓名。實驗5：庫函數(shù)，授權(quán)控制（1）計算每個學(xué)生選修課程的門數(shù)、平均成績。（2）建立一個合法的用戶，將SC表的查詢權(quán)限授予該用戶。（3）使用GRANT語句，把對基本表students、Courses、SC的使用權(quán)限授予其他用戶。實驗6：數(shù)據(jù)庫的備份、恢復(fù)（1）使用完全備份將你的實驗數(shù)據(jù)庫備份到軟盤。（2）刪除你所建立的數(shù)據(jù)庫。（3）恢復(fù)你的數(shù)據(jù)庫。（4）在恢復(fù)后的數(shù)據(jù)庫上撤銷你建立的基本表和視圖。三、實驗結(jié)果及代碼實驗1基本表的創(chuàng)建、數(shù)據(jù)插入收集于網(wǎng)絡(luò)，如有侵權(quán)請聯(lián)系管理員刪除精品文檔i. 由上分析數(shù)據(jù)庫數(shù)據(jù)定義如下，下劃線為主鍵，Students（SNO，SNAME，AGE，SEX）Courses（CNO，CNAME，SCORE，PC＃）SC（SNO，CNO，GRADE） 外鍵為sno，cno代碼為：create database schuse sch;gocreate table students (sno char (9) primary key,sname char (20) not null,age char (3) ,sex char (6));createtablecourses(cnochar(9)primarykey,cnamechar(20)notnull,scoreint,pcchar(3));createtablesc(snochar(9)foreignkeyreferencesstudents(sno),cnochar(9),gradeint,foreignkey(cno)referencescourses(cno));（2）用INSERT命令輸入數(shù)據(jù)。根據(jù)表格輸入數(shù)據(jù)代碼為：usesch;goinsertintostudentsvalues('S1','LU',20,'M');insertintostudentsvalues('S2','YIN',19,'M');insertintostudentsvalues('S3','XU',18,'F');insertintostudentsvalues('S4','QU',18,'F');insertintostudentsvalues('S6','PAN',14,'M');insertintostudentsvalues('S8','DONG',24,'M');insertintoCoursesvalues('C1','數(shù)學(xué)',4,'M');insertintoCoursesvalues('C2','英語',8,'M');insertintoCoursesvalues('C3','數(shù)據(jù)結(jié)構(gòu)',4,'F');insertintoCoursesvalues('C4','數(shù)據(jù)庫',3.5,'F');insertintoCoursesvalues('C5','網(wǎng)絡(luò)',4,'M');insertintoSCvalues('S1','C1',85);insertintoSCvalues('S2','C1',90);insertintoSCvalues('S3','C1',89);insertintoSCvalues('S4','C1',84);insertintoSCvalues('S6','C1',88);insertintoSCvalues('S8','C1',87);收集于網(wǎng)絡(luò)，如有侵權(quán)請聯(lián)系管理員刪除精品文檔insertintoSCvalues('S1','C2',73);insertintoSCvalues('S2','C2',NULL);insertintoSCvalues('S3','C2',86);insertintoSCvalues('S4','C2',82);insertintoSCvalues('S6','C2',75);insertintoSCvalues('S8','C2',85);insertintoSCvalues('S1','C3',88);insertintoSCvalues('S2','C3',80);insertintoSCvalues('S6','C3',90);insertintoSCvalues('S8','C3',NULL);insertintoSCvalues('S1','C4',89);insertintoSCvalues('S2','C4',85);insertintoSCvalues('S4','C4',NULL);insertintoSCvalues('S6','C4',92);insertintoSCvalues('S8','C4',88);insertintoSCvalues('S1','C5',73);insertintoSCvalues('S2','C5',NULL);insertintoSCvalues('S8','C5',87);結(jié)果：其他均已成功輸入數(shù)據(jù)實驗2：數(shù)據(jù)查詢列出選修課程號為C2的學(xué)生學(xué)號與姓名。代碼為： 結(jié)果為：use sch;goselect sc.sno,snamefrom students ,scwhere o='C2' and sc.sno=students .sno收集于網(wǎng)絡(luò)，如有侵權(quán)請聯(lián)系管理員刪除精品文檔檢索選修課程名為“數(shù)學(xué)”的學(xué)生學(xué)號與姓名。代碼為： 結(jié)果為：use sch;goselect sc.sno,snamefrom students ,sc,courseswhere courses .cname='數(shù)學(xué)'and courses .cno=oand students .sno=sc.sno檢索沒有選修C2課程的學(xué)生姓名與年齡。代碼為： 輸出結(jié)果：use sch;goselect sname,agefrom studentswhere not exists( select *from scwhere o='c3' and sno=students .sno);檢索選修全部課程的學(xué)生姓名。代碼為： 輸出結(jié)果：use sch;goselect snamefrom studentswhere not exists( select *from courseswhere not exists(select *from scwhere sno=students .sno and cno=courses .cno));實驗3：數(shù)據(jù)修改、刪除（1）把C2課程的非空成績提高 10％。收集于網(wǎng)絡(luò)，如有侵權(quán)請聯(lián)系管理員刪除精品文檔代碼為：update scset grade =grade *1.1where o='c2' and o is not null查詢c2行代碼結(jié)果如下成績均提高 10%（2）在SC表中刪除課程名為“物理”的成績所對應(yīng)的元組。代碼為：delete from SCwhere cno in (select cnofrom courseswhere cname='物理')結(jié)果：影響行為 03）在S和SC表中刪除學(xué)號為S8的所有數(shù)據(jù)。代碼分別為：deletefromscwheresc.sno='s8'deletefromstudentswherestudents.sno='s8'實驗4：視圖的操作（1）建立男生學(xué)生的視圖，屬性包括學(xué)號，姓名，選修課程名和成績。代碼為：create view student_m (sno,sname,cname,grade )asselect students .sno,students .sname,cname,gradefrom sc,students ,courseswhere students .sno=sc.sno and courses .cno=o and sex='M'2）在男生視圖中查詢平均成績大于80分的學(xué)生學(xué)號和姓名。代碼為：select distinct students .sno,students .snamefrom student_m ,students收集于網(wǎng)絡(luò)，如有侵權(quán)請聯(lián)系管理員刪除精品文檔where student_m .sno=students .sno and grade >80結(jié)果為：（s8信息已在前面刪除）實驗5：庫函數(shù)，授權(quán)控制（1）計算每個學(xué)生選修課程的門數(shù)、平均成績。代碼為：select students .sno,students .sname,count (cno) 選修門數(shù),avg(grade ) 平均成績from students ,scwhere students .sno=sc.snogroup by students .sno,sname輸出結(jié)果：（2）建立一個合法的用戶，將 SC表的查詢權(quán)限授予該用戶。use schgoexec sp_addlogin sch,schuse schgoexec sp_grantdbaccess 'sch' ,'sch'GRANTSELECTONSCTOsch;收集于網(wǎng)絡(luò)，如有侵權(quán)請聯(lián)系管理員刪除精品文檔3）使用GRANT語句，把對基本表students、Courses、SC的使用權(quán)限授予其他用戶。useschgoGRANTallprivileges--授予權(quán)限ONCoursesTOguest;GRANTallprivileges--授予權(quán)限ONstudentsTOguest;GRANTallprivileges--授予權(quán)限ONscTOguest;實驗6：數(shù)據(jù)庫的備份、恢復(fù)（1）使用完全備份將你的實驗數(shù)據(jù)庫備份到軟盤。將數(shù)據(jù)庫ems完全備份到設(shè)備backupdevice_ems上，在驅(qū)動器D上建立backupdev子目錄執(zhí)行下面代碼：use schgoEXECsp_addumpdevice 'DISK' ,'backupdevice_sch' ,'d:\backupdev\sch.bak'BACKUP DATABASE sch TObackupdevice_sch結(jié)果：已為數(shù)據(jù)庫'sch' ，文件'sch'( 位于文件1上)處理了184 頁。已為數(shù)據(jù)庫'sch' ，文件'sch_log'( 位于文件1上)處理了2 頁。BACKUPDATABASE成功處理了186 頁，花費0.389 秒(3.728MB/ 秒)。成功執(zhí)行收集于網(wǎng)絡(luò)，如有侵權(quán)請聯(lián)系管理員刪除精品文檔將數(shù)據(jù)庫sch的日志備份到設(shè)備 backupdevice_sch上/*EXECsp_addumpdevice'D