连接查询（等值与非等值连接查询、自身连接、外连接）、嵌套查询（不相关子查询、相关子查询、嵌套查询的格式:（3种：1、使用IN/NOT IN引入的子查询））

连接查询

        连接是一个操作，允许查询两个或两个以上的表(或视图)，形成单个结果集，其中包括来自每个表的行和列。

        有两种连接，分别是内部连接、外部连接。

        内部连接又包括等值连接（=）、自然连接、非等值连接（除了=以外的连接）等;

        外部连接包括左外部连接、右外部连接等。

      1、等值与非等值连接查询（内连接）

          用来连接两个表的条件称为连接条件或连接谓词，一般格式:

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

        
         比较运算符: =（等值连接）、>、>=、<、<=、!=(<>)
        当连接运算符为“=”时，称为等值连接，使用其它运算符称为非等值连接。

        连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同(自然连接除外（自然连接名字必须相同）)。

        例1:

        两个表实现笛卡尔集。

SELECT * FROM student,course;
或
SELECT*FROM student CROSS JOIN course

        例2:

        查询每个学生及其选修课程的情况。

SELECT student.* sc.* 
/* student.*是把student表中的所有列放到结果表中，sc.*是把sc表中的所有列放到结果表中 */
FROM student,sc
WHERE student.Sno =sc.sno;
/* select from where 是传统的格式 */
或
SELECT student.*,SC.*
FROM student INNER JOIN SC ON student.sno = sc.sno;
 /* 这里是等号，所以是等值连接（内连接） */
 /* INNER JOIN 表示的是内连接，这个是现代的格式 */
两种方式结果和过程都一样！

分析执行过程(教材p100)。

注：上面代码把两个表的列都加入结果了，一般不会这样做，应该明确指出连接哪些列。

 

SELECT student.sno,sname,cno,grade
/* 因为sno可以来自 student 表也可以来自 sc 表，所以需要加上前缀student或sc */
FROM student INNER JOIN sc ON student.sno = sc.sno
/*此方法也实现了自然连接(去掉了重复属性)*/

        例3:

        查询学生的学号，姓名，选课课号，成绩。

        例4:
        查询学生的学号(可以来自student表也可以来自sc表)，姓名（student表），选课名（course表），成绩(sc表)。（三个表作内连接）
 

SELECT student.sno,sname,cname，grade
FROM student INNER JOIN sc ON student.Sno=sc.sno INNER
JOIN course ON sc.cno = course.cno;
或
SELECT student.sno,sname,cname, grade
FROM student,sc,course
Where student.Sno=sc.sno and sc.cno=course.cno;

        2、自身连接（内连接，自身连接的过程中，表名是一样的，所以需要将表起别名）

        连接操作不仅可以在两个表之间进行，也可以是一个表与其自身进行连接，称为表的自身连接。
        例:

        查询每一门课的间接先修课(即先修课的先修课，如：学1号课程需要先修5号课程，学5号课程需要先修7号课程)。
        分析:要找一门课的先修课的先修课，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程，这就要将 course表与其自身连接。(即：先将两个表做笛卡尔积，然后筛选出1号表的先修课等于2号表的后修课)

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST , Course SECOND
/* 自身连接的过程中，表名是一样的，所以需要将表起别名 */
WHERE FIRST.Cpno=SECOND.Cno;

        练习：
        1、查询课程号，课程名，先修课程程号，先修课程名
        2、查询学生201215121选修课程的总学分
        3、统计每个学生的选修课程门数和总学分，以学号、门数和总学分作为结果列

        1、这里注意给自身表格命名（a , b），以便区分，还有查询的时候也要使用表格名区分

select a.cno,a.cname,b.cno,b.cname
from course a,course b
where a.cpno=b.cno
或
select a.cno,a.cname,b.cno,b.cname
from course a inner join course b on a.cpno=b.cno

        2、注意sum的用法和字符串不要抄错

select sum(ccredit) from  course,sc
where sc.sno='201215121'and sc.cno=course.cno;
或
select sum(ccredit) 总和 from sc inner join course on sc.cno=course.cno
where sno='201215121';

        3、思路：

                （1）将sc表和course表在课程号列上进行自然连接

                （2）按学号进行分组，在组内进行聚合（求总学分和选课门数）

select sno 学号,count(*) 门数,sum(ccredit) 总学分
/* count(*)统计组内元组条数，sum(ccredit)对组内学分进行求和 */
from sc,course
/* 来自 sc 表和 course 表 */
where sc.cno=course.cno
/* 两个表进行自然连接 */
group by sno;
/* 按学号进行分组 */

 3、外连接

        外部联接除返回匹配联接条件的绑定行外，还返回来自某个表的不匹配行。

        如果包含左表的不匹配行，这种联接称为左外部联接:

        如果包含右表的不匹配行，这种联接称为右外部联接:

        如果包含左右两个表的不匹配行，这种联接称为完整外部联接。

        这些不匹配行对应另一个表中的列将被设置为空值(不授原字段是否为空限制)。

        注意：外部连接可以用在年终统计所有产品销售情况的时候（包括销售件数为 0 的情况），使用内连接不会统计销售件数为 0 的情况
        例:查询每个学生及其选修课程的情况。

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);
/* student表为结果的左表，左外连接到结果集 */
/* 满足 student.sno = sc.sno 条件的结果加入到结果集中 */
/* 因为是做外部连接，所以会把没有满足条件的student的列加入到结果集中
   且student表中没有sc表中的列均取空值 */
/* left outer join 外部连接 在left outer join 的左侧称为左外部连接*/

嵌套查询（将复杂的查询分解成一系列的步骤，并使用单一的语句解决子问题）

        概念:

        在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。

        将一个查询块嵌套在另一个SQL语句的WHERE子句或HAVING短语的条件中的查询称为嵌套查询（两层查询）。上层的查询块称为外部查询或父查询，下层查询块称为内层查询或子查询。

        子查询是一个查询块，嵌套在SELECT、INSERT、UPDATE和DELETE语句中或另一个子查询内部，返回单个值或单列值。

       不相关子查询:

         子查询的查询条件不依赖于父查询。是由里向外逐层处理。

         即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

       相关子查询:

        子查询的查询条件依赖于父查询。

        首先取外层查询（父查询）中表的第一个元组，根据它与内层查询相关的属性值处理内层有
询，若WHERE子句返回值为真，则取此元组放入结果表;然后再取外层表的下一个元组;重复这一过程，直至外层表全部检查完为1

      何时使用子查询:

        使用子查询将复杂的查询分解成一系列逻辑步骤，并使用单一的语句解决问题。当查询依赖于另一个查询的经果时，子查询很有用。 

      嵌套查询的格式:（3种）

        包括子查询的语句通常采用以下格式中的一种:
       （1） WHERE expression [NOT] IN (subquery)

                通过IN引入的列表上进行操作。（子查询（subquery）需要使用小括号括起来）
       （2） WHERE expression comparison_operator[ANY|ALL](subquery)
                通过比较运算符引入，并且必须返回单个值（加了any和all就可以返回多个值）。
       （3）WHERENOTEXISTS(subquery)

                通过EXISTS引入的存在测试。

        使用子查询的准则:

        （1）必须将子查询放置在小括号中。

        （2）在返回数值的子查询选择列表（select后面）中只使用一个表达式或列名

        （3）不能在包含文本和图像数据类型的列上使用子查询。

        （4）根据需要，可以使用多级子查询，没有限制（一般嵌套2-3层即可，多了不好）。

        （5）子查询不能使用ORDER BY子句（对查询的结果按关键字排序）。 

      1、使用IN/NOT IN引入的子查询

        此子查询返回的结果是值列表。用来判断某个值是否在子查询的结果集中。

        基本语法为:  WHERE expression [NOT] IN (subquery)

        注：（subquery）处可以返回多个值

        例：

        查询计算机系学生的学号，课程号，成绩信息。

select sno,cno,grade
from sc
where sno in (select sno from student where sdept='CS');
/* 会先执行in 里面的语句，先筛选除计算机系的学生，再查询学号等信息 */

        步骤：

        ①查询计算机系学生的学号(201215121，201215122)

        ②查询这两名学生的学号，课程号，成绩信息

        此查询为不相关子查询，用自身连接也能完成查询要求。

        例：

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

select sno,sname 
/* 3、最后在Student关系中取出 Sno 和 Sname */
from student
where sno in
	(
	select sno
    /* 2、然后在SC关系中找出选修了3号课程的学生学号 */
	from sc
	where cno in
		(
		select cno
		from course
		where cname='信息系统' 
        /*1、首先在Course关系中找出“信息系统”的课程号，结果为3号*/
		)
	);