数据库学习系列之查重复值

CREATE TABLE contacts (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL
);
INSERT INTO contacts (first_name,last_name,email) ;
VALUES ('Carine ',‘Schmitt’,‘carine.schmitt@qq.com’),
(‘Jean’,‘King’,‘jean.king@yiibai.com’),
(‘Peter’,‘Ferguson’,‘peter.ferguson@google.com’),
('Janine ',‘Labrune’,‘janine.labrune@aol.com’),
('Jonas ',‘Bergulfsen’,‘jonas.bergulfsen@mac.com’),
('Janine ',‘Labrune’,‘janine.labrune@aol.com’),
(‘Susan’,‘Nelson’,‘susan.nelson@qq.com’),
('Zbyszek ',‘Piestrzeniewicz’,‘zbyszek.piestrzeniewicz@qq.com’),
(‘Roland’,‘Keitel’,‘roland.keitel@yahoo.com’),
(‘Julie’,‘Murphy’,‘julie.murphy@yahoo.com’),
(‘Kwai’,‘Lee’,‘kwai.lee@google.com’),
(‘Jean’,‘King’,‘jean.king@qq.com’),
(‘Susan’,‘Nelson’,‘susan.nelson@qq.comt’),
(‘Roland’,‘Keitel’,‘roland.keitel@yahoo.com’);
SELECT * FROM contacts;
#根据一个列找到重复的行的个数
SELECT c.first_name , COUNT(c.first_name)
FROM
contacts c GROUP BY c.first_name
HAVING COUNT(c.first_name)>1;
#根据多个列找到重复的值，原理一样
SELECT
c.first_name,COUNT(c.first_name),
c.last_name,COUNT(c.last_name),
c.email,COUNT(c.email)
FROM contacts c
GROUP BY
c.first_name,
c.last_name,
c.email
HAVING COUNT(c.first_name)>1
AND COUNT(c.last_name)>1
AND COUNT(c.email)>1;