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

：對象權限總結表對象

操 作表

SELECTUPDATEDELETEINSERTREFERENCE視圖

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