mysql案例分析-面试题总结

一、函数案例

stu(学生信息表)、sc(考试成绩表)、co(课程表)、te(老师信息表)

1、聚合函数：

所有聚合函数会自动忽略空值

（1）查询平均成绩大于等于60分同学的学生编号、学生姓名、选课总数、所有课程的总成绩、平均成绩

select stu.s_id,s_name,count(c_id) 选课门数,sum(score) 总成绩,avg(score) 平均成绩
from stu 
left join sc on stu.s_id=sc.s_id 
group by stu.s_id
having avg(score)>=60;

（2）2017年7月1日-2017年7月31日，有过10天以上的完单并且总完单量在20单以上的司机id，司机姓名，司机完单天数、司机完单数：count+distinct的用法

select driver_id,driver_name,count( distinct order_id) as 司机完单数,count( distinct d_day) as 司机完单天数
from driver_daily
where d_year=2017 and d_month=7 
group by driver_id
having count(order_id)>20 and count( distinct d_day)>10;
#having只能接分组字段、聚合字段、或者跟分组字段一一对应的字段

2、多表连接：

on 后面尽量不要再加and条件筛选

（1）查询学过"张三"老师授课的同学的信息

select distinct *
from stu
left join sc on stu.s_id=sc.s_id
left join co on sc.c_id=co.c_id
left join te on co.t_id=te.t_id
where t_name="张三";

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

select sc.s_id,s_name,avg(sc.score)
from sc 
left join stu 
on sc.s_id=stu.s_id
where sc.s_id in (select s_id from sc where score<60 group by s_id having count(*)>=2)
group by sc.s_id; 

（3）查询不同老师所教不同课程平均分从高到低显示

select te.t_id,t_name,c_name,avg(score) 课程平均分 
from te 
left join co on te.t_id=co.t_id
left join sc on co.c_id=sc.c_idx	
group by te.t_id,co.c_id
order by 课程平均分 desc; #可以同时多条件排序及不同的排序书顺序

3、字符串函数

3.1 CONCAT

CONCAT(str1,str2,…)把多个文本字符串合并成一个长字符串()

select concat(‘CDA’,‘数据’, ‘分析’); ==》CDA数据分析

3.2 group_concat

group_concat将group by产生的同一个分组中的值连接起来，返回一个字符串结果

案例：查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

方法1：
select stu.*
from stu left join sc on stu.s_id=sc.s_id
where c_id in ('01','02')
group by stu.s_id
having group_concat(c_id)='01';

方法2
select stu.*,group_concat(c_id)
from stu
left join sc
on stu.s_id=sc.s_id
group by stu.s_id
having group_concat(c_id) not like "%02%";

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cRtMijhE-1599904704399)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1593944071705.png)]

# in 前面的条件条件也可以是多个，但数据类型和顺序需要与后面的一样
#获取每个用户登录持续时间最长的一次所在的游戏节点
select *
from time_tb
where (uid,minutes) in
(select uid,max(minutes) 
from time_tb
group by uid);

3.3 截取字符串

（1）LEFT(str,len)

返回子字符串substr在文本字符串str中第一次出现的位置()

select left(‘CDA数据分析’, 3); ===》CDA

（2）RIGHT(str,len)
返回字符串str的右端len个字符

right(‘CDA数据分析’, 4)

（3）MID(str,pos,len)
返回字符串str的位置pos起len个字符

（4）SUBSTRING ( expression, start, length )
截取字符串

3.4 REPLACE

REPLACE(str,from_str,to_str)

（1）用字符串to_str替换字符串str中的子串from_str并返回

select replace(‘CDA数据分析’, ‘CDA’, ‘cda’);

（2）手机脱敏

select replace(‘13512345678’,mid(‘13512345678’,4,4),repeat(’*’,4));

4、逻辑函数

（1）CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4…ELSE expr] END

如果expr1的值为true，则返回expr2的值，如果expr3的值为false，则返回expr4的值…

select ename,sal,case when sal>=3000 then '高' when sal>=1500 then '中'
else '低' end 工资级别

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

select *,
sum(case when c_id="01" then score else 0 end ) as "01",
sum(case when c_id="02" then score else 0 end ) as "02",
sum(case when c_id="03" then score else 0 end ) as "03",
avg(score) as 平均成绩
from sc
group by s_id
order by avg(score) desc;

（3）查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
#–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

方法1
select sc.c_id,c_name,max(score) 最高分,min(score) 最低分,avg(score) 平均分,
sum(case when score>=60 and score<70 then 1 else 0 end)/count(sc.c_id) as 及格率,
sum(case when score>=70 and score<80 then 1 else 0 end)/count(sc.c_id) as 中等率,
sum(case wh