数据库——SQL数据连接查询

 

连接查询

查询结果或条件涉及多个表的查询称为连接查询
SQL中连接查询的主要类型
     广义笛卡尔积
     等值连接(含自然连接)
     自身连接查询
     外连接查询

 一、广义笛卡尔积

不带连接谓词的连接
 很少使用
例：
       

SELECT   *
FROM     Student, SC

 二、等值与非等值连接查询

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

用WHERE子句指定连接条件：

SELECT  *
FROM     Student，SC
WHERE  Student.Sno = SC.Sno；

结果表
 Student.Sno  Sname Ssex Sage Sdept    SC.Sno     Cno   Grade
    95001         李勇       男       20     CS    95001       1        92   
    95001         李勇       男       20     CS    95001       2        85   
    95001         李勇       男       20     CS    95001       3        88   
    95002         刘晨       女       19     IS      95002       2        90   
    95002         刘晨       女       19     IS      95002       3        80   
 

或，用FROM子句指定连接条件：

SELECT  *
FROM  Student JOIN SC  ON   Student.Sno=SC.Sno；

不建议使用JOIN方式。

前例是一种等值连接
等值连接:  连接运算符为 = 的连接操作
     

SELECT  *    
FROM     Student，SC
WHERE  Student.Sno = SC.Sno；

引用两表中同名属性时，必须加表名前缀区分。

将等值连接的目标列中重复的属性列去掉，就是自然连接。

[例33]  对[例32]用自然连接完成。

 SELECT  Student.Sno，Sname，Ssex，Sage， Sdept，Cno，Grade
 FROM     Student，SC
 WHERE  Student.Sno = SC.Sno；

带有选择条件的连接查询

[例补充]查询选1号课的学生号， 学生名，成绩

SELECT  Student.Sno，Sname，Grade
FROM     Student，SC
WHERE  Student.Sno = SC.Sno /* 连接条件*/
AND Cno=1；/* 选择条件 */

或：

 SELECT  Student.Sno，Sname，Grade
 FROM  Student JOIN SC  ON Student.Sno=SC.Sno；
  WHERE  Cno=1;

[例35]查询选修2号课程且成绩在90分以上的所有学生的学号、姓名

SELECT Student.Sno, Student.Sname
FROM    Student, SC
WHERE Student.Sno = SC.Sno   /* 连接条件*/
           AND   Cno= 2              /* 选择条件 */
      AND   Grade > 90；       /* 选择条件 */

  三、自身连接

一个表与其自己进行连接，称为表的自身连接

[例34]  查询每一门课的先行课名

SELECT  First.Cname  课名, Second.Cname  先行课名
FROM  Course  First，course  Second
WHERE First.Cpno = Second.Cno； 

        课名         先行课名
        数据库        数据结构
        信息系统    数据库
            。。。。。。。

 自身连接注意问题：

1.需要给表起别名以示区别
2.由于所有属性名都是同名属性，因此必须使用别名前缀

四、外连接（Outer Join）

普通连接操作只输出满足连接条件的元组

SELECT    *   FROM    Student，SC    
 WHERE  Student.Sno = SC.Sno；

只输出满足Student.Sno = SC.Sno的行
外连接操作可以将指定表中不满足连接条件的元组一并输出

[例 33] 查询每个学生及其选修课程的情况
(包括没有选修课程的学生)
 ---用外连接操作

SELECT  Student.Sno，Sname，Ssex，  Sage，Sdept，Cno，Grade
FROM    Student，SC
WHERE  Student.Sno *= SC.Sno；

查询结果：

Student.Sno   Sname   Ssex    Sage      Sdept      Cno    Grade
     95001        李勇        男        20         CS         1        92
    95001        李勇        男        20         CS         2        85
    95001        李勇        男        20         CS         3        88
    95002        刘晨        女        19         IS          2        90
    95002        刘晨        女        19         IS          3        80
    95003        王敏        女        18         MA      null    null
    95004        张立        男        19         IS        null    null

本例中将左边的学生关系不满足连接条件的行输出，称为左外连接

用FROM子句指定连接条件：

SELECT  Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade
 FROM    Student  LEFT OUTER JOIN SC ON  Student.Sno = SC.Sno； 

ORACLE:  LEFT OUT  JOIN

左外连接
将左边关系的不满足连接条件的行输出
 WHERE 子句方式(*出现在左边)：
      WHERE R.A1 *= S.A1
 FROM子句方式：
      FROM R LEFT [OUTER]  JOIN S ON R.A1=S.A1

右外连接
将右边关系的不满足连接条件的行输出
 WHERE 子句方式(*出现在右边) ：
      WHERE R.A1 =* S.A1
 FROM子句方式：
      FROM R RIGHT [OUTER]  JOIN S ON R.A1=S.A1

全外连接
将两边关系的不满足连接条件的行输出
 WHERE 子句方式(*出现在两边) ：
      WHERE R.A1 *=* S.A1
 FROM子句方式：
      FROM R FULL [OUTER]  JOIN S ON R.A1=S.A1

 多表连接

[例36] 查询每个学生的学号、姓名、选修的课程名及成绩。

SELECT Student.Sno，Sname，Cname，Grade
FROM      Student，SC，Course
WHERE    Student.Sno = SC.Sno 
                 AND SC.Cno = Course.Cno；

结果：
      Student.Sno    Sname         Cname           Grade
      95001             李勇           数据库            92
      95001             李勇           数学                85
      ....................................

多表连接+选择条件

[例:补充]查询CS系选数据库课成绩>90的学生的学号、姓名、成绩。

 SELECT Student.Sno，Sname，Grade
 FROM    Student，SC，Course
 WHERE   Student.Sno = SC.Sno 
         AND  SC.Cno = Course.Cno
         AND   Sdept='CS'
         AND   Cname='数据库'
         AND   Grade>90；