赞
踩
MySQL:join语句类型
join从句的类型有以下几种:
内链接(inner)
右外连接(right outer)
左外连接(left outer)
全外连接(full outer)
交叉链接(cross)
连接条件:使用ON设定连接条件,也可以用WHERE代替
· ON:设定连接条件
· WHERE:进行结果集记录的过滤
一,内连接inner join:
内连接是返回左表及右表符合连接条件的记录,在MySQL中JOIN与INNER JOIN是等价的
语法为:select a.user_name,a.over,b.over from user1 as a join user2 as b on a.user_name=b.user_name;
( 选择表中字段 ) (a为user1别名) ( 公共部分 )
例如:SELECT * FROM tabA JOIN tabB ON tabA.name = tabB.name;表示返回都含有的name值对应的字段
其连接关系如下图所示:
二,右外连接:left outer join
显示右表中的全部记录和左表中符合连接条件的记录
如果某字段只存在某一表,那么另一表的里字段返回 NULL
以下语句查询出A表中的key和B表中的key相等的部分 ,而结果查询出了B表中所有的数据和B表和A表相同的部分
select from tableA A right join TableB B on A.key=B.key
以下语句查询出A表中的key和B表中的key相等的部分,而结果查询出了B表中有的,而A表中没有的数据,效果类似于inner join
select from tableA A right join TableB B on A.key=B.key where B.key IS NULL
它的连接关系图如下所示:
三,左外连接:left outer join
显示左表中的全部记录和右表中符合连接条件的记录
若某字段只存在某一表,则另一表的里字段返回 NULL
以下语句查询出A表中的key和B表中的key相等的部分 ,而结果查询出了A表中所有的数据和B表和A表相同的部分
select from tableA A left join TableB B on A.key=B.key
以下语句查询出A表中的key和B表中的key相等的部分,而结果查询出了A表中有的,而B表中没有的数据,效果类似于inner join
select from tableA A left join TableB B on A.key=B.key where B.key IS NULL
它的连接关系图如下所示:
四,交叉连接
(笛卡尔积连接):A*B,其实就是将两个表进行相乘,一般项目中很少用到笛卡尔积连接
select * from user1 a cross join user2 b;
如上语句:如果user1表中有3条记录,user2中有4条记录,则结果就有12条记录
五,MySQL 中 不会支持 full join
解决方法:采用左连接和右连接结合+ union all 方法来取得两表的合集
select a.user_name , a.over , b.over
from user1 a
left join user2 b on a.user_name = b.user_name
union all
select b.user_name , b.over, a.over
from user1 a
right join user2 b on a.user_name = b.user_name
六,MySQL 联合更新技巧:
题目:将user1表中user_name与user2表中user_name相同的user1表的over替换成user2表中的over值?
一般的正常思路:
update user1
set over='齐天大圣'
where user1.user_name in(
select b.user_name from user1 a left join user2 b on a.user_name = b.user_name);
这样去执行思路上是对的,但是MySQL不支持这种机制
解决方法:
update user1 a
join (
select b.user_name from user1 a join user2 b on a.user_name = b.user_name
) b on a.user_name = b.user_name set a.over='齐天大圣'
七,join优化子查询技巧:
一般子查询写法:(数据小时,没有多大影响,如果数据量大时,则要消耗大量的查询)
select a.user_name , a.voer , (select over from user2 where a.user_name = b,user_name) as over2
from user1 a;
join优化后的写法如下所示:
select a.user_name , a.over , b.over from user1 a
left join user2 b on a.user_name = b.user_name
八,MySQL 使用join优化聚合子查询:
问题:如何查询出四人组中打怪最多的日期?
一般思路:聚合子查询
select a.user_name , b.timestr , b.kills
from user1 a join user_kills b on a.id = b.user_id
where b.kills = (select MAX(c.kills) from user_kills c where c.user_id = b.user_id)
优化后的方法:
select a.user_name , b.timestr , b.kills
from user1 a
join user_kills b on a.id = b.user_id
join user_kills c on c.user_id = b.user_id
group by a.user_name , b.timestr , b.kills
having b.kills = MAX(c.kills)
九,分类聚合方式查询每一个用户某一个字段数据最大的两条数据:
select d.user_name ,c.ctimestr,kills from
(select user_id ,timestr ,kills ,(
select count(*) from user_kills b where b.user_id = a.user_id and a.kills <= b.kills) as cnt
from user_kills a group by user_id,timestr,kills) c
join user1 d on c.user_id = d.id where cnt <= 2
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。