［SqlSever2012］多表查询

要学习多表查询的知识，需要以下知识点作为基础：

sql语言实现数据库和基本表的建立

数据更新和简单查询

多表查询的知识点：

内连接：

在每个表中找出符合条件的共有记录，系统会自动忽略两个表中对应不起来的数据。

[x inner join y on...] 

举例：查询student表中的所有信息和学生的成绩score

select *
from student  inner join choice 
on student.s_no=choice.s_no

左外连接： 

根据左表的记录，在被连接的右表中找出符合条件的记录与之匹配，如果找不到与左表匹配的，用null表示。

[x left [outer] join y on...

举例：查询student表中的所有信息和学生的成绩score

select *
from student  left outer  join choice 
on student.s_no=choice.s_no

 右外连接：

根据右表的记录，在被连接的左表中找出符合条件的记录与之匹配，如果找不到匹配的，用null填充。

[x right [outer] join y on...] 

select *
from student   right outer  join choice
on student.s_no=choice.s_no

 关键词：

• all-------全部
• MIN------等价于>ANY
• MAX-----等价于>ALL
• IN--------等价于=ANY
• <>ALL--等价于NOT IN

相关子查询和不相关子查询的辨析：

1.相关子查询：

依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。（子查询执行多次）

2.不相关子查询：

独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。 （子查询执行一次）

举例子：

--查询讲授课号为‘02001’的教师号t_no，教师名t_name，职称t_title
--相关子查询
select distinct t_no,t_name,t_title
from Teacher
where ('02001'<>ALL (select course_no
                     from teaching
                     where t.no=teaching.t_no)
--不相关子查询
select distinct t_no,t_name,t_title
from Teacher
where course_no not in(select course_no
                       from teaching
                       where course_no='02001')

查询题目：

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

select student.s_no,s_name,course_no,score
from student,choice
where (student.s_no=choice.s_no)

--2查询所有同学的选课及成绩情况，要求显示学生的姓名s_name、课程名
--称course_ name、课程的成绩score，并将查询结果存放到一个新的数据 
--表new_table中。（SELECT…INTO）

select s_name,course_name,score
into new_table
from student,choice,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 student,course,choice,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,student.s_name,sum(course_score) as total_score
from student,course,choice
where (student.s_no=choice.s_no)
and (course.course_no=choice.course_no)
and score>=60
group by student.s_no,s_name

--5查询所有同学的平均成绩及选课门数，要求显示学生的学号s_no、姓名
--s_name、平均成绩（将该列定名为average_score）、选课的门数（将该
--列定名为：choice_num）

select student.s_no,s_name,AVG(score) as average_score,COUNT(*) as choice_num
from student,choice
where student.s_no=choice.s_no
group by student.s_no,s_name

--6查询所有选修了课程但未参加考试的所有同学及相应的课程，要求显示学
--生的学号s_no、姓名s_name、课程号course_no、课程名称course_name

select student.s_no,s_name,course.course_no,course_name
from student,choice,course
where (student.s_no=choice.s_no)
and (course.course_no=choice.course_no)
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 student,choice,course
where (student.s_no=choice.s_no)
and (course.course_no=choice.course_no)
and score<60

--8查询选修了课程名为“程序设计语言”的所有同学及成绩情况，要求显示
--学生的姓名s_name、课程的成绩score。
--方法一

select s_name,score
from student,choice,course
where student.s_no=choice.s_no
and course.course_no=choice.course_no
and course_name  = '程序设计语言'

--8方法二

select s_name,score
from student,choice
where student.s_no=choice.s_no
and choice.course_no =any 
                     (select course_no
                      from course
                      where 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 student,class,course,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 teacher,teaching,course
where teacher.t_no=teaching.t_no
and course.course_no=teaching.course_no

--11查询所有教师的任课门数，要求显示教师姓名t_name、担任课程的门数
--（将该列定名为course_number）。

select t_name,COUNT(*) as course_number
from teacher,teaching
where teacher.t_no=teaching.t_no
group by teaching.t_no,t_name  

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

select s_name,class.class_no
from student,class
where class.class_no in (select class.class_no
                         from student,class
                         where s_name='王蕾'
                         and student.class_no =class.class_no)
and class.class_no=student.class_no   

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

select s_name 
from student,course,choice
where (course.course_no=choice.course_no)
and (student.s_no=choice.s_no)        
and course_name !='计算机基础'  

--方法二       

select s_name
from student
where student.s_no not in(select student.s_no
                            from student,choice,course
                            where student.s_no=choice.s_no
                            and choice.course_no=course.course_no
                            and course_name='计算机基础')

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

select distinct Teacher.t_name
from teacher,teaching,course
where teaching.t_no=teacher.t_no
and course.course_no=teaching.course_no
--and course_name in ('数据库原理与应用' ,'数据结构')

--方法二

select distinct teacher.t_name 
from teacher,teaching,course
where teaching.t_no=teacher.t_no
and course.course_no=teaching.course_no
and course_name ='数据库原理与应用' 
union
select distinct teacher.t_name 
from teacher,teaching,course
where teaching.t_no=teacher.t_no
and course.course_no=teaching.course_no
and course_name ='数据结构' 

--15查询讲授了“数据库原理与应用”课程的教师的姓名。
--方法一

select distinct teacher.t_name 
from teacher,teaching,course
where teaching.t_no=teacher.t_no
and course.course_no=teaching.course_no
and course_name ='数据库原理与应用' 

--方法二

select distinct teacher.t_name 
from teacher,teaching,course
where teaching.t_no=teacher.t_no
and course.course_no=teaching.course_no
and course_name ='数据库原理与应用'