武漢紡織大學(xué)最新數(shù)據(jù)庫管理系統(tǒng)課程實驗報告班級： _姓名： 實驗時間： 年 月 日 指導(dǎo)教師:_一、實驗?zāi)康?、通過實驗，使學(xué)生全面了解最新數(shù)據(jù)庫管理系統(tǒng)的基本內(nèi)容、基本原理。2、牢固掌握SQL SERVER的功能操作和Transact-SQL語言。3、緊密聯(lián)系實際，學(xué)會分析，解決實際問題。學(xué)生通過小組項目設(shè)計，能夠運用最新數(shù)據(jù)庫管理系統(tǒng)于管理信息系統(tǒng)、企業(yè)資源計劃、供應(yīng)鏈管理系統(tǒng)、客戶關(guān)系管理系統(tǒng)、電子商務(wù)系統(tǒng)、決策支持系統(tǒng)、智能信息系統(tǒng)中等。二、實驗內(nèi)容1導(dǎo)入實驗用示例數(shù)據(jù)庫：f:教學(xué)庫.mdff:教學(xué)庫_log.ldf f:倉庫庫存.mdff:倉庫庫存_log.ldf1.1 將數(shù)據(jù)庫導(dǎo)入在SqlServer 2005 導(dǎo)入已有的數(shù)據(jù)庫（*.mdf）文件，在SQL Server Management Studio里連接上數(shù)據(jù)庫后，選擇新建查詢,然后執(zhí)行語句 EXEC sp_attach_db dbname = 教學(xué)庫, filename1 = f:教學(xué)庫.mdf, filename2 = f:教學(xué)庫_log.ldf gouse 教學(xué)庫EXEC sp_changedbowner sagoEXEC sp_attach_db dbname = 倉庫庫存, filename1 = f:倉庫庫存.mdf, filename2 = f:倉庫庫存_log.ldfgo use 倉庫庫存EXEC sp_changedbowner sago1.2 可能出現(xiàn)問題附加數(shù)據(jù)庫出現(xiàn)“無法打開物理文件 X.mdf。操作系統(tǒng)錯誤 5:5(拒絕訪問。)。 (Microsoft SQL Server，錯誤: 5120)”。解決：找到要附加的.mdf文件-右鍵-屬性-安全-選擇當前用戶-編輯-完全控制。對.log文件進行相同的處理。2刪除創(chuàng)建的數(shù)據(jù)庫，使用T-SQL語句再次創(chuàng)建該數(shù)據(jù)庫，主文件和日志文件的文件名同上，要求：倉庫庫存_data最大尺寸為無限大，增長速度為20%，日志文件初始大小為2MB，最大尺寸為5MB，增長速度為1MB。CREATE DATABASE倉庫庫存(NAME = 倉庫庫存_data, FILENAME = F:倉庫庫存_data.MDF , SIZE = 10MB, FILEGROWTH = 20%)LOG ON (NAME =倉庫庫存_log, FILENAME = F:倉庫庫存_log. LDF, SIZE = 2MB, MAXSIZE = 5MB,FILEGROWTH = 1MB)2.1 在數(shù)據(jù)庫“倉庫庫存”中完成下列操作。(1)創(chuàng)建“商品”表，表結(jié)構(gòu)如表1： 表1 商品表列名數(shù)據(jù)類型長度是否允許為空值說明商品編號Char6NOT NULL主鍵商品名稱Varchar20NOT NULL,單價Float生產(chǎn)商Varchar30(2)創(chuàng)建“倉庫”表，表結(jié)構(gòu)如表2： 表2 倉庫表列名數(shù)據(jù)類型長度是否允許為空值說明倉庫編號Char3NOT NUL主鍵倉庫地址Varchar20NOT NULL(3)創(chuàng)建“庫存情況”表，表結(jié)構(gòu)如表3： 表3 庫存情況表列名數(shù)據(jù)類型長度是否允許為空值說明倉庫編號Char3NOT NULL主鍵商品編號Char6NOT NUL主鍵數(shù)量int（1）USE倉庫庫存GOCREATE TABLE 商品 (商品編號 char(6) NOT NULL PRIMARY KEY， 商品名稱 char(20) NOT NULL, 單價 Float, 生產(chǎn)商 Varchar （30） )（2），（3）略。2.2 建立“商品”表、“倉庫”表和“庫存情況”表三表之間的關(guān)系圖。2.3分別給“商品”表、“倉庫”表和“庫存情況”表添加數(shù)據(jù)。3數(shù)據(jù)庫查詢.3.1 試用SQL的查詢語句實現(xiàn)下列查詢：（1）統(tǒng)計有學(xué)生選修的課程門數(shù)。答：SELECT COUNT(DISTINCT 課程號) FROM 選課（2）求選修C004課程的學(xué)生的平均年齡。答：SELECT AVG(年齡) FROM 學(xué)生,選課 WHERE 學(xué)生.學(xué)生號=選課.學(xué)生號 and 課程號=C004（3）求學(xué)分為3的每門課程的學(xué)生平均成績。答：SELECT 課程.課程號,AVG(成績) FROM 課程,選課WHERE 課程.課程號=選課.課程號and 學(xué)分=3GROUP BY 課程.課程號（4）統(tǒng)計每門課程的學(xué)生選修人數(shù)，超過3人的課程才統(tǒng)計。要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列。答：SELECT 課程號,COUNT(*) FROM 選課GROUP BY 課程號HAVING COUNT(*) 3ORDER BY COUNT(*) DESC, 課程號（5）檢索學(xué)號比王明同學(xué)大，而年齡比他小的學(xué)生姓名。答：SELECT 姓名 FROM 學(xué)生WHERE 學(xué)生號(SELECT 學(xué)生號FROM 學(xué)生WHERE 姓名=王明) and 年齡(SELECT AVG(年齡) FROM 學(xué)生WHERE 性別=女) （9）求年齡大于所有女同學(xué)年齡的男學(xué)生姓名和年齡。答：SELECT 姓名,年齡 FROM 學(xué)生WHERE 性別=男 and 年齡 all (SELECT 年齡 FROM 學(xué)生WHERE 性別=女) （10）檢索所有比王明年齡大的學(xué)生姓名、年齡和性別。答：SELECT 姓名,年齡,性別 FROM 學(xué)生WHERE 年齡 (SELECT 年齡 FROM 學(xué)生WHERE 姓名=王明) （11）檢索選修課程C001的學(xué)生中成績最高的學(xué)生的學(xué)號。答：SELECT 學(xué)生號 FROM 選課WHERE 課程號=C001 and 成績=(SELECT MAX(成績) FROM選課 WHERE課程號=C001)（12）檢索學(xué)生姓名及其所選修課程的課程號和成績。答：SELECT 姓名, 課程號, 成績 FROM 學(xué)生,選課 WHERE學(xué)生.學(xué)生號=選課.學(xué)生號（13）檢索選修2門以上課程的學(xué)生總成績(不統(tǒng)計不及格的課程)，并要求按總成績的降序排列出來。答：SELECT 學(xué)生號,SUM(成績) FROM 選課WHERE 成績=60GROUP BY學(xué)生號HAVING COUNT(*)=2ORDER BY SUM(成績) DESC3.2 利用控制流語句，查詢學(xué)生號為0101001的學(xué)生的各科成績，如果沒有這個學(xué)生的成績，就顯示“此學(xué)生無成績”。答：IF EXISTS ( SELECT * FROM 選課 WHERE 學(xué)生號=0101001)SELECT 課程號,成績 FROM 選課 WHERE 學(xué)生號=0101001ELSE PRINT 此學(xué)生無成績3.3 用函數(shù)實現(xiàn)：求某個專業(yè)選修了某門課的學(xué)生人數(shù)。答：CREATE FUNCTION renshu(p char(10),cn char(4) RETURNS floatASBEGIN DECLARE cou float SELECT cou=( SELECT count(*) FROM 學(xué)生,選課 WHERE學(xué)生.學(xué)生號=選課.學(xué)生號 and課程號=cnand 專業(yè)=p) RETURN cou END3.4 用函數(shù)實現(xiàn)：查詢某個專業(yè)所有學(xué)生所選的每門課的平均成績。答：CREATE FUNCTION average (p char(10) RETURNS floatASBEGIN DECLARE aver float SELECT aver=( SELECT 課程號,avg(成績) FROM 學(xué)生,選課 WHERE學(xué)生.學(xué)生號=選課.學(xué)生號 and 專業(yè)=pGROUP BY 課程號) RETURN aver END3.5 針對“倉庫庫存”中的“商品”表，查詢商品的價格等級，商品號、商品名和價格等級（單價1000元以內(nèi)為“低價商品”，10003000元為“中等價位商品”，3000元以上為“高價商品”）。答：SELECT商品編號, 商品名稱, CASE WHEN 單價1000 then 低價商品 WHEN 單價=3000 then 高價商品END AS 價格等級FROM 商品4視圖與索引4.1在SQL Server Management Studio中創(chuàng)建一個倉庫庫存信息視圖，要求包含倉庫庫存數(shù)據(jù)庫中三個表的所有列。答：略。4.2 利用T-SQL語句創(chuàng)建一個查詢每個學(xué)生的平均成績的視圖，要求包含學(xué)生的學(xué)生號和姓名。答：CREATE VIEW 學(xué)生_平均成績ASSELECT 學(xué)生.學(xué)生號,姓名,avg(成績) AS平均成績FROM 學(xué)生,選課WHERE 學(xué)生.學(xué)生號=選課.學(xué)生號 GROUP BY學(xué)生.學(xué)生號,姓名4.3 在SQL Server Management Studio中按照選課表的成績列升序創(chuàng)建一個普通索引（非唯一、非聚集）。答：略。4.4 利用T-SQL語句按照商品表的單價列降序創(chuàng)建一個普通索引。答：CREATE INDEX index_商品單價 ON 商品(單價 DESC)5存儲過程、觸發(fā)器和游標5.1 創(chuàng)建存儲過程，計算指定學(xué)生（姓名）的總成績，存儲過程中使用一個輸入?yún)?shù)（姓名）和一個輸出參數(shù)(總成績)。答：CREATE PROCEDURE Sname S_n varchar(20), sum1 int OUTPUTASSELECT sum1= sum(成績) FROM 選課,學(xué)生WHERE 姓名=S_nand 學(xué)生.學(xué)生號=選課.學(xué)生號5.2 在教學(xué)庫中建一個學(xué)生黨費表，屬性（學(xué)生號，姓名，黨費），學(xué)生號是主鍵，也是外鍵（參考學(xué)生表的學(xué)生號）；創(chuàng)建一個觸發(fā)器，保證只能在每年的6月和12月交黨費，如果在其它時間錄入則顯示提示信息。答：CREATE TABLE 學(xué)生黨費表(學(xué)生號 CHAR（7） primary key foreign key references 學(xué)生(學(xué)生號), 姓名 char(6), 黨費 int)CREATE TRIGGER trg_學(xué)生黨費表on 學(xué)生黨費表 for insertASif not(datepart(mm,getdate()=06 or datepart(mm,getdate()=12)BEGINprint對不起,只能在每年的6月和12月交黨費rollbackEND6事務(wù)與并發(fā)控制6.1 創(chuàng)建一個事務(wù)，將所有女生的考試成績都加5分，并提交。答：BEGIN TRANSACTION USE 教學(xué)庫UPDATE 選課SET 成績=成績+5WHERE 學(xué)生號 in (SELECT 學(xué)生號 FROM 學(xué)生 WHERE 性別=女)