DB1_2013302537_張偉

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

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

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

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

5、件名請自定義。數(shù)據(jù)庫創(chuàng)建語句如下：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語句創(chuàng)建上述的三張表，各個字段的數(shù)據(jù)類型請自己確定，每張表只要有主鍵約束即可，不要其他約束。不用輸入數(shù)據(jù)。表的創(chuàng)建語句如下：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 語句將數(shù)據(jù)庫Student備份到一個bak文件。(提示：backup database student to disk)備份語句如下：backup database Student to disk=C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupStudent.bak(4) 用SQL語句刪除創(chuàng)建的表。刪除語句如下：drop table C(5) 將SPJ數(shù)據(jù)庫定義時使用的

8、文件(擴(kuò)展名為mdf,ldf的數(shù)據(jù)文件、日志文件等)復(fù)制到其他文件夾進(jìn)行備份（復(fù)制）后，用SQL語句刪除創(chuàng)建的數(shù)據(jù)庫。刪除數(shù)據(jù)庫語句如下：drop database Student(6) 用SQL中的sp_attache_db語句完成對數(shù)據(jù)庫Student的附加。（通過該方式完成數(shù)據(jù)庫的恢復(fù)時，使用的備份文件是第5）小題中備份好的數(shù)據(jù)文件、日志文件等 ，具體用法：exec sp_attache_db )附加語句如下： 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庫，再用SQL中的restore 語句還原剛才備份的數(shù)據(jù)庫Student。 （提示：restore database student from disk）還原語句如下：restore database Student from disk=C:Program FilesMicrosoft SQL ServerMSS

10、QL10_50.MSSQLSERVERMSSQLBackupStudent.bak(8) 用SQL語言修改數(shù)據(jù)庫Student，給Student數(shù)據(jù)庫添加一個文件組 TestFG1，然后將兩個 5 MB 的文件添加到該文件組。(提示：alter database)語句如下：alter database Studentadd filegroup TestFG1alter database Student/*添加文件組TestFG1到數(shù)據(jù)庫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表，增加一個表示聯(lián)系電話的字段sPhoneNo，數(shù)據(jù)類型為字符串類型。alter table S add sPhoneNo

12、char(10)(10) 利用SQL Server Management Studio的生成腳本功能(選中Student庫-右鍵菜單“任務(wù)”- “生成腳本”)，自動生成S表的創(chuàng)建腳本，分析自動生成的腳本和自己寫的SQL語句的異同點。生成的腳本如下：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ù)據(jù)操作1 實驗?zāi)?/p>

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

15、re COLOR=紅實現(xiàn)結(jié)果：5-9 由S5供給J4的零件P6改為由S3供應(yīng)update SPJ set SNO=S3where SNO=S5 and PNO=P6 and JNO=J4 實現(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;實現(xiàn)結(jié)果：5-11 請將(S2,J6,P4,200)插入供應(yīng)情況關(guān)系insert into SPJvalues(S2,P4,J6,200); 實現(xiàn)結(jié)果： 3 針對SPJ數(shù)據(jù)庫，用SQL語句完成下面的數(shù)據(jù)查詢。(52分，每小

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

17、=J1 and PNO IN (select PNO from P where COLOR=紅);實現(xiàn)結(jié)果：4-4 求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號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=紅)實現(xiàn)結(jié)果：4-5 求至少

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

19、ct PNOfrom SPJwhere SNO IN (select SNO from Swhere CITY=上海)實現(xiàn)結(jié)果：5-6 找出使用上海產(chǎn)的零件號碼select distinct PNAME from P,SPJwhere P.PNO=SPJ.PNO AND SNO IN (select SNO from S where CITY=上海)實現(xiàn)結(jié)果：5-7 找出沒有使用天津產(chǎn)的零件的工程號碼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=天津)實現(xiàn)結(jié)果：（3）實現(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 )實現(xiàn)結(jié)果：空4 針對Student數(shù)據(jù)庫用SQL語句完成下面的數(shù)據(jù)查詢。(12分，每小題4分)(1) 查詢不選修“C語言”課程的學(xué)生學(xué)號。(2) 查詢每個學(xué)生已經(jīng)獲得的學(xué)分的總分（

21、成績及格表示獲得該門課的學(xué)分），并按照所獲學(xué)分由高到低的順序輸出學(xué)號，姓名，所獲學(xué)分。(3) 查詢這樣的學(xué)生姓名：該學(xué)生選修了全部課程并且其中一門課在90分以上。（1）實現(xiàn)語言： select distinct S.snofrom S,SC,Cwhere SC.sno=S.sno AND C.cno=SC.cno AND cname!=C語言實現(xiàn)結(jié)果：（2）實現(xiàn)語言：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實現(xiàn)結(jié)果：（3）實現(xiàn)語言：select distinct snamefrom S,SCwhere SC.sno=S.sno AND (90=ANY (select grade from SC wh