學(xué)生檔案管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)書-展示頁
2025-05-18 22:11本頁面
【正文】 Sno char(10) NOT NULL, Cno char(10) NOT NULL, Score int NULL)1學(xué)生表：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違紀(jì)記錄表：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查詢獲獎(jiǎng)情況USE StudentRecordDBGOCREATE VIEW HJ_VIEW AS SELECT , S_name, RewardName FROM Student,HuoJiang,Reward WHERE = AND =GO查詢違紀(jì)情況USE StudentRecordDBGOCREATE VIEW CF_VIEW AS SELECT , S_name, PunishName,chulijieguo FROM Student,WeiJi,Punish WHERE = AND =GO通過姓名和課程名查詢該生該課程成績IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=39。 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三、索引創(chuàng)建索引use StudentRecordDBgocreate index ind_ClassID on Class(ClassID)create index ind_Cno on Course(Cno)create index ind_DeptID on Dept(DeptID)create index ind_huojiang on HuoJiang(Sno,RewardID)create index ind_PunishID on Punish(PunishID)create index ind_RewardID on Reward(RewardID)create index ind_RoomID on Room(RoomID)create index ind_sc on SC(Sno,Cno)create index ind_Sno on Student(Sno)create index ind_Uid on Users(Uid)create index ind_weiji on WeiJi(Sno,PunishID)go、自定義約束class表約束use StudentRecordDBgoalter table Class add constraint pk_ClassID primary key(ClassID)gocourse表約束use StudentRecordDBgoalter table Course add constraint pk_Cno primary key(Cno)godept表約束use StudentRecordDBgoalter table Dept add constraint pk_DeptID primary key(DeptID)gohuojiang表約束use StudentRecordDBgoalter table HuoJiang add constraint pk_huojiang primary key(Sno,RewardID)gopunish表約束use StudentRecordDBgoalter table Punish add constraint pk_PunishID primary key(PunishID)goreward表約束
教學(xué)教案相關(guān)推薦
鄂ICP備17016276號(hào)-1