河南工业大学实验报告
课程 数据库原理 _ 实验名称 实验二 交互式SQL
院 系：____信息科学与工程学院__ 专业班级 ：__ 计科F1203 _ 姓 名：_______ __ 学 号： ____ _______
指导老师： 孙宜贵 日 期： 2014.10.11
一.实验目的
1. 2. 3. 4. 5. 6. 7.
熟悉SQL的数据定义、数据操纵功能；
掌握利用SQL语句进行多表内/外连接查询和嵌套查询的方法； 掌握GROUP BY子句、HAVING子句、ORDER BY子句的用法； 掌握（NOT）IN、（NOT）EXISTS等谓词的用法；
掌握SUM、AVG、COUNT、MAX、MIN等集合函数的用法； 掌握SQL语句中数据更新相关操作；
熟悉视图基本操作：定义、查询、更新视图。
二.实验内容及要求
本实验的表结构如下所示：
Student (sno, sname, sex，sage，dept, oldgrade, advisor) Teacher (tno, tname, dept, salary, title) Course (cno, cname, descry, dept, credit) SC (sno, cno, grade)
以上数据库表的含义为：
Student—学生：sno（学号），sname（学生姓名），sex（性别），sage(年
龄)，dept（所在院系），oldgrade（高考成绩），advisor（导师） Teacher—教师：tno（教师编号），tname（教师姓名），dept（所在院系），
salary（工资），title（职称） Course—课程：cno（课程号），cname（课程名），descry（课程说明），
dept（开课院系），credits（学分） SC—成绩：sno（学号），cno（课程号），grade（成绩）
根据上述情况完成：
1. 使用SQL语句定义上述各表。
2. 查询选修了105323号课程的学生姓名。 3. 查询所有的学生及其选课信息。
4. 分别利用内连接、左连接和右连接实现上题，观察异同。
5. 查询工资不在2000到3000的教师姓名，按工资排序。 6. 查询高考平均成绩大于700分的学生所在院系。
7. 查询所有院系的平均高考成绩，并按平均成绩排序。 8. 查询所有学生的学号及成绩，并计算总成绩。 9. 查询每个院系教师的最高工资。
10. 把Teacher表中dept字段值为“计算机科学系”的记录修改为“信息科学与工程学院”。 11.新建一个视图，并依此查询课程编号为205323，成绩大于80分的学生名。 12.为 Teacher表增加一个字段，电话（Phone），数据类型char(12)，允许空。
三.实验过程及结果
1. 定义上述各表的SQL语句是：
CREATE TABLE teacher(tno char(3) primary key,tname char(8),dept
varchar(20),salary int,title char(6));
CREATE TABLE student(sno char(7) primary key,sname char(8),sex char(2),dept varchar(20),oldgrade int,tno char(3));
CREATE TABLE Course(cno char(6) primary key,cname varchar(20),descry varchar(50),dept varchar(20),credit float);
CREATE TABLE SC(sno char(7),cno char(6) ,grade int,primary key (sno,cno));
各表创建完成后执行结果如下图1所示：
图1
引入脚本，将各表数据信息录入，以便完成以下的要求。
2. 查询选修了105323号课程的学生姓名，SQL语句是：
select sname from student，course where cno = ‘105323’； 或者：
select sname from student，course where cno like‘105323’；
运行结果如图2所示：
图2
3. 查询所有的学生及其选课信息，SQL语句是：
select student.*,sc.* from student,sc where student.sno = sc.sno;
运行结果如图3所示：
图3
4. 分别利用内连接、左连接和右连接实现上题，观察异同。
内连接：相比等值连接，内链接把目标列中重复的属性列去掉啦。 用内连接查询所有的学生及其选课信息，SQL语句是：
select student.sno,sname,sex,dept,oldgrade,tno,cno,grade from student,sc
where student.sno = sc.sno;
运行结果如图4所示：
图4
左连接：列出左边关系中所有的元祖，运行结果如下图5所示：
图5
右连接：列出右边关系中所有的元祖，运行结果如下图6所示：
select sc.sno,cno,grade,sname,sex,dept,oldgrade,tno from student right outer join sc
on (student.sno = sc.sno);
图6
5. 查询工资不在2000到3000的教师姓名，按工资排序，SQL语句是：
Select tname from teacher where salary < 2000 or salary >3000 order by salary;
或者：
Select tname from teacher where salary not between 2000 and 3000 order by salary;
运行结果如下图7所示：
图7
6. 查询高考平均成绩大于700分的学生所在院系，SQL语句及运行结果如
下图8所示：
图8
7. 查询所有院系的平均高考成绩，并按平均成绩排序，SQL语句是：
select dept,avg(oldgrade) from student group by dept order by avg(oldgrade);
运行结果如下图9所示：
图9
8. 查询所有学生的学号及成绩，并计算总成绩，SQL语句是：
Select student.sno,count(grade)from student,sc where student.sno=sc.sno group by student.sno;
运行结果如下图10所示：
图10
9. 查询每个院系教师的最高工资，SQL语句是：
select dept,MAX(salary) from teacher group by dept;
运行结果如下图11所示：
图11
10. 把Teacher表中dept字段值为“计算机科学系”的记录修改为“信息科学与工程学院”，SQL语句是：
update teacher set dept = '信息科学与工程学院' where dept = '计算机科学';
查询修改后的运行结果如下图12所示：
图12
11. 新建一个视图，并依此查询课程编号为205323，成绩大于80分的学生名。
新建视图ISstudent的SQL语句是：
create view ISstudent as select sname , cno, grade from student,sc;
运行结果如下图13所示：
图13
查询SQL语句是： select sname from ISstudent where cno like '205323' and grade > 80;
运行结果如下图14所示：
图14
12. 为 Teacher表增加一个字段，电话（Phone），数据类型char(12)，允许空。 增加字段phone的SQL语句及增加后的查询结果如下图15、图16所示：
图15
图16
四．实验中的问题及心得
从这次实验中，我认识了什么是交互式SQL，对数据库的书写规范有了更深刻的印象。通过对数据库进行创建，用SQL语句对数据进行更新相关操作，了解到SQL语句功能的强大之处， 简单的一句sql语句就能对数据进行定义、查询、操纵和控制。
通过此次实验，我还简单掌握了group by子句、having子句、order by子句及sum、avg、count、max、min等集合函数的用法；也对视图的创建、查询、更新有了进一步的了解。
但是，此次实验课上的练习是远远不足以保证能完全掌握对数据库的使用，若想完全熟悉用SQL对基本表和视图进行操纵，接下来必须加强练习。
