多表连接查询

一、连接查询结构

1.where、inner join

  1. where子句中使用的连接语句，在数据库语言中，被称为隐性连接。inner join……on子句产生的连接称为显性连接。（其他join参数也是显性连接）
  2. where和inner join产生的连接关系，没有本质区别，结果也一样。
  3. 若两边不相等，则连接不上

2.具体内外连接

  1. (inner) join：内连接，理解为“有效连接”，两张表中都有的数据才会显示
  2. left(outer) join：（左）外连接，显示符合条件的数据行，以及左边表不符合条件的数据行。此时右数据行会易NULL填充
  3. right(outer) join：（右）外连接，显示符合条件的数据行，以及右边表不符合条件的数据行。此时左数据行会易NULL填充
  4. full(outer)join:显示符合条件的数据行，以及左边表和右边表不符合条件的数据行。此时缺乏数据的数据行会易NULL填充
  5. cross join:无特殊要求，任何表都可以交叉查询

3.使用 AS的注意事项

<源表名> [ AS ] <表别名>

注意：当为表指定了别名时，在查询语句中的其他地方，所有用到表名的地方都要使用别名，而不能再使用原表名。

4.关于自连接查询

  • 特殊的内连接
  • 相互连接的表物理上为同一张表
  • 必须为两个表取别名，使之在逻辑上成为两个表。
    如：
    FROM 表1 AS T1 – 在内存中生成“T1”
    JOIN 表1 AS T2 – 在内存中生成“T2”

二、内连接查询（inner）join

(1)查询刘伟老师所教授的课程号，列出教师号，教师姓名，课程号（3种方法）

① select Tno,TN,Cno
from T,TC
where T.Tno = TC.Tno and TN='刘伟'

②select Tno,TN,Cno
form T join TC on T.Tno=TC.Tno
where TN='刘伟'

③select R1.Tno,R1.TN,R2.Cno
from (select Tno,Cno from TC) as R1
inner join (select Tno,TN from T where TN = '刘伟') as R2
on R1.Tno=R2.Tno

(2)查询‘信息管理系’修了‘计算机文化基础’的学生姓名和成绩

select S.Sn,scorce
from S
join SC on S.Sno = SC.Sno
join C on C.Cno 
where dept = '信息管理系' and CN ='计算机文化基础'

(3)统计每一年龄组选修课程的学生人数

select age,count(distinct sno)  # 去重
from S,SC
where S.Sno = SC.Sno
group by age

三、外连接查询

(1)查询所有学生的学号，姓名，选课名称及成绩（没有选课的同学的选课信息显示为空）

select Sno,Sn,Cn,Scorce
from S 
left join SC on S.Sno = SC.Sno
left join C on C.Cno=SC.Cno

(2)查询无人选课的课程名

①select CN
from C left  join SC on C.Cno = SC.Cno
where SC.Cno is null # where SC.Sno is null 也可以

②select CN 
from C where
Cno not in （select Cno from SC )

四、交叉查询（无特殊要求，任何表都可以交叉查询）

对学生表和课程表进行交叉查询

select * from S cross join C

五、自连接查询

(1)查询所有比“刘伟”工作高的教师姓名、工资、和刘伟的工资（3种）

①select X.TN,X.Sal as Sal_a,Y.Sal as Sal_b 
from T as X ,T as Y
where X.sal >Y.sal and Y.TN = '刘伟'

②select X.TN，X.Sal，Y.Sal
from T as X inner join T as Y
on X.Sal >Y.Sal and Y.TN = ’刘伟

③select R1.TN,R1.Sal,R2.Sal 
from (select TN,Sal from T) as R1
inner join
(select Sal from T where TN =‘刘伟') as R2
on R1.Sal  <font color = 'red'> > R2.Sal  #大于连接

(2)检索所有学生姓名，年龄和选课名称（2种）

①select SN,Age,CN
from S,SC ,C
where S.Sno = SC.Sno and SC.Cno = C,.Cno

②select 
from 
(select Sno,SN,Age from S) as R3 #从S表把学号，姓名，年龄选出来,放入R3
inner join 
(select R2.Sno,R1.CN               #从C,SC查出学课学生的学号，被选的课程名，放到R4
   from 
     (select Cno,Cn from C ) as R1
     inner join 
     (select Sno,Cno from SC) as R2
     on R1.Cno = R2.Co) as R4 
on R3.Sno = R4.Sno #R3,R4连接，检索所有学生姓名，年龄和选课名称

(3)与刘晨是一个系学习的学生姓名，所在系

select S2.Sn,S2.dept
from S as S1
inner join S as S2
on S1.dept = S2.dept 
where S1.SN = '刘晨'
and S2.SN <font color='red'>< ></font> '刘晨'

注意：这里必须使用别名