MYSQL函数、高级应用

-- 创建用户

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);
出来的顺序就是指定的顺序了

 

 

查询日期 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 type
from hnmj_game.tb_player_props_log 
where transaction_type in (9,10,11,35) and player_id=50000013
group 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=COMPACT
PARTITION 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;