SQL語句創(chuàng)建學(xué)生信息數(shù)據(jù)庫表的示例實用文檔

用SQL語句創(chuàng)建如下三個基本表：學(xué)生表(Student)、課程表（Course）、學(xué)生選課表（SC），結(jié)構(gòu)如下所示Student表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Sno學(xué)號字符串，長度為7主碼Sname姓名字符串，長度為10非空Ssex性別字符串，長度為2取‘男’或‘女’Sage年齡整數(shù)取值15～45Sdept所在院系字符串，長度為20默認(rèn)為‘計算機(jī)系’Createtable Student(Snovarchar(7)primarykey,Snamevarchar(10)notnull,Ssexchar(2)check(Ssex=‘男’orSsex=’女’),Sageintcheck(Sagebetween15and45),Sdeptvarchar(20)default(‘計算機(jī)系’))Course表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Cno課程號字符串，長度為10主碼Cname課程名字符串，長度為20非空Ccredit學(xué)分整數(shù)取值大于0Semester學(xué)期整數(shù)取值大于0Period學(xué)時整數(shù)取值大于0Createtablecourse(Cnovarchar(10)primarykey,Cnamevarchar(20)notnull,Ccreditintcheck(Sctedit>0),Semesterintcheck(Semester>0),Periodintcheck(Period>0))SC表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Sno學(xué)號字符串，長度為7主碼，引用Student的外碼Cno課程號字符串，長度為10主碼，引用Course的外碼Grade成績整數(shù)取值0～100CreatetableSC(Snovarchar(7)foreignkeyreferencesstudent(Sno),Cnovarchar(10)foreignkeyreferencescourse(Cno),Gradeintcheck(Gradebetween0and100),Primarykey(Sno,Cno))1．查詢學(xué)生選課表中的全部數(shù)據(jù)。SELECT*FROMSCgo2．查詢計算機(jī)系學(xué)生的姓名、年齡。SelectSname,SageFromStudentWhereSdept=’計算機(jī)系’3．查詢成績在70～80分之間的學(xué)生的學(xué)號、課程號和成績。SelectSno,Cno,GradeFromCourse,ScWherecourseo=scoandsc.Gradebetween70and804．查詢計算機(jī)系年齡在18～20之間且性別為“男”的學(xué)生的姓名和年齡。SelectSname,SageFromStudentWhereSagebetween18and20andSsex=’男’andSdept=’計算機(jī)系’go5．查詢課程號為“C01”Selecttop1Gradeselectmax(Grade)as最高分FromScfromScWhereCno=’C01’whereCno=’COrderbyGradedescorderbyGradedesc6．查詢計算機(jī)系學(xué)生的最大年齡和最小年齡。Selectmax(Sage)as年齡最大，min(Sage)as年齡最小FromStudentWhereSdept=’計算機(jī)系’7．統(tǒng)計每個系的學(xué)生人數(shù)。Selectcount(Sdept)as學(xué)生人數(shù)，SdeptFromStudentGroupbySdept8．統(tǒng)計每門課程的選課人數(shù)和考試最高分。Selectcount(Sno)as選課人數(shù)，c.Sno,max(Grade)as最高分FromCoursecleftjoinScsonco=soGroupbyco9．統(tǒng)計每個學(xué)生的選課門數(shù)和考試平均成績，并按學(xué)號的升序顯示結(jié)果。Selectsno,avg(grade)as’平均成績’,count(cno)as’選課門數(shù)’FromscGroupbysnoOrderbysno10．查詢總成績超過200分的學(xué)生，要求列出學(xué)號、總成績。Selectsno,sum(grade)FromscGroupbysnoHavingsum(grade)>20011．查詢選修了課程“C02”Selectsname,sdeptFromstudents1,scs2Wheres1.sno=s2.snoands2o=’c0212．查詢成績在80分以上的學(xué)生的姓名、課程號和成績，并按成績的降序排列結(jié)果。Selects1.sname,s2o,s2.gradeFromstudents1,scs2Wheres1.sno=s2.snoandgrade>80Orderbygradedesc13．查詢哪些課程沒有人選修、要求列出課程號和課程名。Selectco,cameFromcoursecleftjoinscsonco=soGroupbyco,cameHavingcount(s.sno)=014．用子查詢實現(xiàn)如下查詢：(1)查詢選修了課程“C01”Selectsname,sdept,snoFromstudentWheresnoin(SelectsnoFromscWherecno=’c01)(2)查詢信息系成績在80分以上的學(xué)生的學(xué)號、姓名。Selectsno,snameFromstudentWheresdept=’外語系’andsnoin(SelectsnoFromscWheregrade>80)(3)查詢計算機(jī)系考試成績最高的學(xué)生的姓名。Selects1.snamefromstudentsWheresdept=’計算機(jī)系’andsnoin(selectsnofromscWheregradein(selectmax(Grade)fromsc))15．刪除選課成績小于50分的學(xué)生的選課記錄。DeletefromscWheregrade<70Select*fromsc—驗證16．將所有選修了課程“C01”UpdatescSetgrade=grade+10Wherecno=’c0117．將計算機(jī)系所有選修了課程“計算機(jī)文化基礎(chǔ)”課程的學(xué)生的成績加10分。Select*fromscUpdatescSetgrade=grade+10Wherecnoin(selectcnofromcourseWherecname=’計算機(jī)文化基礎(chǔ)’)18．創(chuàng)建查詢學(xué)生的學(xué)號、姓名、所在系、課程號、課程名、課程學(xué)分的視圖。Select*fromcourseSelect*fromstudentsSelect*fromscCreateview學(xué)生基本信息AsSelectstudents.sno,sname,sdept,sco,cname,ccreditFromcourse,sc,studentsWherecourseo=scoAndsco=students.sno19．創(chuàng)建查詢每個學(xué)生的平均成績的視圖，要求列出學(xué)生學(xué)號及平均成績。Createviews_avgAsSelectsno,avg(Grade)as平均成績fromscGroupbysno20．創(chuàng)建查詢每個學(xué)生的選課學(xué)分的視圖，要求列出學(xué)生學(xué)號及總學(xué)分。Createviews_scAsSelectstudents.sno,sum(ccredit)as總學(xué)分fromStudents,sc,courseWherestudents.sno=sc.snoAndsco=courseoGroupbystudents.sno21．用SQL語句創(chuàng)建一個名為f_1的函數(shù)，該函數(shù)能夠求出3到100之間的所有素數(shù)之和。Createfunctionf_1()ReturnsintAsBeginDeclare@aint,@bint,@iint,@sumintSet@i=3Set@sum=0While@i<101BeginSet@b=0While@a<=@i/2BeginIf@i%@a=0BeginSet@b=1BreakEndSet@a=@a+1EndIf@b=0--@b為0說明之前沒有比@i小的數(shù)字可以把@i整除BeginSet@sum=@sum+@iEndSet@i=@i+1EndReturn@sumEndGoSelectdbo.f_1()22．用SQL語句創(chuàng)建一個名為f_2的函數(shù)，該函數(shù)能夠求出任意兩個數(shù)的最大值。Createfunctionf_2(@x1int,@x2int)returnsintAsBeginDeclare@maxintIf@x1>@x2Return@maxEndSelectdbo.f_2(2,6)23．用SQL語句創(chuàng)建一個名為pro_get_stu_information的存儲過程，該存儲過程能夠根據(jù)用戶指定的Sno（學(xué)號）求出與該學(xué)號對應(yīng)的學(xué)生姓名、課程名、成績。Createprocedurepro_get_stu_information@mchar(6)outputAsSelectsname,cname,gradefromstudents,sc,courseWherestudents.sno=sc.snoandsco=courseoandsc.sno=@mExecpro_get_stu_information’060300224．為“學(xué)生”表創(chuàng)建一個依賴于“學(xué)號”的唯一的、非聚集的索引Createuniquenonclusteredindexstu_intonstudents(sno)25．通過游標(biāo)逐行讀取“學(xué)生”表的記錄Declarestu_curcursorforSelect*fromstudentsforreadonlyOpenstu_curFetchstu_curClosestu_curDeallocatestu_cur用MySQL創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)表：步驟：使用show語句找出在服務(wù)器上當(dāng)前存在什么數(shù)據(jù)庫：mysql>showdatabases;創(chuàng)建一個數(shù)據(jù)庫test：mysql>createdatabasetest;選擇你所創(chuàng)建的數(shù)據(jù)庫：mysql>usetest;4創(chuàng)建一個數(shù)據(jù)表：首先查看剛才創(chuàng)建的數(shù)據(jù)庫中存在什么表：mysql>showtables;（說明剛才創(chuàng)建的數(shù)據(jù)庫中還沒有數(shù)據(jù)庫表）接著我們創(chuàng)建一個關(guān)于students的數(shù)據(jù)表：包括學(xué)生的學(xué)號(id)，姓名(name)，性別(sex)，年齡(age)。mysql>createtablestudents(idintunsignednotnullauto_incrementprimarykey,namechar(8)notnull,sexchar(4)notnull,agetinyintunsignednotnull,);解釋：以"idintunsignednotnullauto_incrementprimarykey"行進(jìn)行介紹:"id"為列的名稱;"int"指定該列的類型為int(取值范圍為-8388608到8388607),在后面我們又用"unsigned"加以修飾,表示該類型為無符號型,此時該列的取值范圍為0到16777215;"notnull"說明該列的值不能為空,必須要填,如果不指定該屬性,默認(rèn)可為空;"auto_increment"需在整數(shù)列中使用,其作用是在插入數(shù)據(jù)時若該列為NULL,MySQL將自動產(chǎn)生一個比現(xiàn)存值更大的唯一標(biāo)識符值。在每張表中僅能有一個這樣的值且所在列必須為索引列。"primarykey"表示該列是表的主鍵,本列的值必須唯一,MySQL將自動索引該列。下面的char(8)表示存儲的字符長度為8,tinyint的取值范圍為-127到128,default屬性指定當(dāng)該列值為空時的默認(rèn)值。創(chuàng)建一個表后，用showtables顯示數(shù)據(jù)庫中有哪些表：mysql>showtables；顯示表結(jié)構(gòu)：mysql>describestudents;在表中添加記錄：首先用select命令來查看表中的數(shù)據(jù)：mysql>select*fromstudents;（說明剛才創(chuàng)建的數(shù)據(jù)庫表中還沒有任何記錄）接著加入一條新紀(jì)錄：mysql>insertintostudentsvalue(‘01’,’Tom’，’F’，’18’)；再用select命令來查看表中的數(shù)據(jù)的變化：mysql>select*fromstudents;用文本方式將數(shù)據(jù)裝入一個數(shù)據(jù)庫表：創(chuàng)建一個文本文件“student.sql”，每行包括一個記錄，用TAB鍵把值分開，并且以在createtable語句中列出的次序，例如：02TonyF1803AmyM1804LisaM18將文本文件“student.sql”裝載到students表中：mysql>loaddata