SQL Server——从入门到放弃（4）--SELECT（连接查询、嵌套查询）

从这次练习开始，就进入了多表查询部分。多表查询，顾名思义，就是——不是一个表😜
既然是查询，就离不开一个单词——SELECT（没错，竟然，果然，依然是它）
多表查询现在开始

连接查询： 同时涉及两个以上的表的查询
基本格式：[<表名1> . ]<列名1> <比较运算符>[<表名2> . ]<列名2>
比较运算符主要有：=,>,<,>=,<=,!=(或<>)等

等值于非等值连接查询
等值连接：连接运算符为 =
使用其他运算符时为非等值连接
【例3.49】查询每个学生及其选修课程的情况

select Student.*,SC.*
from Student,SC
where Student.Sno=SC.Sno

连接操作的执行过程：1.嵌套循环法。2.排序合并法。3.索引连接

在等值连接中把目标列中重复的属性列去掉则为自然连接

【例3.50】对【例3.49】用自然连接完成

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student,SC
where Student.Sno=SC.Sno

一条SQL语句可以同时完成选择和连接查询
【例3.51】查询选修2号课程且成绩在85分以上的所有学生和姓名

select Student.Sno,Sname
from Student,SC
where Student.Sno=SC.Sno and SC.Cno='2' and SC.Grade>85

自身连接： 一个表与其自己进行连接

• 需要给表起别名以示区别
• 所有属性名都是同名属性，因此必须使用别名

【例3.52】查询每一门课的间接先修课（即先修课的先修课）

select first.Cno,second.Cpno
from Course first,Course second
where first.Cpno=second.Cno

理想效果

实际运行结果：

原因：标准SQL不接受显示空值，而T-SQL接受并可以显示空值。注意，考试答题时需要按照标准SQL的结果来书写。

外连接： 普通连接操作只输出满足连接条件的元组，而外连接 操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出。
左外连接：列出左边关系中所有的元组
右外连接：列出右边关系中所有的元组

【例3.53】改写【例3.49】

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student left out join SC on (Student.Sno=SC.Sno)
/*T-SQL中应为outer,而不是out*/

多表连接： 两个以上的表进行连接

【例3.54】查询每个学生的学号、姓名、选修的课程名及成绩

select Student.Sno,Sname,Cname,Grade
from Student,SC,Course  /*多表连接*/
where Student.Sno=SC.Sno
and SC.Cno=Course.Cno

嵌套查询：

• 一个SELECT-FROM-WHERE语句称为一个查询块
• 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询
子查询中不能使用ORDER BY子句

相关/不相关子查询
不相关子查询：子查询的查询条件不依赖于父查询
相关查询：子查询的查询条件依赖于父查询

带有IN谓词的子查询
【例3.55】查询与“刘晨”在同一个系学习的学生
第一种方式：

/*1.确定刘晨所在系名*/
select Sdept
from Student
where Sname='刘晨'
/*结果为：CS*/

/*2.查询所有在CS系学习的学生*/
select Sno,Sname,Sdept
from Student
where Sdept='CS'

第二种方式：

select Sno,Sname,Sdept
from Student
where Sdept in
            (
              select Sdept
              from Student
              where Sname='刘晨'
             )

当用户确定内层查询返回是单个值 时，可以用比较运算符。上例中可以用=代替IN

第三种方式：

select s1.Sno,s1.Sname,s1.Sdept
from Student s1,Student s2
where s1.Sdept=s2.Sdept and
      s2.Sname='刘晨'

运行结果都为

【例3.56】查询选修了课程名为“信息系统”的学生学号和姓名

解法一：

select Sno,Sname
from Student 
where Sno in (
           select Sno 
		   from SC
		   where Cno in (
		              select Cno
					  from Course
					  where Cname='信息系统'
					  )
				)

解法二：

select Student.Sno,Sname
from Student,SC,Course
where Student.Sno=SC.Sno
and   SC.Cno=Course.Cno
and   Course.Cname='信息系统'

带有比较运算符的子查询

【例3.57】找出每个学生超过他选修课程平均成绩的课程号

select Sno,Cno
from SC x
where Grade>=(select avg(Grade)
                   from SC y
				   where y.Sno=x.Sno)

x是表SC的别名，又称为元组变量，可以用来表示SC的一个元组。

带有ANY(SOME)或ALL谓词的子查询

【例3.58】查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

解法一：

select Sname,Sage
from Student
where Sage<any(select Sage
                   from Student
				   where Sdept='CS')
and Sdept<>'CS'

解法二：

select Sname,Sage
from Student
where Sage<
                (select max(Sage)
                   from Student
				   where Sdept='CS')
and Sdept<>'CS'

【例3.59】查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄
方法一：用ALL谓词

select Sname,Sage
from Student
where Sage<all
                (select Sage
                   from Student
				   where Sdept='CS')
and Sdept<>'CS'

方法二：用聚集函数

select Sname,Sage
from Student
where Sage<
                (select min(Sage)
                   from Student
				   where Sdept='CS')
and Sdept<>'CS'

两次运行结果相同


今天的练习到这里就结束了。

都看到这里了，就点个赞再走呗。