1 
实验四表的查询操作(4学时)
【实验目的】
了解SQL 语言的使用，进一步理解关系运算，巩固数据库的基础知识。【实验要求】
掌握利用Select 语句进行各种查询操作：单表查询、多表连接及查询、嵌套查询、集合查询等。
【实验内容】
在实验三创建并插入数据的表（Student ，Course ，SC ，Teacher ，TC ）的基础上，完成以下操作。
1.对实验步骤中所给示例进行验证。
2.参考所给示例，完成下列各种查询操作。
（1）将教师‘罗莉’的名字改为‘罗莉莉’。
（2）将两个同学（数据自己临时设置，用后即删除）的两门课程的成绩以运行sql 程序文件的形式插入sc
ore 表中。该题用以验证、理解和掌握关系模型的完整性规则；（3）求每门课的平均成绩，并把结果存入average 表（自行设计并创建）；
（4）将学生“马丽”的年龄改为2424；；
（5）将所有学生的zipcode 属性列值填补上；
（6）将average 表中的所有课程的平均成绩置零；
（7）删除average 表中的课程号为‘表中的课程号为‘c007c007c007’的平均成绩记录；’的平均成绩记录；
（8）删除所有average 表中平均成绩记录；
（9）建立一个临时学生信息表（）建立一个临时学生信息表（tstudent tstudent tstudent））
，删除该表中的学号含‘101’的所有学生记录。
（1010）查询全体学生的学号与姓名；）查询全体学生的学号与姓名；
（1111）查询全体学生的学号、姓名、所属系；）查询全体学生的学号、姓名、所属系；
（1212）查询全体学生的详细记录；）查询全体学生的详细记录；
（1313）查询全体学生的姓名及其年龄；）查询全体学生的姓名及其年龄；
（1414）查询全体学生的姓名、出生年份；）查询全体学生的姓名、出生年份；（1515）查询所有修过课的学生的学号；）查询所有修过课的学生的学号；
（1616）查询“计算机系”班全体学生名单；）查询“计算机系”班全体学生名单；
（1717）查询查询所有年龄在）查询查询所有年龄在23岁以下的学生姓名及其年龄；
（1818）查询考试成绩有不及格的学生的学号；）查询考试成绩有不及格的学生的学号；
（1919）查询年龄在）查询年龄在20至22岁之间的学生姓名、系和年龄；
（2020）查询年龄不在）查询年龄不在20至22岁之间的学生姓名、系和年龄；（2121）查询“）查询“计算机系”和“电商系”的学生的姓名；
（2222）查询既不是“计）查询既不是“计1111”也不是“计”也不是“计6161”班的学生的姓名和班级信息；”班的学生的姓名和班级信息；
（2323）查询学号为“）查询学号为“04262002”的学生的详细情况；
（2424）查询学号以“）查询学号以“04262”打头的学生信息；
（2525）查询所有姓“张”学生的学号、姓名、性别、年龄；）查询所有姓“张”学生的学号、姓名、性别、年龄；
（2626）查询名字中第二个字有“海”字的学生的学号、姓名、性别、年龄；）查询名字中第二个字有“海”字的学生的学号、姓名、性别、年龄；
（2727）查询所有不姓“刘”学生的姓名；）查询所有不姓“刘”学生的姓名；
（2828）查询课程号以“）查询课程号以“C ”开头的最后两个字母为“”开头的最后两个字母为“050505”的课程号和课程名；”的课程号和课程名；
（2929）某些学生选修某门课程后没有参加考试，所以有选修课记录，但没有考试成绩，）某些学生选修某门课程后没有参加考试，所以有选修课记录，但没有考试成绩，试查缺少考试成绩的学生和相应的课程号；
（3030）查全部有成绩记录的学生学号、课程号；）查全部有成绩记录的学生学号、课程号；
（3131）查“计算机系”年龄在）查“计算机系”年龄在22岁以下的学生学号、姓名；
（3232）查选修了“）查选修了“）查选修了“C001C001C001”号课程的学生学号及其成绩，查询结果按分数降序排序；”号课程的学生学号及其成绩，查询结果按分数降序排序；（3333））查询全体学生情况，查询全体学生情况，查询结果按所在系升序排列，查询结果按所在系升序排列，查询结果按所在系升序排列，对同一系中的学生按年龄降序对同一系中的学生按年龄降序排列；
（3434）查询学生总人数；）查询学生总人数；）查询学生总人数；
（3535）查询选修了课程的学生人数；）查询选修了课程的学生人数；）查询选修了课程的学生人数；
（3636）在所有课程中查询最高分的学生学号和成绩；）在所有课程中查询最高分的学生学号和成绩；）在所有课程中查询最高分的学生学号和成绩；
（3737）查询学习“）查询学习“）查询学习“C001C001C001”课程的学生最高分数；”课程的学生最高分数；”课程的学生最高分数；
（3838）计算各个课程号与相应的选课人数；）计算各个课程号与相应的选课人数；）计算各个课程号与相应的选课人数；
（3939）查询“计算机系”选修了两门课程以上的学生学号、姓名；）查询“计算机系”选修了两门课程以上的学生学号、姓名；）查询“计算机系”选修了两门课程以上的学生学号、姓名；
（4040）自然连接）自然连接student 和score 表；表；  （4141）使用自身连接查询每一门课程的间接先行课（即先行课的先行课））使用自身连接查询每一门课程的间接先行课（即先行课的先行课））使用自身连接查询每一门课程的间接先行课（即先行课的先行课）
（4242）使用复合条件连接查询选修“）使用复合条件连接查询选修“）使用复合条件连接查询选修“c001c001c001”号课程且成绩在”号课程且成绩在90分以上的所有同学；分以上的所有同学；
（4343）使用复合条件连接查询每个学生选修的课程名及其成绩；）使用复合条件连接查询每个学生选修的课程名及其成绩；）使用复合条件连接查询每个学生选修的课程名及其成绩；
（4444）查询选修了全部课程的学生；）查询选修了全部课程的学生；）查询选修了全部课程的学生；
（4545）查询所有选修了）查询所有选修了C001号课程的学生学号、姓名；号课程的学生学号、姓名；
（4646）查询选修了课程）查询选修了课程C001或c007的学生学号、姓名；的学生学号、姓名；
（4747）查询“计算机系”的学生及年龄不大于）查询“计算机系”的学生及年龄不大于23岁的学生；岁的学生；
（4848）查询既选修了课程）查询既选修了课程C001又选修了课程c007的所有学生学号、姓名；的所有学生学号、姓名；
（4949）查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄；）查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄；）查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄；
（5050）查询其他班中比“计算机系”所有学生年龄都小的学生名单；）查询其他班中比“计算机系”所有学生年龄都小的学生名单；）查询其他班中比“计算机系”所有学生年龄都小的学生名单；
（5151）查询与“夏天”在同一个系学习的学生学号、姓名、性别、年龄；）查询与“夏天”在同一个系学习的学生学号、姓名、性别、年龄；）查询与“夏天”在同一个系学习的学生学号、姓名、性别、年龄；
（5252）建立“计算机系”学生的视图）建立“计算机系”学生的视图1；
（5353）建立“计算机系”学生的视图）建立“计算机系”学生的视图2，并要求进行修改与插入时，仍须保证该视图只有“计算机系”班学生；有“计算机系”班学生；
（54）建立“计算机系”选修了“C001”课程的学生的视图，定义视图名为“v_cs_C001_student1v_cs_C001_student1””
； （5555）建立“计算机系”班选修了“）建立“计算机系”班选修了“）建立“计算机系”班选修了“C001C001C001”课程且成绩在”课程且成绩在90分以上的学生的视图，定义视图名为“义视图名为“cs_c001_student2cs_c001_student2cs_c001_student2””
； （5656）定义一个反映学生年龄的视图，定义视图名为“）定义一个反映学生年龄的视图，定义视图名为“）定义一个反映学生年龄的视图，定义视图名为“v_birth_student v_birth_student v_birth_student””； （5757）将学生表中所有女生记录定义为一个视图，视图名为“）将学生表中所有女生记录定义为一个视图，视图名为“）将学生表中所有女生记录定义为一个视图，视图名为“v_female_student v_female_student v_female_student””
； （5858）将学生的学号及其平均成绩定义为一个视图，视图名为“）将学生的学号及其平均成绩定义为一个视图，视图名为“）将学生的学号及其平均成绩定义为一个视图，视图名为“v_average_student v_average_student v_average_student””
； （5959）在“计算机系”学生视图中出年龄小于）在“计算机系”学生视图中出年龄小于22岁的学生；岁的学生；
（6060）利用视图查询“计算机系”选修了“）利用视图查询“计算机系”选修了“）利用视图查询“计算机系”选修了“C001C001C001”课程的学生；”课程的学生；”课程的学生；
（6161）通过（）通过（）通过（525252）中的“计算机系”视图修改某个学生的名字；）中的“计算机系”视图修改某个学生的名字；）中的“计算机系”视图修改某个学生的名字；
（6262）通过（）通过（）通过（535353）中的“计算机系”视图，插入一个新学生记录。）中的“计算机系”视图，插入一个新学生记录。）中的“计算机系”视图，插入一个新学生记录。
（6363）通过（）通过（）通过（535353）中的“计算机系”视图，删除一个学生记录。）中的“计算机系”视图，删除一个学生记录。）中的“计算机系”视图，删除一个学生记录。
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 表中检索出教师的职称并且要求显示的职称不重复。
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 ORDER BY
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 SQL 20002000兼容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.ade 
from    student,sc 
WHERE  student.sno=sc.sno 
select  student.sno,student.sname,sco,ade 
from    student,sc,course 
WHERE  student.sno=sc.sno and sco=courseo 
２．使用UNION 子句进行查询子句进行查询
使用UNION 子句可以将一个或者多个表的某些数据类型相同的列显示在同一列上。子句可以将一个或者多个表的某些数据类型相同的列显示在同一列上。如如
从teacher表中列出教工号、姓名并从student表中列出学号及学生姓名，
语句及查询结果如
表中列出学号及学生姓名，语句及查询结果如下：
下：
select sno AS 学号或工号, sname AS 姓名
姓名 from student 
union 
select tno, tname from teacher 
3．用GROUP子句进行查询
子句进行查询
如果要在数据检索时对表中数据按照一定条件进行分组汇总或求平均值，就要在SELECT语句中与GROUP BY子句一起使用集合函数。使用GROUP BY子句进行数据检索可得到数据分类的汇总统计、平均值或其他统计信息。
可得到数据分类的汇总统计、平均值或其他统计信息。
子句。
⑴使用不带HA VING的GROUP BY子句。
使用不带HA VING 的GROUP BY子句汇总出SC表中的学生的学号及总成绩，语句如下：
下：
select '学号
学号'=sno,'总成绩'=SUM(Grade) 
from SC 
group by Sno 
子句。
⑵使用带HA VING 的GROUP BY子句。
使用带HA VING 的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 , ade 
FROM SC 
WHERE sno IN 
( SELECT sno FROM student 
j10011‟‟ AND Ssex='男') 
WHERE Sclass=
Sclass=‟‟j10011
，语句如下： 使用IN关键字查询出与教师不同名的学生(学号，姓名)，语句如下：
select sno 学号, sname 姓名
姓名
from student 
where sname not in (select tname from teacher) 
sql统计每个系的学生人数
EXISTS关键字。使用EXISTS关键字查询出…j10011‟班的学
(2) 使用EXISTS 或NOT 
NOT EXISTS
生的学号、课程号及相应的成绩，语句如下：
生的学号、课程号及相应的成绩，语句如下：
SELECT SC.sno，SCo，SC.grade 
FROM SC 
WHERE EXISTS 
（SELECT * FROM student 
WHERE SC.sno=student.sno AND student.sclass=…j10011‟）
【思考题】
1. 如何提高数据查询和连接速度。
如何提高数据查询和连接速度。
2. 对于常用的查询形式或查询结果，怎样处理好？
对于常用的查询形式或查询结果，怎样处理好？
3..组合查询语句是否可以用其他语句代替，有什么不同？
组合查询语句是否可以用其他语句代替，有什么不同？
4.使用
子句后，语句中的统计函数的运行结果有什么不同？
分组条件>子句后，语句中的统计函数的运行结果有什么不同？
使用 GROUP BY <；分组条件
5.数据库中一般不允许更改主码数据。如果需要更改主码数据时，怎样处理？
数据库中一般不允许更改主码数据。如果需要更改主码数据时，怎样处理？
6. 为什么不能随意删除被参照表中的主码？
为什么不能随意删除被参照表中的主码？

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。