Mysql经典题

查询姓"胡"的老师的个数;
SELECT COUNT(1) 
FROM teacher 
WHERE tname LIKE '李%';
查询每门课程被选修的学生数
SELECT cid,COUNT(1)
FROM sc
GROUP BY cid;
查询平均成绩大于50分的学生的学号、姓名和平均成绩
SELECT st.sid,sname,AVG(score) 
FROM sc,student st
WHERE sc.sid=st.sid
GROUP BY st.sid
HAVING AVG(score)>50;
查询课程名称为"python"，且分数低于60的学生学号,姓名和分数
SELECT st.sid,sname,score
FROM sc,student st,course c
WHERE sc.sid=st.sid AND sc.cid=c.cid
AND c.cname='python' AND score<60;
查询只选修了一门课程的全部学生的学号和姓名
SELECT st.sid,st.sname
FROM sc,student st
WHERE sc.sid = st.sid
GROUP BY st.sid
HAVING COUNT(1)=1;
查询同名同性别学生名单，并统计同名同性别人数
SELECT sname,ssex,COUNT(1)
FROM student 
GROUP BY sname,ssex
HAVING COUNT(1) >1;
1981年出生的学生名单(注：student表中sage列的类型是datetime)
SELECT sid,sname,'1981' age
FROM student
WHERE DATE_FORMAT(sage,'%Y')='1981';
查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT cid,AVG(score) avg_score
FROM sc
GROUP BY cid
ORDER BY AVG(score),cid DESC;
查询平均成绩大于60分的同学的学号和平均成绩;
SELECT sid,ROUND(AVG(score),2) avg_score
FROM sc
GROUP BY sid
HAVING AVG(score)>60;
查询所有同学的学号、姓名、选课数、总成绩;
SELECT st.sid,sname,COUNT(1) num,SUM(score) total_score
FROM sc,student st
WHERE sc.sid=st.sid
GROUP BY st.sid;
查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT cid 课程ID, MAX(score) 最高分,MIN(score)最低分 FROM sc GROUP BY cid;
查询不同老师所教不同课程平均分从高到低显示 要求显示：教师ID,教师姓名，课程ID,课程名称，平均成绩
SELECT MAX(t.tid) "教师ID",MAX(t.tName) "教师姓名",c.cid "课程ID", MAX(c.cname) "课程名称" ,AVG(sc.score) "平均成绩"
FROM sc,course c,teacher t WHERE sc.cid = c.cid AND c.tid = t.tid GROUP BY c.tid,c.cid
ORDER BY AVG(sc.score) DESC;
查询全部学生都选修的课程号
FROM student s,course c, sc 
WHERE s.sid = sc.sid AND sc.cid = c.cid 
GROUP BY sc.cid HAVING COUNT(1) = (SELECT COUNT(1) FROM student);
查询两门以上(包含两门)不及格课程的同学的学号及其平均成绩
SELECT sid,AVG(score) 
FROM sc 
WHERE sid IN (SELECT sid FROM sc WHERE score <60 GROUP BY sid HAVING COUNT(1)>2)
GROUP BY sid; 
查询没学过"叶平"老师讲授的任一门课程的学生姓名
SELECT DISTINCT sname 
FROM student 
WHERE sid NOT IN 
(SELECT sid FROM course,teacher,sc WHERE course.tid=teacher.tid AND sc.cid=course.cid AND tname='叶平'); 
查询所有课程成绩小于60分的同学的学号、姓名;
SELECT sid,sname
FROM student st
WHERE sid NOT IN
(SELECT DISTINCT(sid)
FROM sc 
WHERE score >=60);
查询学过"001"并且也学过编号"002"课程的同学的学号、姓名;
-- 这里用到了内连接的特点:不符合要求的全部过滤掉
SELECT st.sid,sname
FROM 
(SELECT sid FROM sc WHERE cid='001') AS s1,
(SELECT sid FROM sc WHERE cid='002') AS s2,
student st
WHERE s1.sid=s2.sid AND s1.sid=st.sid;
查询"001"课程比"002"课程成绩高的所有学生的学号;
SELECT s1.sid
FROM 
(SELECT sid,cid,score FROM sc WHERE cid='001') AS s1,
(SELECT sid,cid,score FROM sc WHERE cid='002') AS s2
WHERE s1.sid=s2.sid AND s1.score>s2.score;
查询没有学全所有课的同学的学号、姓名;
SELECT st.sid,st.sname
FROM sc,student st
WHERE sc.sid = st.sid
GROUP BY st.sid
HAVING COUNT(1) <
(SELECT COUNT(1) FROM course);
查询至少有一门课与学号为"1003"的同学所学相同的同学的学号和姓名;
SELECT DISTINCT(st.sid),st.sname
FROM sc,student st
WHERE sc.sid = st.sid AND sc.sid !='1003'
AND cid IN (
  SELECT cid FROM sc WHERE sid ='1003'
);
按平均成绩从高到低显示所有学生的"python"、"企业管理"、"英语"三门的课程成绩，按如下形式显示： 学生ID,,python,企业管理,英语,有效课程数,有效平均分
SELECT sc1.sid 学生ID, 数据库,企业管理,英语,有效课程数,平均成绩
FROM 
(SELECT sid,AVG(score) 平均成绩,COUNT(1)有效课程数 FROM sc GROUP BY sid )sc1 LEFT JOIN
(SELECT sid,score 数据库 FROM sc,course c WHERE sc.cid = c.cid AND c.cname='数据库') AS a ON sc1.sid =a.sid LEFT JOIN
(SELECT sid,score 企业管理 FROM sc,course c WHERE sc.cid =c.cid AND c.cname='企业管理') AS b ON sc1.sid = b.sid LEFT JOIN
(SELECT sid,score 英语 FROM sc,course c WHERE sc.cid =c.cid AND c.cname='英语') AS c ON sc1.sid =c.sid
ORDER BY 平均成绩 DESC;
查询男生、女生人数
SELECT SUM(CASE WHEN ssex='男' THEN 1 ELSE 0 END) '男',
SUM(CASE WHEN ssex='女' THEN 1 ELSE 0 END) '女'
FROM student;
按各科平均成绩从低到高和及格率的百分数从高到低排序
SELECT cid,AVG(score) 平均成绩, 100*SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(1) 及格率
FROM sc 
GROUP BY cid ORDER BY 平均成绩,及格率 DESC;
统计各科成绩,各分数段人数:课程ID,课程名称,[85,100],[70,85),[60,70),[ <60]
SELECT c.cid 课程ID, c.cname 课程名称,
SUM(CASE WHEN score>=85 AND score <=100 THEN 1 ELSE 0 END) AS "[85,100]",
SUM(CASE WHEN score>=70 AND score <85 THEN 1 ELSE 0 END) AS "[70,85)",
SUM(CASE WHEN score>=60 AND score < 70 THEN 1 ELSE 0 END) AS "[60,70)",
SUM(CASE WHEN score<60 THEN 1 ELSE 0 END) AS "<60"
FROM sc,course c
WHERE sc.cid=c.cid
GROUP BY c.cid;
查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&java （003），python（004）
SELECT SUM(CASE WHEN cid ='001' THEN score ELSE 0 END)/SUM(CASE cid WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分 
,100 * SUM(CASE WHEN cid = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数 
,SUM(CASE WHEN cid = '002' THEN score ELSE 0 END)/SUM(CASE cid WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分 
,100 * SUM(CASE WHEN cid = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数 
,SUM(CASE WHEN cid = '003' THEN score ELSE 0 END)/SUM(CASE cid WHEN '003' THEN 1 ELSE 0 END) AS UML平均分 
,100 * SUM(CASE WHEN cid = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '003' THEN 1 ELSE 0 END) AS UML及格百分数 
,SUM(CASE WHEN cid = '004' THEN score ELSE 0 END)/SUM(CASE cid WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分 
,100 * SUM(CASE WHEN cid = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数 
FROM sc ;
查询每门功课成绩最好的前两名(开窗函数)
SELECT sid,cid,score 
FROM sc t1
WHERE (
  SELECT COUNT(1)
  FROM sc t2
  WHERE t1.cid=t2.cid
  AND t2.score>t1.score
) <2 ORDER BY t1.cid;
查询所有学生的选课情况;
SELECT sid,GROUP_CONCAT(cid)
FROM sc
GROUP BY sid;
查询学过"叶平"老师所教的所有课的同学的学号、姓名;
SELECT st.sid,st.sname
FROM sc,student st,course c,teacher t
WHERE sc.sid =st.sid AND sc.cid = c.cid AND c.tid = t.tid 
AND tname = '叶平' 
GROUP BY st.sid
HAVING COUNT(1)=
(SELECT COUNT(1) 
FROM teacher t,course c 
WHERE t.tid=c.tid AND tname ='叶平');
查询和"1002"号的同学学习的课程完全相同的其他同学学号和姓名;
SELECT sid 
FROM sc WHERE sid
NOT IN(SELECT DISTINCT(sid) FROM sc WHERE cid
 NOT IN (SELECT cid FROM sc WHERE sid ='1002'))
GROUP BY sid 
HAVING COUNT(1) = (SELECT COUNT(1) FROM sc WHERE sid ='1002') AND sid !='1002';
把"sc"表中"叶平"老师教的课的成绩都更改为此课程的平均成绩;
UPDATE sc,(
  SELECT AVG(score) avg_score,sc.cid
  FROM sc,teacher t,course c
  WHERE sc.cid=c.cid AND c.tid=t.tid AND tname = '叶平'
  GROUP BY sc.cid
) temp
SET sc.score = temp.avg_score
WHERE sc.cid =temp.cid