本文主要是介绍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 sLEFT 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";#关系中间表里 查询选修了课程的学生人数 利用DISTINCT
SELECT count(DISTINCT sid) from sc;#关系中间表里 查询选修了课程的学生人数 利用EXISTS
SELECT COUNT(sid) FROM students as s WHERE EXISTS( SELECT * FROM sc WHERE sc.sid = s.sid );# 查询选修课程超过2门的学生姓名 利用In
select s.sid,s.sname from students as s where s.sid IN (SELECT sid from sc GROUP BY sid HAVING count(sid)>=2);# 查询选修课程超过2门的学生姓名 利用EXISTS
select 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
这篇关于mysql 的DISTINCT、EXISTS、IN、GROUP BY..HAVING 用法记录的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!