表结构
CREATE TABLE `user1` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`over` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `user2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`over` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
类型一:内链接 – Inner Join
内链接Inner join基于连接谓词将两张表(如A和B)的列组合在一起,产生新的结果表
即 查询两表公共的部分
SELECT a.name,a.over,b.over FROM user1 a INNER JOIN user2 b ON a.name = b.name;
类型二:左外连接 – Left Outer Join
SELECT a.name,a.over,b.over FROM user1 a LEFT JOIN user2 b ON a.name = b.name;
SELECT a.name,a.over,b.over FROM user1 a LEFT JOIN user2 b ON a.name = b.name WHERE b.name IS NULL;
类型三:右外连接 – Right Outer join
类型四:全链接 – full join
SELECT a.name,a.over,b.over FROM user1 a LEFT JOIN user2 b ON a.name = b.name UNION ALL SELECT b.name,b.over,a.over FROM user1 a RIGHT JOIN user2 b ON a.name = b.name;
类型五:交叉连接 – Cross Join
交叉连接(crossjoin),又称芦圭尔连牌(cartesianjoin)或
里乘(Product),如果A和B是两个集合,它们的交叉连接就
记为:AxB.
SELECT a.name,a.over,b.over,b.`name` FROM user1 a CROSS JOIN user2 b ;
技巧一:使用join更新表
如何更新使用过滤条件中包括自身的表?
情景:
把同时存在于取经四人组和悟空兄弟表中的记录的
人在取经四人组表中的over字段更新为齐天大圣”。
SELECT b.name FROM user1 a INNER JOIN user2 b ON a.name = b.name --》 '孙悟空'
UPDATE user1 a JOIN (SELECT b.name FROM user1 a INNER JOIN user2 b ON a.name = b.name) b ON a.name = b.name SET a.over = '齐天大圣';
技巧二:使用join优化子查询
技巧三:使用join优化聚合子查询
引入一张新表,按日期记录 每个人打怪的数量
问题:如何查询出四人组中打怪最多的日期?
SELECT a.name,b.timestr,b.kills FROM user1 a JOIN kills b ON a.id = b.user_id WHERE b.kills = (SELECT MAX(c.kills) FROM kills c WHERE c.user_id = b.user_id);