SQL课程设计报告.docx
- 文档编号:10383651
- 上传时间:2023-02-10
- 格式:DOCX
- 页数:22
- 大小:78.64KB
SQL课程设计报告.docx
《SQL课程设计报告.docx》由会员分享,可在线阅读,更多相关《SQL课程设计报告.docx(22页珍藏版)》请在冰豆网上搜索。
SQL课程设计报告
课程设计报告
110054B————张豪
创建数据库:
createdatabaseSanjiangMIS
onprimary
(
name=SanjiangMIS_data,
filename='f:
\SanjiangMIS.mdf',
size=10,
maxsize=unlimited,
filegrowth=10%
)
logon
(
name=Sanjiang_log,
filename='f:
\SanjiangMIS.ldf',
size=1,
maxsize=unlimited,
filegrowth=10%
)
创建表:
createtableEmployee
(
EmNointconstraintpk_EmNoprimarykey,
EmNamevarchar(20),
EmBirtdaydatetime,
EmCardIdvarchar(18),
EmPositionchar(20),
EmSexchar
(2)
)
EmNo
工号
int
primarykey
EmpName
职工姓名
varchar(20)
EmBirthday
职工生日
datetime
EmCardId
职工身份证号
varchar(18)
EmPosition
职称
char(20)
EmSex
职工性别
char
(2)
createtableDepartment
(
DepNointprimarykey,
DepNamevarchar(20),
)
DepNo
系别
int
primarykey
DepName
系名
varchar(20)
createtableDorm
(
DormNovarchar(8)constraintpk_DormNoprimarykeyconstraintck_DormNocheck(DormNolike'[0-9][0-9]-[0-9][0-9][0-9]'),
DormDepintconstraintfk_DormDepforeignkeyreferencesDepartment(DepNo),
DormNuintconstraintck_DormNucheck(DormNuin(4,6)),
DormNowNuintconstraintck_DormNowNucheck(DormNowNu>=0andDormNowNu<=6),
DormSexchar
(2)constraintck_DormSexcheck(DormSexin('男','女'))
)
DormNo
宿舍号
varchar(8)
primarykey
DormDep
系别
Int
foreignkey
DormNu
容量
Int
check
DormNowNu
剩余容量
Int
check
DormSex
性别
char
(2)
check
createtableClass
(
ClassNamevarchar(20),
ClassNointconstraintpk_ClassNoprimarykey,
ClassDepNointconstraintfk_ClassDepNoforeignkeyreferencesDepartment(DepNo),
Classtypevarchar(20),
ClassScureint,
ClassTimeint
)
ClassName
课程名
varchar(20)
ClassNo
课程号
int
primarykey
ClassDepNo
系别
int
foreignkey
ClassType
课程类型
varchar(20)
ClassScure
学分
int
ClassTime
可是
int
createtableTeacher
(
TeacherNointconstraintfk_TeacherNoforeignkeyreferencesEmployee(EmNo)
constraintpk_Teacherprimarykey,
TeacherDepNointconstraintfk_TeacherDepNoforeignkeyreferencesDepartment(DepNo),
Ispartimebit
)
TeacherNo
教师工号
int
foreignkeyprimarykey
TeacherDepNo
教师系别
int
foreignkey
Ispartime
是否专职
bit
Notnull
createtableIncome
(
EmployeeNointconstraintfk_EmployeeNoforeignkeyreferencesEmployee(EmNo),
PayBaseint,
PayYearMondatetime,
PayOtherint,
YearRewordint,
ClassPayint,
)
EmployeeNo
工号
int
foreignkey
PayBase
基本工资
int
PayYearMon
年月
datetime
PayIther
三江津贴
int
YearRword
年终奖
int
ClassPay
课时费
int
createtableStudent
(
SNovarchar(20)constraintpk_SNoprimarykeyconstraintck_SNocheck(SNolike'[A,B,Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
SNamevarchar(20),
SBirthdaydatetime,
SCardIDvarchar(20),
DepNointconstraintfk_DepNoforeignkeyreferencesDepartment(DepNo),
SSexchar
(2)constraintck_SSexcheck(SSexin('男','女')),
DormNovarchar(8)constraintfk_DormNoforeignkeyreferencesDorm(DormNo),
)
SNo
学号
varchar(20)
primarykey
SName
姓名
varchar(20)
SBirthday
生日
datetime
SCardId
身份证号
varchar(20)
DepNo
系号
int
foreignkey
SSex
性别
char
(2)
check
DromNo
宿舍号
varchar(8)
foreignkey
createtableStudentClass
(
ClassNointconstraintfk_ClassNoforeignkeyreferencesClass(ClassNo),
StudentNovarchar(20)constraintfk_StudentNoforeignkeyreferencesStudent(SNo)constraintpk_SCprimarykey(ClassNo,StudentNo),
Gradefloat
)
ClassNo
课程号
int
foreignkeyprimarykey
StudentNo
学号
varchar(20)
foreignkeyprimarykey
Grade
成绩
float
createtableEducateClass(
EducateClassNovarchar(20)constraintpk_EducateClassNoprimarykey,
ClassNointconstraintfk_ClassNoEduforeignkeyreferencesClass(ClassNo),
TeacherNointconstraintfk_TeacherNo1foreignkeyreferencesTeacher(TeacherNo),
ClassRoomNoint,
ClassTermvarchar(12)constraintck_ClassTermcheck(ClassTermlike'[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9]'),
ClassThinkvarchar(400)
)
EducateClassNo
教学班号
varchar(20)
primarykey
ClassNo
学期
int
foreignkey
TeacherNo
班级号
int
foreignkey
ClassRoomNo
课程号
int
ClassTerm
教师工号
varchar(12)
fcheck
ClassThink
评价
varchar(400)
录入表数据:
insertintoDepartmentvalues(1,'Computer')
insertintoDepartmentvalues(2,'English')
insertintoDepartmentvalues(3,'Art')
insertintoDepartmentvalues(4,'Plant')
insertintoDormvalues('01-111',1,6,5,'男')
insertintoDormvalues('02-111',2,6,5,'女')
insertintoDormvalues('03-111',3,6,5,'女')
insertintoDormvalues('04-111',4,4,3,'男')
insertintostudentvalues('A11111111','Tomy','1992-06-11',,1,'男','01-111')
insertintostudentvalues('A11111112','Tony','1992-05-10',,1,'女','02-111')
insertintostudentvalues('A11111113','Linda','1992-05-9',4',2,'女','03-111')
insertintostudentvalues('A11111114','Tods','1992-05-8',5',1,'男','04-111')
insertintoClassvalues('Computer',1,1,'专业课',2,24)
insertintoClassvalues('English',2,2,'专业课',2,24)
insertintoClassvalues('Musci',3,3,'专业课',2,24)
insertintoClassvalues('Tree',4,4,'专业课',2,24)
insertintoEmployeevalues(1,'Weiyuanyuan','1989-2-1',,'讲师','女')
insertintoEmployeevalues(2,'Yuzhenghong','1988-3-1',,'副教授','男')
insertintoEmployeevalues(3,'Weizhiqing','1989-2-1',,'教授','男')
insertintoEmployeevalues(4,'Huanghui','1989-2-1',,'讲师','女')
insertintoTeachervalues(1,1,0)
insertintoTeachervalues(2,2,0)
insertintoTeachervalues(3,3,0)
insertintoTeachervalues(4,4,0)
insertintoStudentClassvalues(1,'A11111111',90)
insertintoStudentClassvalues(2,'A11111112',80)
insertintoStudentClassvalues(3,'A11111113',75)
insertintoStudentClassvalues(4,'A11111114',85)
insertintoEducateClassvalues(001,1,1,3311,'2011-2012-01','Good')
insertintoEducateClassvalues(002,2,2,3202,'2011-2012-01','VertyGood')
insertintoEducateClassvalues(003,3,3,2211,'2011-2012-01','Good')
insertintoEducateClassvalues(004,4,4,1111,'2011-2012-01','Good')
查询学生信息:
createprocedurepro_AskGrade
@SNovarchar(20),@CardIdvarchar(20)
as
begintry
if(exists(select*fromStudentwhereSNo=@SNoandSCardId=@CardId))
begin
Declare@ClassCountint,
@UnpassClassint,@AllScureint,@MaxGradefloat,@MinGradefloat
select@ClassCount=count(ClassNo)fromStudentClasswhereStudentNo=@SNo
select@UnpassClass=count(Grade)fromStudentClasswhereStudentNo=@SNoandGrade<60
select@AllScure=sum(ClassScure)fromClasswhereClassNoin(selectClassNofromStudentClasswhereStudentNo=@SNoandGrade>60)
select@MaxGrade=max(Grade)fromStudentClass
whereStudentClass.StudentNo=@SNo
select@MinGrade=min(Grade)fromStudentClass
whereStudentClass.StudentNo=@SNo
selectClassNameas'课程名',Gradeas'最高成绩'fromStudentClassjoin
ClassonStudentClass.ClassNo=Class.ClassNo
whereStudentClass.StudentNo=@SNoandGrade=@MaxGrade
selectClassNameas'课程名',Gradeas'最低成绩'fromStudentClassjoin
ClassonStudentClass.ClassNo=Class.ClassNo
whereStudentClass.StudentNo=@SNoandGrade=@MinGrade
print'学生'+cast(@SNoaschar(12))
print'选修课程数'+cast(@ClassCountaschar
(2))+'不及格的课程数'+cast(@UnpassClassaschar
(2))
+'总学分'+cast(@AllScureaschar
(2))
select*fromStudentClasswhereStudentNo=@SNo
end
else
print'用户名/密码错误'
endtry
begincatch
print'error'
endcatch
execpro_AskGrade'A11111111',
发放教职工工资:
createprocedurepro_PayTeacher
as
begintry
declare@ClassTimeint,@TeacherTitlechar(10),@YearRewardint,@PayBaseint,@PayOtherint,@TeacherNoint
declarecur_PayTeachercursor
forselectEmNo,EmPositionfromEmployee
opencur_PayTeacher
fetchcur_PayTeacherinto@TeacherNo,@TeacherTitle
while(@@fetch_status=0)
begin
select@ClassTime=sum(ClassTime)fromClasswhereClassNoin(selectClassNofromEducateClasswhereTeacherNo=@TeacherNo)
if(@ClassTime=null)
set@ClassTime=0
if((selectIspartimefromTeacherwhereTeacherNo=@TeacherNo)=0)
set@PayOther=1500
else
set@PayOther=0
if(@TeacherTitle='初级')
begin
set@PayBase=1000
set@YearReward=1000
end
else
if(@TeacherTitle='中级')
begin
set@PayBase=1200
set@YearReward=2000
end
else
begin
set@PayBase=1500
set@YearReward=3000
end
if(month(getdate())!
=12)
insertintoIncomevalues(@TeacherNo,@PayBase,getdate(),@PayOther,0,@ClassTime*80/6)
else
insertintoIncomevalues(@TeacherNo,@PayBase,getdate(),@PayOther,@YearReward,@ClassTime*80/6)
select*fromIncomewhereEmployeeNo=@TeacherNo
fetchcur_PayTeacherinto@TeacherNo,@TeacherTitle
end
closecur_PayTeacher
deallocatecur_PayTeacher
endtry
begincatch
print'error'
endcatch
execpro_PayTeacher
了解课程情况:
createprocedurepro_Teach
@TeacherNoint,@TeacherCardIdvarchar(18)
as
begintry
if(exists(select*fromEmployeewhereEmNo=@TeacherNoandEmCardId=@TeacherCardId))
if(exists(select*fromTeacherwhereTeacherNo=@TeacherNo))
begin
declare@CountUnpassint,@ClassNoint,@ClassNamevarchar(20),@EducateClassNoint
declarecur_Countcursor
forselectClass.ClassNo,Class.ClassName,EducateClassNofromEducateClassjoin
ClassonClass.ClassNo=EducateClass.ClassNo
whereTeacherNo=@TeacherNo
orderbyClass.ClassName,EducateClassNo
opencur_Count
fetchcur_Countinto@ClassNo,@ClassName,@EducateClassNo
while(@@Fetch_status=0)
begin
select@CountUnpass=count(Grade)fromStudentClasswhereClassNo=@ClassNoandGrade<60
print'课程名'+@ClassName+'课程班号'+cast(@EducateClassNoaschar
(2))+'不及格学生人数'+cast(@CountUnpassaschar
(2))
selectStudentNo,SName,GradefromStudentClassjoinStudentonSNo=StudentNowhereClassNo=@ClassNoandGrade<60
selecttop3StudentNo,SName,GradefromStudentClassjoinStudentonSNo=StudentNowhereClassNo=@ClassNo
fetchcur_Countinto@ClassNo,@ClassName,@EducateClassNo
end
end
elseprint'非合法教师'
else
print'用户名/密码错误'
endtry
begincatch
endcatch
execpro_Teach1,
新生报到:
Createprocedurepro_NewStudent
@StudentNovarchar(20),@StudentNamevarchar(20),
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 课程设计 报告