/*查询name重复的所有数据*/
SELECT * FROM student WHERE (student.name) in
(SELECT name FROM student GROUP BY name having COUNT(*) > 1 )
/*查询关键字重复的数据*/
SELECT name, COUNT(name) as '重复次数' from student GROUP BY name
HAVING COUNT(*) > 1
/*查询所有姓名重复的记录*/
SELECT * FROM student WHERE name in
(SELECT name FROM student GROUP BY name HAVING COUNT(`name`) > 1)
/*查询多个字段重复信息*/
SELECT * from student where (student.name,student.age) in
(SELECT student.name,student.age FROM student GROUP BY name,age HAVING COUNT(*) > 1)
/*查询多个字段重复数据不包括course_id最小的*/
SELECT * FROM student WHERE (student.`name`,student.age) in
(SELECT `name`,age FROM student GROUP BY name ,age HAVING COUNT(*) > 1)
AND course_id NOT in (SELECT MIN(course_id) from student GROUP BY name ,age HAVING COUNT(*) >1 )
SELECT DISTINCT * FROM student
/*UNION的使用方法union ALL能列出所有人员信息*/
(SELECT * FROM student WHERE sex = '1' ORDER BY age DESC)
UNION
(SELECT * FROM student where sex = '2' ORDER BY age ASC)
/*显男女数量*/
SELECT sex as '性别' ,COUNT(sex) as '数量' FROM student GROUP BY sex
/*查询每个同学的最高分*/
SELECT name,score FROM (SELECT * FROM student LEFT JOIN course
ON student.course_id = course.score_id GROUP BY student.name,course.score DESC)
as a GROUP BY name
/*EXISTS的用法,返回值结果0或1,如果存在择列出所有数据*/
SELECT * FROM student where EXISTS(SELECT * FROM student WHERE age > 14)