mysql开发技巧

开发技巧一

• 使用join从句
  1.内连接–INNER JOIN—-取得表中公共的记录
  select m.id,m.username,f.id as fid,f.username as fname FROM mokey AS m INNER JOIN mokey_friends AS f ON m.username = f.username;

  1. 左外连接–LEFT OUTER JOIN – 取左边表全部，右边不存在为null
     select M.,F. FROM mokey AS m LEFT OUTER JOIN mokey_friends AS f ON m.id = f.id;
  2. 右外连接 –RIGHT OUTER JOIN–取右边表全部，左边不存在为null
     select M.,F. FROM mokey AS m RIGHT OUTER JOIN mokey_friends AS f ON m.id = f.id where m.id IS NOT NULL;
     
     1. 全连接 – FULL join – 左连接 union all 右连接
        select M.* FROM mokey AS m left JOIN mokey_friends AS f ON m.id = f.id UNION ALL select M.* FROM mokey AS m right JOIN mokey_friends AS f ON m.id = f.id;
     2. 笛卡尔连接 –cross join
        select M.,F. FROM mokey AS m CROSS JOIN mokey_friends AS f ;

• 子查询

  1. 优化子查询
  2. 优化聚合查询
  3. 分组选择

开发技巧二

1. 行列转化
   SELECT m.username,k.timestr,SUM(k.kills) FROM kills k INNER JOIN mokey m ON k.userid = m.id GROUP BY m.username ORDER BY m.username ASC;
   ————-行转列 case ————

SELECT SUM(CASE WHEN username =param1 THEN kills END) AS ‘param1 ‘,
SUM(CASE WHEN username =’param2 ’ THEN kills END) AS ‘param2 ‘,
SUM(CASE WHEN username =’param3 ’ THEN kills END) AS ‘param3 ’ FROM mokey m INNER JOIN kills k ON m.id = k.userid;
—–修改表名：RENAME TABLE mokey_friends to user111;
—序列化表
SELECT username ,REPLACE(SUBSTRING(SUBSTRING_INDEX(mobile,’,’,a.id),CHAR_LENGTH(SUBSTRING_INDEX(mobile,’,’,a.id-1))+1),’,’,”)AS mobile FROM tb_sequence a
CROSS JOIN(SELECT username,CONCAT(mobile,’,’) AS mobile,LENGTH(mobile)-LENGTH(REPLACE(mobile,’,’,”))+1 size FROM user11 b)b ON a.id <=b.size;

—–列转行
用case when then end 进行改造

1. 生成唯一序列号
   I.自动生成
   II.sql生成序列号
2. 处理重复数据
   
   1. 用group by having 判断是否重复
      delete from table1 as a inner join (select username ,count(),max(id) as id from a group by username having count()>1 ) b on a.username = b.username where a.id

开发技巧三

1. 子查询匹配多个值

SELECT a.username ,b.timestr,kills FROM user11 a INNER JOIN kills b ON a.id = b.userid
JOIN (SELECT userid ,max(kills) AS cnt FROM kills GROUP BY userid) c ON b.userid = c.userid and b.kills =c.cnt;
等价余
SELECT a.username ,b.timestr,kills FROM user11 a INNER JOIN kills b ON a.id = b.userid
WHERE (b.userid,b.kills) in( SELECT userid,MAX(kills) FROM kills GROUP BY userid)
2. 多值过滤

3.累计税类