【基础知识】sql查询语句

标题查询在 SC 表存在成绩的学生信息

select s.sname,s.sid,s.sage,s.ssex from sc inner join student as s on sc.sid=s.sid
group by s.sname,s.sid,s.sage,s.ssex

查询「李」姓老师的数量

SELECT COUNT(*) FROM teacher where tname LIKE '李%';

查询各科成绩最高分、最低分和平均分

SELECT c.cid,c.cname,MAX(sc.score),MIN(sc.score) ,AVG(sc.score) FROM sc LEFT JOIN course as c ON sc.cid=c.cid GROUP BY c.cid,c.cname

查询男生、女生人数

SELECT ssex,COUNT(1) FROM student GROUP BY ssex

查询名字中含有「风」字的学生信息

SELECT * FROM student where sname LIKE '%风%'

查询同名同性学生名单，并统计同名人数

SELECT sname,ssex,COUNT(*) FROM student as st GROUP BY st.sname,st.ssex HAVING COUNT(*)>1

查询 1990 年出生的学生名单

SELECT sname from student WHERE sage like '%1990%'

求每门课程的学生人数

SELECT course.cid,course.cname,COUNT(*) FROM sc  INNER JOIN  course ON sc.cid=course.cid GROUP BY course.cid,course.cname

统计每门课程的学生选修人数（超过 5 人的课程才统计）

SELECT sc.cid,c.cname,COUNT(*) FROM sc 
left join course c on sc.cid = c.cid GROUP BY sc.cid,c.cname HAVING COUNT(*)>5;

检索至少选修两门课程的学生学号

SELECT sid FROM sc GROUP BY sid HAVING COUNT(*)>=2;