概述
在实际开发中,除了单表查询,还会经常遇到多表查询的需求。例如,文章和栏目是两张表,如果要求查询结果中既有文章标题又有栏目名称,就需要对两张表进行查询。接下来,本任务将对多表查询的相关内容进行详细讲解。
合并查询就是将多个 SELECT 语句的查询结果合并到一起。
在使用 UNION 执行合并查询时,有以下几点需要注意的地方:
查询结果集中的字段名称总是与第一个 SELECT 语句中的字段名称相同。
每个SELECT语句必须拥有相同数量的字段,和相似的数据类型。另外,每条 SELECT 语句中相同数据类型的字段顺序也必须相同。
合并查询——准备数据
SELECT cid,cname FROM category where cid=1
union ALL -- 实现合并
SELECT id,aname FROM article where id=5;
值得一提的是,当使用 UNION 连接的多个查询结果中存在相同的记录时,会自动合并成一条记录。如果不需要合并可以使用 UNION ALL 进行合并查询。
连接查询
——交叉连接
SELECT 查询字段 from 表1 CROSS JOIN 表2;
-- 交叉连接
-- c分别与a连接
-- cross join 交叉连接(交叉连接返回左表中的每一行与右表中的所有行组合)
SELECT * FROM category c CROSS JOIN article a WHERE c.cid=a.cid;
-- 等价于下一条
SELECT * FROM category c,article a WHERE c.cid=a.cid;
——内连接
内连接(INNER JOIN)又称简单连接或自然连接,是一种常见的连接查询。在连接时,使用 ON 关键字指定连接条件,并返回满足条件的记录数据。基本语法格式如下:
SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段 = 表2.关系字段 WHERE 条件;
在上述语法中,ON 与 WHERE 虽然都是用于连接查询条件,但是它们的使用是有区别的。ON 用于过滤两表连接的条件,WHERE 用于过滤中间表的记录数据。其中,由于内连接查询是默认的连接方式,因此可以省略INNER 关键字。
-- 内连接 INNER JOIN.....on
-- INNER JOIN 前后顺序改变没有影响
-- 根据条件匹配。(只查询匹配行,所以内连接会丢失数据)
SELECT * FROM article a INNER JOIN category c on a.cid=c.cid;
下面对文章表和栏目表进行内连接查询,具体 SQL 语句如下:
SELECT a.id
, a.name
, a.cid
, c.cname
FROM article
a
JOIN category
c ON a.cid
=c.cid
;
值得一提的是,在内连接查询中还有一种特殊的查询:自连接查询。它是指相互连接的表在物理上为同一个表,但逻辑上分为两个表。
——外连接
与内连接不同的是,外连接(OUTER JOIN)生成的结果集不仅可以包括符合连接条件的数据记录,而且还可以包括左表、右表或两表中所有的数据记录。根据使用需求不同,外连接可以分为:左连接“LEFT (OUTER) JOIN”、右连接查询“RIGHT (OUTER) JOIN”。基本语法格式如下:
SELECT 所查字段 FROM 表1 LEFT|RIGHT [OUTER] JOIN 表2
ON 表1.关系字段 = 表2.关系字段 WHERE 条件
关键字“LEFT|RIGHT [OUTER] JOIN”左边的表被称为左表,关键字右边的表被称为右表。其中,OUTER在查询时可以省略。
为了更好的学习外连接查询,接下来就针对左连接、右连接查询分别进行讲解。
① 左连接查询(LEFT JOIN 或 LEFT OUTER JOIN)
左连接查询用于返回左表中的所有记录,以及右表中符合连接条件的记录。当左表的某行记录在右表中没有匹配的记录时,右表中相关的记录将设为空值。
-- 内连接 INNER JOIN.....on
-- INNER JOIN 前后顺序改变没有影响
-- 根据条件匹配。(只查询匹配行,所以内连接会丢失数据)
SELECT * FROM article a INNER JOIN category c on a.cid=c.cid;
下面对文章表和栏目表进行内连接查询,具体 SQL 语句如下:
SELECT a.id
, a.name
, a.cid
, c.cname
FROM article
a
JOIN category
c ON a.cid
=c.cid
;
值得一提的是,在内连接查询中还有一种特殊的查询:自连接查询。它是指相互连接的表在物理上为同一个表,但逻辑上分为两个表。
——外连接
与内连接不同的是,外连接(OUTER JOIN)生成的结果集不仅可以包括符合连接条件的数据记录,而且还可以包括左表、右表或两表中所有的数据记录。根据使用需求不同,外连接可以分为:左连接“LEFT (OUTER) JOIN”、右连接查询“RIGHT (OUTER) JOIN”。基本语法格式如下:
SELECT 所查字段 FROM 表1 LEFT|RIGHT [OUTER] JOIN 表2
ON 表1.关系字段 = 表2.关系字段 WHERE 条件
关键字“LEFT|RIGHT [OUTER] JOIN”左边的表被称为左表,关键字右边的表被称为右表。其中,OUTER在查询时可以省略。
为了更好的学习外连接查询,接下来就针对左连接、右连接查询分别进行讲解。
① 左连接查询(LEFT JOIN 或 LEFT OUTER JOIN)
左连接查询用于返回左表中的所有记录,以及右表中符合连接条件的记录。当左表的某行记录在右表中没有匹配的记录时,右表中相关的记录将设为空值。
– 左外连接 以category为基准表
SELECT * FROM category c LEFT JOIN article a ON a.cid=c.cid;
② 右连接查询(RIGHT JOIN 或 RIGHT OUTER JOIN)
右连接与左连接相反,它返回右表中的所有记录,以及左表中符合连接条件的记录。如果左表中没有与右表匹配的记录,则将左表相关的记录设为空值。
-- 右外链接
SELECT * FROM category c RIGHT JOIN article a ON a.cid=c.cid;
3.全外连接
-- 全外链接
SELECT * FROM category c LEFT JOIN article a ON a.cid=c.cid
UNION ALL
SELECT * FROM category c RIGHT JOIN article a ON a.cid=c.cid;
SELECT * FROM category c LEFT JOIN article a ON a.cid=c.cid
UNION
SELECT * FROM category c RIGHT JOIN article a ON a.cid=c.cid;
子查询
子查询就是包含在一条 SQL 语句中的 SELECT 语句。当遇到多层子查询时,首先会从最里层的子查询开始执行,然后将返回的结果作为外层查询的过滤条件。需要注意的是,子查询必须书写在括号内。
使用子查询时,外层语句的 WHERE 后面除了比较运算符外,还可以使用 IN、EXISTS、ANY、ALL 等操作符。接下来将对子查询的使用进行讲解。
——单行子查询
单行子查询,就是将一个 SELECT 查询语句的结果作为另一个查询语句的 WHERE 条件。示例 SQL 语句如下。
SELECT * FROM article
WHERE cid
=
(SELECT cid
FROM category
WHERE cname
=‘生活’);
上述 SQL 语句表示通过栏目名称查找栏目 cid,然后到文章表中根据 cid 查找该栏目下的文章记录。
-- 单行子查询
-- 查询那些是文章对应诗 新闻和歌
SELECT * FROM article WHERE cid=(SELECT cid FROM category WHERE cname='诗');
SELECT * FROM article WHERE cid=(SELECT cid FROM category WHERE cname='新闻');
SELECT * FROM article WHERE cid=(SELECT cid FROM category WHERE cname='歌');
——带IN关键字的子查询
使用 IN 关键字时,子查询将返回一个结果集,作为外层 SQL 语句的判断条件。
-- 带in 关键字
-- 查询那些是文章对应诗 新闻和歌
SELECT *FROM article WHERE cid in (SELECT cid FROM category WHERE cname in ('诗','新闻','歌') );
-- 那些文章不是PHP学习和歌
SELECT cid FROM article WHERE cid NOT in (SELECT cid FROM category WHERE cname in ('php学习','歌') );
SELECT cid FROM article WHERE cid NOT in (SELECT cid from category WHERE cname='php学习' AND cname='歌');
SELECT cid FROM article WHERE cid in (SELECT cid from category WHERE cname!='php学习' AND cname!='歌' );
SELECT cid FROM article WHERE cid in (SELECT cid from category WHERE cname NOT in ('php学习','歌') );
——带EXISTS关键字的子查询
EXISTS 关键字后面连接的子查询语句不返回查询记录,而是返回一个真假值。当子查询语句查询到满足条件的记录时,就返回 TRUE,执行外层SQL 语句;否则返回 FALSE,不执行外层的 SQL 语句。
-- EXISTS
-- 如果category表中存在cid=3,则修改article表中cid=2的name为哈哈,
UPDATE article SET aname='哈哈' WHERE cid=2 AND EXISTS(SELECT cname FROM category WHERE cid=3 );
使用 ANY 关键字时,只要其后的子查询满足其中任意一个判断条件,就返回结果作为外层 SQL 语句的执行条件。
-- ANY ang(some)和all都用于子查询,any表示是任何一个,all表示的所有
SELECT * FROM article WHERE cid>any(SELECT cid FROM category WHERE cname in('诗','新闻','歌'));
-- all
SELECT * FROM article WHERE cid>all(SELECT cid FROM category WHERE cname in('诗','新闻','歌'));
——带ALL关键字的子查询
ALL 关键字在使用时,只有满足内层查询语句返回的所有结果,才可以执行外层查询语句。