标准SQL语句（4） --- 表的查询（2）

前提假设,数据库中有如下表:

S（学生）表

/***学生表***/
create table S(
SNo char(6) primary key, 
SN varchar(16) not null ,   /* 姓名 */
Sex char(2) default '男',
Age int ,
Dept varchar(12)	   /* 院系 */
)

T（教师）表

/***教师表***/
create table T(
TNo char(6) primary key,
TN varchar(16) not null,
Sex char(2),
Age int,
Prof varchar(6),			/* 职位 */
Sal int,				/* 工资 */
Comm int,				/* 岗位津贴 */
Dept varchar(8)				/* 院系 */
)

C （课程 ）表

/***课程表***/
create table C(
CNo char(6) primary key,   
CN varchar(32) not null,
CT int					/*课时*/
)

SC(（ 选课 ）表

/***选课表***/
create table SC(
SNo char(6) constraint SC_SNo_FK foreign key references S(SNo),
CNo char(6) constraint SC_CNo_FK foreign key references C(CNo),
Score int,
constraint SC_SNo_CNo_PK primary key(SNo, CNo) 
)

TC（ 授课 ）表

/***授课表***/
create table TC(
TNo char(6) constraint TC_TNo_FK foreign key references T(TNo),
CNo char(6) constraint TC_CNo_FK foreign key references C(CNo)
constraint TC_TNo_CNo_PK primary key(TNo, CNo)
)

引言

数据库中的各个表中存放着不同的数据，用户往往需要用多个表中的数据来组合、提炼出所需要的信息。如果一个查询需要对多个表进行操作，就称为连接查询。

连接查询的结果集或结果表，称为表之间的连接。连接查询实际上是通过各个表之间共同列的关联性来查询数据的，数据表之间的联系是通过表的字段值来体现的，

这种字段称为连接字段。连接操作的目的就是通过加在连接字段的条件将多个表连接起来，以便从各个表中查询数据。

表的连接方法有以下两种：

1) 表之间满足一定条件的行进行连接时，from子句指明进行连接的表名，where子句指明连接的列名及其连接条件。

2) 利用关键字join进行连接。

具体的连接方法分为以下几种：

1) inner join  内连接， 只返回两个表中连接字段相等的行，此为默认值。

2) left (outer) join 称为左（外）连接，用于显示符合条件的数据行以及左表中不符合条件的数据行，此时右边数据行会以null来显示。

3) right (outer) join 称为右（外）连接，用于显示符合条件的数据行以及右表中不符合条件的数据行，此时左边数据行会null来显示。

4) full (outer) join 显示符合条件的数据行以及左边表和右边表中不符合条件的数据行。此时缺乏数据的数据行会以null来显示。

5) cross join 将一个表的每一个记录和另一个表的每个记录匹配成新的数据行。

注意：当将join关键词放于from子句中时，应有关键词on与之对应，以表明连接的条件。

1. 等值连接与非等值连接

例1：查询“刘伟”老师所讲授的课程，要求列出教师号、教师姓名和课程号。

 方法1:

select T.TNo, TN, CNo from T,TC where (T.TNo=TC.TNo) and (TN='刘伟')

解释：这里TN='刘伟'为查询条件，而T.TNo = TC.TNo 为连接条件，TNo为连接字段。连接条件一般格式为：

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

其中比较运算符主要有： = 、>、<、>=、<=、!=

当比较运算符为 “=” ，称为等值连接。其他情况称为非等值连接。

引用列名TNo时要加上表名前缀，这是因为当两个表中的列名相同时，必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。

如果列名是唯一的那么前缀可加也可不加。

方法2:（内连接）

select T.TNo, TN, CNo from T inner join TC on T.TNo=TC.TNo where T.TN='刘伟'

例2：查询所有选课学生的学号、姓名、选课名称及成绩。

select S.SNo, S.SN, C.CN, SC.Score from S,SC,C where (S.SNo=SC.SNo) and (SC.CNo=C.CNo)

例3:查询每门课程的课程名、任课教师姓名以及其职务、选课人数。

select CN, TN, Prof, count(SC.SNo) from C,TC,T,SC where C.CNo=TC.CNo and TC.TNo=T.TNo and C.CNo=SC.CNo group by SC.CNo

2.自身连接

当一个表与其自己进行连接操作时，称为表的自身连接。

例1：查询所有比“刘伟”工资高的教师姓名、工资和刘伟的工资。

方法1：

分析：要查询的内容均在同一表T中，可以将表T分别取两个别名，一个是X, 一个是Y。将X, Y中满足比刘伟工资高的行连接起来，即同一张表的T的大于连接。

select X.TN, X.Sal as OthersTSal, Y.Sal as LiuWeiTSal from T as X, T as Y where X.Sal > Y.Sal and Y.TN='刘伟'

方法2：内连接。

select X.TN, X.Sal as OthersTSal, Y.Sal as LiuWeiTSal from T as X join T as Y on X.Sal>Y.Sal and Y.TN='刘伟'

例3：查询所有学生姓名、年龄和选修课名称。

select S.SN, S.Age, C.CN from S,SC,C where S.SNo = SC.SNo and SC.CNo = C.CNo 

3.外连接

在上面的连接操作中，不满足连接条件的元组不能作为查询结果输出。而在外部连接中，参与连接的表有主从之分，以主表的每行数据去匹配从表的数据列。

符合连接条件的数据将被直接返回到结果集中，对于那些不符合连接条件的列，将被填上NULL值后再返回到结果集中。

外部连接分为左外部连接和右外部连接两种。 以主表所在方向区分外部连接，主表在左边，则称为左外部连接；主表在右边，则称为右外部连接。

例：查询所有学生的学号、姓名、选课名称和成绩（没有选课的同学的选课信息显示为空）

分析：要求没有选课的同学的选课信息显示为空的，即主表是课程表C，从表为SC，而要想得到学生信息，则还要把SC作为主表，S作为从表来查。

select S.SNo, S.SN, C.CN, SC.Score from S left outer join SC on S.SNo=SC.SNo left outer join C on SC.CNo=C.CNo