mysql常用查询

显示行号

SELECT

@rowno:=@rowno+1 as rowno,

r.*

from t_article r,(select @rowno:=0) t

 

 

格式化字符串

SELECT LPAD('12341', 6 , 0) // 保留6位，不足补0

 

 

清空表

truncate table 表名

 

 

表解锁

select * from information_schema.innodb_trx;

kill 419

 

 

分数转小数

set @fraction='2/10';

SELECT SUBSTRING_INDEX(@fraction,'/',1) / SUBSTRING_INDEX(@fraction,'/',-1)

 

 

GROUP_CONCAT 指定分隔符

GROUP_CONCAT （字段 SEPARATOR ‘，’）

 

 

mysql查询表字段名称，字段类型

 

select column_name,column_comment,data_type 

from information_schema.columns 

where table_name='查询表名称' and table_schema='数据库名称'

 

 

删除重复数据

DELETE consum_record

FROM

consum_record,

(

SELECT

min(id) id,

user_id,

monetary,

consume_time

FROM

consum_record

GROUP BY

user_id,

monetary,

consume_time

HAVING

count(*) > 1

) t2

WHERE

consum_record.user_id = t2.user_id

and consum_record.monetary = t2.monetary

and consum_record.consume_time = t2.consume_time

AND consum_record.id > t2.id;

 

 

求已过时间

timestampdiff(hour, t.CREATE_TIME_,now()) pastTimeHour,

@sec := timestampdiff(second, t.CREATE_TIME_, now()) pastTime2,

concat(@a:=(@sec div (60*60*24)),'天', @b:=(@sec div (60*60))%24, '小时', @c:=(@sec div 60)%60, '分', @d:=@sec%60, '秒') t,

((@a*24+@b)*60+@c)*60+@d a,

 

timestampdiff(hour, t.CREATE_TIME_,now()) pastTimeHour,

@sec := timestampdiff(second, t.CREATE_TIME_, now()) pastTimeSecond,

concat(@sec div (60*60*24),'天', (@sec div (60*60))%24, '小时', (@sec div 60)%60, '分', @sec%60, '秒') pastTime,