数据库学分管理系统SQL语句_百度文库

1、create database Credit -创建学分管理系统数据库 on ( name=Credit_data, filename=c:CreditData.mdf, size=10, maxsize=50, filegrowth=5 create table Course -创建课程信息表 ( c_no char(20 primary key not null, c_name char(20, score float, /*学分*/ type char(20, create table Student -创建学生信息表 ( s_no char(20 primary key not nul

2、l, s_name char(20, sex char(10, class char(20, dept char(20, create table SC -学生选课情况表 ( s_no char(20 not null, c_no char(20 not null, grade int, score float create unique index SC_ind on SC(s_no,c_no insert into Course(c_no,c_name,score,type -录入各个表的基本资料信息 select 001,C语言,30,基础课 union select 002,数据结构,

3、32,专业课 union select 003,操作系统,30,基础课 union select 004,计算机网络,28,选修课 union select 005,Java语言,32,专业课 union select 006,网络基础,32,专业课 union select 007,大学英语,30,基础课 union select 008,现代通信,27.5,选修课 union select 009,数据库,32,专业课 union select 010,网络安全,30,基础课 union select 011,近代史,10,人文类 union select 012,数据库实验,24,实验课

4、 union select 013,C+实验,24,实验课 union select 014,马列主义,10.5,人文类 union select 015,思修品德,10.5,人文类 union select 016,趣味经济学,27.5,选修课 union select 017,C语言实验,24,实验课 union select 018,高等数学,30.5,基础课 union select 019,线性代数,0.5,基础课 union select 020,计算机组成原理,32,专业课 insert into SC select 09110901,001,80,28 union select

5、 09110901,002,85,30.5 union select 09110901,004,85,24.5 union select 09110901,019,80,28 union select 09110901,009,95,31.5 union select 09110901,011,80,8 union select 09110901,017,70,21 union select 09110902,005,80,30 union select 09110902,006,83,30 union select 09110902,018,70,28.5 union select 0911

6、0902,003,80,28 union select 09110902,016,60,24 union select 09110902,014,70,8.5 union select 09110904,007,85,28.5 union select 09110904,018,70,25.5 union select 09110904,002,80,30 union select 09110904,006,85,30.5 union select 09110904,015,70,8.5 union select 09110904,017,75,21.5 union select 091109

7、06,007,80,28 union select 09110906,001,80,27.5 union select 09110906,009,75,27 union select 09110906,020,75,27 union select 09110906,012,70,21 union select 09110906,014,65,8.5 union select 09110909,001,80,28 union select 09110909,018,75,27 union select 09110909,006,80,30 union select 09110909,020,55

8、,0 union select 09110909,002,65,25.5 union select 09110909,015,60,8 union select 09110909,017,80,22 union select 09110910,007,80,27 union select 09110910,019,85,27.5 union select 09110910,006,70,27 union select 09110910,005,75,27.5 union select 09110910,014,55,0 union select 09110910,015,65,8.5 unio

9、n select 09110910,017,85,22 union select 09110912,001,70,27 union select 09110912,020,70,29 union select 09110912,014,80,9 union select 09110912,016,90,26 union select 09110912,013,80,22 insert into Student select 09110901,蔡仕航,男,091109,软件 union select 09110902,陈建辉,男,091109,软件 union select 09110903,陈

10、靖宇,男,091109,软件 union select 09110904,陈双,女,091109,软件 union select 09110905,葛金文,男,091109,软件 union select 09110906,管峡涛,男,091109,软件 union select 09110907,郭成,男,091109,软件 union select 09110908,郭贤韬,男,091109,软件 union select 09110909,韩勇涛,男,091109,软件 union select 09110910,洪家庄,男,091109,软件 union select 09110911

11、,胡胜,男,091109,软件 union select 09110912,江志勇,男,091109,软件 create view show -创建视图，提供基本查询 As select sc.s_no,class,s_name,Course.c_no,c_name,sc.score,type From SC,Student,Course Where SC.s_no=Student.s_no and SC.c_no=Course.c_no /*触发器*/ create trigger c_insert -在Course表上创建插入触发器 on Course for insert as sele

12、ct * from show order by s_no create trigger c_delete -在Course表上创建删除触发器 on Course for delete as declare oldcno varchar(20 select oldcno=c_no from deleted delete from SC where c_no=oldcno create trigger c_update -在Course表上创建修改触发器 on Course for update as declare oldcno varchar(20,newcno varchar(20 if u

13、pdate(c_no begin select oldcno=c_no from deleted select newcno=c_no from inserted update SC set c_no=newcno where c_no=oldcno select * from show end update Course -在Course表上修改数据 set c_no=001 where c_name=C语言 create trigger sc_insert -在SC表上创建插入触发器 on SC for insert as select * from show order by s_no

14、create trigger sc_delete -在SC表上创建删除触发器 on SC for delete as select * from show order by s_no create trigger sc_update -在SC表上创建修改触发器 on SC for update as select * from show order by s_no create trigger s_delete -在Student表上创建删除触发器 on Student for delete as declare oldsno varchar(20 select oldsno=s_no fro

15、m deleted delete from SC where s_no=oldsno create trigger s_update -在Student表上创建修改触发器 on Student for update as declare newsno varchar(20 update SC set s_no=newsno where s_no in(select s_no from deleted /*存储过程*/ create procedure luru -创建存储过程，在SC表中插入信息 (录入某位学生的学分信息 ( sno char(20, cno char(20, grade in

16、t, score float as insert into SC(s_no,c_no,grade,score values(sno,cno,grade,score exec luru 09110901,007,85,28.5 -录入信息 create procedure sno -创建存储过程，按给定学号，显示某位学生的学分完成情况 (sno char(20 as select s_no,class,s_name,c_name,type,score from show where s_no=sno order by s_no,type exec sno 09110901 -给定一个学号查询 c

17、reate procedure sum_score -创建存储过程，按给定学号，显示某位学生的各个课程类型获得学分情况 (sno char(20 as select s_no,s_name,type,sum(score as score from show where s_no=sno group by s_no,type,s_name exec sum_score 09110901 -给定一个学号查询 create procedure class -创建存储过程，按给定班号，显示该班所有学生的学分完成情况 (class char(20 as select * from show where

18、class=class order by class exec class 091109 -给定一个班号查询 create procedure score_update -给定学号，修改该学生的学分情况 ( sno char(20, cno char(20, grade int, score float as update SC set c_no=cno,grade=grade,score=score where s_no=sno exec score_update sno=09110902,cno=016,grade=75,score=25 -给定学号及学分情况，执行修改 create pr

19、ocedure paixu -创建存储过程，按照某类课程的学分高低进行排序 (cno char(20 as select * from show where c_no=cno order by score desc exec paixu 002 -给定一门课程的课程号 create view graduation -创建视图，用于判定学生是否毕业 as select s_no,s_name,type,sum(score as score from show group by s_no,type,s_name create procedure judge -判定毕业 (sno char(10 as declare jc float declare rw float declare sy float declare xx float declare zy float declare x int set x=0 select jc=score from graduation where sno=s_no and type=基础课 if jc=50 begin set x=1 select rw=score from graduation where sno=s_no and typ