數(shù)據(jù)庫學(xué)生管理系統(tǒng)(存儲(chǔ)版)
【正文】 urse set teacherID in(select teacherID from inserted)where teacherID in(select teacherID from deleted)end用戶輸入：update teachers set teacherID=39。,x outputselect x as 人數(shù)(5)創(chuàng)建一個(gè)存儲(chǔ)過程，輸入老師姓名,顯示該老師所教的所有課程編號(hào)，課程名稱，并顯示這位老師的ID號(hào)，所屬院系，教研室名稱create proc teacherprocteacher char(10),ID char(10) output,college char(10) outchar,department char(20) outputasbeginselect o,coursenamefrom teachers,teachercoursewhere = andteachername=teacherselect ID=teacherID,college=collegename,department=departmentfrom teacherswhere teachername=teacherendgo用戶請(qǐng)輸入：declare x char(10),y char(20),z char(10)exec teacherproc 39。 3） 創(chuàng)建索引在成績上創(chuàng)建索引score_indexcreate nonclustered index score_indexon selectcourse(score desc)在學(xué)生年齡上創(chuàng)建索引student_sage_indexcreate nonclustered index student_sage_indexon student(sage desc)在老師成績上創(chuàng)建索引teachers_sage_indexcreate nonclustered index teachers_sage_indexon teachers(sage desc)4） 創(chuàng)建視圖為學(xué)生信息創(chuàng)建視圖student_viewcreate view student_viewasselect*from student創(chuàng)建各院系學(xué)生的視圖college_major_screate view college_major_sasselect sno,studentname,collegename,majorname,telfrom studentgroup by collegename,majorname,sno,studentname,tel創(chuàng)建各班級(jí)視圖class_screate view class_sasselect sno,studentname,grade,class,telfrom studentgroup by collegename,majorname,grade,class,sno,studentname,tel為各學(xué)院課程創(chuàng)建視圖college_coursecreate view college_courseasselect *from coursegroup by collegename,o,coursename,credit為各班的學(xué)生選課成績創(chuàng)建視圖selectcourse_screate view selectcourse_s(sno,o,teacherID,score)asselect ,o,teacherID,scorefrom selectcourse,class_swhere =為各班學(xué)生的學(xué)號(hào)及平均成績創(chuàng)建視圖avg_screate view avg_s(sno,grade,class,gavg)asselect ,grade,class,avg(score)from selectcourse,class_swhere =group by grade,class,為老師信息創(chuàng)建視圖teachers_viewcreate view teachers_viewasselect *from teachersgroup by collegename,department,teacherID,teachername,sex,sage,tel為教研室創(chuàng)建視圖depart_viewcreate view depart_viewasselect *from departgroup by collegename,departID,department為老師任課創(chuàng)建視圖teachercourse_viewcreate view teachercourse_viewasselect *from teachercoursegroup by teacherID,o,coursename為各學(xué)院所擁有的專業(yè)創(chuàng)建視圖c_major_viewcreate view c_major_view(collegename,collegeID,majorname,majorID)asselect collegeID,majorID,majornamefrom college,majorwhere =5） 創(chuàng)建存儲(chǔ)過程(1)創(chuàng)建一個(gè)存儲(chǔ)過程，輸入學(xué)號(hào)可以查詢?cè)搶W(xué)生的各科成績create proc scoreprocsno char(10)asbeginselect ,studentname,coursename,score,creditfrom student,course,selectcourse where = and = and =snoendgo用戶請(qǐng)輸入：exec scoreproc 39。D:\DATA\39。3. 索引在成績上創(chuàng)建索引score_index在學(xué)生年齡上創(chuàng)建索引student_sage_index在老師成績上創(chuàng)建索引teachers_sage_index4. 設(shè)置視圖為學(xué)生信息創(chuàng)建視圖student_view創(chuàng)建各院系學(xué)生的視圖college_major_s創(chuàng)建各班級(jí)視圖class_s為各學(xué)院課程創(chuàng)建視圖college_course為各班的學(xué)生選課成績創(chuàng)建視圖selectcourse_s為各班學(xué)生的學(xué)號(hào)及平均成績創(chuàng)建視圖avg_s為老師信息創(chuàng)建視圖teachers_view為教研室創(chuàng)建視圖depart_view為老師任課創(chuàng)建視圖teachercourse_view為各學(xué)院所擁有的專業(yè)創(chuàng)建視圖c_major_view5. 設(shè)置存儲(chǔ)過程，輸入學(xué)號(hào)可以查詢?cè)搶W(xué)生的各科成績，輸入課程名顯示該門課程的平均成績和選課人次，輸入學(xué)院名稱顯示該學(xué)院的代號(hào)，學(xué)院名稱以及包含的各個(gè)專業(yè)的代號(hào)和名稱，并統(tǒng)計(jì)該學(xué)院包含專業(yè)的數(shù)目，輸入學(xué)院名稱,專業(yè)名稱，年級(jí)，班級(jí)顯示該班級(jí)所有學(xué)生的信息，并統(tǒng)計(jì)該班的人數(shù)，輸入老師姓名,顯示該老師所教的所有課程編號(hào)，課程名稱，并顯示這位老師的ID號(hào)，所屬院系，教研室名稱6. 設(shè)置觸發(fā)器(1)要求學(xué)生選課課程表中的課程號(hào)被刪除時(shí)，學(xué)生選課成績表中的對(duì)應(yīng)的課程號(hào)的信息也被刪除。學(xué)生信息表student名稱字段類型長度是否為空備注學(xué)號(hào)snochar10否主鍵姓名studentnamevarchar10否性別sexchar2年齡sagesmallint學(xué)院名稱collegenamevarchar
公司管理相關(guān)推薦
鄂ICP備17016276號(hào)-1