MySQL自己不常用的语句

一、查询字段不重复的数据

1、distinct

select distinct name from table;

select distinct name, id from table; #作用于两个字段 distinct (name, id)

select id, distinct name from table; #[ERROR] distinct必须放在开头

 

2、group by

select * from table group by name;

select * from table group by name order by id desc limit 100;  #group by必须放前面

 

二、查询字段重复的数据

1、SELECT id, name FROM table WHERE name IN (SELECT name FROM table GROUP BY name HAVING COUNT(name)>1); #效率太低

 

2、建立临时表

CREATE table 'tmp' AS (SELECT name FROM table GROUP BY name HAVING COUNT(name)>1);

SELECT * FROM tmp a, table b WHERE a.name = b.name;

 

3、关于查询效率(性能)

SELECT * FROM table WHERE name IN (SELECT name FROM table GROUP BY name HAVING COUNT(*)>1);

SELECT * FROM table INNER JOIN (SELECT name FROM table GROUP BY name HAVING COUNT(*)>1) as tmp USING (name); #性能更高 （比2方法更简洁的写法，思路一样）