mysql 的DISTINCT、EXISTS、IN、GROUP BY..HAVING 用法记录

mysql 的DISTINCT （去掉重复）
mysql 的EXISTS （存在于、 条件的字段，值均在括号中）
mysql 的IN （在、 条件字段的在括号前，条件值在括号中）
mysql 的GROUP BY..HAVING（分组，把字段值相同的统计出来，having 统计个数限制条件）GROUP BY..HAVING结合使用

SELECT * from class;SELECT * from students;SELECT * from  sc;# 查询选修c02课程的学生信息SELECT s.sid,s.sname,c.cid from students as s LEFT JOIN sc as c on s.sid = c.sid where c.cid = "c02";# 查询选修c02课程的学生信息SELECT c.cid,s.sname,s.sid from sc as c LEFT JOIN students as s on s.sid=c.sid where c.cid = "c03";#关系中间表里 查询选修了课程的学生人数 利用DISTINCTSELECT count(DISTINCT sid) from sc; #关系中间表里 查询选修了课程的学生人数 利用EXISTSSELECT COUNT(sid) FROM students as s WHERE EXISTS( SELECT * FROM sc WHERE sc.sid = s.sid );# 查询选修课程超过2门的学生姓名 利用Inselect s.sid,s.sname from students as s where s.sid IN (  SELECT sid from sc GROUP BY sid HAVING count(sid)>=2);# 查询选修课程超过2门的学生姓名 利用EXISTSselect s.sid,s.sname from students as s where EXISTS (  SELECT sid from sc where sc.sid = s.sid GROUP BY sid HAVING count(sid)>=2);# SELECT sid,count(sid) from sc GROUP BY sid HAVING count(sid)>2