MYSQL开发技巧(一)

sql开发技巧之一
正确使用sql的好处:
1.增加数据库处理效率,减少应用响应时间.
2.减少数据库服务器负载,增加服务器稳定性.
3.减少服务器间通讯的网络流量.
如何正确的使用Join从句
举例中使用的表如下:西天取经四人组VS悟空的朋友们
id       user_name
1 唐僧
2 猪八戒
3 孙悟空
4 沙僧


id        user_name
1 孙悟空
2 牛魔王
3 蛟魔王
4 鹏魔王
5 狮驼王


SQL标准中Join的类型
JOIN:
    1. 内连接(INNER)
2. 全外连接(FULL OUTER)
3. 左外连接(LEFT OUTER)
4. 右外连接(RIGHT OUTER)
5. 交叉连接(CROSS)

1.1 JOIN操作的类型----- Inner Join
内连接Inner join基于连接谓词将两张表(如A和B)的列组合在一起,产生新的结果表.(把两张表中公共的部分选取出来)
如图所示涂红的部分是Inner JOIN产生的结果表:

SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key=B.Key

取出user1表和user2表公共记录的数据:
SELECT
a.user_name
FROM
user1 a
INNER JOIN user2 b ON a.user_name = b.user_name
运行结果:

3.1 JOIN操作的类型-----　Left OUTER Join
A,B两张表进行左外连接时,那么结果集中将以左表(A表)为基础,其中包括A表的所有记录;
同时图形显示如下:

查询取经四人组中那些人不是悟空的结拜兄弟?
SELECT
a.user_name,b.user_name
FROM
user1 a
LEFT JOIN user2 b ON a.user_name = b.user_name
结果:

SELECT
a.user_name,b.user_name
FROM
user1 a
LEFT JOIN user2 b ON a.user_name = b.user_name
WHERE b.user_name is null 
结果:

4.1 Join操作的类型-----  Right Outer Join
A,B两张表进行右外连接时,那么结果集中将以右表(B表)为基础,其中包括B表的所有记录;
同时图形显示如下:

查询悟空的结拜兄弟中那些人没有去取经?
SELECT
a.user_name,
b.user_name
FROM
user1 a
RIGHT JOIN user2 b ON a.user_name = b.user_name
结果:

SELECT
a.user_name,
b.user_name
FROM
user1 a
RIGHT JOIN user2 b ON a.user_name = b.user_name
WHERE a.user_name is null 
结果:

2.1 Join操作的类型------  Full Join
Full Join 全连接,它实际上是左连接和右连接的合集;
Full Join 可以查询出所有在A表和B表中存放的数据
如图所示:

Full Join如何解决?
SELECT
a.user_name
FROM
user1 a
LEFT JOIN user2 b ON a.user_name = b.user_name
UNION ALL
SELECT
b.user_name
FROM
user1 a
RIGHT JOIN user2 b ON a.user_name = b.user_name
结果:

5.1 Join操作的类型------  Cross Join
交叉连接(cross join),又称笛卡尔连接(cartesian join)或叉乘(Product),如果A和B是两个集合,它们的交叉连接就记为: A*B;
使用交叉连接时不需要提供连接关键词的;
SELECT
a.user_name,
b.user_name
FROM
user1 a
CROSS JOIN user2 b;
结果:

二. 与Join相关的SQL技巧
1.1 如何更新使用过滤条件中包括自生的表?
情景:
把同时存在于取经四人组和悟空兄弟表中的记录的人在取经四人组表中的over字段更新为"齐天大圣"
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
)
以上这个sql在mysql中是不支持这种更新的.
错误如图所示:

使用Join来解决问题
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='齐天大圣';
运行结果:

2.1 使用JOIN优化子查询
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;
结果:

3.1 使用JOIN优化聚合子查询
引入一张新表,按日期记录四人组中每个人打怪的数量

问题: 如何查询出四人组中打怪最多的日期?
SELECT
a.user_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
);
结果:

验证:

问题:如何查询出四人组中打怪最多的日期?(避免子查询的方式)
SELECT
a.user_name,
b.timestr,
b.kills
FROM
user1 a
JOIN kills b ON a.id = b.user_id
JOIN 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
a.user_name,
b.timestr,
b.kills
FROM
user1 a
JOIN kills b ON a.id = b.user_id
WHERE user_name='猪八戒'
ORDER BY b.kills DESC
LIMIT 2;
结果:

问题: 1.如果分类或是用户很多的情况下则需要多次执行同一查询
2.增加应用程序同数据库的交互次数
3.增加了数据库执行查询的次数,不符合批处理的原则
4.增加了网络流量
  
优化方式1:

优化方式2:
SELECT
d.user_name,
c.timestr,
c.kills
FROM
(
SELECT
user_id,
timestr,
kills,
(
SELECT
COUNT(*)
FROM
kills b
WHERE
b.user_id = a.user_id
AND a.kills <= b.kills
) AS cnt
FROM
kills a
GROUP BY
user_id,
timestr,
kills
) c
JOIN user1 d ON c.user_id = d.id
WHERE
cnt <= 2;
结果: