MySQL编程技巧_MySQL学习笔记---MySQL开发技巧

SQL语句分类

DDL：数据定义语言 --- CREATE、ALTER、DROP、TRUNCATE

TPL：事务处理语言 --- COMMIT、ROLLBACK、SAVEPOINT、SET TRANSACTION

DCL：数据控制语言 --- GRANT、REVOKE

DML：数据操作语言 --- SELECT、UPDATE、INSERT、DELETE

join从句—内连接

join从句：： inner | full outer | left outer | right outer | cross

内连接inner join:

select a.user_name,a.over,b.over from user1 a join user2 b on a.user_name=b.user_name;

LEFT OUTER JOIN：包含左表中的所有数据，当某个连接谓词不在右表中时，新生成的表的右表字段为NULL。对左表数据的信息进行扩展，增加右表中的字段，当某个连接谓词不在右表中时，新生成的表的右表字段为NULL。

使用LEFT OUTER JOIN对NOT IN 进行优化。

SELECT select_sxpressions

FROM TABLE A LEFT OUTER JOIN TABLE B ON A.key=B.key

WHERE B.key IS NULL;

表A中不包含表B中的数据。

SELECT select_sxpressions

FROM TABLE A LEFT OUTER JOIN TABLE B ON A.key=B.key

WHERE B.key IS NOT NULL;

表A与表B的交集，相当于INNER JOIN。

忽然想起来有次面试问我如何用left join实现not in的功能

select A.内容,B.内容 from A left join B ON a.key=b.key where b.key is null

RIGHT OUTER JOIN：包含右表中的所有数据，当某个连接谓词不在左表中时，新生成的表的左表字段为NULL。对右表数据的信息进行扩展，增加左表中的字段，当某个连接谓词不在左表中时，新生成的表的左表字段为NULL。

使用RIGHT OUTER JOIN对NOT IN 进行优化。

SELECT select_sxpressions

FROM TABLE A RIGHT OUTER JOIN TABLE B ON A.key=B.key

WHERE A.key IS NULL;

表B中不包含表A中的数据。

SELECT select_sxpressions

FROM TABLE A RIGHT OUTER JOIN TABLE B ON A.key=B.key

WHERE A.key IS NOT NULL;

表A与表B的交集，相当于INNER JOIN。

MySQL 中 不会支持 full join

解决方法：采用左连接和右连接结合+ 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 a.user_name = b.user_name

join优化子查询技巧：

一般子查询写法：(数据小时，没有多大影响，如果数据量大时，则要消耗大量的查询)

select a.user_name , a.voer , (select over from user2 where a.user_name = b,user_name) as over2

from user1 a;

如果这两张表的记录相当多 那么这个子查询相当于对A标的每一条记录都要进行一次子查询。

join优化(左连接)后的写法：

select a.user_name , a.over , b.over from user1 a

left join user2 b on a.user_name = b.user_name

使用join + having优化聚合子查询:

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,btimestr,b.kills

having b.kills = max(c.kills);

分类聚合方式查询每一个用户某一个字段数据最大的两条数据：

select d.user_name ,c.ctimestr,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

如何进行行列转换

如何进行行列转换

需要进行行转列的场景：汇总显示

Paste_Image.png

Paste_Image.png

未完待续...