文章目录

• • Mysql50题
  • • 练习题1
    • 练习题2
    • 练习题3
    • 练习题4
    • 练习题5
    • 练习题6
    • 练习题7
    • 练习题10
    • 练习题11
    • 练习题12
    • 练习题13
    • 练习题14
    • 练习题15
    • 练习题16
    • 练习题17
    • 练习题18
    • 练习题19
    • • • 19.按各科平均成绩进行排序，并显示排名
    • 练习题20
    • 练习题21
    • 练习题22
    • 练习题24
    • 练习题25
    • 练习题26.
    • 练习题27
    • 练习题28
    • 练习题29
    • 练习题30
    • 练习题31
    • 练习题32
    • 练习题33
    • 练习题34
    • 练习题35
    • 练习题36
    • 练习题37
    • 练习题38
    • 练习题39
    • 练习题40
    • 练习题41
    • 练习题42
    • 练习题43
    • 练习题44
    • 练习题45
    • 练习题46
    • 练习题47
    • 练习题48
    • 练习题49
    • 练习题50

Mysql50题

#建表
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10)
); 
#课程表
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10)
);
# 老师表
create table Teacher(TId varchar(10),Tname varchar(10)
);
#成绩表
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1)
);
-- 插入对应的数据
-- 学生表 Student
insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); 
insert into Student values('02' , '钱电' , '1990-12-21' , '男'); 
insert into Student values('03' , '孙风' , '1990-12-20' , '男'); 
insert into Student values('04' , '李云' , '1990-12-06' , '男'); 
insert into Student values('05' , '周梅' , '1991-12-01' , '女'); 
insert into Student values('06' , '吴兰' , '1992-01-01' , '女'); 
insert into Student values('07' , '郑竹' , '1989-01-01' , '女'); 
insert into Student values('09' , '张三' , '2017-12-20' , '女'); 
insert into Student values('10' , '李四' , '2017-12-25' , '女'); 
insert into Student values('11' , '李四' , '2012-06-06' , '女'); 
insert into Student values('12' , '赵六' , '2013-06-13' , '女'); 
insert into Student values('13' , '孙七' , '2014-06-01' , '女'); -- 科⽬表 Course 
insert into Course values('01' , '语文' , '02'); 
insert into Course values('02' , '数学' , '01'); 
insert into Course values('03' , '英语' , '03');-- 教师表 Teacher 
insert into Teacher values('01' , '张三'),('02' , '李四'),('03' , '王五'); -- 成绩表 SC 
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);

• 建表的层次图
  

练习题1

• 1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

-- 1.查询01课程的成绩的学生信息
select * from SC where CId = '1';

-- 2.查询出该学生课程1和课程2的信息,且她选择的课程1的分数大于课程2的分数的学生IDselect t1.SId FROM( select * from SC where CId = '01') t1 LEFT JOIN(select SId,Cid,score from SC where CId = '02') t2 on t1.SId = t2.SIdwhere t1.score > t2.score

-- 3.查询对应的学生信息和课程分数
select tt1.SId,tt2.Sname,tt3.CId,tt3.scorefrom(select t1.SId FROM( select * from SC where CId = '01') t1 LEFT JOIN(select SId,Cid,score from SC where CId = '02') t2 on t1.SId = t2.SIdwhere t1.score > t2.score)tt1join student tt2 on tt1.Sid = tt2.SIdjoin SC tt1 on tt1.SId = tt3.SId

练习题2

• 2.查询同时存在01课程和02课程的信息

-- 同时具有o1和02代表我们需要进行表连接
# 最终代码
SELECT t1.SId 
FROM(SELECT SId FROM SC WHERE CId='01'
)AS t1 JOIN (SELECT SId FROM SC WHERE CId='01'
)AS t2
ON t1.SId = t2.SId;

• 查询的是01课程的全部信息

select * from SC where CId = '01';

select * from SC where CId = '02';

• 查询两者都有的，利用内连接
  

练习题3

• 3…查询存在" 01 “课程但可能不存在” 02 "课程信息的情况(不存在时显示为 null )

select  t1.SId,t1.CId,t1.score,t2.CId as t2CId,t2.score as t2Score
from 
(SELECT * from SC where CId = '01' ) as t1
left join
(SELECT * from SC where CId = '02') as t2
on t1.SId = t2.SId;

练习题4

• 查询可能不存在01课程但是02课程存在的情况

-- 利用右连接可以筛选出02有的01没有
SELECT t1.SId,t1.CId,t1.score,t2.CId as t2Cid,t2.score as t2Score
from
(select * from SC where CId = '01') as t1
RIGHT JOIN (SELECT SId,CId,score FROM SC WHERE CId = '02') as t2
ON t1.SId = t2.SId;

练习题5

• 查询平均成绩大于等于60分的同学编号和学生姓名和平均成绩

# 先查询平均成绩大于60分的同学有哪些
SELECT SId,ROUND(AVG(score),2) AS avg_scorefromSC group by SIdHAVING avg_score >=60;

# 进行表连接
select t1.SId,
t2.Sname,
t1.avg_score
FROM(
SELECT SId,ROUND(AVG(score),2) AS avg_scorefromSC group by SIdHAVING avg_score >=60
) as t1 JOIN Student as t2 ON t1.SId = t2.SId;

练习题6

6. 查询在SC表里面学生的姓名和ID

select t1.SId,t2.Sname 
from(
select DISTINCT SId from sc
) t1 JOIN student t2 ON
t1.SId = t2.SId;

练习题7

• 7…查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，没成绩的显示为 null

-- 首先就是先查询对应的学生编号和选课总数和总成绩
select SId,count(CId) as cnt,SUM(score) as sum_socre from SC group by SId

-- 题目说查询学生姓名，没成绩的显示为Null,那么就是右连接，这样可以知道没成绩的是哪些
select t2.SId,t2.Sname,t1.cnt as '选课总数',t1.sum_score '总成绩数'from(select SId,COUNT(CId) as cnt,SUM(score) as sum_scoreFROM scGROUP BY SId) t1right join student t2on t1.SId = t2.SId

• 8 查询李姓老师的数量

• 9.查询学过张三老师上课学生的信息

-- 查询到张三老师的课程号
select CId from course where TId = (select TId from teacher WHERE Tname='张三');-- 查询上张三的学生的学生编号
select SId from SC where CId = (select CId from course where TId = (select TId from teacher WHERE Tname='张三'))-- 查询对应的学生信息
SELECT t2.SId
,t2.Sname 
FROM(SELECT SId FROM SC WHERE CId = (SELECT CId FROM Course WHERE TId =(SELECT TId FROM Teacher WHERE Tname = '张三')
))t1 JOIN Student t2
ON t1.SId = t2.SId;

练习题10

• 查询没有学全所有课程的同学

分析：1.查询学生所学课程的信息

​ 2.将学生课程表与学生表关联查出对应的学生信息

​ 3.进行筛选出小于全课程数量的学生

# 查询出对应的学生编号和学生信息
SELECT t1.SId,t1.CId,t2.Snamefrom(SELECT SId,CIdfrom sc				) as t1 join student t2 on t1.SId = t2.SId;

# 2.查询出count(cid) < 3的；这里利用了group by 后面接having过滤的小知识
SELECT tt1.SId,tt1.Sname,count(tt1.CId) as cntFROM(select t1.SId,t1.CId,t2.SnameFROM(SELECT SId,CIdFROM sc) t1 JOIN student t2ON t1.SId = t2.SId)tt1 GROUP BY tt1.SId,tt1.SnameHAVING cnt < (SELECT count(*) from course);

练习题11

• 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

分析：1.查询学号为01学生的所有课程

		2. 查询至少有一门课在01课程里面的学生2. 查询除了01学号的有哪些

# 1.查询学号为01学生的所有课程
SELECT CId from sc WHERE SId = '01';

-- 2.查询至少有一门同学的课程在01课程表的人
SELECT distinct SIdfrom scWHERE CId IN(SELECT CId from sc WHERE SId = '01');

# 除了01
SELECT DISTINCT tt1.SId,tt2.SnameFROM(SELECT SIdFROM sc WHERE CId IN(SELECT CId from sc WHERE SId = '01')) tt1JOIN student tt2ON tt1.SId = tt2.SIdWHERE tt1.SId !='01';

练习题12

• 12.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息

分析：

1、首先查询出学号为01的课程信息
2、然后查询出学号不为01的课程信息
3、再将上面两个表分别作为t1、t2进行关联，得出01同学学习的所有课程信息和其他同学的所 有课程信息
4、然后将SC表作为t1表与Student表t2表进行关联、表tt1分组对学号为01的学生进行筛选

SELECT t2.SId,t1.CId AS t1CId,t2.CId AS t2CId 
FROM(SELECT SId,CId FROM SC WHERE SId='01'
) t1 JOIN(SELECT SId,CId FROM SC WHERE SId!='01') t2ON t1.CId = t2.CId;

2.将上述查询的信息与student表进行连接，通过SId进行分组，CId个数进行比对

SELECT tt1.SId,COUNT(tt1.t1CId) AS t1Cnt,COUNT(tt1.t2CId) as t2Cnt 
FROM(SELECT t2.SId,t1.CId AS t1CId,t2.CId AS t2CId FROM(SELECT SId,CId FROM SC WHERE SId='01'
) t1 JOIN(SELECT SId,CId FROM SC WHERE SId!='01') t2ON t1.CId = t2.CId) tt1 GROUP BY tt1.SIdHAVING t1Cnt AND t2Cnt = (SELECT COUNT(*) FROM SC WHERE SId = '01');

练习题13

• 13.查询没学过"张三"老师讲授的任一门课程的学生姓名
• 分析

1.查询张三老师的课程ID

2.查询张三老师课程上的学生的SId

3.查询student表里面的学生SId不在张三课上的人的信息

-- 查询张三老师的CId
select CId FROM courseWHERE TId = (SELECT TId from teacher where Tname = '张三')

-- 查询上张三课的学生
SELECT SIdfrom sc where CId in(select CId FROM courseWHERE TId = (SELECT TId from teacher where Tname = '张三'）)

-- 查询对应学生的信息
SELECT SId,Sname 
FROM Student
WHERE SId 
NOT IN (SELECT SId FROM SC WHERE CId in(SELECT CId FROM Course WHERE TId = (SELECT TId FROM Teacher WHERE Tname = '张三'))
);

练习题14

• 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

分析

1.查询不及格同学的SId

2.查询两门以上不及格同学的平均分和SId

3.将上诉表和student连接，查询对应的信息

-- 查询两门不及格的信息
select SId,AVG(score) avg_scoreFROM scWHERE score <'60'GROUP BY SIdHAVING COUNT(score) >=2;

-- 查询对应学生的信息和成绩
SELECT  tt1.SId,tt1.avg_score,t2.Snamefrom(select SId,AVG(score) avg_scoreFROM scWHERE score <'60'GROUP BY SIdHAVING COUNT(score) >=2)tt1 JOIN student t2on tt1.SId = t2.SId;

练习题15

– 检索01课程 学生分数低于60分，按分数降序排列的同学信息

分析：

1.查询出对应的学生的编号、姓名、和分数

2.查询出对应的的学生课程为01且分数为60的学生信息

SELECT t1.SId,t2.Sname,t1.score FROM sc t1JOIN student t2on t1.SId = t2.SId

SELECT t1.SId,t2.Sname,t1.score FROM sc t1JOIN student t2on t1.SId = t2.SIdWHERE t1.CId = '01' AND t1.score <'60'ORDER BY t1.score DESC;

练习题16

• 16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

分析：

1.查出对应的SId,score的平均分在sc表并以SId为分组

2.指定对应的CId ,并查出对应的SId和Score从sc

3.将表1和表2进行左连接

SELECT tt1.SId,tt1.avg_score AS '平均分',tt2.score AS '语文',tt3.score as '数学',tt4.score AS '英语'
FROM (SELECT t1.SId,AVG(t1.score) as avg_score from sc t1 GROUP BY t1.SId
)tt1
LEFT JOIN(SELECT SId,score from sc WHERE CId = '01') tt2 ON tt1.SId = tt2.SId
LEFT JOIN(SELECT SId,score FROM sc WHERE CId = '02') tt3 on tt1.SId = tt3.SId
LEFT JOIN(SELECT SId,score FROM sc where CId = '03') tt4 ON tt1.SId = tt4.SId
ORDER BY tt1.avg_score DESC;

练习题17

– 17.查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

分析：

1.将我们的CID和我们的SC表进行连接，查询出对应的CId和Cname

2.然后分别计算出最高分、最低分、平均分、及格率、中等率、优良率、优秀率，并将查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT  t1.CId,t2.Cname FROM sc t1 JOINcourse t2 ON t1.CId = t2.CId;

SELECT t1.CId,t2.Cname,MAX(score) AS '最高分',MIN(score) AS '最低分',AVG(score) AS '平均分',CONCAT(ROUND(SUM(IF(score >= 60,1,0))*100/COUNT(score),2),"%") AS '及格率' ,CONCAT(ROUND(SUM(IF(score >= 70,1,0))*100/COUNT(score),2),"%") AS '中等率' ,CONCAT(ROUND(SUM(IF(score >= 80,1,0))*100/COUNT(score),2),"%") AS '优良率' ,CONCAT(ROUND(SUM(IF(score >= 90,1,0))*100/COUNT(score),2),"%") AS '优秀率' ,COUNT(score) as '人数'FROM sc t1 JOINcourse t2 ON t1.CId = t2.CIdGROUP BY t1.CId,t2.Cname;ORDER BY '人数' DESC,CId ASC

练习题18

• 按各科平均成绩进行排序，并显示排名

引入变量的知识： @i := 0;

-- 1.先查询出各科平均成绩
select CId,AVG(score) avg_scorefrom scgroup by CIdorder by avg_score desc;-- 2.引入变量，进行排名
set @i := 0;
select t1.CId,t1.avg_score,@i := @i+1 as '排名'FROM(select CId,AVG(score) avg_scorefrom scgroup by CIdorder by avg_score desc) t1;

练习题19

19.按各科平均成绩进行排序，并显示排名

分析：

1. 首先查出各科平均成绩，用group by CId就行
2. 最后定义一个变量@，将其上表作为t1表进行排名计算，查询出结果

• 首先查询出各科的平均分并进行排序

SELECT CId,AVG(score) avg_score 
FROM SC 
GROUP BY CId 
ORDER BY avg_score DESC;

• 然后定义一个变量@i 然后将上表作为t1表进行排名计算

SET @i :=0;-- 定义一个变量
SELECT t1.CId,t1.avg_score,@i := @i + 1 AS '排名' 
FROM(SELECT CId,AVG(score) avg_score 
FROM SC 
GROUP BY CId 
ORDER BY avg_score DESC
) t1;

练习题20

20.查询学生的总成绩，并进行排名，总分重复保留名次空缺（122345）

分析

1.先查询总成绩，以SId进行分组，并进行排序

2.最后将上表作为t1表然后定义变量@i@j进行排名，@p@q用来控制总分重复时保留名次空缺

SELECT SId,SUM(score) 
AS sum_score 
FROM SC 
GROUP BY SId 
ORDER BY sum_score DESC;

-- 定义变量用来排名的
set @i :=0;
set @j :=0;
set @p :=0;
set @q :=0;
SELECT t1.SId,t1.sum_score AS '总分',@j :=@j+1,@p := t1.sum_score,IF(@p=@q,@j,@i :=@j) AS '排名',@q := @pFROM(SELECT SId,SUM(score) as sum_scoreFROM sc GROUP BY SIdORDER BY sum_score DESC)t1;

练习题21

– 21.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

set @i :=0;
set @p :=0;
set @q :=0;
SELECT t1.SId,t1.sum_score as '总分',@p := t1.sum_score,IF(@p=@q,@i,@i :=@i+1) -- 如果分数相等就可以等于i否则就进行加一,@q := @pFROM(SELECT SId,SUM(score) as sum_scoreFROM sc GROUP BY SIdORDER BY sum_score DESC) t1;

练习题22

– 22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85），[85-70），[70-60），[60-0）及所占百分比

分析

1. 查询我们对应的人数的话是sum求和：1、0来记录人数，百分比需要用到concat，sum/concat
2. 再将信息表与课程表关联

selectt1.CId as '课程编号',t2.Cname as '课程名字',sum(case when score between 85 and 100 then 1 else 0 end) as '[100-85]',concat(round(sum(case when score between 85 and 100 then 1 else 0 end) / count(*) * 100, 2), '%') as '百分比',sum(case when score between 70 and 85 then 1 else 0 end) as '[85-70]',concat(round(sum(case when score between 70 and 85 then 1 else 0 end) / count(*) * 100, 2), '%') as '百分比',sum(case when score between 60 and 70 then 1 else 0 end) as '[70-60]',concat(round(sum(case when score between 60 and 70 then 1 else 0 end) / count(*) * 100, 2), '%') as '百分比',sum(case when score between 0 and 60 then 1 else 0 end) as '[0-60]',concat(round(sum(case when score between 0 and 60 then 1 else 0 end) / count(*) * 100, 2), '%') as '百分比'
from sc t1
inner join course t2
on t1.CId = t2.CId
group by t1.CId,t2.Cname;

– 23.查询各科成绩前三名的记录

分析

1.查看各科成绩信息，进行降序排名，找出前三

2.进行学生表，课程表相连接

SELECT tt3.CId,tt3.Cname,tt1.SId,tt2.Sname,tt1.score
FROM(
SELECT * FROM
sc t1
where(SELECT count(*) from sc t2where t1.CId = t2.CIdand t1.score < t2.score
) < 3
) tt1
INNER JOIN student tt2 on tt1.SId = tt2.SId
INNER JOIN course tt3 on tt1.CId = tt3.CId
ORDER BY tt1.CId,tt1.score DESC

练习题24

– 24.查询每门课程选修的学生人数

SELECT CId,COUNT(SId) as '人数'
FROM sc
GROUP BY CId;

练习题25

• 查询出对应学生所学课程的数量

SELECT t1.SId,t2.SnameFROM(SELECT SId,count(CId) as numFROM scGROUP BY SId HAVING num = 2) t1 JOIN student t2ON t1.SId = t2.SId

练习题26.

• 查询男生，女生各自的人数

SELECT Ssex
,COUNT(1) 
FROM Student 
GROUP BY Ssex;

练习题27

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

SELECT * FROM Student WHERE Sname LIKE '%风%';

练习题28

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

SELECT Sname,Ssex,COUNT(1) 
AS cnt 
FROM Student 
GROUP BY Sname,Ssex 
HAVING cnt > 1;

练习题29

• 查询1999 年出生的学生名单

SELECT * FROM Student 
WHERE Sage 
LIKE '1990%';

练习题30

• 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

分析：

1. 查询出课程的平均成绩
2. 利用order by自带升序，让后多个字段代表相同时按第二个排列

-- 查询出平均成绩
SELECT  SC.CId,Course.Cname,AVG(SC.score) AS avg_score
FROM SC 
JOIN Course 
ON SC.CId = Course.CId 
GROUP BY SC.CId,Course.Cname;

-- 加入排序字段
SELECT  SC.CId,Course.Cname,AVG(SC.score) AS avg_score
FROM SC 
JOIN Course 
ON SC.CId = Course.CId 
GROUP BY SC.CId,Course.Cname
ORDER BY avg_score DESC,SC.CId;

练习题31

• 31.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

分析：

1.查询出成绩大于等于平均成绩的课程信息

2.然后将上表与student表进行关联

-- 首先查询出成绩大于等于85的平均成绩的课程信息
SELECT SId,ROUND(AVG(score),2) AS avg_score 
FROM SC 
GROUP BY SId
HAVING avg_score>=85;

SELECT t1.SId,t2.Sname,t1.avg_score 
FROM(SELECT SId,ROUND(AVG(score),2) AS avg_score FROM SC GROUP BY SIdHAVING avg_score>=85
) t1 JOIN Student t2
ON t1.SId = t2.SId;

练习题32

• 32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

分析：

1、首先查询出课程名为数学的CId
2、然后再将上表作为字表查询出课程名为数学，且分数低于60的课程信息
3、最后将上表作为表t1与Student表t2做关联取出学生信息

SELECT t2.Sname,t1.Score 
FROM(SELECT SId,Score 
FROM SC 
WHERE CId =(SELECT  CIdFROM Course WHERE CName = '数学'
)AND score<60
) t1 JOIN Student t2
ON t1.SId = t2.SId;

练习题33

• .查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

分析：

1.查出对应学生学号的各科成绩分数情况

2.与student进行右连接就可以，取出学生姓名

-- 1.查询学生各科成绩
SELECT  SId,SUM(CASE CId WHEN '01' THEN score ELSE 0 END) AS '语文成绩',SUM(CASE CId WHEN '02' THEN score ELSE 0 END) AS '数学成绩',SUM(CASE CId WHEN '03' THEN score ELSE 0 END) AS '英语成绩'
FROM SC
GROUP BY SId;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3xORP5YL-1669720265138)(C:\Users\裴承林\AppData\Roaming\Typora\typora-user-images\image-20221111113256778.png)]

-- 进行连接
SELECT t1.Sname,t2.语文成绩,t2.数学成绩,t2.英语成绩 
FROM
Student t1 LEFT JOIN (SELECT  SId,SUM(CASE CId WHEN '01' THEN score ELSE 0 END) AS '语文成绩',SUM(CASE CId WHEN '02' THEN score ELSE 0 END) AS '数学成绩',SUM(CASE CId WHEN '03' THEN score ELSE 0 END) AS '英语成绩'FROM SCGROUP BY SId
) t2
ON t1.SId = t2.SId;

练习题34

• 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

分析：

1、首先查询出成绩在70分以上的课程信息
2、然后再将上表作为t1表与Student表t2关联，再与Course表t3做关联取出任何一门课程成绩在 70 分以上的姓名、课程名称和分数信息

-- 查询出成绩在70分的课程成绩
SELECT  SId,CId,score
FROM SC
WHERE score>70;

-- 然后再将上表作为t1表与Student表t2关联，再与Course表t3做关联取出任何一门课程成绩在 70 分以上的姓名、课程名称和分数信息
SELECT t2.Sname,t3.Cname,t1.Score 
FROM(SELECT  SId,CId,scoreFROM SCWHERE score>70
) t1 JOIN Student t2
ON t1.SId = t2. SId
JOIN Course t3
ON t1.CId = t3.CId;

练习题35

• 查询不及格的课程学生姓名及课程名

SELECT t2.Sname,t3.Cname,t1.CId,t1.scoreFROM(select SId,CId,scoreFROM sc where score <60)t1 JOIN student t2ON t1.SId = t2.SIdJOIN course t3ON t1.CId = t3.CId;

练习题36

– 36.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

分析

1.查询出课程为01且分数在80的人的SId

2.进行表连接查询

SELECT SId
FROM sc
WHERE CId = '01' AND score >=80;SELECT t1.SId,t2.SnameFROM(SELECT SIdFROM scWHERE CId = '01' AND score >=80)t1 JOIN student t2ON t1.SId = t2.SId;

练习题37

• 求每门课程的学生人数

SELECT t1.CId,t2.Cname,t1.人数FROM(SELECT CId,COUNT(SId) AS '人数'from sc GROUP BY CId)t1 JOIN course t2ON t1.CId = t2.CId;

练习题38

– 38.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

分析：

1、查询张三老师的CId;

2.查询此CId的学生信息

SELECT CId 
FROM course
WHERE TId = (SELECT TId from teacher WHERE Tname = '张三');

SELECT SId,score FROM sc 
WHERE CId =(SELECT CId 
FROM course
WHERE TId = (SELECT TId from teacher WHERE Tname = '张三'))
ORDER BY score DESC
LIMIT 1

-- 查询出对应学生的信息
SELECT t1.SId AS '学号',t2.Sname as '姓名',t1.CId,t1.score as '分数'FROM(SELECT SId,score,CId FROM sc WHERE CId =(SELECT CId FROM courseWHERE TId = (SELECT TId from teacher WHERE Tname = '张三'))ORDER BY score DESCLIMIT 1)t1 JOIN student t2ON t1.SId = t2.SId;

练习题39

– 39.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

 -- 1.查询出对应的SId,CId,score等的信息SELECT SId,CId,score 
FROM SC
WHERE CId IN (SELECT CId FROM Course WHERE TId = (SELECT TId FROM Teacher WHERE Tname = '张三')
) ORDER BY score DESC;

-- 与其他表进行相连接
SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECT tt1.SId,tt1.CId,tt1.score,tt2.SnameFROM(SELECT  t1.SId,t1.CId,t1.score,@p := t1.score,IF(@p=@q,@i,@i := @i+1) AS `dense_rank`,@q :=@pFROM (SELECT SId,CId,score FROM SCWHERE CId IN (SELECT CId FROM Course WHERE TId = (SELECT TId FROM Teacher WHERE Tname = '张三')
) ORDER BY score DESC) t1)tt1 JOIN student tt2ON tt1.`dense_rank` = 1AND tt1.SId = tt2.SId;

练习题40

• – 40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

分析：

1.查询sc中分数一样但是cid不一样的学生SId,以及CId,score

2.将上述表查询出来的进行联结

SELECT t1.SId,t1.CId,t2.scoreFROM sc t1JOIN sc t2on t1.score = t2.scoreAND t1.CId != t2.CId;

SELECT t3.Sname,t1.CId,t2.scoreFROM sc t1JOIN sc t2on t1.score = t2.scoreAND t1.CId != t2.CIdJOIN student t3ON t1.SId = t3.SId;

练习题41

• 查询出每门课程成绩最好的前两名

分析：首先这里提到前两门也就代表了我们需要进行排序，可以利用变量也可以利用窗口函数，窗口函数要求mysql是在8.0以上的版本

1.查询出每门课程成绩的排名

2.进行表连接，取排名对应的前两名

• 方法一

# 将成绩按降序排名
SELECT  SId,CId,scoreFROM sc ORDER BY CId,score DESC

-- 定义变量，求出排名情况
set @i :=0;
set @p :=0;
set @q :=0;
SELECT 	t1.SId,t1.CId,t1.score,@p := t1.CId,IF(@p=@q,@i := @i+1,@i :=1) as rn,@q :=@pFROM(SELECT  SId,CId,scoreFROM sc ORDER BY CId,score DESC) t1

-- 进行表相连接			
SELECT tt1.SId,tt2.Sname,tt1.CId,tt1.score,tt1.rnFROM(SELECT 	t1.SId,t1.CId,t1.score,@p := t1.CId,IF(@p=@q,@i := @i+1,@i :=1) as rn,@q :=@pFROM(SELECT  SId,CId,scoreFROM sc ORDER BY CId,score DESC) t1)tt1 JOIN student tt2ON tt1.SId = tt2.SId AND tt1.rn <=2ORDER BY tt1.CId,tt1.score DESC;

• 解法二（窗口函数）

  -- 按课程分类进行成绩降序排名
  SELECT  *,ROW_NUMBER() OVER (PARTITION BY CIdORDER BY score DESC
  ) AS rn FROM sc 
  

  

SELECT  t1.SId,t2.Sname,t1.CId,t1.score,t1.rnFROM(SELECT *,ROW_NUMBER() OVER (PARTITION BY CIdORDER BY score DESC) AS rn FROM sc ) t1 JOIN student t2 ON t1.rn <=2 AND t1.SId = t2.SIdORDER BY CId,score DESC;

练习题42

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

SELECT CId,COUNT(SId) as '选课人数'FROM scGROUP BY CIdHAVING 选课人数 > 5;

练习题43

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

分析：

1.直接统计课程人数，按学生学号进行分组

SELECT SId,COUNT(CId) AS cnFROM scGROUP BY SIdHAVING cn>=2;

练习题44

• 查询选修了全部课程的学生信息

分析：

1. 查询课程表中共有多少课程
2. 查询出按学生分组计算课程数等于表1查询的课程数
3. 进行表连接，查询出学生姓名等信息

SELECT SId,count(CId) as cnFROM scGROUP BY SIdHAVING cn = (SELECT COUNT(CId) FROM course)

SELECT  t1.SId,t2.SnameFROM(SELECT SId,count(CId) as cnFROM scGROUP BY SIdHAVING cn = (SELECT COUNT(CId) FROM course))t1 JOIN student t2 ONt1.SId = t2.SId;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Xz58B1B6-1669720265156)(C:\Users\裴承林

练习题45

• 查询各学生的年龄，只按年份来算

select year(now())-date_formate(Sage,'%Y') from student

练习题46

• 46.按照出生日期来算，当前月日 < 出生年月的月日，则年龄减一

分析：

1.先查询出对应的生日日期以及当前日期

2.在进行当前日期与生日日期的比较

SELECT SId ,Sname,(YEAR(now()) - date_format(Sage,'%Y') ) AS age,date_format(Sage,'%m-%d') AS month_day,date_format(now(),'%m-%d') AS now_month_day
FROM Student;

SELECT SId ,SName,CASE WHEN now_month_day

SELECT  SId,Sname,Sage,WEEK(Sage)FROM studentWHERE WEEK(Sage) = WEEK(NOW());

SELECT SId,Sname,Sage,WEEK(Sage)FROM studentWHERE WEEK(Sage) = WEEK(DATE_ADD(NOW(),INTERVAL 1 week));

SELECT SId,Sname,Sage,MONTH(Sage)FROM studentWHERE MONTH(Sage) = MONTH(NOW());

SELECT SId,Sname,Sage,MONTH(Sage)FROM studentWHERE WEEK(Sage) = WEEK(DATE_ADD(NOW(),INTERVAL 1 WEEK));