MySQL常用技巧

mysql中group by分组后查询无数据补0

MySQL对sum()字段进行条件筛选:having

显示按日期汇总且内数量大于10的记录。
SELECT date, count(*) as num FROM tbName GROUP BY date HAVING num>10;
在这里，我们不能用where来筛选超过10的记录，因为表中不存在这样一条记录。相反，having子句可以让我们筛选成组后的各组数据

count(if)

SELECT count(distinct(user_id) , COUNT(if(status=2,true,null)) as succ_num, COUNT(if(status=20,true,null)) as refund_num FORM trade
WHERE ctime>=unix_timestamp()-600;

常用SQL语句

# 创建表
CREATE TABLE tb_test (
id bigint(21) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
name varchar(100) NOT NULL DEFAULT '' COMMENT '名称',
id_card varchar(40) NOT NULL DEFAULT '' COMMENT '身份证号',
user_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
create_time int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
is_del tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否禁用',
PRIMARY KEY (id),
KEY idx_user (name,user_id),
UNIQUE KEY `idx_id_card` (`id_card`),
KEY `idx_created` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户信息';

预估好数据将来的量，尽量往大理想，如使用 bigint 代替 int

查询昨天

from_unixtime(created, '%Y-%m-%d %H:%i:%s') >= adddate(curdate(), -1) and
from_unixtime(created, '%Y-%m-%d %H:%i:%s') < curdate()

复制表或数据结构

create table t1_copy as select * from t1 where 1=2

或者 create table t1_copy as select * from t1 limit 0

缺少t1表（源表）的索引信息而只有表结构相同。(如果要复制数据，去掉limit 0即可，也就是查询到的所有数据复制)

as 用来创建相同表结构并复制源表数据。
like 用来创建完整表结构和全部索引。

注意：两种方式在复制表的时候是不会复制权限对表的设置。比如说原本对表A做了权限设置，复制表AA后，表AA并不具备类似于表A的权限。