MySQL练习(二)——MySQL之经典面试50题(下)

  我们在上篇文章开始介绍MySQL经典的面试题,我们接着上篇文章进行介绍剩下的25题。

  • 26、查询每门课程被选修的学生数
select c_id,count(s_id) 选修人数 
from sc
group by c_id;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 43、练习:统计每门课程的学生选修人数(超过5人的课程才统计)
#要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select c_id,count(s_id) 选修人数 
from sc
group by c_id
having count(s_id)>5;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 30、查询同名同姓学生名单,并统计同名人数
select s_name,count(s_name)-1 同名人数
from stu
group by s_name;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select s_name,c_name,score 
from stu 
join sc on stu.s_id=sc.s_id
join co on sc.c_id=co.c_id
where score>70;

select s_name,c_name,score 
from stu 
join sc on stu.s_id=sc.s_id
join co on sc.c_id=co.c_id
where stu.s_id in (select s_id from sc where score>70);

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 37、练习:查询出现过学生考试不及格的课程
select c_name,sc.c_id,score
from sc join co on sc.c_id=co.c_id
where score<60;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 41、查询课程不同、成绩相同的学生的学生编号、课程编号、学生成绩
select distinct t1.* 
from sc t1 join sc t2 on t1.s_id=t2.s_id and t1.c_id<>t2.c_id and t1.score=t2.score;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 47、查询本周过生日的学生
select * 
from stu
where week(s_birth)=week(curdate());

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 48、练习:查询下周过生日的学生
select * 
from stu
where week(s_birth)=if(week(curdate())=54,1,week(curdate())+1);

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 49、查询本月过生日的学生
select * 
from stu
where month(s_birth)=month(curdate());

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 50、练习:查询下月过生日的学生
select * 
from stu
where month(s_birth)=if(month(curdate())=12,1,month(curdate())+1);

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select stu.s_id, stu.s_name, count(sc.c_id), sum(score)
from sc
left join co  on sc.c_id = co.c_id
right join stu on sc.s_id = stu.s_id
group by stu.s_id, stu.s_name;

SELECT stu.s_id,stu.s_name,COUNT(sc.c_id),
CASE WHEN SUM(sc.score) IS NULL THEN 0.00 ELSE ROUND(SUM(sc.score),2)  END AS '总成绩'
FROM  stu 
LEFT JOIN sc ON sc.s_id=stu.s_id
GROUP BY stu.s_id;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT st.s_name 
FROM stu st 
WHERE st.s_id 
NOT IN (
SELECT sc.s_id
FROM sc
INNER JOIN co ON co.c_id=sc.c_id
INNER JOIN te ON te.t_id=co.t_id AND te.t_name="张三"
);

select stu.s_name 
from stu
where stu.s_id not in
(select sc.s_id 
from sc,co,te
where sc.c_id=co.c_id and co.t_id=te.t_id and te.t_name='张三');

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT stu.s_id,stu.s_name,AVG(sc.score) 平均成绩
FROM stu 
LEFT JOIN sc ON sc.s_id=stu.s_id
WHERE sc.s_id IN
(
SELECT sc.s_id 
FROM sc
WHERE sc.score<60 OR sc.score IS NULL
GROUP BY sc.s_id HAVING COUNT(1)>=2
)
GROUP BY stu.s_id;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 16、检索"01"课程分数小于 60,按分数降序排列的学生信息
SELECT stu.*
FROM  stu
LEFT JOIN  sc ON sc.s_id=stu.s_id
WHERE sc.c_id='01' AND sc.score<60
ORDER BY sc.score DESC ;

select *
from stu 
where s_id in (
				select s_id
				from sc 
                where c_id = '01' and score < 60
                order by score desc
			  );

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 21、查询不同老师所教不同课程平均分从高到低显示
SELECT te.t_id,te.t_name,co.c_name,AVG(sc.score) 平均成绩
FROM te
LEFT JOIN co ON co.t_id=te.t_id 
LEFT JOIN sc ON sc.c_id =co.c_id
GROUP BY te.t_id
ORDER BY AVG(sc.score) DESC;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)
  我们发现报错了,报了1055错误,错误的原因大致是:在MySQL5.7之后,sql_mode中默认存在ONLY_FULL_GROUP_BYSQL语句未通过ONLY_FULL_GROUP_BY语义检查所以报错。 这里的ONLY_FULL_GROUP_BY要求select语句中查询出来的列必须是明确的。我们以SQL语句select columes from table group by list为例:columns必须是聚集函数或者在group by后的表达式list中,并且list中必须包含主键,否则也会报错。当然,其他的insertupdatedelete语句都会报错(但不影响SQL语句的执行),因为这三种语句执行之前也会执行查询操作。通过网上查询,发现我们只需要将ONLY_FULL_GROUP_BY关掉重新打开MySQL workbench即可;具体实现如下:

SELECT @@sql_mode;
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

  我们再次运行上面的命令即可成功,具体效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 27、查询出只有两门课程的全部学生的学号和姓名
select stu.s_id, s_name, count(sc.c_id) 选修课程 
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
having count(distinct sc.c_id) = 2;

select s_id, s_name 
from stu
where s_id in (
				select s_id
                from sc
                group by s_id
                having count(distinct c_id) = 2
				);

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 28、查询男生、女生人数
select s_sex, count(distinct s_id)
from stu
group by s_sex;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 31、查询 1990 年出生的学生名单(注:Stu表中s_birth列的类型是 datetime)
select *
from stu
where year(s_birth) = 1990 ;

SELECT * 
FROM stu 
WHERE s_birth LIKE "1990%";

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号
select c_id,avg(score) 平均成绩
from sc
group by c_id
order by 平均成绩 desc, c_id;

SELECT co.c_id,co.c_name,AVG(sc.score) 平均成绩
FROM co JOIN sc ON sc.c_id=co.c_id  
GROUP BY co.c_id 
ORDER BY AVG(sc.score) DESC,co.c_id ASC;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 33、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT stu.s_id,stu.s_name,AVG(sc.score) 平均成绩
FROM stu
LEFT JOIN sc ON sc.s_id=stu.s_id
GROUP BY stu.s_id 
HAVING AVG(sc.score)>=85;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 34、查询课程名称为"数学",且分数低于 60 的学生姓名和分数
select c_name, s_name, score
from sc 
left join stu on sc.s_id = stu.s_id
left join co on sc.c_id = co.c_id
where c_name = "数学" and score < 60;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 38、查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT stu.s_id,stu.s_name,sc.score 
FROM stu JOIN sc ON sc.s_id=stu.s_id AND sc.c_id="01" AND sc.score>=80;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 39、求每门课程的学生人数
select c_id, count(c_id)
from sc
group by c_id;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 44、检索至少选修两门课程的学生学号
SELECT s_id,COUNT(DISTINCT c_id) 
FROM sc
GROUP BY s_id 
HAVING COUNT(DISTINCT c_id)>=2;

SELECT stu.s_id, COUNT(1) 选修课程
FROM stu
LEFT JOIN sc ON sc.s_id=stu.s_id
GROUP BY stu.s_id 
HAVING COUNT(1)>=2;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)

  • 46、查询各学生的年龄
SELECT s_id,s_birth,FLOOR(DATEDIFF(CURDATE(),s_birth)/365)'年龄' 
FROM stu;

select s_id,s_birth,
case when month(current_date())<month(s_birth) then year(current_date())-year(s_birth)-1
	 when month(current_date())=month(s_birth)  and day(current_date())<day(s_birth) then year(current_date())-year(s_birth)-1
	 else year(current_date())-year(s_birth)
	 end 年龄
from stu;

  执行的效果如下:
MySQL练习(二)——MySQL之经典面试50题(下)
  至此,我们MySQL经典的50道面试题全部给大家介绍完毕,希望大家多多练习,MySQL其实大多数都是用来查的,因此,我们要熟练掌握这些查询语句,不断提高自己书写MySQL语句的能力。

上一篇:初学python写个自娱自乐的小游戏


下一篇:python 提取域名、根域名