• SQL39道代码练习题

    文章目录

    🎉前言

    🎉欢迎关注🔎点赞👍收藏⭐️留言📝
    🎉推荐up主专题文章【C语言编程一百题】
    📌QQ：3052645092 有问题可以一起讨论哦
    ⭐️ 财压奴婢手，艺压当行人，一起拼搏吧

    ⭐️各个表的基本信息

    # 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
#课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
#教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
#成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20), # 学号
`c_id` VARCHAR(20),# 课程号
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);

#插入学生表测试数据
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' , '女');
#课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

#教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

#成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score 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

    在这里插入图片描述

    ⭐️1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

    在这里插入图片描述

    分析 01课程成绩和02 的在一个表上了 我们把一个表拆分成两个表 每个临时表来返回 0x课程的成绩 用内连接连接他们两表 连接的条件是学生号相同因为要比的是一个学生的两门成绩高低

    SELECT tmp3.s_name as '学生名字', tmp1.s_score as '01成绩', tmp2.s_score as '02成绩'  from (

        select * from score where c_id=01 
) as tmp1
inner join(
         select *from score where c_id=02
) as tmp2  
          on tmp1.s_id = tmp2.s_id -- 用学生编号来拼接起来 因为你要比的是同一个学生的两门课程成绩
  -- inner join student as tmp3 on tmp3.s_id=tmp1.s_id
       inner join (select *from student) as tmp3 -- 和上面那个语句等价
					 on tmp3.s_id=tmp1.s_id	 
				where tmp1.s_score > tmp2.s_score -- 01课程大于02课程分数比较条件


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

    ⭐️2、查询平均成绩大于60分的学生的学号和平均成绩

    
 
select s_id as '学号',avg(s_score) as avg_score from score
group by s_id
having  avg_score>60 -- group by 后面必须是having 不能是where

    • 1
    • 2
    • 3
    • 4
    • 5

    ⭐️3、查询所有学生的学号、姓名、选课数、总成绩

    分析 把学生表和分数表连接起来 可以获得课程id和分数 在用count求课程数 用sum求总分数 为什么用左外连 student有没有选课的故此没有分数也要显示
    在这里插入图片描述

    
-- 分析 把学生表和分数表连接起来 可以获得课程id和分数 在用count求课程数 用sum求总分数 
-- 为什么用左外连 student有没有选课的故此没有分数也要显示  


    select a.s_id,a.s_name ,count(b.c_id), 
    sum(case when b.s_score is null then 0 else b.s_score end) 
     from student as a 
       left join score as b 
			 on  a.s_id=b.s_id -- 通过学号相同做连接
     group by a.s_id,a.s_name -- 根据学号来排序

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

    在这里插入图片描述

    ⭐️4、查询姓“猴”的老师的个数

    4、查询姓“猴”的老师的个数

select count(t_id) from teacher
where t_name like '张%'

-- count(distinct t_id)这样可以去重

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    ⭐️5、查询没学过“张三”老师课的学生的学号、姓名（重点）

    5、查询没学过“张三”老师课的学生的学号、姓名（重点）

1.先找到张三的教师id 

select t_id from teacher 
where t_name='张三'

2 通过老师id获得课程c_id

select * from course
where t_id = (select t_id from teacher 
where t_name='张三')

3 通过课程id来获得学生s_id 找到了学过这门课的人

select * from score 
where c_id = (select c_id from course
where t_id = (select t_id from teacher 
where t_name='张三'))

4 通过学生id可以用student来获得名字b not in 来判断不在这个范围之内的 就是没选张三课的
			select * from student 
			where s_id not in   (
			select s_id from score 
			where c_id = (select c_id from course
			where t_id = (select t_id from teacher 
			where t_name='张三')
			)
			)

方法2

select *from student 
where s_id not in(
 select s_id from score as s
 inner join course as c on s.c_id = c.c_id
 inner join teacher as t on c.t_id=t.t_id
 where t_name='张三'
 )

select s_id from score as s
 inner join course as c on s.c_id = c.c_id
 inner join teacher as t on c.t_id=t.t_id
 where t_name='张三'




    • 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

    ⭐️6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

    在这里插入图片描述

    6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）
-- 就是3张表连接成一张表 根据相同的属性连接
  -- 一步步建一张表 学号 姓名 课程 老师 （张三）
	
	select st.s_id,st.s_name, c.c_id , t.t_id, t.t_name from student as st
	inner join score as sc on st.s_id= sc.s_id -- 根据学生id连接两个表可以获得课程id
	inner join course as c on c.c_id=sc.c_id -- 连接到course 
	inner join teacher as t on t.t_id = t.t_id -- 连接到老师表
	where t.t_name='张三'
	order by st.s_id 
	
	
	

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

    ⭐️7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）

    在这里插入图片描述

    	
	7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）

-- 学号 姓名 课程号 

select s_id,s_name from student 
where s_id in (
select a.s_id from 
(select s_id from score where c_id = '01') as a-- 学过01课程的人学号  
inner join 
(select s_id from score where c_id = '02' )as b-- 学过02课程的人学号  
on a.s_id= b.s_id 
)



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

    ⭐️8、查询课程编号为“02”的总成绩

    SELECT SUM(s_score) AS '总分' FROM score
GROUP BY c_id
HAVING c_id= '02'

-- 法2
SELECT SUM(s_score) FROM score 
WHERE c_id= '02'

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    ⭐️9、查询学生每门课程成绩均小于60分的学生的学号、姓名

    -- 9、查询学生每门课程成绩均小于60分的学生的学号、姓名
-- 查出小于60分的学生的课程数
-- 查出有分数的学生的课程数
SELECT st.s_name ,st.s_id FROM 
(
SELECT s_id,COUNT(c_id) AS con  FROM score 
 WHERE s_score <60
GROUP BY s_id  -- 找出分数小于60分的课程数 
)AS a INNER JOIN -- 内连接取交集
(
SELECT s_id,COUNT(c_id) AS con FROM score
GROUP BY s_id  -- 有分数学生的课程数
)AS b 
ON a.s_id=b.s_id  -- 内连接取交集 获得名字
INNER JOIN student AS st ON b.s_id= st.s_id 
WHERE a.con=b.con -- 分数小于60数量和有分数的学生数量比较 相等就是
-- 各科均小于60的学生


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

    在这里插入图片描述

    在这里插入图片描述

    ⭐️10.查询没有学全所有课的学生的学号、姓名(重点)

    在这里插入图片描述

    -- 错误做法8号同学一门课也没学 
SELECT a.s_id,b.s_name,a.con FROM 
(SELECT s_id, COUNT(c_id) AS con  FROM score  
GROUP BY s_id) AS a
INNER JOIN student AS b ON a.s_id = b.s_id
WHERE con<3 


-- 正确做法
SELECT st.*,COUNT( sc.c_id) FROM student AS st -- 左连接  
LEFT JOIN score AS sc  ON st.s_id = sc.s_id 
GROUP BY st.s_id     -- 根据 学生id对课程id分组 
HAVING COUNT(DISTINCT sc.c_id)<
(SELECT COUNT(DISTINCT c_id) FROM course)
--      score里面的课程数小于 实在的课程数 就是没有全部学玩课程的人
 

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

    ⭐️11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）

    法1 ：
SELECT s_id ,s_name FROM student -- 判断student的学生学号在不是返回的这个学号范围内
WHERE s_id IN
(
SELECT DISTINCT sc.s_id FROM score AS sc 
WHERE sc.c_id IN(-- 判断别的学生的课程在不在这个范围内
 SELECT c_id FROM score -- 找出01学生学的课程 
 WHERE s_id='01')
 AND s_id!='01' 
 ) 



-- 法2 内连接的方法
SELECT a.s_id ,a.s_name FROM student AS a -- 判断student的学生学号在不是返回的这个学号范围内
 INNER JOIN  
(
SELECT DISTINCT sc.s_id FROM score AS sc 
WHERE sc.c_id IN(-- 判断别的学生的课程在不在这个范围内
 SELECT c_id FROM score -- 找出01学生学的课程 
 WHERE s_id='01') 
 AND s_id!='01' 
 ) AS b 
ON a.s_id=b.s_id  
 
 -- 找出学过和01号学生学过的一样课的学号 在和student表连接 
 -- 通过学号连接 

    • 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

    ⭐️12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)

    -- 12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)

SELECT s_id,COUNT(DISTINCT c_id) FROM score 
WHERE s_id !='01' -- 不能算自己
GROUP BY s_id -- 根据学号分组找课程数
HAVING COUNT(DISTINCT c_id) = (SELECT COUNT(DISTINCT c_id) FROM score 
WHERE s_id = '01')            
AND s_id NOT IN -- 这些学了三门课程的学生 还不能是一下条件里面的
(
SELECT s_id FROM score 
WHERE c_id NOT IN ( -- 找出 学了01号学生课程之外课程的学生 这样学生不符合要求
SELECT c_id FROM score 
WHERE s_id = '01'   -- 找出01学生学了哪几门课
)
)

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

    1. 找出学了三门课的学生
    2. 找出学了01号学生课程之外课程的学生 踢出

    在这里插入图片描述

    在这里插入图片描述
    在这里插入图片描述

    ⭐️13、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）

    -- 13、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）

SELECT st.s_id,st.s_name,AVG(s_score) FROM student AS st 
INNER JOIN score AS sc ON sc.s_id= st.s_id 
WHERE st.s_id IN(
SELECT s_id FROM score
WHERE s_score <60  -- 分数小于60
GROUP BY s_id -- 根据学号分组
HAVING COUNT(s_id)>=2) -- 不及格成绩大于等于两门的 
GROUP BY st.s_id 

-- 找到 分数小与60 且2门以上的同学的学号
SELECT s_id FROM score
WHERE s_score <60  -- 分数小于60
GROUP BY s_id -- 根据学号分组
HAVING COUNT(s_id)>=2


-- 连接两个表 
SELECT st.s_id,st.s_name,AVG(s_score) FROM student AS st 
INNER JOIN score AS sc ON sc.s_id= st.s_id 
WHERE st.s_id IN(


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

    在这里插入图片描述

    ⭐️14、检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）

    14、检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）
SELECT *FROM student AS a INNER JOIN 
score AS b ON a.s_id =b.s_id 
WHERE b.c_id='01' AND b.s_score <60  
ORDER BY s_score DESC  -- 降序排列

    • 1
    • 2
    • 3
    • 4
    • 5

    ⭐️15、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

    15、按平均成绩从高到低
显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)
法一 	
	SELECT sc.s_id,sc.s_score ,tmp.avg1 FROM score AS sc,
	(SELECT s_id,AVG(s_score)AS avg1 FROM score
	GROUP BY s_id -- 通过学号来算平均分
	) tmp -- 创建一个临时表
	WHERE tmp.s_id=sc.s_id -- 为了不出现笛卡尔积
	ORDER BY avg1 DESC -- 排序

法2

SELECT  s_id '学号',MAX(CASE WHEN c_id='01' THEN s_score ELSE NULL END) '语文',
MAX(CASE WHEN c_id='02' THEN s_score ELSE NULL END) '数学',
MAX(CASE WHEN c_id='03' THEN s_score ELSE NULL END) '英语',
AVG(s_score) FROM score 
GROUP BY s_id 
ORDER BY AVG(s_score)DESC
-- 等课程号等于01 是找最大值 01课程只有一门 只有一个成绩 所以最高的就是这一个


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

    在这里插入图片描述

    ⭐️16.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)

    
-- 16.查询各科成绩最高分、最低分和平均分：以如下形式显示：
-- 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)


SELECT b.c_id,b.c_name,MAX(s_score),MIN(s_score),AVG(s_score),
SUM(CASE WHEN s_score >=60 THEN 1 ELSE 0 END)/COUNT(s_id) AS '及格',
-- 大于等于60就返回1 sum累加 在除以人数
SUM(CASE WHEN s_score >=70 AND s_score <80 THEN 1 ELSE 0 END)/COUNT(s_id) AS '中等',
SUM(CASE WHEN s_score >=80 AND s_score < 90 THEN 1 ELSE 0 END)/COUNT(s_id) AS '优良',
SUM(CASE WHEN s_score >=90 THEN 1 ELSE 0 END)/COUNT(s_id) AS '优秀'
 FROM score AS a 
INNER JOIN course AS b ON a.c_id=b.c_id 
GROUP BY b.c_id ,b.c_name  -- 内连接得到课程名 根据课程号分组



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

    在这里插入图片描述

    ⭐️17、查询学生的总成绩并进行排名

    17、查询学生的总成绩并进行排名

SELECT s_id,SUM(s_score)AS sum1 FROM score 
GROUP BY s_id 
ORDER BY sum1 DESC 

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述

    ⭐️18 、查询每个老师所教课程平均分从高到低显示以课程为主体来求平均分

     -- 18 、查询不同老师所教不同课程平均分从高到低显示
-- 以课程为主体来求平均分


SELECT t.t_name,c.c_name,avg1 FROM teacher AS t -- 老师表内连接课程表 在内连接分数表
--                                            获得每门课程的平均分
INNER JOIN course AS c 
ON t.t_id=c.t_id 
INNER JOIN (
SELECT c_id,AVG(s_score) AS avg1 FROM score
GROUP BY c_id) AS sc ON sc.c_id = c.c_id 
 ORDER BY avg1 DESC
 
 

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

    在这里插入图片描述

    ⭐️19、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

    
19、使用分段[100-85],[85-70],[70-60],[<60]
来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

SELECT *FROM score
SELECT *FROM course

SELECT * FROM course AS a
INNER JOIN (
SELECT c_id,SUM(s_score)AS sum1,
(SUM(CASE WHEN s_score<60 THEN 1 ELSE 0 END)) AS '0-60',
(SUM(CASE WHEN s_score>=60 AND s_score<=70 THEN 1 ELSE 0 END)) AS '60-70',
(SUM(CASE WHEN s_score>70 AND s_score<=85 THEN 1 ELSE 0 END)) AS '70-85',
(SUM(CASE WHEN s_score>85 AND s_score<=100 THEN 1 ELSE 0 END)) AS '85-100'
 FROM score 
GROUP BY c_id 
)AS b  ON a.c_id =b.c_id


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

    在这里插入图片描述

    ⭐️20、查询学生平均成绩及其名次

    20、查询学生平均成绩及其名次
SELECT AVG(s_score)AS avg1 FROM score
GROUP BY s_id
ORDER BY avg1 DESC


    • 1
    • 2
    • 3
    • 4
    • 5

    ⭐️21、查询每门课程被选修的学生数

    -- 26、查询每门课程被选修的学生数
-- score course 
SELECT b.c_id,b.c_name ,COUNT( DISTINCT a.s_id) FROM score AS a
INNER JOIN course AS b 
ON a.c_id = b.c_id 
GROUP BY b.c_id,b.c_name-- 根据课程分组 count累加学了这门课的学生


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    ⭐️ 22、 查询出只有两门课程的学生的学号和姓名、

    -- 27、 查询出只有两门课程的学生的学号和姓名、
-- 法1
SELECT *FROM student AS st INNER JOIN (
SELECT s_id ,COUNT(c_id) AS con FROM score 
GROUP BY s_id
)AS sc ON sc.s_id=st.s_id 
WHERE sc.con=2  --  依据学号找出学了两门课的学生  
-- 法2

SELECT *FROM student -- 子查询找出学了两门课的学生 在和student比较
WHERE s_id IN(
SELECT s_id FROM score 
GROUP BY s_id 
HAVING COUNT(c_id) =2 
)



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

    在这里插入图片描述

    ⭐️23、查询男生、女生人数

    -- 23、查询男生、女生人数

-- 法1 
SELECT s_sex,COUNT(s_sex) FROM student 
GROUP BY s_sex

-- 法2 查男女人数 按学号就行 按性别就是看 字段‘男’‘女’有几个  
SELECT s_sex,COUNT(DISTINCT s_id) FROM student 
GROUP BY s_sex

-- 法3 
SELECT s_sex,
SUM(CASE WHEN s_sex='男' THEN 1 ELSE 0 END)AS 'man',
SUM(CASE WHEN s_sex='女' THEN 1 ELSE 0 END)AS 'woman'
FROM student 
GROUP BY s_sex 


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

    在这里插入图片描述

    ⭐️24 查询名字中含有"风"字的学生信息

    -- 24 查询名字中含有"风"字的学生信息
SELECT *FROM student 
WHERE s_name LIKE '%风%'




    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述

    ⭐️25、查询1990年出生的学生名单

    -- 25、查询1990年出生的学生名单 
SELECT *FROM student
WHERE YEAR(s_birth)='1990'

    • 1
    • 2
    • 3

    在这里插入图片描述

    ⭐️26、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

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

SELECT a.s_id,a.s_name ,AVG(b.s_score) AS avg_ FROM student AS a INNER JOIN
score AS b ON a.s_id=b.s_id  -- 连接两个表
GROUP BY a.s_id,a.s_name-- 根据学号分组合名字分组都一样因为名字和学号是唯一一一对应的
HAVING avg_>=85


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

    在这里插入图片描述

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

    -- 27、查询每门课程的平均成绩，结果按平均成绩升序排序，
-- 平均成绩相同时，按课程号降序排列
SELECT c_id, AVG(s_score)AS avg_score FROM score 
GROUP BY c_id
ORDER BY avg_score ,c_id DESC
-- 先按平均分排序，分数一样然后按课程号排序


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

    ⭐️28、查询课程名称为"数学"，且分数低于60的学生姓名和分数

    -- 28、查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT *FROM student AS a  -- 学生表连接分数表连接课程表 
INNER JOIN score AS b 
ON a.s_id=b.s_id 
INNER JOIN course AS c
ON b.c_id=c.c_id
WHERE c.c_name = '数学' AND b.s_score<60


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    在这里插入图片描述

    ⭐️29、查询所有学生的课程及分数情况

    -- 29、查询所有学生的课程及分数情况
-- 备注:1.因为要选出需要的字段 用case when 当co.c_name='数学' then
-- 可以得到对应的 sc.s_core
-- 2.因为GROUP UP 要与select 列一致，所以case when 加修饰max
-- 3.因为最后要展现出每个同学的各科成绩为一行，所以用到case



SELECT a.s_id,a.s_name,
MAX(CASE WHEN c.c_name='语文' THEN b.s_score ELSE NULL END)AS '语文',
-- 因为只有一个语文成绩 得到的最大值就是语文成绩
MAX(CASE WHEN c.c_name='数学' THEN b.s_score ELSE NULL END)AS '数学',
MAX(CASE WHEN c.c_name='英语' THEN b.s_score ELSE NULL END)AS '英语'
FROM student AS a LEFT JOIN -- 左连接student里面的学生都会显示 
score AS b ON a.s_id=b.s_id LEFT JOIN 
course AS c ON b.c_id=c.c_id 
GROUP BY a.s_name ,a.s_id -- 根据学生id来分组  
ORDER BY a.s_id



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

    在这里插入图片描述

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

    -- 30、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
-- 化繁为简，先死后活
SELECT  sc.s_id,st.s_name, sc.s_score  FROM score  AS sc
INNER JOIN student AS st ON sc.s_id=st.s_id
INNER JOIN course AS so ON so.c_id= sc.c_id
 WHERE sc.s_score >70

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述

    ⭐️31、查询不及格的课程并按课程号从大到小排列

    -- 31、查询不及格的课程并按课程号从大到小排列


SELECT *FROM student AS st INNER JOIN
(SELECT *FROM score 
WHERE s_score<60 -- 小于60的
ORDER BY c_id)AS a -- 按课程号排序
ON st.s_id=a.s_id 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    在这里插入图片描述

    ⭐️32、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

    -- 32、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
SELECT sc.s_id,st.s_name,sc.s_score FROM score AS sc INNER JOIN 
student AS st ON sc.s_id=st.s_id  
WHERE sc.s_score>80 AND sc.c_id='03'
-- 因为没有名字所以要连接student表


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述

    ⭐️33、求每门课程的学生人数

    -- 33、求每门课程的学生人数
SELECT COUNT(s_score) AS '每门课程人数'FROM score 
GROUP BY c_id 


    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

    ⭐️34、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

    -- 34、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩


  
-- 法1 本题主要学limit这个关键字 因为要求最高分用group by 别的信息就把不能出现在select语句中 
-- 排序完用limit可以找出第一个最高分  
SELECT *FROM student AS st 
INNER JOIN score AS sc ON st.s_id=sc.s_id 
WHERE sc.c_id =(
SELECT c_id FROM course -- 获得课程号
WHERE t_id =(
SELECT t_id FROM teacher -- 获得老师号
WHERE t_name= '张三')
) ORDER BY sc.s_score DESC LIMIT 0,1

-- 法2
SELECT s.s_id,st.s_name ,s.s_score,c.c_name,t.t_name FROM score AS s INNER JOIN 
course AS c ON s.c_id=c.c_id INNER JOIN
teacher AS t ON  c.t_id =t.t_id INNER JOIN
student AS st ON st.s_id=s.s_id 
WHERE t.t_name='张三'
ORDER BY s.s_score DESC
LIMIT 0,1


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

    在这里插入图片描述

    ⭐️35.查询2门及以上课程成绩相同的学生的学生编号、课程编号、学生成绩

    -- 35.查询2门及以上课程成绩相同的学生的学生编号、课程编号、学生成绩 

SELECT *FROM score AS sc4 INNER JOIN (
SELECT sc3.s_id FROM  (
SELECT sc1.s_id,sc1.s_score FROM score AS sc1 INNER JOIN
(
SELECT s_id FROM score 
GROUP BY s_id HAVING COUNT(DISTINCT c_id)>1 -- 排除一门课程的同学 得到多门课程的同学学号
) AS sc2 ON sc1.s_id=sc2.s_id 
-- 查找1门以上成绩相等的学生学号
GROUP BY sc1.s_id,sc1.s_score --  group by 按学号和成绩分组 成绩一样就分为一个组
) AS sc3 
GROUP BY sc3.s_id-- 再根据学号分组分为1个组的就是所要找的
HAVING COUNT(sc3.s_id)=1   -- 所以这里要是==1 就说明成绩一样
) AS sc5 ON sc4.s_id=sc5.s_id 



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

    在这里插入图片描述

    ⭐️36、查询各学生的年龄（精确到月份）

    -- 36、查询各学生的年龄（精确到月份）

SELECT s_name,FLOOR(DATEDIFF(NOW(),s_birth)/365) FROM student 
-- 向下取整 

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

    ⭐️37 查询选修了全部课程的学生

    -- 37 查询选修了全部课程的学生
SELECT s_id, COUNT(c_id)AS cnt FROM score 
GROUP BY s_id  -- 根据学生号分组 
HAVING cnt=(SELECT COUNT(c_id)FROM course)
-- 计算出来的个数等于课程个数 就合法


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述

    ⭐️38 查询下周过生日的学生

    -- 38 查询下周过生日的学生 

-- 因为每一年的周数是不同的出生年的周数和当前年可能是不一样的 
-- 所以要按当前年的年算
-- 拼接成当前年的 某某月份
SELECT * FROM student
WHERE WEEK('2022-1-10')+1 = WEEK(CONCAT(YEAR(NOW()),SUBSTRING(s_birth,5,6)))
-- +1 就是+1 周的意思 也就是判断有没有下一周过生日的
		-- week() 是根据日期求周数 


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    在这里插入图片描述

    ⭐️39 查找本月过生日的人

    -- 39 查找本月过生日的人

SELECT*FROM student 
WHERE MONTH(NOW())=MONTH(s_birth)

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

  • 相关阅读:
    函数以及变量
    Windows 删除恢复分区
    《QT实用小工具·三十四》Qt/QML使用WebEngine展示的百度ECharts图表Demo
    Windows系统无法激活Python虚拟环境的解决方案：无法加载文件 ，因为在此系统上禁止运行脚本。
    Intel汇编-Linux系统调用
    在线电子表格spreadjs
    SpringMvc高级（拦截器和文件上传下载）
    SpringBoot项目打印接口请求日志，CommonsRequestLoggingFilter实现方式
    信号完整性（SI）电源完整性（PI）学习笔记（三十一）电源分配网路（三）
    [X3m]Ubuntu 根文件系统制作
  • 原文地址：https://blog.csdn.net/m0_56398287/article/details/126528555