mysql 面试题，如下，怎么写

select date as "日期",

sum(decode(area,'海淀',value,0)) as "海淀统计数",

sum(decode(area,'朝阳',value,0)) as "朝阳统计数",

sum(value) as "总数"

from a

group by date

--插入学生表测试数据

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"课程成绩高的学生的信息及课程分数

select c,as_score as 01课程score,bs_score as 02课程score from

score a,score b

left join student c

on bs_id = cs_id

where as_id = bs_id and ac_id = '01' and bc_id = '02' and as_score > bs_score;

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select a ,bs_score as 01课程,cs_score as 02课程 from student a

join score b

on as_id=bs_id and bc_id = '01'

left join score c

on bs_id = cs_id and cc_id = '02'

where bs_score < cs_score ;

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

select as_id,as_name,round(avg(bs_score),2) as 平均成绩 from student a

join score b

on as_id = bs_id

group by bs_id having 平均成绩 >= 60;

备注：round[avg(成绩),1]里,round是四舍五入函数，1代表保留1位小数

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

-- (包括有成绩的和无成绩的)

select b ,round(avg(as_score),2) as 平均成绩 from

student b

left join score a on bs_id = as_id group by as_id having 平均成绩 < 60

union

select b ,0 as 平衡成绩 from student b where bs_id not in (select s_id from score);

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

select as_id,as_name,count(bc_id) as 选课总数 ,sum(bs_score) as 总分 from student a

left join score b

on as_id = bs_id group by s_id ;

-- 6、查询"李"姓老师的数量

select count() as 李姓老师数量 from teacher where t_name like '李%';

-- 7、查询学过"张三"老师授课的同学的信息

select a from student a join score b

on as_id = bs_id

where bc_id in (select cc_id from course c

join teacher d on ct_id = dt_id where dt_name = '张三');

-- 8、查询没学过"张三"老师授课的同学的信息

select a from student a left join score b on as_id = bs_id where as_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 = '张

三'))) group by as_id;

-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select from student where s_id in

(select as_id from score a join score b on as_id = bs_id

where ac_id = '01' and bc_id = '02');

-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select from student where s_id in

(select s_id from score where c_id = '01' )

and s_id not in (select s_id from score where c_id = '02' );

-- 11、查询没有学全所有课程的同学的信息

select from student where s_id not in

(select s_id from score group by s_id having count(c_id) = 3);

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

select distinct a from student a left join score b

on as_id = bs_id where bc_id in

(select c_id from score where s_id = '01') and as_id != '01' ;

注意：distinct是去重的

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

select from student where s_id in

(select s_id from score group by s_id having count(c_id) =

(select count(c_id) from score where s_id = '01') and 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');

-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select s_name from student where s_id not in

(select s_id from score where c_id in

(select c_id from course where t_id in

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

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

select as_id ,bs_name,round(avg(as_score),2) as 平均成绩 from score a

left join student b on as_id = bs_id

where s_score < 60 group by s_id having count(1) >=2;

或者试试

select as_id ,bs_name,round(avg(as_score),2) as 平均成绩 from score a

left join student b on as_id = bs_id

where as_score < 60 group by as_id having count() >=2;

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

select a ,bc_id ,bs_score from student a

left join score b on as_id = bs_id

where bc_id = '01' and bs_score < 60

order by bs_score desc;

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

select as_name ,

sum(case when bc_id = '01' then s_score else null end ) as 语文,

sum(case when bc_id = '02' then s_score else null end ) as 数学,

sum(case when bc_id = '03' then s_score else null end ) as 英语,

round(avg(s_score),2) as 平均成绩

from student a left join score b on as_id = bs_id group by as_name

order by 平均成绩 desc;

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

--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select bc_id,bc_name,

max(as_score) as 最高分,

min(as_score) as 最低分,

round(avg(as_score),2) as 平均分,

round(sum(case when as_score>= 60 then 1 else 0 end)/count(s_id),2) as 及格率 ,

round(sum(case when as_score>= 70 and as_score <80 then 1 else 0 end)/count(s_id),2) as 中等率,

round(sum(case when as_score>= 80 and as_score <90 then 1 else 0 end)/count(s_id),2) as 优良率,

round(sum(case when as_score>= 90 then 1 else 0 end)/count(s_id),2) as 优秀率

from score a

left join course b

on ac_id = bc_id group by bc_id;

-- 19、按各科成绩进行排序，并显示排名

第一种：

set @pre_c_id:= '01';

set @rank:=0;

select tb2s_id ,tb2c_id,tb2s_score,tb2排名 from

(select ,(case when tb1c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1c_id then @pre_c_id else @pre_c_id:=tb1c_id end ) as pre_c_id

from

(select from score order by c_id,s_score desc) tb1 )tb2;

如果看不懂用第二种方法：

SELECT ac_id,as_id,as_score,COUNT(bs_score)+1 AS 排名

FROM score a LEFT JOIN score b ON as_score<bs_score AND ac_id = bc_id

GROUP BY ac_id,as_id,as_score ORDER BY ac_id,排名,as_id ASC

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

set @rank:=0;

select ,(@rank:=@rank+1) as rank from

(select s_id ,sum(s_score) as 总成绩 from score

group by s_id order by 总成绩 desc) tb1;

-- 21、查询不同老师所教不同课程平均分从高到低显示

select ac_id, dt_name,round(avg(as_score)) as 平均分 from score a

left join student b on as_id = bs_id

left join course c on ac_id = cc_id

left join teacher d on ct_id = dt_id group by ac_id

order by 平均分 desc;

-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

set @pre_c_id:= '01';

set @rank:=0;

select bs_name,tb2s_id ,tb2c_id,tb2s_score,tb2排名 from

(select ,(case when tb1c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1c_id then @pre_c_id else @pre_c_id:=tb1c_id end ) as pre_c_id

from

(select from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2s_id = bs_id where 排名 = 2 or 排名 =3;

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

select bc_id,bc_name ,

sum(case when as_score >=85 then 1 else 0 end) as 100-85 ,

concat(round(100 sum(case when as_score >=85 then 1 else 0 end)/count( ),2), '%') as 百分比,

sum(case when as_score <85 and as_score >=70 then 1 else 0 end) as 85-70 ,

concat(round(100 sum(case when as_score <85 and as_score >=70 then 1 else 0 end)/count( ),2),'%') as 百分比,

sum(case when as_score <70 and as_score >=60 then 1 else 0 end) as 70-60 ,

concat(round(100 sum(case when as_score <70 and as_score >=60 then 1 else 0 end)/count( ),2) ,'%')as 百分比,

sum(case when as_score <60 and as_score >=0 then 1 else 0 end) as 60-0 ,

concat(round(100 sum(case when as_score <60 and as_score >=0 then 1

else 0 end)/count( ),2),'%') as 百分比

from score a left join course b on ac_id = bc_id group by bc_id;

-- 24、查询学生平均成绩及其名次

select tb1,(@rank:=@rank +1 ) as rank from

(select s_id ,round(avg(s_score),2) as 平均成绩 from score

group by s_id order by 平均成绩 desc) tb1,(select @rank:=0) b;

-- 25、查询各科成绩前三名的记录

set @pre_c_id:= '01';

set @rank:=0;

select bs_name,tb2s_id ,tb2c_id,tb2s_score,tb2排名 from

(select ,(case when tb1c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1c_id then @pre_c_id else @pre_c_id:=tb1c_id end ) as pre_c_id

from

(select from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2s_id = bs_id where 排名 <4;

-- 26、查询每门课程被选修的学生数

select c_id ,count(s_id) as 选修人数 from score group by c_id;

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

select as_id ,bs_name from score a left join student b on as_id = bs_id group by s_id having count() = 2;

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

select sum(case s_sex when '男' then 1 else 0 end) as 男生人数,

sum(case s_sex when '女' then 1 else 0 end) as 女生人数 from student;

-- 29、查询名字中含有"风"字的学生信息

select from student where s_name like '%风%';

-- 30、查询同名同性学生名单，并统计同名人数

--略，不想写

-- 31、查询1990年出生的学生名单

select from student where s_birth like '1990%';

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

select c_id ,round(avg(s_score),2) as 平均成绩 from score group by c_id order by 平均成绩 desc, c_id asc;

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

select as_id,bs_name ,round(avg(s_score),2) as 平均成绩 from score a

left join student b on as_id = bs_id group by as_id having 平均成绩>=85;

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

select bs_name ,as_score from score a

left join student b on as_id = bs_id

where ac_id=(select c_id from course where c_name = '数学')and as_score < 60;

-- 35、查询所有学生的课程及分数情况；

select bs_name,

sum(case when ac_id = '01' then as_score else null end) as 语文,

sum(case when ac_id = '02' then as_score else null end) as 数学,

sum(case when ac_id = '03' then as_score else null end) as 英语

from score a right join student b on as_id = bs_id group by bs_name

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

select bs_name,

sum(case when ac_id = '01' then as_score else null end) as 语文,

sum(case when ac_id = '02' then as_score else null end) as 数学,

sum(case when ac_id = '03' then as_score else null end) as 英语

from score a right join student b on as_id = bs_id group by bs_name having 语文>= 70 or 数学>= 70 or 英语>= 70 ;

-- 37、查询不及格的课程

select as_id,ac_id,bc_name,as_score from score a

left join course b on ac_id = bc_id where as_score<60;

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

select as_id,bs_name from score a left join student b on as_id = bs_id where ac_id = '01' and as_score>=80;

-- 39、求每门课程的学生人数

select c_id,count() as 学生人数 from score group by c_id ;

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

select a,bc_id,max(bs_score) as 最高成绩 from student a

right join score b on as_id = bs_id

group by bc_id

having bc_id = (select c_id from course

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

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

--（这题我搞不清题目是什么意思,是指查找学生个体参加了的所有课程的成绩各不相同的那个学生信息呢？还是所有课程之间做对比呢，我更倾向于理解为前者）

--理解为前者的写法

select from

(select from score group by s_id,s_score) tb1

group by s_id having count() = 1;

--理解为后者的写法

select distinct as_id,ac_id,bs_score from score a,score b where ac_id != bc_id and as_score = bs_score;

-- 42、查询每门课程成绩最好的前两名

set @pre_c_id:= '01';

set @rank:=0;

select tb2s_id ,tb2c_id,tb2s_score from

(select ,(case when tb1c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1c_id then @pre_c_id else @pre_c_id:=tb1c_id end ) as pre_c_id

from

(select from score order by c_id,s_score desc) tb1 )tb2

join student b on tb2s_id = bs_id where 排名 <3;

-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人相同，按课程号升序排列

select c_id ,count() as 选修人数 from score group by c_id having 选修人数>5 order by 选修人数 desc , c_id asc;

-- 44、检索至少选修两门课程的学生学号

select s_id from score group by s_id having count() >= 2;

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

select from student where s_id in

(select s_id from score group by s_id having count() = 3)

--46、查询各学生的年龄

select s_name ,(date_format(now(),'%Y')-date_format(s_birth,'%Y') + (CASE when date_format(now(),'%m%d')>=date_format(s_birth,'%m%d') then 0 else 1 end)) as age

from student

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

---(实现得并不完全，因为例如出生月日为‘01-01’在每一年可能会输入不同周）

select from student where week(date_format(s_birth,'%m%d'))=week(date_format(now(),'%m%d')) ;

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

select from student

where week(date_format(s_birth,'%m%d'))=week(date_format(date_add(now(),interval 7-dayofweek(now())+1 day),'%m%d'));

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

select from student where date_format(s_birth,'%m') = date_format(now(),'%m')

-- 50、查询下月过生日的学生

select from student where date_format(s_birth,'%m') = date_format(date_add(now(),interval 1 month),'%m')

MySQL 主从一直是面试常客，里面的知识点虽然基础，但是能回答全的同学不多。

比如楼哥之前面试小米，就被问到过主从复制的原理，以及主从延迟的解决方案，因为回答的非常不错，给面试官留下非常好的印象。你之前面试，有遇到过哪些 MySQL 主从的问题呢？

所谓 MySQL 主从，就是建立两个完全一样的数据库，一个是主库，一个是从库， 主库对外提供读写的 *** 作，从库对外提供读的 *** 作 ，下面是一主一从模式：

对于数据库单机部署，在 4 核 8G 的机器上运行 MySQL 57 时，大概可以支撑 500 的 TPS 和 10000 的 QPS， 当遇到一些活动时，查询流量骤然，就需要进行主从分离。

大部分系统的访问模型是读多写少，读写请求量的差距可能达到几个数量级，所以我们可以通过一主多从的方式， 主库只负责写入和部分核心逻辑的查询，多个从库只负责查询，提升查询性能，降低主库压力。

MySQL 主从还能做到服务高可用，当主库宕机时，从库可以切成主库，保证服务的高可用，然后主库也可以做数据的容灾备份。

整体场景总结如下：

MySQL 的主从复制是依赖于 binlog 的，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上二进制日志文件。

主从复制就是将 binlog 中的数据从主库传输到从库上，一般这个过程是异步的，即主库上的 *** 作不会等待 binlog 同步的完成。

详细流程如下：

当主库和从库数据同步时，突然中断怎么办？因为主库与从库之间维持了一个长链接，主库内部有一个线程，专门服务于从库的这个长链接的。

对于下面的情况，假如主库执行如下 SQL，其中 a 和 create_time 都是索引：

我们知道，数据选择了 a 索引和选择 create_time 索引，最后 limit 1 出来的数据一般是不一样的。

所以就会存在这种情况：在 binlog = statement 格式时，主库在执行这条 SQL 时，使用的是索引 a，而从库在执行这条 SQL 时，使用了索引 create_time，最后主从数据不一致了。

那么我们改如何解决呢？

可以把 binlog 格式修改为 row，row 格式的 binlog 日志记录的不是 SQL 原文，而是两个 event:Table_map 和 Delete_rows。

Table_map event 说明要 *** 作的表，Delete_rows event用于定义要删除的行为，记录删除的具体行数。 row 格式的 binlog 记录的就是要删除的主键 ID 信息，因此不会出现主从不一致的问题。

但是如果 SQL 删除 10 万行数据，使用 row 格式就会很占空间的，10 万条数据都在 binlog 里面，写 binlog 的时候也很耗 IO。但是 statement 格式的 binlog 可能会导致数据不一致。

设计 MySQL 的大叔想了一个折中的方案，mixed 格式的 binlog，其实就是 row 和 statement 格式混合使用， 当 MySQL 判断可能数据不一致时，就用 row 格式，否则使用就用 statement 格式。

有时候我们遇到从数据库中获取不到信息的诡异问题时，会纠结于代码中是否有一些逻辑会把之前写入的内容删除，但是你又会发现，过了一段时间再去查询时又可以读到数据了，这基本上就是主从延迟在作怪。

主从延迟，其实就是“从库回放” 完成的时间，与 “主库写 binlog” 完成时间的差值， 会导致从库查询的数据，和主库的不一致 。

谈到 MySQL 数据库主从同步延迟原理，得从 MySQL 的主从复制原理说起：

总结一下主从延迟的主要原因 ：主从延迟主要是出现在 “relay log 回放” 这一步，当主库的 TPS 并发较高，产生的 DDL 数量超过从库一个 SQL 线程所能承受的范围，那么延时就产生了，当然还有就是可能与从库的大型 query 语句产生了锁等待。

我们一般会把从库落后的时间作为一个重点的数据库指标做监控和报警，正常的时间是在毫秒级别，一旦落后的时间达到了秒级别就需要告警了。

解决该问题的方法，除了缩短主从延迟的时间，还有一些其它的方法，基本原理都是尽量不查询从库。

具体解决方案如下：

在实际应用场景中，对于一些非常核心的场景，比如库存，支付订单等，需要直接查询从库，其它非核心场景，就不要去查主库了。

两台机器 A 和 B，A 为主库，负责读写，B 为从库，负责读数据。

如果 A 库发生故障，B 库成为主库负责读写，修复故障后，A 成为从库，主库 B 同步数据到从库 A。

一台主库多台从库，A 为主库，负责读写，B、C、D为从库，负责读数据。

如果 A 库发生故障，B 库成为主库负责读写，C、D负责读，修复故障后，A 也成为从库，主库 B 同步数据到从库 A。

以上就是关于mysql 面试题，如下，怎么写全部的内容，包括:mysql 面试题，如下，怎么写、sql面试题50题（mysql版）、MySQL 主从，5 分钟带你掌握等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！