Student(Sid,Sname,Sage,Ssex) 学生表 Sid：学号；Sname：学生姓名；Sage：学生年龄；Ssex：学生性别
Course(Cid,Cname,Tid) 课程表 Cid,课程编号；Cname：课程名字；Tid：教师编号
SC(Sid,Cid,score) 成绩表 Sid：学号；Cid,课程编号；score：成绩
Teacher(Tid,Tname) 教师表 Tid：教师编号； Tname：教师名字
1、查询1课程比2课程成绩高的所有学生的学号；
SELECT
a.Sid
FROM
(SELECT Sid,score FROM sc WHERE Cid= 1) a,
(SELECT Sid,score FROM sc WHERE Cid= 2) b
WHERE
a.score>b.score
AND a.Sid= b.SId;
需求：查询学号
条件：比较成绩(同一学生)
对同一张表进行内连接查询
2、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT
Sid,
AVG(score)
FROM
sc
GROUP BY
Sid
HAVING AVG(score)> 60;
需求：查询学号和平均成绩(定位主查成绩表)
条件：比较平均成绩
按学号分组查询平均成绩，注意涉及到分组字段条件关键字用HAVING，其他字段条件在分组前用where
3、查询所有同学的学号、姓名、选课数、总成绩；
SELECT
student.Sid,
student.Sname,
COUNT(sc.Cid),
SUM(sc.score)
FROM
student
LEFT JOIN sc ON student.Sid=sc.Sid
GROUP BY student.Sid,student.Sname
需求：查询学生信息，成绩信息(定位学生表、成绩表)
条件：无
count函数，sum函数，学生id，学生name，通过对学生id，学生name分组，学生表左链接成绩表查出相关信息
4、查询姓“语”的老师的个数；
SELECT
COUNT(teacher.Tid)
FROM
teacher
GROUP BY
teacher.Tid,teacher.Tname
HAVING
teacher.Tname LIKE'语%';
SELECT
COUNT(DISTINCT(Tname))
FROM
teacher
WHERE
Tname LIKE'语%';
需求：求教师的个数(教师表，聚合函数count)
条件：姓‘语’(like ‘语%’)
HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。
SELECT
COUNT(teacher.Tid)
FROM
teacher
WHERE teacher.Tname="语文老师"GROUP BY
teacher.Tid
HAVING
teacher.Tid= 1
5、查询没学过“语文老师”课的同学的学号、姓名
SELECT
student.Sid,
student.Sname
FROM
student
WHERE
student.Sid NOT IN (
SELECT
DISTINCT(sc.Sid)
FROM
sc,
teacher,
course
WHERE
sc.Cid=course.Cid
AND teacher.Tid=course.Tid
AND teacher.Tid= "语文老师");
需求：查询学生信息(学生表，教师表，课程表，成绩表-关联学生)
条件：没学过语文老师的课程(没... not in)学过语文老师的课程
6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
SELECT
student.Sid,
student.Sname
FROM
student,
sc
WHERE
student.Sid=sc.Sid
AND sc.Cid= 1AND EXISTS (
SELECT*FROM
sc AS sc2
WHERE
sc2.Sid=sc.Sid
AND sc2.Cid= 2)
需求：查询学生信息(学生表，成绩表)
条件：学过“1”并且也学过编号“2”课程(不用查主表，应为条件为id所以关联表成绩表，同一字段两个条件：条件1 and exists(条件2 and 表1条件字段=表2条件字段))
7、查询学过“语文老师”所教的所有课的同学的学号、姓名；
SELECT
student.Sid,
student.Sname
FROM
student
WHERE
student.Sid IN (
SELECT
sc.Sid
FROM
sc,
course,
teacher
WHERE
sc.Cid = course.Cid
AND course.Tid = teacher.Tid
AND teacher.Tname = "语文老师"
GROUP BY
sc.Sid
HAVING
COUNT(sc.Cid) = (
SELECT
COUNT(course.Cid)
FROM
course,
teacher
WHERE
course.Tid = teacher.Tid
AND teacher.Tname = "语文老师"
)
);
需求：查询学生信息(学生表，教师表，成绩表，课程表)
条件：学过“语文老师”所教的所有课，学过语文老师的课+学过语文老师的所有课(数据表id in 内连接 条件1 分组 条件2 count 内连接 条件1)
8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
SELECT
student.Sid,
student.Sname
FROM
student,
(
SELECT
sc.Sid,
sc.score
FROM
sc
WHERE
sc.Cid = 1
) a1,
(
SELECT
sc.Sid,
sc.score
FROM
sc
WHERE
sc.Cid = 2
) a2
WHERE
student.Sid = a1.Sid
AND a1.Sid = a2.Sid
AND a1.score > a2.score;
SELECT
ret.Sid,
ret.Sname
FROM
(
SELECT
student.Sid,
student.Sname,
sc.score,
(
SELECT
sc2.score
FROM
sc sc2
WHERE
sc2.Sid=student.Sid
AND sc2.Cid= 2) score2
FROM
student,
sc
WHERE
student.Sid=sc.Sid
AND sc.Cid= 1) ret
WHERE
ret.score2< ret.score
需求：查询学生信息(学生表，成绩表)
条件：课程1成绩比课程2成绩高
课程1的成绩比课程2的成绩作比较，因为中间表成绩表中有课程id和学生id所以不会用到课程表，主查询表为学生表，同一学生不同课程成绩的比较。
不同课程的成绩可从中间表查询学生id和比较的分数其结果作为中间表a1，同样中间表a2，两表和主表内联查询，最后where作比较
一个中间表既包括学生信息，又包括两个分数信息，但是第二个分数需要做子查询和主表关联。最后条件比较两个分数
9、查询所有课程成绩小于60分的同学的学号、姓名；
SELECT
student.Sid,
student.Sname
FROM
student
WHERE
student.Sid NOT IN (
SELECT DISTINCT
(student1.Sid)
FROM
sc,
student student1
WHERE
sc.Sid=student1.Sid
AND sc.score> 60)
需求：查询学生信息(学生表，成绩表)
条件：所有课程成绩小于60
说明：所有小于 = 不大于，所以要用 not in 大于一个数的查询用来简化条件
10、查询没有学全所有课的同学的学号、姓名；
SELECT
student.Sid,
student.Sname
FROM
student,
sc
WHERE
student.Sid=sc.Sid
GROUP BY
student.Sid,
student.Sname
HAVING
COUNT(sc.Cid)
SELECT
COUNT(course.Cid)
FROM
course
)
需求：查询学生信息(学生表，课程表，成绩表)
条件：没有学全所有课
说明：所有课和学完了的课做比较，连个数量比较，属于一个人的两个聚合关系的比较，所以要按人的其他非聚合信息分组，在having中比较聚合关系
11、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
SELECT DISTINCT
student.Sid,
student.Sname
FROM
student,
(SELECT sc.Sid, sc.Cid FROM sc) sc1,
(
SELECT
sc2.Sid,
sc2.Cid
FROM
sc sc2
WHERE
sc2.Sid= 1) sc3
WHERE
student.Sid=sc1.Sid
AND sc1.Cid=sc3.Cid;
SELECT
DISTINCT
student.Sid,
student.Sname
FROM
student,
sc
WHERE
student.Sid=sc.Sid
AND sc.Cid IN (
SELECT
sc1.Cid
FROM
sc sc1
WHERE
sc1.Sid= 1);
需求：查询学生信息(学生表，成绩表)
条件：至少有一门课与学号为“1”的同学所学课程相同
说明：主表为学生表，主表id作为关联，单条数据中的课程id in 学号为1所学课程的id，或者子查询所有课程和学号1所学课程内连接，需要注意的是结果笛卡尔积，需要使用distinct去重
12、查询至少学过学号为“1”同学所有课的其他同学学号和姓名；
13、把“SC”表中“语文老师”老师教的课的成绩都更改为此课程的平均成绩；
UPDATE sc
INNER JOIN (
SELECT
a.*FROM
(
SELECT
sc2.Cid,
AVG(sc2.score) average
FROM
sc sc2
GROUP BY
sc2.Cid
) a,
teacher,
course
WHERE
a.Cid=course.Cid
AND teacher.Tid=course.Tid
AND teacher.Tname= "语文老师") AS b ON sc.Cid=b.Cid
SET sc.score= b.average
UPDATE sc
SET sc.score = (
SELECT
AVG(sc1.score)
FROM
sc AS sc1,
course AS course1,
teacher AS teacher1
WHERE
sc1.Cid = course1.Cid
AND course1.Tid = teacher1.Tid
AND teacher1.Tname = "语文老师"
)
WHERE
sc.Cid IN (
SELECT
course.Cid
FROM
course,
teacher
WHERE
course.Cid = teacher.Tid
AND teacher.Tname = "语文老师"
);
[Err] 1093 - You can't specify target table 'sc' for update in FROM clause
需求：批量修改某老师课程的成绩表中成绩为此课程的平均成绩
条件：语文老师所教的课
说明：批量修改一般使用set 属性=值 where id in 数组的操作，但是这样做得在编程中将条件查询成常量，在进行二次查询，否则出现上述错误，在子查询中不能用被更新的表。mysql 中可以使用update a join b on a.id=b.id set a.name=b.name where a.m='...' and b.n='...'
14、查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名；
SELECT
student.Sid,
student.Sname,
COUNT(sc.Cid)
FROM
student,
sc
WHERE
student.Sid= sc.Sid AND sc.Cid IN (SELECT Cid FROM sc WHERE Sid = 1)
GROUP BY
Sid,Sname
HAVING
COUNT(1) =(
SELECT
COUNT(1)
FROM
sc
WHERE
Sid= 1)
需求：查询学生信息(学生表，成绩表)
条件：和同学1的课程完全相同的其他学生的信息
说明：学生1的课程信息 = 其他学生的信息。这个条件进行拆分：其他同学的课程id in 学生1的课程id && 其他同学的课程count = 学生1的课程数量
15、删除学习“语文老师”课的SC表记录；
DELETE
FROM
sc
WHERE
sc.Cid IN (
SELECT
sc1.Cid
FROM
sc sc1,
course,
teacher
WHERE
sc1.Cid=course.Cid
AND course.Tid=teacher.Tid
AND teacher.Tname= "语文老师");
[Err] 1093 - You can't specify target table 'sc' for update in FROM claus
DELETE sc
FROM
sc,
course,
teacher
WHERE
sc.Cid=course.Cid
AND course.Tid=teacher.Tid
AND teacher.tName= "语文老师"AND sc.score= 33;
需求：删除成绩表记录(成绩表，老师表)
条件：学习过语文老师课的成绩
说明：修改操作不能再子查询中使用被修改的表，所以第一种写法错误，第二种写法中delete a from a,b,c .... 这里被删除的a必须在from后边有出现过，否则语法错误
16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“3”课程的同学学号、2、号课的平均成绩；
INSERT Sid,1,(SELECT AVG( score) FROM sc WHERE Cid=1) FROM student WHERE Sid NOT IN(SELECT Sid FROM sc WHERE Cid=1)
需求：插入成绩信息(学生表，成绩表)
条件：没上过课程3的学生，平均成绩。先查出课程，按照课程id程批量插入。 主查询,子查询 + not in
17、按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分
SELECT
t.Sid AS'学生id',(
SELECT
score
FROM
sc
WHERE
Sid=t.Sid
AND Cid= 1) AS'语文成绩',
(
SELECT
score
FROM
sc
WHERE
Sid=t.Sid
AND Cid= 2) AS'数学成绩',
(
SELECT
score
FROM
sc
WHERE
Sid=t.Sid
AND Cid= 3) AS'英语成绩',
COUNT(1) AS '有效课程数',
AVG( t.score) AS '平均成绩'FROM
sc AS t
GROUP BY
t.Sid
ORDER BY
AVG(t.score)
需求：学生ID,语文,数学,英语,有效课程数,有效平均分(sc)
条件：按平均成绩从高到低显示，主查询中课程id作为子查询的条件来查各课程的成绩，最后主查询按照学生id分组，按照平均成绩排序
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT
Cid AS'课程id',
MAX(score) AS'最高成绩',
MIN(score) AS'最低成绩'FROM
sc
GROUP BY
Cid;
SELECT
Cid AS'课程id',
MAX(score) AS'最高成绩',
MIN(score) AS'最低成绩'FROM
sc,student WHERE sc.Sid=student.Sid
GROUP BY
Cid;
SELECT
DISTINCT
l.Cid,
l.score,
r.score
FROM
sc l,
sc r
WHERE
l.Cid=r.Cid
AND l.score=(
SELECT
MAX(ll.score)
FROM
sc ll,
student lm
WHERE
l.Cid=ll.Cid
AND lm.Sid=ll.Sid
GROUP BY
ll.Cid
)
AND r.score=(
SELECT
MIN(lr.score)
FROM
sc AS lr
WHERE
r.Cid=lr.Cid
GROUP BY
lr.Cid
);
需求：查询各科成绩最高和最低的分(sc,student)
条件：
说明：各科说明按课程id分组，最低分，最高分都在成绩表中，所以使用max，min可以直接查出，同时也可以自关联通过条件子查询max，min进行等于比较进行条件判断来查询相关信息。
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT
sc.Cid,
course.Cname,
AVG(sc.score) AS'平均成绩',100 *SUM(
CASE
WHEN IFNULL(sc.score,0) >= 60THEN1ELSE0END
)/ COUNT(1) AS '及格百分比'FROM
sc,
course
WHERE
sc.Cid=course.Cid
GROUP BY
sc.Cid
ORDER BY
AVG(sc.score) ASC,100 *SUM(
CASE
WHEN IFNULL(sc.score,0) >= 60THEN1ELSE0END
)/ COUNT(1)
DESC
需求：查询课程id，课程名称，平均成绩，及格百分数(成绩表，课程表)
条件：各科平均成绩正序，及格率倒序
说明：成绩表关联课程表，按照课程id，课程名称分组，及格百分比通过及格课程数/课程总数获得 case when else end, ifnull
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 语文(1)，数学(2)，英语(3)
需求：查询各课程平均成绩，及格率
条件：一条记录查询各课程平均成绩，及格率
说明：查询两个字段，两个函数计算结果得出一条记录，所以都用子查询，以主表某字段作为子查询条件，%可以说使用concat ( left (数值1 / 数值2 *100,5),'%')先截取字符串，在链接字符串，同时截取字符串可以替换为四舍五入等操作
SELECT
SUM(
CASE
WHEN Cid= 1THEN
score
ELSE0END
)/ SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0 END) AS '语文平均分',100 *SUM(
CASE
WHEN Cid= 1AND score>= 60THEN1ELSE0END
)/ SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0 END) '语文及格率',
SUM(
CASE
WHEN Cid= 2THEN
score
ELSE0END
)/ SUM(CASE WHEN Cid = 2 THEN 1 ELSE 0 END) AS '数学平均分',100 *SUM(
CASE
WHEN Cid= 2AND score>= 60THEN1ELSE0END
)/ SUM(CASE WHEN Cid = 2 THEN 1 ELSE 0 END) '数学及格率',
SUM(
CASE
WHEN Cid= 3THEN
score
ELSE0END
)/ SUM(CASE WHEN Cid = 3 THEN 1 ELSE 0 END) AS '英语平均分',100 *SUM(
CASE
WHEN Cid= 3AND score>= 60THEN1ELSE0END
)/ SUM(CASE WHEN Cid = 3 THEN 1 ELSE 0 END) '英语及格率'FROM sc
SELECT
SUM(
CASE
WHEN Cid= 1THEN
score
ELSE0END
)/ SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0 END) AS '语文平均分',
CONCAT(
LEFT (100 *SUM(
CASE
WHEN Cid= 1AND score>= 60THEN1ELSE0END
)/ SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0END),5),'%')'语文及格率',
SUM(
CASE
WHEN Cid= 2THEN
score
ELSE0END
)/ SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0 END) AS '数学平均分',
CONCAT(
LEFT (100 *SUM(
CASE
WHEN Cid= 2AND score>= 60THEN1ELSE0END
)/ SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0END),5),'%')'数学及格率',
SUM(
CASE
WHEN Cid= 3THEN
score
ELSE0END
)/ SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0 END) AS '英语平均分',
CONCAT(
LEFT (100 *SUM(
CASE
WHEN Cid= 3AND score>= 60THEN1ELSE0END
)/ SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0END),5),'%')'英语及格率'FROM
sc
21、查询不同老师所教不同课程平均分从高到低显示
需求：查询老师，课程，分数(teacher,sc,course)
条件：不同老师，不同课程，平均分，倒序
说明：不同老师，按老师分组，不同课程，按课程分组，平均分 avg，倒序desc 老师为条件，主要结果为平均分，所以主表为sc
SELECT
teacher.Tname,
course.Cname,
AVG(sc.score)
FROM
sc,
course,
teacher
WHERE
sc.Cid=course.Cid
AND course.Tid=teacher.Tid
GROUP BY
course.Tid,
sc.Cid
ORDER BY
AVG(sc.score) DESC;
SELECT
MAX(z.Tname),
MAX(c.Cname),
AVG(t.score)
FROM
sc t,
course c,
teacher z
WHERE
t.Cid=c.Cid
AND c.Tid=z.Tid
GROUP BY
c.Cid
ORDER BY
AVG(t.score) DESC
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：语文(1)，数学(2)，英语 (3)[学生ID],[学生姓名],语文，数学，英语，平均成绩
需求：(课程表，成绩表，学生表)
条件：课程成绩第 3 名到第 4名的学生成绩单
说明：课程成绩从第x名到第y名 order by 总成绩，同一个人多列成绩转变为一个人一列多课程成绩，这样的查询可以自关联已课程iD为条件，这里avgScore 不能复用totalScore/4进行计算
SELECT DISTINCT
sc.Sid,
student.Sname,
sc1.score AS '语文',
sc2.score AS '数学',
sc3.score AS '英语',
sc4.score AS '政治',
(
IFNULL(sc1.score, 0) + IFNULL(sc2.score, 0) + IFNULL(sc3.score, 0) + IFNULL(sc4.score, 0)
) AS totalScore,
(
IFNULL(sc1.score, 0) + IFNULL(sc2.score, 0) + IFNULL(sc3.score, 0) + IFNULL(sc4.score, 0)
)/4 AS avgScore
FROM
student,
sc
LEFT JOIN sc AS sc1 ON sc.Sid = sc1.Sid
AND sc1.Cid = 1
LEFT JOIN sc AS sc2 ON sc.Sid = sc2.Sid
AND sc2.Cid = 2
LEFT JOIN sc AS sc3 ON sc.Sid = sc3.Sid
AND sc3.Cid = 3
LEFT JOIN sc AS sc4 ON sc.Sid = sc4.Sid
AND sc4.Cid = 4
WHERE
student.Sid = sc.Sid
ORDER BY
totalScore
LIMIT 2,
2;
23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
需求：课程信息，分数信息(课程表，分数表)
条件：各科成绩
说明：统计if(条件) 个数等信息：case when else end
SELECT
course.Cid,
course.Cname,
(
SELECT
SUM(
CASE
WHEN sc.score BETWEEN85AND100THEN1ELSE0END
)
FROM
sc
WHERE
sc.Cid=course.Cid
AND sc.score
) AS'85-100',
(
SELECT
SUM(
CASE
WHEN sc.score BETWEEN70AND85THEN1ELSE0END
)
FROM
sc
WHERE
sc.Cid=course.Cid
AND sc.score
) AS'70-85',
(
SELECT
SUM(
CASE
WHEN sc.score BETWEEN60AND70THEN1ELSE0END
)
FROM
sc
WHERE
sc.Cid=course.Cid
AND sc.score
) AS'60-70',
(
SELECT
SUM(
CASE
WHEN sc.score< 60THEN1ELSE0END
)
FROM
sc
WHERE
sc.Cid=course.Cid
AND sc.score
) AS'<60'FROM
course;
SELECT
course.Cid,
course.Cname,
SUM(
CASE
WHEN sc.score BETWEEN85AND100THEN1ELSE0END
) AS'85-100',
SUM(
CASE
WHEN sc.score BETWEEN70AND85THEN1ELSE0END
) AS'70-85',
SUM(
CASE
WHEN sc.score BETWEEN60AND70THEN1ELSE0END
) AS'60-70',
SUM(
CASE
WHEN sc.score< 60THEN1ELSE0END
) AS'<60'FROM
course,
sc
WHERE
course.Cid=sc.Cid
GROUP BY
course.Cid,
course.Cname;
24、查询学生平均成绩及其名次
需求：查询平均成绩和名次
条件：学生的平均成绩和名次
说明：平均成绩需要按学生分组，学生名次：子查询中的分数和主查询中的分数作比较统计个数 + 1 可以作为名次
SELECT
Sid,
avgScore,
(
SELECT1 +COUNT(avgScore)
FROM
(
SELECT
Sid,
AVG(score) AS avgScore
FROM
sc
GROUP BY
Sid
) AS t1
WHERE
t1.avgScore>t2.avgScore
) AS ranking
FROM
(
SELECT
Sid,
AVG(score) AS avgScore
FROM
sc
GROUP BY
Sid
) AS t2
ORDER BY
avgScore DESC
25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
需求：查询课程表(sc)
条件：各科成绩前三，不考虑并列
分析：并列是否考虑先不说，各科成绩前三，说明两个条件，各科，成绩前三，需要先查询前三的成绩，参数为课程id，因为mysql 不支持top，所以只能使用limit，又因为limit不能用在in里边，同时临时表中不能使用主表字段做比较条件，这样就给该查询造成了困难。
这里要用到一个函数EXISTS():exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当 exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录，反之如果exists里的条 件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为 false
查询sc表，已分数作为条件在查询sc表，如果子表分数大于主表分数的数量 > 1就是前一名，如果 > 2就是前2名，如果有并列都会查询出来
SELECT*FROM
sc AS t
WHERE
EXISTS (
SELECT
COUNT(1)
FROM
sc
WHERE
t.Cid=sc.Cid
AND t.score
HAVING
COUNT(1) < 1)
ORDER BY Cid
26. 查询每门课程被选修的学生数
需求：求学生数(sc表)
条件：每门课程被选修的学生数
说明：聚合函数，学生信息，按学生分组
SELECT
sc.Cid,
course.Cname,
COUNT(sc.Sid)
FROM
sc
JOIN course ON sc.Cid=course.Cid
GROUP BY
sc.Cid;
27、查询出只选修了一门课程的全部学生的学号和姓名
需求：学生的学号和姓名(sc,student)
条件：只选修了一门课程
说明：sc,student两个表关联，最后有个条件一门课程，就是要对sc表中的Cid使用聚合函数，但是就不能再查下学生信息，这是个误区。所以不考虑链接查询，替换情况就是查询学生表通过in函数来查询。通过查询成绩表中的学生id，按照学生id分组，一个学生id对应多个课程id，然后对课程id聚合数量作为条件查出符合条件的学生id
SELECT
student.Sid,
student.Sname
FROM
student
WHERE
student.Sid IN (
SELECT
sc1.Sid
FROM
sc AS sc1
GROUP BY
sc1.Sid
HAVING
COUNT(Cid)= 1);
28、查询男生、女生人数
需求：查询不同种类的学生人数(student)
条件：不同种类的学生人数
说明：同一表，同一查询(不同条件)多次查询
SELECT
COUNT(student.Ssex)
FROM
student
GROUP BY
student.Ssex
HAVING
student.Ssex= 1;
SELECT
COUNT(student.Ssex)
FROM
student
GROUP BY
student.Ssex
HAVING
student.Ssex= 0;
29、查询姓“张”的学生名单
SELECT
Sname
FROM
student
WHERE
Sname LIKE'%张';
30、查询同名同性学生名单，并统计同名人数
说明：相同姓名，即要按照姓名分组。同名人数，即人数大于1
SELECT
Sname,
COUNT(Sname)
FROM
student
GROUP BY
Sname
HAVING
COUNT(*) > 1;
31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
说明：日期转换，date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') 20080808222301
SELECT
DATE_FORMAT(NOW(),'%Y') AS age,
Sname
FROM
student
WHERE
DATE_FORMAT(NOW(),'%Y') = 2018;
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
说明：升序ASC，降序DESC
SELECT
Cid,AVG(score)
FROM
sc
GROUP BY
Cid
ORDER BY
AVG(score),Cid DESC
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
说明：分组查询，条件使用having
SELECT
student.Sid,
student.Sname,
AVG(sc.score)
FROM
student
INNER JOIN sc ON student.Sid=sc.Sid
GROUP BY student.Sid,student.Sname
HAVING
AVG(sc.score)> 85
SELECT
Sname,
sc.Sid,
AVG(score)
FROM
student,
sc
WHERE
student.Sid=sc.Sid
GROUP BY
sc.Sid,
Sname
HAVING
AVG(score)> 85;
34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
SELECT
student.Sname,
IFNULL(sc.score,0)
FROM
student,
sc,
course
WHERE
student.Sid = sc.Sid
AND sc.Cid = course.Cid
AND sc.score < 60
AND course.Cname = "政治";
//这里由于sc表中score不能为null，并且是全连接所以ifnull无意义
SELECT
student.Sname,
IFNULL(sc.score, 0)
FROM
student
LEFT JOIN sc ON student.Sid = sc.Sid
LEFT JOIN course ON sc.Cid = course.Cid
WHERE
sc.score < 60
AND course.Cname = "政治";
//这里由于student左链接其他两个表，所以ifnull给null值赋默认值
35、查询所有学生的选课情况；
说明：如果为null给默认值ifnull
SELECT
sc.Sid,
student.Sname,
sc.Cid,
course.Cname
FROM
sc,
student,
course
WHERE
sc.Sid=student.Sid
AND sc.Cid=course.Cid;
SELECT
student.Sid,
student.Sname,
IFNULL(course.Cid,-1),
IFNULL(course.Cname,"未选课程")
FROM student LEFT JOIN sc ON student.Sid= sc.Sid LEFT JOIN course ON sc.Cid = course.Cid
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
说明：只要有一门课程在70分以上，没有任何一门课程在70分以上，所有课程在70分以上
//只要有一门课程在70分以上-满足条件即可
SELECT DISTINCT
student.Sid,
student.Sname,
sc.Cid,
sc.score
FROM
student,
sc
WHERE
student.Sid=sc.Sid
AND sc.score>= 70;
//所有课程在70分以下，多转一，取反
SELECT DISTINCT
student.Sid,
student.Sname
FROM
student
LEFT JOIN sc ON student.Sid=sc.Sid
WHERE
student.Sid NOT IN (
SELECT
student.Sid
FROM
student
LEFT JOIN sc ON student.Sid=sc.Sid
WHERE
sc.score >= 70)
//所有课程在70分以上，多转一，取反
SELECT DISTINCT
student.Sid,
student.Sname
FROM
student
LEFT JOIN sc ON student.Sid=sc.Sid
WHERE
student.Sid NOT IN (
SELECT
student.Sid
FROM
student
LEFT JOIN sc ON student.Sid=sc.Sid
WHERE
sc.score < 70)
A表左链接B表，以A表为主，如果查询字段有B表字段那么A表中没有关联B表的关联关系，那么结果中就会出现一行A字段为没有，B字段为null的列
37、查询不及格的课程，并按课程号从大到小排列
说明：倒序desc
SELECT sc.Cid FROM sc WHERE sc.score < 60 ORDER BY sc.Cid DESC
38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
SELECT
sc.Sid,
student.Sname
FROM
student,
sc
WHERE
student.Sid=sc.Sid
AND sc.Cid= '1'AND sc.score> 80
39、求选了课程的学生人数
说明：多级查询
SELECT COUNT(1) FROM (SELECT COUNT(sc.Sid) FROM sc GROUP BY sc.Sid) AS scount
40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
说明：成绩最高，通过子查询查询出最高成绩在和成绩形成等于关系
SELECT
sc.Sid,
student.Sname,
sc.score
FROM
sc,
student,
course,
teacher
WHERE
sc.Sid=student.Sid
AND sc.Cid=course.Cid
AND course.Tid=teacher.Tid
AND teacher.Tname= "语文老师"AND sc.score=(
SELECT
MAX(sc.score)
FROM
sc,
teacher,
course
WHERE
sc.Cid=course.Cid
AND course.Tid=teacher.Tid
AND teacher.Tname= "语文老师");
子查询中可以使用父查询表中的字段，子查询中查询一个字段，不用给结果as别名
SELECT
sc.Sid,
student.Sname,
sc.score
FROM
sc,
student,
course,
teacher
WHERE
sc.Sid=student.Sid
AND sc.Cid=course.Cid
AND course.Tid=teacher.Tid
AND teacher.Tname= "语文老师"AND sc.score=(
SELECT
MAX(sc1.score)
FROM
sc sc1
WHERE
sc1.Cid=sc.Cid
);
41、查询各个课程及相应的选修人数
说明：按照课程id，课程名称分组查询选同一门课程的人数
SELECT
course.Cid,
course.Cname,
COUNT(sc.Sid)
FROM
sc
LEFT JOIN course ON sc.Cid=course.Cid
LEFT JOIN student ON sc.Sid=student.Sid
GROUP BY sc.Cid
SELECT COUNT(1) FROM sc GROUP BY Cid
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
说明：所有条件和查询结果都在一张表中，可以自关联来满足多个条件
SELECT
a.Sid,
b.score
FROM
sc AS a,
sc AS b
WHERE
a.score=b.score
AND a.Cid!= b.Cid
43、查询每门课程成绩最好的前两名
说明：条件拆分：每门课程 + 成绩前两名，正常请求下如果说每门课程就要按照课程分组了，但是这里的后续条件前两名，就不能按照课程分组了，结果会是一门课程多条数据的一个集合。所以重点条件是第二个条件：前两名，所以成绩表里的每一条数据和成绩表里的每一条数据作比较来求数量，条件为课程id相同，且条件查询分数大于主查询分数，这个数量<=1就是前二名，因为如果相当于自关联，这个数量的值为0，那么这门课程就一个人，如果这个数量是1，那么说明同一门课比他分数高的有一人，那么他就是第二名。当然，这个查询是包含并列的。比我成绩高有一个人，我俩就是前二名。
SELECT
sc1.Sid,
sc1.Cid,
sc1.score
FROM
sc AS sc1
WHERE
score IN (
SELECT
sc2.score
FROM
sc AS sc2
WHERE sc1.score=sc2.score
ORDER BY sc2.score DESC
LIMIT2)
[Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
包含并列成绩
SELECT
sc1.Sid,
sc1.Cid,
sc1.score
FROM
sc AS sc1
WHERE
(
SELECT
COUNT(1)
FROM
sc AS sc2
WHERE
sc2.Cid=sc1.Cid
AND sc2.score>sc1.score
)<= 1ORDER BY sc1.Cid
44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
说明：在成绩表中查询每门课程输出一行，典型的分组问题。
SELECT
sc.Cid,
COUNT(sc.Sid)
FROM
sc
GROUP BY
sc.Cid
HAVING
COUNT(sc.Sid)> 2ORDER BY
COUNT(sc.Sid) DESC,
sc.Cid ASC
SELECT
sc.Cid,
COUNT(1)
FROM
sc
GROUP BY
sc.Cid
HAVING
COUNT(1) > 2ORDER BY
COUNT(1) DESC,
sc.Cid ASC
45、检索至少选修两门课程的学生学号
说明：至少选两门课程，所选课程数大于1，查询学号，按照学号分组，课程数大于1为条件
SELECT
sc.Sid
FROM
sc
GROUP BY
sc.Sid
HAVING
COUNT(1) > 3
46、查询全部学生选修的课程的课程号和课程名
说明：全部学生id集合作为条件查询成绩表
SELECT
Cid,
Cname
FROM
course
WHERE
Cid IN (SELECT Cid FROM sc GROUP BY Cid)
47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
说明：没有。。。 = not in 。。。
SELECT
Sid,
Sname
FROM
student
WHERE
Sid NOT IN (
SELECT
Sid
FROM
sc,
course,
teacher
WHERE
sc.Cid=course.Cid
AND course.Tid=teacher.Tid
AND teacher.Tname= "语文老师")
48、查询两门以上不及格课程的同学的学号及其平均成绩
说明：不及格成绩>2的人
SELECT
sc1.Sid,
AVG(sc1.score)
FROM
sc sc1
GROUP BY
sc1.Sid
HAVING
(
SELECT
count(1)
FROM
sc AS sc2
WHERE
sc1.Sid=sc2.Sid
AND sc2.score< 60)> 2;
SELECT
Sid,
AVG(score)
FROM
sc
WHERE
Sid IN (
SELECT
Sid
FROM
sc
WHERE
score< 60GROUP BY
Sid
HAVING
COUNT(*) > 2)
GROUP BY Sid
49、检索“004”课程分数小于60，按分数降序排列的同学学号
SELECT
Sid
FROM
sc
WHERE
Cid= 2AND score< 60ORDER BY
score DESC;
50、删除“002”同学的“001”课程的成绩
DELETE
FROM
sc
WHERE
Sid= 1AND Cid= 1
761
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
