六种关联查询
- 交叉连接(CROSS JOIN);
- 内连接(INNER JOIN);
- 外连接(LEFT JOIN/RIGHT JOIN);
- 联合查询(UNION 与 UNION ALL);
- 全连接(FULL JOIN)【在MySQL中本身不支持全连接,若要使用,得需要一些方法实现】.
1.交叉连接(CROSS JOIN)
SELECT * FROM A, B(, C) 或者
SELECT * FROM A CROSS JOIN B (CROSS JOIN C);
注:没有任何关联条件,结果是 笛卡尔积,结果集 会很大,没有意义,很少使用。
2.内连接(INNER JOIN)
SELECT * FROM A, B WHERE A.id = B.id 或者
SELECT * FROM A INNER JOIN B ON A.id = B.id;
注:多表中同时符合某种条件的数据记录的集合。
内连接分为三类:
① 等值连接:ON A.id = B.id
;
② 不等值连接:ON A.id > B.id
;
③ 自连接:SELECT * FROM A T1 INNER JOIN A T2 ON T1.id = T2.pid
;
3. 外链接
外链接又分为左外链接(LEFT JOIN)和右外链接(RIGHT JOIN)
1)左外链接
LEFT OUTER JOIN
,以左表为主,先查询出左表,按照 ON
后的关联条件匹配右表,没有匹配到的用 NULL
填充,可以简写 成 LEFT JOIN
.
2)右外链接
RIGHT OUTER JOIN
,以右表为主,先查询出右表,按照 ON
后的关联条件匹配坐表,没有匹配到的用 NULL
填充,可以简写 成 RIGHT JOIN
.
4.联合查询(UNION 与 UNION ALL)
SELECT * FROM A UNION SELECT * FROM B UNION ......
注:
- 就是把多个结果集集中在一起,UNION 前的结果为基准,需要注意的是联合查询的列数要相等,相同的记录行会合并(如果前者 select 和 后者 select 有重复,则会合并,且以前面的为基准);
- 如果使用 UNION ALL,不会合并重复的记录行;
- 在效率方面,UNION 高于 UNION ALL。
5.全连接(FULL JOIN)
SELECT * FROM A LEFT JOIN B ON A.id = B.id UNION SELECT * FROM A RIGHT JOIN B ON A.id = B.id;
MySQL本身不支持全连接,如果要使用全连接,可以使用 LEFT JOIN 和 UNION 和 RIGHT JOIN 联合使用。
6)嵌套查询【不推荐使用,效率不可把控】
题解:为了记录足球比赛的结果,设计表如下:
team:参赛队伍表
字段名 | 类型 | 描述 |
---|---|---|
teamID | int | 主键 |
teamName | varchar(20) | 队伍名称 |
match:赛程表
字段名称 | 类型 | 描述 |
---|---|---|
matchID | int | 主键 |
hostTeamID | int | 主队ID |
guestTeamID | int | 客队ID |
matchResult | varchar(20) | 比赛结果 |
matchTime | date | 比赛日期 |
其中,match赛程表 中的 hostTeamID 与 guestTeamID 都和 team表 中的 teamID关联,查询 2006-6-1 到 2006-7-1 之间举行的所有比赛,并且用以下形式列出:拜仁 2:0 不莱梅 2006-6-21。
解题思路:
- 先找出我们的结果需求:主队名称 比赛结果 客队名称 比赛日期
- 分析表与表之间的关联关系,match表中的主队ID(hostTeamID)和客队ID(guestTeamID)都和team表中的teamID关联,通过关联查询可以获得主队名称和客队名称
- 根据match表中的matchTime字段查询 2006-6-1到2006-7-1之间举行的比赛即可
方法一:
SELECT
t1.teamName,
m.matchResult,
t2.teamName,
m.matchTime
FROM
`match` AS m
LEFT JOIN team AS t1 ON m.hostTeamID = t1.teamID
LEFT JOIN team AS t2 ON m.guestTeamID = t2.teamID
WHERE
m.matchTime BETWEEN '2006-6-1'
AND '2006-7-1';
方法二:
SELECT
T1.teamName,
M.matchResult,
T2.teamName,
M.matchTime
FROM
team T1,
team T2,
`match` M
WHERE
T1.teamId = M.hostTeamID
AND T2.teamId = M.guestTeamID
AND M.matchTime >= "2006-06-01“ and M.matchTime <=”2006-07-01";
最后的时间比较也可用 between ... and