数据
-- 学生
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
-- 课程
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
-- 老师
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-- 分数
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
学生
老师
课程
分数
01
查询"01"课程比"02"课程成绩高的学生的信息及课程分数
先查询01课程
再查询02
交集
SELECT
*
FROM
(SELECT * FROM SC WHERE cid='01') a
JOIN
(SELECT * FROM SC WHERE cid='02') b on a.sid = b.sid
增加限制条件,01分数 > 02分数
关联查询课程信息
SELECT
*
FROM
(SELECT * FROM SC WHERE cid='01') a
JOIN
(SELECT * FROM SC WHERE cid='02') b on a.sid = b.sid and a.score > b.score
JOIN
Course c on a.cid = c.cid
JOIN
Course d on b.cid = d.cid
关联查询学生信息
SELECT
*
FROM
(SELECT * FROM SC WHERE cid='01') a
JOIN
(SELECT * FROM SC WHERE cid='02') b on a.sid = b.sid and a.score > b.score
JOIN
Course c on a.cid = c.cid
JOIN
Course d on b.cid = d.cid
JOIN
Student e on a.sid = e.sid
汇总
-- 01
SELECT
e.sid,
e.sname,
e.sage,
e.ssex,
a.cid AS cid01,
c.cname AS cname01,
a.score AS cscore01,
b.cid AS cid02,
d.cname AS cname02,
b.score AS cscore02
FROM
( SELECT * FROM SC WHERE cid = '01' ) a
JOIN ( SELECT * FROM SC WHERE cid = '02' ) b ON a.sid = b.sid AND a.score > b.score
JOIN Course c ON a.cid = c.cid
JOIN Course d ON b.cid = d.cid
JOIN Student e ON a.sid = e.sid
02
查询学生选课存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null)
SELECT
*
FROM
( SELECT * FROM SC WHERE cid = '01' ) a
LEFT JOIN ( SELECT * FROM SC WHERE cid = '02' ) b ON a.sid = b.sid
03
查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
先查询平均分
SELECT
*,
AVG(score) avg
FROM sc
GROUP BY sc.sid
HAVING avg >= 60
再多表关联查询
SELECT
a.sid, b.sname, a.avg
FROM
( SELECT *, AVG( score ) avg FROM sc GROUP BY sc.sid HAVING avg >= 60 ) a
JOIN Student b ON a.sid = b.sid
04
查询在 SC 表存在成绩的学生信息
SELECT b.*, a.score FROM sc a, Student b WHERE a.sid = b.sid
GROUP BY a.sid
-- or
SELECT b.*, a.score FROM sc a
JOIN Student b on a.sid = b.sid
GROUP BY a.sid
05
查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
SELECT
a.sid,
b.sname,
COUNT(DISTINCT a.cid) count,
SUM(a.score) scores
FROM sc a
JOIN Student b on a.sid = b.sid
GROUP BY a.sid
06
查询「李」姓老师的数量
SELECT (COUNT(DISTINCT tid)) count FROM Teacher WHERE tname like '李%'
07
查询学过「张三」老师授课的同学的信息
SELECT
a.sid,
d.sname,
d.sage,
d.ssex,
c.tid,
c.tname
FROM
sc a
JOIN Course b ON a.cid = b.cid
JOIN Teacher c ON b.tid = c.tid AND c.tname = '张三'
JOIN Student d ON d.sid = a.sid
08
查询没有学全所有课程的同学的信息
学生表关联成绩表,进行匹配查询
SELECT
*
FROM
Student a
LEFT JOIN sc b on a.sid = b.sid
根据sid进行分组,查询cid的个数
SELECT
*,
COUNT(DISTINCT b.cid) count
FROM
Student a
LEFT JOIN sc b on a.sid = b.sid GROUP BY b.sid
根据课程总数进行过滤
SELECT
a.*,
COUNT(DISTINCT b.cid) count
FROM
Student a
LEFT JOIN sc b on a.sid = b.sid GROUP BY b.sid
HAVING count < (SELECT COUNT(*) FROM Course)
09
查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
关联查询学生表和成绩表
SELECT
a.*,
b.*
FROM
Student a
JOIN sc b ON a.sid = b.sid
筛选匹配学号为" 01 "的同学所学课程
SELECT
a.*,
b.*
FROM
Student a
JOIN sc b ON a.sid = b.sid
WHERE
b.cid in (SELECT cid FROM sc WHERE sid='01')
按sid分组并排除01
SELECT
a.*,
b.*
FROM
Student a
JOIN sc b ON a.sid = b.sid
WHERE
b.cid in (SELECT cid FROM sc WHERE sid='01')
GROUP BY a.sid
HAVING a.sid != '01'
10
查询和" 01 "号的同学所学课程,完全相同的其他同学的信息
多表查询,排除01
SELECT
a.*,
b.*
FROM
Student a
JOIN sc b ON a.sid = b.sid and a.sid != '01'
拼接课程ID,方便后面比较
SELECT
a.*,
GROUP_CONCAT(b.cid SEPARATOR '-') courses
FROM
Student a
JOIN sc b ON a.sid = b.sid and a.sid != '01'
GROUP BY b.sid
和01同学比较
SELECT
a.*,
GROUP_CONCAT(b.cid SEPARATOR '-') courses
FROM
Student a
JOIN sc b ON a.sid = b.sid and a.sid != '01'
GROUP BY b.sid
HAVING courses=(SELECT GROUP_CONCAT(cid SEPARATOR '-') courses FROM sc WHERE sid='01' GROUP BY sid)
11
查询没学过"张三"老师讲授的任一门课程的学生姓名
先关联查询成绩表的课程信息和老师信息
SELECT
*
FROM
sc b
JOIN Course c ON b.cid = c.cid
JOIN Teacher d ON c.tid = d.tid
过滤出张三老师
SELECT
*
FROM
sc b
JOIN Course c ON b.cid = c.cid
JOIN Teacher d ON c.tid = d.tid
WHERE
tname = '张三'
从学生表中筛选出不在当前所含sid的其他同学信息
SELECT
*
FROM
Student
WHERE
sid NOT IN (
SELECT
b.sid
FROM
sc b
JOIN Course c ON b.cid = c.cid
JOIN Teacher d ON c.tid = d.tid
WHERE
tname = '张三'
)
12
查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
查询不及格
SELECT * FROM sc
WHERE score < 60
分组统计次数并筛选数量、计算平均成绩
SELECT
sid, AVG(score) avg
FROM sc
WHERE score < 60
GROUP BY sid
HAVING COUNT( cid ) >= 2
关联查询学生信息
SELECT
b.*,
a.avg
FROM
( SELECT sid, AVG( score ) avg FROM sc WHERE score < 60 GROUP BY sid HAVING COUNT( cid ) >= 2 ) a
JOIN Student b ON a.sid = b.sid
13
查询" 01 "课程分数小于 60,按分数降序排列的学生信息
SELECT
b.*,
a.score
FROM
( SELECT sid, score FROM sc WHERE score < 60 AND cid = '01' ORDER BY score DESC ) a
JOIN Student b ON a.sid = b.sid
14
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
a.*,
b.avg
FROM
SC a
JOIN ( SELECT sid, avg( score ) avg FROM sc GROUP BY sid ) b ON a.sid = b.sid
ORDER BY avg DESC
15
查询各科成绩最高分、最低分和平均分
字段:课程 id,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:[70,80),优良为:[80-90),优秀为:>=90
SELECT
cid,
MAX( score ) maxScore,
MIN( score ) minScore,
AVG( score ) avgScore,
COUNT( sid ) count,
SUM( CASE WHEN score >= 60 THEN 1 ELSE 0 END ) / COUNT( sid ) '及格率',
SUM( CASE WHEN score > 70 AND score < 80 THEN 1 ELSE 0 END ) / COUNT( sid ) '中等率',
SUM( CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END ) / COUNT( sid ) '优良率',
SUM( CASE WHEN score >= 90 THEN 1 ELSE 0 END ) / COUNT( sid ) '优秀率'
FROM
sc
GROUP BY
cid
ORDER BY
cid ASC
16
按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
SELECT
*,
rank() over ( PARTITION BY cid ORDER BY score DESC ) AS ranked
FROM
sc;
MySQL可以实现Oracle中的排名公式,一共有三种
- rank() over(order by col_name desc)
- dense_rank() over()
- row_number() over()
第一个是如果出现了相同排名都为同一排名,下个排名跳过,例如1,1,3,4
第二个是如果出现了相同排名都为同一排名,下个排名不跳过,例如1,1,2,3
第三个是直接对行进行排名不分是否有相同值
此题目要按照各科成绩进行排序 over()中要填partition by col_name order by col_name
第一个columname 为分组的内容,第二个是按什么值排的内容。
17
查询学生的总成绩,并进行排名,总分重复时保留名次空缺
SELECT
a.*,
RANK() over(ORDER BY sum DESC) ranked
FROM
(SELECT sid, sum(score) sum FROM sc GROUP BY sid) a
18
查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
SELECT
a.*,
DENSE_RANK() over(ORDER BY sum DESC) ranked
FROM
(SELECT sid, sum(score) sum FROM sc GROUP BY sid) a
19
统计各科成绩各分数段人数:课程编号,[100-85),[85-70),[70-60),[60-0] 及所占百分比
SELECT
cid,
SUM( CASE WHEN score <= 60 THEN 1 ELSE 0 END ) / count( sid ) p1,
SUM( CASE WHEN score > 60 AND score <= 70 THEN 1 ELSE 0 END ) / count( sid ) p2,
SUM( CASE WHEN score > 70 AND score <= 85 THEN 1 ELSE 0 END ) / count( sid ) p3,
SUM( CASE WHEN score > 85 THEN 1 ELSE 0 END ) / count( sid ) p4
FROM
SC
GROUP BY
cid
20
查询各科成绩前三名的记录
SELECT
*
FROM
( SELECT sid, cid, score, rank() over ( PARTITION BY cid ORDER BY score DESC ) ranked FROM sc ) a
WHERE
a.ranked <= 3
21
查询每门课程被选修的学生数
SELECT
cid,
COUNT(sid)
FROM
sc
GROUP BY cid
22
查询出只选修两门课程的学生学号和姓名
SELECT
a.sid,
a.count,
b.sname,
b.sage,
b.ssex
FROM
( SELECT sid, COUNT( cid ) count FROM sc GROUP BY sid HAVING count = 2 ) a
JOIN Student b ON a.sid = b.sid
SQL的执行顺序应该为 FORM-JOIN ON-WHERE-GROUP BY-HAVING-SELECT-DISTINCT-UNION-ORDER
23
查询男生、女生人数
SELECT
ssex,
COUNT(*) count
FROM
Student
GROUP BY ssex
24
查询名字中含有「风」字的学生信息
SELECT
*
FROM
Student
WHERE sname like '%风%'
25
查询同名同姓学生名单,并统计同名人数
SELECT
*,
COUNT(*) count
FROM
Student a
JOIN Student b on a.sname=b.sname and a.ssex=b.ssex
GROUP BY a.sid
HAVING count >= 2
26
查询 1990 年出生的学生名单
SELECT
*
FROM
Student
WHERE YEAR(sage) = 1990
27
查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECT
cid,
AVG( score ) avg
FROM
sc
GROUP BY
cid
ORDER BY avg DESC, cid asc
28
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT
a.*,
b.sname
FROM
( SELECT a.sid, avg( a.score ) avg FROM SC a GROUP BY sid HAVING avg >= 85 ) a
JOIN Student b on a.sid = b.sid
29
查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
SELECT
c.sid,
c.sname,
a.score,
b.cname
FROM
sc a
JOIN Course b on a.cid = b.cid and b.cname='数学'
JOIN Student c on a.sid = c.sid
WHERE score < 60
30
查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
SELECT
c.sname,
b.cname,
a.score
FROM
SC a
JOIN Course b ON a.cid = b.cid
JOIN Student c ON a.sid = c.sid
31
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT
c.sname,
b.cname,
a.score
FROM
(SELECT * FROM sc WHERE score > 70) a
JOIN Course b on a.cid = b.cid
JOIN Student c on a.sid = c.sid
32
查询不及格的课程
SELECT
a.sid,
b.cid,
b.cname,
a.score
FROM
(SELECT * FROM sc WHERE score < 60) a
JOIN Course b on a.cid = b.cid
33
查询课程编号为 01 且课程成绩在 60 分以上的学生的学号和姓名
SELECT
a.sid,
b.sname,
a.score,
a.cid,
c.cname
FROM
(SELECT * from sc WHERE cid='01' and score > 60) a
JOIN Student b on a.sid = b.sid
JOIN Course c on a.cid = c.cid
34
求每门课程的学生人数
SELECT
a.cid,
b.cname,
a.count
FROM
( SELECT cid, COUNT( DISTINCT sid ) count FROM sc GROUP BY cid ) a
JOIN Course b ON a.cid = b.cid
35
成绩没有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT
d.sid,
d.sname,
b.cid,
b.cname,
MAX(score) maxScore,
c.tid,
c.tname
FROM
(SELECT * from SC) a
JOIN Course b on a.cid = b.cid
JOIN Teacher c on b.tid = c.tid and c.tname='张三'
JOIN Student d on a.sid = d.sid
36
成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
先改点数据,最高分变两个
rank() 函数排序
SELECT
d.sid,
d.sname,
b.cid,
b.cname,
c.tid,
c.tname,
RANK() over ( ORDER BY a.score DESC ) ranked
FROM
( SELECT * FROM sc ) a
JOIN Course b ON a.cid = b.cid
JOIN Teacher c ON b.tid = c.tid
AND c.tname = '张三'
JOIN Student d ON a.sid = d.sid
然后筛选 ranked=1
SELECT
*
FROM
(
SELECT
d.sid,
d.sname,
b.cid,
b.cname,
c.tid,
c.tname,
RANK() over ( ORDER BY a.score DESC ) ranked
FROM
( SELECT * FROM sc ) a
JOIN Course b ON a.cid = b.cid
JOIN Teacher c ON b.tid = c.tid
AND c.tname = '张三'
JOIN Student d ON a.sid = d.sid
) tmp
WHERE
ranked = 1
37
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT
DISTINCT a.sid,
a.cid,
a.score
FROM
sc a
JOIN sc b ON a.score = b.score
AND a.cid != b.cid
38
查询每门功成绩最好的前两名
前两名是排名的前2个,即第一个排名1 和第二个排名2,如果有两个并列第一,一个第二,那么前两名应该是3个人,用dense_rank,排名不跳过;如果说是最好的前两个人,就用rank,排名跳过
SELECT
*
FROM
( SELECT *, DENSE_RANK() over ( PARTITION BY cid ORDER BY score DESC ) ranked FROM sc ) a
WHERE
ranked <=2
39
统计每门课程的学生选修人数(超过 5 人的课程才统计)
SELECT
a.*,
b.cname
FROM
( SELECT cid, count(*) count FROM sc GROUP BY cid ) a
JOIN Course b ON a.cid = b.cid
40
检索至少选修两门课程的学生学号
SELECT
b.sname,
a.*
FROM
(SELECT sid, count(*) count FROM sc GROUP BY sid HAVING count >= 2) a
JOIN Student b on a.sid = b.sid
41
查询选修了全部课程的学生信息
SELECT
a.*,
b.sname,
b.sage,
b.ssex
FROM
(SELECT sid, COUNT(cid) count FROM sc GROUP BY sid HAVING count = (SELECT count(*) from Course)) a
JOIN Student b on a.sid = b.sid
42
查询各学生的年龄,只按年份来算
SELECT
sname,
YEAR(NOW()) - YEAR(sage) age
FROM
Student
43
按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
SELECT
sname,
sage,
(DATE_FORMAT( now(), '%m-%d' ) - DATE_FORMAT( sage, '%m-%d' )) sub,
CASE WHEN (DATE_FORMAT( now(), '%m-%d' ) - DATE_FORMAT( sage, '%m-%d' )) < 0
THEN YEAR (NOW()) - YEAR ( sage ) - 1
ELSE YEAR (NOW()) - YEAR ( sage )
END AS age
FROM
student