DB1_2013302537_張偉

1、 數(shù)據(jù)庫(kù)原理實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)名稱學(xué)號(hào)姓名班級(jí)日期一、 數(shù)據(jù)庫(kù)和表的創(chuàng)建與管理二、 基本表的數(shù)據(jù)操作2013302537張偉100113032015-09-27實(shí)驗(yàn)一 數(shù)據(jù)庫(kù)和表的創(chuàng)建與管理1實(shí)驗(yàn)?zāi)康模?） 掌握SQL Server中數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)和物理結(jié)構(gòu)，基本數(shù)據(jù)類型等。（2） 熟練掌握使用圖形用戶界面以及SQL語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)和表。（3） 掌握使用不同方法備份和還原數(shù)據(jù)庫(kù)。（4） 掌握數(shù)據(jù)庫(kù)和基本表的修改和刪除方法。2實(shí)驗(yàn)內(nèi)容及結(jié)果1. 利用圖形用戶界面創(chuàng)建，備份，刪除和還原數(shù)據(jù)庫(kù)和數(shù)據(jù)表，數(shù)據(jù)庫(kù)和表的要求（第四版教材第二章習(xí)題5要求的數(shù)據(jù)庫(kù)）數(shù)據(jù)庫(kù)名：SPJ，其中包含四張表：S表, P表,

2、J表, SPJ表 圖2.1 S表(供貨商表) 圖2.2 P表(零件表) 圖2.4 SPJ表(供應(yīng)情況表) 圖2.3 J表(工程項(xiàng)目表)l 完成以下具體操作：(1) 創(chuàng)建SPJ數(shù)據(jù)庫(kù)，初始大小為 10MB，最大為50MB，數(shù)據(jù)庫(kù)自動(dòng)增長(zhǎng)，增長(zhǎng)方式是按5比例增長(zhǎng)；日志文件初始為2MB，最大可增長(zhǎng)到5MB，按1MB增長(zhǎng)。數(shù)據(jù)庫(kù)的邏輯文件名和物理文件名均采用默認(rèn)值。(2) 在SPJ數(shù)據(jù)庫(kù)中創(chuàng)建如圖2.1-圖2.4的四張表(只輸入一部分?jǐn)?shù)據(jù)示意即可)。各表創(chuàng)建如下：(3) 備份數(shù)據(jù)庫(kù)SPJ（第一種方法)：備份成一個(gè)擴(kuò)展名為bak的文件。（提示：最好先刪除系統(tǒng)默認(rèn)的備份文件名，然后添加自己指定的備份文件名

3、）(4) 備份數(shù)據(jù)庫(kù)SPJ（第二種方法)：將SPJ數(shù)據(jù)庫(kù)定義時(shí)使用的文件(擴(kuò)展名為mdf,ldf的數(shù)據(jù)文件、日志文件等)復(fù)制到其他文件夾進(jìn)行備份。(5) 刪除已經(jīng)創(chuàng)建的工程項(xiàng)目表(J表)。(6) 刪除SPJ數(shù)據(jù)庫(kù)。（可以在系統(tǒng)默認(rèn)的數(shù)據(jù)存儲(chǔ)文件夾下查看此時(shí)SPJ數(shù)據(jù)庫(kù)對(duì)應(yīng)的mdf,ldf文件是否存在。結(jié)果：不存在。(7) 利用備份過(guò)的bak備份文件還原剛才刪除的SPJ數(shù)據(jù)庫(kù)。（還原數(shù)據(jù)庫(kù)）(8) 利用備份過(guò)的mdf,ldf的備份文件還原剛才刪除的SPJ數(shù)據(jù)庫(kù)。（附加）(9) 將SPJ數(shù)據(jù)庫(kù)的文件大小修改為100MB。實(shí)現(xiàn)結(jié)果：(10) 修改S表，增加一個(gè)聯(lián)系電話的字段sPhoneNo，數(shù)據(jù)類型

4、為字符串類型。修改后的S表2. 利用SQL語(yǔ)言創(chuàng)建和刪除數(shù)據(jù)庫(kù)和數(shù)據(jù)表（50分，每小題5分）l 數(shù)據(jù)庫(kù)和表的要求數(shù)據(jù)庫(kù)名：Student， 其中包含三個(gè)表：S：學(xué)生基本信息表；C：課程基本信息表；SC：學(xué)生選課信息表。各表的結(jié)構(gòu)以及數(shù)據(jù)如下所示： 圖2.5 S表(學(xué)生表) 圖2.7 SC表（選課表） 圖2.6 C表（課程表）l 完成以下具體操作：(1) 用SQL語(yǔ)句創(chuàng)建如圖2.5-圖2.7要求的數(shù)據(jù)庫(kù)Student， 初始大小為20MB，最大為100MB，數(shù)據(jù)庫(kù)自動(dòng)增長(zhǎng)，增長(zhǎng)方式是按10M兆字節(jié)增長(zhǎng)；日志文件初始為2MB，最大可增長(zhǎng)到5MB，按1MB增長(zhǎng)。數(shù)據(jù)庫(kù)的邏輯文件名和物理文件名，日志文

5、件名請(qǐng)自定義。數(shù)據(jù)庫(kù)創(chuàng)建語(yǔ)句如下：create database Student on(name=Student_dat, filename=C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAStudent_dat.mdf, size=20MB, maxsize=50MB, filegrowth=10MB)log on(name=Student_log, filename=C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAStude

6、nt_log.ldf, size=2MB, maxsize=5MB, filegrowth=1MB)(2) 用SQL語(yǔ)句創(chuàng)建上述的三張表，各個(gè)字段的數(shù)據(jù)類型請(qǐng)自己確定，每張表只要有主鍵約束即可，不要其他約束。不用輸入數(shù)據(jù)。表的創(chuàng)建語(yǔ)句如下：create table S(sno char(10) primary key, sname nchar(10) not null, ssex nchar(2), sbirth date,)create table C( cno int primary key, cname nchar(20) not null, cpno int, ccredit int,

7、)create table SC( sno char(10), cno int, grade int,)(3) 用SQL中的backup 語(yǔ)句將數(shù)據(jù)庫(kù)Student備份到一個(gè)bak文件。(提示：backup database student to disk)備份語(yǔ)句如下：backup database Student to disk=C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupStudent.bak(4) 用SQL語(yǔ)句刪除創(chuàng)建的表。刪除語(yǔ)句如下：drop table C(5) 將SPJ數(shù)據(jù)庫(kù)定義時(shí)使用的

8、文件(擴(kuò)展名為mdf,ldf的數(shù)據(jù)文件、日志文件等)復(fù)制到其他文件夾進(jìn)行備份（復(fù)制）后，用SQL語(yǔ)句刪除創(chuàng)建的數(shù)據(jù)庫(kù)。刪除數(shù)據(jù)庫(kù)語(yǔ)句如下：drop database Student(6) 用SQL中的sp_attache_db語(yǔ)句完成對(duì)數(shù)據(jù)庫(kù)Student的附加。（通過(guò)該方式完成數(shù)據(jù)庫(kù)的恢復(fù)時(shí)，使用的備份文件是第5）小題中備份好的數(shù)據(jù)文件、日志文件等 ，具體用法：exec sp_attache_db )附加語(yǔ)句如下： EXEC sp_attach_db dbname=Student, filename1=C:Program FilesMicrosoft SQL ServerMSSQL10_50

9、.MSSQLSERVERMSSQLBackupStudent_dat.mdf, filename2=C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupStudent_log.ldf(7) 先刪除SPJ庫(kù)，再用SQL中的restore 語(yǔ)句還原剛才備份的數(shù)據(jù)庫(kù)Student。 （提示：restore database student from disk）還原語(yǔ)句如下：restore database Student from disk=C:Program FilesMicrosoft SQL ServerMSS

10、QL10_50.MSSQLSERVERMSSQLBackupStudent.bak(8) 用SQL語(yǔ)言修改數(shù)據(jù)庫(kù)Student，給Student數(shù)據(jù)庫(kù)添加一個(gè)文件組 TestFG1，然后將兩個(gè) 5 MB 的文件添加到該文件組。(提示：alter database)語(yǔ)句如下：alter database Studentadd filegroup TestFG1alter database Student/*添加文件組TestFG1到數(shù)據(jù)庫(kù)Student*/add file( name=A, filename=C:Program FilesMicrosoft SQL ServerMSSQL10_5

11、0.MSSQLSERVERMSSQLDATAA.ndf, size=5MB)to filegroup TestFG1alter database Studentadd file( name=B, filename=C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAB.ndf, size=5MB)to filegroup TestFG1/*添加文件A和B給文件組TestFG1*/(9) 修改S表，增加一個(gè)表示聯(lián)系電話的字段sPhoneNo，數(shù)據(jù)類型為字符串類型。alter table S add sPhoneNo

12、char(10)(10) 利用SQL Server Management Studio的生成腳本功能(選中Student庫(kù)-右鍵菜單“任務(wù)”- “生成腳本”)，自動(dòng)生成S表的創(chuàng)建腳本，分析自動(dòng)生成的腳本和自己寫的SQL語(yǔ)句的異同點(diǎn)。生成的腳本如下：USE StudentGO/* Object: Table dbo.S Script Date: 10/03/2015 16:33:24 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE dbo.S(sno char(10) NOT NUL

13、L,sname nchar(10) NOT NULL,ssex nchar(2) NULL,sbirth date NULL,sPhoneNo char(10) NULL,PRIMARY KEY CLUSTERED (sno ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOSET ANSI_PADDING OFFGO實(shí)驗(yàn)二 基本表的數(shù)據(jù)操作1 實(shí)驗(yàn)?zāi)?/p>

14、的（1）掌握使用圖形用戶界面對(duì)基本表進(jìn)行數(shù)據(jù)插入、修改和刪除。（2）掌握使用SQL語(yǔ)句對(duì)基本表進(jìn)行數(shù)據(jù)插入、修改和刪除。（3）掌握使用SQL語(yǔ)句進(jìn)行各種查詢。2 實(shí)驗(yàn)內(nèi)容1 在圖形用戶界面中對(duì)表中的數(shù)據(jù)進(jìn)行更新。（6分，每小題2分）(1) 按照實(shí)驗(yàn)一圖2.1-圖2.4中所示數(shù)據(jù)，輸入SPJ數(shù)據(jù)庫(kù)所含四張表中的數(shù)據(jù)。(2) 修改S表的任意一條數(shù)據(jù)(3) 刪除S表的任意一條數(shù)據(jù)。SC表C表S表2 針對(duì)SPJ數(shù)據(jù)庫(kù)，用SQL語(yǔ)句完成下面的數(shù)據(jù)更新。（12分，每小題3分）(1) 第四版教材P127頁(yè) 5題的8-11小題。 5-8 把全部紅色零件的顏色改成藍(lán)色update Pset COLOR=藍(lán)whe

15、re COLOR=紅實(shí)現(xiàn)結(jié)果：5-9 由S5供給J4的零件P6改為由S3供應(yīng)update SPJ set SNO=S3where SNO=S5 and PNO=P6 and JNO=J4 實(shí)現(xiàn)結(jié)果：5-10 從供應(yīng)商關(guān)系中刪除S2的記錄，并從供應(yīng)情況關(guān)系中刪除相應(yīng)的記錄deletefrom S where SNO=S2;deletefrom SPJ where SNO=S2;實(shí)現(xiàn)結(jié)果：5-11 請(qǐng)將(S2,J6,P4,200)插入供應(yīng)情況關(guān)系insert into SPJvalues(S2,P4,J6,200); 實(shí)現(xiàn)結(jié)果： 3 針對(duì)SPJ數(shù)據(jù)庫(kù)，用SQL語(yǔ)句完成下面的數(shù)據(jù)查詢。(52分，每小

16、題4分)(1) P127頁(yè) 第4題。（5道小題）(2) P127頁(yè) 第5題的1-7小題。（7道小題）(3) 查詢這樣的工程：供給該工程的零件P1的平均供應(yīng)量大于供給工程J1的任何一種零件的最大供應(yīng)量。（1）4-1 求供應(yīng)工程J1零件的供應(yīng)商號(hào)碼select distinct SNOfrom SPJwhere JNO=J1實(shí)現(xiàn)結(jié)果：4-2 求供應(yīng)工程J1零件P1的供應(yīng)商號(hào)碼select distinct SNOfrom SPJwhere JNO=J1 and PNO=P1實(shí)現(xiàn)結(jié)果：4-3 求供應(yīng)工程J1零件為紅色的供應(yīng)商號(hào)碼select distinct SNOfrom SPJwhere JNO

17、=J1 and PNO IN (select PNO from P where COLOR=紅);實(shí)現(xiàn)結(jié)果：4-4 求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號(hào)JNOselect distinct JNOfrom SPJ FIRSTwhere NOT EXISTS (select * from SPJ SECOND where FIRST.JNO=SECOND.JNO AND SECOND.SNO IN (select SNO from S where CITY=天津) AND SECOND.PNO IN (select PNO from P where COLOR=紅)實(shí)現(xiàn)結(jié)果：4-5 求至少

18、用了供應(yīng)商S1所供應(yīng)的全部零件的工程號(hào)（2）5-1 找出所有供應(yīng)商的姓名和所在城市select SNAME,CITYfrom S實(shí)現(xiàn)結(jié)果：5-2 找出所有零件的名稱、顏色、重量select PNAME,COLOR,WEIGHTfrom P實(shí)現(xiàn)結(jié)果：5-3 找出使用供應(yīng)商S1所供應(yīng)零件的工程號(hào)碼select JNOfrom SPJwhere SNO=S1實(shí)現(xiàn)結(jié)果：5-4 找出工程項(xiàng)目J2使用的各種零件的名稱及數(shù)量select PNAME,QTYfrom P,SPJwhere JNO=J2 AND P.PNO=SPJ.PNO實(shí)現(xiàn)結(jié)果：5-5 找出上海廠商供應(yīng)的所有零件號(hào)碼select distin

19、ct PNOfrom SPJwhere SNO IN (select SNO from Swhere CITY=上海)實(shí)現(xiàn)結(jié)果：5-6 找出使用上海產(chǎn)的零件號(hào)碼select distinct PNAME from P,SPJwhere P.PNO=SPJ.PNO AND SNO IN (select SNO from S where CITY=上海)實(shí)現(xiàn)結(jié)果：5-7 找出沒有使用天津產(chǎn)的零件的工程號(hào)碼select distinct JNOfrom SPJ FIRSTwhere NOT EXISTS (select * from SPJ SECOND where SECOND.JNO=FIRST

20、.JNO AND SECOND.SNO IN( select SNO from S where CITY=天津)實(shí)現(xiàn)結(jié)果：（3）實(shí)現(xiàn)代碼： select JNOfrom SPJ FIRSTwhere FIRST.PNO=P1group by JNOhaving AVG(QTY)ALL (select SUM(QTY) from SPJ SECOND where SECOND.JNO=J1 group by PNO )實(shí)現(xiàn)結(jié)果：空4 針對(duì)Student數(shù)據(jù)庫(kù)用SQL語(yǔ)句完成下面的數(shù)據(jù)查詢。(12分，每小題4分)(1) 查詢不選修“C語(yǔ)言”課程的學(xué)生學(xué)號(hào)。(2) 查詢每個(gè)學(xué)生已經(jīng)獲得的學(xué)分的總分（

21、成績(jī)及格表示獲得該門課的學(xué)分），并按照所獲學(xué)分由高到低的順序輸出學(xué)號(hào)，姓名，所獲學(xué)分。(3) 查詢這樣的學(xué)生姓名：該學(xué)生選修了全部課程并且其中一門課在90分以上。（1）實(shí)現(xiàn)語(yǔ)言： select distinct S.snofrom S,SC,Cwhere SC.sno=S.sno AND C.cno=SC.cno AND cname!=C語(yǔ)言實(shí)現(xiàn)結(jié)果：（2）實(shí)現(xiàn)語(yǔ)言：select S.sno,S.sname, SUM(ccredit) creditfrom S,SC,Cwhere SC.sno=S.sno and C.cno=SC.cno and grade=60group by S.sno,S.snameorder by SUM(ccredit) DESC實(shí)現(xiàn)結(jié)果：（3）實(shí)現(xiàn)語(yǔ)言：select distinct snamefrom S,SCwhere SC.sno=S.sno AND (90=ANY (select grade from SC wh