sql數(shù)據(jù)庫實例(數(shù)據(jù)庫入門)

/數(shù)據(jù)庫設(shè)計及應(yīng)用實驗實驗內(nèi)容創(chuàng)建數(shù)據(jù)庫：包括Student,Course,Enroll,Statistics表,表的結(jié)構(gòu)如下：Student<sno,sname,age,sex>Course<cno,cname,credit>Enroll<sno,cno,grade>Statistics<sno,cNumber,creditSum>說明：cNumber是sno學(xué)生選修課程的數(shù)目；creditSum是sno學(xué)生選修課程的總學(xué)分。設(shè)計并在MSSQLServer2000中創(chuàng)建以上表結(jié)構(gòu),并設(shè)置完整性約束。查詢所有選修課程的學(xué)生的基本信息、課程信息及相應(yīng)的考試成績。查詢所有學(xué)生的信息,若已選課就還要給出選修課程的信息及考試成績。查詢所有課程的信息,若課程有學(xué)生選修就還要給出選修課程的學(xué)生的信息及考試成績。查詢選修名為"數(shù)據(jù)庫"的課程的考試成績最高的學(xué)生的信息。對Student的age創(chuàng)建規(guī)則,滿足18≤age≤25,并給出驗證實例及驗證結(jié)果。創(chuàng)建觸發(fā)器：當(dāng)學(xué)生選修一門新的課程后,Statistics表的cNumber自動加1,且creditNumber自動增加新選課程的學(xué)分。創(chuàng)建視圖：找出所有已修學(xué)分超過6、所修課程平均分不低于60的學(xué)生的基本信息、以及所修課程的平均分。按照平均分排序,若平均分相同按照學(xué)號排序。對于如下一組數(shù)據(jù)操作：select*fromcourse;select*fromcoursewherecname=’數(shù)據(jù)庫’select*fromcoursewherecredit=3select*fromcoursewherecredit>2andcredit<5updatecoursesetcredit=3wherecredit=2對Course表的credit屬性創(chuàng)建索引,并給出上述查詢在創(chuàng)建了該索引后的執(zhí)行計劃；去掉Course表的credit屬性上創(chuàng)建的索引,并給出上述查詢的執(zhí)行計劃。對上述有無索引個查詢執(zhí)行的情況進(jìn)行對比分析,總結(jié)出什么時候索引有效？將表Student、Course、Enroll作內(nèi)連接的結(jié)果發(fā)布為HTML網(wǎng)頁格式。實驗步驟及分析過程1、設(shè)計并在MSSQLServer2000中創(chuàng)建以上表結(jié)構(gòu),并設(shè)置完整性約束。〔1創(chuàng)建數(shù)據(jù)庫SC。SQL語句為：createdatabaseSC創(chuàng)建后的數(shù)據(jù)庫如圖1。圖1創(chuàng)建數(shù)據(jù)庫圖1創(chuàng)建數(shù)據(jù)庫的結(jié)果〔2創(chuàng)建表創(chuàng)建學(xué)生表：createtableStudent< snovarchar<10>notnullprimarykey, snamevarchar<50>notnull, ageint, sexvarchar<2>notnull >創(chuàng)建課程表：createtableCourse< cnovarchar<10>notnullprimarykey, cnamevarchar<50>notnull, creditintnotnull >創(chuàng)建注冊表：createtableEnroll< snovarchar<10>notnull referencesStudent<sno>, cnovarchar<10>notnull referencesCourse<cno>, gradeint, primarykey<sno,cno> >創(chuàng)建選課情況表：createtableStatisticss< snovarchar<10>notnullprimarykey referencesStudent<sno>, cNumberintnotnull, creditSumintnotnull, >創(chuàng)建后的表如圖2。圖2圖2創(chuàng)建表的結(jié)果〔3插入數(shù)據(jù)Student表：insertintoStudentvalues<'2008001','李貴斌',22,'男'>insertintoStudentvalues<'2008002','冉從寶',21,'男'>insertintoStudentvalues<'2008003','楊文學(xué)',20,'男'>insertintoStudentvalues<'2008004','楊璐',22,'女'>insertintoStudentvalues<'2008005','李小萌',20,'女'>Course表：insertintoCoursevalues<'001','數(shù)據(jù)庫',4>insertintoCoursevalues<'002','java',3>insertintoCoursevalues<'003','操作系統(tǒng)',5>insertintoCoursevalues<'004','軟件工程',4>insertintoCoursevalues<'005','計算機(jī)英語',2>Enroll表：insertintoEnrollvalues<'2008001','001',89>insertintoEnrollvalues<'2008002','003',98>insertintoEnrollvalues<'2008003','002',85>insertintoEnrollvalues<'2008001','004',88>insertintoEnrollvalues<'2008004','001',89>insertintoEnrollvalues<'2008002','001',90>insertintoEnrollvalues<'2008003','003',78>insertintoEnrollvalues<'2008002','004',79>〔4查看表的內(nèi)容select*fromStudent結(jié)果如圖3。圖3圖3表Student中的數(shù)據(jù)select*fromCourse結(jié)果如圖4。圖4圖4表Course中的數(shù)據(jù)select*fromEnroll結(jié)果如圖5。圖5圖5表Enroll中的數(shù)據(jù)2、查詢所有選修課程學(xué)生的基本信息、課程信息及相應(yīng)的考試成績。SQL語句為：selecta.sno,a.sname,a.age,a.sex,o,ame,b.credit,c.gradefromStudenta,Courseb,Enrollc wherea.sno=o=o執(zhí)行結(jié)果如圖6所示。圖6選修課程的學(xué)生、課程及成績信息圖6選修課程的學(xué)生、課程及成績信息執(zhí)行結(jié)果分析：只顯示了選了課程的學(xué)生信息。3、查詢所有學(xué)生的信息,若已選課就還要給出選修課程的信息及考試成績。SQL語句為：selecta.sno,a.sname,a.sex,a.age,o,ame,d.credit,d.gradefromStudentaleftouterjoin <o,ame,b.credit,c.grade,c.sno fromCourseb,Eo=o>dona.sno=d.sno執(zhí)行結(jié)果如圖7所示。圖7圖7所有學(xué)生選課情況及成績信息執(zhí)行結(jié)果分析：不但列出選了課程的學(xué)生而且還列出來沒選課程的學(xué)生信息。4、查詢所有課程的信息,若課程有學(xué)生選修就還要給出選修課程的學(xué)生的信息及考試成績。SQL語句為：o,ame,a.credit,d.sno,d.sname,d.sex,d.age,d.gradefromCoursealeftouterjoin <selectb.sno,b.sname,b.age,b.sex,c.grade,o fromStudentb,Enrollcwhereb.sno=c.sno>o=o執(zhí)行結(jié)果如圖8所示。圖圖8所有課程、選課的學(xué)生及成績信息執(zhí)行結(jié)果分析：不但列出被選課程的信息和學(xué)生選課情況而且還列出來沒被選課程的信息。5、查詢選修名為"數(shù)據(jù)庫"的課程的考試成績最高的學(xué)生的信息?！?先查看選了數(shù)據(jù)庫課程的學(xué)生信息。SQL語句為：selecta.sno,a.sname,a.sex,a.age,ame,c.gradefromStudenta,Courseb,Enrollc wherea.sno=o=ame='數(shù)據(jù)庫'執(zhí)行結(jié)果如圖9所示。圖9圖9選修"數(shù)據(jù)庫"課程的學(xué)生信息執(zhí)行結(jié)果分析：有三位學(xué)生選修了數(shù)據(jù)庫課程?！?查詢選修"數(shù)據(jù)庫"課程的考試成績最高的學(xué)生的信息。SQL語句為：selecta.sno,a.sname,a.sex,a.age,ame,d.gradefromStudenta, <o,ame,c.grade,c.snofromCourseb,Enrollc o=ame='數(shù)據(jù)庫'>dwherea.sno=d.snoandd.grade=<selectmax<grade> from<o,ame,c.grade fromCourseb,Eo=o>d ame='數(shù)據(jù)庫'>執(zhí)行結(jié)果如圖10所示。圖10圖10選修"數(shù)據(jù)庫"課程成績最高學(xué)生執(zhí)行結(jié)果分析：與圖9結(jié)合比較,圖10了列出了選修"數(shù)據(jù)庫"課程成績最高學(xué)生信息。6、對Student的age創(chuàng)建規(guī)則,滿足18≤age≤25,并給出驗證實例及驗證結(jié)果?！?創(chuàng)建規(guī)則并且綁定它。SQL語句為：createruleage_ruleas @age>=18and@age<=25sp_bindruleage_rule,'Student.age'〔2插入數(shù)據(jù)檢驗。insertintoStudentvalues<'2008006','李志鋒',20,'男'>〔所影響的行數(shù)為1行insertintoStudentvalues<'2008007','李小龍',32,'男'>insertintoStudentvalues<'2008008','楊一民',17,'男'>服務(wù)器:消息513,級別16,狀態(tài)1,行1列的插入或更新與先前的CREATERULE語句所強(qiáng)制的規(guī)則沖突。該語句已終止。沖突發(fā)生于數(shù)據(jù)庫'SC',表'Student',列'age'。語句已終止。執(zhí)行結(jié)果分析：規(guī)則設(shè)置成功。7、創(chuàng)建觸發(fā)器：當(dāng)學(xué)生選修一門新的課程后,Statistics表的cNumber自動加1,且creditNumber自動增加新選課程的學(xué)分。SQL語句為：createtriggerinsert_statisticssonEnrollforinsertasbeginupdateasetcNumber=cNumber+d.countCno,creditSum=creditSum+d.sumCreditfromStatisticssainnerjoin <selectb.sno,count<o>ascountCno,sum<c.credit> assumCreditfrominsertedb innerjoinCo=o groupbyb.sno>dona.sno=d.snoinsertStatisticssselectb.sno,count<o>ascountCno,sum<c.credit>assumCreditfrominsertedbinnerjoinCo=o wheresnonotin<selectdistinctsnofromStatisticss> groupbyb.snoend沒有在Enroll表插入任何數(shù)據(jù)時表為空,如圖11所示。圖1圖11表Statisticss的初始信息在Enroll表插入以下數(shù)據(jù)：insertintoEnrollvalues<'2008001','005',70>insertintoEnrollvalues<'2008006','001',59>插入數(shù)據(jù)后的結(jié)果如圖12所示。圖12圖12表Statisticss的信息執(zhí)行結(jié)果分析：當(dāng)在Enroll表中插入選課情況時,在表上建立的觸發(fā)器insert_statisticss就被觸發(fā),進(jìn)行cNumber自動加1和creditNumber自動增加新選課程的學(xué)分。8、創(chuàng)建視圖：找出所有已修學(xué)分超過6、所修課程平均分不低于60的學(xué)生的基本信息、以及所修課程的平均分。按照平均分排序,若平均分相同按照學(xué)號排序。SQL語句為：createviewTranscriptasselecttop10d.sno,d.sname,d.sex,d.age,d.avg_grade,d.creditSumfrom<selecta.sno,a.sname,a.age,a.sex,avg<grade> asavg_grade,sum<credit>ascreditSum fromStudenta,Courseb,Enrollc wherea.sno=o=o groupbya.sno,a.sname,a.sex,a.age >dwhereavg_grade>=60andcreditSum>6orderbyavg_grade,d.snoselect*fromTranscript執(zhí)行結(jié)果如圖13所示。圖13Transcript圖13Transcript視圖表執(zhí)行結(jié)果分析：列出的學(xué)生都按平均成績大小從低到高排列且平均分都大于60.當(dāng)平均分相同時,表中冉從寶和楊璐就按照學(xué)號大小從小低到高排列。9、對照對Course表的credit屬性創(chuàng)建索引和去掉Course表的credit屬性上創(chuàng)建的索引查詢的執(zhí)行計劃。對Course表的credit屬性創(chuàng)建索引為：createindexcredit_indexonCourse<credit>對比有無索引credit_index情形下,"select*fromCourse"的查詢的執(zhí)行計劃分別如圖14、圖15所示。圖14圖14有索引情形下的查詢的執(zhí)行計劃圖15無索引情形下的查詢的執(zhí)行計劃結(jié)果分析：由圖14,、圖15可知當(dāng)查詢是針對整個表進(jìn)行,創(chuàng)建索引和不創(chuàng)建索引,查詢的執(zhí)行計劃都是一樣的?！?對比有無索引credit_index情形下,"select*fromcoursewherecname=’數(shù)據(jù)庫’"的查詢的執(zhí)行計劃分別如圖16、圖17所示。圖16圖16有索引情形下的查詢的執(zhí)行計劃圖17無索引情形下的查詢的執(zhí)行計劃結(jié)果分析：由圖16,、圖17可知當(dāng)條件針對的字段不是創(chuàng)建了索引的那個字段時,索引是無效的。有索引和無索引,查詢的執(zhí)行計劃都是一樣的?！?對比有無索引credit_index情形下,"select*fromcoursewherecredit=3"的查詢的執(zhí)行計劃分別如圖18、圖19所示。圖18圖18有索引情形下的查詢的執(zhí)行計劃圖19無索引情形下的查詢的執(zhí)行計劃結(jié)果分析：由圖18,、圖19可知where條件為credit的屬性,索引起效。有索引的查詢效率比沒有索引的查詢效率高數(shù)十倍。〔4對比有無索引credit_index情形下,"select*fromcoursewherecredit>2andcredit<5”的查詢的執(zhí)行計劃分別如圖20、圖21圖20圖20有索引情形下的查詢的執(zhí)行計劃圖21無索引情形下的查詢的執(zhí)行計劃結(jié)果分析：由圖20,、圖21可知where條件為credit的屬性,索引起效。有索引的查詢效率比沒有索引的查詢效率高數(shù)十倍。〔5對比有無索引credit_index情形下,"updatecoursesetcredit=3wherecredit=2”圖22圖22有索引情形下的查詢的執(zhí)行計劃圖23無索引情形下的查詢的執(zhí)行計劃結(jié)果分析：由圖20,、圖21可知where條件為credit的屬性,索引起效。有索引的查詢效率比沒有索引的查詢效率高數(shù)十倍。索引小結(jié)：=1\*GB3①索引是對數(shù)據(jù)庫表中的一列或多列的值進(jìn)行排序的結(jié)構(gòu),有助于更快地獲取信息。=2\*GB3②只有當(dāng)經(jīng)常查詢索引列中的數(shù)據(jù)時,才需要創(chuàng)建索引。=3\*GB3③折衷：索引提高查詢速度vs.索引占用磁盤空間,降低數(shù)據(jù)更新執(zhí)行速度。=4\*GB3④若應(yīng)用中大多數(shù)是數(shù)據(jù)更新操作〔插入、刪除和修改。則應(yīng)限制索引的數(shù)量。10、將表Student、Course、Enroll作內(nèi)連接的結(jié)果發(fā)布為HTML網(wǎng)頁格式?！?在企業(yè)管理器的控制臺