数据表
-
学生表 Student (SId,Sname,Sage,Ssex)
- SId 学生编号
- Sname 学生姓名
- Sage 出生年月
- Ssex 学生性别
-
课程表 Course (CId,Cname,TId)
- CId 课程编号
- Cname 课程名称
- TId 教师编号
-
教师表 Teacher (TId,Tname)
- TId 教师编号
- Tname 教师姓名
-
成绩表 SC (SId,CId,score)
- SId 学生编号
- CId 课程编号
- score 分数
查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
USE test1;
SELECT * FROM
sc sc1
JOIN sc sc2 USING (SId)
WHERE sc1.CId = '01'
AND sc2.CId = '02'
AND sc1.score > sc2.score
查询同时存在" 01 “课程和” 02 "课程的情况
USE test1;
SELECT * FROM
sc sc1
JOIN sc sc2 USING (SId)
WHERE sc1.CId = '01'
AND sc2.CId = '02'
查询存在" 01 “课程但可能不存在” 02 "课程的情况 (不存在时显示为 null)
USE test1;
SELECT * FROM
(
SELECT * FROM sc sc1
WHERE CId = '01'
) AS t1
LEFT JOIN
(
SELECT * FROM sc sc2
WHERE CId = '02') AS t2
USING (SId)
查询不存在" 01 “课程但存在” 02 "课程的情况
USE test1;
SELECT * FROM sc
WHERE SId NOT IN (
SELECT SId FROM sc
WHERE CId = '01'
)
AND sc.CId = '02'
查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
USE test1;
SELECT t1.SId, t1.Sname, t2.average
FROM student t1
JOIN (
SELECT SId, AVG(score) average FROM sc
GROUP BY SId
HAVING average >= 60
) t2
USING (SId)
查询在 SC 表存在成绩的学生信息
USE test1;
SELECT * FROM student
WHERE SId IN (
SELECT SId FROM sc
)
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
USE test1;
SELECT t1.SId, t1.Sname, t2.count_course, t2.sum_score
FROM student t1
LEFT JOIN (
SELECT SId, COUNT(CId) as count_course, SUM(score) as sum_score
FROM sc
GROUP BY SId) t2
USING (SId)
查有成绩的学生信息
USE test1;
SELECT *
FROM student
WHERE EXISTS (
SELECT * FROM sc
WHERE student.SId=sc.SId
)
查询「李」姓老师的数量
USE test1;
SELECT count(*) num FROM teacher
WHERE Tname REGEXP '^李'
查询学过「张三」老师授课的同学的信息
方法1:使用嵌套查询
USE test1;
SELECT * FROM student
WHERE SId IN (
SELECT SId FROM sc
WHERE CId IN (
SELECT CId FROM course
JOIN teacher USING (TId)
WHERE Tname = '张三'
)
)
方法2:使用自然连接
USE test1;
SELECT s.* FROM
student s, teacher t, course c, sc
WHERE s.SId = sc.SId
AND t.TId = c.TId
AND c.CId = sc.CId
AND t.Tname = '张三'
查询没有学全所有课程的同学的信息
方法1:
USE test1;
SELECT SId,COUNT(CId) course_num FROM sc
GROUP BY SId
HAVING course_num < (
SELECT COUNT(CId) FROM course
)
方法2:
USE test1;
SELECT t1.SId, t1.CId, COUNT(sc.CId) as course_num
FROM (
SELECT student.SId, course.CId
FROM student,course
) AS t1
LEFT JOIN sc
ON t1.SId = sc.SId
AND t1.CId = sc.CId
GROUP BY SId
HAVING course_num < (
SELECT COUNT(CId) FROM course
)
查询至少有一门课与学号为"01"的同学所学相同的同学的信息
USE test1;
SELECT DISTINCT student.* FROM sc
JOIN student
USING (SId)
WHERE sc.CId IN (
SELECT CId FROM sc WHERE SId = '01'
)
查询和"01"号的同学学习的课程完全相同的其他同学的信息
USE test1;
SELECT sc2.SId, student.Sname FROM sc sc1
JOIN sc sc2
ON sc1.CId = sc2.CId
AND sc1.SId != sc2.SId
AND sc1.SID = '01'
JOIN student
ON student.SId = sc2.SId
GROUP BY sc2.SId
HAVING COUNT(*) = (SELECT COUNT(*) FROM sc WHERE SId = '01')
查询没学过"张三"老师讲授的任一门课程的学生姓名
USE test1;
SELECT s.Sname, COUNT(t1.CId) num
FROM student s
LEFT JOIN sc USING (SId)
LEFT JOIN (
SELECT CId FROM course
JOIN teacher USING (TId)
WHERE Tname = '张三'
) t1
ON t1.CId = sc.CId
GROUP BY SId
HAVING num = 0
查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
USE test1;
SELECT SId, Sname, AVG(score) average, COUNT(IF(score<60,1,NULL)) num
FROM sc
JOIN student
USING (SId)
GROUP BY SId
HAVING num>=2
检索"01"课程分数小于 60,按分数降序排列的学生信息
USE test1;
SELECT student.*, score
FROM sc
JOIN student
USING (SId)
WHERE CId = '01'
AND score < 60
ORDER BY score DESC
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
USE test1;
SELECT SId, Sname, CId, score, (
SELECT AVG(score) FROM sc
WHERE SId = t1.SId
) AS average
FROM sc t1
JOIN student
USING (SId)
ORDER BY average DESC
查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
USE test1;
SELECT
CId,
Cname,
MAX(score) as max,
MIN(score) as min,
AVG(score) as average,
COUNT(SId) as num,
COUNT(IF(score BETWEEN 60 AND 69,1,NULL)) / COUNT(SId) as 及格,
COUNT(IF(score BETWEEN 70 AND 79,1,NULL)) / COUNT(SId) as 中等,
COUNT(IF(score BETWEEN 80 AND 89,1,NULL)) / COUNT(SId) as 优良,
COUNT(IF(score >= 90,1,NULL)) / COUNT(SId) as 优秀
FROM sc
JOIN course
USING (CId)
GROUP BY CId
ORDER BY num,CId
按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
USE test1;
SET @curRank := 0;
SET @prevRank := NULL;
SET @incRANK := 1;
SELECT CId, score, myrank
FROM (
SELECT CId, score, @curRank := IF(@prevRank = score, @curRank, @incRank) AS myrank,
@incRANK := @incRANK + 1,
@prevRank := score
FROM sc
ORDER BY score DESC
) t1
按各科成绩进行排序,并显示排名, Score 重复时合并名次
相同分数名次不同
USE test1;
SET @curRank := 0;
SELECT CId, score, @curRank:= @curRank+1 AS myrank
FROM sc
ORDER BY score DESC
相同分数名词相同
USE test1;
SET @curRank := 0;
SET @prevRank := NULL;
SELECT CId, score,
CASE
WHEN @prevRank = score THEN @curRank
WHEN @prevRank := score THEN @curRank:= @curRank + 1
END AS myrank
FROM sc
ORDER BY score DESC
查询学生的总成绩,并进行排名,总分重复时保留名次空缺
USE test1;
SET @curRank := 0;
SET @prevScore := NULL;
SET @incRank := 1;
SELECT SId, sumScore, myrank
FROM (
SELECT SId, sumScore,
@curRank := IF(@prevScore = sumScore, @curRank, @incRank) AS myrank,
@incRank := @incRank +1,
@prevScore := sumScore
FROM (
SELECT SId, SUM(score) sumScore FROM sc
GROUP BY SId
ORDER BY sumScore DESC
) t1
) t2
查询学生的总成绩,并进行排名,总分重复时合并名次
USE test1;
SET @curRank := 0;
SET @prevScore := NULL;
SELECT SId, sumScore,
CASE
WHEN @prevScore = sumScore THEN @curRank
WHEN @prevScore := sumScore THEN @curRank := @curRank + 1
END AS myrank
FROM (
SELECT SId, SUM(score) sumScore FROM sc
GROUP BY SId
ORDER BY sumScore DESC
) t1
统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
USE test1;
SELECT
CId,
Cname,
COUNT(IF(score BETWEEN 85 AND 100,1,NULL)) AS '[85-100]',
COUNT(IF(score BETWEEN 70 AND 84,1,NULL)) AS '[70-85)',
COUNT(IF(score BETWEEN 60 AND 69,1,NULL)) AS '[60-70)',
COUNT(IF(score BETWEEN 0 AND 59,1,NULL)) AS '[0,60)',
COUNT(IF(score BETWEEN 85 AND 100,1,NULL))/COUNT(*) AS '[85-100]',
COUNT(IF(score BETWEEN 70 AND 84,1,NULL))/COUNT(*) AS '[70-85)',
COUNT(IF(score BETWEEN 60 AND 69,1,NULL))/COUNT(*) AS '[60-70)',
COUNT(IF(score BETWEEN 0 AND 59,1,NULL))/COUNT(*) AS '[0,60)'
FROM sc
JOIN course USING(CId)
GROUP BY CId
查询各科成绩前三名的记录
方法1:通过组内排名来实现
USE test1;
SET @curRank := 1;
SET @preCId := NULL;
SELECT * FROM (
SELECT *,
CASE
WHEN @preCId = CId THEN @curRank := @curRank + 1
WHEN @preCId := CId THEN @curRank := 1
END AS myrank
FROM (
SELECT * FROM sc
ORDER BY CId,score DESC
) t1
) t2
WHERE myrank <= 3
方法2:前三名转化为若大于此成绩的人数少于三
USE test1;
SELECT * FROM sc
WHERE (
SELECT count(*) FROM sc t1
WHERE sc.CId = t1.CId
AND sc.score < t1.score
) < 3
ORDER BY CId ,sc.score DESC
查询每门课程被选修的学生数
USE test1;
SELECT CId, Cname, COUNT(SId) num
FROM sc JOIN course USING (CId)
GROUP BY CId
查询出只选修两门课程的学生学号和姓名
方法1:
USE test1;
SELECT DISTINCT SId, Sname
FROM sc JOIN student USING (SId)
WHERE (
SELECT COUNT(*) FROM sc t1
WHERE t1.SId = sc.SId
GROUP BY SId
) = 2
方法2:
USE test1;
SELECT SId, Sname
FROM sc JOIN student USING (SId)
GROUP BY SId
HAVING COUNT(*) = 2
查询男生、女生人数
USE test1;
SELECT Ssex, COUNT(*) num
FROM student
GROUP BY Ssex
查询名字中含有「风」字的学生信息
USE test1;
SELECT * FROM student
WHERE Sname REGEXP '风'
查询同名同性学生名单,并统计同名人数
USE test1;
SELECT * FROM student
WHERE Sname IN (
SELECT Sname FROM student
GROUP BY Sname, SSex
HAVING COUNT(*) >= 2
)
查询 1990 年出生的学生名单
USE test1;
SELECT * FROM student
WHERE YEAR(Sage) = 1990
查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
USE test1;
SELECT CId, AVG(score) average
FROM sc
GROUP BY CId
ORDER BY average DESC, CId
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
USE test1;
SELECT SId, Sname, average
FROM student JOIN (
SELECT SId, AVG(score) average FROM sc
GROUP BY SId
HAVING average >= 85
) t1 USING (SId)
查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
USE test1;
SELECT Sname, score
FROM sc
JOIN course USING (CId)
JOIN student USING (SId)
WHERE Cname = '数学'
AND score < 60
查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
USE test1;
SELECT SId, Sname, Cname, score
FROM student
LEFT JOIN sc USING (SId)
LEFT JOIN course USING (CId)
ORDER BY SId
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
USE test1;
SELECT Sname, score, Cname
FROM sc
JOIN student USING (SId)
JOIN course USING (CId)
WHERE score > 70
查询存在不及格的课程
USE test1;
SELECT DISTINCT CId, Cname
FROM sc
JOIN course USING (CId)
WHERE score < 60
查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
USE test1;
SELECT SId, Sname
FROM sc
JOIN student USING (SId)
WHERE CId = 01
AND score > 80
求每门课程的学生人数
USE test1;
SELECT CId, COUNT(*) num
FROM sc
GROUP BY CId
成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
USE test1;
SELECT SId, Sname, Sage, Ssex, score
FROM sc
JOIN student USING (SId)
WHERE CId = (
SELECT CId
FROM teacher JOIN course USING (TId)
WHERE Tname = '张三'
)
ORDER BY score DESC
LIMIT 1
成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
方法1:
USE test1;
SELECT SId, Sname, Sage, Ssex, score
FROM sc
JOIN student USING (SId)
WHERE CId = (
SELECT CId
FROM teacher JOIN course USING (TId)
WHERE Tname = '张三'
)
AND score = (
SELECT MAX(score)
FROM sc
JOIN course USING (CId)
JOIN teacher USING (TId)
WHERE Tname = '张三'
)
方法2:
USE test1;
SET @prevScore := NULL;
SET @myRank := 0;
SELECT student.*, t1.score
FROM student
JOIN (
SELECT sc.SId, sc.score,
CASE
WHEN @prevScore = sc.score THEN @myRank
WHEN @prevScore := sc.score THEN @myRank := @myRank+1
END AS myRank
FROM course, teacher, sc
WHERE course.CId = sc.CId
AND course.TId = teacher.TId
AND teacher.Tname = '张三'
ORDER BY sc.score DESC
) AS t1
ON student.SId=t1.SId
WHERE t1.myRank = 1
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
USE test1;
SELECT * FROM sc t1
WHERE EXISTS (
SELECT * FROM sc t2
WHERE t1.score = t2.score
AND t1.CId != t2.CId
AND t1.SId = t2.SId
)
查询每门课成绩最好的前两名
USE test1;
SELECT *
FROM sc t1
WHERE (
SELECT COUNT(*) FROM sc t2
WHERE t1.CId = t2.CId
AND t1.score < t2.score
) < 2
ORDER BY CId
统计每门课程的学生选修人数(超过 5 人的课程才统计)
USE test1;
SELECT CId, COUNT(*) num
FROM sc
GROUP BY CId
HAVING num > 5
检索至少选修两门课程的学生学号
USE test1;
SELECT SId FROM sc
GROUP BY SId
HAVING COUNT(*) >= 2
查询选修了全部课程的学生信息
USE test1;
SELECT SId, Sname
FROM sc
JOIN student USING (SId)
GROUP BY SId
HAVING COUNT(*) = (
SELECT COUNT(*) FROM course
)
查询各学生的年龄,只按年份来算
方法1
USE test1;
SELECT SId, Sname, YEAR(Sage) birth_year
FROM student
方法2
USE test1;
SELECT SId, Sname, TIMESTAMPDIFF(YEAR,Sage,CURDATE()) age
FROM student
按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
USE test1;
SELECT *,
CASE
WHEN MONTH(Sage) > MONTH(NOW()) THEN YEAR(NOW()) - YEAR(Sage)
WHEN MONTH(Sage) < MONTH(NOW()) THEN YEAR(NOW()) - YEAR(Sage) - 1
WHEN DAY(Sage) >= DAY(NOW()) THEN YEAR(NOW()) - YEAR(Sage)
ELSE YEAR(NOW()) - YEAR(Sage) - 1
END age
FROM student
查询本周过生日的学生
USE test1;
SELECT * FROM student
WHERE YEARWEEK(student.Sage) = YEARWEEK(CURDATE())
查询下周过生日的学生
方法1
USE test1;
SELECT * FROM student
WHERE YEARWEEK(student.Sage) = YEARWEEK(CURDATE()) + 1
方法2
USE test1;
SELECT * FROM student
WHERE YEARWEEK(Sage) = CONCAT(YEAR(CURDATE()),WEEK(CURDATE())+1)
查询本月过生日的学生
USE test1;
SELECT * FROM student
WHERE MONTH(Sage) = MONTH(CURDATE())
查询下月过生日的学生
USE test1;
SELECT * FROM student
WHERE MONTH(Sage) = (MONTH(CURDATE())+1) MOD 12