发布时间 : 星期五 文章数据库实验4 表的查询操作(4学时)更新完毕开始阅读7a196a84a1c7aa00b52acbb4
实验四 表的查询操作(4学时)
【实验目的】
了解SQL语言的使用，进一步理解关系运算，巩固数据库的基础知识。
【实验要求】
掌握利用Select语句进行各种查询操作：单表查询、多表连接及查询、嵌套查询、集合查询等。
【实验内容】
在实验三创建并插入数据的表（Student， Course，SC，Teacher，TC）的基础上，完成以下操作。
1.对实验步骤中所给示例进行验证。
2.参考所给示例，完成下列各种查询操作。 （1）将教师‘罗莉’的名字改为‘罗莉莉’。
（2）将两个同学（数据自己临时设置，用后即删除）的两门课程的成绩以运行sql程序文件的形式插入score表中。该题用以验证、理解和掌握关系模型的完整性规则；
（3）求每门课的平均成绩，并把结果存入average表（自行设计并创建）； （4）将学生“马丽”的年龄改为24；
（5）将所有学生的zipcode属性列值填补上；
（6）将average表中的所有课程的平均成绩置零；
（7）删除average表中的课程号为‘c007’的平均成绩记录； （8）删除所有average表中平均成绩记录； （9）建立一个临时学生信息表（tstudent），删除该表中的学号含‘101’的所有学生记录。
（10）查询全体学生的学号与姓名；
（11）查询全体学生的学号、姓名、所属系； （12）查询全体学生的详细记录； （13）查询全体学生的姓名及其年龄； （14）查询全体学生的姓名、出生年份； （15）查询所有修过课的学生的学号； （16）查询“计算机系”班全体学生名单；
（17）查询查询所有年龄在23岁以下的学生姓名及其年龄； （18）查询考试成绩有不及格的学生的学号；
（19）查询年龄在20至22岁之间的学生姓名、系和年龄； （20）查询年龄不在20至22岁之间的学生姓名、系和年龄； （21）查询“计算机系”和“电商系”的学生的姓名；
（22）查询既不是“计11”也不是“计61”班的学生的姓名和班级信息； （23）查询学号为“04262002”的学生的详细情况； （24）查询学号以“04262”打头的学生信息；
（25）查询所有姓“张”学生的学号、姓名、性别、年龄；
（26）查询名字中第二个字有“海”字的学生的学号、姓名、性别、年龄； （27）查询所有不姓“刘”学生的姓名；
（28）查询课程号以“C”开头的最后两个字母为“05”的课程号和课程名；
（29）某些学生选修某门课程后没有参加考试，所以有选修课记录，但没有考试成绩，试查找缺少考试成绩的学生和相应的课程号；
（30）查找全部有成绩记录的学生学号、课程号；
（31）查找“计算机系”年龄在22岁以下的学生学号、姓名；
（32）查找选修了“C001”号课程的学生学号及其成绩，查询结果按分数降序排序； （33）查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列；
1
（34）查询学生总人数；
（35）查询选修了课程的学生人数；
（36）在所有课程中查询最高分的学生学号和成绩； （37）查询学习“C001”课程的学生最高分数； （38）计算各个课程号与相应的选课人数；
（39）查询“计算机系”选修了两门课程以上的学生学号、姓名； （40）自然连接student和score表；
（41）使用自身连接查询每一门课程的间接先行课（即先行课的先行课）
（42）使用复合条件连接查询选修“c001”号课程且成绩在90分以上的所有同学； （43）使用复合条件连接查询每个学生选修的课程名及其成绩； （44）查询选修了全部课程的学生；
（45）查询所有选修了C001号课程的学生学号、姓名； （46）查询选修了课程C001或c007的学生学号、姓名； （47）查询“计算机系”的学生及年龄不大于23岁的学生；
（48）查询既选修了课程C001又选修了课程c007的所有学生学号、姓名； （49）查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄； （50）查询其他班中比“计算机系”所有学生年龄都小的学生名单； （51）查询与“夏天”在同一个系学习的学生学号、姓名、性别、年龄； （52）建立“计算机系”学生的视图1；
（53）建立“计算机系”学生的视图2，并要求进行修改与插入时，仍须保证该视图只有“计算机系”班学生；
（54）建立“计算机系”选修了“C001”课程的学生的视图，定义视图名为“v_cs_C001_student1”；
（55）建立“计算机系”班选修了“C001”课程且成绩在90分以上的学生的视图，定义视图名为“cs_c001_student2”；
（56）定义一个反映学生年龄的视图，定义视图名为“v_birth_student”；
（57）将学生表中所有女生记录定义为一个视图，视图名为“v_female_student”； （58）将学生的学号及其平均成绩定义为一个视图，视图名为“v_average_student”； （59）在“计算机系”学生视图中找出年龄小于22岁的学生； （60）利用视图查询“计算机系”选修了“C001”课程的学生； （61）通过（52）中的“计算机系”视图修改某个学生的名字； （62）通过（53）中的“计算机系”视图，插入一个新学生记录。 （63）通过（53）中的“计算机系”视图，删除一个学生记录。 3.自行设计不同情况的DML语句（不少于100条语句），并进行实验验证。 【实验步骤】 一、单表查询
1．简单查询
打开查询分析器，根建立teacher表，并加入数据。从teacher表中分别检索出教师的所有信息，以及仅查询教工号、姓名和职称。语句如下：
select * from teacher
select tno, tname from teacher
如要查询时改变列标题的显示，则从teacher表中分别检索出教师教工号、姓名、电子邮箱信息并分别加上?教师姓名?、?教工号?、?电子邮箱?等标题信息。
select tno 工号, tname 姓名, temail 电子邮箱 from teacher
使用TOP关键字：分别从teacher中检索出前2条及前面67%的教师的信息。 select top 2 * from teacher
select top 67 percent * from teacher 使用DISTINCT关键字：从teacher表中检索出教师的职称并且要求显示的职称不重复。
2
select distinct tposition from teacher
2．用计算列：将teacher表中各教师的姓名、教工号及工资按95%发放的信息，第2条语句将工资按95%发放后列名该为?预发工资?。语句如下：
select tno tname ,tsalary*0.95 from teacher
select tno工号, tname姓名, tsalary*0.95 AS 预发工资 from teacher 3．使用ORDER BY子句对查询的结果进行排序
使用ORDER BY语句可以对查询的结果进行排序，ASC、DESC分别是升序和降序排列的关键字，系统默认的是升序排列。从teacher表中查询工资大于2800的教师的教工号、姓名，并按升序排列，语句如下：
select tno, tname from teacher
WHERE tsalary>2800 order by tsalary ASC 4．条件查询
⑴ 使用关系运算符：从teacher表中查询出工作量大于288的教师资料，语句如下： select * from teacher
WHERE tamount>288 order by tamount DESC
⑵ 使用BETWEEN AND谓词：从teacher表中查询出工作量界于144和288之间的教师资料，语句如下：
select * from teacher WHERE tamount between 144 and 288
⑶ 使用IN谓词：从teacher表中查询出职称为“教授”或“副教授”的教师的教工号、教师姓名、职称及家庭住址，语句如下：
select tno,tname,tposition, taddress from teacher WHERE tposition in ('教授','副教授')
⑷ 使用LIKE谓词：从teacher表中分别检索出姓?王?的教师的资料，或者姓名的第2个字是?莉?或?轩?的教师的资料，语句如下：
select * from teacher WHERE tname like '王%'
select * from teacher WHERE tname like '_[莉，轩]%' 二、多表查询
数据库各表中存放着不同的数据，用户经常需要用多个表中的数据来组合提炼出所需要的信息，如果一个查询需要对多个表进行操作，就称为关联查询，关联查询的结果集或结果表称为表之间的连接。关联查询实际上是通过各各表之间共同列的关联来查询数据的，它是关系数据库查询最基本的特征。
1．SQL 2000兼容2种连接形式：用于FROM子句的ANSI连接语法和用于WHERE子句的SQL SERVER连接语法形式。
从student、course和SC三张表中检索学生的学号、姓名、学习课程号、学习课程名及课程成绩，语句如下：
select student.sno, sname, cno, grade
from student inner join SC on student.sno=SC.sno
select student.sname,sc.grade from student,sc
WHERE student.sno=sc.sno
select student.sno,student.sname,sc.cno,course.cname,sc.grade from student,sc,course
WHERE student.sno=sc.sno and sc.cno=course.cno
２．使用UNION子句进行查询
使用UNION子句可以将一个或者多个表的某些数据类型相同的列显示在同一列上。如
3
从teacher表中列出教工号、姓名并从student表中列出学号及学生姓名，语句及查询结果如下：
select sno AS 学号或工号, sname AS 姓名 from student union
select tno, tname from teacher 3．用GROUP子句进行查询
如果要在数据检索时对表中数据按照一定条件进行分组汇总或求平均值，就要在SELECT语句中与GROUP BY子句一起使用集合函数。使用GROUP BY子句进行数据检索可得到数据分类的汇总统计、平均值或其他统计信息。
⑴ 使用不带HAVING的GROUP BY子句。
使用不带HAVING 的GROUP BY子句汇总出SC表中的学生的学号及总成绩，语句如下：
select '学号'=sno,'总成绩'=SUM(Grade) from SC group by Sno
⑵ 使用带HAVING 的GROUP BY子句。
使用带HAVING 的GROUP BY子句汇总出SC表中总分大于450分的学生的学号及总成绩，语句如下：
select '学号'=sno,'总成绩'=SUM(Grade) from SC group by Sno
Having SUM(Grade)>160
4．用COMPUTE和COMPUTE BY子句进行查询
使用COMPUTE和COMPUTE BY 既能浏览数据又能看到统计的结果。
⑴ 用COMPUTE子句汇总出SC表中每个学生的学号及总成绩，语句如下： select '学号'=sno,'成绩'=Grade from SC order by sno COMPUTE SUM(Grade)
⑵ 用COMPUTE BY 子句按学号汇总出SC表中每个学生的学号及总成绩，语句如下： select '学号'=sno,'成绩'=Grade from SC
ORDER BY Sno COMPUTE SUM(Grade) by sno
观察执行COMPUTE和COMPUTE BY子句的结果有什么不同？
5．嵌套查询
(1) 使用IN或NOT IN关键字
使用IN关键字查询出j10011班所有男生的学号、课程号及相应的成绩，语句如下： select SC.sno , SC.cno,SC.grade FROM SC WHERE sno IN
( SELECT sno FROM student
WHERE Sclass=?j10011? AND Ssex='男')
使用IN关键字查询出与教师不同名的学生(学号，姓名)，语句如下： select sno 学号, sname 姓名 from student
where sname not in (select tname from teacher)
(2) 使用EXISTS 或NOT EXISTS关键字。使用EXISTS关键字查询出?j10011?班的学生的学号、课程号及相应的成绩，语句如下：
4