成绩管理数据库.docx

成绩管理数据库.docx

《成绩管理数据库.docx》由会员分享，可在线阅读，更多相关《成绩管理数据库.docx（39页珍藏版）》请在冰豆网上搜索。

成绩管理数据库

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）

否

学生所在班级

useGradeManager

go

create

table

Student

（学号char（7）

primary

key,

姓名nchar（4）

not

null,

性别nchar

（1）

not

null,

年龄smallint

班级char（5）notnull

）

Go

Course课程表

属性名

Cno

Cname

Credit

数据类型

Char

（1）

VarChar（20）

Smallint

可否为空

否

否

可

含义

课程号（唯一）

课程名称

学分

use

GradeManager

go

createtableCourse

（课程号char

（1）primary

课程名称varchar（20）not

学分smallint

key,

null,

）

go

Class班级表

属性名

数据类型

可否为空

含义

Clno

Char（5）

否

班级号（唯一）

Speciality

VarChar（20）

否

班级所在专业

Inyear

Char（4）

否

入校年份

Number

Integer

可

班级人数

Monitor

Char（7）

可

班长学号

useGradeManager

go

create

table

Class

（班级号char

（5）

primary

key,

专业varchar

（20）

not

null,

入学年份

char

（4）

notnull,

班级人数

integer

班长学号

char

（7）

）

Go

Grade成绩表

属性名

数据类型

可否为空

含义

Sno

Char（7）

否

学号

Cno

Char

（1）

否

课程号

Gmark

Decimal（4,1）

可

成绩

use

GradeManager

go

createtable

（学号char（7）

课程号char

（1）

成绩decimal（

Grade

not

not

4,1）

null,

null,

）

go

图2-4成绩管理数据库中的表结构

Student学生表

Sno

Sname

Ssex

Sage

Clno

2000101

李勇

男

20

00311

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

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

2000104

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）

否

学生所在班级

外部码，级联

useGradeManager

go

altertableStudent

addconstraintPK_Student

primarykey（学号）

go

--添加主键约束

altertableStudent

addconstraintDF_sexdefault（'男'）for性别

go

--添加默认值，默认性别为男

altertableStudent

addconstraintck_xingbiecheck（性别='男'or性别='女'）

go

--添加check约束，指定性别输入为男或女

alter

table

student

add

constraint

ck_nianling

check（年龄between14and65）;

go

--添加check约束，指定年龄输入范围是14~65

alter

table

Student

add

constraint

FK_class_student

foreignkey

（班级）

References

Class（班级号）

--添加外键约束

ondeletecascade

--指定为级联删除

go

Course课程表

属性名数据类型可否为空含义完整性约束

CnoChar

（1）否课程号主码

CnameVarChar（20）否课程名称

CreditSmallint可学分1、2、3、4、5、6之一

useGradeManager

go

altertableCourse

addconstraintPK_Courseprimarykey（课程号）

go

--为课程号添加主键

altertableCourse

addconstraintck_Coursecheck（学分='1'or学分='2'or学分='3'or学分='4'

or学分='5'or学分='6'）

go

--添加check约束，指定学分只能为1,2,3,4,5,6

Class班级表

属性名数据类型可否为空含义完整性约束

Clno

Char（5）

否

班级号

主码

Speciality

VarChar（20）

否

班级所在专业

Inyear

Char（4）

否

入校年份

Number

Integer

可

班级人数

大于1，小于100

Monitor

Char（7）

可

班长学号

外部码

use

GradeManager

go

alter

table

class

add

constraint

CK_banjirenshu

check

（班级人数>1and班级人数<100）

go

--添加check约束，指定班级人数为

1~100

alter

table

Class

add

constraint

FK_Student_Class

foreign

key

（班长学号）

References

Student

（学号）

go

--添加外键，在

class

表的【班长学号】字段列上添加一个名为

FK_Student_Class

的外

键约束，该外键参照

Student表的主键字段列【学号】

Grade成绩表

属性名

数据类型

可否为空

含义

完整性约束

Sno

Char（7）

否

学号

主属性，外部码，级联

Cno

Char

（1）

否

课程号

主属性，外部码，级联

Gmark

Decimal（4,1）

可

成绩

大于0，小于100`

use

GradeManager

go

alter

table

grade

add

constraint

FK_Student_Grade

foreign

key

（学号）

References

Student

（学号）

--添加外键，在grade

表的【学号】字段列上添加一个名为FK_Student_grade

的外键约束，

该外键参照

Student

表的主键字段列【学号】

on

delete

cascade

--指定为级联删除

go

alter

table

grade

addconstraint

FK_Course_Grade

foreign

key

（课程号）

References

Course

（课程号）

--添加外键，在

grade

表的【课程号】字段列上添加一个名为FK_Course_grade

的外键约

束，该外键参照Course表的主键字段列【课程号】

ondeletecascade

--指定为级联删除

go

altertableGrade

addconstraintCK_chengjicheck（成绩>=0and成绩<=100）

go

--添加check约束，指定成绩范围为0~100

图3-6加了约束说明后的表结构

1.针对以上四个表，用SQL语言完成以下各项操作：

①给学生表增加一属性Nation（民族），数据类型为Varchar（20）；useGradeManager

go

altertablestudent

add民族varchar（20）null

go

--使用add可以添加新列

②删除学生表中新增的属性Nation；useGradeManager

go

altertablestudent

dropcolumn民族go

--使用drop删除列

③向成绩表中插入记录（”2001110”，”3”，80）；

useGradeManager

go

insert

intoStudent

values

（'2001110'

'李四','男','20',

'01311'）

go

--添加该记录是为了能使下一条记录顺利添加，因为

grade

表的【学号】字段列上含

外键约束，该外键参照

Student

表的主键字段列【学号】

为了保证数据的完整性，

Student

表中因含有'2001110'

记录的一行数据。

insert

Grade

values

（'2001110'

'3','80'

）

go

--使用insert[into]

添加一条记录

④修改学号为”2001110”的学生的成绩为70

分；

useGradeManager

go

updateGrade

set成绩=70

where学号=2001110

go

--使用update修改一条记录

⑤删除学号为”2001110”的学生的成绩记录；

useGradeManager

go

deletefromGrade

where学号=2001110

go

--使用delete语句删除一条记录

2.针对以上四个表，用SQL语言完成以下各项查询：

1.找出所有被学生选修了的课程号；

useGradeManager

go

selectdistinct课程号fromGrade

go

--从grade表中查询出所用被学生选修的课程号（grade表为成绩表，被选修的课程

必须有成绩，所以从该表中查询），distinct关键字用来删除重复行。

2.找出01311班女学生的个人信息；useGradeManager

go

select*

fromStudent

where班级=01311and性别='女'

go

3.找出01311班、01312班的学生姓名、性别、出生日期、当前时间；useGradeManager

go

select姓名,性别,出生日期=YEAR（GETDATE（））-年龄,YEAR（GETDATE（））as

前时间

fromStudent

where班级=01311or班级=01312

go

--YEAR（GETDATE（））函数用来获得当前的年份。

3.找出所有姓李的学生的个人信息；

useGradeManager

go

select*

fromStudent

where姓名like'李%'

go

--使用like进行模糊查询;

语法为：

匹配表达式[not]like‘匹配模式串’；

用于匹配模式串的通配符有：

%百分号代表零个或多个任意字符串；

-下划线代表一个任意字符，双字节字符应对应两个下划线，如汉字；

[]方括号代表方括号中列出的或属于指定范围内的任何单一字符；

[^]括在方括号中的^代表不在方括号中列出或不属于指定范围内的任何单一字符；

4.找出学生李勇所在班级的学生人数；

useGradeManager

go

select班级人数

fromClassinnerjoinStudentonStudent.学号=class.班长学号

where姓名='李勇'

go

--使用innerjoin关键字实现内连接，内连接为系统默认的，所以inner可以省

略；

格式：

from<表名1>[inner]join<

<连接条件>可拆分为：

表名1.字段列名

表名2>on<连接条件>

1<运算符>表名2.字段列名2

运算符有：

=,!

=,>,>=,!

>,<,<=,!

<;

根据<运算符>分，又可以分为：

等值连接和非等值连接

select班级人数

from

where

Class,Student

Student.学号

=Class

.

班长学号

and

姓名='

李勇'

go

--不使用join关键字实现内连接

格式：

from<表名1>,<表名2>where<连接条件>

6.找出课程名为操作系统的平均成绩、最高分、最低分；useGradeManager

go

select

AVG（

成绩）

as平均成绩,

MAX（成绩）

as

最高分,MIN（成绩）as

最低分

from

Grade

inner

joinCourse

onCourse

.课程号=Grade.课程号

where

课程名称='操作系统'

go

--使用inner

join

的内连接

select

avg（

成绩）

as平均成绩,max（成绩）

as

最高分,min（成绩）as

最低分

from

grade,course

where

grade

.课程号=course.课程号and

course

.课程名称='操作系统'

go

--不使用inner

join

的内连接

7.找出选修了课程的学生人数；useGradeManager

go

selectcount（distinct学号）

fromGrade

go

--distinct用来删除重复的记录

8.找出选修了课程操作系统的学生人数；useGradeManager

go

select

from

COUNT（distinct

Gradeinnerjoin

学号）Course

as

选修操作系统的学生人数

onCourse.课程号=Grade

.课程号

where

课程名称

='

操作系统

'

go

--使用内连接完成

useGradeManager

go

selectCOUNT（学号）as选修操作系统的学生人数

fromStudent

where学号in

（select学号

fromGrade

where课程号in

（select课程号

fromCourse

where课程名称='操作系统'）

）

Go

--使用嵌套查询完成，最后一个select语句用来查询课程名称为操作系统的课程号，

第二个select语句用来查询该课程号对应的学号，而第一个select语句只是对查询出

的结果使用聚合函数统计出了数目。

9.找出2000级计算机软件班的成绩为空的学生姓名。

useGradeManager

go

select姓名

fromstudentinnerjoingradeonstudent.学号=grade.学号

innerjoinclassonclass.班级号=student.班级

whereclass.入学年份='2000'andclass.专业='计算机软件'andGrade.

成绩isnull

go

--使用多个join实现多表连接查询。

4.针对以上四个表，用SELECT的嵌套查询完成以下各项查询：

①找出与李勇在同一个班级的学生信息；useGradeManager

go

select*fromstudentwhere班级in

（select班级fromStudentwhere姓名='李勇'）

go

②找出所有与学生李勇有相同选修课程的学生信息；

useGradeManager

go

select

*fromstudent

where

学号in

（select

学号from

Grade

where

课程号in

（select

课程号from

Grade

where

学号in

（select

学号from

Student

where

姓名='李勇'）））

go

③找出年龄介于学生李勇和

25岁之间的学生信息；

useGradeManager

go

select

*from

Student

where

年龄>（select

年龄

from

Student

where

姓名='

李勇'）and

年龄<25

go

④找出选修了课程操作系统的学生学号和姓名；

useGradeManager

go

select

（select

（select

学号,姓名fromstudentwhere学号

学号fromGradewhere课程号in

课程号fromCoursewhere课程名称

in

='

操作系统

'））

go

⑤找出所有没有选修1号课程的学生姓名；

useGradeManager

go

select

select

姓名

学号

from

from

studentgrade

wher