數(shù)據(jù)庫實驗實驗報告

1、淮海工學(xué)院計算機工程學(xué)院實驗報告書課程名： 數(shù)據(jù)庫原理及應(yīng)用 題 目： T-SQL高級應(yīng)用 班 級： 軟件132 學(xué) 號： 2013122907 姓 名： 孫瑩瑩 評語：成績： 指導(dǎo)教師： 批閱時間： 年 月 日 數(shù)據(jù)庫原理及應(yīng)用 實驗報告 - 13 一 目的與要求1. 掌握復(fù)雜查詢的使用方法；2. 掌握多表連接的方法；3. 掌握SELECT語句在多表查詢中的應(yīng)用。4. 掌握數(shù)據(jù)匯總、外連接查詢的使用方法；5. 掌握事務(wù)定義的一般方法二 實驗內(nèi)容1 給出教材14-16的程序運行結(jié)果。2 編寫程序，查詢“1033”班的最高分的學(xué)生的學(xué)號、姓名、班號、課程號和分數(shù)。3 編寫程序，創(chuàng)建一個新表，包含

2、所有學(xué)生的姓名、課程名和分數(shù)，并以姓名排序。4 編寫程序，輸出每個班最高分的課程名和分數(shù)。5 在上次實驗建立的factory數(shù)據(jù)庫的基礎(chǔ)上，完成如下各題：l 刪除factory數(shù)據(jù)庫上各個表之間建立的關(guān)系；l 顯示各職工的工資記錄和相應(yīng)的工資小計。l 按性別和部門名的所有組合方式列出相應(yīng)的平均工資。l 在worker表中使用以下語句插入一個職工記錄：insert into worker values(20,陳立，女，55/03/08，1，75/10/10，4)，在depart表中使用以下語句插入一個部門記錄：insert into depart values(5,設(shè)備處)。再對worker和d

3、epart表進行全外連接顯示職工的職工號，姓名和部門名。然后刪除這兩個插入的記錄。l 顯示最高工資的職工的職工號、姓名、部門名、工資發(fā)放日期和工資。l 顯示最高工資的職工所在的部門名。l 顯示所有平均工資低于全部職工平均工資的職工的職工號和姓名。l 先顯示worker表中的職工人數(shù)，開始一個事務(wù)，插入一個職工記錄，再顯示worker表中的職工人數(shù)，回滾該事務(wù)，最后顯示worker表中的職工人數(shù)。三 實驗步驟刪除factory數(shù)據(jù)庫上各個表之間建立的關(guān)系的操作步驟如下： 啟動SQL Server管理控制器。 在“對象資源管理器”中展開LCB-PC服務(wù)器節(jié)點。 展開“數(shù)據(jù)庫”節(jié)點。 選中，將其展開

4、。 展開“數(shù)據(jù)庫關(guān)系圖”節(jié)點。 選中dbo.Diagram_1，右擊，在出現(xiàn)的快捷菜單中選擇“修改”命令，如圖5.1所示。 在數(shù)據(jù)庫關(guān)系圖中，選擇表示要從關(guān)系圖中刪除的關(guān)系的連接線（對于兩條連線均進行的操作）。 右擊關(guān)系線，從快捷菜單中選擇“從數(shù)據(jù)庫中刪除關(guān)系”命令。 出現(xiàn)一個消息框，提示確認刪除。單擊“是”按鈕。 在出現(xiàn)的對話框中單擊“是”按鈕保存所做的修改。這樣就將worker表和depart表以及worker表和salary表之間的關(guān)系刪除了。四 測試數(shù)據(jù)與實驗結(jié)果第1-14題圖第1-15題圖第1-16題圖第2題圖第3題圖第4題圖圖5.1第5-2題圖第5-3題圖第5-4題圖第5-5題圖第

5、5-6題圖第5-7題圖第5-8題圖第5-9題圖第5-10題圖五 結(jié)果分析與實驗體會在FROM子句中指定連接條件，有助于將這些連接條件與WHERE子句中的其他搜索條件分開，指定連接時建議使用這種方法。如果某個表只出現(xiàn)在子查詢中而不出現(xiàn)在外部查詢中，那么該表中的列就無法包含在輸出中（外部查詢的選擇列表）。使用EXISTS引入的子查詢在以下幾方面與其他子查詢略有不同：lEXISTS關(guān)鍵字前面沒有列名，常量或其他表達式。l由EXISTS引入的子查詢的選擇列表通常都是由星號（*）組成。由于只是測試是否存在符合子查詢中指定條件的行，所以不必列出列名。六 源碼1use schoolselect 學(xué)號,課程號

6、,分數(shù)from scorewhere 學(xué)號 in(103,105)order by 學(xué)號compute avg(分數(shù)) by 學(xué)號go2use schoolgoselect student.班號,course.課程名,avg(score.分數(shù))as'平均分'from student,course,scorewhere student.學(xué)號=score.學(xué)號 and course.課程號=score.課程號group by student.班號,course.課程名 with cubego3use schoolgobegin transaction Mytran insert i

7、nto teacher values('999','張英','男','1960/03/05','教授','計算機系')save transaction Mytran insert into teacher values('888','胡麗','男','1982/08/04','副教授','電子工程系')rollback transaction Mytran commit transaction gosel

8、ect * from teachergodelete teacher where 編號='999'go4use schoolselect student.學(xué)號,student.姓名,student.班號,score.課程號,score.分數(shù)from student,scorewhere 分數(shù) in (select max(分數(shù)) from score where 班號='1033' and student.學(xué)號=score.學(xué)號)5USE schoolgoSELECT s.姓名,c.課程名,sc.分數(shù)INTO studFROM student s,course

9、c,score scWHERE s.學(xué)號=sc.學(xué)號 and c.課程號=sc.課程號 and sc.分數(shù) is not nullORDER BY s.姓名SELECT * FROM studGo6USE schoolGOSELECT 班號,課程名,MAX(分數(shù)) 分數(shù)FROM (SELECT s.學(xué)號,s.姓名,s.班號,c.課程名,sc.分數(shù) FROM student s,course c,score sc WHERE s.學(xué)號=sc.學(xué)號 AND c.課程號=sc.課程號 AND 分數(shù) IS NOT NULL) TGROUP BY 班號,課程名ORDER BY 班號GO7USE fact

10、oryGOSELECT worker.職工號,worker.姓名,salary.工資FROM worker,salaryWHERE worker.職工號=salary.職工號ORDER BY worker.職工號,worker.姓名COMPUTE SUM(salary.工資) BY worker.職工號 GO8USE factoryGOSELECT worker.性別,depart.部門名,AVG(salary.工資) AS '平均工資'FROM worker,depart,salaryWHERE worker.職工號=salary.職工號 AND worker.部門號=dep

11、art.部門號GROUP BY worker.性別,depart.部門名 WITH CUBE GO9USE factoryGOINSERT INTO worker VALUES('20','陳立','女','55/03/08',1,'75/10/10',4)GOINSERT INTO depart VALUES(5,'設(shè)備處')GOSELECT worker.職工號,worker.姓名,depart.部門名FROM worker FULL JOIN departON(worker.部門號=depar

12、t.部門號)ORDER BY worker.職工號GODELETE FROM worker WHERE 職工號='20'GODELETE FROM depart WHERE 部門號=5GO10USE factoryGOSELECT worker.職工號,worker.姓名,depart.部門名,salary.日期,salary.工資FROM worker,depart,salaryWHERE worker.部門號=depart.部門號 AND worker.職工號=salary.職工號AND salary.工資= (SELECT MAX(工資) FROM salary)GO11

13、USE factoryGOSELECT 部門名FROM departWHERE 部門號= (SELECT 部門號 FROM worker WHERE 職工號= (SELECT 職工號 FROM salary WHERE 工資= (SELECT MAX(工資) FROM salary) ) )GO12USE factoryGOSELECT 職工號,姓名FROM workerWHERE 職工號 IN (SELECT 職工號 FROM salary GROUP BY 職工號 HAVING AVG(工資)<(SELECT AVG(工資) FROM salary) GO13USE factoryG

14、OSET NOCOUNT ON-聲明變量DECLARE dname char(10)-聲明游標(biāo)DECLARE d_cursor CURSOR FOR SELECT 部門名 FROM depart WHERE 部門號= (SELECT 部門號 FROM worker WHERE 職工號= (SELECT 職工號 FROM salary WHERE 工資= (SELECT MAX(工資) FROM salary) ) )-打開游標(biāo)OPEN d_cursor-提取第一行數(shù)據(jù)FETCH NEXT FROM d_cursor INTO dname-打印表標(biāo)題PRINT '部門名'PRIN

15、T '-'WHILE FETCH_STATUS = 0BEGIN -打印一行數(shù)據(jù) PRINT dname -提取下一行數(shù)據(jù) FETCH NEXT FROM d_cursor INTO dnameEND-關(guān)閉游標(biāo)CLOSE d_cursor-釋放游標(biāo)DEALLOCATE d_cursorGO14USE factoryGOSET NOCOUNT ON-聲明變量DECLARE no int,name char(10)-聲明游標(biāo)DECLARE w_cursor CURSOR FOR SELECT 職工號,姓名 FROM worker WHERE 職工號 IN (SELECT 職工號 F

16、ROM salary GROUP BY 職工號 HAVING AVG(工資)<(SELECT AVG(工資) FROM salary)-打開游標(biāo)OPEN w_cursor-提取第一行數(shù)據(jù)FETCH NEXT FROM w_cursor INTO no,name-打印表標(biāo)題PRINT '職工號 姓名'PRINT '-'WHILE FETCH_STATUS = 0BEGIN -打印一行數(shù)據(jù) PRINT CAST(no AS char(8)+name -提取下一行數(shù)據(jù) FETCH NEXT FROM w_cursor INTO no,nameEND-關(guān)閉游標(biāo)CLOSE w_cursor-釋放游標(biāo)DEALLOCATE w_cursorGO15USE factoryGODECLARE num intSELECT num=COUNT(*) FROM workerPRINT '原職工人數(shù):'+CAST(num AS CHAR(3) GODECLARE num intBEGIN TRANSACTION -啟動事務(wù)-插入一個職工記錄 INSERT INTO worker VALUES(20,'陳立','女','55/03/0