实战练习

创建基础数据表

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','[email protected]'),
       ('Jean','King','[email protected]'),
       ('Peter','Ferguson','[email protected]'),
       ('Janine ','Labrune','[email protected]'),
       ('Jonas ','Bergulfsen','[email protected]'),
       ('Janine ','Labrune','[email protected]'),
       ('Susan','Nelson','[email protected]'),
       ('Zbyszek ','Piestrzeniewicz','[email protected]'),
       ('Roland','Keitel','[email protected]'),
       ('Julie','Murphy','[email protected]'),
       ('Kwai','Lee','[email protected]'),
       ('Jean','King','[email protected]'),
       ('Susan','Nelson','[email protected]'),
       ('Roland','Keitel','[email protected]');

查询所有记录

SELECT 
    *
FROM
    contacts;

查询某一列重复

SQL语法

SELECT 
    col, 
    COUNT(col)
FROM
    table_name
GROUP BY col
HAVING COUNT(col) > 1;

接下来我们查询email重复的记录

SELECT 
    email, 
    COUNT(email)
FROM
    contacts
GROUP BY email
HAVING COUNT(email) > 1;

查询结果

查询多列重复

SQL语法

SELECT 
    col1, COUNT(col1),
    col2, COUNT(col2),
    ...
 
FROM
    table_name
GROUP BY 
    col1, 
    col2, ...
HAVING 
       (COUNT(col1) > 1) AND 
       (COUNT(col2) > 1) AND 
       ...

我们查询first_name,last_name,email重复的记录

SELECT 
    first_name, COUNT(first_name),
    last_name,  COUNT(last_name),
    email,      COUNT(email)
FROM
    contacts
GROUP BY 
    first_name , 
    last_name , 
    email
HAVING  COUNT(first_name) > 1
    AND COUNT(last_name) > 1
    AND COUNT(email) > 1;

查询结果

8X----------------------------------------

查找所有重复标题(title)的方法：

#Option 1

SELECT * FROM table a WHERE ((SELECT COUNT(*) FROM table WHERE Title = a.Title) > 1) ORDER BY Title DESC

#Option 2

SELECT title, COUNT(*) c FROM table GROUP BY title HAVING c > 1;

#Option 3

SELECT title
FROM table
GROUP BY title
HAVING count(*) > 1;