DB1_2013302537_張偉

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

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

3、）(4) 備份數(shù)據(jù)庫SPJ（第二種方法)：將SPJ數(shù)據(jù)庫定義時使用的文件(擴展名為mdf,ldf的數(shù)據(jù)文件、日志文件等)復制到其他文件夾進行備份。(5) 刪除已經創(chuàng)建的工程項目表(J表)。(6) 刪除SPJ數(shù)據(jù)庫。（可以在系統(tǒng)默認的數(shù)據(jù)存儲文件夾下查看此時SPJ數(shù)據(jù)庫對應的mdf,ldf文件是否存在。結果：不存在。(7) 利用備份過的bak備份文件還原剛才刪除的SPJ數(shù)據(jù)庫。（還原數(shù)據(jù)庫）(8) 利用備份過的mdf,ldf的備份文件還原剛才刪除的SPJ數(shù)據(jù)庫。（附加）(9) 將SPJ數(shù)據(jù)庫的文件大小修改為100MB。實現(xiàn)結果：(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：學生基本信息表；C：課程基本信息表；SC：學生選課信息表。各表的結構以及數(shù)據(jù)如下所示： 圖2.5 S表(學生表) 圖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、文件(擴展名為mdf,ldf的數(shù)據(jù)文件、日志文件等)復制到其他文件夾進行備份（復制）后，用SQL語句刪除創(chuàng)建的數(shù)據(jù)庫。刪除數(shù)據(jù)庫語句如下：drop database Student(6) 用SQL中的sp_attache_db語句完成對數(shù)據(jù)庫Student的附加。（通過該方式完成數(shù)據(jù)庫的恢復時，使用的備份文件是第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庫-右鍵菜單“任務”- “生成腳本”)，自動生成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 實驗目

14、的（1）掌握使用圖形用戶界面對基本表進行數(shù)據(jù)插入、修改和刪除。（2）掌握使用SQL語句對基本表進行數(shù)據(jù)插入、修改和刪除。（3）掌握使用SQL語句進行各種查詢。2 實驗內容1 在圖形用戶界面中對表中的數(shù)據(jù)進行更新。（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 把全部紅色零件的顏色改成藍色update Pset COLOR=藍whe

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

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

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

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

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

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