sql數據庫實例(數據庫入門)

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