學生檔案管理系統(tǒng)數(shù)據(jù)庫設(shè)計書-免費閱讀
【正文】 PunishID_punish_tri39。tr39。USE StudentRecordDBGOIF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=39。成績超出~100！請重新輸入。 AND TYPE=39。)DROP PROCEDURE Pro_S_nameGOcreate procedure Pro_QScore S_name_in char(8),Cname_in char(10),Score_out int outputAs select Score_out=Score from Student,Course,SC where = and = and S_name=S_name_in and Cname=Cname_in建立一個觸發(fā)器，如果修改Student中的學號，則SC、HouJiang、WeiJi中的學號自動修改。)GO班級表：CREATE TABLE Class( ClassID CHAR(8) NOT NULL, ClassName CHAR(20) NOT NULL, DeptID CHAR(8) NULL,)院系表CREATE TABLE College( CollegeID CHAR(8) NOT NULL, CollegeName CHAR(20) NOT NULL, CollegeSummary CHAR(10) NULL,)課程表CREATE TABLE Course( Cno CHAR(10) NOT NULL, Cname CHAR(10) NOT NULL, keshi int NOT NULL, xuefen int NOT NULL, ksxs char(8) NOT NULL)系表：CREATE TABLE Dept( DeptID CHAR(8) NOT NULL, DeptName CHAR(30) NOT NULL, CollegeID CHAR(8) NULL)獲獎表：CREATE TABLE HuoJiang( Sno CHAR(10) NOT NULL, RewardID CHAR(8) NOT NULL, Time datetime NULL)處罰表：CREATE TABLE Punish( PunishID char(8) NOT NULL, PunishName char(10) NULL)獎勵表：CREATE TABLE Reward( RewardID char(8) NOT NULL, RewardName char(10) NOT NULL, RewardLevel char(10) NOT NULL)宿舍表：CREATE TABLE Room( RoomID char(8) NOT NULL, RoomAdd char(20) NOT NULL, RoomNum int NOT NULL)選課表：CREATE TABLE SC( Sno char(10) NOT NULL, Cno char(10) NOT NULL, Score int NULL)1學生表：CREATE TABLE Student( Sno char(10) NOT NULL, S_name char(8) NOT NULL, S_xmpy char(20) NOT NULL, S_cym char(8) NULL, S_sex char(2) NOT NULL, S_age int NOT NULL, S_mz char(10) NOT NULL, S_jg char(100) NOT NULL, S_csrq datetime NOT NULL, RommID int NULL, ClassID int NULL, S_sfzh char(15) NOT NULL, S_image image NULL, S_zzmm char(4) NOT NULL, S_csd char(100) NULL, S_syd char(100) NOT NULL, S_rxnf datetime NOT NULL, S_byxx char(20) NOT NULL, S_pycc char(10) NOT NULL, S_jtzz char(100) NOT NULL, S_jtlxdh char(15) NULL, S_sj char(11) NULL)1用戶表：CREATE TABLE Users( Uid int NOT NULL, Uname char(20) NOT NULL, PassWord char(20) NOT NULL)1違紀記錄表：CREATE TABLE WeiJi( Sno char(10) NOT NULL, PunishID char(8) NOT NULL, Time datetime NOT NULL, chulijieguo text NULL)GO二、視圖查詢成績USE StudentRecordDBGOCREATE VIEW SCORE_VIEW AS SELECT , S_name,Cname, Score FROM Student,Course,SC WHERE = AND =GO查詢基本信息USE StudentRecordDBGOCREATE VIEW jbxx_VIEW AS SELECT ,S_name,S_sex,S_age,S_jg,S_zzmm,S_rxnf, S_jtzz,S_sj, FROM Student,Class,Dept,Room WHERE = AND = AND =GO查詢獲獎情況USE StudentRecordDBGOCREATE VIEW HJ_VIEW AS SELECT , S_name, RewardName FROM Student,HuoJiang,Reward WHERE = AND =GO查詢違紀情況USE StudentRecordDBGOCREATE VIEW CF_VIEW AS SELECT , S_name, PunishName,chulijieguo FROM Student,WeiJi,Punish WHERE = AND =GO通過姓名和課程名查詢該生該課程成績IF EXISTS (SELECT NAME FROM sysobjects
教學教案相關(guān)推薦
鄂ICP備17016276號-1