MYSQL随笔

1.mysql查询重复记录方法

select * from table1
where t_id in (select t_id from table1 group by t_id having count(t_id) > 1);

select * from table1
where t_id in (select t_id from table1 group by t_id having count(t_id) > 1);

2.mysql新建用户及分配数据库权限

CREATE USER admin123 IDENTIFIED BY 'pwd';

GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost' IDENTIFIED BY 'pwd';

3.mysql将一张表部分字段数据更新到另一张关联表

UPDATE  tablename1 as  B, tablename2 as C
SET B.gender=C.gender
WHERE C.id=B.id

4.mysql删除重复数据,只保留一行

• 用户表members member_name存在重复值, 删除重复值,只保留id最大一行

DELETE FROM members WHERE member_name IN 
	( SELECT t.member_name FROM ( SELECT member_name FROM members GROUP BY member_name HAVING count(1) > 1 ) t )
		AND member_id NOT IN (SELECT dt.mindeptno FROM ( SELECT max(member_id) AS mindeptno FROM members GROUP BY member_name HAVING count(1) > 1 ) dt)