试验三数据查询实验报告

2007-03-31 16:32:02

试验三数据查询实验报告

学生表
学号	姓名	性别	籍贯	出生日期	班号
S200601001	乔峰	男	辽宁.沈阳	1983-6-1	S200601
S200601002	虚竹子	男	宁夏.银川	1982-1-8	S200601
S200602003	令狐冲	男	陕西.华山	1980-4-1	S200602
S200602004	任盈盈	女	河南.登封	1982-3-4	S200602
S200602005	岳灵珊	女	陕西.华山	1985-1-1	S200602
S200603006	黄蓉	女	福建.泉州	1988-4-7	S200603
S200603007	郭靖	男	内蒙古.呼和浩特	1984-3-1	S200603
S200603008	穆念慈	女	江苏.南京	1984-9-5	S200603
S200603009	杨康	男	山东.青岛	1983-7-1	S200603
S200603010	华峥	女	内蒙古.呼和浩特	1980-1-2	S200603

课程表
课程名	课号	教材	所属系	课时
测试技术与数字信号处理	200601		机电学院	60
数据库	200602		机电学院	40
机械振动理论	200603		机电学院	60
软件工程	200604		机电学院	40
现代控制理论	200605		机电学院	40
面向对象程序设计	200606		机电学院	60
微机接口技术	200607		机电学院	60
汽车制动技术	200608		机电学院	40
先进制造技术	200609		机电学院	40
机械故障诊断	200610		机电学院	40

班级表
班号	专业	所在系	人数	教室
S200601	机械电子工程	机电学院	40	一教101
S200602	机械设计	机电学院	50	二教202
S200603	机械制造及自动化	机电学院	60	三教303

授课表
班号	课号	教师号	学期	授课教室	上课评价
S200601	200601	CCTV001
S200601	200602	CCTV003
S200601	200603	CCTV002
S200602	200601	CCTV001
S200602	200604	HNTV002
S200602	200605	HNTV004
S200603	200602	CCTV003
S200603	200606	FHTV001
S200603	200607	FHTV001
S200603	200608	FHTV002

教师表
教师号	教师名	性别	出生日期	职称
CCTV001	白岩松	男		教授
CCTV002	崔永元	男		教授
CCTV003	柴静	女		副教授
CCTV004	张泉灵	女		副教授
FHTV001	窦文涛	男		教授
FHTV002	吴小莉	女		教授
HNTV001	李湘	女		副教授
HNTV002	汪涵	男		副教授
HNTV003	何炅	男		副教授
HNTV004	谢娜	女		副教授

基于教学管理数据库，对其进行的各种查询如下：

1.简单查询

（1） 查询班号为012011的学生情况。

SELECT *

FROM 学生

WHERE 班号=’ 012011’

(2) 查询成绩高于86分的学号和课名。

SELECT 学号，课号

FROM 成绩

WHERE 成绩<86

(3) 查询成绩，学号，和课号，结果按成绩的降序排列。

SELECT *

FROM成绩

ORDER BY 成绩 DESC

2.统计查询

（1）查询总成绩和平均成绩。

SELECT SUM(成绩) AS ‘总成绩’，AVG(成绩) AS’平均成绩’

FROM 成绩

（2）查询各课程的平均成绩。

SELECT 课号，AVG(成绩) AS’平均成绩’

FROM 成绩

GROUP BY 课号

（3）查询分别按学号和课号分组的平均成绩。

SELECT 学号，课号，AVG(成绩) AS ‘平均成绩’

FROM 成绩

GROUP BY 学号，课号

WITH CUBE

（4）查询按学号分组的平均成绩

SELECT 学号，课号，AVG(成绩) AS ‘平均成绩’

FROM 成绩

GROUP BY 学号，课号

WITH ROLLUP

(5)查询学号，课号和成绩，并计算总成绩。

SELECT 学号，课号,成绩

FROM 成绩

ORDER BY 课号

COMPUTE SUM(成绩)

（6）查询学号，班号和成绩，并按课号分组计算总成绩

SELECT 学号，班号，成绩

FROM 成绩

ORDER BY 课号

COMPUTE SUM(成绩) BY 课号

3.内连接查询

查询学生的学号，姓名，课名，及成绩情况。

SELECT student.学号,student.姓名,course.课名,scores.成绩

FROM 学生 student INNER JOIN 成绩 scores

ON student.学号=scores.学号 INNER JOIN 课程 COURSE

ON scores.课号=course.课号

4.左连接查询

查询所有学生的上课情况，包括学号，姓名及课号。

SELECT student.学号,student.姓名,course.课名

FROM 学生 student LEFT OUTER JOIN 成绩 scores

ON student.学号=scores.学号 INNER JOIN 课程 COURSE

ON student.学号=scores.学号

5.右连接查询

SELECT student.学号,student.姓名,course.课名

FROM 成绩 scores RIGHT OUTER JOIN 学生 student

ON student.学号=scores.学号 INNER JOIN 课程 COURSE

ON scores.学号=student.学号

6.自连接

查询在同一个班的学生的学号和姓名

SELECT student1.学号,student1.姓名

FROM 学生 student1 INNER JOIN 成绩 scores

ON student.学号=scores.学号 INNER JOIN 学生 student2

ON student1.班号=student2.班号

WHERE student1.学号> 学号

7.子查询

查询成绩低于86分的学生姓名

SELECT 姓名

FROM 学生

WHERE 学号 IN

(SELECT 学号 FROM 成绩 WHERE 成绩>86 )

8.集合查询

查询学生姓名和教师姓名

SELECT 姓名

FROM 学生

UNION

SELECT 教师名

FROM 教师

1.利用Transact-SQL创建视图：

以教学管理数据库中的学生，班级和课程为基表，创建班级成绩单视图，具体操作步骤如下：

（1） 在Enterprise Manager中的工具菜单中启动查询分析器。

（2） 在查询分析器中输入下列语句：

CREATE VIEW 学生成绩单

AS

SELECT 成绩.学号,学生.姓名，课程.课名，成绩.成绩

FROM 成绩INNER JOIN

学生 ON 成绩.学号=学生.学号 INNER JOIN

班级表 ON 学生.班号=班级.班号 INNER JOIN

课程 ON 成绩.课号=课程.课号

(3) 单击运行按钮创建视图。

2. 利用Transact-SQL创建存储过程

本例在教学管理数据库中创建一个使用OUTPUT参数的存储过程GetSpecial Teacher Rank 用于获取指定教师的职称。具体操作步骤如下：

（1） 在Enterprise Manager中的工具菜单中启动查询分析器。

（2） 在查询分析器中输入下列语句：

USE 教学管理数据库

IF EXISTS

(SELECT name

FROM systemobjects

WHERE name=’GetSpecialTeacherRank’AND type=’p’)

DROP PROCEDURE GetSpecialTeacherRank

GO

CREATE PROCEDURE GetSpecialTeacherRank

@TeacherID varchar(15),

@Rank varchar(10) OUTPUT

AS

SELECT @Rank=职称 FROM 教师

WHERE 教师号=@TeacherID

GO

（3） 单击运行按钮创建存储过程。

3. 利用Transact-SQL创建触发器

以教学管理系统为例，分别介绍Insert,Delete和Update三种触发器的具体创建方法。

（1） 插入型触发器：为学生表创建一个INSERT除法器。当在学生表插入一行数据（表明新增一名学生）时，自动更新班级表的人数字段的数值。

在查询分析器中，键入一下命令，并单击命令栏中的执行按钮：

USE 教学管理数据库

GO

CREATE TRIGGER Trigger_Student_Insert

ON 学生表

FOR INSERT

AS

Update 班级表 Set 人数=人数+@@ROWCOUNT

FROM 班级表 b INNER JOIN

Inserted i ON b.班号=i.班号

GO

(2) 删除型触发器：为学生表创建一个DELETE触发器。当在学生表上删除一行数据（表明减少一名学生）时，自动删除其成绩表中该学生的成绩信息。

在查询分析器中，键入一下命令，并单击命令栏中的执行按钮：

USE 教学管理数据库

GO

CREATE TRIGGER Trigger_Student_Delete

ON 学生表

FOR DELETE

AS

Delete

From 成绩表

Where 成绩表.学号 in （Select 学号 From Deleted）

(2) 更新型触发器：为学生表创建一个UPDATE触发器。当在学生表上修改学生的班号时，自动修改班级表中对应班号的人数。

在查询分析器中，键入一下命令，并单击命令栏中的执行按钮：

USE 教学管理数据库

GO

CREATE TRIGGER Trigger_Student_Update

ON 学生表

FOR UPDATE

AS

IF update(班号)

BEGIN

Update 班级表 Set 人数=人数-@@ROWCOUNT

FROM 班级表 b INNER JOIN Delete d ON b.班号=d.班号

Update 班级表 Set 人数=人数+@@ROWCOUNT

FROM 班级表 b INNER JOIN Inserted i ON b.班号=i.班号

END

GO