MYSQL突击

已于 2023-03-08 19:57:19 修改
阅读量303 收藏
点赞数
分类专栏： mysql 文章标签： java 数据结构 Powered by 金山文档
于 2023-03-07 23:34:08 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_61058262/article/details/129394254
版权

创建学生表

#Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
CREATE TABLE `Student` (
    `s_id` VARCHAR(20),
    s_name VARCHAR(20) NOT NULL DEFAULT '',
    s_brith VARCHAR(20) NOT NULL DEFAULT '',
    s_sex VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(s_id)
);

创建课程表

#Course(c_id,c_name,t_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)
);

创建教师表

#Teacher(t_id,t_name) –教师编号,教师姓名 
CREATE TABLE Teacher(
    t_id VARCHAR(20),
    t_name VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(t_id)
);

创建成绩表

#Score(s_id,c_id,s_score) –学生编号,课程编号,分数
Create table Score(
    s_id VARCHAR(20),
    c_id VARCHAR(20) not null default '',
    s_score INT(3),
    primary key(`s_id`,`c_id`)
);

添加数据

#--插入学生表测试数据#('01' , '赵雷' , '1990-01-01' , '男')
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 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

-- 分析题目关键字: 学生信息 课程分数  课程编号

-- 第一步:锁定使用表  学生表 分数表 这两张表能提供题目所需

-- 第二步:通过s_id关联 学生表和分数表
SELECT
	*
FROM
	student a
INNER JOIN score b ON a.s_id = b.s_id

-- 第三步:进行同一学生不同成绩比较,那么就需要在关联一次成绩表,利用s_id相同,c_id不同进行关联
SELECT
	*
FROM
	student a
INNER JOIN score b ON a.s_id = b.s_id
INNER JOIN score c ON a.s_id = c.s_id
AND b.c_id = "01"
AND c.c_id = "02"

-- 第四步:最终回归到题目,只需要筛选一下课程,利用where条件比较分数即可
SELECT
	*
FROM
	student a
INNER JOIN score b ON a.s_id = b.s_id
INNER JOIN score c ON a.s_id = c.s_id
AND b.c_id = "01"
AND c.c_id = "02"
WHERE
	b.s_score > c.s_score

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

-- 分析题目关键字, 学生编号 学生姓名 平均成绩>60

-- 第一步:锁定使用表  学生表 成绩表 这两张表提供题目所需信息

-- 第二步:成绩表按照s_id聚合 获取平均成绩,并且筛选出平均成绩大于等于60的s_id 和平均成绩
select
s_id,
avg(s_score) avg_score
from score 
GROUP BY s_id
HAVING avg_score>60

-- 第三步:关联学生表 获取学生姓名
select 
*
from student a RIGHT  JOIN (select
s_id,
avg(s_score) avg_score
from score 
GROUP BY s_id
HAVING avg_score>60) b
ON a.s_id = b.s_id

3 查询在score表存在成绩的学生信息

-- 分析题目关键字 score表 成绩 学生信息

-- 第一步: 锁定表 学生表 成绩表

-- 第二步: 以成绩表为主,左关联学生表 连接主键s_id 使用 left join 关键就在于去重处理,否则会有多条重复记录
 select DISTINCT(s_id) from score
 
-- 第三步:使用 distinct 从成绩表中获取唯一sid 左连接学生表  
select 
*
from (select DISTINCT(s_id) from score ) a LEFT JOIN student b
on a.s_id = b.s_id

4 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

-- 分析题目关键字 学生信息 选课总述 所有课程总成绩

-- 第一步:锁定表 学生表 成绩表

-- 第二步:在score表中对s_id进行聚合,获取每个学生的选课总数 所有课程的总成绩

select
s_id,
count(1) as ct,
SUM(s_score) total
from score
GROUP BY s_id

-- 第三步:学生表为主表,与第二步得到的成绩汇总表进行左关联查询
select
a.s_id,
a.s_name,
b.ct,
b.total
from student a  LEFT JOIN 
(select
s_id,
count(1) as ct,
SUM(s_score) total
from score
GROUP BY s_id) b 
on a.s_id = b.s_id

4.1 查询有成绩的学生信息

-- 第一步:锁定表 学生表 成绩表

-- 第二步:筛选出有成绩的s_id 并用group_by /distinct 分组去重
SELECT 
DISTINCT(s_id)
FROM
score

-- 第三步:利用有成绩的s_id 在 student表 做筛选
SELECT
*
from student
where s_id in(SELECT 
DISTINCT(s_id)
FROM
score)

5 查询"李"姓老师的数量

-- 分析题目关键字 老师 李  数量
-- 第一步:锁定表 教师表
-- 第二步:筛选出 李 姓老师 (提示: 使用like关键字 % 表示占位符)
select 
*
from
teacher
WHERE
t_name like "李%"
-- 第三步:统计 李 姓老师 老师的数量
select 
 count(*) 数量
from
teacher
WHERE
t_name like "李%"

6 查询学过 张三 老师授课的同学的信息

-- 分析题目关键字 教师姓名  学生信息

-- 第一步:锁定表 首先学生表 与 教师表没有直接关联 此时就需要通过课程表 成绩表 进行间接关联

-- 第二步:关联课程表和教师表 得到课程c_id 与 教师 t_name 的关系
SELECT 
a.*,
b.t_name
FROM
course a ,teacher b 
WHERE
a.t_id = b.t_id

-- 第三步:与成绩表关联,得到学生s_id 与教师 t_name的关系
SELECT
a.*,b.t_name
from
score a INNER JOIN (SELECT 
a.*,
b.t_name
FROM
course a ,teacher b 
WHERE
a.t_id = b.t_id) b
on a.c_id = b.c_id

-- 第四步:与学生表关联,获取学生信息
SELECT
a.* ,b.t_name
FROM student a INNER JOIN 
(SELECT
a.*,b.t_name
from
score a INNER JOIN (SELECT 
a.*,
b.t_name
FROM
course a ,teacher b 
WHERE
a.t_id = b.t_id) b
on a.c_id = b.c_id) b 
ON a.s_id = b.s_id

-- 第五步:增加筛选条件,t_name 为 张三
SELECT
a.* ,b.t_name
FROM student a INNER JOIN 
(SELECT
a.*,b.t_name
from
score a INNER JOIN (SELECT 
a.*,
b.t_name
FROM
course a ,teacher b 
WHERE
a.t_id = b.t_id) b
on a.c_id = b.c_id) b 
ON a.s_id = b.s_id
WHERE b.t_name = "张三"

7 查询没有学全所有课程的同学的信息

-- 分析题目关键字 所有课程数   学生信息

-- 第一步:锁定表 首先学生表 成绩表 课程表

-- 第二步:查询出所有课程的课程数
select count(1) from course

-- 第三步:在成绩表中获取每一名s_id 的课程数 并筛选出所学课程小于总课程数的s_id
select 
s_id,
count(1) ct
from score 
GROUP BY s_id
HAVING ct < (select count(1) from course)

-- 第四步:根据s_id与学生表进行关联获取学生信息
select 
*
from student a RIGHT JOIN 
(select 
s_id,
count(1) ct
from score 
GROUP BY s_id
HAVING ct < (select count(1) from course)) b
ON a.s_id = b.s_id

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

-- 分析题目关键字 01 同学所学课程   学生信息

-- 第一步: 锁定表 学生表 成绩表

-- 第二步:根据成绩表 获取01同学所学的课程编号
select c_id FROM score where s_id = "01"

-- 第三步:使用 in 获取课程表在 01 同学 所学课程编号范围内的记录
SELECT
*
FROM
score
where 
c_id in (select c_id FROM score where s_id = "01")

-- 第四步:关联学生表,获取学生信息 并用distinct去重
SELECT
DISTINCT a.*
from student a INNER JOIN
(SELECT
*
FROM
score
where 
c_id in (select c_id FROM score where s_id = "01")) b
ON a.s_id = b.s_id

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

-- 分析题目关键字  01同学学习课程的信息 完全相同 学生信息
		-- 完全相同 有 2层含义  
		    -- 第一层:没有学习01号同学学习课程以外的其他课程 
            -- 第二层:与01号同学学习课程数量相等
            
-- 第一步:锁定表 学生表 成绩表

-- 第二步: 先获取出 学了01号同学学习课程以外其他课程的同学 并使用not in 过滤
SELECT  s_id from score WHERE c_id not in (SELECT c_id from score where s_id = "01")

-- 第三步:利用反向思维 排除第二步找到的同学以及01号同学
SELECT s_id from score
where s_id not in (SELECT  s_id from score WHERE c_id not in (SELECT c_id from score where s_id = "01"))
AND s_id != "01" GROUP BY s_id

-- 第四步: 筛选出与 01 号同学所学课程数量相同的同学 
SELECT s_id from score
where s_id not in (SELECT  s_id from score WHERE c_id not in (SELECT c_id from score where s_id = "01"))
AND s_id != "01" 
GROUP BY s_id
HAVING COUNT(c_id) = (SELECT count(*) from score where s_id = "01")

-- 第五步:关联学生表,获取学生信息
select
b.*
from (SELECT s_id from score
where s_id not in (SELECT  s_id from score WHERE c_id not in (SELECT c_id from score where s_id = "01"))
AND s_id != "01" 
GROUP BY s_id
HAVING COUNT(c_id) = (SELECT count(*) from score where s_id = "01")) a  INNER JOIN  student b 
ON a.s_id = b.s_id

10 查询没学过"张三"老师授课的任一门课程的同学信息

-- 分析题目关键字 由于教师表与学生表没有直接关联所有需要成绩表与课程包进行关联 四表关联

-- 第一步: 锁定表 4张表

-- 第二步: 关联成绩表,获取学过张三老师课程的s_id
select * from
  score a LEFT JOIN course b ON a.c_id = b.c_id
	INNER JOIN teacher c ON b.t_id = c.t_id 
WHERE c.t_name = '张三'

-- 第三步: 关联学生表,使用not in 筛选出没有学过张三 老师课程的sid
select
*
from student a 
WHERE a.s_id not in (select s_id from
  score a LEFT JOIN course b ON a.c_id = b.c_id
	INNER JOIN teacher c ON b.t_id = c.t_id 
WHERE c.t_name = '张三')

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

-- 分析题目关键字 两门及以上 学生学号和姓名  平均成绩

-- 第一步: 锁定表 学生表 成绩表

-- 第二步:求出学生的 平均成绩
select s_id, AVG(s_score) avg_score from score GROUP BY s_id  

-- 第三步:找出有两门及以上不及格的同学
select 
s_id from score
WHERE s_score < 60 
GROUP BY s_id
HAVING count(c_id)>=2

-- 第四步:结合第二步和第三步 求出两门及以上不及格课程的同学的平均分
select 
a.s_id ,avg(s_score) avg_score
from score a INNER JOIN (select 
s_id from score
WHERE s_score < 60 
GROUP BY s_id
HAVING count(c_id)>=2) b 
on a.s_id = b.s_id
GROUP BY  a.s_id

-- 第五步:关联学生表,获取学生姓名
select 
a.s_name,a.s_id,avg_score
from student a INNER JOIN (select 
a.s_id ,avg(s_score) avg_score
from score a INNER JOIN (select 
s_id from score
WHERE s_score < 60 
GROUP BY s_id
HAVING count(c_id)>=2) b 
on a.s_id = b.s_id
GROUP BY  a.s_id) b
ON a.s_id = b.s_id

12 检索"01"课程分数小于60，按分数降序排列的学生信息

-- 分析题目关键字 

-- 第一步: 锁定表 成绩表 学生表

-- 第二步: 查询01课程小于60的s_id
select s_id,s_score from score where c_id = '01' AND s_score < 60

-- 第三步: 关联学生表,获取学生信息,并且按照分数降序排列
select
a.*,b.s_score
from student a INNER JOIN (select s_id,s_score from score where c_id = '01' AND s_score < 60) b
ON a.s_id = b.s_id ORDER BY s_score DESC

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

-- 分析题目关键字 所有学生 所有课程 平均成绩 由于排序时最后执行,所以本题目最大的误区就是先获取平均成绩

-- 第一步: 锁定表 学生表 成绩表

-- 第二步: 获取所有学生的所有课程成绩(学生表左连接成绩表,没有成绩的显示为null)
select 
a.s_id,b.c_id,b.s_score
from student a LEFT JOIN score b 
ON a.s_id = b.s_id

-- 第三步: 求出学生的平均成绩
select
s_id,AVG(s_score) avg_scroe
from score
GROUP BY s_id

-- 第四步:给每条成绩记录加上平均成绩,最终按照平均成绩降序排列
select 
*
from 
(select 
a.s_id,b.c_id,b.s_score
from student a LEFT JOIN score b 
ON a.s_id = b.s_id) m
LEFT JOIN 
(select
s_id,AVG(s_score) avg_scroe
from score
GROUP BY s_id) n 
ON m.s_id = n.s_id 
ORDER BY n.avg_scroe DESC

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

-- 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
-- 要求输出课程号和选修人数,查询结果按人数排序排列,若人数相同,按课程号升序排列
-- 分析题目关键字: 课程号,课程名称 聚合计算(选修人数,最高分,最低分,平均分,及格率,中等率,优良率,优秀率) 排序

-- 第一步: 锁定表 课程表 成绩表

-- 第二步:聚合计算(使用提交判断语句 case when)
select
c_id,
count(*) 选修人数,
max(s_score) 最高分,
min(s_score) 最低分,
avg(s_score) 平均分,
sum(case when s_score>=60 then 1 else 0 end)/count(*) 及格率,
sum(case when s_score>=70 AND s_score<80 then 1 else 0 end) /count(*) 中等率,
sum(case when s_score>=80 and s_score<90 then 1 else 0 end) /count(*) 优良率,
sum(case when s_score>=90 then 1 else 0 end )/count(*) 优秀率
from
score
GROUP BY c_id 

-- 第三步: 排序(按照选修人数降序 课程号升序)
select
c_id,
count(*) 选修人数,
max(s_score) 最高分,
min(s_score) 最低分,
avg(s_score) 平均分,
sum(case when s_score>=60 then 1 else 0 end)/count(*) 及格率,
sum(case when s_score>=70 AND s_score<80 then 1 else 0 end) /count(*) 中等率,
sum(case when s_score>=80 and s_score<90 then 1 else 0 end) /count(*) 优良率,
sum(case when s_score>=90 then 1 else 0 end )/count(*) 优秀率
from
score
GROUP BY c_id 
ORDER BY count(*),c_id asc

-- 第四步:关联课程表 输出课程名称
select 
a.*,
b.c_name
from
(select
c_id,
count(*) 选修人数,
max(s_score) 最高分,
min(s_score) 最低分,
avg(s_score) 平均分,
sum(case when s_score>=60 then 1 else 0 end)/count(*) 及格率,
sum(case when s_score>=70 AND s_score<80 then 1 else 0 end) /count(*) 中等率,
sum(case when s_score>=80 and s_score<90 then 1 else 0 end) /count(*) 优良率,
sum(case when s_score>=90 then 1 else 0 end )/count(*) 优秀率
from
score
GROUP BY c_id 
ORDER BY count(*),c_id asc) a LEFT  JOIN course b 
ON a.c_id = b.c_id

15 按各科成绩进行排序，并显示排名,score重复时,也继续排名

窗口函数就是在满足某种提交的记录集合上执行的特殊函数,按照功能划分,可以把mysql支持的窗口函数分为如下几类:

名称

方法

序号函数

row_number() / rank()/dense_rank() 主要解决排序/排名

分布函数

percent_rank() /cume_dist()

前后函数

lag() /lead()

头尾函数

first_val() /last_val()

其他函数

nth_value() /nfile()

 -- 按各科成绩进行排序，并显示排名,score重复时,也继续排名
SELECT
*,row_number() over (partition  by c_id order by s_score desc) as 排名
from score

    -- 按各科成绩进行排序,并显示排名,score 重复时,合并名次
SELECT
*,dense_rank() over (partition  by c_id order by s_score desc) as 排名
from score

-- row_number() rank() dense_rank()区别
-- (1) row_number:依次排序,不会出现相同排名
-- (2) rank():出现相同的排名时,跳跃排序
-- (3) dense_rank():出现相同排序时,连续排序

16 查询学生的总成绩并进行排名 总分重复时保留名词空缺

-- 分析题目关键字 总成绩  保留名词空缺

-- 第一步:计算总成绩
select 
s_id,
sum(s_score) as sum_score
from score 
GROUP BY s_id

-- 第二步:按照总成绩进行排序(排名跳跃)
select
*,
rank() over(ORDER BY sum_score) as 排名
from (select 
s_id,
sum(s_score) as sum_score
from score 
GROUP BY s_id) a 


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

-- 分析题目关键字 总成绩  保留名词空缺

-- 第一步:计算总成绩

-- 第二步:按照总成绩进行排序(排名连续)
select
*,
dense_rank() over(ORDER BY sum_score) as 排名
from (select 
s_id,
sum(s_score) as sum_score
from score 
GROUP BY s_id) a

17 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

-- 分析题目关键字 

-- 第一步:锁定表 成绩表 课程表

-- 第二步:使用case when ,按照课程编号统计各个分数段的人数
select
c_id,
sum(case when s_score>=0 and s_score<60 then 1 else 0 end ) '[0-60]',
sum(case when s_score>=60 and s_score<70 then 1 else 0 end ) '[60-70]',
sum(case when s_score>=70 and s_score<85 then 1 else 0 end ) '[70-85]',
sum(case when s_score>=85 and s_score<=100 then 1 else 0 end ) '[100-85]'
from 
score
GROUP BY c_id

-- 第三步:转化成所占百分比
select
c_id,
sum(case when s_score>=0 and s_score<60 then 1 else 0 end )/count(*) '[0-60]',
sum(case when s_score>=60 and s_score<70 then 1 else 0 end )/count(*)  '[60-70]',
sum(case when s_score>=70 and s_score<85 then 1 else 0 end )/count(*)  '[70-85]',
sum(case when s_score>=85 and s_score<=100 then 1 else 0 end )/count(*)  '[100-85]'
from 
score
GROUP BY c_id

-- 第四步:使用concat拼接 输出百分比符号
select
c_id,
concat(sum(case when s_score>=0 and s_score<60 then 1 else 0 end )/count(*)*100,'%') '[0-60]',
concat(sum(case when s_score>=60 and s_score<70 then 1 else 0 end )/count(*)*100,'%')  '[60-70]',
concat(sum(case when s_score>=70 and s_score<85 then 1 else 0 end )/count(*)*100,'%')  '[70-85]',
concat(sum(case when s_score>=85 and s_score<=100 then 1 else 0 end )/count(*)*100,'%')  '[100-85]'
from 
score
GROUP BY c_id

-- 第五步:关联课程表,输出课程名称
select 
*
from
course a LEFT JOIN (select
c_id,
concat(sum(case when s_score>=0 and s_score<60 then 1 else 0 end )/count(*)*100,'%') '[0-60]',
concat(sum(case when s_score>=60 and s_score<70 then 1 else 0 end )/count(*)*100,'%')  '[60-70]',
concat(sum(case when s_score>=70 and s_score<85 then 1 else 0 end )/count(*)*100,'%')  '[70-85]',
concat(sum(case when s_score>=85 and s_score<=100 then 1 else 0 end )/count(*)*100,'%')  '[100-85]'
from 
score
GROUP BY c_id) b
ON a.c_id = b.c_id

18 查询各科成绩前三名的记录

-- 分析题目关键字 借助于窗口函数 各科成绩 前三名
-- 第一步:锁定表 成绩表
-- 第二步:根据成绩进行排名
select 
*,
dense_rank() over(PARTITION by c_id order by s_score desc) as socre_rank
from score
-- 第三步: 筛选出前三名 只要是socre_rank 小于等于3的记录就行
select
*
from (select 
*,
dense_rank() over(PARTITION by c_id order by s_score desc) as socre_rank
from score) a 
WHERE socre_rank<=3

19 查询每门课程被选修的学生数

-- 分析题目关键字 每门课程 学生数

-- 第一步:锁定表 成绩表

-- 第二步:按照课程编号进行分组统计
select 
c_id,
count(s_id) 学生数
from
score
GROUP BY c_id

20 查询出只有两门课程的全部学生的学号和姓名

-- 分析题目关键字 两门课程 学生编号和姓名

-- 第一步:锁定表 成绩表 学生表

-- 第二步:统计出每个学生选修的课程数 并筛选出只选修2门课程的学生s_id
select
s_id,
count(c_id) cnum
FROM score
GROUP BY s_id
HAVING cnum =2
-- 第三步:关联学生表,输出学生姓名
select 
a.s_name,
b.cnum
from student a RIGHT  JOIN (select
s_id,
count(c_id) cnum
FROM score
GROUP BY s_id
HAVING cnum =2) b
ON a.s_id = b.s_id

21 查询男生、女生人数

-- 分析题目关键字  不同性别  学生数
-- 第一步:锁定表 学生表
-- 第二步:按照性别进行分组统计
select 
s_sex,
count(1) "人数"
from student
GROUP BY s_sex

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

-- 分析题目关键字 包含风字(字符串匹配问题) 学生信息

-- 第一步:锁定表 学生表

-- 第二步:使用 like 操作符 %作为占位符
select 
*
from student
where s_name like '%风%'

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

-- 分析题目关键字  同名同性 人数 

-- 第一步:锁定表 学生表

-- 第二步: 自连接查询,筛选出名字,性别相同但是学号不同的信息
select
*
from
student a INNER JOIN student b
ON a.s_name = b.s_name AND a.s_sex = b.s_sex AND a.s_id != b.s_id

-- 第三步:统计同名同性的人数
select 
a.s_name,
a.s_sex,
count(*) as ct
from(select
a.*
from
student a INNER JOIN student b
ON a.s_name = b.s_name AND a.s_sex = b.s_sex AND a.s_id != b.s_id) a
GROUP BY a.s_name,a.s_sex

24 查询1990年出生的学生名单

-- 分析题目关键字 出生日期 

-- 第一步:锁定表 学生表

-- 第二步:使用year() 筛选出1990年出生的学生
select year("1990-01-01")

select 
* 
from 
student
where year(s_brith)='1990'

-- 另外一种解法:
select
*
from
student
where left(s_brith,4)="1990"

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

-- 分析题目关键字  平均成绩 排序

-- 第一步:锁定表 成绩表

-- 第二步: 计算没门课程平均成绩
select
s_id,
AVG(s_score) avg_score
from 
score
GROUP BY c_id
-- 第三步:排序
select
s_id,
AVG(s_score) avg_score
from 
score
GROUP BY c_id
ORDER BY avg_score DESC,c_id ASC

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

-- 分析题目关键字 平均成绩 学号 姓名
-- 第一步:锁定表 成绩表 学生表
-- 第二步:分组聚合求出每一名同学的平均成绩 并筛选出平均成绩大于85的s_id
SELECT
s_id,
AVG(s_score) as s_avg_score
FROM
score
GROUP BY s_id
HAVING s_avg_score >=85
-- 第三步:关联学生表 获取学生姓名
select 
a.s_name,
b.*
from  student a RIGHT JOIN 
(SELECT
s_id,
AVG(s_score) as s_avg_score
FROM
score
GROUP BY s_id
HAVING s_avg_score >=85) b 
on a.s_id = b.s_id

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

-- 分析题目关键字 数学  低于60分  学生姓名 

-- 第一步:锁定表 课程表  成绩表 学生表

-- 第二步: 现获取 课程名为数学对应的s_id 和分数
select
b.s_id,
a.c_name,
b.s_score
FROM
course a LEFT JOIN score b
on a.c_id = b.c_id
where a.c_name = "数学" AND b.s_score < 60

-- 第三步:关联学生表,获取学生姓名
select
a.s_name,
b.*
from student a RIGHT JOIN 
(select
b.s_id,
a.c_name,
b.s_score
FROM
course a LEFT JOIN score b
on a.c_id = b.c_id
where a.c_name = "数学" AND b.s_score < 60) b
ON a.s_id = b.s_id

28 查询所有学生的课程及分数情况

-- 分析题目关键字 所有学生 课程 分数

-- 第一步:锁定表 学生表 成绩表

-- 第二步:学生表左关联成绩表
select 
*
from student a LEFT JOIN score b 
on a.s_id = b.s_id

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

-- 分析题目关键字 70分以上 学生姓名 课程姓名 分数

-- 第一步:锁定表 学生表 课程表 成绩表

-- 第二步: 课程分数大于70 的 课程名名称
select
a.s_id,
b.c_name,
a.s_score
from 
score a LEFT  JOIN course b
ON a.c_id = b.c_id
where 
s_score > 70

-- 第三步:关联学生表获取学生姓名
select
a.s_name, 
b.*
from student a RIGHT JOIN 
(select
a.s_id,
b.c_name,
a.s_score
from 
score a INNER JOIN course b
ON a.c_id = b.c_id
AND
s_score > 70) b
on a.s_id = b.s_id

30 查询不及格的课程

-- 分析题目关键字 不及格 课程信息

-- 第一步:锁定表 成绩表 课程表

-- 第二步:筛选出分数小于60的课程c_id
select 
distinct(c_id)
from score
where s_score <60
-- 第三步:使用子查询,获取课程信息
select 
*
from course
where c_id in(select 
distinct(c_id)
from score
where s_score <60)

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

-- 分析题目关键字 课程编号 成绩 学生学号 学生姓名

-- 第一步:锁定表 学生表 成绩表

-- 第二步:
select 
a.*,
b.s_name
from score a LEFT JOIN student b
on a.s_id = b.s_id
where a.c_id = "01" and a.s_score >=80

32 求每门课程的学生人数

-- 分析题目关键字 每门课程 人数

-- 第一步:锁定表 成绩表

-- 第二步:使用聚合函数
select 
c_id,
count(*) "人数"
FROM
score
GROUP BY c_id

33 假设成绩不重复,查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

-- 分析题目关键字  成绩不重复 张三老师所授课程的学生  成绩最高   成绩

-- 第一步:锁定表 教师表 课程表 成绩表 学生表

-- 第二步: 关联四张表
SELECT
b.*,
s_score,
t_name
from score a LEFT JOIN student b on a.s_id = b.s_id
LEFT JOIN course c ON a.c_id = c.c_id 
LEFT JOIN teacher d on c.t_id = d.t_id

-- 第三步:添加筛选条件为 张三老师
SELECT
b.*,
s_score,
t_name
from score a LEFT JOIN student b on a.s_id = b.s_id
LEFT JOIN course c ON a.c_id = c.c_id 
LEFT JOIN teacher d on c.t_id = d.t_id
WHERE d.t_name  = "张三" 

-- 第四步 按照分数排序,只显示第一条记录(limit)
SELECT
b.*,
s_score,
t_name
from score a LEFT JOIN student b on a.s_id = b.s_id
LEFT JOIN course c ON a.c_id = c.c_id 
LEFT JOIN teacher d on c.t_id = d.t_id
WHERE d.t_name  = "张三" 
ORDER BY s_score desc limit 1

34 假设成绩有重复,查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

-- 分析题目关键字  成绩重复 张三老师所授课程的学生     成绩最高   成绩

-- 第一步:锁定表 教师表 课程表 成绩表 学生表

-- 第二步:添加筛选条件为 张三老师
SELECT
b.*,
s_score,
t_name
from score a LEFT JOIN student b on a.s_id = b.s_id
LEFT JOIN course c ON a.c_id = c.c_id 
LEFT JOIN teacher d on c.t_id = d.t_id
WHERE d.t_name  = "张三" 

-- 第三步 使用dense_rank()进行排名
SELECT
b.*,
s_score,
t_name,
dense_rank() over (order by s_score desc) as score_rank
from score a LEFT JOIN student b on a.s_id = b.s_id
LEFT JOIN course c ON a.c_id = c.c_id 
LEFT JOIN teacher d on c.t_id = d.t_id
WHERE d.t_name  = "张三" 

-- 第四步 筛选出第一名
select
* from 
(SELECT
b.*,
s_score,
t_name,
dense_rank() over (order by s_score desc) as score_rank
from score a LEFT JOIN student b on a.s_id = b.s_id
LEFT JOIN course c ON a.c_id = c.c_id 
LEFT JOIN teacher d on c.t_id = d.t_id
WHERE d.t_name  = "张三" ) a 
where score_rank = 1

35 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

-- 分析题目关键字 不同课程 成绩相同

-- 第一步:锁定表 成绩表

-- 第二步:自连接查询成绩表 链接条件是学生编号相同  课程编号不同 成绩相同
select
a.*
from
score a INNER JOIN score b
on a.s_id = b.s_id AND a.c_id != b.c_id AND a.s_score = b.s_score

-- 第三步:去重
select
DISTINCT a.*
from
score a INNER JOIN score b
on a.s_id = b.s_id AND a.c_id != b.c_id AND a.s_score = b.s_score

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

-- 分析题目关键字 每门课程  成绩最好前两名 

-- 第一步:锁定表  成绩表

-- 第二步: 典型的排名问题 在mysql8后 可以使用窗口函数轻松解决
select
*,
dense_rank() over(PARTITION by c_id order by s_score desc) score_rank
from
score

-- 第三步:筛选出前两名 score_rank 等于1 和 2的记录
SELECT
*
from (select
*,
dense_rank() over(PARTITION by c_id order by s_score desc) score_rank
from
score) a WHERE score_rank<=2

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

-- 分析题目关键字 每门课程 选修人数 超过5人才统计

-- 第一步:锁定表 成绩表

-- 第二步:分组聚合 求每门课程的选修人数
select
c_id,
COUNT(*) ct
from score
GROUP BY c_id

-- 第三步: 筛选出选修人数大于5的记录
select
c_id,
COUNT(*) ct
from score
GROUP BY c_id
HAVING ct>5

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

-- 分析题目关键字 至少  选修两门 学生学号

-- 第一步:锁定表 成绩表

-- 第二步: 分组聚合 查询 每个学生选修的课程数
SELECT
s_id,
count(*)
from score
GROUP BY s_id

-- 第三步: 添加筛选条件
SELECT
s_id,
count(*) ct
from score
GROUP BY s_id
HAVING ct>=2

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

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

-- 分析题目关键字 选修全部课程 学生信息

-- 第一步:锁定表 成绩表 学生表  课程表(统计有几门课程)

-- 第二步:查询出课程表的总课程数
select count(1) from course

-- 第三步:获取每名学生选修课程的总数
select
s_id,
count(1) ct 
from score
GROUP BY s_id

-- 第四步:筛选出学习了3门课程的学生sid
select
s_id,
count(1) ct 
from score
GROUP BY s_id
HAVING ct = (select count(1) from course)

-- 第五步:关联学生表,获取学生信息
select
*
from student a RIGHT JOIN 
(select
s_id,
count(1) ct 
from score
GROUP BY s_id
HAVING ct = (select count(1) from course)) b
ON a.s_id = b.s_id

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

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

-- 分析题目关键字 年龄  按年份计算

-- 第一步:锁定表 学生表

-- 第二步:使用now() year()函数计算年龄
select year('2022-01-01 00:00:00')
select now()
select 
* ,
(year(now())-year(s_brith)) "年龄"
from student

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

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

-- 分析题目关键字 年龄  当前月日<出生年月的月日 则年龄减一

-- 第一步:锁定表 学生表

-- 第二步:使用now() timestampdiff()函数计算年龄
-- TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)  用来专门解决日期相减问题的函数
    -- 用法 3个参数  expr2-expr1  第一个参数返回值的格式 eg:如果是year 表示两个日期相差的年份 如果是day 表示
    -- 两个日期相差的天数 hour 表示相差的小时数
select TIMESTAMPDIFF(year, '2020-02-01 00:00:00','2022-01-01 00:00:00')
select TIMESTAMPDIFF(year, '2020-02-01 00:00:00','2022-03-01 00:00:00')
select TIMESTAMPDIFF(day, '2020-02-01 00:00:00','2022-03-01 00:00:00')
select TIMESTAMPDIFF(hour, '2020-02-01 00:00:00','2022-03-01 00:00:00')
select 
* ,
TIMESTAMPDIFF(YEAR,s_brith,NOW()) "年龄"
from student

42 查询本周过生日的学生

-- 查询本周过生日的学生

-- 分析题目关键字 本周过生日:当前的日期所处的周数 等于 出生日期所对应的周数 

-- 第一步:锁定表 学生表

-- 第二步:使用week()函数查询
select week('2020-02-01 00:00:00')
select
*,
WEEK(s_brith),
week('2020-05-20 00:00:00')
from student
where week(s_brith)=week('2020-05-20 00:00:00')

43 查询下周过生日的学生

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

-- 分析题目关键字 本周过生日:当前的日期所处的周数 等于 出生日期所对应的周数

-- 第一步:锁定表 学生表

-- 第二步:使用week()函数查询
select week('2020-02-01 00:00:00')
select
*,
WEEK(s_brith),
week('2020-05-20 00:00:00')+1
from student
where week(s_brith)=week('2020-05-20 00:00:00')+1

44 查询本月过生日的学生

-- 查询本月过生日的学生

-- 分析题目关键字 月份 本月过生日:当前的日期所处的月份 等于 出生日期所对应的月份

-- 第一步:锁定表 学生表

-- 第二步:使用month()函数查询
select MONTH('2020-02-01 00:00:00')
select
*,
WEEK(s_brith),
MONTH(NOW())
from student
where MONTH(s_brith)=MONTH(NOW())

45 查询下月过生日的学生

-- 查询本月过生日的学生

-- 分析题目关键字 月份 本月过生日:当前的日期所处的月份 等于 出生日期所对应的月份 

-- 第一步:锁定表 学生表

-- 第二步:使用month()函数查询
select MONTH('2020-02-01 00:00:00')
select
*,
WEEK(s_brith),
MONTH(NOW())+1
from student
where MONTH(s_brith)=MONTH(NOW())+1
予你行乐
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL突击面试宝典
08-03
MySQL是世界上最流行的关系型数据库管理系统之一，尤其在Web开发领域广泛应用。在面试中，了解MySQL的相关知识，包括索引、ACID特性和主从复制等，对于求职者来说至关重要。 一、索引 1. 为什么需要索引 索引的...
mysql面试突击班.jpg
09-07
mysql面试突击班.jpg
MySQL面试突击(五)
绅士jiejie的博客
04-03 259
MySQL支持事务吗？ 答:MySQL默认情况下是自动提交的，不支持事务，但是如果存储引擎采用的是InnoDB或者BDB，就可以使用SET AUTOCOMMIT=0关闭自动提交，然后通过COMMIT来手动提交修改，或者是ROLLBACK来回滚更改。 NULL是什么意思？ 答：Null表示这个字段值未知，尚未存储数据库字段值，它不表示“ ”(空字符串)，无法将Null与值进行比较，使用IS ...
Mysql面试突击
weixin_43572928的博客
10-04 101
MyISAM与InnoDB 的区别（9个不同点）
太牛了！吊打全网的MySQL进阶面试突击，吃透最少阿里P7
xingduan5153的博客
07-11 662
网上有不少关于 MySQL 调优的文章，之前也发过一些。但不少朋友反映依旧对 MySQL 底层原理及架构这些内容一知半解，没有吃透，总是会遇到不少问题： 如何提高查询语句性能？如何突破单库性能瓶颈？如何做到数据库的高并发与高可用？ 为了解答 MySQL 体系架构、InnoDB 执行流程、索引优化等问题，这里给大家推荐的这份【MySQL进阶面试突击】，特别适合对 MySQL索引调优、事务和锁优化...
MySQL面试突击
小朱小朱绝不认输的博客
02-16 2283
MySQL相关面试问题的整理总结。 文章目录一、基础知识篇1. Mysql中的MyISAM与InnoDB的区别？2. InnoDB存储引擎的四大特性？3. 什么是视图？视图的使用场景有哪些？4. 数据库三大范式？5. 非关系型数据库和关系型数据库区别，优势比较?6. 什么是 内连接、外连接、交叉连接、笛卡尔积等?7. SQL语言分类二、事务1. 什么是事务？2. 数据库事务的四大特性？3. InnoDB如何保证事务的四大特性？4. MySQL数据库提供的四种隔离级别？5. 不考虑事务的隔离性，会发生几种问题
MySQL面试突击_面试突击
weixin_39598568的博客
02-07 443
1、如果线上机器突然宕机，线程池的阻塞队列中的请求怎么办？导致队列中积压的任务会丢失解决 ： 提交任务之前先在数据库里插入这个任务的信息，并标以状态比如 ： 未提交、已提交、已完成。待机器重启后，使用一个后台线程扫描表中已提交和未提交的数据，进行重新提交2、谈谈你对Java内存模型的理解每条线程有自己的工作内存，工作内存中保存了被该线程使用的变量的主内存副本。线程不可以直接操作主内存，不同线程不可...
Mysql面试突击（体系结构与架构设计）.md
最新发布
01-08
Mysql面试突击（体系结构与架构设计）.Mysql面试突击（体系结构与架构设计）.mdMysql面试突击（体系结构与架构设计）.mdMysql面试突击（体系结构与架构设计）.mdMysql面试突击（体系结构与架构设计）.mdMysql面试...
MySQL55题及答案
07-14
以下是一些基于标题"MySQL55题及答案"和描述"mysql面试题 + 适用于跳槽人员突击使用"的相关知识点。 1. **SQL基础** - 数据库概念：理解什么是关系型数据库，如何创建、删除和修改数据库。 - 表操作：包括创建、...
php实战突击 在线论坛 php mysql
10-26
【PHP实战突击在线论坛系统详解】 本资源是一个基于PHP和MySQL构建的在线论坛系统，旨在帮助初学者通过实战提升PHP编程技能。这个论坛系统涵盖了PHP基础、MySQL数据库操作以及前端页面设计等多个方面的重要知识点。...
MySQL面试突击(四)
绅士jiejie的博客
04-03 272
什么是非标准字符串类型？ 答：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT。 列的字符串类型可以是什么？ 答：SET，BLOB，ENUM，CHAR，TEXT，VARCHAR 如何在Unix和Mysql时间戳之间进行转换？ 答：从Unix时间戳转换为Mysql时间戳的命令是FROM_UNIXTIME，从Mysql时间戳转换为Unix时间戳的命令是UNIX_TIMES...
MySQL面试突击(三)
绅士jiejie的博客
04-03 309
说说MySQL数据库中MyISAM类型表的特点？ 答： 一个MYISAM类型的表中有三个文件，分别是索引文件、表结构文件、数据文件 MYISAM类型的表，不支持事务，但是每次查询都是原子操作 支持表级锁，每次操作都是对整个表加锁 会存储表的总行数 采用的是非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。 说说MySQL数据库中InnoD...
MySQL面试突击(二)
绅士jiejie的博客
04-02 802
说说MySQL中有哪几种锁？ 答：表级锁，行级锁，页级锁 说说表级锁的特点？ 答：开销小，加锁快，不会出现死锁，但是一锁就是锁整个表，所以锁定粒度大，会发生锁冲突的概率最高，并发度是最低的。 说说行级锁的特点？ 答：开销大，加锁慢，会出现死锁，锁的是表中的行，锁定粒度最小，发生锁冲突的概率最低，所以并发度也是最高的。 说说页面锁的特点？ 答：开销和加锁时间介于表锁和行锁之间，也会出...
MySql突击学习（6.17）
我是太阳
06-17 346
能够熟练的书写SQL基本上是一个程序员基本的素养。对于我来说，之前每次都是遇到不会的就去问百度，这虽然能解决在日常开发中的一些问题，但是面试的时候可不会让你去问百度，这就要求我们对SQL语句熟练掌握，目前市面上主流的关系型数据库有MySql和Oracle数据库，但这两种数据库所用的sql语句有所差异。下面将总结一下在写SQL的时候遇到的问题以及一些注意点。当然想要提高sql能力，力扣刷题也是一个不错的选择。 一、嵌套查询 1、什么是嵌套查询？ 嵌套查询的意思是，一个查询语句(select-from-wher
一文解决MySQL突击面试，关键知识点总结
自律使我自由
05-03 1444
A （Atomicity, 原子性）一个事务中的所有操作要不全部成功，要不全部失败，不能出现部分成功，部分失败的情况。C（Consistency，一致性）数据库设计上这个含义比较模糊，简单可以理解为财务的对账一样，两边数据的加加减减必须要能保持一致。I（Isolation，隔离性）主要是针对在并发场景下，对于共享资源的访问要有一定的隔离性，在MySQL中隔离性也是分等级的，根据不同的业务需求选择不同的隔离性，主要依靠锁+MVCC来实现，隔离性越强，数据库的吞吐就越差。
Mysql经典例题练习与总结
qq_42265608的博客
06-14 3762
Mysql练习题
mysql常用的基本语句_mysql基础常用语句
weixin_33365214的博客
01-27 76
表名和字段–1.学生表Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别–2.课程表Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号–3.教师表Teacher(t_id,t_name) –教师编号,教师姓名–4.成绩表Score(s_id,c_id,s_score) –学生编号,课程编号,分数测试...
建立索引的原则
Starqinsx
12-20 2182
1) 定义主键的数据列一定要建立索引。 2) 定义有外键的数据列一定要建立索引。 3) 对于经常查询的数据列最好建立索引。 4) 对于需要在指定范围内的快速或频繁查询的数据列; 5) 经常用在WHERE子句中的数据列。 6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面
MySQL经典50题
weixin_44839146的博客
03-23 4917
虽然网上已经有很多相关资源，但是正确解答的优质资源并不太多，因此本篇文章将这50道题重新整理了一下，希望能给刚学习sql的童鞋一点帮助。 Student(S,Sname,Sage,Ssex) 学生表 Course(C,Cname,T) 课程表 SC(S,C,score) 成绩表 Teacher(T,Tname) 教师 1.1 创建学生表 CREATE TABLE `student` ( `S` varchar(10) NOT NULL, `Sname` varchar(10) DEFA
MySQL索引优化与面试必备技巧
"MySQL突击面试宝典深入探讨了MySQL数据库中的关键知识点，特别关注索引这一核心概念。索引在数据库中起着至关重要的作用，它的工作原理类似于现实生活中的有序分类系统，如字典或货柜，能加速数据检索。索引的结构...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值