实验三、 多表查询
1.实验目的
1) 熟悉SQL Server 2008对象资源管理器环境。
2) 掌握基本的SELECT查询及其相关子句的使用。
3) 掌握复杂的SELECT查询,如多表查询、子查询、连接和联合查询。
2.实验内容
1) 启动SQL Server 2008 对象资源管理器环境。
2) 涉及多表的简单查询。
3) 涉及多表的复杂查询。
3. 实验步骤
1)打开数据库中三个表
在“SQL对象资源管理器”窗口中选择要操作的数据库,如“XSCJ”数据库。
SELECT *
FROM KC
SELECT *
FROM XS_KC
SELECT *
FROM XSQK
2) 在KC表中查询学分低于6的课程信息,并按课程号升序排列。
SELECT * FROM KC
WHERE KC.学分<6
ORDER BY 课程号
3) 在XS_KC表中按学号分组汇总学生的平均分,并按平均分的降序排列。
SELECT 学号,平均分=AVG(成绩) FROM XS_KC
GROUP BY 学号
ORDER BY 平均分 DESC
4) 在XS_KC表中查询选修了1门以上课程的学生学号。
SELECT 学号 FROM XS_KC
GROUP BY 学号
HAVING COUNT(*)>1
5) 按学号对不及格的成绩记录进行明细汇总。
SELECT 学号,课程号,成绩 FROM XS_KC
WHERE 成绩<60
ORDER BY 学号
COMPUTE COUNT(成绩)
BY 学号
6) 分别用子查询和连接查询,求107号课程不及格的学生信息。
用子查询:
SELECT 学号,姓名,联系电话 FROM XSQK
WHERE 学号 IN
( SELECT 学号
FROM XS_KC
WHERE 课程号=107 AND 成绩<60)
用连接查询:
SELECT 学号,姓名,联系电话 FROM XSQK
JOIN XS_KC ON XSQK.学号=XS_KC.学号
WHERE课程号=107 AND 成绩<60
7) 用连接查询在XSQK表中查询住在同一寝室的学生,即其联系电话相同
SELECT A.学号,A.姓名,A.联系电话
FROM XSQK A JOIN XSQK B ON A.联系电话=B.联系电话
WHERE A.学号!=B.学号
请自已完成以下的查询:
8) 查询XSQK表中所有的系名。
SELECT DISTINCT 所在系
FROM XSQK
9) 查询有多少同学选修了课程。
SELECT DISTINCT 学号
FROM XS_KC
10) 查询有多少同学没有选课。
SELECT 学号
FROM XSQK
WHERE (NOT EXISTS(SELECT *
FROM XS_KC
WHERE XSQK.学号=XS_KC.学号 ))
11) 查询与杨颖同一个系的同学姓名。
SELECT 姓名,所在系
FROM XSQK
WHERE 所在系=(SELECT 所在系 FROM
XSQK WHERE 姓名='杨颖')
12) 查询选修了课程的学生的姓名、课程名与成绩。
SELECT 姓名,课程号,成绩
FROM XSQK,XS_KC
WHERE (EXISTS(SELECT *
FROM XS_KC
WHERE XSQK.学号=XS_KC.学号 ))
13) 统计每门课程的选课人数和最高分。
SELECT 课程号,COUNT(课程号) AS 选课人数,MAX(成绩) AS 最高分
FROM XS_KC
GROUP BY 课程号
14) 统计每个学生的选课门数和考试总成绩,并按选课门数的降序排列。
SELECT 学号,COUNT(课程号) AS 选课门数,SUM(成绩) AS 考试总成绩
FROM XS_KC
GROUP BY 学号
ORDER BY 选课门数 DESC
4.思考回答
1) 多表查询的两种方法:连接查询、嵌套查询,在实现时如何选取,试对两种方法做比较。
答:嵌套查询是包含一个或多个子查询或子查询的另一个术语的select语句。在外部查询中包含内部查询的另一个子查询是SQL语句的扩展。
连接查询是关系数据库中最重要的查询。在关系数据库管理系统中,数据之间的关系不需要确定表的建立时间,实体的所有信息通常都存储在表中。检索数据时,通过连接操作查询存储在多个表中的不同实体的信息。
当查询条件比较多,查询流程呈线性递推的结构时,使用嵌套查询可以更加具有逻辑性。当查询涉及不止一个表,且条件比较简单时,使用连接查询比较方便。
当两个表之间没有相同的属性时,只能建立两个表的交叉连接,没办法进行内、外连接。但是在特殊情况下可以进行嵌套查询。
2) 内连接、外连接有什么不同?左外连接、右外连接、全外连接又有什么不同?
答: 内连接,INNER JOIN,显示符合条件的记录,此为默认值。即连接结果仅包含符合连接条件的值行,参与连接的两个表都应该符合连接条件。外连接则不然,外连接包括左、右、全外连接。
外连接是主表中的数据无论是否满足连接条件,都保留在结果中,从表中的符合数据的符合的值保留在结果中。
左外连接,即左表为主表。左表中的值全保留,右表中不存在满足连接条件的值时,用NULL补充。
右外连接,即右表为主表。右表中的值全保留,左表中不存在满足连接条件的值时,用NULL补充。
全外连接,即两个表均为主表。表中的值全保留,两个表中不存在满足连接条件的值都用NULL补充。
3) 嵌套查询中不相关子查询、相关子查询的求解方法。
答:不相关子查询:在子查询中,不涉及父查询中的属性。在普通子查询中,先执行子查询,然后将其结果参与到父查询中。即子查询和父查询是先后进行的
相关子查询:在子查询中,引用父查询中的属性值。在相关子查询中,首先取父查询表中的一行记录,内部的子查询利用此行中相关的属性值进行查询,然后父查询根据返回的结果判断此行是否满足查询条件。如果满足条件,则把该行放入父查询的查询结果集合中,重复这一过程,直到父查询表中的每一行数据都被执行。
利用嵌套查询,重在分析问题,将问题分成相互联系的最小子问题,逐个解决,进而解决整个复合问题。例如:查询选修了计算机基础课程的学生的姓名、成绩。可以分为:1.查询计算机基础课程的课程号、2.利用课程号查询选修该课程的学生学号、3.利用学号查询姓名和成绩。
SELECT Student.s_name,score
FROM Student,Choice
WHERE Student.s_no=Choice.s_no
AND Student.s_no = ANY (SELECT s_no
FROM Choice
WHERE course_no=(SELECT course_no
FROM Course
WHERE course_name=’计算机基础’))
这样就利用子查询将问题解决了。