实验三 多表查询

1．实验目的

1. 进一步熟悉掌握SQL Server 查询分析器环境。
2. 掌握多表查询的两种方法：连接查询、嵌套查询。
3. 掌握合并查询的方法。

2．实验内容

1. 连接查询的两种连接方法：同一查询要求，考虑两种方法实现。
2. 通过实验体会外连接的实现：左外连接、右外连接、全外连接。
3. 涉及自身连接的查询 。
4. 涉及三表（多表）的连接查询。
5. 嵌套查询中不相关子查询、相关子查询的应用。
6. 引出子查询的谓词IN、ANY、ALL的应用。
7. 合并查询：并操作(UNION)。
8. 存储查询结果到表中：SELECT…INTO 语句的应用。

3. 实验步骤

  在实验一、二的基础上，运用SQL语句实现以下查询要求：

   1） 查询所有同学的选课及成绩情况，要求显示学生的学号s_no、姓名s_name、

      课程号course_no和课程的成绩score。

select [study].[dbo].[Choice].s_no,[s_name],[course_no],[score]

from [study].[dbo].[Student],[study].[dbo].[Choice]

where [study].[dbo].[Student].s_no=[study].[dbo].[Choice].s_no

   2）查询所有同学的选课及成绩情况，要求显示学生的姓名s_name、课程名

      称course_ name、课程的成绩score，并将查询结果存放到一个新的数据

      表new_table中。（SELECT…INTO）

select s_name,course_name,score

into new_table6

from [study].[dbo].Student,[study].[dbo].Choice,[study].[dbo].Course

where (Student.s_no=Choice.s_no)

and (Course.course_no=Choice.course_no)

   3）查询“计算机99-1”班的同学的选课及成绩情况，要求显示学生的学号

      s_no、姓名s_name、课程号course_no、课程名称course_name、课程的

      成绩score。

select Student.s_no,s_name,Course.course_no,Course_name,score

from [study].[dbo].Student,[study].[dbo].Course,[study].[dbo].Choice,[study].[dbo].Class

where (Student.s_no=Choice.s_no)

and (Course.course_no=Choice.course_no)

and Class.class_no=Student.class_no

and class_name='计算机99-1'

   4）查询所有同学的学分情况（假设课程成绩≥60分时可获得该门课程的学

      分），要求显示学生的学号s_no、姓名s_name、总学分（将该列定名为：

      total_score）。（用JOIN）

select Student.s_no,s_name,sum(score) as total_score

from [study].[dbo].Student join [study].[dbo].Choice

on Student.s_no=Choice.s_no

where score>=60

group by Student.s_no,s_name

   5）查询所有同学的平均成绩及选课门数，要求显示学生的学号s_no、姓名

      s_name、平均成绩（将该列定名为average_score）、选课的门数（将该

      列定名为：choice_num）。

select Student.s_no,s_name,Course.course_no,course_name

from [study].[dbo].Student join [study].[dbo].Choice

on Student.s_no=Choice.s_no

join [study].[dbo].Course

on Choice.course_no=Course.course_no

where Choice.course_no is not null and score is null

  

  6）查询所有选修了课程但未参加考试的所有同学及相应的课程，要求显示学

      生的学号s_no、姓名s_name、课程号course_no、课程名称course_name。

select Student.s_no,s_name,Course.course_no,course_name

from [study].[dbo].Student join [study].[dbo].Choice

on Student.s_no=Choice.s_no

join [study].[dbo].Course

on Choice.course_no=Course.course_no

where Choice.course_no is not null and score is null

  

  7）查询所有选修了课程但考试不及格（假设＜60分为不及格）的所有同学

      及相应的课程，要求显示学生的学号s_no、姓名s_name、课程号course_no、

      课程名称course_name、学分course_score。

select Student.s_no,s_name,Course.course_no,course_name,course_score

from [study].[dbo].Student join [study].[dbo].Choice

on Student.s_no=Choice.s_no

join [study].[dbo].Course

on Course.course_no=Choice.course_no

where score<60

   8）查询选修了课程名为“程序设计语言”的所有同学及成绩情况，要求显示

      学生的姓名s_name、课程的成绩score。（使用ANY）

select s_name,score

from [study].[dbo].Student join [study].[dbo].Choice

on Student.s_no=Choice.s_no

join [study].[dbo].Course

on Course.course_no=Choice.course_no

where Course.course_name  = '程序设计语言'

   9）查询“计算机系”的所有同学及成绩情况，要求显示学生的学号s_no、

      姓名s_name、班级名称class_name、课程号course_no、课程名称

      course_name、课程的成绩score。

select Student.s_no,s_name,class_name,Course.course_no,course_name,score

from [study].[dbo].Student,[study].[dbo].Class,[study].[dbo].Course,[study].[dbo].Choice

where (Course.course_no=Choice.course_no)

and (Class.class_no=Student.class_no)

and (Student.s_no=Choice.s_no)

and class_dept='计算机系'

   10）查询所有教师的任课情况，要求显示教师姓名t_name、担任课程的名称

       course_name。

/*连接查询*/

select t_name,course_name

from [study].[dbo].Teacher join [study].[dbo].Teaching

on Teacher.t_no=Teaching.t_no

join [study].[dbo].Course

on Course.course_no=Teaching.course_no

/*子循环嵌套查询*/

select t_name,course_name

from [study].[dbo].Teacher,[study].[dbo].Course

where course_name=(select course_name

                   from [study].[dbo].Teaching,[study].[dbo].Course

                   where Teaching.course_no=Course.course_no)

   11）查询所有教师的任课门数，要求显示教师姓名t_name、担任课程的门数

     （将该列定名为course_number）。

select t_name,COUNT(*) as course_number

from [study].[dbo].Teacher,[study].[dbo].Teaching

where Teacher.t_no=Teaching.t_no

group by Teaching.t_no,t_name  

   12）查询和“王蕾”是同一班级的同学的姓名。（使用子查询）

select s_name

from [study].[dbo].Student

where class_no=(select class_no

from [study].[dbo].Student

where s_name='王蕾'

) 

   13）查询没有选修“计算机基础”课程的学生姓名。（使用子查询）

select s_name

from [study].[dbo].Student

where s_no not in(select s_no,course_name

                  from [study].[dbo].Student,[study].[dbo].Course

      where course_name=(select course_name

                         from [study].[dbo].Course

         where course_name='计算机基础')

    )

   14）查询主讲“数据库原理与应用”和主讲“数据结构”的教师姓名。（用

       UNION）

select distinct teacher.t_name --100

from [study].[dbo].Teacher join [study].[dbo].Teaching

on Teacher.t_no=Teaching.t_no

join [study].[dbo].Course

on Course.course_no=Teaching.course_no

where course_name='数据库原理与应用'

union

select distinct teacher.t_name

from [study].[dbo].Teacher join [study].[dbo].Teaching

on Teacher.t_no=Teaching.t_no

join [study].[dbo].Course

on Course.course_no=Teaching.course_no

where course_name='数据结构'

1. 1. 查询讲授了“数据库原理与应用”课程的教师的姓名。

select distinct teacher.t_name

from [study].[dbo].Teacher join [study].[dbo].Teaching

on Teacher.t_no=Teaching.t_no

join [study].[dbo].Course

on Course.course_no=Teaching.course_no

where course_name='数据库原理与应用'  

5．思考回答

1. 多表查询的两种方法：连接查询、嵌套查询，在实现时如何选取，试对两种方法做比较。

多表查询有两种方法：连接查询和嵌套查询。

连接查询：连接查询指的是将两个或多个表按照某个条件进行关联，然后根据关联结果进行查询。连接查询的优点是查询结果的数据量较小，查询速度较快，而且容易理解和编写。连接查询通常在查询结果中包含多个表的列，而且通常需要使用 JOIN 关键字或者 INNER JOIN、LEFT JOIN 等关键字来实现表的关联。在多表查询中，连接查询通常使用在多个表之间存在某些关联关系的场景中。

嵌套查询：嵌套查询指的是将一个查询语句嵌入到另一个查询语句中，使得内部的查询语句的结果作为外层查询语句的条件之一。嵌套查询的优点是灵活性强，可以根据不同的情况采取不同的查询方式，而且可以实现比较复杂的查询操作。嵌套查询通常在查询结果中只包含一个表的列，而且可以使用 WHERE 子句来筛选结果。

在实现时，我们需要根据实际需求选取连接查询或嵌套查询。如果需要查询多个表之间的关联数据，我们可以使用连接查询，如果需要查询某个表中的满足一定条件的数据，可以使用嵌套查询。需要注意的是，在使用连接查询时，我们需要确定两个或多个表之间的关联关系；在使用嵌套查询时，我们需要注意内层查询语句的效率和正确性，避免出现性能瓶颈或错误的查询结果。

两种方法的比较：

1. 查询结果不同：连接查询通常可以查询多个表的数据，并将这些表的信息结合在一起，返回多个表联合后的结果集；而嵌套查询只会返回单个表的信息，查询结果不包含其他表的数据。

2. 效率不同：连接查询通常比嵌套查询执行得更快，因为连接查询只需要执行一次查询，而嵌套查询需要多次执行嵌套查询语句。

3. 灵活性不同：嵌套查询通常比连接查询更灵活，因为嵌套查询可以根据不同的查询需求动态实现查询，而连接查询需要提前明确表与表之间的关联关系。

4. 编写难度不同：连接查询的编写通常比嵌套查询更容易，因为连接查询的语法清晰简单；而嵌套查询需要嵌套多个查询语句，需要考虑多层嵌套关系，编写和调试难度较大。

1. 内连接、外连接有什么不同？左外连接、右外连接、全外连接又有什么不同？

内连接和外连接是两种常见的连接方式，用于多表查询的操作。

内连接：内连接是指根据两个或多个表之间的关联条件，将满足条件的记录进行匹配，返回匹配成功的结果集。内连接只返回两个表中满足关联条件的记录，不返回未匹配的记录。内连接使用的关键字是 INNER JOIN 或直接使用 JOIN。

外连接：外连接是指在内连接的基础上，再返回未匹配的记录。外连接可以分为左外连接、右外连接和全外连接。

左外连接（Left Outer Join）：左外连接将返回左表中的所有记录，无论是否与右表的记录匹配。如果右表中的记录无法与左表中的记录匹配，将返回 NULL 值。左外连接使用的关键字是 LEFT JOIN。

右外连接（Right Outer Join）：右外连接将返回右表中的所有记录，无论是否与左表的记录匹配。如果左表中的记录无法与右表中的记录匹配，将返回 NULL 值。右外连接使用的关键字是 RIGHT JOIN。

全外连接（Full Outer Join）：全外连接将返回左表和右表中所有的记录，无论是否匹配。如果左表或右表中的记录无法与另外一张表中的记录匹配，将返回 NULL 值。全外连接使用的关键字是 FULL JOIN 或 FULL OUTER JOIN。

总结：

- 内连接返回两个表中满足关联条件的记录。

- 左外连接返回左表中的所有记录以及与之匹配的右表记录。

- 右外连接返回右表中的所有记录以及与之匹配的左表记录。

- 全外连接返回左表和右表中的所有记录。

- 外连接可用于解决查询结果中可能存在空值或未匹配记录的情况。

1. 嵌套查询中不相关子查询、相关子查询的求解方法。

1. 不相关子查询

不相关子查询（Noncorrelated subquery）又称单独子查询，指的是在查询中嵌套了一个独立的子查询，独立子查询的执行不依赖于主查询，即独立子查询的结果在主查询中所有记录中都是固定的。它的求解方法是，首先会执行嵌套的子查询得到一个结果集，然后将结果集作为查询主语句中某个匹配条件，执行主查询，返回查询结果。

不相关子查询一般可以用 EXISTS、NOT EXISTS、IN 或 NOT IN 关键字来实现，

2. 相关子查询

相关子查询（Correlated subquery）指的是在一个查询中，子查询依赖于主查询中的表数据，且子查询语句中的条件包含了主查询中的表。相关子查询中的子查询和外部查询之间有一定的数据依赖关系。其求解方法是，首先执行主查询结果集，然后将每个结果集逐个与子查询进行匹配，返回匹配成功的行记录。

相关子查询通常可以用 EXISTS、NOT EXISTS、IN 或 NOT IN 关键字来实现，

总而言之，相关子查询和不相关子查询是两种不同的查询方式，需要根据实际情况选择合适的方式。不相关子查询适合于查询一个固定的条件，独立于主查询的结果，而相关子查询则更适合v