数据库实验报告——多表查询

集美大学计算机工程学院实验报告

课程名称：SQL Server实用教程班级：网络0911实验成绩：指导教师：李传目姓名：钟小平

实验项目名称：

多表查询学号：

2009811012

上机实践日期：

2011-11-25

实验项目编号：05 组号：上机实践时间：

2课时

一、实验目的

1．掌握多表连接查询方法；

2．掌握IN 子查询的嵌套查询；

3．了解EXISTS嵌套查询方法。

二、实验内容与设计思想

1．多表等值连接查询；

2．外连接查询；

3．IN子查询嵌套；

4* EXISTS嵌套查询。

设计思想：

1、复习书本上的相关知识，如查阅多表的连接查询、多表的等值连接查询、表自身的连

接、IN嵌套子查询和EXISTS嵌套子查询等知识点；

2、先建立一个XSDA库，在建立相关表（利用上次验证试验建立好的数据库）；

3、根据实验要求进行各种查询操作；

4、遇到问题，及时问老师或同学，查阅资料，以便得到及时解决；

5、认真做好试验报告。

三、实验使用环境

操作系统：Win-7

编程环境：Microsoft SQL Server 2008

四、实验步骤和调试过程

###验证性实验

1．多表的连接查询（相当于做笛卡儿乘积）

SELECT * FROM C XX，SC XX（截图中间有省略）

…………

(1) 查询各学生的选课信息（包括学号、课程名、成绩）

因为学号和成绩在SC表中，而课程名在C表中，因此需要多表查询。SELECT SNO，CNAME，GRADE

FROM C XX C，SC XX SC

WHERE https://www.360docs.net/doc/5b16984455.html,O=https://www.360docs.net/doc/5b16984455.html,O

(2) 查询学生的选课记录，显示学生的学号、姓名、课程号、成绩）SELECT S.SNO，SNAME，CNO，GRADE

FROM S XX S，SC XX SC

WHERE S.SNO=SC.SNO

(3) 查询学生的选课记录，显示学生的学号、姓名、课程号、课程名、成绩SELECT S.SNO，SNAME，https://www.360docs.net/doc/5b16984455.html,O，CNAME，GRADE

FROM S XX S，SC XX SC，C XX C

WHERE S.SNO=SC.SNO AND https://www.360docs.net/doc/5b16984455.html,O=https://www.360docs.net/doc/5b16984455.html,O

3．左外连接查询：当希望左表（第一张表）中所有记录全部显示出来时，需要用左外连接操作。

INSERT INTO S (SNO,SNAME) VALUES（’20000’,’ZXX’）

SELECT S.SNO，SNAME，CNO，GRADE

FROM S XX S

LEFT OUTER JOIN SC XX SC ON S.SNO=SC.SNO

观察与下面等值连接的执行结果有何不同？

SELECT S.SNO，SNAME，CNO，GRADE

FROM S XX S, SC XX SC WHERE S.SNO=SC.SNO

4．表自身的连接

(1) 查询与‘李勇’同系的学生学号

将学生表S与S本身进行等值连接（系部相等），因为S与S做连接操作时不能区分，所以，对表取一个别名。然后将第二张表S中名字为‘李勇’的记录选择出就可。SELECT S1.SNO

FROM S XX AS S1，S XX AS S2

WHERE S1.DEPA=S2.DEPA AND S2.SNAME=’李勇’

（2）显示每个学生的非最高分成绩（学生自己的选课成绩中，不是最高分的选课记录显示出来）

SELECT SNO, CNO, GRADE FROM SC XX AS SC1

WHERE GRADE <(SELECT MAX(GRADE) FROM SC XX AS SC2

WHERE SC2.SNO=SC1.SNO)

5．IN嵌套子查询

（1）不相关的IN 子查询：子查询可以单独执行，与被嵌套的查询无关。

如，查询与‘李勇’同系的学生学号

可以先查询出‘李勇’所在的系，然后再到S表中查询与上述结果相同的记录。SELECT SNO

FROM S XX

WHERE DEPA IN

（SELECT DEPA FROM S XX WHERE SNAME=‘李勇’）

查询‘数据库原理’课程的选课人数

SELECT COUNT（*）FROM SC XX

WHERE CNO IN

（SELECT CNO FROM C XX WHERE CNAME=‘数据库原理’）

（2）相关的子查询：子查询中要用到父查询表的信息，子查询不能独立执行。

如，查询选修课程号为“C01”课程且成绩至少高于选修课程号为“C02”的同学的Cno、Sno和GRADE。

在子查询中，因为要查找该同学‘C02’课程的成绩，所以，需要父查询表中该学生的学号信息。

SELECT CNO，SNO，GRADE

FROM SC XX AS SC1

WHERE CNO=’C01’AND GRADE >(

SELECT GRADE FROM SC XX AS SC 2

WHERE SC2.SNO=SC1.SNO AND https://www.360docs.net/doc/5b16984455.html,O=’C02’)

6* EXISTS嵌套子查询

（1）执行以下语句，观察显示的两个查询结果

SELECT SNO，SNAME

FROM S XX

WHERE EXISTS

（SELECT * FROM SCV WHERE CNO=‘C03’）

GO

SELECT SNO，SNAME FROM S XX

（2）执行以下语句，观察显示的两个查询结果

INSERT INTO C XX VALUES（‘C06’，‘数据库安全’，NULL，3）SELECT SNO，SNAME

FROM S XX

WHERE EXISTS

（SELECT * FROM SC WHERE CNO=‘C06’）

（3）查询‘数据库原理’课程的选课人数

SELECT COUNT（*）

FROM SC XX

WHERE EXISTS

（ SELECT * FROM C XX

WHERE https://www.360docs.net/doc/5b16984455.html,O=https://www.360docs.net/doc/5b16984455.html,O AND CNAME=’数据库原理’)

###设计性实验

（1）查询“计算机系“学生所教课程的成绩表。

代码：

SELECT S.SNO,CNO,GRADE

FROM SC12SC,S12S

WHERE SC.SNO=S.SNO AND S.DEPA='计算机系'

（2）查询成绩比该课程平均成绩低的同学的成绩表。

代码：

SELECT SNO,CNO,GRADE FROM SC12SC1

WHERE GRADE<(SELECT AVG(GRADE)FROM SC12SC2

WHERE https://www.360docs.net/doc/5b16984455.html,O=https://www.360docs.net/doc/5b16984455.html,O)

（3）查询选修101 课程的学生学号、课程名、成绩，并要求对查询结果按学号的降序排列，如果学号相同则按成绩的升序排列。

代码：

SELECT SNO,CNAME,GRADE FROM SC12SC,C12 C

WHERE https://www.360docs.net/doc/5b16984455.html,O='c01'AND https://www.360docs.net/doc/5b16984455.html,O=https://www.360docs.net/doc/5b16984455.html,O

ORDER BY SNO DESC

（4）查询选修两门及两门以上课程的学生学号及姓名。

代码：

SELECT S.SNO,SNAME FROM S12S

JOIN SC12SC ON S.SNO=SC.SNO

GROUP BY S.SNO,SNAME

HAVING COUNT(*)>=2

（5）查询年龄在20-22之间的男生的选修的课程号。

代码：

SELECT CNO FROM SC12SC

WHERE EXISTS(

SELECT*FROM S12S

WHERE SEX='男'AND AGE BETWEEN 20 AND 22 AND SC.SNO=S.SNO )

（6）查询选修‘数据结构’课程的学生人数。

代码：

SELECT COUNT(*)FROM SC12SC

WHERE EXISTS(

SELECT*FROM C12 C

WHERE https://www.360docs.net/doc/5b16984455.html,O=https://www.360docs.net/doc/5b16984455.html,O AND CNAME='数据结构')

GO

SELECT COUNT(*)FROM SC12

WHERE CNO IN(

SELECT CNO FROM C12WHERE CNAME='数据结构')

（7）查询每门选课成绩在80分以上的学生学号、姓名。

代码：

SELECT S.SNO,SNAME FROM S12S

WHERE (SELECT MIN(GRADE)FROM SC12WHERE S.SNO=SNO)>=80 GO

SELECT S.SNO,SNAME FROM S12S

WHERE EXISTS(

SELECT*FROM SC12SC

GROUP BY SNO

HAVING MIN(GRADE)>=80 AND S.SNO=SC.SNO)

五、实验小结

1、在这次实验之前，认真查阅书本上相关知识点和代码；

2、通过这次实验基本掌握多表连接查询方法、掌握IN 子查询的嵌套查询、了解EXISTS嵌套查询方法。

3、子句HAVING()必须要与GROUP BY语句使用，这一点非常重要，由于不小心，在实验中有两三次提示这样的错误，切记切记；

思考题：

1、多表的连接查询是否都可以转换成嵌套查询？什么情况下连接查询不能用嵌套查询表示？

多表的连接查询不是都可以转换成嵌套查询的，当查询结果中有从多张表中输出元素时，这种情况下就不能转换的；当涉及左外连接或右外连接时，也不能转换。

2、多表连接查询和IN嵌套子查询从查询效率上来说，哪种查询的效果更好？为什么?

关于查询效果的好坏，两者之间没有绝对的好坏，只能根据具体情况而定。当嵌套子查询为无关嵌套查询时，IN嵌套子查询的查询效率要高，因为无关嵌套查询只对子查询涉及的表扫描一次，而多表连接要做笛卡尔成绩运算，子表要查询多次。如果嵌套子查询为相关嵌套查询时，两者的查询效率差不多。

六、附录

《SQL Server实用教程》