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

时间:2022-12-09 09:04:23

连接查询

查询结果或条件涉及多个表的查询称为连接查询
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=;/* 选择条件 */

或:

 SELECT  Student.Sno,Sname,Grade
FROM Student JOIN SC ON Student.Sno=SC.Sno;
WHERE Cno=;

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

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

三、自身连接

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

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

[例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>;