sql數(shù)據(jù)庫(kù)實(shí)例(數(shù)據(jù)庫(kù)入門(mén))

/數(shù)據(jù)庫(kù)設(shè)計(jì)及應(yīng)用實(shí)驗(yàn)實(shí)驗(yàn)內(nèi)容創(chuàng)建數(shù)據(jù)庫(kù)：包括Student,Course,Enroll,Statistics表,表的結(jié)構(gòu)如下：Student<sno,sname,age,sex>Course<cno,cname,credit>Enroll<sno,cno,grade>Statistics<sno,cNumber,creditSum>說(shuō)明：cNumber是sno學(xué)生選修課程的數(shù)目；creditSum是sno學(xué)生選修課程的總學(xué)分。設(shè)計(jì)并在MSSQLServer2000中創(chuàng)建以上表結(jié)構(gòu),并設(shè)置完整性約束。查詢(xún)所有選修課程的學(xué)生的基本信息、課程信息及相應(yīng)的考試成績(jī)。查詢(xún)所有學(xué)生的信息,若已選課就還要給出選修課程的信息及考試成績(jī)。查詢(xún)所有課程的信息,若課程有學(xué)生選修就還要給出選修課程的學(xué)生的信息及考試成績(jī)。查詢(xún)選修名為"數(shù)據(jù)庫(kù)"的課程的考試成績(jī)最高的學(xué)生的信息。對(duì)Student的age創(chuàng)建規(guī)則,滿(mǎn)足18≤age≤25,并給出驗(yàn)證實(shí)例及驗(yàn)證結(jié)果。創(chuàng)建觸發(fā)器：當(dāng)學(xué)生選修一門(mén)新的課程后,Statistics表的cNumber自動(dòng)加1,且creditNumber自動(dòng)增加新選課程的學(xué)分。創(chuàng)建視圖：找出所有已修學(xué)分超過(guò)6、所修課程平均分不低于60的學(xué)生的基本信息、以及所修課程的平均分。按照平均分排序,若平均分相同按照學(xué)號(hào)排序。對(duì)于如下一組數(shù)據(jù)操作：select*fromcourse;select*fromcoursewherecname=’數(shù)據(jù)庫(kù)’select*fromcoursewherecredit=3select*fromcoursewherecredit>2andcredit<5updatecoursesetcredit=3wherecredit=2對(duì)Course表的credit屬性創(chuàng)建索引,并給出上述查詢(xún)?cè)趧?chuàng)建了該索引后的執(zhí)行計(jì)劃；去掉Course表的credit屬性上創(chuàng)建的索引,并給出上述查詢(xún)的執(zhí)行計(jì)劃。對(duì)上述有無(wú)索引個(gè)查詢(xún)執(zhí)行的情況進(jìn)行對(duì)比分析,總結(jié)出什么時(shí)候索引有效？將表Student、Course、Enroll作內(nèi)連接的結(jié)果發(fā)布為HTML網(wǎng)頁(yè)格式。實(shí)驗(yàn)步驟及分析過(guò)程1、設(shè)計(jì)并在MSSQLServer2000中創(chuàng)建以上表結(jié)構(gòu),并設(shè)置完整性約束?！?創(chuàng)建數(shù)據(jù)庫(kù)SC。SQL語(yǔ)句為：createdatabaseSC創(chuàng)建后的數(shù)據(jù)庫(kù)如圖1。圖1創(chuàng)建數(shù)據(jù)庫(kù)圖1創(chuàng)建數(shù)據(jù)庫(kù)的結(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)建注冊(cè)表：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ù)庫(kù)',4>insertintoCoursevalues<'002','java',3>insertintoCoursevalues<'003','操作系統(tǒng)',5>insertintoCoursevalues<'004','軟件工程',4>insertintoCoursevalues<'005','計(jì)算機(jī)英語(yǔ)',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、查詢(xún)所有選修課程學(xué)生的基本信息、課程信息及相應(yīng)的考試成績(jī)。SQL語(yǔ)句為：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é)生、課程及成績(jī)信息圖6選修課程的學(xué)生、課程及成績(jī)信息執(zhí)行結(jié)果分析：只顯示了選了課程的學(xué)生信息。3、查詢(xún)所有學(xué)生的信息,若已選課就還要給出選修課程的信息及考試成績(jī)。SQL語(yǔ)句為：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é)生選課情況及成績(jī)信息執(zhí)行結(jié)果分析：不但列出選了課程的學(xué)生而且還列出來(lái)沒(méi)選課程的學(xué)生信息。4、查詢(xún)所有課程的信息,若課程有學(xué)生選修就還要給出選修課程的學(xué)生的信息及考試成績(jī)。SQL語(yǔ)句為：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é)生及成績(jī)信息執(zhí)行結(jié)果分析：不但列出被選課程的信息和學(xué)生選課情況而且還列出來(lái)沒(méi)被選課程的信息。5、查詢(xún)選修名為"數(shù)據(jù)庫(kù)"的課程的考試成績(jī)最高的學(xué)生的信息?！?先查看選了數(shù)據(jù)庫(kù)課程的學(xué)生信息。SQL語(yǔ)句為：selecta.sno,a.sname,a.sex,a.age,ame,c.gradefromStudenta,Courseb,Enrollc wherea.sno=o=ame='數(shù)據(jù)庫(kù)'執(zhí)行結(jié)果如圖9所示。圖9圖9選修"數(shù)據(jù)庫(kù)"課程的學(xué)生信息執(zhí)行結(jié)果分析：有三位學(xué)生選修了數(shù)據(jù)庫(kù)課程。〔2查詢(xún)選修"數(shù)據(jù)庫(kù)"課程的考試成績(jī)最高的學(xué)生的信息。SQL語(yǔ)句為：selecta.sno,a.sname,a.sex,a.age,ame,d.gradefromStudenta, <o,ame,c.grade,c.snofromCourseb,Enrollc o=ame='數(shù)據(jù)庫(kù)'>dwherea.sno=d.snoandd.grade=<selectmax<grade> from<o,ame,c.grade fromCourseb,Eo=o>d ame='數(shù)據(jù)庫(kù)'>執(zhí)行結(jié)果如圖10所示。圖10圖10選修"數(shù)據(jù)庫(kù)"課程成績(jī)最高學(xué)生執(zhí)行結(jié)果分析：與圖9結(jié)合比較,圖10了列出了選修"數(shù)據(jù)庫(kù)"課程成績(jī)最高學(xué)生信息。6、對(duì)Student的age創(chuàng)建規(guī)則,滿(mǎn)足18≤age≤25,并給出驗(yàn)證實(shí)例及驗(yàn)證結(jié)果?！?創(chuàng)建規(guī)則并且綁定它。SQL語(yǔ)句為：createruleage_ruleas @age>=18and@age<=25sp_bindruleage_rule,'Student.age'〔2插入數(shù)據(jù)檢驗(yàn)。insertintoStudentvalues<'2008006','李志鋒',20,'男'>〔所影響的行數(shù)為1行insertintoStudentvalues<'2008007','李小龍',32,'男'>insertintoStudentvalues<'2008008','楊一民',17,'男'>服務(wù)器:消息513,級(jí)別16,狀態(tài)1,行1列的插入或更新與先前的CREATERULE語(yǔ)句所強(qiáng)制的規(guī)則沖突。該語(yǔ)句已終止。沖突發(fā)生于數(shù)據(jù)庫(kù)'SC',表'Student',列'age'。語(yǔ)句已終止。執(zhí)行結(jié)果分析：規(guī)則設(shè)置成功。7、創(chuàng)建觸發(fā)器：當(dāng)學(xué)生選修一門(mén)新的課程后,Statistics表的cNumber自動(dòng)加1,且creditNumber自動(dòng)增加新選課程的學(xué)分。SQL語(yǔ)句為：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沒(méi)有在Enroll表插入任何數(shù)據(jù)時(shí)表為空,如圖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表中插入選課情況時(shí),在表上建立的觸發(fā)器insert_statisticss就被觸發(fā),進(jìn)行cNumber自動(dòng)加1和creditNumber自動(dòng)增加新選課程的學(xué)分。8、創(chuàng)建視圖：找出所有已修學(xué)分超過(guò)6、所修課程平均分不低于60的學(xué)生的基本信息、以及所修課程的平均分。按照平均分排序,若平均分相同按照學(xué)號(hào)排序。SQL語(yǔ)句為：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é)生都按平均成績(jī)大小從低到高排列且平均分都大于60.當(dāng)平均分相同時(shí),表中冉從寶和楊璐就按照學(xué)號(hào)大小從小低到高排列。9、對(duì)照對(duì)Course表的credit屬性創(chuàng)建索引和去掉Course表的credit屬性上創(chuàng)建的索引查詢(xún)的執(zhí)行計(jì)劃。對(duì)Course表的credit屬性創(chuàng)建索引為：createindexcredit_indexonCourse<credit>對(duì)比有無(wú)索引credit_index情形下,"select*fromCourse"的查詢(xún)的執(zhí)行計(jì)劃分別如圖14、圖15所示。圖14圖14有索引情形下的查詢(xún)的執(zhí)行計(jì)劃圖15無(wú)索引情形下的查詢(xún)的執(zhí)行計(jì)劃結(jié)果分析：由圖14,、圖15可知當(dāng)查詢(xún)是針對(duì)整個(gè)表進(jìn)行,創(chuàng)建索引和不創(chuàng)建索引,查詢(xún)的執(zhí)行計(jì)劃都是一樣的?！?對(duì)比有無(wú)索引credit_index情形下,"select*fromcoursewherecname=’數(shù)據(jù)庫(kù)’"的查詢(xún)的執(zhí)行計(jì)劃分別如圖16、圖17所示。圖16圖16有索引情形下的查詢(xún)的執(zhí)行計(jì)劃圖17無(wú)索引情形下的查詢(xún)的執(zhí)行計(jì)劃結(jié)果分析：由圖16,、圖17可知當(dāng)條件針對(duì)的字段不是創(chuàng)建了索引的那個(gè)字段時(shí),索引是無(wú)效的。有索引和無(wú)索引,查詢(xún)的執(zhí)行計(jì)劃都是一樣的?！?對(duì)比有無(wú)索引credit_index情形下,"select*fromcoursewherecredit=3"的查詢(xún)的執(zhí)行計(jì)劃分別如圖18、圖19所示。圖18圖18有索引情形下的查詢(xún)的執(zhí)行計(jì)劃圖19無(wú)索引情形下的查詢(xún)的執(zhí)行計(jì)劃結(jié)果分析：由圖18,、圖19可知where條件為credit的屬性,索引起效。有索引的查詢(xún)效率比沒(méi)有索引的查詢(xún)效率高數(shù)十倍?！?對(duì)比有無(wú)索引credit_index情形下,"select*fromcoursewherecredit>2andcredit<5”的查詢(xún)的執(zhí)行計(jì)劃分別如圖20、圖21圖20圖20有索引情形下的查詢(xún)的執(zhí)行計(jì)劃圖21無(wú)索引情形下的查詢(xún)的執(zhí)行計(jì)劃結(jié)果分析：由圖20,、圖21可知where條件為credit的屬性,索引起效。有索引的查詢(xún)效率比沒(méi)有索引的查詢(xún)效率高數(shù)十倍?！?對(duì)比有無(wú)索引credit_index情形下,"updatecoursesetcredit=3wherecredit=2”圖22圖22有索引情形下的查詢(xún)的執(zhí)行計(jì)劃圖23無(wú)索引情形下的查詢(xún)的執(zhí)行計(jì)劃結(jié)果分析：由圖20,、圖21可知where條件為credit的屬性,索引起效。有索引的查詢(xún)效率比沒(méi)有索引的查詢(xún)效率高數(shù)十倍。索引小結(jié)：=1\*GB3①索引是對(duì)數(shù)據(jù)庫(kù)表中的一列或多列的值進(jìn)行排序的結(jié)構(gòu),有助于更快地獲取信息。=2\*GB3②只有當(dāng)經(jīng)常查詢(xún)索引列中的數(shù)據(jù)時(shí),才需要?jiǎng)?chuàng)建索引。=3\*GB3③折衷：索引提高查詢(xún)速度vs.索引占用磁盤(pán)空間,降低數(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è)格式?！?在企業(yè)管理器的控制臺(tái)