参考：
1、 MySQL开发技巧
2、 MySQL开发技巧2
3、MySQL开发技巧3
DDL:数据定义语言
TPL:事务处理语言
DCL:数据控制语言
DML:数据操作语言（CRUD）
内连接INNER全外连接(不支持)FULL OUTER左外连接LEFT OUTER右外连接RIGHT OUTER交叉连接(又称笛卡尔积)CROSS
全外连接(FULL OUTER)实现方式：
SELECT A.*,B.* FROM table A LEFT JOIN table B ON B.Key = A.Key
UNION ALL
SELECT A.*,B.* FROM table B RIGNT JOIN table A ON A.Key = B.Key
UPDATE table A
JOIN table B ON A.key = B.key
SET A.column=set_value;
准备表：
CREATE TABLE `users` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL, `password` varchar(32) NOT NULL, `age` tinyint(3) unsigned NOT NULL DEFAULT '10', `sex` tinyint(1) DEFAULT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8
CREATE TABLE `province` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `pname` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
第1种子查询：
select A.id,A.username,(select pname from province B WHERE A.username=B.pname) as pname from users A；
show profiles;
第2种外连接查询；
select A.id,A.username,B.pname from users A left join province B on A.username=B.pname;
show profiles;
所以第2种比较好。
查询每人打怪最多的两条记录；
方法1：
WITH tmp AS(
SELECT a.user_name,b.timestr,b.kills, ROW_NUMBER() over(partition by a.user_name order by b.kills) cnt
FROM user1 a
JOIN user_kills b ON a.id = b.user_id
)select * from tmp where cnt <=2;
方法2：
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;
1. Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。
2.确定支持show profile 后，查看profile是否开启，数据库默认是不开启的。变量profiling是用户变量，每次都得重新启用。
查看方法： show variables like "%pro%";
设置开启方法： set profiling = 1;
再次查看show variables like "%pro%"; 已经是开启的状态了。
3.可以开始执行一些想要分析的sql语句了，执行完后，show profiles; 即可查看所有sql的总的执行时间。
show profile for query 1 即可查看第1个sql语句的执行的各个操作的耗时详情。
show profile cpu, block io, memory,swaps,context switches,source for query 6;可以查看出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等
show profile all for query 6 查看第6条语句的所有的执行信息。
测试完毕后，关闭参数：
mysql> set profiling=0方法二： timestampdiff来查看执行时间。
这种方法有一点要注意，就是三条sql语句要尽量连一起执行，不然误差太大，根本不准
set @d=now(); select * from comment; select timestampdiff(second,@d,now());如果是用命令行来执行的话，有一点要注意，就是在select timestampdiff(second,@d,now());后面，一定要多copy一个空行，不然最后一个sql要你自己按回车执行，这样就不准了。
场景：
1、报表统计
2、汇总显示
set names 'gbk';
mysql> select a.user_name,sum(kills) -> from user1 a -> join user_kills b on a.id =b.user_id -> group by a.user_name; +-----------+------------+ | user_name | sum(kills) | +-----------+------------+ | 孙悟空 | 47 | | 沙僧 | 9 | | 猪八戒 | 24 | +-----------+------------+ 3 rows in set (0.01 sec) --方法1： select * from( select sum(kills) as '沙僧' from user1 a join user_kills b on a.id =b.user_id and a.user_name='沙僧' ) a cross join( select sum(kills) as '猪八戒' from user1 a join user_kills b on a.id =b.user_id and a.user_name='猪八戒' ) b cross join( select sum(kills) as '孙悟空' from user1 a join user_kills b on a.id =b.user_id and a.user_name='孙悟空' ) c ; --方法2： select sum(case when user_name='孙悟空' then kills end) as '孙悟空' ,sum(case when user_name='猪八戒' then kills end) as '猪八戒' ,sum(case when user_name='沙僧' then kills end) as '沙僧' from user1 a join user_kills b on a.id = b.user_id ; mysql> select * -> from( -> select sum(kills) as '沙僧' from user1 a join user_kills b on a.id =b.user_id and a.user_n ame='沙僧' -> ) a -> cross join( -> select sum(kills) as '猪八戒' from user1 a join user_kills b on a.id =b.user_id and a.user _name='猪八戒' -> ) b -> cross join( -> select sum(kills) as '孙悟空' from user1 a join user_kills b on a.id =b.user_id and a.user _name='孙悟空' -> ) c -> ; +------+--------+--------+ | 沙僧 | 猪八戒 | 孙悟空 | +------+--------+--------+ | 9 | 24 | 47 | +------+--------+--------+ 1 row in set (0.01 sec) mysql> select sum(case when user_name='孙悟空' then kills end) as '孙悟空' -> ,sum(case when user_name='猪八戒' then kills end) as '猪八戒' -> ,sum(case when user_name='沙僧' then kills end) as '沙僧' -> from user1 a -> join user_kills b on a.id = b.user_id -> ; +--------+--------+------+ | 孙悟空 | 猪八戒 | 沙僧 | +--------+--------+------+ | 47 | 24 | 9 | +--------+--------+------+ 1 row in set (0.00 sec)
