一、考点
1、MySQL 的关联 UPDATE 语句
关联更新
// 方法一:
UPDATE A, B SET A.c1=B.c1, A.c2=B.c2 WHERE A.id=B.id;
// 方法二:连接查询
UPDATE A INNER JOIN B ON A.id=B.id SET A.c1=B.c1, A.c2=B.c2 WHERE ...
2、延伸:MySQL 的关联查询语句
六种关联查询:
- 交叉连接(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
;
注:INNER JOIN
可以缩写为 JOIN
。
3)外连接(LEFT JOIN/RIGHT JOIN
)
① 左外连接
LEFT OUTER JOIN
,以左表为主,先查询出左表,按照ON
后的关联条件匹配右表,没有匹配到的用NULL
填充,可以简写成LEFT JOIN
.
② 右外连接
RIGHT OUTER JOIN
,以右表为主,先查询出右表,按照ON
后的关联条件匹配坐表,没有匹配到的用NULL
填充,可以简写成RIGHT JOIN
.
4)联合查询
SELECT * FROM A UNION SELECT * FROM B UNION ......
注:
- 就是把多个结果集集中在一起,
UNION
前的结果为基准,需要注意的是联合查询的列数要相等,相同的记录行会合并(如果前者 select 和 后者 select 有重复,则会合并,且以前面的为基准);- 如果使用
UNION ALL
,不会合并重复的记录行;- 在效率方面,
UNION
高于UNION ALL
。
5)全连接(FULL JOIN
)
MySQL本身不支持全连接,如果要使用全连接,可以使用 LEFT JOIN 和 UNION 和 RIGHT JOIN 联合使用。
SELECT * FROM A LEFT JOIN B ON A.id = B.id UNION
SELECT * FROM A RIGHT JOIN B ON A.id = B.id;
6)嵌套查询【不推荐使用,效率不可把控】
用一条SQL语句的结果作为另外一条SQL语句的条件
SELECT * FROM A WHERE id IN (SELECT id FROM B);
二、解题方法
根据考题要搞清楚 表的结构 和 多表 之间的关系,根据想要的结果思考使用哪种关联方式,通常把要查询的列先写出来,然后分析这些列都属于哪些表,才考虑使用关联查询。
三、真题
1、有A(id, sex, par, c1, c2),B(id, age, c1, c2)
两张表,其中 A.id
与 B.id
关联,现在要去写出一条 SQL 语句,将 B 中 age > 50
的记录的 c1, c2
更新到 A表中,统一记录中到 c1, c2
字段中。
// 方法1:
UPDATE
A, B
SET
A.c1 = b.c1, A.c2 = B.c2
WHERE
A.id = B.id AND B.age > 50;
// 方法二:
UPDATE
A INNER JOIN B ON A.id = B.id
SET
A.c1 = B.c1, A.c2 = B.c2
WHERE
B.age > 50;
2、请写出 左连接(LEFT JOIN)、右连接(RIGHT JOIN)和 内连接(INNER JOIN)三者的区别。
- 左连接:以 左表为主,按照
ON
后的关联条件匹配右表,没有匹配到的用NULL
填充;- 右连接:以 右表为主,按照
ON
后的关联条件匹配左表,没有匹配都得用NULL
填充;- 内连接:不以任何表为主,只找
ON
后的关联条件,如果满足关联条件才显示。
3、为了记录足球比赛的结果,设计表如下:
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。
解题思路:
- 先找出我们要 显示的内容;
- 分析两张表的关联性。
如图所示:
结果:
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 t2 ON m.guestTeamID = t2.guestTeamID
WHERE m.matchTime BETWEEN "2006-6-1" AND "2006-7-1";