成绩管理数据库Word文档下载推荐.docx
《成绩管理数据库Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《成绩管理数据库Word文档下载推荐.docx（19页珍藏版）》请在冰豆网上搜索。
Char
（1）
课程号（唯一）
Cname
课程名称
Credit
学分
createtableCourse
（课程号char
（1）primarykey,
课程名称varchar（20）notnull,
学分smallint
Class班级表
班级号（唯一）
Speciality
班级所在专业
Inyear
Char（4）
入校年份
Number
Integer
班级人数
Monitor
班长学号
useGradeManager
createtableClass
（班级号char（5）primarykey,
专业varchar（20）notnull,
入学年份char（4）notnull,
班级人数integer,
班长学号char（7）
Grade成绩表
学号
课程号
Gmark
Decimal（4,1）
成绩
createtableGrade
（学号char（7）notnull,
课程号char
（1）notnull,
成绩decimal（4,1）
图2-4成绩管理数据库中的表结构
2000101
李勇
男
20
00311
2000102
刘诗晨
女
19
2000103
王一鸣
00312
2000104
张婷婷
21
2001101
李勇敏
01311
2001102
贾向东
22
2001103
陈宝玉
2001104
张逸凡
1
数据库
4
2
离散数学
3
管理信息系统
操作系统
5
数据结构
6
数据处理
7
C语言
计算机软件
2000
89
计算机应用
68
2001
98
92
88
86
78
55
65
66
54
83
70
80
90
76
56
图2-5成绩管理数据库中的数据
加入完整性约束说明，如图3-6所示，请用SQL语句完成图中约束的说明。
完整性约束
主码
男或女，默认为男
大于14，小于65
外部码，级联
altertableStudent
addconstraintPK_Student
primarykey（学号）
--添加主键约束
addconstraintDF_sexdefault（'
男'
）for性别
--添加默认值，默认性别为男
addconstraintck_xingbiecheck（性别='
or性别='
女'
--添加check约束，指定性别输入为男或女
altertablestudent
addconstraintck_nianlingcheck（年龄between14and65）;
--添加check约束，指定年龄输入范围是14~65
addconstraintFK_class_student
foreignkey（班级）
ReferencesClass（班级号）
--添加外键约束
ondeletecascade
--指定为级联删除
1、2、3、4、5、6之一
altertableCourse
addconstraintPK_Courseprimarykey（课程号）
--为课程号添加主键
addconstraintck_Coursecheck（学分='
1'
or学分='
2'
or学分='
3'
4'
5'
6'
--添加check约束，指定学分只能为1,2,3,4,5,6
班级号
大于1，小于100
外部码
altertableclass
addconstraintCK_banjirenshucheck（班级人数>
1and班级人数<
100）
--添加check约束，指定班级人数为1~100
altertableClass
addconstraintFK_Student_Class
foreignkey（班长学号）
ReferencesStudent（学号）
--添加外键，在class表的【班长学号】字段列上添加一个名为FK_Student_Class的外键约束，该外键参照Student表的主键字段列【学号】
主属性，外部码，级联
大于0，小于100`
altertablegrade
addconstraintFK_Student_Grade
foreignkey（学号）
ReferencesStudent（学号）
--添加外键，在grade表的【学号】字段列上添加一个名为FK_Student_grade的外键约束，该外键参照Student表的主键字段列【学号】
addconstraintFK_Course_Grade
foreignkey（课程号）
ReferencesCourse（课程号）
--添加外键，在grade表的【课程号】字段列上添加一个名为FK_Course_grade的外键约束，该外键参照Course表的主键字段列【课程号】
altertableGrade
addconstraintCK_chengjicheck（成绩>
=0and成绩<
=100）
--添加check约束，指定成绩范围为0~100
图3-6加了约束说明后的表结构
2.针对以上四个表，用SQL语言完成以下各项操作：
1给学生表增加一属性Nation（民族），数据类型为Varchar（20）；
useGradeManager
add民族varchar（20）null
--使用add可以添加新列
2删除学生表中新增的属性Nation；
dropcolumn民族
--使用drop删除列
3向成绩表中插入记录（”2001110”，”3”，80）；
go
insertintoStudent
values（'
2001110'
'
李四'
20'
01311'
--添加该记录是为了能使下一条记录顺利添加，因为grade表的【学号】字段列上含外键约束，该外键参照Student表的主键字段列【学号】,为了保证数据的完整性，Student表中因含有'
记录的一行数据。
insertGrade
80'
--使用insert[into]添加一条记录
4修改学号为”2001110”的学生的成绩为70分；
updateGrade
set成绩=70
where学号=2001110
--使用update修改一条记录
5删除学号为”2001110”的学生的成绩记录；
deletefromGrade
where学号=2001110
--使用delete语句删除一条记录
3.针对以上四个表，用SQL语言完成以下各项查询：
1.找出所有被学生选修了的课程号；
selectdistinct课程号fromGrade
--从grade表中查询出所用被学生选修的课程号（grade表为成绩表，被选修的课程必须有成绩，所以从该表中查询），distinct关键字用来删除重复行。
2.找出01311班女学生的个人信息；
useGradeManager
select*
fromStudent
where班级=01311and性别='
3.找出01311班、01312班的学生姓名、性别、出生日期、当前时间；
select姓名,性别,出生日期=YEAR（GETDATE（））-年龄,YEAR（GETDATE（））as前时间
fromStudent
where班级=01311or班级=01312
--YEAR（GETDATE（））函数用来获得当前的年份。
4.找出所有姓李的学生的个人信息；
where姓名like'
李%'
--使用like进行模糊查询;
语法为：
匹配表达式[not]like‘匹配模式串’；
用于匹配模式串的通配符有：
%百分号代表零个或多个任意字符串；
-下划线代表一个任意字符，双字节字符应对应两个下划线，如汉字；
[]方括号代表方括号中列出的或属于指定范围内的任何单一字符；
[^]括在方括号中的^代表不在方括号中列出或不属于指定范围内的任何单一字符；
5.找出学生李勇所在班级的学生人数；
useGradeManager
select班级人数
fromClassinnerjoinStudentonStudent.学号=class.班长学号
where姓名='
李勇'
go
--使用innerjoin关键字实现内连接，内连接为系统默认的，所以inner可以省略；
格式：
from<
表名1>
[inner]join<
表名2>
on<
连接条件>
<
可拆分为：
表名1.字段列名1<
运算符>
表名2.字段列名2
运算符有：
=,!
=,>
>
>
<
<
;
根据<
分，又可以分为：
等值连接和非等值连接
select班级人数
fromClass,Student
whereStudent.学号=Class.班长学号and姓名='
--不使用join关键字实现内连接
<
where<
6.找出课程名为操作系统的平均成绩、最高分、最低分；
selectAVG（成绩）as平均成绩,MAX（成绩）as最高分,MIN（成绩）as最低分
fromGradeinnerjoinCourseonCourse.课程号=Grade.课程号
where课程名称='
操作系统'
--使用innerjoin的内连接
selectavg（成绩）as平均成绩,max（成绩）as最高分,min（成绩）as最低分
fromgrade,course
wheregrade.课程号=course.课程号andcourse.课程名称='
--不使用innerjoin的内连接
7.找出选修了课程的学生人数；
selectcount（distinct学号）
fromGrade
--distinct用来删除重复的记录
8.找出选修了课程操作系统的学生人数；
selectCOUNT（distinct学号）as选修操作系统的学生人数
fromGradeinnerjoinCourseonCourse.课程号=Grade.课程号
where课程名称='
--使用内连接完成
selectCOUNT（学号）as选修操作系统的学生人数
where学号in
（select学号
fromGrade
where课程号in
（select课程号
fromCourse
--使用嵌套查询完成，最后一个select语句用来查询课程名称为操作系统的课程号，第二个select语句用来查询该课程号对应的学号，而第一个select语句只是对查询出的结果使用聚合函数统计出了数目。
9.找出2000级计算机软件班的成绩为空的学生姓名。
select姓名
fromstudentinnerjoingradeonstudent.学号=grade.学号
innerjoinclassonclass.班级号=student.班级
whereclass.入学年份='
2000'
andclass.专业='
计算机软件'
andGrade.成绩isnull
--使用多个join实现多表连接查询。
4.针对以上四个表，用SELECT的嵌套查询完成以下各项查询：
1找出与李勇在同一个班级的学生信息；
select*fromstudentwhere班级in
（select班级fromStudentwhere姓名='
2找出所有与学生李勇有相同选修课程的学生信息；
select*fromstudentwhere学号in
（select学号fromGradewhere课程号in
（select课程号fromGradewhere学号in
（select学号fromStudentwhere姓名='
）））
3找出年龄介于学生李勇和25岁之间的学生信息；
select*fromStudent
where年龄>
（select年龄fromStudentwhere姓名='
）and年龄<
25
go
4找出选修了课程操作系统的学生学号和姓名；
select学号,姓名fromstudentwhere学号in
（select课程号fromCoursewhere课程名称='
））
5找出所有没有选修1号课程的学生姓名；
select姓名fromstudentwhere学号notin（
select学号fromgradewhere课程号='
6找出选修了全部课程的学生姓名。
select姓名fromstudentwhere学号in（
select学号fromgradegroupby学号
havingcount（distinct学号）in
（selectcount（课程号）fromcourse））
--groupby语句根据一列或多列的值对查询数据集进行分组；
Having子句用来对生成的各个组进行条件筛选，只对满足筛选条件的那些组进行统计与汇总；
having子句如果与groupby子句同时出现，它只能出现在groupby子句之后；
如果脱离了groupby子句，having子句的行为与where子句一样，但用法有所限制，要求having子句中出现的所用列必须同时出现在select语句的<
目标项列表>
中。
Where子句与having子句的区别如下：
1,where子句作用的对象为数据表或视图，从中选择满足条件的记录，having子句作用于查询数据集，从中选出满足条件的组。
2.having子句中的<
条件表达式>
可以使用聚合函数，而where子句中的<
不可以。
5.针对以上四个表，用SQL语言完成以下各项查询：
1查询选修了3号课程的学生学号及其成绩，并按成绩的降序排列；
select学号,成绩
where课程号='
orderby成绩desc
2查询全体学生信息，要求查询结果按班级号升序排列，同一班级学生按年龄降序排列；
select*
orderby班级desc,年龄asc
3求每个课程号及相应的选课人数；
select课程号,COUNT（课程号）as选课人数
groupby课程号
4查询选修了3门以上课程的学生学号。
select学号fromGradewhere课程号in
（select课程号fromGrade
havingcount（课程号）>
=3）
6.针对以上四个表，用SQL语言完成以下各项操作：
1对每个班，求学生的平均年龄，并把结果存入数据库；
selectAVG（年龄）as平均年龄
intostudnet_avg_age
groupby班级
--into子句用来将查询结果数据集保存到指定名称的数据表中；
2将01311班的全体学生的成绩置零；
set成绩=0
where学号in
（select学号fromS
升级会员 