Join语句
一、Join语句几种方式
1.内连接(INNER)
SELECT a.username,a.over,b.over
FROM user1 a
INNER JOIN user2 b
ON a.username = b.username;
查询结果:a、b两表的公共数据
2.左外连接(LEFT OUTER)
SELECT a.username,a.over,b.over
FROM user1 a
LEFT JOIN user2 b
ON a.username = b.username
WHERE b.username IS NOT NULL;
查询结果:a表的所有数据
3.右外连接(RIGHT OUTER )
SELECT b.username,b.over,a.over
FROM user1 a
RIGHT JOIN user2 b
ON a.username = b.username
WHERE a.username IS NULL;
查询结果:b表的所有数据 - ab两个表的公共数据
4.全外连接(FULL OUTER)
SELECT a.username,a.over,b.over
FROM user1 a
FULL JOIN user2 b
ON a.username = b.username
WHERE a.username IS NULL OR b.username IS NULL;
MySQL中不支持FULL OUTER JOIN 连接,故上面查询会出现错误
SELECT a.username,a.over,b.over
FROM user1 a
LEFT JOIN user2 b ON a.username = b.username
UNION ALL
SELECT b.username,b.over,a.over
FROM user1 a
RIGHT JOIN user2 b ON a.username = b.username
WHERE a.username IS NULL OR b.username IS NULL;
查询结果:a,b表的所有数据 - a,b表的公共数据
5.交叉连接(CROSS)或笛卡尔连接(cartesian)或叉乘(Product)
SELECT a.username,a.over,b.over
FROM user1 a
CROSS JOIN user2 b
ON a.username = b.username
一般要避免使用此查询
二、Join语句的使用
1.使用join更新表
UPDATE user1
SET over = '齐天大圣'
WHERE user1.username IN(
SELECT b.username
FROM user1 a
JOIN user2 b
ON a.username = b.username
);
MySQL不支持UPDATE操作,故上面更新操作会出现错误
UPDATE user1 a JOIN (
SELECT b.username
FROM user1 a JOIN user2 b
ON a.username = b.username
) b ON a.username = b.username
SET a.over='齐天大圣';
更新结果:将over字段的数据更新为‘齐天大圣’
2.使用join优化子查询
SELECT a.username,a.over,
(
SELECT over FROM user2 b
WHERE a.username = b.username
) AS over2
FROM user1 a;
查询结果:对a表中的每一条数据都进行子查询,很慢
优化后:
SELECT a.username,a.over,b.over AS over2
FROM user1 a
LEFT JOIN user2 b ON
a.username = b.username
优化后和优化前结果相同则为有效优化
查询结果:对a表中的每一条数据都进行子查询,较快
3.使用join优化聚合子查询
SELECT a.username,b.timestr,b.kills
FROM user1 a JOIN userkills b
ON a.id = b.userid
WHERE b.kills = (
SELECT MAX(c.kills)
FROM userkills c WHERE c.userid = b.userid
);
查询结果:各个username打怪(kills)最多的日期
避免子查询:
SELECT a.username,b.timestr,b.kills
FROM user1 a
JOIN userkills b ON a.id = b.userid
JOIN userkills c ON c.userid = b.userid
GROUP BY a.username,b.timestr,b.kills
HAVING b.kills = MAX(c.kills);
上一篇: Qiime1-10.Alpha多样性分析
下一篇: Android树形控件的实现方法