试验三数据查询实验报告
学生表 | |||||
姓名 | 性别 | 籍贯 | 出生日期 | 班号 | |
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