Mysql 经典45道题

最新推荐文章于 2025-05-14 19:43:06 发布
最新推荐文章于 2025-05-14 19:43:06 发布
阅读量317 收藏
点赞数
分类专栏： 数据库 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/fen_dou_shao_nian/article/details/120215773
版权

一、建表语句

/*
Navicat MySQL Data Transfer

Source Server         : Mysql经典45题
Source Server Version : 50719
Source Host           : localhost:3306
Source Database       : mysql-test45

Target Server Type    : MYSQL
Target Server Version : 50719
File Encoding         : 65001

Date: 2021-09-07 08:13:43
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `CId` varchar(10) DEFAULT NULL COMMENT '课程编号',
  `Cname` varchar(10) DEFAULT NULL COMMENT '课程名称',
  `TId` varchar(10) DEFAULT NULL COMMENT '教师编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `SId` varchar(10) DEFAULT NULL COMMENT '学生编号',
  `CId` varchar(10) DEFAULT NULL COMMENT '课程编号',
  `score` decimal(18,1) DEFAULT NULL COMMENT '分数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表';

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('01', '01', '80.0');
INSERT INTO `sc` VALUES ('01', '02', '90.0');
INSERT INTO `sc` VALUES ('01', '03', '99.0');
INSERT INTO `sc` VALUES ('02', '01', '70.0');
INSERT INTO `sc` VALUES ('02', '02', '60.0');
INSERT INTO `sc` VALUES ('02', '03', '80.0');
INSERT INTO `sc` VALUES ('03', '01', '80.0');
INSERT INTO `sc` VALUES ('03', '02', '80.0');
INSERT INTO `sc` VALUES ('03', '03', '80.0');
INSERT INTO `sc` VALUES ('04', '01', '50.0');
INSERT INTO `sc` VALUES ('04', '02', '30.0');
INSERT INTO `sc` VALUES ('04', '03', '20.0');
INSERT INTO `sc` VALUES ('05', '01', '76.0');
INSERT INTO `sc` VALUES ('05', '02', '87.0');
INSERT INTO `sc` VALUES ('06', '01', '31.0');
INSERT INTO `sc` VALUES ('06', '03', '34.0');
INSERT INTO `sc` VALUES ('07', '02', '89.0');
INSERT INTO `sc` VALUES ('07', '03', '98.0');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `SId` varchar(10) DEFAULT NULL COMMENT '学生编号',
  `Sname` varchar(10) DEFAULT NULL COMMENT '学生姓名',
  `Sage` datetime DEFAULT NULL COMMENT '出生年月',
  `Ssex` varchar(10) DEFAULT NULL COMMENT '学生性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20 00:00:00', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06 00:00:00', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01 00:00:00', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01 00:00:00', '女');
INSERT INTO `student` VALUES ('09', '张三', '2017-12-20 00:00:00', '女');
INSERT INTO `student` VALUES ('10', '李四', '2017-12-25 00:00:00', '女');
INSERT INTO `student` VALUES ('11', '李四', '2017-12-30 00:00:00', '女');
INSERT INTO `student` VALUES ('12', '赵六', '2017-01-01 00:00:00', '女');
INSERT INTO `student` VALUES ('13', '孙七', '2018-01-01 00:00:00', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `TId` varchar(10) DEFAULT NULL COMMENT '教师编号',
  `Tname` varchar(10) DEFAULT NULL COMMENT '教师姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教师表';

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

二、练习题

##############################################################################################################
##########																																													##########
##########																				mysql45道经典题																		##########
##########																																													##########
##############################################################################################################

## 第1.1题：查询同时存在01课程 和 02 课程的记录
### 01课程和 02 课程的记录表 只有在成绩表中才有
### 分析：先找到01课程然后连接02课程
SELECT
	*
FROM
	(SELECT * FROM sc WHERE CId = '01') a
INNER JOIN (SELECT * FROM sc WHERE CId = '02') b ON a.SId = b.SId 

### 改进
SELECT
	*
FROM
	sc a
INNER JOIN sc b ON a.SId = b.SId
WHERE
	a.CId = '01'
AND b.CId = '02' 

## 第1.2题：查询存在‘01’ 课程但可能不存在 ‘02’课程的情况（不存在是显示为null）
### 分析：先找到存在01课程的记录，然后和自己的其他课程关联，如果时02就关联，不是02就不关联，显示为null
### 不存在时显示为null那么只能用left join 才可以,这道题的挂件的左联条件，左联本身没有时就显示null，可能存在可能不存在的
### 左联条件就是=‘02’
SELECT
	*
FROM
	(SELECT * FROM sc WHERE CId = '01') a
LEFT JOIN sc b ON a.SId = b.SId
AND b.CId = '02'

### 改进
SELECT * from sc a LEFT JOIN sc b
ON a.SId=b.SId and b.CId='02'
where a.CId='01' ;

## 第1.3题：找出不存在01课程的记录，但存在 02 课程的记录
### 分析：用子查询找到不存在01课程的记录后，然后再进行关联找到存在02课程的记录，可以用子查询筛选+inner join实现

SELECT  * from 
(SELECT * from sc where SId not in(SELECT SId from sc WHERE CId='01')) a
INNER JOIN sc b on a.SId=b.SId AND b.CId='02'

### 改进，找出不存在01课程的记录，然后把存在02的找出来
### 由于第一个种方法有关联链，所有最好做个去重
SELECT * from  sc a 
where SId NOT in(SELECT SId FROM sc where CId='01') 
AND a.CId='02'

--------------------------------------------------------------------------------------------------------------

## 第2题：查询平均成绩大于等于60的同学的编号和学生姓名和平均成绩
### 分析：从成绩表查询出平均成绩大于60的学生信息，然后通过SId的关联学生信息
### 关键点：group by  having
### 第一步：查询出平均成绩大于60的学生
SELECT
 sid,AVG(score) as vag_score
from sc  GROUP BY SId HAVING AVG( score)>60;
### 第二步：关联查询
SELECT a.SId,a.Sname,b.vag_score from student a 
INNER JOIN (SELECT sid,AVG(score) as vag_score from sc  GROUP BY SId HAVING AVG( score)>60)b
ON a.SId=b.SId;

--------------------------------------------------------------------------------------------------------------

## 第3题：查询在sc表中存在成绩的学生信息
### 分析：成绩表肯定都有学生的，所以用成绩表左联就可以得到学生信息。
### 关键点：用group by 去重
### group by 只能查询分组字段，其他字段需要通过聚合信息查出来。因为分组是对一个组的信息的表达，不能把某条个人的信息查出来。
SELECT b.* from sc a
LEFT JOIN student b 
ON a.SId=b.SId;

### 改进后去重
SELECT b.*
from (SELECT SId from sc GROUP BY SId) a
LEFT JOIN student b
ON a.SId=b.SId;

--------------------------------------------------------------------------------------------------------------

## ★第4题：查询所有学生的编号、姓名、选课总数、所有课程的总成绩（没成绩的显示为null）
### 分析：选课总数和课程的总成绩可以通过分组聚合查到，用学生信息取关联分组信息后的结果就可以得到最终结果
### 关键点：goupby count(1) sum() LEFT JOIN
### 第一步：查询分组信息，选课总数和总分
select SId,SUM(score) as scoreSum,COUNT(1) as cons from sc GROUP BY SId;
### 第二步：学生表左联上面的结果集
SELECT a.SId,a.Sname,b.cons, b.scoreSum from student a
LEFT JOIN (select SId,SUM(score) as scoreSum,COUNT(1) as cons from sc GROUP BY SId) b
ON a.SId=b.SId
### 改进：先把所有的信息关联出来再分组
SELECT a.SId,a.SName,SUM(b.score) as scoreSum,COUNT(1) as cons   from student a
LEFT JOIN sc b
ON a.SId=b.SId
GROUP BY a.SId;

--------------------------------------------------------------------------------------------------------------

## 第5题：查询 李 姓老师的数量
### 分析：
### 关键点：% 占位符的使用
SELECT count(1) as cons FROM teacher
WHERE Tname LIKE '李%'

--------------------------------------------------------------------------------------------------------------

## 第6题：查询学习过 张三  老师授课的学生信息
### 分析：需要关联4张表，成绩表，学生表，课程表，老师表，通过判断老师表的名称查询出来。
### 关键点：inner  join  like
SELECT * from sc a
INNER JOIN student b on a.SId=b.SId
INNER JOIN course c on a.CId=c.CId
INNER JOIN teacher d on c.TId=d.TId
WHERE d.Tname='张三'


--------------------------------------------------------------------------------------------------------------

## 第7题：查询没有学全所有课程的同学信息
### 分析：通过学生标号分组查询每个人学的课程数量，条件是数量<课程表中的总数量
### 关键点：group by  HAVING
SELECT a.SId,b.Sname,COUNT(1) as cons from sc  a
INNER JOIN student b
ON a.SId=b.SId
GROUP BY SId
HAVING cons<(SELECT count(1) as counts FROM course)

--------------------------------------------------------------------------------------------------------------

## 第8题：查询至少有一门课与学号为 01 的同学所学相同的同学的信息
### 分析：首选查出01同学学的所有课程，然后判断当前的课程是不是再01同学的课程结合中，最后查询的是同学信息，那么where条件肯定要删除自己本身，
### 关键点：group by   in
SELECT a.*,b.Sname from sc a 
INNER  JOIN student b 
ON a.SId=b.SId
WHERE a.SId!='01' AND a.CId in(SELECT CId from sc WHERE SId='01') 
GROUP BY a.SId;

--------------------------------------------------------------------------------------------------------------

## ★第9题：查询和 01 号同学学习的课程完全相同的其他同学的信息
### 分析：用两个条件来保证 第一没有 01 号同学课程以外的课程，第二 和01号同学所学的课程数量是一致的。
### 关键点：两个not in   group by  和  having
### 第一步：找到01同学学了那些课程
SELECT CId from sc WHERE SId='01'
### 第二步：找到没有学01课程的同学id
SELECT * from sc WHERE CId NOT IN(SELECT CId from sc WHERE SId='01')
### 第三步：
SELECT a.SId,b.* FROM sc a
INNER JOIN student b ON a.SId=b.SId
WHERE a.SId  NOT in(SELECT SId from sc WHERE CId NOT IN(SELECT CId from sc WHERE SId='01'))
GROUP BY a.SId
HAVING COUNT(1)=(SELECT COUNT(1) from sc WHERE SId='01' )

--------------------------------------------------------------------------------------------------------------

## ★第10题：查询没有学过 张三  老师讲授的任一门课程的学生
### 分析：第一步：找到张三老师带的课，然后找到学生的课程id not in 就可以了。
### 关键点：not in  
### 第一步：找到张三老师带的课
SELECT a.CId from course a INNER JOIN teacher b ON a.TId=b.TId WHERE b.Tname='张三'
###  这里应该找到学过张三老师课的学生
SELECT a.Si from sc a 
LEFT JOIN student b ON a.SId=b.SId
LEFT JOIN course c on a.CId=c.CId
LEFT JOIN teacher d on c.TId=d.TId
WHERE d.Tname='张三'
### 第二步 错误写法
SELECT  * from sc c
LEFT JOIN student d
ON c.SId=d.SId
WHERE c.CId not in(SELECT a.CId from course a INNER JOIN teacher b ON a.TId=b.TId WHERE b.Tname='张三') 

### 正确写法
SELECT  DISTINCT d.* from sc c
LEFT JOIN student d
ON c.SId=d.SId
WHERE c.SId not in(SELECT a.SId from sc a 
										LEFT JOIN student b ON a.SId=b.SId
										LEFT JOIN course c on a.CId=c.CId
										LEFT JOIN teacher d on c.TId=d.TId
										WHERE d.Tname='张三') 


--------------------------------------------------------------------------------------------------------------

## ★第11题：查询两门课及以上不及格课程的学号、姓名、以及平均成绩
### 分析：分组查询学生的平均信息，把符合2门以上不合格的学生信息inner join 从而过滤掉不符合的。
### 关键点：group by 利用inner join 做条件进行查询
### 第一步：通过学生编号分组查询，学生的平均成绩
SELECT a.SId,b.Sname,AVG(a.score) as avg_student from sc a
INNER JOIN student b ON a.SId=b.SId
GROUP BY a.SId
### 第二步：通过学生分组查询哪些同学有两门以上不合格
SELECT SId from sc where score<60
GROUP BY SId
HAVING COUNT(1)>1
### 第三步：再求平均值的时候通过过滤条件筛选出来
SELECT a.SId,b.Sname,AVG(a.score) as avg_student from sc a
LEFT JOIN student b ON a.SId=b.SId
-- 此处的inner join 相当于作为条件去过滤了没有对应的数据
INNER JOIN (SELECT SId from sc where score<60 GROUP BY SId HAVING COUNT(1)>1) c ON a.SId=c.SId
GROUP BY a.SId

--------------------------------------------------------------------------------------------------------------

## ★第12题：检索 01 课程分数小于60分， 按照分数降序排列的学生信息
### 分析：查询01 课程小于60分的学生id 然后 通过SId 关联学生表
### 关键点：子查询作为结果集
### 第一步：查询01 课程小于60分的学生id
SELECT SId from sc where score<60 AND CId='01' 
GROUP BY SId;
### 第二步：通过SId 关联学生表
SELECT a.score,b.* from sc a
LEFT JOIN student b on a.SId=b.SId
WHERE a.score<60 and a.CId='01'
ORDER BY a.score desc
### 改进
SELECT a.score,b.* from (SELECT SId,score from sc where score<60 AND CId='01' GROUP BY SId) a
INNER JOIN student b ON a.SId=b.SId
ORDER BY a.score DESC

--------------------------------------------------------------------------------------------------------------

## 第13题：按平均成绩从高到低显示学生的所有课程的成绩以及平均成绩
### 分析：
### 关键点：INNER JOIN 中嵌套子查询
### 第一步：查询出每个学生的平局成绩
SELECT SId,AVG(score) as avg_score from sc GROUP BY SId
### 第二步：给每个学生信息加上平均成绩
SELECT * from sc a 
INNER JOIN (SELECT SId,AVG(score) as avg_score from sc GROUP BY SId) b ON a.SId=b.SId
ORDER BY b.avg_score 

--------------------------------------------------------------------------------------------------------------

## ★第14题：查询各科成绩最高分，最低分和平均分,以如下形式显示：
### 课程ID，课程name，最高分，最低分，平均分，及格率（score>=60），中等率（[70-80)），优良率（[80-90)），优秀率（>=90）
### 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号的升序排列
### 分析：按照学科进行分类，然后通过聚合函数输出
### 关键点：case WHEN 条件计数
### 第一步：分类查询各科成绩最高分，最低分和平均分
SELECT 
CId,
MAX(score) as 最高分,
MIN(score) as 最低分,
AVG(score) as 平均分,
count(1) as 选修人数,
sum(case WHEN score>=60 then 1 ELSE 0 end)/count(1) as 及格率,
sum(case WHEN score>=70 and score<80 then 1 ELSE 0 end)/count(1) as 中等率,
sum(case WHEN score>=80 and score<90 then 1 ELSE 0 end)/count(1) as 优良率,
sum(case WHEN score>=90 then 1 ELSE 0 end)/count(1) as 优秀率
from sc a GROUP BY CId
order BY COUNT(1) DESC , CId ASC

--------------------------------------------------------------------------------------------------------------

## 第15.1题：按各科成绩进行排序，并显示排名，Score重复时继续排序
### 分析：按照成绩排序，from 后面的多个表，表示没有条件的inner join
### 关键点：@rank:=1变量的使用,变量的执行顺序，from后面从左到右，selec后面从左到右
### 第一步：定义变量
SELECT @rank:=1;
SELECT @rank;
### 第二步：
SELECT a.CId,a.SId,a.score,(@rank:=@rank+1) sort_
FROM sc a ,(SELECT @rank:=0) b 
ORDER BY a.score desc

## ★第15.2题：按各科成绩进行排序，并显示排名，Score重复时并列排序
### 分析：按照成绩排序，from 后面的多个表，表示没有条件的inner join
### 关键点：@rank:=1变量的使用冒号表示赋值
### 第一步：定义变量
SELECT @rank:=1;
SELECT @rank;
### 第二步：按各科成绩进行排序，并显示排名，Score重复时合并名次如两个并列第一 
SELECT a.CId,a.SId,a.score,
CASE WHEN (@sco=score) THEN @rank ELSE @rank:=@rank+1 end AS sort_,
@sco:=score
FROM sc a ,(SELECT @rank:=0,@sco:=null) b 
ORDER BY a.score desc
### 改进
SELECT a.CId,a.SId,a.score,
CASE  WHEN (@sco=score) THEN @rank 
			WHEN @sco:=score THEN @rank:=@rank+1
			end AS sort_
FROM sc a ,(SELECT @rank:=0,@sco:=null) b 
ORDER BY a.score desc

--------------------------------------------------------------------------------------------------------------

## 第16题：查询学生的总成绩，并进行排名，总分重复时保留名次空值
### 分析：
### 关键点：自定义变量 用:= 进行赋值
### 第一步：自定义变量
#### 方法一
set @a:=2;
SELECT @a;
#### 方法二
SELECT @b:=4;
SELECT @b;
### 第二步：查询学生的总分并倒叙排列
SELECT SId,SUM(score) as sum_score from sc a   GROUP BY SId ORDER BY sum_score DESC
### 第三步：定义变量
SELECT 
		b.*, 
		@rank:=if(@sco=sum_score,'',@rank+1) as 排名,
		@sco:=sum_score 总分
from 
(SELECT SId,SUM(score) as sum_score from sc a   GROUP BY SId ORDER BY sum_score DESC) b ,
(SELECT @sco:=null,@rank:=0) c

--------------------------------------------------------------------------------------------------------------

## ★第17题：统计各科成绩个分段人数：课程编号，课程名称,[100-85),[85-70),[70-60),[60-0] 及所占百分比
### 分析：按照课程进行分组统计
### 关键点：concat()拼接  SUM() case when
### 第一步：求百分率

SELECT
  CId,
	COUNT(1) as count_,
	concat((SUM(case WHEN score<=60 and score>=0 THEN 1 ELSE 0 END)/COUNT(1)*100),'%') as persent_1,
	concat((SUM(case WHEN score<=70 and score>60 THEN 1 ELSE 0 END)/COUNT(1)*100),'%') as persent_2,
	concat((SUM(case WHEN score<=85 and score>70 THEN 1 ELSE 0 END) /COUNT(1)*100),'%') as persent_3,
	concat((SUM(case WHEN score<=100 and score>85 THEN 1 ELSE 0 END)/COUNT(1)*100),'%') as persent_4
from sc where 1 GROUP BY CId ORDER BY count_;


--------------------------------------------------------------------------------------------------------------

## ★第18题：查询各科成绩前三名的记录
### 分析：前三名转化为若大于此成绩的数量少于3 即为前三名SELECT * from sc where 大于此成绩的记录小于3条
### 关键点：where 条件中用到了笛卡尔积
SELECT a.* from sc a
WHERE
(SELECT count(1) from sc b where a.CId=b.CId and b.score>a.score)<3
ORDER BY a.CId ASC , a.score desc


--------------------------------------------------------------------------------------------------------------

## 第19题：查询没门课程被选修的学生数
### 分析：
### 关键点：GROUP BY  COUNT(1)
### 第一步：
SELECT CId,COUNT(1) as count_course from sc a
GROUP BY CId;

--------------------------------------------------------------------------------------------------------------

## 第20题：查询出只选修2门课程的学生编号和姓名
### 分析：
### 关键点：GROUP BY HAVING
### 第一步：查询出选修了2们课的学生id
SELECT DISTINCT a.SId,c.sname from sc a
LEFT JOIN student c ON a.SId=c.Sid 
WHERE (SELECT count(1) from sc b where a.SId=b.SId)=2 ;

### 改进
SELECT a.SId,b.sname from sc a
LEFT JOIN student b on a.SId=b.SId
GROUP BY a.SId,b.sname
HAVING count(1)=2

--------------------------------------------------------------------------------------------------------------

## 第21题：查询男生，女生的人数
### 分析：用性别进行分组
### 关键点：group by
### 第一步：
SELECT a.Ssex,COUNT(1) as count_sex from student a GROUP BY a.Ssex

--------------------------------------------------------------------------------------------------------------

## 第21题：查询男生，女生的人数
### 分析：用性别进行分组
### 关键点：group by
### 第一步：
SELECT a.Ssex,COUNT(1) as count_sex from student a GROUP BY a.Ssex

--------------------------------------------------------------------------------------------------------------

## 第22题：查询名字中含有 风 字的学生信息
### 分析：
### 关键点：like  % 0个或多个字符；_ 一个字符 
### 第一步：
SELECT * from student a where a.Sname LIKE '%风%';
SELECT * from student a where a.Sname LIKE '_风%';

--------------------------------------------------------------------------------------------------------------

## 第23题：从查询同名同性别学生名单，并统计同名同性别人数
### 分析：学生表自己关联自己，然后进行姓名和性别的分组
### 关键点：INNER JOIN group by
### 第一步：找到同名同性别的人
SELECT * from student a
INNER JOIN student b on a.Sname=b.Sname and a.Ssex=b.Ssex and a.SId!=b.SId
### 第二步：用姓名和性别进行统计
SELECT a.*,count(1) as count_sname from student a
INNER JOIN student b on a.Sname=b.Sname and a.Ssex=b.Ssex and a.SId!=b.SId
GROUP BY a.Sname,a.Ssex

--------------------------------------------------------------------------------------------------------------

## 第24题：查询1990年出生的学生名单
### 分析：通过 出生日期 字段查询，通过year函数判断
### 关键点：year函数的使用
### 第一步：year() 函数的使用
SELECT year('2021-01-02 11:11:11');
SELECT year('2021/01/02 11:11:11');
SELECT year('2021-01/02 11:11:11');
### 第二步：通过year函数做条件查询
SELECT a.* from student a 
WHERE YEAR(a.Sage)='1990'

--------------------------------------------------------------------------------------------------------------

## 第25题：查询每门课程的平均成绩，结果按平均成绩降序排列,如果平均成绩相同，按照课程编号升序排序
### 分析：
### 关键点：ORDER BY AVG 
SELECT CId,AVG(score) as avg_score from sc a 
GROUP BY CId 
ORDER BY avg_score desc,CId;

--------------------------------------------------------------------------------------------------------------

## 第26题：查询平均成绩大于等于 85 的所有学生的学号，姓名和平均成绩
### 分析：用性别进行分组
### 关键点：group by
### 第一步：
SELECT a.Ssex,COUNT(1) as count_sex from student a GROUP BY a.Ssex

--------------------------------------------------------------------------------------------------------------

## 第26题：查询平均成绩大于等于 85 的所有学生的学号，姓名和平均成绩
### 分析：用性别进行分组
### 关键点：group by
### 第一步：
SELECT a.Ssex,COUNT(1) as count_sex from student a GROUP BY a.Ssex

--------------------------------------------------------------------------------------------------------------

## 第26题：查询平均成绩大于等于 85 的所有学生的学号，姓名和平均成绩
### 分析：分组聚合求平均值，然后筛选学生信息
### 关键点：group by
### 第一步：查询出平均成绩大于85的学生id
SELECT a.SId,avg(a.score) as avg_score from sc a GROUP BY a.SId HAVING avg(a.score)>85
### 第二步： 左列学生的学号和姓名
SELECT a.SId,b.Sname,avg(a.score) as avg_score from sc a 
LEFT JOIN student b on a.SId=b.SId
GROUP BY a.SId HAVING avg(a.score)>85
### 改进
SELECT a.SId,b.Sname,a.avg_score as avg_score 
from (SELECT a.SId,avg(a.score) as avg_score from sc a GROUP BY a.SId HAVING avg(a.score)>85) a 
LEFT JOIN student b on a.SId=b.SId

--------------------------------------------------------------------------------------------------------------

## 第27题：查询课程名称为 数学 ，且分数低于 60 分的学生姓名和分数
### 分析：分数表管理课程表关联学生表
### 关键点：inner join 条件关联查询
### 第一步：查询课程名称为 数学 的数据
SELECT * from sc a INNER JOIN course b on a.CId=b.CId WHERE b.Cname='数学'
### 第二步：关联学生表
SELECT * from sc a 
INNER JOIN course b on a.CId=b.CId 
INNER JOIN student c on a.SId=c.SId 
WHERE b.Cname='数学' AND a.score<60

### 改进
SELECT * from sc a  
INNER JOIN student c on a.SId=c.SId 
WHERE a.CId=(SELECT CId FROM course where Cname='数学') AND a.score<60

--------------------------------------------------------------------------------------------------------------

## 第28题：查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
### 分析：给学生信息上加上课程情况
### 关键点：LEFT JOIN
SELECT a.SId,a.Sname,c.Cname,b.score from  student a 
LEFT JOIN sc b on a.SId=b.SId
LEFT JOIN course c on b.CId=c.CId
ORDER BY a.SId,b.score DESC;

--------------------------------------------------------------------------------------------------------------

## 第29题：查询任何一门课程成绩在 70 分以上的学生姓名，课程名称和分数
### 分析：查询成绩表关联学生表和课程表
### 关键点：inner JOIN 条件查询
SELECT b.SId,b.Sname,c.Cname,a.score FROM sc a
INNER JOIN student b ON a.SID=b.SId
INNER JOIN course c ON a.CId=c.CId
WHERE a.score>70
ORDER BY a.score desc;

--------------------------------------------------------------------------------------------------------------

## 第30题：查询存在不及格的课程
### 分析：查的时课程
### 关键点：关联查询 或者 条件查询 
### 关联查询
SELECT DISTINCT a.CId, c.Cname  FROM sc a
INNER JOIN course c ON a.CId=c.CId
WHERE a.score<60
ORDER BY a.score desc;

### 条件查询
### 第一步：查询出不及格的课程id
SELECT CId from sc WHERE score<60
### 第二步：把上面的结果作为查询条件
SELECT a.* from course a 
WHERE a.CId in(SELECT CId from sc WHERE score<60);

--------------------------------------------------------------------------------------------------------------

## 第31题：查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和名字
### 分析：
### 关键点：条件查询 
### 第一步：查询出编号为 01 并且 分数>=80 的学生id
SELECT SId from sc WHERE CId='01' and score>=80
### 第二步：查询student 把上面的结果作为查询条件
SELECT * from student a 
WHERE 
a.SId in(SELECT SId from sc WHERE CId='01' and score>=80);

--------------------------------------------------------------------------------------------------------------

## 第32题：求每门课程的学生人数
### 分析：对分数表进行分组聚合
### 关键点：分组聚合
SELECT CId,COUNT(1) as count_ from sc GROUP BY CId;

--------------------------------------------------------------------------------------------------------------

## ★第33题：假设成绩不重复，查询选修  张三  老师所授课程的学生中，成绩最高的学生信息和成绩
### 分析：关联4张表，通过条件筛选 张三 老师的数据，最后通过排序和limit找出最高的一条
### 关键点：关联 查询 筛选 限制LEFT JOIN ORDER BY LIMIT
SELECT * from sc a
LEFT JOIN student b ON a.SId=b.SId
LEFT JOIN course c ON a.CId=c.CId
LEFT JOIN teacher d on c.TId=d.TId
WHERE d.Tname='张三'
ORDER BY a.score DESC 
LIMIT 1

--------------------------------------------------------------------------------------------------------------

## ★第34题：成绩有重复的情况下，查询选修 张三 老师所授课程的学生中，成绩最高的学生信息和成绩
### 分析：成绩有重复时，如果有并列第一的时候需要把并列的都查出来，做排名，把排名为1的查出来
### 关键点：变量做排名 ，别名在where中无法使用，要用别名就需要在外面再套一层，别名再having中是可以使用的
### 第一步：找到 张三 关联的数据并排序
SELECT a.SId,b.Sname,c.Cname,a.score from sc a
LEFT JOIN student b ON a.SId=b.SId
LEFT JOIN course c ON a.CId=c.CId
LEFT JOIN teacher d on c.TId=d.TId
WHERE d.Tname='张三'
ORDER BY a.score DESC
### 第二步：加入排名
SELECT
a.*,
CASE  WHEN @sco=a.score THEN @rank 
			WHEN @sco:=a.score THEN @rank:=@rank+1
END AS rank
FROM
(
	SELECT a.SId,b.Sname,c.Cname,a.score from sc a
	LEFT JOIN student b ON a.SId=b.SId
	LEFT JOIN course c ON a.CId=c.CId
	LEFT JOIN teacher d on c.TId=d.TId
	WHERE d.Tname='张三'
	ORDER BY a.score DESC
) a,(SELECT @sco:=null,@rank:=0) b
### 第三步：找到排名为 1 的数据
SELECT * FROM
(
	SELECT
	a.*,
	CASE  WHEN @sco=a.score THEN @rank 
				WHEN @sco:=a.score THEN @rank:=@rank+1
	END AS rank
	FROM
	(
		SELECT a.SId,b.Sname,c.Cname,a.score from sc a
		LEFT JOIN student b ON a.SId=b.SId
		LEFT JOIN course c ON a.CId=c.CId
		LEFT JOIN teacher d on c.TId=d.TId
		WHERE d.Tname='张三'
		ORDER BY a.score DESC
	) a,(SELECT @sco:=null,@rank:=0) b
) a
WHERE a.rank=1

--------------------------------------------------------------------------------------------------------------

## 第35题：查询不同课程成绩相同的学生的编号，课程编号，学生成绩
### 分析：对于某一个学生来说如果他的课程中有分数相同的就把这个学生挑出来
### 关键点：条件关联筛选
SELECT a.* from sc a
INNER JOIN sc b ON a.SId=b.SId
WHERE a.CId!=b.Cid and a.score=b.score
GROUP BY a.SId,a.CId

--------------------------------------------------------------------------------------------------------------

## ★第36题：查询每门课程成绩最好的前两名
### 分析：首先通过课程id和成绩排名，然后通过变量给结果按照课程排名，最后筛选排名<3 的
### 关键点：变量 ORDER BY  IF
### 第一步：按照CId升序和分数降序排序，并定义3个变量@sco 分数，@rank排序 @c_cid 课程id
SELECT * 
from sc a,
(SELECT @sco:=null,@rank:=0,@c_cid:=null) b
ORDER BY a.CId,a.score DESC
### 第二步：得到所有门课程的成绩排序
### @rank:=if(@c_cid=a.CId,(if(@sco=a.score,@rank,@rank+1)),1) 
### 首先判断当前的课程id是不是定义课程id相同，如果不同说明是新的课程排名从1开始直接返回1
### 如果相同再判断分数是不是与上一条的分数一样，一样名次不变，不一样名此次+1
### 最后：再把名次重新复制为变量@rank
SELECT 
	a.*,
	@rank:=if(@c_cid=a.CId,(if(@sco=a.score,@rank,@rank+1)),1) AS rank,
	@sco:=a.score,
	@c_cid:=a.CId
from sc a,
(SELECT @sco:=null,@rank:=0,@c_cid:=null) b
ORDER BY a.CId,a.score DESC
### 第三步：再套一层获取名次<3 的内容就是前2名
SELECT * FROM
(
	SELECT 
		a.*,
		@rank:=if(@c_cid=a.CId,(if(@sco=a.score,@rank,@rank+1)),1) AS rank,
		@sco:=a.score,
		@c_cid:=a.CId
	from sc a,
	(SELECT @sco:=null,@rank:=0,@c_cid:=null) b
	ORDER BY a.CId,a.score DESC
) a
WHERE a.rank<3

--------------------------------------------------------------------------------------------------------------

## 第37题：统计每门课程的学生选修人数（超过5人的课程才统计）
### 分析：
### 关键点：GROUP BY HAVING
SELECT CId,COUNT(1) AS count_ from sc GROUP BY CId HAVING count_>5;

--------------------------------------------------------------------------------------------------------------

## 第38题：检索至少选修2门课程的学生学号
### 分析：通过学生编号分组
### 关键点：GROUP BY    HAVING
SELECT *,COUNT(1) as count_ from sc GROUP BY SId HAVING count_>=2
SELECT * FROM student a WHERE a.SId in(SELECT SId  from sc GROUP BY SId HAVING count(1)>=2);
--------------------------------------------------------------------------------------------------------------

## 第39题：查询选修了所有课程的学生
### 分析：查询学生表，条件是学生的成绩数量和课程表数量一致
### 关键点：条件笛卡尔积（效率不高）
SELECT * from student a
WHERE 
	(SELECT count(1) from sc b WHERE a.SId=b.SId)
	=
	(SELECT count(1) from course)
### 改进
SELECT *  from sc a
INNER JOIN student b ON a.SId=b.SId
GROUP BY a.SId
HAVING COUNT(1)=(SELECT count(1) from course)

--------------------------------------------------------------------------------------------------------------

## 第40题：查询哥学生的年龄，只按年份来算
### 分析：year函数的使用
### 关键点：year,NOW,CURDATE函数
### 第一步：year函数的使用
SELECT year('2020-11-11 11:11'), NOW(),CURDATE();
### 第二步：通过年份相减获取年龄
SELECT *, year(now())-year(Sage) as age from student


--------------------------------------------------------------------------------------------------------------

## 第41题：按照出生日期来算年龄，当前月日 < 出生年月的月日则，年龄减 1
### 分析：
### 关键点：TIMESTAMPDIFF
### 第一步：TIMESTAMPDIFF（开始日期减去结束日期的值，返回值与第一个参数定义的单位有关系） 函数的使用 ,参数1 单位；参数2  开始日期；参数3：结束日期
SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2003-04-01');
SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2003-05-01');
SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2003-06-01 11:11');
### 第二步：
SELECT *, TIMESTAMPDIFF(YEAR,Sage,NOW()) as age, Sage from student


--------------------------------------------------------------------------------------------------------------

## 第42题：查询出本周过生日的学生
### 分析：
### 关键点：week 的使用
### 第一步：week函数：表示这个日期在本年属于第几周
SELECT WEEK(NOW());
### 第二步：通过week对比作为条件查询
SELECT * ,WEEK(NOW()) FROM student 
WHERE WEEK(Sage)=WEEK('2020-12-21');

--------------------------------------------------------------------------------------------------------------

## 第43题：查询出下周过生日的学生
### 分析：
### 关键点：week 的使用
### 第一步：week函数：表示这个日期在本年属于第几周
SELECT WEEK(NOW());
### 第二步：通过week对比作为条件查询
SELECT * ,WEEK(NOW()) FROM student 
WHERE WEEK(Sage)=WEEK('2020-12-21')+1;


--------------------------------------------------------------------------------------------------------------

## 第44题：查询本月过生日的学生
### 分析：日期函数month做条件查询
### 关键点：month
### 第一步：month 函数
SELECT month(NOW())
SELECT * from student WHERE month('2021-12-12')=MONTH(Sage)

--------------------------------------------------------------------------------------------------------------

## 第45题：查询下个月过生日的学生
### 分析：日期函数month做条件查询
### 关键点：month  if
### 第一步：month 函数 if 判断12月份的特殊情况
SELECT month(NOW());
SELECT if(month(NOW())=12,1,month(NOW())+1);
###  第二步：通过条件查找
SELECT * from student WHERE if(month(NOW())=12,1,month(NOW())+1)=MONTH(Sage)

--------------------------------------------------------------------------------------------------------------
完结
MySQL经典45题
qq_40980325的博客
07-21 827
练习数据 数据表 –1.学生表 Student(SId,Sname,Sage,Ssex)–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 –2.课程表 Course(CId,Cname,TId) --CId --课程编号,Cname 课程名称,TId 教师编号 –3.教师表 Teacher(TId,Tname) --TId 教师编号,Tname 教师姓名 –4.成绩表 SC(SId,CId,score) --SId 学生编号,CId 课程编号,score 分数 创建测试数据
sql之sql经典45题_mysql
mmknzb的博客
11-17 949
sql之mysql45题建表+数据建表语句增加数据45题个人认为第11,12,13,18,20,26,38,42题比较有意思1.查询“001”课程比“002”课程成绩高的所有学生的学号;2.查询平均成绩大于60分的同学的学号和平均成绩;3.查询所有同学的学号、姓名、选课数、总成绩;4.查询姓“李”的老师的个数;5.查询没学过“叶平”老师课的同学的学号、姓名;6.查询学过“001”并且也学过编号“0...
MySQL查询语句的45道练习
acxl0372的博客
03-17 1853
一、设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表（一）~表（四）所示。用SQL语句创建四个表并完成相关题目。 表1-1数据库的表结构 表（一）Student (学...
常见MySQL语句学习+练习
最新发布
m0_74907886的博客
05-14 443
说明：本文章个人总结，用来练习学习和练习mysql语句。
MySQL45道经典题（持续更新）
qq_43641035的博客
06-22 906
基本表： student表 sc表 course表 teacher表 1.查询01课程比02课程成绩高的学生的信息及课程分数 SELECT * FROM student a LEFT JOIN sc b ON a.sid=b.sid INNER JOIN sc c ON b.sid=c.sid AND b.cid!=c.cid WHERE b.cid='01' AND c.cid='02' AND b.score>c.score; 1.1查询同时存在01课程和02课
mysql查询45题_MySQL经典45题
weixin_39997400的博客
02-05 167
创建表CREATE TABLE students(sno VARCHAR(3) NOT NULL,sname VARCHAR(4) NOT NULL,ssex VARCHAR(2) NOT NULL,sbirthday DATETIME,class VARCHAR(5));CREATE TABLE courses(cno VARCHAR(5) NOT NULL,cname VARCHAR(10) ...
MySql45道练习题
qidao12的博客
01-17 2818
一、设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表（一）~表（四）所示。用SQL语句创建四个表并完成相关题目。                  表1-1数据库的表结构 表（一）Student (学生表)  属性
Mysql经典40道面试题
03-28
45、MySQL驱动程序是连接应用程序与MySQL服务器的中间件，例如Java的JDBC驱动，Python的pymysql等。 46、TIMESTAMP类型在UPDATE CURRENT_TIMESTAMP设置下，会在每次更新记录时自动设置为当前时间。 47、主键是唯一...
MySQL 45 道面试题及答案.docx
07-07
MySQL 面试题汇总 MySQL 是一种关系型数据库管理系统，广泛应用于各种行业和领域。以下是 MySQL 面试题汇总，涵盖了 MySQL 的基础知识、视图、存储过程、触发器、索引、关联查询等方面。 视图 视图是一个虚拟的表...
mysql 45题 答案_MySQL经典45题（一）
weixin_28753113的博客
01-18 730
1、学生表 Student(SId,Sname,Sage,Ssex)SId ：学生编号Sname：学生姓名Sage ：出生年月Ssex：学生性别2、课程表 Course(CId,Cname,TId)CId ：课程编号Cname ：课程名称TId ：教师编号3、教师表 Teacher(TId,Tname)TId ：教师编号Tname ：教师姓名4、成绩表 SC(SId,CId,score)SId ：...
MySQL45道面试题及答案
11-27
以下是一些基于给定面试题的MySQL知识点： 1. **创建数据库和切换数据库**： 使用`CREATE DATABASE`语句可以创建一个新的数据库，如`CREATE DATABASE school;`，而`USE`语句则用于切换到指定的数据库，如`USE ...
MySQL经典练习50题
08-08
MySQL经典题库50题，无答案。MySQL经典题库50题，无答案。
mysql经典50题
04-01
mysql经典50题。以及注释，经过实战运行及改善，可以直接用。挺有用的，对于sql入门及网申笔试来说。
【一日速撕sql】MySql经典45题（含答案及解析过程）
weixin_44594082的博客
07-31 2157
MySql 班级，学生，课程经典45题
Mysql经典45道题(详解1-15)
热门推荐
Hudas的博客
02-10 1万+
Mysql经典45道题题解(1-15)
mysql45经典题
tangerine_的博客
01-07 737
测试表格 –1.学生表 Student(S#,Sname,Sage,Ssex)  –S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 –2.课程表  Course(C#,Cname,T#)  –C# –课程编号,Cname 课程名称,T# 教师编号 –3.教师表  Teacher(T#,Tname)  –T# 教师编号,Tname 教师姓名 –4.成绩...
mysql经典45道题_MYSQL数据库45道练习题
weixin_35301706的博客
01-28 784
--第一题查询Student表中的所有记录的Sname、Ssex和Class列。select Sname,Ssex,Class from Student;--第二题查询教师所有的单位即不重复的Depart列。select distinct Depart from Teacher;--第三题、 查询Student表的所有记录select * from Student;--第四题、 查询Score表中...
SQL经典练习题47道（简单）
HHHBan的博客
11-18 584
【代码】SQL经典练习题47道（简单）
MySQL---45道练习题解答
2302_79994252的博客
01-18 408
1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数 2.查询同时存在" 01 "课程和" 02 "课程的情况 3.查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null ) 4.查询不存在" 01 "课程但存在" 02 "课程的情况 5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩 7.查询在 SC 表存在成绩的学生信息,查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为
mysql50道经典面试题
10-19
MySQL是一种关系型数据库管理系统，常用于Web应用程序的后台数据管理。以下是MySQL的50道经典面试题： 1. 什么是MySQL？ 2. MySQL的优点是什么？ 3. MySQL的数据类型有哪些？ 4. MySQL中的存储引擎有哪些？ 5. 什么是索引？MySQL中的索引有哪些类型？ 6. 什么是主键？如何设置主键？ 7. 什么是外键？如何设置外键？ 8. 什么是事务？MySQL中如何使用事务？ 9. 什么是视图？MySQL中如何创建视图？ 10. 什么是存储过程？MySQL中如何创建存储过程？ 11. 什么是触发器？MySQL中如何创建触发器？ 12. 什么是游标？MySQL中如何使用游标？ 13. 什么是连接？MySQL中如何进行连接操作？ 14. 什么是子查询？MySQL中如何使用子查询？ 15. 什么是临时表？MySQL中如何创建临时表？ 16. 什么是备份？MySQL中如何进行备份操作？ 17. 什么是恢复？MySQL中如何进行恢复操作？ 18. 什么是复制？MySQL中如何进行复制操作？ 19. 什么是分区表？MySQL中如何创建分区表？ 20. 什么是分布式数据库？MySQL中如何实现分布式数据库？ 21. 什么是索引覆盖？MySQL中如何实现索引覆盖？ 22. 什么是慢查询？MySQL中如何优化慢查询？ 23. 什么是死锁？MySQL中如何避免死锁？ 24. 什么是优化器？MySQL中如何使用优化器？ 25. 什么是explain？MySQL中如何使用explain？ 26. 什么是锁？MySQL中有哪些锁？ 27. 什么是MyISAM？MySQL中如何使用MyISAM？ 28. 什么是InnoDB？MySQL中如何使用InnoDB？ 29. 什么是Memory？MySQL中如何使用Memory？ 30. 什么是CSV？MySQL中如何使用CSV？ 31. 什么是Blackhole？MySQL中如何使用Blackhole？ 32. 什么是Federated？MySQL中如何使用Federated？ 33. 什么是Merge？MySQL中如何使用Merge？ 34. 什么是Archive？MySQL中如何使用Archive？ 35. 什么是NDB？MySQL中如何使用NDB？ 36. 什么是Cluster？MySQL中如何使用Cluster？ 37. MySQL是如何保证主备同步的？ 38. 什么是binlog？MySQL中如何使用binlog？ 39. 什么是redo log？MySQL中如何使用redo log？ 40. 什么是undo log？MySQL中如何使用undo log？ 41. 什么是事务日志？MySQL中如何使用事务日志？ 42. 什么是XA事务？MySQL中如何使用XA事务？ 43. 什么是XA协议？MySQL中如何使用XA协议？ 44. 什么是XA事务管理器？MySQL中如何使用XA事务管理器？ 45. 什么是XA资源管理器？MySQL中如何使用XA资源管理器？ 46. 什么是XA事务的两阶段提交？MySQL中如何使用XA事务的两阶段提交？ 47. 什么是MySQL的锁机制？MySQL中有哪些锁？ 48. 什么是表锁？MySQL中如何使用表锁？ 49. 什么是行锁？MySQL中如何使用行锁？ 50. 什么是间隙锁？MySQL中如何使用间隙锁？
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值