資料庫實驗指導書+實驗報告（2015）_基礎/彙編/編譯/組成原理

預備實驗（選做）

實驗名稱：SQL Server 2005的安裝與系統設定

 

實驗目的和要求：掌握SQL Server 2005的安裝和配置方法，熟悉SQL Server的系統內容，主要是對“查詢分析器”和“企業管理器”的熟悉和使用。

實驗裝置：

（１） 作業系統為WINDOWS xp/ 2003 server的電腦一台；

（２） SQL Server 2005標準版安裝軟體；

實驗內容和步驟：

安裝SQL Server 2005：將安裝光碟片放入CD－ROM ，SQL Server 2005的安裝光碟片共有2張，先開啟第一張，點“伺服器組件、工具、聯機叢書和樣本(C)”

         

                                                          

                                                      

                                           

此步驟系統配置檢查很重要，14個項目裡面如果有1項有錯誤或者警告，整個SQL Server 2005都將不正常。

 

安裝SQL Server 2005：將安裝光碟片放入CD－ROM，或者從互連網下載SQL Server 2005 開發版安裝程式，雙擊servers目錄下的setup.exe程式，按提示進行安裝，一般需要人工進行幹預的有：

 

選擇安裝類型和安裝路徑：安裝類型有：典型安裝、最小安裝、和自訂安裝。安裝路徑是指SQL Server的系統檔案和資料檔案的安裝位置。預設情況下“安裝類型”是典型安裝，“安裝路徑”是作業系統設定的“Program Files”檔案夾。你可以自行改變，初次安裝最好不要改變他，按預設情況使用；

點擊“進階”按鈕，可以進行手工選擇：

 

配置啟動服務的帳號：有兩類使用者帳號：一類是與Windows作業系統的整合帳號，一類是混合帳號。選擇第一類帳號進行安裝；

1． 設定管理員端網路程式庫：SQL Server支援多種網路程式庫，這些網路程式庫必須與作業系統的網路通訊協定共同工作，才能實現客戶機與資料庫伺服器的通訊。安裝完成後，可以通過作業系統的開始菜單操作：“開始”―》SQL Server”－》“資料庫伺服器網路設定”進行配置；

2． 配置用戶端網路程式庫：客戶機要與資料庫伺服器通訊，必須安裝有與伺服器網路程式庫一樣的網路程式庫。可以通過作業系統的開始菜單操作：“開始”―》SQL Server”－》“用戶端網路設定”進行配置；

3． 使用SQL Server 2005管理平台（SQL Server Management Studio），它包含了SQL Server 2000企業管理器（Enterprise Manager），以及查詢分析器（Query Analyzer）等方面的功能。（1）瞭解管理器的介面、菜單結構以及各項功能；（2）瞭解“資料庫伺服器”的屬性配置；（3）瞭解“資料庫”的屬性配置；（4）初步瞭解資料庫、表、視圖、使用者、安全管理，複製等概念；（5）怎樣選擇不同的資料庫；（3）怎樣查看資料庫的結構；（3）查詢分析器的初步使用。

實驗思考：

（１） 假如伺服器端網路程式庫設定“TCP/IP”協議的連接埠為：2866，那麼在用戶端的網路程式庫中應該怎樣設定TCP/IP 連接埠。

（２） 管理平台（SQL Server Management Studio）的作用是什麼。

 

實驗一 SQL資料定義

實驗名稱：SQL資料定義

實驗目的和要求：

（1）使用管理平台（SQL Server Management Studio）用CREATE、DROP、ALTER語句建立和刪除資料庫，建立、刪除、更新基本表等。

（2）通過實驗，熟練掌握SQL資料定義語言 (Data Definition Language)。

（3）認真書寫實驗報告，如實填寫各項實驗內容。

實驗內容和步驟:

（1）利用管理平台（SQL Server Management Studio）建立學生課程資料庫。

用按一下滑鼠“開始”後，將滑鼠分別指向程式/Microsoft SQL server 2005,在Microsoft SQL server 2005下單擊SQL Server Management Studio進入管理平台；點擊工具列上的“建立查詢”，在右邊視窗中鍵入CREATE DATABASE S_C命令並儲存，檔案名稱為s_c.sql。用按一下滑鼠工作列的“。執行”按鈕運行該命令，這樣，S_C資料庫就建成了。

（2）利用管理平台建立基本表student(sno,sname,ssex,sage,sdept)、course(cno,cname,ccpno,ccredit)、sc(sno,cno,grade)。在管理平台工作列的資料庫選為S_C。

A．建立查詢，在表單下鍵入如下命令：

CREATE TABLE Student (sno   CHAR(9) PRIMARY KEY,                             sname  CHAR(20) UNIQUE,            ssex    CHAR(2),            sage   SMALLINT,            sdept  CHAR(20)           ); 

然後儲存，檔案名稱為student.sql。用按一下滑鼠工作列的“。執行”按鈕運行該命令。

B．建立查詢，在表單下鍵入如下命令：

CREATE TABLE  Course( cno  CHAR(4) PRIMARY KEY,                 cname  CHAR(40),                            cpno     CHAR(4) ,                                                      ccredit  SMALLINT,                FOREIGN KEY (cpno) REFERENCES  Course(cno)            );

儲存，檔案名稱為Course.sql，用按一下滑鼠工作列的“。執行”按鈕運行該命令。觀察結果。

C．建立查詢，在表單下鍵入如下命令：

CREATE TABLE  SC (sno  CHAR(9),       cno  CHAR(4),       grade    SMALLINT,       PRIMARY KEY (sno,cno),                      FOREIGN KEY (sno) REFERENCES Student(sno),            FOREIGN KEY (cno) REFERENCES Course(cno)               );

儲存，檔案名稱為sc.sql, 用按一下滑鼠工作列的“。執行”按鈕運行該命令。

這樣在S_C資料庫中建成了三個表：Student,Course,SC.

D．在視窗下分別鍵入DROP TABLE Student及DROP TABLE SC命令, 用按一下滑鼠工作列的“。執行”按鈕運行該命令，觀察結果。

如果先執行DROP TABLE Student 則會出現如下錯誤：

無法刪除對象'Student'，因為該對象正由一個FOREIGN KEY 約束引用。

E. 再重新運行student.sql及 sc.sql,觀察結果。

 

（3）利用查詢分析器修改student表。

   建立查詢，在查詢分析器表單下分別鍵入如下命令：

   A．ALTER TABLE Student ADD scome DATETIME;

   B．ALTER TABLE Student ALTER COLUMN sdept VARCHAR(20);

   C．ALTER TABLE Student DROP COLUMN sage;

  運行並觀察結果。

 

（4）利用管理平台（SQL Server Management Studio）向表中輸入資料。按右鍵student表，點擊“開啟表”， 向表中輸入資料。用相同的方法向Course、sc表輸入資料。

 

實驗二 SQL資料查詢

實驗名稱：SQL資料查詢

實驗目的和要求：

（1）使用SQL查詢分析器用SELECT語句進行單表的資料查詢，分別使用以下方法，掌握單表的各種查詢方法：

A．選擇表中的若干元組

B. 消除取值重複的行

C. 查詢滿足單條件和多重條件的元組

D．使用萬用字元（%和_ ） 

E．對查詢結果排序和分組

F．使用彙總函式

（2）使用SQL查詢分析器用SELECT語句進行多表的資料查詢，分別使用以下方法，目的是掌握多表的各種查詢方法和巢狀查詢方法：

A．使用串連、多條件等：選擇多表中的若干元組

B. 使用單表自身串連。

C. 使用嵌套串連。

D．使用帶有各種謂詞的子查詢。

E．使用集合查詢。

（3）通過實驗，掌握上述查詢方法。

（4）認真書寫實驗報告，如實填寫各項實驗內容。

 

實驗內容和步驟：

1．用實驗1的方法建立資料庫、表，輸入資料。

2．在管理平台（SQL Server Management Studio）下，建立查詢，用SELECT陳述式完成下列單表查詢：

（１） 查詢全體學生的詳細記錄；

              

          SELECT *          FROM Student,Course,SC;

（２） 查詢考試成績不及格的學生的學號；

      

             SELECT sno             FROM SC             WHERE grade<60;

 

（３） 查資訊系（IS）、數學系（MA）和電腦科學系（CS）的學生的姓名和性別；

                

SELECT sname,ssexFROM StudentWHERE sdept='IS' OR sdept='CS' OR sdept='MA';

（４） 查所有姓劉的學生的姓名、學號和性別；

SELECT sname,sno,ssexFROM StudentWHERE sname LIKE '劉%'; 

（５） 查 DB_Design 課程的課程號和學分；

 SELECT cno,ccredit FROM Course WHERE cname='DB_Design';

（６） 查詢選修了3號課程的學生的學號及其成績，查詢結果按分數的降序排列；

     

 SELECT sno,gradeFROM SCWHERE cno='3'ORDER BY grade DESC;

（７） 查詢學習1號課程的學生最高分數；      

SELECT MAX(grade)FROM SCWHERE cno='1' ;

                             

（８） 查詢學號為“05001”的學生選修了多少門課程。

SELECT count(cno)FROM SCWHERE sno='05001';

（９） 查詢所有學生選修的課程門數。

     

SELECT count(cno)FROM SC,StudentWHERE SC.sno=Student.sno;

     

3．在管理平台（SQL Server Management Studio）查詢時段下用SELECT陳述式完成下列多表查詢：

（１） 查詢每個學生及其選修課程的情況。

           

SELECT Student.sno,SC.cnoFROM SC,StudentWHERE SC.sno=Student.sno;

（２） 查詢選修2號課程且成績在90分以上的所有學生的學號、姓名。

       

SELECT Student.sno,snameFROM SC,StudentWHERE SC.sno=Student.sno  AND cno='2' AND grade>90 ;

（３） 查詢每一門課的間接先修課（即先修課的先修課）。

    

（４） 使用巢狀查詢：查詢其他系中比資訊系任意一個(其中某一個)學生年齡小的學生姓名和年齡。

        

SELECT sname,sageFROM StudentWHERE sdept='MA' OR sdept='CS' OR sdept='TU'GROUP BY sname,sageHAVING  sage <ANY(SELECT sage                  FROM Student                  WHERE sdept='IS');                     SELECT sname,sageFROM StudentWHERE sage < ANY(SELECT sage                 FROM Student                  WHERE sdept='IS') AND sdept<>'IS';

（５） 使用帶有EXISTS謂詞的子查詢：查詢所有選修了1號課程的學生姓名。

   

SELECT snameFROM   Student,SCWHERE SC.sno=Student.sno AND cno='1'; SELECT snameFROM   StudentWHERE   EXISTS(SELECT *               FROM SC               WHERE sno=Student.sno AND cno='1');    

       

（６） 查詢選修了全部課程的學生姓名。

     

SELECT snameFROM  StudentWHERE NOT EXISTS (SELECT *                  FROM Course                  WHERE NOT EXISTS(SELECT *                  FROM SC                   WHERE sno=Student.sno AND cno=Course.cno ));

（７） 使用集合查詢：查詢選修了課程1或者選修了課程2的學生。

 

(SELECT snameFROM  Student,SCWHERE SC.sno=Student.sno AND cno='1')UNION(SELECT snameFROM  Student,SCWHERE SC.sno=Student.sno AND cno='2');

實驗步驟略。 

提示：

SELECT語句的一般格式為：

SELECT〈列名〉[{，〈列名〉}]

FROM〈表名或視圖名〉[{，〈表名或視圖名〉}]

[WHERE〈檢索條件〉]

[GROUP BY <列名1>[HAVING <條件運算式>]]

[ORDER BY <列名2>[ASC|DESC]];

  實驗三 SQL資料更新

實驗名稱：SQL資料更新

實驗目的和要求：

（1）使用SQL查詢分析器用INSERT、UPDATE、DELETE語句進行資料更新。

（2）通過實驗，掌握使用SQL語言對資料更新的方法。

（3）認真書寫實驗報告，如實填寫各項實驗內容。

 

實驗內容和步驟:

（1）利用查詢分析器用INSERT語句向表中插入資料。

A．將一個新學生記錄（學號：95020；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。       

INSERT StudentVALUES (95020,'陳冬','男',18,'IS');

 B．對每一個系，求學生的平均年齡，並把結果存入資料庫。

              CREATE TABLE  AVG_sage(                                   sdept CHAR(20),                                   avg_sage  REAL);                                    INSERT AVG_sage                                   SELECT sdept,AVG(sage)                                    FROM  Student                                   WHERE sdept IN (SELECT sdept FROM Student )                                    GROUP BY sdept;        

C．另建一個學生表Student_1,其結構與Student表結構一樣，在Student_1表中輸入元組。然後，把Student_1表的內容插入到Student表中。

     

   CREATE TABLE Student_1 (sno   CHAR(9) PRIMARY KEY,                                             sname  CHAR(20) UNIQUE,                            ssex    CHAR(2),                            sage   SMALLINT,                            sdept  CHAR(20)                       );    INSERT INTO Student           TABLE  Student_1;(結果不對。)（課本的語句錯誤）             INSERT  INTO  Student                       SELECT Student_1.*                        FROM Student_1;

分別運行，觀察結果。

（2）利用查詢分析器用UPDATE語句更改表中資料。

A．將學生05001的年齡改為22歲。

      

UPDATE StudentSET sage=22WHERE sno=05001;

  B．將所有學生的年齡增加1歲。

UPDATE StudentSET sage=sage+1;(7 行受影響)

C．將電腦科學系全體學生的成績置零。

       

UPDATE SCSET grade=0WHERE sno IN (SELECT SC.sno                          FROM Student,SC                    WHERE Student.sno=SC.sno AND sdept='CS');

分別運行，觀察結果。

 

（3）利用查詢分析器用DELETE語句刪除表中資料。

A．刪除學號為95019的學生記錄。

設定了Student、SC中同時含有95019學號記錄的情況：

錯誤執行個體：

DELETE  FROM   * 或者 Student，SCWHERE sno='95019'

（注意：DELETE語句只能從一個基本表中刪除資料）

DELETE  FROM   StudentWHERE sno='95019'DELETE FROM SCWHERE sno='95019';  DELETE 語句與REFERENCE 約束"FK__SC__sno__03317E3D"衝突。該衝突發生於資料庫"S_C"，表"dbo.SC", column 'sno'。語句已終止。(1 行受影響) 因為忽略了主外鍵的依賴關係，此種刪除順序使得只刪除了SC表中的記錄，Student表的沒有刪掉。      DELETE FROM SCWHERE sno='95019'DELETE  FROM   StudentWHERE sno='95019';

B．刪除所有的學生選課記錄。

 DELETE  FROM SC;

C．刪除電腦科學系所有學生的選課記錄。

DELETE FROM  SCWHERE   sno  IN (SELECT SC.sno                 FROM Student,SC                 WHERE SC.sno=Student.sno  AND  sdept='CS');

分別運行，觀察結果。

 

實驗步驟略。

 

提示：

資料插入格式：

（1）單元組的插入

    INSERT  INTO  基本表名 [（列名表）] VALUES（元組值）

（2）子查詢結果的插入

    INSERT  INTO  基本表名 [(列名表)]＜SELECT查詢語句＞

     

資料修改格式：

UPDATE  基本表名

SET 列名=值運算式[，列名=值運算式…] 

[WHERE 條件運算式]

 

資料刪除格式：

DELETE FROM基本表名

[WHERE 條件運算式]

 

實驗四 SQL的視圖操作

實驗名稱：SQL的視圖操作

實驗目的和要求：

（1）使用SQL查詢分析器用CREATE、DROP語句建立和刪除視圖，使用INSERT、UPDATE、DELETE語句更新視圖。

（2）通過實驗，掌握使用SQL語言對SQL的視圖操作的方法。

（3）認真書寫實驗報告，如實填寫各項實驗內容。

 

實驗內容和步驟:

（1）用查詢分析器用CREATE語句建立視圖：

對於教學資料庫中基本表S、SC、C，使用者經常要用到Sno、SNAME、CNAME和GRADE等列的資料，那麼可用下列語句建立視圖：

CREATE VIEW STUDENT_GRADE  as

select STUDENT.Sno,SNAME,CNAME,GRADE from STUDENT,sc,course where STUDENT.sno=sc.sno and sc.cno=course.cno

（2）利用查詢分析器用INSERT、UPDATE、DELETE語句更新該視圖。

（3）撤銷STUDENT_GRADE視圖。

（4）建立其他視圖。

運行並觀察結果。 

 

實驗步驟略。

 

思考題：什麼情況下才能對視圖作更新操作。

提示：

視圖的建立， 建立視圖可用“CREATE VIEW”語句實現。其句法如下：

CREATE VIEW <視圖名>（列表名）

AS <SELECT 查詢語句>

 

視圖的撤銷

在視圖不需要時，可以用“DROP VIEW”語句把其從系統中撤銷。其句法如下：DROP VIEW 視圖名

 

視圖的更新語句與基本表的更新語句是一樣的。

 

(1) 若視圖是基於多個表使用聯結操作而匯出的，那麼對這個視圖執行更新操作時，每次只能影響其中的一個表。

(2) 若視圖匯出時包含有分組和彙總操作，則不允許對這個視圖執行更新操作。

(3) 若視圖是從一個表經選擇、投影而匯出的，並在視圖中包含了表的主鍵字或某個候選索引鍵，這類別檢視稱為‘行列子集視圖’。對這類別檢視可執行更新操作。

定義可更新視圖時加上WITH CHECK OPTION短語，表示強制在視圖上的所有資料更新語句都必須符合由select查詢語句所設定的準則。

由於視圖不一定包括表中的所有欄位，所以在插入記錄時可能會遇到問題。視圖中那些沒有出現的欄位無法顯式插入資料，假如這些欄位不接受系統指派的null值，那麼插入操作將失敗。但這類別檢視仍然可以用於修改和刪除操作。