-- 创建用户
CREATE USER 'wangjieming'@'192.168.%.%' IDENTIFIED BY "fullshare";
-- 授权
grant select, insert, update, delete,CREATE,DROP on fullshare_campaign.* to fullshare_jiahao@'192.168.%.%';
-- 创建并授权
GRANT Select ON fullshare_eberp.* TO Elaine@'%' IDENTIFIED BY "32f@^frkt";
-- 删除用户
drop user uElaine@'%';
查询表中的字段名：select COLUMN_NAME from information_schema.COLUMNS where table_name = 'table_name';
将13位的时间戳转换为时间：FROM_UNIXTIME(created/1000)
增加排序字符：
set @num=0
select @num:=@num+1,name from user;
提取字符串中两字符间的内容
SELECT SUBSTRING(
sentence,
(LOCATE('<BWACNAME>', sentence) + LENGTH('<BWACNAME>')),
LOCATE('</BWACNAME>', sentence) - (LOCATE('<BWACNAME>', sentence) + LENGTH('<BWACNAME>'))
)
FROM (SELECT '<BWACNAME>com.dimeng.p2p.S61.entities.T6141@67a4dffd</BWACNAME>' AS sentence) temp
插入一个字段，并按顺序排列（比如原来排到5，那这条插入的就是6）
insert into tb_deposit_commodity_161208
select @max_id:=@max_id+1 as id,
from (select @max_id:=max(id) from tb_deposit_commodity_161208) as b
统计本月：BETWEEN DATE_SUB(@record_date,INTERVAL DAY(@record_date)-1 DAY)+ INTERVAL 0 second and (LAST_DAY(@record_date)+ INTERVAL 1 day) - INTERVAL 1 second
统计本周：BETWEEN DATE_ADD(@record_date,INTERVAL -WEEKDAY(@record_date) DAY)+ interval 0 second and (@record_date+interval 1 day)-interval 1 second
按照IN中字段排序：
SQL: select * from table where id IN (3,6,9,1,2,5,8,7);
这样的情况取出来后，其实，id还是按1,2,3,4,5,6,7,8,9,排序的，但如果我们真要按IN里面的顺序排序怎么办？SQL能不能完成？是否需要取回来后再foreach一下？
其实可以这样
sql: select * from table where id IN (3,6,9,1,2,5,8,7) order by field(id,3,6,9,1,2,5,8,7);
出来的顺序就是指定的顺序了
这样的情况取出来后，其实，id还是按1,2,3,4,5,6,7,8,9,排序的，但如果我们真要按IN里面的顺序排序怎么办？SQL能不能完成？是否需要取回来后再foreach一下？
其实可以这样
sql: select * from table where id IN (3,6,9,1,2,5,8,7) order by field(id,3,6,9,1,2,5,8,7);
出来的顺序就是指定的顺序了
查询日期 DATE_FORMAT(date,format)
ag：DATE_FORMAT(d.F06,'%Y-%m')='2015-12'
前30：LIMIT 30
select F01,F02,YEAR(NOW())-YEAR(a.F08),F08 from S61.T6141 a 计算年龄
b.create_time >= CURDATE() + interval 0 second and b.create_time <= now()
（获取当天的00:00:00） 整一句代表，今天00:00:00到现在
SELECT (@rowNO := @rowNo+1) AS rowno FROM tb_platform_snapshot, (SELECT @rowNo := 0)b; 加序列
select rela_id,count(1) as rela_count,case GROUP_CONCAT(transaction_type order by transaction_type) when '11,35' then 'myself' when '9,10,11,35' then 'other' when '9,10' then 'agent_cards' else GROUP_CONCAT(transaction_type order by transaction_type) end as typefrom hnmj_game.tb_player_props_log where transaction_type in (9,10,11,35) and player_id=50000013group by rela_id
sql_cache意思是说，查询的时候使用缓存。
select sql_cache name from tb_player
分区：
CREATE TABLE `tb_open_deal_detail_log` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`deal_id` bigint(20) NOT NULL,`game_deal_id` bigint(20) NOT NULL,`cur_deal` int(11) NOT NULL,`deal_data` longtext NOT NULL,`play_code` bigint(20) NOT NULL,`begin_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',`end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',`create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',PRIMARY KEY (`id`,create_time),UNIQUE KEY `uk_deal_cur_id` (`deal_id`,`cur_deal`,create_time),KEY `create_time_key` (`create_time`) USING BTREE,KEY `idx_play_code` (`play_code`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACTPARTITION BY RANGE (UNIX_TIMESTAMP(create_time))(PARTITION p1702 VALUES LESS THAN (UNIX_TIMESTAMP('2017-03-01')) ENGINE = InnoDB,PARTITION p1703 VALUES LESS THAN (UNIX_TIMESTAMP('2017-04-01')) ENGINE = InnoDB,PARTITION p1704 VALUES LESS THAN (UNIX_TIMESTAMP('2017-05-01')) ENGINE = InnoDB,PARTITION p1705 VALUES LESS THAN (UNIX_TIMESTAMP('2017-06-01')) ENGINE = InnoDB,PARTITION p1706 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-01')) ENGINE = InnoDB,PARTITION p1707 VALUES LESS THAN (UNIX_TIMESTAMP('2017-08-01')) ENGINE = InnoDB,PARTITION p1708 VALUES LESS THAN (UNIX_TIMESTAMP('2017-09-01')) ENGINE = InnoDB,PARTITION p1709 VALUES LESS THAN (UNIX_TIMESTAMP('2017-10-01')) ENGINE = InnoDB,PARTITION p1710 VALUES LESS THAN (UNIX_TIMESTAMP('2017-11-01')) ENGINE = InnoDB,PARTITION p1711 VALUES LESS THAN (UNIX_TIMESTAMP('2017-12-01')) ENGINE = InnoDB,PARTITION p1712 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01')) ENGINE = InnoDB,PARTITION p1801 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-01')) ENGINE = InnoDB,PARTITION p1802 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-01')) ENGINE = InnoDB,PARTITION p1803 VALUES LESS THAN (UNIX_TIMESTAMP('2018-04-01')) ENGINE = InnoDB,PARTITION p1804 VALUES LESS THAN (UNIX_TIMESTAMP('2018-05-01')) ENGINE = InnoDB,PARTITION p2000 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) ;
-- 当最小分区存在的情况下，删除7天前的数据
select count(1) into @del_count from information_schema.`PARTITIONS` where TABLE_NAME='tb_player_action_day_snapshot' and TABLE_SCHEMA='box_gamesnapshot' and PARTITION_NAME=@del_p;