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

1、【精品文檔】如有侵權(quán)，請聯(lián)系網(wǎng)站刪除，僅供學(xué)習(xí)與交流數(shù)據(jù)庫實驗-華中科技大學(xué).精品文檔.數(shù)據(jù)庫實驗報告Xxxx2012-6-1一、實驗?zāi)康恼莆誗QL Server 2000 的工具使用掌握DDL的使用方法 掌握DML的使用方法掌握SELECT命令 使用方法掌握DCL的使用方法 掌握數(shù)據(jù)庫的備份和恢復(fù)二、實驗原理圖6 Server Manager 窗口1 SQL Server 2000的工具1） 服務(wù)管理器（Server Manager）圖7 服務(wù)管理器在任務(wù)欄中的狀態(tài)在進(jìn)行任何數(shù)據(jù)庫操作前，都必須啟動服務(wù)器，Server Manager可以方便啟動、停止、暫停本地或遠(yuǎn)程服務(wù)器。圖6 是打開的S

2、erver Manager 窗口。適當(dāng)配置窗口中的選項，點擊“啟動”按鈕即可啟動SQL Server服務(wù)器。SQL Server 2000 安裝以后，每次開機時，Windows都會自動啟動服務(wù)管理器。在任務(wù)欄上有一個圖標(biāo)表示服務(wù)管理器的狀態(tài)。如圖7所示。2） SQL Server 2000 查詢分析器（Query Analyzer）圖11 查詢分析器查詢分析器是一個重要工具，實驗中的所有SQL語言命令均需在查詢分析器中輸入、編輯運行。從Program SQL Server - Query Analyzer 可以打開查詢分析器，如圖11所示。它是一個多文檔程序，在其上可以同時打開多個查詢程序（窗

3、口），在查詢分析器的工具欄中的綠色4按鈕為執(zhí)行當(dāng)前窗口中SQL語句按鈕。點擊它即可逐行執(zhí)行。2.DDL使用方法 1） 數(shù)據(jù)庫創(chuàng)建在查詢分析器中執(zhí)行下列語句即可在默認(rèn)的設(shè)備上創(chuàng)建新的數(shù)據(jù)庫sch。CREATE DATABASE database_name 2） 基本表的建立創(chuàng)建基本表的命令為：CREATE TABLE table_name，在該命令中定義主碼和外碼時，可以使用列約束（Column Constraint）或表約束（Table Constraint）子句。創(chuàng)建基本表時，應(yīng)先選擇包含表的數(shù)據(jù)庫。3） 視圖的建立視圖是組成數(shù)據(jù)庫體系結(jié)構(gòu)三級模式兩級映像結(jié)構(gòu)中的外模式的基本單元，SQL-S

4、erver的視圖定義命令為： CREATE VIEW view-name AS SELECT statement視圖是用于定義終端用戶數(shù)據(jù)來源的。在視圖定義中可以使用復(fù)雜的SELECT命令。機視圖。3.DML使用方法SQL的DML包括插入（INSERT）、刪除(DELETE)、修改(UPDATE)等命令。DML命令的執(zhí)行是可能造成數(shù)據(jù)庫不一致的根源。因此，每一條語句在執(zhí)行前，SQL-Server都要驗證語句是否符合完整性要求，包括實體完整性、參照完整性、用戶定義完整性。1）INSERT命令SQL語言的插入命令：INSERT INTO table-name (column-list) VALUE

5、S(values-list)，可以完成數(shù)據(jù)輸入功能。注意：在執(zhí)行過程中，如果有一條語句出錯誤，再次執(zhí)行時，在出錯語句前面的語句就會被拒絕執(zhí)行。因為，在查詢分析器中的命令在執(zhí)行時按順序逐條執(zhí)行，當(dāng)執(zhí)行到出錯語句時停止執(zhí)行，此時，在出錯語句的前面各條語句的結(jié)果已經(jīng)存入表中，故當(dāng)重復(fù)執(zhí)行時，它們違反了實體完整性。2） DELETE命令SQL的刪除命令為：DELETE FROM table-name WHERE condition-expression;執(zhí)行DELETE命令后，系統(tǒng)會刪除滿足命令中條件表達(dá)式的所有元組。這種刪除只是邏輯的。因此，當(dāng)再次插入一個與被刪除的元組具有相同關(guān)鍵字的元組時，被認(rèn)為

6、違反了實體完整性。3） UPDATE命令 SQL的修改命令為：UPDATE table-name SET column-name = expression WHERE condition-expression;執(zhí)行UPDATE命令后，系統(tǒng)會按照SET子句修改滿足命令中條件表達(dá)式的所有元組。如果使用修改命令更新關(guān)鍵字的值，而該關(guān)鍵字在其他表中作為外碼存在時，操作可能違反參照完整性。系統(tǒng)將拒絕執(zhí)行。4） SELECT命令SELECT 命令是SQL語言中使用最頻繁的命令，其變化形式繁多，靈活運用SELECT 命令可以完成任意復(fù)雜的查詢要求。SELECT 命令的基本語法為:SELECT query_e

7、xpression|column-list|* FROM table_name_list|view_name_listWHERE condition-expressionGROUP BY HAVING condition-expressionORDER BY order_expression| column ASC|DESC,n;由于SELECT 命令非常復(fù)雜,上面的語法描述還無法完全表達(dá)，請參考Transaction SQL 的幫助。具體查詢方法參考教材。4.DCL 的使用方法1）SQL Server 登錄認(rèn)證 新登錄的創(chuàng)建新的登錄者可以是WINDOWS的用戶或用戶組，也可以是使用SQL S

8、erver認(rèn)證模式的登錄帳號，設(shè)定WINDOWS的用戶為SQL Server登錄者時使用sp_grantlogin。創(chuàng)建新的使用SQL Server認(rèn)證模式的登錄帳號時使用sp_addlogins。 登錄的刪除刪除基于使用SQL Server認(rèn)證模式的SQL Server的登錄可以使用sp_droplogin。但是，如果與該登錄匹配的數(shù)據(jù)庫用戶仍存在sysuser表中，則不能刪除該登錄賬號。刪除WINDOWS的用戶或用戶組時，使用sp_revokelogin，拒絕WINDOWS的用戶或用戶組連接到SQL Server時，使用sp_decnylogin。 登陸的查看sp_helplogins可以

9、用來顯示SQL Server的所有登錄者的信息。如圖29是顯示數(shù)據(jù)庫ems 的所有登錄者信息的代碼。2） 用戶管理SQL Server利用下列系統(tǒng)過程管理數(shù)據(jù)庫用戶：sp_adduser sp_grangdbaccess sp_dropuser Sp_dropuser sp_revokedbaccess sp_helpuser 。其中sp_adduser Sp_dropuser 是為了保持與以前版本相兼容，因此不主張使用，在SQL Server 2000中，建議使用sp_grangdbaccess sp_revokedbaccess 。 創(chuàng)建新數(shù)據(jù)庫用戶所有的數(shù)據(jù)庫用戶（除了guest）均必須

10、與某一登錄賬號相匹配，因此在使用系統(tǒng)過程創(chuàng)建新數(shù)據(jù)庫用戶時，不但要指出新數(shù)據(jù)庫用戶的名稱，還必須指出一個已經(jīng)存在的登錄賬號。系統(tǒng)過程sp_grantdbaccess用于為SQL Server 登錄用戶或NT用戶或用戶組建立相匹配的數(shù)據(jù)庫用戶帳號。 刪除數(shù)據(jù)庫用戶系統(tǒng)過程sp_revokedbaccess用于將數(shù)據(jù)庫用戶從當(dāng)前數(shù)據(jù)庫中刪除，刪除后與其匹配的登錄即無法再訪問該數(shù)據(jù)庫了。 查看數(shù)據(jù)庫用戶信息sp_helpuser可用來顯示當(dāng)前數(shù)據(jù)庫的指定用戶信息。3） 用戶授權(quán)管理對于登錄到SQL Server的合法數(shù)據(jù)庫用戶，必須獲得對數(shù)據(jù)庫操作的授權(quán)。在SQL Server中包括兩種類型的權(quán)限，

11、即對象權(quán)限和語句權(quán)限。對象權(quán)限總是針對表、視圖、存儲過程而言，它決定了能對這些對象執(zhí)行哪些操作（如UPDATE DELETE INSERT SELECT EXECUTE）。不同類型的對象支持不同類型的操作，表3為各種對象的可能操作列舉。表3 ：對象權(quán)限總結(jié)表對 象操 作表SELECT UPDATE DELETE INSERT REFERENCE 視圖SELECT UPDATE DELETE INSERT 存儲過程EXECUTE列UPDATE SELECT 表4 語句權(quán)限總結(jié)表語句含義CREATE DTATBASE創(chuàng)建數(shù)據(jù)庫CREATE TABLE創(chuàng)建表CREATE VIEW創(chuàng)建視圖CREATE

12、 RULE創(chuàng)建規(guī)則CREATE DEFAULT創(chuàng)建缺省CREATE PROCEDURE創(chuàng)建存儲過程BACKUP DATABASE備份數(shù)據(jù)庫BACKUP LOG創(chuàng)建事務(wù)日志語句權(quán)限指數(shù)據(jù)庫用戶執(zhí)行某種語句的操作權(quán)，如創(chuàng)建數(shù)據(jù)庫、表、存儲過程等。這些語句雖然（如CREATE 命令）包含有操作對象，但這些對象在操作前并不存在于數(shù)據(jù)庫中。表4為所有權(quán)語句清單。5.數(shù)據(jù)庫的備份和恢復(fù)備份是指對SQL Server 數(shù)據(jù)庫或事務(wù)日志進(jìn)行拷貝，如果數(shù)據(jù)庫因意外而損壞，備份文件可以用來恢復(fù)數(shù)據(jù)庫。SQL Server 2000 中有四種備份類型，分別是：數(shù)據(jù)庫備份、事務(wù)日志備份、差異備份、文件和文件組備份。在

13、使用是可以根據(jù)實際情況選擇一種或幾種的混合應(yīng)用。1)備份設(shè)備在進(jìn)行備份以前必須創(chuàng)建備份設(shè)備。備份設(shè)備是用來存儲數(shù)據(jù)庫、事務(wù)日志、文件和文件組備份的存儲介質(zhì)?？梢允谴疟P、磁帶或管道。SQL Server 2000 提供了兩種創(chuàng)建備份的途徑，即可視化方式和命令方式。用命令備份數(shù)據(jù)庫使用BACKUP 命令進(jìn)行備份操作。l 備份完整數(shù)據(jù)庫/* EXEC sp_addumpdevice DISK,backupdevice_name, d:backupdevems.bak目標(biāo)路徑*/BACKUP DATABASE database TO backupdevice_name2)恢復(fù)數(shù)據(jù)庫使用命令恢復(fù)數(shù)據(jù)庫數(shù)

14、據(jù)庫恢復(fù)命令為：RESTORE DATABASE/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ù)C1 數(shù)學(xué) 4 MC2 英語 8 MC3 數(shù)據(jù)結(jié)構(gòu) 4 FC4 數(shù)據(jù)庫 3.5 FC5 網(wǎng)絡(luò) 4 MS1 LU 20 M

15、S2 YIN 19 MS3 XU 18 FS4 QU 18 FS6 PAN 14 MS8 DONG 24 M表8 基本表SC的數(shù)據(jù)（空格為未選修，NULL為選修但還未有成績）： SNOCNOS1S2S3S4S6S8C1859089848887C273NULL86827585C3888090NULLC48985NULL9288C573NULL87實驗2：數(shù)據(jù)查詢(1) 列出選修課程號為C2的學(xué)生學(xué)號與姓名。ii. 檢索選修課程名為“數(shù)學(xué)”的學(xué)生學(xué)號與姓名。iii. 檢索沒有選修C2課程的學(xué)生姓名與年齡。iv. 檢索選修全部課程的學(xué)生姓名。實驗3：數(shù)據(jù)修改、刪除（1） 把C2課程的非空成績提高10

16、。（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ù)你的

17、數(shù)據(jù)庫。（4） 在恢復(fù)后的數(shù)據(jù)庫上撤銷你建立的基本表和視圖。三、實驗結(jié)果及代碼實驗1 基本表的創(chuàng)建、數(shù)據(jù)插入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);create

18、table courses(cno char(9) primary key, cname char(20) not null, score int , pc char(3);create table sc(sno char(9) foreign key references students(sno), cno char(9), grade int, foreign key(cno) references courses(cno);（2）用INSERT 命令輸入數(shù)據(jù)。 根據(jù)表格輸入數(shù)據(jù)代碼為：use sch;goinsert into students values(S1,LU,20,M);i

19、nsert into students values(S2,YIN,19,M);insert into students values(S3,XU,18,F);insert into students values(S4,QU,18,F);insert into students values(S6,PAN,14,M);insert into students values(S8,DONG,24,M);insert into Courses values(C1 ,數(shù)學(xué) , 4 , M);insert into Courses values(C2 ,英語 , 8 , M);insert into

20、 Courses values(C3 ,數(shù)據(jù)結(jié)構(gòu) , 4 , F);insert into Courses values(C4 ,數(shù)據(jù)庫 , 3.5 , F);insert into Courses values(C5 ,網(wǎng)絡(luò) , 4 , M);insert into SC values(S1 ,C1 , 85);insert into SC values(S2 ,C1 , 90);insert into SC values(S3 ,C1 , 89);insert into SC values(S4 ,C1 , 84);insert into SC values(S6 ,C1 , 88);in

21、sert into SC values(S8 ,C1 , 87);insert into SC values(S1 ,C2 , 73);insert into SC values(S2 ,C2 , NULL);insert into SC values(S3 ,C2 , 86);insert into SC values(S4 ,C2 , 82);insert into SC values(S6 ,C2 , 75);insert into SC values(S8 ,C2 , 85);insert into SC values(S1 ,C3 , 88);insert into SC value

22、s(S2 ,C3 , 80);insert into SC values(S6 ,C3 , 90);insert into SC values(S8 ,C3 , NULL);insert into SC values(S1 ,C4 , 89);insert into SC values(S2 ,C4 , 85);insert into SC values(S4 ,C4 , NULL);insert into SC values(S6 ,C4 , 92);insert into SC values(S8 ,C4 , 88);insert into SC values(S1 ,C5 , 73);i

23、nsert into SC values(S2 ,C5 , NULL);insert into SC values(S8 ,C5 , 87);結(jié)果：其他均已成功輸入數(shù)據(jù)實驗2：數(shù)據(jù)查詢(1) 列出選修課程號為C2的學(xué)生學(xué)號與姓名。 代碼為： 結(jié)果為：use sch;goselect sc.sno,sname from students,scwhere o=C2 and sc.sno=students.sno(2) 檢索選修課程名為“數(shù)學(xué)”的學(xué)生學(xué)號與姓名。代碼為： 結(jié)果為：use sch;goselect sc.sno,sname from students,sc,courseswhere a

24、me=數(shù)學(xué) and o=o and students.sno=sc.sno(3) 檢索沒有選修C2課程的學(xué)生姓名與年齡。代碼為： 輸出結(jié)果：use sch;goselect sname,agefrom studentswhere not exists(select * from sc where o=c3 and sno=students.sno);(4) 檢索選修全部課程的學(xué)生姓名。代碼為： 輸出結(jié)果：use sch;goselect snamefrom studentswhere not exists(select * from courses where not exists( sele

25、ct * from sc where sno=students.sno and cno=o);實驗3：數(shù)據(jù)修改、刪除（1） 把C2課程的非空成績提高10。代碼為：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 cno from courses where cname=物理)結(jié)果：影響行為0（3） 在S和SC表中刪除學(xué)號為S8的所有數(shù)據(jù)。代碼分別為：delete

26、 from scwhere sc.sno=s8delete from studentswhere students.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 o=o and sex=M（2） 在男生視圖中查詢平均成績大于80分的學(xué)生學(xué)號和姓名。代

27、碼為：select distinct students.sno,students.snamefrom student_m,studentswhere student_m.sno=students.sno and grade80結(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.sno group by students.sno,sn

28、ame輸出結(jié)果：（2） 建立一個合法的用戶，將SC表的查詢權(quán)限授予該用戶。use schgo exec sp_addlogin sch,schuse schgoexec sp_grantdbaccess sch,schGRANT SELECTON SCTO sch;（3） 使用GRANT語句，把對基本表students、Courses、SC的使用權(quán)限授予其他用戶。use schgoGRANT all privileges-授予權(quán)限ON CoursesTO guest;GRANT all privileges-授予權(quán)限ON studentsTO guest;GRANT all privilege

29、s-授予權(quán)限ON scTO guest;實驗6：數(shù)據(jù)庫的備份、恢復(fù)（1） 使用完全備份將你的實驗數(shù)據(jù)庫備份到軟盤。將數(shù)據(jù)庫ems完全備份到設(shè)備backupdevice_ems上，在驅(qū)動器D上建立backupdev 子目錄執(zhí)行下面代碼：use schgo EXEC sp_addumpdevice DISK,backupdevice_sch, d:backupdevsch.bakBACKUP DATABASE sch TO backupdevice_sch結(jié)果：已為數(shù)據(jù)庫sch，文件sch (位于文件1 上)處理了184 頁。已為數(shù)據(jù)庫sch，文件sch_log (位于文件1 上)處理了2 頁。BACKUP DATABASE 成功處理了186 頁，花費0.389 秒(3.728 MB/秒)。成功執(zhí)行將數(shù)據(jù)庫sch的日志備份到設(shè)備backupdevice_sch上/* EXEC sp_addumpdevice DI