大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

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
未完待续...