Oracle数据库学习笔记6——PL/SQL高级查询-连接查询

连接查询

在关系数据库管理系统中，经常把一个实体的信息存储在一个表里，当查询相关数据时，通过连接运算就可以查询存放在多个表中不同实体的信息，把多个表按照一定的关系连接起来，在用户看来好像是查询一个表一样。

使用连接谓词指定的连接

连接条件由比较运算符在WHERE子句中给出

语法格式：

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

说明：
在连接谓词表示形式中，FROM子句指定需要连接的多个表的表名，WHERE子句指定连接条件，比较运算符有：<、<=、=、>、>=、!=、<>、!<、!>。
由于连接多个表存在公共列，为了区分是哪个表中的列，引入表名前缀指定连接列。例如，student.sno表示student表的sno列，score.sno表示score表的sno列。为了简化输入，SQL允许在查询中使用表的别名，可在FROM子句中为表定义别名，然后在查询中引用。
经常用到的连接有等值连接、自然连接和自连接等

等值连接：两个有相同数据类型的就可以连接，不要求意义；
自然连接：不仅要求有相同数据类型，还要求要有相同的意义，正是因为有相同的意义，所以两个相同的字段之中就会去掉一个重复的，而等值连接不会去掉重复值

自然连接就是一种等值连接

1）等值连接— =

表之间通过比较运算符“=”连接起来，称为等值连接，举例如下。

原本连接得到的是两个表的笛卡尔积，即第一个表的第一条记录+第二个表的第一条记录、第一个表的第一条记录+第二个表的第二条记录……以此类推，而等值连接多了一个要求，要两个记录相等，不相等的话就不取（即如果第一个表的第一条记录和第二个表的第一条记录不相等，就不要，第一个表的第一条记录和第二个表的第二条记录相等，就要），由此，等值连接得到的是两个表笛卡尔积的子集

【例】查询学生的情况和选修课程的情况。

SELECT student.*, score.*
  FROM student, score
  WHERE student.sno=score.sno;

该语句采用等值连接。

2）自然连接

如果在目标列中去除相同的字段名，称为自然连接，以下例题为自然连接。

【例1】对上例进行自然连接查询。

SELECT student.*, score.cno, score.grade
  FROM student, score
  WHERE student.sno=score.sno;

【例2】查询选修了“数字电路”且成绩在80分以上的学生姓名。

SELECT a.sno, a.sname, b.cname, c.grade
  FROM student a, course b, score c
  WHERE a.sno=c.sno AND b.cno=c.cno AND b.cname='数字电路' AND c.grade>=80;

（三表连接，是两两连接就可以了：第一个表和第二个表连接，然后第一个表和第二个表的其中一个和第三个表连接。所以三表连接有两个=就可以了，以此类推，四表连接就有三个等值）

3）自连接

将同一个表进行连接，称为自连接，举例如下。

（要重点看的）【例】查询选修了“1201”课程的成绩高于学号为“121002”的成绩的学生姓名。

SELECT a.cno, a.sno, a.grade
  FROM score a, score b
  WHERE a.cno='1201' AND a.grade>b.grade AND b.sno='121002' AND b.cno='1201'
  ORDER BY a.grade DESC;

使用JOIN关键字指定的连接

语法格式：

<表名> <连接类型> <表名> ON <条件表达式>
| <表名> CROSS JOIN <表名>
| <连接表>

其中，<连接类型>的格式为：

<连接类型>::=
      [ INNER | { LEFT | RIGHT | FULL } [ OUTER ] CROSS JOIN

说明：

在以JOIN关键字指定连接的表示方式中,在FROM子句中用JOIN关键字指定连接的多个表的表名,用ON子句指定连接条件。
在连接类型中，INNER表示内连接，OUTER表示外连接，CROSS表示交叉连接，这是 JOIN关键字指定的连接的3种类型。

1）内连接

内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。
内连接是系统默认的，可省略INNER关键字。

内连接实际上就是一个等值连接

【例1】查询学生的情况和选修课程的情况。

SELECT *
  FROM student INNER JOIN score ON student.sno=score.sno;

该语句采用内连接

【例2】查询选修了数据库系统课程且成绩在84分以上的学生情况。

SELECT a.sno, a.sname, c.cname, b.grade
  FROM student a JOIN score b ON a.sno=b.sno JOIN course c ON b.cno=c.cno
  WHERE c.cname='数据库系统' AND b.grade>=84;

该语句采用内连接,省略INNER关键字,使用了WHERE子句。

2）外连接

在内连接的结果表，只有满足连接条件的行才能作为结果输出。外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接有以下3种：

• 左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括左表的所有行；（左边这个表的所有值都要显示）
• 右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行；（右边这个表的所有值都要显示）
• 完全外连接（FULL OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。（左右两边的表的所有值都要显示）

【例1】采用左外连接查询教师任课情况。

SELECT teacher.tname, course.cname
  FROM teacher LEFT JOIN course ON (teacher.tno=course.tno);

该语句采用左外连接。

（左边表teacher的内容都要显示，右边表course没有的就为空，ON后面是连接条件）

【例2】采用右外连接查询教师任课情况。

SELECT teacher.tname, course.cname
  FROM teacher RIGHT JOIN course ON (teacher.tno=course.tno);

该语句采用右外连接。（要显示course表（右表）的所有记录）

【例3】采用全外连接查询教师任课情况。

SELECT teacher.tname, course.cname
  FROM teacher FULL JOIN course ON (teacher.tno=course.tno);

该语句采用全外连接。

这三种连接常用于报表和统计

3）交叉连接

返回被连接的两个表所有数据行的笛卡尔积

【例】采用交叉连接查询教师和课程所有可能组合。

SELECT teacher.tname,course.cname
  FROM teacher CROSS JOIN course;

该语句采用交叉连接。

交叉连接用在一些统计分析
或者数据仓库（通过一个很大的数据来获取一些有价值的信息），做了一个很大的全集，从中抽取一些满足某些条件的数据。有点像查询，但是这是先设置好一个全集，然后可以任意改变条件抽取符合的信息