壹定发老虎机娱乐平台-医院检验员有什么不好

首页

AD联系:507867812

壹定发老虎机娱乐平台

时间:2019-11-13 00:26:46 作者:图库—新宝会 浏览量:92391

壹定发老虎机娱乐平台

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析,见下图

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL处理重复数据实例分析MySQL处理重复数据实例分析,见下图

MySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析MySQL处理重复数据实例分析,如下图

MySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL处理重复数据实例分析

如下图

MySQL处理重复数据实例分析,如下图

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux,见图

壹定发老虎机娱乐平台

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析

MySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析MySQL处理重复数据实例分析MySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析MySQL处理重复数据实例分析MySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

壹定发老虎机娱乐平台

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL处理重复数据实例分析

1.MySQL处理重复数据实例分析

MySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析MySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析

2.MySQL处理重复数据实例分析。

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析MySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

3.

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL处理重复数据实例分析MySQL处理重复数据实例分析MySQL处理重复数据实例分析MySQL处理重复数据实例分析MySQL处理重复数据实例分析MySQL处理重复数据实例分析MySQL处理重复数据实例分析

4.

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析MySQL处理重复数据实例分析

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linuxMySQL处理重复数据实例分析。壹定发老虎机娱乐平台

展开全文
相关文章
澳门美高梅国际

MySQL处理重复数据实例分析

传奇国际娱乐APP

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux....

AG视讯在哪里靠谱

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux....

拈花网移动版久久

MySQL处理重复数据实例分析....

恒达股东

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux....

相关资讯
2018相泽南九息娱乐

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux....

澳门拉斯维加斯网页

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux....

博越会员怎么注册

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux....

海洋之神590快速充值

MySQL限制数据重复的方式:表上增加主键(Primary Key)或增加唯一性索引(Unique)

主键对重复资料进行限制,这样资料在导入时就无法重复插入

create table primary_t(last_name varchar(20),first_name varchar(20),sex varchar(8),primary key(last_name,first_name))engine=innodb default charset=utf8;show table status like 'primary_t'\Gshow index from primary_t\G模拟插入两笔有主键约束的资料insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'PRIMARY'insert into primary_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from primary_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Coco | Li | Woman || Frank | Cai | Man || Frank | Li | Man |+-----------+------------+-------+rows in set (0.00 sec)

唯一性索引对复制资料进行限制

create table unique_t(last_name varchar(20),first_name varchar(20),sex varchar(8),unique index unique_name(last_name,first_name))engine=innodb default charset=utf8;show table status like 'unique_t'\Gshow index from unique_t\G模拟插入两笔有唯一性约束的资料insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Li','Man'),('Coco','Li','Woman');ERROR 1062 (23000): Duplicate entry 'Frank-Li' for key 'unique_name'insert into unique_t(last_name,first_name,sex) values('Frank','Li','Man'),('Frank','Cai','Man'),('Coco','Li','Woman');mysql> select * from unique_t;+-----------+------------+-------+| last_name | first_name | sex |+-----------+------------+-------+| Frank | Li | Man || Frank | Cai | Man || Coco | Li | Woman |+-----------+------------+-------+rows in set (0.00 sec)

对资料的统计

create table count_t(last_name varchar(20),first_name varchar(20),sex varchar(8))engine=innodb default charset=utf8;insert into count_t values('Frank','A','Man'),('Frank','B','Man'),('Frank','C','Woman'),('Frank','D','Man'),('Frank','E','Man'),('Frank','F','Woman'),('Frank','G','Man'),('Frank','H','Man'),('Frank','I','Woman'),('Coco','A','Woman'),('Coco','B','Man'),('Coco','C','Man'),('Coco','D','Man'),('Coco','E','Man'),('Coco','F','Man'),('Coco','G','Woman'),('Coco','H','Woman'),('Alex','B','Man'),('ALex','C','Man'),('ALex','D','Man'),('Alex','E','Man'),('ALex','F','Man'),('ALex','G','Woman'),('ALex','H','Woman');commit;按各个列进行分组统计select last_name,count(*) from count_t group by last_name;select first_name,count(*) from count_t group by first_name;select sex,count(*) from count_t group by sex;mysql> select last_name,count(*) from count_t group by last_name;+-----------+----------+| last_name | count(*) |+-----------+----------+| Alex | 7 || Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)mysql> select first_name,count(*) from count_t group by first_name;+------------+----------+| first_name | count(*) |+------------+----------+| A | 2 || B | 3 || C | 3 || D | 3 || E | 3 || F | 3 || G | 3 || H | 3 || I | 1 |+------------+----------+rows in set (0.00 sec)mysql> select sex,count(*) from count_t group by sex;+-------+----------+| sex | count(*) |+-------+----------+| Man | 16 || Woman | 8 |+-------+----------+rows in set (0.00 sec)对分组的统计在进行限定,having 大于等于8次的记录select last_name ,count(*) from count_t group by last_name having count(*) >=8;mysql> select last_name ,count(*) from count_t group by last_name having count(*) >=8;+-----------+----------+| last_name | count(*) |+-----------+----------+| Coco | 8 || Frank | 9 |+-----------+----------+rows in set (0.00 sec)

对资料进行去重操作,主要使用distinct函数

select distinct last_name from count_t;select distinct first_name from count_t;select distinct sex from count_t;

对已有资料的表进行去重操作

insert into count_t values('Xi','Xi','Man'),('Xi','Xi','Man');增加主键约束,加入ignore,忽悠重复的资料alter ignore table count_tadd primary key(last_name,first_name);select * from count_t where last_name='Xi';mysql> alter ignore table count_t -> add primary key(last_name,first_name);Query OK, 26 rows affected (0.01 sec)Records: 26 Duplicates: 1 Warnings: 0mysql> select * from count_t where last_name='Xi';+-----------+------------+------+| last_name | first_name | sex |+-----------+------------+------+| Xi | Xi | Man |+-----------+------------+------+row in set (0.00 sec)使用group by方式也可去除重复资料create table count_t0 as select last_name,first_name,sex from count_t group by last_name,first_name,sex;linux....

热门资讯