数据库作业七—SELECT连接查询和嵌套查询

连接查询

说通俗点，连接查询就是一种多表查询，当一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询是关系型数据库中最重要的查询。
包括：内连接、外连接、交叉连接等。
内连接形式：等职连接、自然连接和不等连接。
外连接形式：左外连接、右外连接、全外连接。
语法：

SELECT <列名1>[,<列名2>,...]
FROM <表1>,<表2>[,<表3>,...]
WHERE [<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>;

本次操作所用到的表的建立与数据的插入的代码
https://blog.csdn.net/qq_38975453/article/details/104696976

1.等值连接

SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno

--上述SELECT语句与下边的语句等效
SELECT *
FROM Student,SC
WHERE Student.Sno=SC.Sno

2.自然连接
在上张图片中，肯定注意到了，有两列Sno，在最后的结果中，重复的数据对我们无益。

--稍微修改一下
SELECT
Student.Sno,Student.Sname,Student.Ssex,Student.Sage,Student.Sdept,SC.Cno,SC.Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno

3.不等连接

SELECT 
Student.*,SC.*
FROM Student,SC
WHERE Student.Sno<>SC.Sno

等值连接与不等连接，在查找时，都是先做所查询表的笛卡尔积，形成一张大表，在根据WHERE的条件，从大表中筛选。
练习
查找选修2号课程并且成绩在86分以上的学生的姓名和学号

SELECT 
Student.Sname,Student.Sno
FROM Student,SC
WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>86

4.自身连接

--查找每一门课的先行课(Cpno)的先行课(Cpno)
SELECT
First_Course.Cno,Second_Course.Cpno
FROM Course as First_Course,Course as Second_Course
WHERE First_Course.Cpno=Second_Course.Cno

自身连接仍是多表连接，多张同一张表，仍先做笛卡尔积，在从中筛选。

外连接
Join 表名 on 关联条件
左外连接：left (outer) join 两表中完全匹配的数据，左表中特有的数据 以左表为准，没有的补null
右外连接：right (outer) join 两表中完全匹配的数据，右表中特有的数据 以右表为准，没有的补null
完全外连接：full outer join，两表中完全匹配的数据，左表中特有的数据，右表中特有的数据，没有的补null

针对外连接的形式，内连接也有一种类似形式
inner join

练习

--查询每个学生及其选修课的情况和未选课的学生的情况
--如果我们不外连接，查询出来的结果只有选了课的学生的信息，没有未选课的学生的信息
SELECT 
Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER  JOIN SC ON Student.Sno=SC.Sno

右连接与左连接一样，只是位置换了换，一个以左表为基准，一个以右表为基准，完全外连接，左右两边都是基准。

三张表的连接

--查询学生的学号，姓名，选修课的课程名和考试成绩
SELECT 
Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno

SELECT 
*
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno

嵌套查询

1 .指在一个外层查询中包含有另一个内层查询。其中外层查询称为主查询，内层查询称为子查询。
2 .SQL允许多层嵌套，由内而外地进行分析，子查询的结果作为主查询的查询条件
3 .子查询中一般不使用order by子句，只能对最终查询结果进行排序

先来一个例子：
查找与刘晨同系的学生的学号，姓名和系

--找与刘晨同系的，首先要找出刘晨是哪个系的，
--确定刘晨为哪个系后，再以此为查询条件，
--再进行查询。
SELECT
Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
           (SELECT Sdept
           FROM Student
           WHERE Sname = N'刘晨')

看了上个例子，大概对嵌套查询有了一点了解，下面接着说它。

带有IN谓词的子查询
in关键字用于where子句中用来判断查询的表达式是否在多个值的列表中。返回满足in列表中的满足条件的记录。

上边那个例子就是带着IN谓词的嵌套查询
下面来一个比上边那个复杂一点的例子

--查找选修了课程名为信息系统的学生的学号和姓名
--先根据信息系统找出课程号(Cno)，再根据课程号找出学号(Sno)，再找出学号对应的学生的姓名
SELECT Sname,Sno
FROM Student
WHERE Sno IN
          (SELECT Sno
           FROM SC
           WHERE Cno IN
                     (SELECT Cno
                      FROM Course
                      WHERE Cname = N'信息系统')
           )

其实上边那个例子也可以由连接查询实现

SELECT Sname,Student.Sno
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno 
      AND Course.Cno = SC.Cno
      AND Course.Cname = N'信息系统'

带有比较运算符的子查询
带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单个值时，可以用 >、<、=、>=、<=、!=、或<>等比较运算符。

查询年龄比李勇年龄小的学生的姓名和年龄

--先查询出李勇的年龄，再找比他年龄小的
SELECT 
Sname,Sage
FROM Student
WHERE Sage<(SELECT
            Sage
            FROM Student
            WHERE Sname=N'李勇'
			)

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

子查询返回单值时可以用比较运算符，但返回多值时要用ANY（有的系统用SOME）或ALL谓词修饰符。而使用ANY或ALL谓词时则必须同时使用比较运算符

查找非计科系比计科系任意一个学生年龄小的学生的姓名和年龄

SELECT Sname,Sage
FROM Student
WHERE Sdept <> 'CS' AND
      Sage < ANY(SELECT Sage
                 FROM Student
                 WHERE Sdept = 'CS')

计科系学生的年龄

查找非计科系中比计科系所有学生年龄小的学生姓名和年龄

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'

在此把ANY、ALL与聚合函数的对应关系总结一下