经典Mysql入门必刷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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

建表的层次图
$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-29xIy9gq-1669720265101)(C:\Users\裴承林\AppData\Roaming\Typora\typora-user-images\image-20221031191125922.png)]$

练习题1

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

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

在这里插入图片描述

-- 2.查询出该学生课程1和课程2的信息,且她选择的课程1的分数大于课程2的分数的学生ID
		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.SId
			where t1.score > t2.score
1
2
3
4

在这里插入图片描述

-- 3.查询对应的学生信息和课程分数
select tt1.SId,
		tt2.Sname,
		tt3.CId,
		tt3.score
		from(
        	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.SId
			where t1.score > t2.score
        )tt1
        join student tt2 on tt1.Sid = tt2.SId
        join SC tt1 on tt1.SId = tt3.SId
		
1
2
3
4
5
6
7
8
9
10
11
12
13

练习题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;
1
2
3
4
5
6
7
8
9
10
11
12
13

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

select * from SC where CId = '01';
1

在这里插入图片描述

select * from SC where CId = '02';
1

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

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

练习题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;
1
2
3
4
5
6
7
8
9
10

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

练习题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;
1
2
3
4
5
6
7
8
9
10

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

练习题5

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

# 先查询平均成绩大于60分的同学有哪些
SELECT SId,
			ROUND(AVG(score),2) AS avg_score
			from
			SC group by SId
			HAVING avg_score >=60;
1
2
3
4
5
6

在这里插入图片描述

# 进行表连接
select t1.SId,
t2.Sname,
t1.avg_score
FROM(
SELECT SId,
			ROUND(AVG(score),2) AS avg_score
			from
			SC group by SId
			HAVING avg_score >=60
) as t1 JOIN Student as t2 ON t1.SId = t2.SId;
1
2
3
4
5
6
7
8
9
10
11

在这里插入图片描述

练习题6

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

select t1.SId,t2.Sname 
from(
select DISTINCT SId from sc
) t1 JOIN student t2 ON
t1.SId = t2.SId;
1
2
3
4
5

在这里插入图片描述

练习题7

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

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12

在这里插入图片描述

8 查询李姓老师的数量

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

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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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

练习题10

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

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

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

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

# 查询出对应的学生编号和学生信息
SELECT t1.SId,
				t1.CId,
				t2.Sname
				from(
				SELECT SId,CId
				from sc				
				) as t1 join student t2 
				on t1.SId = t2.SId;
1
2
3
4
5
6
7
8
9

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

# 2.查询出count(cid) < 3的；这里利用了group by 后面接having过滤的小知识
SELECT tt1.SId,
				tt1.Sname,
				count(tt1.CId) as cnt
				FROM(
				select t1.SId,
								t1.CId,
								t2.Sname
								FROM(
								SELECT SId,CId
								FROM sc
								) t1 JOIN student t2
								ON t1.SId = t2.SId
				)tt1 
				GROUP BY tt1.SId,tt1.Sname
				HAVING cnt < (SELECT count(*) from course);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

练习题11

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

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

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

在这里插入图片描述

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

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

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

在这里插入图片描述

# 除了01
SELECT DISTINCT tt1.SId,tt2.Sname
				FROM(SELECT SId
							FROM sc 
							WHERE CId IN(SELECT CId from sc WHERE SId = '01')) tt1
							JOIN student tt2
							ON tt1.SId = tt2.SId
							WHERE tt1.SId !='01';

1
2
3
4
5
6
7
8
9

练习题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') t2
   ON t1.CId = t2.CId;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

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') t2
   ON t1.CId = t2.CId) tt1 GROUP BY tt1.SId
   HAVING t1Cnt AND t2Cnt = (SELECT COUNT(*) 
                             FROM SC 
                             WHERE SId = '01');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

练习题13

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

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

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

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

-- 查询张三老师的CId
select CId FROM course
						WHERE TId = (
						SELECT TId from teacher where Tname = '张三'
						)
1
2
3
4
5

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

-- 查询上张三课的学生
SELECT SId
				from sc where CId in(
				select CId FROM course
						WHERE TId = (
						SELECT TId from teacher where Tname = '张三'）
						)
1
2
3
4
5
6
7

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

-- 查询对应学生的信息
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 = '张三')
    )
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

练习题14

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

分析

1.查询不及格同学的SId

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

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

-- 查询两门不及格的信息
select SId,
				AVG(score) avg_score
				FROM sc
				WHERE score <'60'
				GROUP BY SId
				HAVING COUNT(score) >=2;
1
2
3
4
5
6
7

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

-- 查询对应学生的信息和成绩
SELECT  tt1.SId,
				tt1.avg_score,
				t2.Sname
				from(
				select SId,
				AVG(score) avg_score
				FROM sc
				WHERE score <'60'
				GROUP BY SId
				HAVING COUNT(score) >=2)
				tt1 JOIN student t2
				on tt1.SId = t2.SId;
1
2
3
4
5
6
7
8
9
10
11
12
13

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

练习题15

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

分析：

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

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

SELECT t1.SId,t2.Sname,t1.score 
			FROM sc t1
			JOIN student t2
			on t1.SId = t2.SId
1
2
3
4

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

SELECT t1.SId,t2.Sname,t1.score 
			FROM sc t1
			JOIN student t2
			on t1.SId = t2.SId
			WHERE t1.CId = '01' AND t1.score <'60'
			ORDER BY t1.score DESC;
1
2
3
4
5
6

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

练习题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;
1
2
3
4
5
6
7
8
9
10
11
12

练习题17

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

分析：

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

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

SELECT  t1.CId,t2.Cname 
				FROM sc t1 JOIN
				course t2 ON t1.CId = t2.CId;
1
2
3

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

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 JOIN
				course t2 ON t1.CId = t2.CId
				GROUP BY t1.CId,t2.Cname;
				ORDER BY '人数' DESC,CId ASC
				
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

练习题18

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

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

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rFcSCzVK-1669720265124)(C:\Users\裴承林\AppData\Roaming\Typora\typora-user-images\image-20221107102124015.png)]$
在这里插入图片描述

-- 1.先查询出各科平均成绩
select CId,
		AVG(score) avg_score
		from sc
		group by CId
		order by avg_score desc;

-- 2.引入变量，进行排名
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

练习题19

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

分析：

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

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

SELECT CId
       ,AVG(score) avg_score 
FROM SC 
GROUP BY CId 
ORDER BY avg_score DESC;
1
2
3
4
5

然后定义一个变量@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;
1
2
3
4
5
6
7
8
9
10
11

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

练习题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;
1
2
3
4
5
6

-- 定义变量用来排名的
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 := @p
				FROM(
				SELECT SId,SUM(score) as sum_score
				FROM sc 
				GROUP BY SId
				ORDER BY sum_score DESC
				)t1;
				
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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

练习题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 := @p
			FROM(
				SELECT SId,SUM(score) as sum_score
				FROM sc 
				GROUP BY SId
				ORDER BY sum_score DESC
			) t1;
			
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

练习题22

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

分析

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

select
	t1.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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

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

分析

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

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

SELECT tt3.CId,tt3.Cname,tt1.SId,tt2.Sname,tt1.score
FROM(
SELECT * FROM
sc t1
where(SELECT count(*) from sc t2
			where t1.CId = t2.CId
			and 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
1
2
3
4
5
6
7
8
9
10
11
12

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

练习题24

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

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

1
2
3
4

练习题25

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

SELECT t1.SId,
				t2.Sname
				FROM(
				SELECT SId,
				count(CId) as num
				FROM sc
				GROUP BY SId HAVING num = 2
				) t1 JOIN student t2
				ON t1.SId = t2.SId
1
2
3
4
5
6
7
8
9

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

练习题26.

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

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

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

练习题27

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

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

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

练习题28

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

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

1
2
3
4
5
6
7
8

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

练习题29

查询1999 年出生的学生名单

SELECT * FROM Student 
WHERE Sage 
LIKE '1990%';
1
2
3

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

练习题30

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

分析：

查询出课程的平均成绩
利用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;
1
2
3
4
5
6
7
8

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

-- 加入排序字段
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;

1
2
3
4
5
6
7
8
9
10

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

练习题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;

1
2
3
4
5
6
7

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

SELECT t1.SId
       ,t2.Sname
       ,t1.avg_score 
FROM(
   SELECT SId
          ,ROUND(AVG(score),2) AS avg_score 
   FROM SC 
   GROUP BY SId
   HAVING avg_score>=85
) t1 JOIN Student t2
ON t1.SId = t2.SId;
1
2
3
4
5
6
7
8
9
10
11

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

练习题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  CId
      FROM Course 
      WHERE CName = '数学'
)AND score<60
) t1 JOIN Student t2
ON t1.SId = t2.SId;
1
2
3
4
5
6
7
8
9
10
11
12
13

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

练习题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;
1
2
3
4
5
6
7

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(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 SC
        GROUP BY SId
) t2
ON t1.SId = t2.SId;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

练习题34

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

分析：

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

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

1
2
3
4
5
6
7

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

练习题35

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

SELECT t2.Sname,
				t3.Cname,
				t1.CId,
				t1.score
				FROM(
				select SId,CId,score
				FROM sc where score <60
				)t1 JOIN student t2
				ON t1.SId = t2.SId
				JOIN course t3
				ON t1.CId = t3.CId;
1
2
3
4
5
6
7
8
9
10
11

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

练习题36

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

分析

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

2.进行表连接查询

SELECT SId
FROM sc
WHERE CId = '01' AND score >=80;

SELECT t1.SId,
				t2.Sname
				FROM(
				SELECT SId
				FROM sc
				WHERE CId = '01' AND score >=80
				)t1 JOIN student t2
				ON t1.SId = t2.SId;
1
2
3
4
5
6
7
8
9
10
11
12

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

练习题37

求每门课程的学生人数

SELECT t1.CId,
				t2.Cname,
				t1.人数
				FROM(
				SELECT CId,
			COUNT(SId) AS '人数'
			from sc GROUP BY CId
				)t1 JOIN course t2
				ON t1.CId = t2.CId;
1
2
3
4
5
6
7
8
9

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

练习题38

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

分析：

1、查询张三老师的CId;

2.查询此CId的学生信息

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

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

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
1
2
3
4
5
6

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

练习题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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

-- 与其他表进行相连接
SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECT tt1.SId,
				tt1.CId,
				tt1.score,
				tt2.Sname
				FROM(
				SELECT  t1.SId
        ,t1.CId
        ,t1.score
        ,@p := t1.score
        ,IF(@p=@q,@i,@i := @i+1) AS `dense_rank`
        ,@q :=@p
				FROM (
				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) t1
				)tt1 JOIN student tt2
				ON tt1.`dense_rank` = 1
				AND tt1.SId = tt2.SId;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

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

练习题40

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

分析：

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

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

SELECT 
				t1.SId,
				t1.CId,
				t2.score
				FROM sc t1
				JOIN sc t2
				on t1.score = t2.score
				AND t1.CId != t2.CId;
1
2
3
4
5
6
7
8

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

SELECT t3.Sname,
				t1.CId,
				t2.score
				FROM sc t1
				JOIN sc t2
				on t1.score = t2.score
				AND t1.CId != t2.CId
				JOIN student t3
				ON t1.SId = t3.SId;
1
2
3
4
5
6
7
8
9

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

练习题41

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

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

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

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

方法一

# 将成绩按降序排名
SELECT  SId,
				CId,
				score
				FROM sc 
				ORDER BY CId,score DESC
1
2
3
4
5
6

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

-- 定义变量，求出排名情况
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 :=@p
				FROM(
				SELECT  SId,
				CId,
				score
				FROM sc 
				ORDER BY CId,score DESC
				) t1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

-- 进行表相连接			
SELECT tt1.SId,
				tt2.Sname,
				tt1.CId,
				tt1.score,
				tt1.rn
				FROM(
				SELECT 	t1.SId,
								t1.CId,
								t1.score,
								@p := t1.CId,
								IF(@p=@q,@i := @i+1,@i :=1) as rn,
								@q :=@p
				FROM(
								SELECT  SId,
								CId,
								score
				FROM sc 
				ORDER BY CId,score DESC
				) t1
				)tt1 JOIN student tt2
				ON tt1.SId = tt2.SId AND tt1.rn <=2
				ORDER BY tt1.CId,tt1.score DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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

解法二（窗口函数）

-- 按课程分类进行成绩降序排名
SELECT  *,
			ROW_NUMBER() OVER (
  PARTITION BY CId
  ORDER BY score DESC
) AS rn FROM sc 
1
2
3
4
5
6

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

SELECT  t1.SId,
				t2.Sname,
				t1.CId,
				t1.score,
				t1.rn
				FROM(
				SELECT *,
			       ROW_NUMBER() OVER (
             PARTITION BY CId
             ORDER BY score DESC
             ) AS rn FROM sc 
				) t1 JOIN student t2 ON t1.rn <=2 AND t1.SId = t2.SId
				ORDER BY CId,score DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13

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

练习题42

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

SELECT CId,
				COUNT(SId) as '选课人数'
				FROM sc
				GROUP BY CId
				HAVING 选课人数 > 5;
1
2
3
4
5

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

练习题43

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

分析：

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

SELECT SId,
				COUNT(CId) AS cn
				FROM sc
				GROUP BY SId
				HAVING cn>=2;
1
2
3
4
5

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

练习题44

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

分析：

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

SELECT SId,
				count(CId) as cn
				FROM sc
				GROUP BY SId
				HAVING cn = (SELECT COUNT(CId) FROM course)
1
2
3
4
5

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

SELECT  t1.SId,
				t2.Sname
				FROM(
				SELECT SId,
				count(CId) as cn
				FROM sc
				GROUP BY SId
				HAVING cn = (SELECT COUNT(CId) FROM course)
				)t1 JOIN student t2 ON
				t1.SId = t2.SId;
1
2
3
4
5
6
7
8
9
10

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

练习题45

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

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

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

练习题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;

1
2
3
4
5
6
7

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

SELECT SId 
       ,SName
       ,CASE WHEN now_month_day<month_day THEN age-1 ELSE age END AS new_age
       ,age
FROM(
  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
) t1;
1
2
3
4
5
6
7
8
9
10
11
12

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

练习题47

查询本周过生日的学生

SELECT  SId,
				Sname,
				Sage,
				WEEK(Sage)
				FROM student
				WHERE WEEK(Sage) = WEEK(NOW());
1
2
3
4
5
6

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

代表本周没有人生日

练习题48

查询下周生日的学生

SELECT SId,
				Sname,
				Sage,
				WEEK(Sage)
				FROM student
				WHERE WEEK(Sage) = WEEK(DATE_ADD(NOW(),INTERVAL 1 week));
1
2
3
4
5
6

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

练习题49

查询本月过生日的学生

SELECT SId,
				Sname,
				Sage,
				MONTH(Sage)
				FROM student
				WHERE MONTH(Sage) = MONTH(NOW());
1
2
3
4
5
6

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

练习题50

查询下个月过生日的学生

SELECT SId,
				Sname,
				Sage,
				MONTH(Sage)
				FROM student
				WHERE WEEK(Sage) = WEEK(DATE_ADD(NOW(),INTERVAL 1 WEEK));
1
2
3
4
5
6

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

相关阅读:
C语言 | 类型的基本归类
 NodeJS V8引擎的内存和垃圾回收器（GC）
字典类型和字典函数、字典方法
 Vue_组件间通信
 计算机操作系统：进程的同步和互斥
 纯知识分享||Java多线程又遇到死锁？三招帮你彻底解决
 C++ 标准库类型学习笔记（一）（vector、string 篇）
精品基于Uniapp+SSM实现的Android安全网购平台
 基于深度学习的表格检测与识别技术的优势
 vscode支持c++编译
原文地址：https://blog.csdn.net/weixin_59823583/article/details/128103539

经典Mysql入门必刷50题及全网最新最详细的笔记记录

文章目录

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