2022年成绩管理数据库 .pdf

《2022年成绩管理数据库 .pdf》由会员分享，可在线阅读，更多相关《2022年成绩管理数据库 .pdf（12页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1.设有一数据库GradeManager（成绩管理） ，包括四个表：学生表（Student ） 、课程表（Course） 、班级表（ Class ）以及成绩表（ Grade） 。四个表的结构如图2-4 所示，数据如图 2-5 所示。用SQL语句创建四个表。Student 学生表属性名数据类型可否为空含义Sno Char(7) 否学号（唯一）Sname VarChar(20) 否学生姓名Ssex Char(2) 否性别Sage Smallint 可年龄Clno Char(5) 否学生所在班级useGradeManagergo createtableStudent ( 学号char( 7)prim

2、arykey , 姓名nchar( 4)notnull, 性别nchar( 1)notnull, 年龄smallint, 班级char ( 5)notnull ) Go Course 课程表属性名数据类型可否为空含义Cno Char(1) 否课程号（唯一）Cname VarChar(20) 否课程名称Credit Smallint 可学分useGradeManagergo createtableCourse ( 课程号char( 1)primarykey , 课程名称varchar( 20 )notnull, 学分smallint ) goClass 班级表属性名数据类型可否为空含义Clno

3、Char(5) 否班级号（唯一）Speciality VarChar(20) 否班级所在专业Inyear Char(4) 否入校年份Number Integer 可班级人数Monitor Char(7) 可班长学号名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 12 页 - - - - - - - - - useGradeManagergo createtableClass ( 班级号char( 5)primarykey , 专业varchar( 20 )notnull,

4、 入学年份char ( 4)notnull, 班级人数integer, 班长学号char ( 7) ) Go Grade 成绩表属性名数据类型可否为空含义Sno Char(7) 否学号Cno Char(1) 否课程号Gmark Decimal(4,1) 可成绩useGradeManagergo createtableGrade ( 学号char( 7)notnull, 课程号char ( 1)notnull, 成绩decimal( 4, 1) ) go图 2-4 成绩管理数据库中的表结构Student 学生表Sno Sname Ssex Sage Clno 2000101 李勇男20 0031

5、1 2000102 刘诗晨女19 00311 2000103 王一鸣男20 00312 2000104 张婷婷女21 00312 2001101 李勇敏女19 01311 2001102 贾向东男22 01311 2001103 陈宝玉男20 01311 2001104 张逸凡男21 01311 Course 课程表Cno Cname Credit 1 数据库4 2 离散数学3 3 管理信息系统2 4 操作系统4 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 12 页

6、- - - - - - - - - 5 数据结构4 6 数据处理2 7 C语言4 Class 班级表Clno Speciality Inyear Number Monitor 00311 计算机软件2000 89 2000101 00312 计算机应用2000 68 2000103 01311 计算机软件2001 98 2001103 Grade 成绩表Sno Cno Gmark 2000101 1 92 2000101 3 88 2000101 5 86 2000102 1 78 2000102 6 55 2000103 3 65 2000103 6 78 2000103 5 66 2000

7、104 1 54 2000104 6 83 2001101 2 70 2001101 4 65 2001102 2 80 2001102 4 90 2001102 6 83 2001103 4 76 2001103 6 56 图 2-5 成绩管理数据库中的数据加入完整性约束说明，如图3-6 所示，请用SQL语句完成图中约束的说明。Student 学生表属性名数据类型可否为空含义完整性约束Sno Char(7) 否学号主码Sname VarChar(20) 否学生姓名Ssex Char(2) 否性别男或女，默认为男Sage Smallint 可年龄大于 14，小于 65 Clno Char(5)

8、 否学生所在班级外部码，级联useGradeManagergo 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 12 页 - - - - - - - - - altertableStudent addconstraintPK_Student primarykey ( 学号 ) go - 添加主键约束altertableStudent addconstraintDF_sexdefault ( 男 )for性别go - 添加默认值，默认性别为男altertableStuden

9、t addconstraintck_xingbiecheck ( 性别 = 男or性别 = 女 ) go - 添加 check约束，指定性别输入为男或女altertablestudent addconstraintck_nianlingcheck ( 年龄 between 14 and 65 ); go - 添加 check约束，指定年龄输入范围是1465 altertableStudent addconstraintFK_class_studentforeignkey ( 班级 ) ReferencesClass( 班级号 ) - 添加外键约束ondeletecascade - 指定为级联删

10、除go Course 课程表属性名数据类型可否为空含义完整性约束Cno Char(1) 否课程号主码Cname VarChar(20) 否课程名称Credit Smallint 可学分1、2、3、4、5、6 之一useGradeManagergo altertableCourse addconstraintPK_Courseprimarykey ( 课程号 ) go - 为课程号添加主键altertableCourse add constraintck_Coursecheck ( 学分 =1or学分 =2or学分 =3or学分 =4or学分 =5or学分 =6) go - 添加 check约束

11、，指定学分只能为1,2,3,4,5,6Class 班级表属性名数据类型可否为空含义完整性约束名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 12 页 - - - - - - - - - Clno Char(5) 否班级号主码Speciality VarChar(20) 否班级所在专业Inyear Char(4) 否入校年份Number Integer 可班级人数大于 1， 小于 100 Monitor Char(7) 可班长学号外部码useGradeManagergo a

12、ltertableclass addconstraintCK_banjirenshucheck ( 班级人数 1 and班级人数 =0 and成绩 = 100 ) go - 添加 check约束，指定成绩范围为0100 图 3-6 加了约束说明后的表结构2.针对以上四个表，用SQL语言完成以下各项操作：给学生表增加一属性Nation （民族），数据类型为Varchar （20） ；useGradeManager go altertablestudent add民族varchar( 20 )null go - 使用 add 可以添加新列删除学生表中新增的属性Nation ；useGradeMan

13、ager go altertablestudent dropcolumn民族go - 使用 drop删除列向成绩表中插入记录（”2001110” ， ”3” ，80） ；useGradeManager goinsertintoStudentvalues ( 2001110, 李四 , 男 , 20, 01311) go - 添加该记录是为了能使下一条记录顺利添加，因为grade表的【学号】字段列上含外键约束，该外键参照Student表的主键字段列【学号】, 为了保证数据的完整性，Student表中因含有 2001110记录的一行数据。insertGrade values ( 2001110,

14、3, 80) go - 使用 insert into添加一条记录修改学号为” 2001110”的学生的成绩为70 分；useGradeManager goupdateGradeset成绩 =70 where学号=2001110 go - 使用 update修改一条记录删除学号为” 2001110”的学生的成绩记录；useGradeManagergo 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 12 页 - - - - - - - - - deletefromGrade

15、where学号= 2001110 go - 使用 delete语句删除一条记录3.针对以上四个表，用SQL语言完成以下各项查询：1. 找出所有被学生选修了的课程号；useGradeManagergo selectdistinct课程号fromGradego - 从 grade表中查询出所用被学生选修的课程号（grade表为成绩表， 被选修的课程必须有成绩，所以从该表中查询），distinct关键字用来删除重复行。 2. 找出 01311 班女学生的个人信息；useGradeManager go select* fromStudentwhere班级 =01311 and性别 = 女 go3. 找

16、出 01311 班、 01312 班的学生姓名、性别、出生日期、当前时间；useGradeManagergo select姓名 , 性别 , 出生日期 =YEAR( GETDATE ()-年龄 , YEAR( GETDATE()as前时间fromStudent where班级 =01311 or班级 =01312 go - YEAR ( GETDATE()函数用来获得当前的年份。4. 找出所有姓李的学生的个人信息；useGradeManagergo select* fromStudentwhere姓名like 李% go - 使用 like进行模糊查询 ; 语法为：匹配表达式 not like

17、 匹配模式串 ；用于匹配模式串的通配符有：% 百分号代表零个或多个任意字符串；-下划线代表一个任意字符，双字节字符应对应两个下划线，如汉字； 方括号代表方括号中列出的或属于指定范围内的任何单一字符； 括在方括号中的 代表不在方括号中列出或不属于指定范围内的任何单一字符；5. 找出学生李勇所在班级的学生人数；useGradeManager名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 12 页 - - - - - - - - - go select班级人数fromClass

18、innerjoinStudentonStudent. 学号 =class. 班长学号where姓名 = 李勇 go - 使用inner join关键字实现内连接，内连接为系统默认的，所以inner 可以省略；格式： from inner join on 可拆分为：表名1. 字段列名1 表名 2. 字段列名2 运算符有： = , != , , = , ! , , = , ! ; 根据 分，又可以分为：等值连接和非等值连接select班级人数fromClass, Student whereStudent. 学号= Class. 班长学号and姓名 = 李勇 go - 不使用join 关键字实现内连

19、接格式： from , where 6. 找出课程名为操作系统的平均成绩、最高分、最低分；useGradeManager go selectAVG( 成绩 )as平均成绩 , MAX( 成绩 )as最高分 , MIN ( 成绩 )as最低分fromGradeinnerjoinCourseonCourse. 课程号 =Grade . 课程号where课程名称 = 操作系统 go - 使用 innerjoin的内连接selectavg ( 成绩 )as平均成绩 , max( 成绩 )as最高分 , min ( 成绩 )as最低分fromgrade, coursewheregrade. 课程号 =c

20、ourse. 课程号andcourse. 课程名称 = 操作系统 go - 不使用 innerjoin的内连接 7.找出选修了课程的学生人数；useGradeManagergo selectcount( distinct学号 )fromGrade go -distinct用来删除重复的记录8. 找出选修了课程操作系统的学生人数；useGradeManagergo selectCOUNT( distinct学号 ) as 选修操作系统的学生人数fromGradeinnerjoinCourseonCourse. 课程号=Grade . 课程号where课程名称= 操作系统 go 名师资料总结 -

21、- -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 12 页 - - - - - - - - - - 使用内连接完成useGradeManagergo selectCOUNT( 学号 )as选修操作系统的学生人数fromStudentwhere学号 in ( select学号fromGradewhere课程号in ( select课程号fromCoursewhere课程名称 = 操作系统 ) ) Go - 使用嵌套查询完成， 最后一个select语句用来查询课程名称为操作系统的课程号，第二个

22、select语句用来查询该课程号对应的学号，而第一个select语句只是对查询出的结果使用聚合函数统计出了数目。9. 找出 2000 级计算机软件班的成绩为空的学生姓名。useGradeManagergo select姓名fromstudentinnerjoingradeonstudent. 学号= grade. 学号innerjoinclassonclass. 班级号=student. 班级whereclass. 入学年份 =2000andclass. 专业= 计算机软件 andGrade .成绩 isnull go - 使用多个 join实现多表连接查询。4.针对以上四个表，用SELECT

23、 的嵌套查询完成以下各项查询：找出与李勇在同一个班级的学生信息；useGradeManagergo select*fromstudentwhere班级in ( select班级 fromStudentwhere姓名 = 李勇 ) go 找出所有与学生李勇有相同选修课程的学生信息；useGradeManagergo select*fromstudentwhere学号in ( select学号 fromGradewhere课程号in ( select课程号fromGradewhere学号 in ( select学号 fromStudentwhere姓名 = 李勇 ) go 找出年龄介于学生李勇和2

24、5 岁之间的学生信息；useGradeManager名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 12 页 - - - - - - - - - go select*fromStudentwhere年龄 ( select年龄 fromStudentwhere姓名 = 李勇 )and年龄 25 go找出选修了课程操作系统的学生学号和姓名；useGradeManagergo select学号 , 姓名fromstudentwhere学号in ( select学号 fromGr

25、adewhere课程号in ( select课程号fromCoursewhere课程名称 = 操作系统 ) go找出所有没有选修1 号课程的学生姓名；useGradeManagergo select姓名fromstudentwhere学号notin( select学号 fromgradewhere课程号 =1) go找出选修了全部课程的学生姓名。useGradeManagergo select姓名fromstudentwhere学号in( select学号fromgradegroupby学号havingcount( distinct学号 )in ( selectcount( 课程号 )from

26、course) Go -group by 语句根据一列或多列的值对查询数据集进行分组；Having 子句用来对生成的各个组进行条件筛选，只对满足筛选条件的那些组进行统计与汇总； having子句如果与group by 子句同时出现，它只能出现在group by 子句之后；如果脱离了group by 子句， having 子句的行为与where子句一样，但用法有所限制，要求 having子句中出现的所用列必须同时出现在select语句的 中。Where 子句与 having子句的区别如下：1,where子句作用的对象为数据表或视图，从中选择满足条件的记录，having子句作用于查询数据集，从中选

27、出满足条件的组。2.having子句中的 可以使用聚合函数，而where子句中的 不可以。5.针对以上四个表，用SQL语言完成以下各项查询：查询选修了3 号课程的学生学号及其成绩，并按成绩的降序排列；useGradeManagergo select学号 , 成绩fromGradewhere课程号 =3orderby成绩desc 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 12 页 - - - - - - - - - go查询全体学生信息，要求查询结果按班级号升序排列

28、，同一班级学生按年龄降序排列；useGradeManagergo select*fromStudent orderby班级desc , 年龄asc go求每个课程号及相应的选课人数；useGradeManagergo select课程号 , COUNT( 课程号 )as选课人数fromGradegroupby课程号go查询选修了3 门以上课程的学生学号。useGradeManagergo select学号fromGradewhere课程号in ( select课程号fromGradegroupby课程号havingcount( 课程号 )= 3) go 6.针对以上四个表，用SQL语言完成以下

29、各项操作：对每个班，求学生的平均年龄，并把结果存入数据库；useGradeManagergo selectAVG( 年龄 )as平均年龄intostudnet_avg_age fromStudentgroupby班级go -into子句用来将查询结果数据集保存到指定名称的数据表中；将 01311 班的全体学生的成绩置零；useGradeManagergo updateGradeset成绩 =0 where学号in ( select学号 fromStudentwhere班级 =01311 ) go删除 2001 级计算机软件的全体学生的选课记录；useGradeManager名师资料总结 - -

30、 -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 12 页 - - - - - - - - - go deletefromgradewhere学号in ( select学号 fromclass, studentwhere class. 班级号=student. 班级and class. 入学年份=2001andclass. 专业= 计算机软件 ) go - 使用了不带join的内连接完成筛选条件；学生李勇已退学，从数据库中删除有关他的记录；？7.视图操作：建立 01312 班选修了1 号课程的学生视图Stu_01312_1；建立 01312 班选修了1 号课程并且成绩不及格的学生视图Stu_01312_2 ；建立视图Stu_year ，由学生学号、姓名、出生年份组成。查询 1983 年以后出生的学生姓名。查询 01312 班选修了1 号课程并且成绩不及格的学生的学号、姓名、出生年份。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 12 页 - - - - - - - - -