MySQL语句技巧

1、查询时将时间戳格式化显示：

SELECT  FROM_UNIXTIME(1234567890, '%Y-%m-%d %H:%i:%S')  FROM  table_name

2、最高效的删除重复记录方法 ( 因为使用了ROWID)例子：

DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID) FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO);

3、查询一段时间内，每5分钟间隔分时在线数据统计(eventtime是时间戳)

    可以延伸统计一段时间内每10分钟、30分钟、1小时等时间段为分组的登录、付费、激活等各种数据。

SELECT  FROM_UNIXTIME(`eventtime`-`eventtime`% (5*60), '%Y-%m-%d %H:%i:%S')  AS stime, count(distinct uid) uids

FROM 20170828_online WHERE eventtime>=1503921000 AND eventtime<=1503925200

GROUP BY stime;

4、将两个表的查询结果合并成一行

select A.newusers,B.pay from (SELECT COUNT(DISTINCT uid) AS newusers FROM applogs.20171025_firstentry WHERE game=12 AND client=1) AS A,

(SELECT SUM(money)/100 AS pay FROM applogs.20171025_paylog WHERE eventdate=flogindate AND game=12 AND client=1 ) AS B;

5、列转行技巧

eg：统计id为1的记录数，id为2的记录数以及id为3的记录数。

SELECT COUNT(CASE WHEN id=1 THEN 1 ELSE NULL END ) AS `one_num`,COUNT(CASE WHEN id=2 THEN 1 ELSE NULL END ) AS `two_num`,COUNT(CASE WHEN id=3 THEN 1 ELSE NULL END ) AS `tree_num` FROM test;

表数据：

执行该语句的结果：

 

6、group_concat函数的使用方法

公式：group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

基础表格：（以5中的表格数据为例）

（1）以id分组，把name字段的值放在同一结果行，以逗号分隔(默认)

SELECT id,GROUP_CONCAT(NAME) AS id_name FROM test GROUP BY id;

结果：

（2）以id分组，把name字段的值放在同一结果行，以分号分隔

SELECT id,GROUP_CONCAT(NAME SEPARATOR ';') AS id_name FROM test GROUP BY id;

结果：

（3）以id分组，把去冗余的name字段的值放在同一结果行， 以逗号分隔

SELECT id,GROUP_CONCAT(DISTINCT `name`) AS id_name FROM test GROUP BY id;

结果：

（4）以id分组，把name字段的值放在同一结果行，逗号分隔，以name排倒序

SELECT id,GROUP_CONCAT(`name` ORDER BY `name` DESC) AS id_name FROM test GROUP BY id;

结果：

 

7、with rollup 的用法 (表格以5中的数据表为例)

SELECT `name`, SUM(id) FROM test.test GROUP BY NAME WITH ROLLUP
SELECT COALESCE(`name`, '总数') AS `name`, SUM(id) FROM test.test GROUP BY NAME WITH ROLLUP

结果：

 COALESCE函数说明：

　　select coalesce(a,b,c);

　　如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null。coalesce(a,b,c,d...)同理。

8、插入数据出现UNIQUE索引或PRIMARY KEY冲突时就使用更新（ON DUPLICATE KEY UPDATE 语法）

(1) INSERT INTO `sy`.`day` (id, name, phone) VALUES('666', 'xst', '10086') ON DUPLICATE KEY UPDATE name = VALUES(name),phone = VALUES(phone);

(2) INSERT INTO TABLE (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;  （UPDATE TABLE SET c=c+1 WHERE a=1;）

(3) INSERT INTO TABLE (a,b,c) VALUES  (1,2,3),(2,5,7),(3,3,6),(4,8,2) ON DUPLICATE KEY UPDATE b=VALUES(b);

(4)插入一行数据，单主键已经存在，则更新该主键的相关数据，但数据表中 level原先的数据等于0或者为空或者小于即将更新进去的 level 值的时候，就将新的值更新进去，否则不做处理。

INSERT IGNORE INTO test.test (uid,roleid,rolename,game,`server`,`level`) VALUES('2','00002','test2','1','1001','98') ON DUPLICATE KEY UPDATE
`level` = (CASE WHEN `level`=0 OR 'level'=NULL OR `level`<VALUES(`level`) THEN VALUES(`level`) ELSE `level` END)

9、查询一个用户是否连续 7天或者10天登录(通过两个日期字段计算其相差的天数)

SELECT eventdate FROM login WHERE user='xst' AND DATEDIFF(eventdate,firstlogindate)<=7 GROUP BY eventdate

SELECT COUNT(DISTINCT eventdate) AS days FROM login WHERE user='xst' AND DATEDIFF(eventdate,flogindate)<=7

10、查询某个字段的一个值出现过两次以上的记录

select * from did_table where did in (select did from did_table group by did having count(did)>1);

11、将一个表（B）的某个字段数据更新到另一个表（A）

UPDATE first_open A, open B SET A.uuid=B.uuid WHERE A.game=B.game AND A.did=B.did AND A.cid=B.cid AND A.aid=B.aid AND A.osid=B.osid and A.ip=B.ip and A.eventtime=B.eventtime

12、查询每个用户最后一条数据

select *,max(eventtime) from usertable group by uid;

select * from (select * from usertable order by eventtime desc) a group by uid;

SELECT eventdate,zid,sid,pid,osid,SUM(money) AS g_coin
FROM (SELECT MAX(eventtime) AS eventtime,eventdate,zid,sid,pid,osid,uid,money FROM usertable  GROUP BY uid) AS A
GROUP BY zid,sid,pid,osid  (对分组下对应用户的最新一行数据的money字段进行求和)

13、查询两个表某个字段的交集

（1）左表有，右表没有

SELECT A.finish_date,B.game,A.order_num,B.pay_num,B.pay_type,A.company FROM mydb.mytable AS A LEFT JOIN mydb.testtab AS B ON A.order_num = B.pay_num WHERE B.game=63 AND B.pay_num IS NULL

（2）右表有，左表没有

SELECT B.game,A.order_num,B.pay_num,B.pay_type FROM mydb.mytable AS A RIGHT JOIN mydb.testtab AS B ON A.order_num = B.pay_num WHERE B.game=63 AND A.order_num IS NULL