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

评论

1 楼 yunmoxue 2011-02-24  

good!