天津理工大學(xué)數(shù)據(jù)庫(kù)實(shí)驗(yàn)數(shù)據(jù)庫(kù)設(shè)計(jì)與查詢(xún)優(yōu)化

1、人hi大學(xué)實(shí)驗(yàn)報(bào)學(xué)院（系）名稱(chēng)： 計(jì)算機(jī)與通信工程學(xué)院姓名xx學(xué)號(hào)XX專(zhuān)業(yè)信息安全班級(jí)2班實(shí)驗(yàn)名稱(chēng)數(shù)據(jù)庫(kù)設(shè)計(jì)與查詢(xún)優(yōu)化課程名稱(chēng)數(shù)據(jù)庫(kù)系統(tǒng)課程代碼0668026實(shí)驗(yàn)時(shí)間實(shí)驗(yàn)地點(diǎn)7219批改意見(jiàn)成績(jī)教師簽字：第2頁(yè)共18頁(yè)1、實(shí)驗(yàn)?zāi)康?）熟悉索引的創(chuàng)建;2）體會(huì)索引前和索引后執(zhí)行效率的變化;3）熟悉對(duì)于實(shí)現(xiàn)相同功能，不同編程方法（比如連接和嵌套等）的效率。2. 實(shí)驗(yàn)環(huán)境MS SQL server3. 實(shí)驗(yàn)要求(1)對(duì)于 student1表，對(duì)于 student1表，對(duì)于 student1表，對(duì)于 student1表，不按照姓名創(chuàng)建索引，查詢(xún)某個(gè)姓名，所需要的時(shí)間。按照姓名創(chuàng)建索引，查詢(xún)某個(gè)姓名，所

2、需要的時(shí)間。不按照系別創(chuàng)建索引，查詢(xún)某個(gè)系所有學(xué)生，所需要的時(shí)間。按照系別創(chuàng)建各種索引，查詢(xún)某個(gè)系所有學(xué)生，所需要的時(shí)間。查詢(xún)SC1表所需時(shí)間。將student1和sc1連接所需時(shí)間。將將student1和sc1和coursel連接所需時(shí)間。（8）查詢(xún)選修了 “數(shù)據(jù)庫(kù)”學(xué)生的學(xué)號(hào)姓名，分別用嵌套和連接的方法，觀(guān)察兩種方法所用的時(shí)間。（9）將實(shí)驗(yàn)1中和實(shí)驗(yàn)2中各個(gè)操作在student1,course1,sc1上運(yùn)行，觀(guān)察各個(gè)操作所需時(shí)間。4. 實(shí)驗(yàn)過(guò)程記錄（源程序、測(cè)試用例、測(cè)試結(jié)果及心得體會(huì)等）2.對(duì)于student1表，按照姓名創(chuàng)建索引，查詢(xún)某個(gè)姓名，所需要的時(shí)間。I農(nóng) Microsoft

3、5QL Server Managemenl Studio!執(zhí)行兇B 丿舄雪3文件 漏輯圄 視園00 瑩詢(xún) 頊貝 謂試2J工具 菌口呦 社區(qū) S助凹 臉 db_coijrstlSQLQueryL糾I - loj.dmmi就旅” 耳尸f vma3：LdbjcoLir磚 1 - dbdEtud&ntlselect FfroiL St;idencl where SnairerrrSnaSname Seex 5age 5depl|B- -i IBSTOOOm j 周探4433 女 31 曰語(yǔ)1 dt_coursel1行R.i. 11_ III I.一-1“ 一localhost C10.& R.TW)

4、vnna31AdiTilni5trator C55J第5頁(yè)共18頁(yè)列16Ch 16Ins文件 海輯叵I視圍凹 M（e）渭試（0 ZMCE 窗口呦 社區(qū) S帥凹執(zhí)行兇b 、跖卻圖I曙嗚琶圜泊: 2旋鄒I由I想泌冷丨0）I 3 d豈弐-:二艇 db_courseflSQLCueryLsql - locdminislrator C53)* YmflHl.db-eurwml - dbQStiidgntl*|=周2Sm33rIB create 工md色：K S匸;13耳里忙世 cm Studer;丄(Snait-e *L select * froir StudentL wezre Snairsrrr結(jié)果

5、la消總SnoSname Ssex Sage SdeptH ! m/QWIM !周2W33 女 21a 語(yǔ) I 11 III l”LfLF”-localhost 10.0 R.TM)vma31AdiTiinistrator C53) cJb-_coursel 00:03?491 行行2Ch 25Ins實(shí) Wicncsoft SQL Server Management Studio哀件 躺魚(yú) 視圍也 童詢(xún) 頊目（E）謂試迎I工具 窗口呦 社區(qū) 哥朋凹72.沁童丨山丨曲沮 苗口丨（3 a昌|邇匕 “歸昌國(guó)T嗎迪團(tuán)；： 寸X |:門(mén)宦 db_courselSQLueryLiql - llOC-.dm

6、imstrator* dbo.Studgntl* creaue Index Srusnaite on Srcdenrl (Snairel :L select * from StudentL whezre Snsjrs = 周 254433-rrr旳L呂巨er分析手寸間=_弋PU時(shí)間=0占甬時(shí)間=“ S利心SQL分析芋唆譯時(shí)詢(xún)二亠匸四時(shí)間=1 S秒，占用時(shí)間=1E4臺(tái)秒。5QX ServfiEPU時(shí)間執(zhí)行時(shí)間:_=5 65626耋秒，占用時(shí)間=2290D4 SthSQL SeiTTFa fCPU時(shí)間SQL Sen-er分析手唆譯時(shí)i旬：_時(shí)間=O a瓠占用時(shí)間=01童科*SflL SEVr分析無(wú)

7、唆譯時(shí)|旬=I _CPU時(shí)間-01 a稅 占用時(shí)間-01 a穢。鈔您卵制,占用時(shí)間=.詡.（1行受嶷響）SQL Server扌九行吐間；_弋PU吋間=0 a秒，占用時(shí)間=2 5毫秒Q置這已成localhos-t fiO.ft ftTW) viTia31Adinlnitrartcjr (53) db-_coursel M;O3451 上r?1|23ch 21In第7頁(yè)共18頁(yè)3.對(duì)于student1表，不按照系別創(chuàng)建索引，查詢(xún)某個(gè)系所有學(xué)生，所需要的時(shí)間。怏 lMicro5oft SQL Server Management StudioTI赳MB文件遲硝視g型查簡(jiǎn)A.昶童頸 CNJI Cj pt

8、Si a S I Gi S H 3-陸 db_cour5el頂目舊調(diào)試程工具CD 匿口0 杜區(qū) 和助凹閔1?財(cái)兇“記妙宵r聲輜畫(huà)芒ISQLQueryLEy - loc.dministrator (53)* , vma31.db_coursel - d bo .Stud tntl*select frorr Studentl where Sdept =匕學(xué)a結(jié)果巴二肖息SnoSnameSaexSageSdept119S70MHX16鄭 2685=2罰化學(xué)219&70MHH2jgSOSBJfl19化學(xué)313&7OMM37奚as吃曲20化學(xué)41W70WWJ蘇 7017931化學(xué)S1S97CWa2U2M

9、2r5)iXiiiB iirJ化學(xué)1 的7000109花 &4SS&GIE化學(xué)71W7O0O113史33155119化學(xué)3197000113殿1翩刃20化學(xué)319&7O0O143哪462毓21化學(xué)10197000156金3345筠17化學(xué)111357000172何55134520化學(xué)1213570001S4柳 &mS4IB化學(xué)1313&70K1S3雷73337221化學(xué)141997000155S54DS2220化學(xué)15IWTOODMS責(zé)，S2M3W21化學(xué)s童localhost 10.0 RTM) vina 31Adininistrator (53) db_cour5el CK)：00：3&

10、1700770 行Ines結(jié)果I為消息分析和編譯時(shí)間；_S秒，占用時(shí)間-二03自1毫秒。 分析味奧S譯時(shí)間._毫秒，占用時(shí)間=0毫秒。SSL Servet CP口 時(shí)間-4540 3皿 SftEVtJC _CPU時(shí)間二0SftL Szv.=執(zhí)行時(shí)間_CPU時(shí)間-9 00 5秒，占用時(shí)間-2773奎秒。第9頁(yè)共18頁(yè)4.對(duì)于student1表，按照系別創(chuàng)建各種索引，查詢(xún)某個(gè)系所有學(xué)生，所需要的時(shí)間。:宴 Microsoft SQL Server Managemenl Studio文件 購(gòu)WW 視園OQ 童向 項(xiàng)呂(B)舄試迎I工具 窗口回 新遷貫161 aa乜I n a昌 遹三7障圍圍I置峙迫圈

11、 ;SQLueryLtql - lojHmiristRttor C53)*t vmaMlFb-tuursEl ” dbo.Studentl* create Index srudepu on 5tnd電n匸1iSderxiselect * ZIOX StudentL wfte二& Sdept = *彳匕學(xué)*祖 db_coursefl?執(zhí)行兇krrrOPSnoSnamep-卯頑麗n卻2eS5衛(wèi) HJI IltKl IMH HIII|IUBM|IBI1的7000012 m7CHX27 m7WMU4SsestSage20K.mTMOIW mToooiu m7MX1J319&7MOU31011mTOTO

12、IMm7M&172IZm7W01&4131415mvwoias mTOOOlM 1 眸0213辰503&38奚851233蘇 70179M2&402花smes史 301551騙1弼57尊46藥SO金3&4535 ut er . If Thdu wtreen appearin. fol lowsure Any new hardware or scftware fs pr*iDip4f1y 4rsta1 led.# new Initallit 1 an, ask your hjrdwjre op software utianufiCTurer indsws updates you (rigiT n

13、*dlCont 1nue. disab1 or rtmoue any newly 1n$ca1led hardwareSli 9 4 b 11 B IiOS fTKmor opt 1 ons suchj h1 ng Or fnMC7. 將將student1和sc1和coursel連接所需時(shí)間。系統(tǒng)藍(lán)屏TA_LNt=fclFP1 em h-is uewTJVh =?|- h i.jrlEh童 1 rst t Pm些 you Je s ten This stop erG廣 dOnipijt , 1 審 *Mz電電n appearsscreari. (1 lowL-律*jc督 sure Any ne

14、w har-dwre or scftware A s properly instilled!, a new Installt 1 an, sb your hjrdwre or softwart BianurfacTurer Wi|ndi?w$ updates youn*(S.iMCK Continue, d1iSAb1 or rvnouen#w1y 1nftilled hardreItli 9 4tt1 * B JCfS Eamory opt 1 ons j uch ds j _-=_ clb_coursel?執(zhí)行兇/SQLQl ery S I (Io ,ozeryyrozer (51) *

15、曰 select discxnct sno fran: 3匚1 aclxwhereAVS (Grade ii frcm SUL 3Cly-where acly. 3；5c=3clsc. Sno )60*nrsno2.19S72224S72.1997222492Z.1曲7222葩5313777? 2| (locaD (10.50 RTM)roieryyrozer (51)db_cQur5el00:07:232333749ft行4列1Ch 1第25頁(yè)共18頁(yè)(2)求計(jì)算機(jī)系沒(méi)有選修數(shù)據(jù)庫(kù)課程的學(xué)生姓名。select where wherewheresn ame fromStude nt1Sno n

16、ot in (selectSnoCno in (select Cno from Sname =數(shù)據(jù)庫(kù)系統(tǒng))andfrom SC1CourselSdept =計(jì)算機(jī)I O II 回奩口兇fcEce新麗直誨団S陽(yáng)由酋H；2 朗 db_courel?執(zhí)行兇” MQLQuery5K|l (ld”Q左它ryy0jr曰 select BTiHire from Studentlwherewhere-whereMicrosoft EQL Server Management Studio文祥CE礎(chǔ)宜看M查詢(xún)項(xiàng)目調(diào)試園二風(fēng) 芻聞-y OP7皿Sno U2Z ir. ii select Sno fiom 5C1Cn

17、o iri (select Cnc iront CounselonsiT.e=-數(shù)據(jù)庫(kù)系統(tǒng)nar.d 5depl:= 計(jì)算機(jī)*rrrS結(jié)果tZd消息1sname1趙 W5459I234花4?的SI575232G尤 24&31SW-Ch 1(3)求至少選修了學(xué)號(hào)為S1所選修的全部課程的學(xué)生學(xué)號(hào)。selectdistinetsno from SCI sc1xwherenotexists(select*fromSC1sc1ywheresc1y.Sno =1997000194andnotexists(select* from sc1wheresc1z.sno =sc1x . sno andsc1y.C

18、no =sc1z.Cno )sc1zMicrosoft SQL Server Management Studio文件CE髓 查SY）查詢(xún) 項(xiàng)目 洞試 工風(fēng) 蠱口險(xiǎn) 杜區(qū)=J.新斷Q険底陥Q 3HeS胡 db courstfl執(zhí)行兇卜/ SQLQueryS.sql - 1o.c2eryyr&Mr (51)r曰 select di scinct sno f rorr. SCI sc lx wherewhere scly_ Sno1997000154 and not exists(占亡 L wherenet exisLS (select: * fzem SCI 3clyl|ect - from sc

19、：3clz . 3nQ=3clx . sno snd scly . Ciici=3clz . Cno ( iMl百結(jié)果消息I2345Gsnom : Bilik Jill IIIL JJII LBJ Ii 15700051 if /1970001(0199700011619371)0013319970001921的同00134local) (10.50 RTM) roz.nyyrozr (SlJ | db_coGr5el | 00:34:40 | 76489 齊 列斗了Ch 47（*查詢(xún)時(shí)間過(guò)長(zhǎng)終止查詢(xún)。）（4）求各系的系的學(xué)生人數(shù)的，并將結(jié)果按學(xué)生人數(shù)的降序排序。selectgrou P or

20、derSdept , COUNT( sno ) from Student1 by Sdeptby Sde pt ASCG 回 111nrselectwherewheresno , gradefromSC1 sc1xGrade =( select AVG( Grade ) from SC1 sc1yCno =( selectCno fromCourse1G Microsoft SQL Server Management Studio文件CE 稠 查SY）查詢(xún) 項(xiàng)目 ii試 TMCD 蠱口險(xiǎn) 杜區(qū)=.4新建亙?cè)僀j SS陥Q酋閾-蟄 db_tourstfl!執(zhí)行09 卜SQLQuery&.sql - 1o,.c2eryyr&Mr (51)rselect 5de匚匸 23UHr (snoji f jcm Studertl group bv Sdept order by Sdept ASCa結(jié)果恪消息Sdept保列茗)4 1 i 材料 i iraisijf 2 査理=3 忖17007704 機(jī)械169%a95 計(jì)算機(jī)1E316G熱能17MS5