图书管理系统数据库设计报告

SQL Server 2000实验报告

values

('计算机系',9311,931145,'王语嫣','女','1991-3-12') 2.3更新课程表中“C程序设计”的学分更新为4学分。

update 课程表 set 学分=4

where 课程名称='C程序设计'

2.4用FROM子句删除姓名为“张萌”学生的成绩。

delete 成绩表

from 成绩表,学生表

where 成绩表.学号=学生表.学号 and 姓名='张萌'

? 3查询

3.1在学生表中查询全体学生的学号、姓名和年龄（使用YEAR和GETDATE函数） select 学号,姓名,year(getdate())-year(出生日期)as 年龄 from 学生表

3.2在成绩表中查询有成绩的学生的学号。 select 学号 from 成绩表

where 分数 like '[1-9]%'

3.3查询所有不及格的学生学号和成绩。 select 学号,分数 from 成绩表 where 分数<60

3.4查询所有姓“金”的学生的姓名，学号和性别。 select 姓名,学号,性别 from 学生表

where 姓名 like '金%' 3.5 查询学生总数。

select count(id) as '学生总人数' from 学生表

3.6查询各个系相应的学生人数。 select 所在系,count(*) as 人数 from 学生表 group by 所在系

3.7查询每个学生的学号，姓名，选修的课程名及成绩等查询。 select 课程表.课程名称,成绩表.分数,成绩表.学号,学生表.姓名 from 课程表,学生表,成绩表

where 成绩表.课程编号=课程表.课程编号 and 成绩表.学号=学生表.学号 3.8查询学号第一位数字不是2~7的学生信息。 select 所在系,班级,学号,姓名,性别,出生日期,年龄 from 学生表

where 学号 like '[^2-7]%' 3.9查询没有班主任的班级。

4

SQL Server 2000实验报告

select 班级,班主任编号 from 班级表

where 班主任编号 is NULL

3.10查询数据库成绩最高的四条记录，若有多人成绩并列，一并显示出来。 select top 4 with ties 课程名称,分数 from 成绩表,课程表

where 课程名称='数据库'and 课程表.课程编号=成绩表.课程编号 order by 分数 desc

3.11查询数据库课程的平均成绩。 select AVG(分数) as 平均分 from 成绩表,课程表

where 成绩表.课程编号=课程表.课程编号 and 课程名称='数据库' 3.12查询选修每一门课程的学生人数。 select 课程名称,count(*) as 学生人数 from 成绩表,课程表

where 成绩表.课程编号=课程表.课程编号 group by 课程名称

3.13查询平均分高于85分的所有课程。 select 课程名称,avg(分数) as 平均分 from 成绩表,课程表

where 成绩表.课程编号=课程表.课程编号 group by 课程名称 HAVING avg(分数)>85

3.14统计学生表中男女生人数和平均年龄。

select 性别, count(*) as 人数,avg(year(getdate())-year(出生日期))as 平均年龄 from 学生表 group by 性别

3.15查询教授数据结构课程的教师姓名和所在系部。

select 课程名称,教师姓名 as 授课教师,所在系 as 授课教师所在系 from 课程表,授课表,教师表

where 授课表.授课课程编号=课程表.课程编号 and 授课表.授课教师编号=教师表.教

师编号and 课程名称='数据库'

3.16用自连接方法查询所有名字相同的学生信息。

select a.所在系,a.班级,a.学号,a.姓名,a.性别,a.出生日期, b.所在系,b.班级,b.学号,b.姓名,b.性别,b.出生日期 from 学生表 a inner join 学生表 b on a.姓名=b.姓名 where a.学号<>b.学号 order by a.姓名

3.17查询数据库成绩低于该门平均分的学生学号。

select 课程名称,学号 as 成绩低于平均分的学号,分数 from 成绩表,课程表

where 成绩表.课程编号=课程表.课程编号 and 课程表.课程名称='数据库' and (select AVG(分数) as 平均分 from 成绩表,课程表

5

SQL Server 2000实验报告

where 成绩表.课程编号=课程表.课程编号 and 课程名称='数据库')>分数 3.18用NOT EXISTS查询没有选修过任何课程的学生信息 select 学号,姓名 from 学生表

where not exists (select * from 成绩表

where 学生表.学号=成绩表.学号 ) 3.19使用CASE。。。。。。END分支处理程序实现查询学生成绩表，并按照优秀、良好、中等、及格和不及格对成绩进行分级并输出，输出信息包括学号、姓名、课程名。

select 学生表.学号,姓名,课程名称,分数,成绩评价=case when 分数 is null then '没有成绩' when 分数<60 then '不及格' when 分数>=60 and 分数<70 then '及格' when 分数>=70 and 分数<80 then '中等' when 分数>=80 and 分数<90 then '良好' when 分数>=90 and 分数<=100 then '优秀' else '分数错误！' end

from 学生表,成绩表,课程表

where 成绩表.课程编号=课程表.课程编号 and 成绩表.学号=学生表.学号

? 4添加约束

4.1对成绩表添加约束，使成绩在0~100间。 alter table 成绩表

add constraint chk_fs check(分数 like'[0-9]'or 分数 like'[0-9][0-9]' or 分数 like '100') 4.2在学生表中添加性别约束 alter table 学生表

add constraint chk_xb check(性别 in ('男','女'))

? 5创建视图

建立计算机系学生的视图。

create view 计算机系学生表 as

select 所在系, 班级,学生表.学号,姓名,性别, year(getdate())-year(出生日期)as 年龄, 课程名称 as 所学课程,分数 as 课程得分 from 学生表,成绩表,课程表

where 成绩表.课程编号=课程表.课程编号 and 成绩表.学号=学生表.学号 and 所在系='计算机系'

? 6创建存储过程

建立带参数的存储过程，根据学生学号得到该学生相应的成绩，并写出执行语句。 （1）创建存储过程up_学生成绩

create procedure up_学生成绩 (@学号 [varchar](10),

6

SQL Server 2000实验报告

@课程编号 [varchar](30) output, @分数[varchar](20)output) as

select @课程编号=课程编号, @分数=分数 from 成绩表

where 学号=@学号 go

（2）执行存储过程exec up_学生成绩

DECLARE @课程编号 [varchar](30) DECLARE @分数 [varchar](20) exec up_学生成绩 '931109',

@课程编号 output,@分数 output print '课程编号:'+@课程编号 print '分数:'+@分数

? 7创建触发器

建立触发器，要求当删除学生信息表中的某个学生的记录时，该学生在学生成绩表中的记录也随之自动被删除。

（1）建立触发器 学生表_delete

create trigger 学生表_delete on 学生表 for delete as

/* dechare @学号 varchar(10) select @学号=学号 from deleted begin */

delete 成绩表

where 成绩表.学号=(select 学号 from deleted) print ('OK！已删除')

（2）验证触发器 学生表_delete

delete 学生表

where 学号='82234'

七、 实验总结

通过本次对students_score数据库的创建及应用，学习到了更多关于SQL Server 2000教材的相关知识；学会应用数据库来解决实际生活中可能出现的问题；学会管理信息资源。

7