mysql查询重复数据 以及导入导出数据

表test结构
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| code | int(10) | NO | | 0 | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
数据字段
select * from test;
+----+------+------+
| id | code | name |
+----+------+------+
| 1 | 1 | aaa |
| 2 | 2 | aaa |
| 3 | 1 | b |
| 4 | 3 | b |
| 5 | 2 | b |
| 6 | 1 | c |
| 7 | 4 | c |
+----+------+------+
去除code的重复字段
select * from test[表名称] where grou by id[字段名称] having count(id[统计重复数据的字段])；
mysql> select * from test group by code having count(code);
+----+------+------+
| id | code | name |
+----+------+------+
| 1 | 1 | aaa |
| 2 | 2 | aaa |
| 4 | 3 | b |
| 7 | 4 | c |
+----+------+------+
成功
去除name字段的重复值
mysql> select * from test group by name having count(name);
+----+------+------+
| id | code | name |
+----+------+------+
| 1 | 1 | aaa |
| 3 | 1 | b |
| 6 | 1 | c |
+----+------+------+
还可以利用distinct关键字

去除name的重复 并通过ID 统计各个重复的数量
mysql> select * , count(distinct id) from test group by name;
+----+------+------+--------------------+
| id | code | name | count(distinct id) |
+----+------+------+--------------------+
| 1 | 1 | aaa | 2 |
| 3 | 1 | b | 3 |
| 6 | 1 | c | 2 |
+----+------+------+--------------------+

去除CODE的重复 并通过ID 统计各个重复的数量
mysql> select * , count(distinct id) from test group by code;
+----+------+------+--------------------+
| id | code | name | count(distinct id) |
+----+------+------+--------------------+
| 1 | 1 | aaa | 3 |
| 2 | 2 | aaa | 2 |
| 4 | 3 | b | 1 |
| 7 | 4 | c | 1 |
+----+------+------+--------------------+
显示CODE字段有重复的数据的所有信息
mysql> select * from test where id in(select id from test group by code having c
ount(code)>1);
+----+------+------+
| id | code | name |
+----+------+------+
| 1 | 1 | aaa |
| 2 | 2 | aaa |
+----+------+------+
也可以为
mysql> select id from test group by code having c
ount(code)>1);
+----+------+------+
| id | code | name |
+----+------+------+
| 1 | 1 | aaa |
| 2 | 2 | aaa |
+----+------+------+

自身左连接
select t1.*,t2.id[字段] from test t1 left join test t2 on t1.id= t2.id[关联的字段]

导入数据文件用
mysql>source D:/tbl_usr_shop_2010-8-29.sql;

导出数据库 (包括数据库结构)

mysqldump -h localhost -u root -p databasename > /home/test/test.sql;

导出数据库结构)

mysqldump --opt -d database -u root -p > /home/test/test.sql;

导出数据

mysqldump -t -u root -p databasename > /home/test/test.sql;

导出单个数据表结构 （不包含数据）
mysqldump -h localhost -uroot -p123456 -d database table > dump.sql