MySQL开发技巧学习笔记一

本文内容来自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