最新數(shù)據(jù)庫(kù)管理系統(tǒng)實(shí)驗(yàn)報(bào)告含答案資料.doc

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