SQL语句开发技巧
正确使用SQL带来的优势
1 增加数据库处理效率,减少应用响应时间
2 减少数据库服务器负载,增加服务器稳定性
3 减少服务器间通讯的网络流量
正确使用join语句
SQL标准中join的类型
内连接 INNER
全外连接 FULL OUTER
左外连接 LEFT OUTER
右外连接 RIGHT OUTER
交叉连接 CROSS
内连接:查询两个表中公共的部分
左连接:查询包含左表全部结果以及和左表对应的右边匹配的结果
右连接:左连接的反向
全外连接:FULL JOIN 左右连接的合集
mysql不直接支持全外连接,需要借助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 b.`user_name`=a.`user_name`;
CROSS JOIN 交叉连接,又称笛卡尔连接或叉乘,如果A和B是两个集合,它们的交叉连接就记为A X B
>
select a.`user_name`,a.`over`,b.`user_name`,b.`over` from user1 a cross join user2 b;
如何更细使用过滤条件中包含自身的表?
原始SQL
update user1 set over='齐天大圣' where user1.`user_name` in
(
select b.`user_name` from user1 a join user2 on a.`user_name` = b.`user_name`
)
会报错 更细的表不能出现在from从句中
改进
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优化子查询
原始SQL
select a.user_name,a.over,(select over from user2 b where a.user_name=b.user_name) as over2 from user1 a;
优化后
select a.user_name,a.over,b.over as over2 from user1 a left join user2 b on a.user_name = b.user_name;
使用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);
MySQL查询之join优化
最新推荐文章于 2024-08-10 21:04:12 发布