MySQL几个小用法

1. 查询重复记录

select user_name,count(*) as count from user_table group by user_name having count>1;

2. 删除重复记录, 只保留一条记录:

DELETE FROM novel WHERE id NOT IN(SELECT * FROM(SELECT id FROM novel GROUP BY title)AS b) 

3. 查询结果加入临时自增ID

 SELECT @rownum:=@rownum+1 AS rownum, book_chapters_list.* FROM (SELECT @rownum:=0) r, book_chapters_list where book_name='书名'; 

4. 重置ID

    alter table tablename drop column id;
    alter table tablename add id mediumint(8) not null primary key auto_increment first;

5.根据条件分组, 更新序号

SET @rowno := 0;
SET @pid := 0;
UPDATE t_order tld
JOIN (
 SELECT
  tmp.`id` AS tmp_id,
 IF
  ( @pid <> tmp.`member_id`, @rowno := 1, @rowno := @rowno + 1 ) AS rowno,
 IF
  ( @pid <> tmp.`member_id`, @pid := tmp.`member_id`, @pid ) AS pid 
 FROM
  ( SELECT * FROM t_order WHERE pay_status = 'PAID' ORDER BY member_id ASC, create_time ASC ) tmp 
 ) AS a ON tld.id = a.tmp_id 
 SET tld.recharge_num = a.rowno;