将某个表字段按照排序进行字符和递增数值串拼
SET @num = 0;
UPDATE tab SET realname=CONCAT('yena',(@num:=@num+1)) WHERE user_id in (SELECT id FROM tab WHERE mobile >=xxx AND mobile<=xxx ORDER BY id)
按照指定排序修改数据库的主键id，rownum+1表示的是起始id的开始位置。
update tab
inner join
(SELECT @rownum := @rownum + 1 AS rank, tab.id
FROM tab, (SELECT @rownum := 0) r
order by create_time asc) c on c.id = tab.id
set tab.id = c.rank;
修改日期并且对小时做调整。
update tab set create_time= ADDTIME(DATE('2020-12-19') + interval +9 hour,time(create_time)) WHERE create_time like '2020-12-19 04%' ORDER BY id DESC LIMIT 150;
按照年月日进行分组查询。
select date_format(create_time, '%Y-%m-%d') dat, count(*) coun from tab GROUP BY date_format(create_time, '%Y-%m-%d');
截取字符串中 = 号后面的所有字符串，1代表=号前面的所有字符串。
SELECT count(live_url),SUBSTRING_INDEX(live_url,'=',-1) FROM tab GROUP BY live_url
用随机值从另外一个表里取符合算法的参数，floor表示向下取整。在用随机数进行取值时，一次最好取一个，否则有可能取出来的一批数据会存在连续，如limit为10的话，就会取到大于这个值的十条数据，这10条数据实际是连续的。
SELECT * FROM `tab` WHERE user_id >= (SELECT floor(RAND() * (SELECT MAX(user_id) FROM `tab`))) LIMIT 1
对求和数据再次求和
select sum(a.count_sum) FROM (SELECT share_url,count(share_url) as count_sum FROM tab GROUP BY share_url) a
多次连表查询
SELECT
`tab1`.`name`,
`tab1`.`image_url`,
`tab1`.`price`,
`tab2`.`asset_id`,
`tab2`.`item_id`,
`tab3`.`csgo_float`,
`tab3`.`unlock_time`,
`tab3`.`user_id`,
`tab4`.`nickname`,
count( tab2.item_id ) AS itemnum,
count( IF ( tab3.user_id = - 1 OR tab3.user_id IS NULL, 1, NULL ) ) AS systemnum,
count( IF ( tab3.user_id > - 1 AND tab3.user_id IS NOT NULL, 1, NULL ) ) AS usernum
FROM
`tab1`
LEFT JOIN `tab2` ON `tab1`.`id` = `tab2`.`steam_inventory_id`
LEFT JOIN `tab3` ON `tab1`.`item_id` = `tab3`.`id`
LEFT JOIN `tab4` ON `tab1`.`steam_id` = `tab4`.`steam_id`
WHERE
`tab3`.`user_id` = - 1 AND `tab3`.locked=1
GROUP BY
`tab2`.`item_id`
ORDER BY
`tab2`.`id` DESC
LIMIT 10 OFFSET 0
从其他表拿数据并插入到指定表
INSERT INTO Database1.`tab1`(flag,appid,`status`,user_id,item_id,price,original_id,create_time,create_user_id,create_user_ip)
SELECT 1001,appid,'normal',1,11796994,0,0,0,0,0 FROM Database1.`tab2` WHERE appid=65033 LIMIT 11;
为了把时间戳转换为方便查看的日期，使用from_unixtime(时间戳字段)来格式化
SELECT id,`mode`,`status`,game_time,update_time ,FROM_UNIXTIME(update_time)FROM tab WHERE tournament_schedule_id=100236377 ORDER BY update_time desc;
统计某个字段特定参数值的数量：
SELECT `item_id`, COUNT( IF ( type = 1, 1, NULL ) ) AS dnum,COUNT( IF ( type = 2, 1, NULL ) ) AS pnum FROM `tab` WHERE `user_id` = 0 AND `status` = 1 AND `item_id` IN ( '10707901', '10002801', '11783305', '11783224', '11264224' ) GROUP BY `item_id`
将不规律的字段值按一定规律重新赋值并重新排序
SELECT * FROM tab1 AS a LEFT JOIN tab2 AS b ON a.id=b.expert_item_id LEFT JOIN expert AS d ON a.expert_id=d.id WHERE a.category='dota' AND a.gold !=0 AND a.`status` in (1,2,3,4,5) AND a.updated_at BETWEEN DATE_SUB(NOW(),INTERVAL 3 MONTH) AND NOW() ORDER BY (CASE WHEN a.`status`=1 THEN 2 WHEN a.`status`=2 THEN 1 WHEN a.`status`=3 THEN 0 WHEN a.`status`=4 THEN 0 WHEN a.`status`=5 THEN 0 END ) DESC,(CASE WHEN(SELECT COUNT(*)FROM tab WHERE user_id=8757456 AND expert_id in(SELECT id FROM expert))>0 THEN 2WHEN (SELECT COUNT(*)FROM tab WHERE user_id=8757456 AND expert_id in(SELECT id FROM expert))=0 THEN 1 END)DESC,d.sort DESC,a.created_at DESC,a.expert_id DESC;
网友评论