多表连接查询是指查询同时涉及两个或两个以上的表,连接查询是关系数据库中最主要的数据查,表与表之间的连接分为交叉连接、内连接、自连接、外连接。外连接又分为3种,即左外连接、右外连接和全外连接
连接查询的类型可以在select语句的from子句种指定,也可以在where子句中指定
交叉连接又称笛卡儿连接,是指两个表之间做笛卡儿积操作,得到结果集的行数是两个表的行数的乘积。交叉连接的一般格式如下
- select [all|distinct] [别名,]<选项1> [AS<显示列名>] [,[别名.]<选项2>[AS<显示列名>][,....]]
- from<表名1>[别名1],<表名2>[别名2];
需要连接查询的表明在from子句中指定,表明之间用英文逗号隔开
成绩表(sc)和课程表关系表(course)进行交叉连接
- select A.*,B.*
- from course A,sc B;
此处为了简化表名,分别给两个表指定了别名A和B。但是,一旦表明指定了别名,在该命令中,都必须用别名代替表明
内连接的一般格式如下
- select [all|distinct] [别名.]<选项1>[AS<显示列名>] [,[别名.]<选项2>[AS<显示列名>]<,....>]
- from <表明1> [别名1],<表明2>[别名2][,....]
- [where <条件表达式>];
或者
- select [all|distinct] [别名.]<选项1>[AS<显示列名>] [,[别名.]<选项2>[AS<显示列名>]<,....>]
- from <表名1>[别名1] inner join <表名2>[别名2]on<连接条件表达式>
- [where <条件表达式>];
其中,第一种格式的连接类型在where子句中指定,第2种格式的连接类型在from子句种指定
另外,连接条件是指在连接查询种连接两个表的条件。连接条件表达式的一般格式如下
[<表名1>]<别名1.列名> <比较运算符> [<表名2>]<别名2.列名>
比较运算符可以使用等号‘=’,此时称作等值连接,也可以使用不等比较运算符,包括>、 <、>=、<=、!>、!<、<>等,此时为不等值连接
查询每个学生及其选修课的情况
因为学生的基本情况存放在student表中,选课情况存放在sc表中,所以查询过程涉及上述两个表。这两个表是通过公共字段sno实现内连接
- select A.*,B.*
- from student A,sc B
- where A.sno=B.sno;
或者
- select A.*,B.*
- from student A inner join sc B on A.sno=B.sno;
若在等值连接中把目标列中的重复字段去掉,则称为自然连接
用自然连接完成查询
- select student.sno,sanme,ssex,sbirthday,sdept,cno,degree
- from student,sc
- where student.sno=sc.sno;
sno前的表名不能省略,因为sno是student和sc共有的属性,所以必须添加上表名前缀
输出所有女生的学号、姓名、课程号及成绩
- select A.sno,sname,cno,degree
- from student A,sc B
- where A.sno=B.sno and ssex='女';
连接操作不只在表之间进行,一张表内可以进行自身连接操作,即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。在自连接中,必须为表指定两个别名,使之在逻辑上称为两张表
自连接一般格式如下
- select [all|distinct] [别名.]<选项1>[AS<显示列名>] [,[别名.]<选项2>[AS显示列名][,...]]
- from<表名1>[别名1],<表名1>[别名2][,...]
- where <连接条件表达式> [and<条件表达式>];
查询同时选修了c01和c04课程的学生学号
- select A.sno
- from sc A,sc B
- where A.sno=B.sno and A.cno='c01' and B.cno='c04';
查询与张三在同一个系的学生的学号、姓名、和所在系
- select B.sno,B.sname,B.sdept
- from student A,student B
- where A.sdept=B.sdept and A.sanme='张三'and B.sname!='张三';
在自然连接中,只有在两个表中匹配的行才能在结果集中出现。而外连接中可以只限制一个表,而对另外一个表不加限制(所有的行都出现在结果集中)
外连接分为左外连接、右外连接和全外连接。左外连接是对连接条件中左边的表不加限制,即在结果集中保留连接表达方式左边表中的非匹配记录;右外连接是对右边的表不加限制,即在结果集中保留连接表达式右边表中的非匹配记录;全外连接对两个表都不加限制,两个表中所有的行都会包括在结果集中
外连接的一般格式如下
- select [all|distinct] [别名.]<选项1>[AS<显示列明>] [,[别名.]<选项2> [AS<显示列名>][,...]]
- from <表名1>left|right|full[outer]join <表名2>
- on <表名1.列名1>=<表名2.列名2>
由于2005030301和2005020202没有选修课程,所以在查询结果中没有这两个学生的信息,但有时候在查询结果中也需要显示这样的信息,这就需要使用外连接查询
利用左外连接查询改写例
- select student.sno,sname,ssex,sbirthday,sdept,cno,degree
- from student left join sc
- on student.sno=sc.sno