成绩管理数据库

1. 设有一数据库GradeManager（成绩管理），包括四个表：学生表（Student）、课程表（Course）、班级表（Class）以及成绩表（Grade）。四个表的结构如图2-4所示，数据如图2-5所示。用SQL语句创建四个表。Student学生表属性名数据类型可否为空含义SnoChar(7)否学号（唯一）SnameVarChar(20)否学生姓名SsexChar(2)否性别SageSmallint可年龄ClnoChar(5)否学生所在班级use GradeManager gocreate table Student(学号 char(7) primary key,姓名 nchar(4) not null,性别 nchar(1) not null,年龄 smallint ,班级 char(5) not null)GoCourse课程表属性名数据类型可否为空含义CnoChar(1)否课程号（唯一）CnameVarChar(20)否课程名称CreditSmallint可学分use GradeManager gocreate table Course(课程号 char(1) primary key,课程名称 varchar(20) not null,学分 smallint)goClass班级表属性名数据类型可否为空含义ClnoChar(5)否班级号（唯一）SpecialityVarChar(20)否班级所在专业InyearChar(4)否入校年份NumberInteger可班级人数MonitorChar(7)可班长学号use GradeManager gocreate table Class(班级号 char(5) primary key,专业 varchar(20) not null,入学年份 char(4) not null,班级人数 integer ,班长学号 char(7)GoGrade成绩表属性名数据类型可否为空含义SnoChar(7)否学号CnoChar(1)否课程号GmarkDecimal(4,1)可成绩use GradeManager gocreate table Grade(学号 char(7) not null,课程号 char(1) not null,成绩 decimal(4,1)go图2-4 成绩管理数据库中的表结构Student学生表SnoSnameSsexSageClno李勇男2000311刘诗晨女1900311王一鸣男2000312张婷婷女2100312李勇敏女1901311贾向东男2201311陈宝玉男2001311张逸凡男2101311Course课程表CnoCnameCredit1数据库42离散数学33管理信息系统24操作系统45数据结构46数据处理27C语言4Class班级表ClnoSpecialityInyearNumberMonitor00311计算机软件20008900312计算机应用20006801311计算机软件200198Grade成绩表SnoCnoGmark192388586178655365678566154683270465280490683476656 图2-5 成绩管理数据库中的数据加入完整性约束说明，如图3-6所示，请用SQL语句完成图中约束的说明。Student学生表属性名数据类型可否为空含义完整性约束SnoChar(7)否学号主码SnameVarChar(20)否学生姓名SsexChar(2)否性别男或女，默认为男SageSmallint可年龄大于14，小于65ClnoChar(5)否学生所在班级外部码，级联use GradeManager goalter table Studentadd constraint PK_Studentprimary key (学号)go-添加主键约束alter table Studentadd constraint DF_sex default (男) for 性别go-添加默认值，默认性别为男alter table Studentadd constraint ck_xingbie check (性别=男 or 性别=女)go-添加check约束，指定性别输入为男或女alter table studentadd constraint ck_nianling check (年龄 between 14 and 65);go-添加check约束，指定年龄输入范围是1465alter table Studentadd constraint FK_class_student foreign key (班级)References Class (班级号)-添加外键约束on delete cascade-指定为级联删除goCourse课程表 属性名数据类型可否为空含义完整性约束CnoChar(1)否课程号主码CnameVarChar(20)否课程名称CreditSmallint可学分1、2、3、4、5、6之一use GradeManager goalter table Courseadd constraint PK_Course primary key (课程号)go-为课程号添加主键alter table Courseadd constraint ck_Course check (学分=1or 学分=2 or 学分=3 or 学分=4 or 学分=5 or 学分=6)go-添加check约束，指定学分只能为1,2,3,4,5,6Class班级表属性名数据类型可否为空含义完整性约束ClnoChar(5)否班级号主码SpecialityVarChar(20)否班级所在专业InyearChar(4)否入校年份NumberInteger可班级人数大于1，小于100MonitorChar(7)可班长学号外部码use GradeManager goalter table classadd constraint CK_banjirenshu check (班级人数1 and 班级人数=0 and 成绩=100)go-添加check约束，指定成绩范围为0100 图3-6 加了约束说明后的表结构2. 针对以上四个表，用SQL语言完成以下各项操作： 给学生表增加一属性Nation（民族），数据类型为Varchar（20）；use GradeManagergoalter table studentadd 民族 varchar(20) nullgo-使用add可以添加新列 删除学生表中新增的属性Nation；use GradeManagergoalter table studentdrop column 民族 go-使用drop删除列 向成绩表中插入记录（”，”3”，80）；use GradeManagergo insert into Student values (,李四,男,20,01311)go-添加该记录是为了能使下一条记录顺利添加，因为grade表的【学号】字段列上含外键约束，该外键参照 Student表的主键字段列【学号】,为了保证数据的完整性，Student表中因含有记录的一行数据。insert Gradevalues (,3,80)go-使用insert into添加一条记录 修改学号为”的学生的成绩为70分；use GradeManagergo update Grade set 成绩=70where 学号 =go-使用update修改一条记录 删除学号为”的学生的成绩记录；use GradeManager godelete from Grade where 学号 = go-使用delete语句删除一条记录3. 针对以上四个表，用SQL语言完成以下各项查询：1.找出所有被学生选修了的课程号；use GradeManager goselect distinct 课程号 from Grade go-从grade表中查询出所用被学生选修的课程号（grade表为成绩表，被选修的课程必须有成绩，所以从该表中查询），distinct关键字用来删除重复行。 2.找出01311班女学生的个人信息；use GradeManagergoselect *from Student where 班级=01311 and 性别=女go3.找出01311班、01312班的学生姓名、性别、出生日期、当前时间；use GradeManager goselect 姓名,性别,出生日期=YEAR(GETDATE()-年龄,YEAR(GETDATE() as 前时间from Studentwhere 班级=01311 or 班级=01312go- YEAR(GETDATE()函数用来获得当前的年份。4.找出所有姓李的学生的个人信息；use GradeManager goselect *from Student where 姓名 like 李%go-使用like进行模糊查询;语法为：匹配表达式 not like 匹配模式串 ；用于匹配模式串的通配符有：%百分号代表零个或多个任意字符串；- 下划线代表一个任意字符，双字节字符应对应两个下划线，如汉字；方括号代表方括号中列出的或属于指定范围内的任何单一字符；括在方括号中的代表不在方括号中列出或不属于指定范围内的任何单一字符；5.找出学生李勇所在班级的学生人数；use GradeManager goselect 班级人数 from Class inner join Student on Student.学号=class.班长学号where 姓名=李勇go-使用 inner join关键字实现内连接，内连接为系统默认的，所以inner 可以省略；格式：from inner join on 可拆分为：表名1.字段列名1 表名2.字段列名2运算符有：= , != , , = , ! , , = , ! ;根据分，又可以分为：等值连接和非等值连接select 班级人数from Class,Studentwhere Student.学号 = Class.班长学号 and 姓名=李勇go-不使用 join 关键字实现内连接格式：from , where 6.找出课程名为操作系统的平均成绩、最高分、最低分；use GradeManagergoselect AVG(成绩) as 平均成绩,MAX(成绩) as 最高分,MIN(成绩) as 最低分from Grade inner join Course on Course.课程号=Grade.课程号 where 课程名称=操作系统go-使用inner join 的内连接select avg(成绩) as 平均成绩,max(成绩) as 最高分,min(成绩) as 最低分 from grade,course where grade.课程号=course.课程号 and course.课程名称=操作系统go-不使用inner join 的内连接 7.找出选修了课程的学生人数；use GradeManager goselect count(distinct 学号) from Gradego-distinct用来删除重复的记录8.找出选修了课程操作系统的学生人数；use GradeManager goselect COUNT(distinct 学号) as 选修操作系统的学生人数from Grade inner join Course on Course.课程号 = Grade.课程号 where 课程名称 =操作系统go-使用内连接完成use GradeManager goselect COUNT(学号) as 选修操作系统的学生人数from Student where 学号 in(select 学号 from Grade where 课程号 in(select 课程号 from Course where 课程名称=操作系统)Go-使用嵌套查询完成，最后一个select语句用来查询课程名称为操作系统的课程号，第二个select语句用来查询该课程号对应的学号，而第一个select语句只是对查询出的结果使用聚合函数统计出了数目。9.找出2000级计算机软件班的成绩为空的学生姓名。use GradeManager goselect 姓名 from student inner join grade on student.学号 = grade.学号 inner join class on class.班级号 = student.班级 where class.入学年份=2000 and class.专业 =计算机软件 and Grade.成绩 is nullgo-使用多个join实现多表连接查询。4. 针对以上四个表，用SELECT的嵌套查询完成以下各项查询： 找出与李勇在同一个班级的学生信息；use GradeManager goselect * from student where 班级 in(select 班级 from Student where 姓名=李勇)go 找出所有与学生李勇有相同选修课程的学生信息；use GradeManager goselect * from student where 学号 in(select 学号 from Grade where 课程号 in(select 课程号 from Grade where 学号 in(select 学号 from Student where 姓名=李勇)go 找出年龄介于学生李勇和25岁之间的学生信息；use GradeManager goselect * from Student where 年龄(select 年龄 from Student where 姓名=李勇) and 年龄25go 找出选修了课程操作系统的学生学号和姓名；use GradeManager goselect 学号,姓名 from student where 学号 in(select 学号 from Grade where 课程号 in(select 课程号 from Course where 课程名称=操作系统)go 找出所有没有选修1号课程的学生姓名；use GradeManager goselect 姓名 from student where 学号 not in(select 学号 from grade where 课程号=1)go 找出选修了全部课程的学生姓名。use GradeManager goselect 姓名 from student where 学号 in(select 学号 from grade group by 学号having count(distinct 学号) in(select count(课程号) from course)Go-group by 语句根据一列或多列的值对查询数据集进行分组；Having 子句用来对生成的各个组进行条件筛选，只对满足筛选条件的那些组进行统计与汇总；having子句如果与group by 子句同时出现，它只能出现在group by 子句之后；如果脱离了group by 子句，having 子句的行为与where子句一样，但用法有所限制，要求having子句中出现的所用列必须同时出现在select语句的中。Where子句与having子句的区别如下：1,where子句作用的对象为数据表或视图，从中选择满足条件的记录，having子句作用于查询数据集，从中选出满足条件的组。2.having子句中的可以使用聚合函数，而where子句中的不可以。5. 针对以上四个表，用SQL语言完成以下各项查询： 查询选修了3号课程的学生学号及其成绩，并按成绩的降序排列；use GradeManager goselect 学号,成绩 from Grade where 课程号=3 order by 成绩 descgo 查询全体学生信息，要求查询结果按班级号升序排列，同一班级学生按年龄降序排列；use GradeManager goselect * from Studentorder by 班级 desc,年龄 ascgo 求每个课程号及相应的选课人数； use GradeManager goselect 课程号,COUNT(课程号) as 选课人数from Grade group by 课程号go 查询选修了3门以上课程的学生学号。use GradeMana