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;