本文内容来自MySQL开发技巧(一)
MySQL开发技巧学习笔记一
正确谁用SQL:
- 增加数据库处理效率,减少应用响应时间
- 减少数据库服务器负载,增加服务器稳定性
- 减少服务器间通讯的网络流量
如何正确使用Join语句
SQL标准中Join的类型
- 内连接(INNER)
- 全外连接(FULL OUTER)
- 左外连接(LEFT OUTER)
- 右外连接(RIGHT OUTER)
- 交叉连接(CROSS)
内连接
内连接Inner join基于连接谓词将两张表(如A和B)的列组合在一起,产生新的结果表。可以用来交集。
左外连接
左外连接有两种形式
一种是包含所有A表中的数据
一种是通过左外连接查询出只存在于A表中,不存在与B表中的数据
select a.id,a.user_name, a.over, b.id, b.user_name, b.over
from user1 a
left join user2 b
on a.user_name = b.user_name
where b.user_name is null;
右外连接
以右表为基础,查询结果会包好右表中所有的记录。
全连接
全连接可以查询所有存在于A表或B表中的数据
或者查询只存在A表或B表中的数据
MySQL不支持全连接,可以使用union
来实现
select a.id,a.user_name, a.over, b.id, b.user_name, b.over
from user1 a
left join user2 b
on a.user_name = b.user_name
union
select a.id,a.user_name, a.over, b.id, b.user_name, b.over
from user1 a
right join user2 b
on a.user_name = b.user_name;
结果为:
参考MySQL UNION 与 UNION ALL 语法与用法
左连接查询结果为:
select a.id,a.user_name, a.over, b.id, b.user_name, b.over
from user1 a
left join user2 b
on a.user_name = b.user_name;
右连接查询结果为:
select a.id,a.user_name, a.over, b.id, b.user_name, b.over
from user1 a
right join user2 b
on a.user_name = b.user_name;
交叉连接
交叉连接(cross join),又称笛卡尔连接(cartesian join)或叉乘,如果A和B是连个集合,他们的交叉连接即为AXB
不需要提供连接关键词从句
Join相关的SQL技巧
如何更新过滤条件中包含自身的表
如下,把user1中的over字段改为'齐天大圣'
,过滤条件使用了user1
UPDATE user1
SET
over = '齐天大圣'
WHERE
user1.user_name IN (SELECT
b.user_name
FROM
user1 a
INNER JOIN
user2 b ON a.user_name = b.user_name);
在MySQL中执行会出错:
Error Code: 1093. Table 'user1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
原因是:
if you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query (you can however reference a field from that outer table...)
参考:
- You can’t specify target table for update in FROM clause
- Error Code: 1093. Table ‘site_html’ is specified twice, both as a target for ‘UPDATE’ and as a separate source for data
所以正确的做法可以是:
update user1 a join (
select b.user_name
from user1 a inner join user2 b on
a.user_name = b.user_name
) b on a.user_name = b.user_name
set a.over = '齐天大圣';
或者
update user1 a inner join user2 b on a.user_name = b.user_name set a.over='齐天大圣';
使用join优化子查询
如下查询语句,未使用join
select a.user_name, a.over, (
select over from user2 b where a.user_name = b.user_name
) as over2
from user1 a;
查询结果为:
如果使用join
连接,查询的结果一样,但是所用的时间更少
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优化聚合子查询
参考SQL Select only rows with Max Value on a Column
如何查询出四人组中打怪最多的日期?
user_kills
表中的数据如下:
原始方法如下:
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);
实现分组选择数据
现在2张表
user1
表中的数据如下:
user_kills
表中的数据如下:
需求是:选择多条记录,比如阅读量最多的前几条
本例是每个人杀怪最多的头两天
对"孙悟空"
而言查询语句如下:
select a.id, a.user_name, b.timestr, b.kills
from user1 a join user_kills b
on a.id = b.user_id
where user_name = '孙悟空'
order by b.kills desc
limit 2;
查询结果为:
查询"猪八戒"
时,把user_name
换成"猪八戒"
即可。
但这样做存在着一些问题:
1.如果分类或是用户很多的情况下则需要多次执行同一查询
2.增加应用程序同数据库的交互次数
3.增加了数据库执行查询的次数,不符合批处理的原则
4.增加了网络流量
优化方式一:
优化方式二:
select d.user_name, c.timestr, 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;
结果为:
其中如下的查询结果如下
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