SQL多表查询

多表连接查询（谓词连接，外连接）

谓词连接（等值连接和不等连接）

用连接谓词进行连接分两种情况，当连接谓词是“=”时的连接，称为等值连接。当连接谓词是不等运算符时的连接，称为不等连接。

【举例】查询学生的基本信息和成绩信息。

SELECT  a.* , b.*
FROM  student a, score b
WHERE   a.id=b.student_id

自然连接(少用)

简单的说就是两个表有两个一模一样的属性关联时不用别名关联 去掉重复列
自然连接(Natural join)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。

在针对多表进行查询时。如果所引用的列为被查询的表所共有，则引用列时必须用表名来指定列是来源于哪个表，指定方法为：表名.列名。反之，不一定需要表名来指定来源。

【举例】查询学生的基本信息和成绩信息。

等值连接：
SELECT  *  FROM  student a, score b
WHERE  a.student_id = b.student_id
自然连接：
select * from tab_student a natural join tab_score b;

复合条件连接

简单说就是多个表进行关联时用and隔开，至少有n-1中关联（n代表表数），否则会出现笛卡尔积问题
在多表连接查询时含有多个连接条件称为复合条件连接。

【举例】查询学生的学号、姓名、所学课程的名称和成绩信息。

SELECT    a.student_id,student_name,course_name,grade
FROM  student a, score b, course c
WHERE  a.student_id = b.student_id
         AND  b.course_id = c.course_id

自连接

简单讲就是自己跟自己关联

一个表与自身进行连接称为自连接。
在同一个表中查找具有相同字段值的行，则可以使用自连接。在使用自连接时需要为表指定两个别名，且对所有引用的列均要用别名来指定其来源。
【举例】查找同一课程成绩相同的学生的学号、课程号和成绩。

SELECT  a.student_id,b.student_id,a.course_id,a.grade
FROM   score  a, score  b
WHERE   a.grade=b.grade
                AND a.student_id<>b.student_id
	      AND  a.course_id=b.course_id

以JOIN关键字连接

以JOIN关键字来连接表的方式，增强了表的连接能力和连接的灵活性。 使用JOIN关键字连接表的的基本格式为：

SELECT <输出字段列表> FROM 表名1 <连接类型> 表名2 ON <连接条件>
[ <连接类型> 表名3 ON <连接条件>]…

参数说明：
1）表名1，表名2，表名3等用来指明需要连接的表。
2）连接类型有：[ INNER | { LEFT | RIGHE | FULL } OUTER ] JOIN。其中INNER JOIN表示内连接；OUTER JOIN表示外连接，外连接又分：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。
3）ON：用来指明连接条件。

内连接（INNER JOIN）

谓词连接就是内连接 INNER可以省略
等值连接、不等连接和自然连接属于内连接。以JOIN关键字来实现内连接——按照ON所指定的连接条件合并两个表，返回满足条件的行。
【举例】查询学生的基本信息和成绩信息

SELECT  a.*,b.*,c.*
FROM  student a INNER  JOIN  score  b
      ON  a.student_id = b.student_id INNER JOIN grade c ON c.student_id

在内连接中，INNER可以省略，使用内连接后仍然可以使用WHERE子句对连接后的记录进行筛选。

外连接(OUTER JOIN)

外连接返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。

（1）左外连接
查询输出结果中显示符合条件的数据行及左边数据表中不符合条件的数据行。左外连接的连接格式为：

FROM 左表名 LEFT [OUTER] JOIN 右表名 ON 连接条件
其他的类似 RIGHT FULL 跟内连接一样方式扩展外部连接
举例全外连接 以两边为基准不匹配的也要显示出来

--查询各个部门的员工信息，包括无部门的员工、无员工的部门 全外连接 以两边为基准不匹配的也要显示出来
select  e.*, d.department_name
from dep d full outer join emp e--outer 可省略
on e.department_id = d.department_id;

--查询各个部门的员工信息，包括无部门的员工、无员工的部门 左外连接 以左边为基准不匹配的也好显示出来
select  e.*, d.department_name
from dep d left outer join emp e--outer 可省略
on e.department_id = d.department_id;

--查询各个部门的员工信息，包括无部门的员工、无员工的部门 右外连接 以右边为基准不匹配的也好显示出来
select  e.*, d.department_name
from dep d right outer join emp e--outer 可省略
on e.department_id = d.department_id;

外连接比起谓词连接写起来更加的复杂一些，Oracle提供了谓词的写法

--oracle +写给谁以另一边的为基准  以下是左外(无全外连接)
select  e.*, d.department_name
from emp e,dep d
where e.department_id = d.department_id(+);

交叉连接

交叉连接也叫非限制连接，它将两个表不加任何约束地组合起来。在数学上，就是两个表的笛卡尔积。交叉连接后得到的结果集的行数是两个被连接表的行数的乘积。
【举例】查询学生的基本信息及其成绩信息（使用交叉连接）。

SELECT  * FROM  student  CROSS  JOIN  score
SELECT  * FROM  student , score

嵌套子查询

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句中的WHERE 或 HAVING 子句内，或嵌套在其他子查询中的SELECT查询。

子查询可以嵌套，它能将比较复杂的查询分解为几个简单的查询。一个SELECT—FROM—WHERE语句称为一个查询块。将一个查询块嵌套在另一个WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

嵌套子查询：

嵌套查询的执行顺序是：首先执行最底层的内部查询（即子查询），它的查询结果并不显示，而是传递给外层查询，用来做外外部查询的查询条件。即按照由里向外的顺序执行。

子查询的使用 ：
可以使用子查询的地方有：
●在使用 IN 或NOT IN的集合查询中。
●在UPDATE、DELETE和INSERT语句中。
●在使用比较运算符时
●使用 ANY 或 ALL时。
●使用 EXISTS或NOT EXISTS 引入的存在测试时。
●在有表达式的地方。

带谓词IN或NOT IN的子查询

IN子查询是把子查询的结果做为外部查询的条件。判断外部查询中的某个值是否属于子查询的结果集合。

格式：<表达式> [NOT] IN （子查询）

【举例】查找选修了1002号课程的学生的学号、姓名和班级编号。

SELECT  id, name, class
FROM   student
WHERE  id   IN
     (SELECT  id  
      FROM    score
      WHERE   course_id=‘1002’)

带有比较运算符的子查询

当子查询返回的是单值时，子查询可以由一个比较运算符（ =、<、<=、>、>=、!=或<>）引入。当子查询可能返回多个值时则应把比较运算符与ALL和ANY 结合使用。

格式为：表达式 { 比较运算符 } { ALL|ANY } (子查询)

ALL：表示表达式要与子查询的结果集中的所有值进行比较，当表达式与每个值都满足比较关系时，才返回TRUE，否则返回FALSE。

ANY：表示表达式只要与子查询的结果集中的某个值满足比较关系时，就返回TRUE，否则返回FALSE。

【举例】查询选修了1001号课程且成绩比0801103号学生1001号课程成绩高的学生的学号、课程编号和成绩。

SELECT  student_id,  course_id,  grade
FROM  score
WHERE  course_id='1001'
      AND  grade > (SELECT  grade
                    FROM  score
                    WHERE  student_id='0801103'
                            AND  course_id='1001')

INSERT、DELETE和UPDATE语句中的子查询

子查询可以嵌套在INSERT、DELETE和UPDATE语句中，把子查询的结果插入到新表中或用来设置删除和修改记录的条件。

带子查询的插入操作

INSERT和SELECT语句结合起来，可以往指定的表中插入批量的记录。带子查询的插入操作的格式为：

INSERT [INTO] <表名>[（<字段1>[，<字段2>…]）]
SELECT [（<字段A>[，<字段B>…]）]
FROM <表名>
[WHERE<条件表达式>]
举例子

insert into emp2
select e.employee_id,e.name,e.salary
from emp e
where e.department_id=60;

注意插入属性与查询属性位置一致

带子查询的修改操作

子查询与UPDATE嵌套，子查询用来指定修改的条件。
【举例】将08011班学生成绩增加5分。

UPDATE  score
 SET  grade=grade+5
 WHERE  student_id  in
       (SELECT  student_id
        FROM student
        WHERE  class_id='08011')

带子查询的删除操作

子查询与DELETE嵌套，子查询用来指定删除的条件。
【举例】删除没有选修1001号课程的学生记录。

 DELETE  student
WHERE  student_id  not  in
        (SELECT  student_id
         FROM  score
         WHERE         
         course_id='1001' )

相关子查询

允许引用外部查询中的任何表的子查询称为相关子查询
相关子查询在执行时，要使用到外部查询的数据。外部查询首先选择数据提供给子查询，然后子查询对数据进行比较，执行结束后再将查询结果返回到它的外部查询中 。

相关子查询通常使用关系运算符与逻辑运算符（EXISTS、AND、SOME、ANY、ALL）。

EXISTS 代表存在。带有EXISTS量词的子查询不返回任何实际数据，它只产生逻辑真值TRUE或逻辑假值FALSE。若子查询结果非空，则外层的WHERE子句返回真值，否则返回假值。

EXISTS也与NOT结合使用，即NOT EXISTS，其返回值与EXISTS刚好相反。
格式为：[NOT] EXISTS （子查询）

举例】查询1001号课程的成绩小于该课程平均成绩的学生的学号和成绩。

SELECT student_id, grade
FROM  score  a
WHERE  grade<( SELECT  AVG(grade)
                 FROM  score  b
                 WHERE  a.course_id=b.course_id
                         AND course_id= '1001'
                 )