常用 SQL 汇总
使用索引注意事项
- 使用like关键字时，前置%会导致索引失效。
- 使用null值会被自动从索引中排除，索引一般不会建立在有空值的列上。
- 使用or关键字时，or左右字段如果存在一个没有索引，有索引字段也会失效。
- 使用!=操作符时，将放弃使用索引。因为范围不确定，使用索引效率不高，会被引擎自动改为全表扫描。
- 不要在索引字段进行运算。
- 在使用复合索引时，最左前缀原则，查询时必须使用索引的第一个字段，否则索引失效；并且应尽量让字段顺序与索引顺序一致。
- 避免隐式转换，定义的数据类型与传入的数据类型保持一致。
- 使用多列字段排序的时候，应当尽量使用同样的排序规则，否则会导致索引失效。
- 使用 lower 函数进行不区分大小写比较时，应当先检查字段的排序规则是否已经为 utf8_general_ci 不区分大小写排序，如果已经是，则避免使用 lower 转小写进行比较，因为 lower 会导致索引失效。
【事务】
mysql的MyISAM引擎是不支持事务的，需要更改为Innodb
alter table table_name engine=InnoDB;【复杂统计】
insert into web_statis(user,create_time,stone,dirt,orecoal,ironore,goldore,diamond,allore) select al.*,(stone+dirt+orecoal+ironore+goldore+diamond) as allore from (select cu.user,
'2015-2-9 10:09:18' as create_time,(case when c1 is null then 0 else c1 end) as stone,(case when c2 is null then 0 else c2 end) as dirt,(case when c3 is null then 0 else c3 end) as orecoal
,(case when c4 is null then 0 else c4 end) as ironore,(case when c5 is null then 0 else c5 end) as goldore,(case when c6 is null then 0 else c6 end) as diamond from co_user cu
left join (select count(*) c1,user from co_block where type in(1,4) and time >=1423353600 and time < 1423440000 group by user) tc1 on cu.rowid = tc1.user
left join (select count(*) c2,user from co_block where type in(2,3) and time >=1423353600 and time < 1423440000 group by user) tc2 on cu.rowid = tc2.user
left join (select count(*) c3,user from co_block where type = 16 and time >=1423353600 and time < 1423440000 group by user) tc3 on cu.rowid = tc3.user
left join (select count(*) c4,user from co_block where type = 15 and time >=1423353600 and time < 1423440000 group by user) tc4 on cu.rowid = tc4.user
left join (select count(*) c5,user from co_block where type = 14 and time >=1423353600 and time < 1423440000 group by user) tc5 on cu.rowid = tc5.user
left join (select count(*) c6,user from co_block where type = 56 and time >=1423353600 and time < 1423440000 group by user) tc6 on cu.rowid = tc6.user) al【根据查询结果update】
update A表 a INNER JOIN(select 字段1,字段2 from B表 b)t on t.字段2 = a.字段2 set a.字段3 = "XXX";格式：update table_a inner join table_b on xxx = xxx set table_a.xxx = "xxx"
【反向like查询】
SELECT id,keywords FROM wx_keywords WHERE '百度一下' LIKE CONCAT('%',keywords,'%');【字段数据转移】
update Devices as A left join Devices as B on A.ParentCode = B.Code set A.ParentID = B.ID;【区分大小写查询】
select * from wx_news where url like binary("%appid%");【时间偏移】
时间字段 <= date_add(now(), interval - 6 MONTH)
MySQL 添加索引
1.添加PRIMARY KEY（主键索引） www.2cto.com
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE (
`column`
)
3.添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )【从小到大排序，0在最后】
select * from test order by seq=0,seq;【MySQL计算时间差】
SELECT TIMESTAMPDIFF(MONTH,'2009-10-01','2009-09-01');interval可是：SECOND 秒、MINUTE 分钟、HOUR、DAY 天、MONTH 月、YEAR 年
【按月统计数据】
select DATE_FORMAT(create_date,'%Y年%m月') months,sum(amount) amount from p2p_investment where transfer is null group by months;【替换URL域名】
update p2p_ad set url = concat("https://www.51checai.com/", substr(url,LENGTH("http://test.51checai.com/")+1)) where url like "http://test.51checai.com/%";
update `p2p_borrowing_material` set large = concat("https://img-51checai.b0.upaiyun.com/", substr(large,LENGTH("https://img.51checai.com/")+1)) where large like "https://img.51checai.com/%";【推荐数据修复】
查询：
select id,create_date,`referral_setting`,date_add(create_date, interval +180 day),
CONCAT("{\"referralAmount\":10000,\"referralGift\":\"31\",\"referralGiftTwo\":\"32,33\",\"paymentTime\":\"2016-04-01\",\"finishTime\":\""
,DATE_FORMAT(date_add(create_date, interval +180 day),'%Y-%m-%d')
,"\",\"feeRate\":0.01}")
from p2p_member where create_date >= "2016-04-01" and create_date < "2016-09-22" and `referrer` is not null order by id desc;更新：
update p2p_member set `referral_setting` =
CONCAT("{\"referralAmount\":10000,\"referralGift\":\"31,38\",\"referralGiftTwo\":\"32,33,39\",\"paymentTime\":\"2016-04-01\",\"finishTime\":\""
,DATE_FORMAT(date_add(create_date, interval +180 day),'%Y-%m-%d'),"\",\"feeRate\":0.01}")
where create_date >= "2016-09-22" and `referrer` is not null;【指定数据互换】
update test t1
left join test t2 on t1.id = ((select 1+2)-t2.id )
set t1.position = t2.position
where t1.id in (1,2);
