MySQL数据库——复杂函数查询，聚合函数查询，其他函数的使用，分组选择数据

最新推荐文章于 2024-05-24 22:44:58 发布

凊野

最新推荐文章于 2024-05-24 22:44:58 发布

阅读量1.7k

点赞数 21

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/HL031125/article/details/137194644

版权

【头歌】MySQL实训代码、答案

第2关：使用 HAVING 与 ORDER BY

MySQL数据库—复杂查询（一）

第1关：交换工资


#请在此添加实现代码
########## Begin ##########
UPDATE tb_Salary
SET
sex = CASE sex WHEN "m"  THEN "f"
ELSE "m"
END;

########## End ##########

第2关：换座位

#请在此添加实现代码
########## Begin ##########
SELECT if(Id%2=0,Id-1,if(Id=5,Id,Id+1)) AS id,name
FROM tb_Seat ORDER BY Id;
########## End ##########

第3关：分数排名


#请在此添加实现代码
########## Begin ##########
select Score,(select count(distinct score) from score where score >=s.score) as Rank
from score as s order by Score desc;
select Score,(select count(*) from score as s2 where s2.score >s1.score)+1 as Rank
from score as s1 order by Rank;

########## End ##########

第4关：体育馆的人流量

#请在此添加实现代码
########## Begin ##########
select distinct a.* from gymnasium a,gymnasium b,gymnasium c
where a.visitors_flow>=100 and b.visitors_flow>=100
and c.visitors_flow>=100
and(
    (a.id = b.id-1 and b.id = c.id - 1)or
    (a.id = b.id-1 and a.id = c.id + 1)or
    (a.id = b.id+1 and b.id = c.id + 1)
)
order by a.id;

########## End ##########

第5关：统计总成绩

#请在此添加实现代码
########## Begin ##########
select t1.classname,t1.chinese,t2.maths
from(select c.classname classname,sum(s.chinese)
chinese from tb_class c,tb_score s where c.stuname=
s.name and s.chinese>=60 group by c.classname)t1,
(select c.classname classname,sum(s.maths)maths from tb_class c,tb_score s
where c.stuname=s.name and s.maths>=60 group by c.classname)t2
where t1.classname=t2.classname;

########## End ##########

MySQL数据库—复杂查询（二）

第1关：查询学生平均分


#请在此添加实现代码
########## Begin ##########
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2)as avg_score from student b 
inner join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING avg_score <60
union 
select a.s_id,a.s_name,0 as avg_score from student a 
where a.s_id not in (select distinct s_id from score);

########## End ##########

第2关：查询修课相同学生信息


#请在此添加实现代码
########## Begin ##########
create view temp as(select s_id,group_concat(c_id)as c from score group by s_id);
select * from student where s_id in(select s_id from temp where c=(select c from temp where s_id="01")and s_id<>"01");
########## End ##########

第3关：查询各科成绩并排序

#请在此添加实现代码
########## Begin ##########
select a.*,count(b.s_score)+1 rank from score a left join score b 
on a.c_id = b.c_id and a.s_score <b.s_score
group by a.c_id,a.s_id
order by a.c_id,count(b.s_score);

########## End ##########

第4关：查询张老师课程成绩最高的学生信息


#请在此添加实现代码
########## Begin ##########
select a.*,b.s_score,b.c_id,c.c_name from student a 
INNER JOIN score b ON a.s_id = b.s_id
INNER JOIN course c ON b.c_id = c.c_id
where b.c_id = (select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name="张三")
and b.s_score in (select MAX(s_score)from score where c_id="02");

########## End ##########

第5关：查询两门课程不及格同学信息


#请在此添加实现代码
########## Begin ##########
select a.s_id,a.s_name,ROUND(AVG(b.s_score))
avg_score from student a 
inner join score b on a.s_id = b.s_id
where a.s_id in(
    select s_id from score where s_score<60 GROUP BY s_id having count(*)>=2
)
GROUP BY a.s_id,a.s_name;

########## End ##########

MySQL数据库—使用聚合函数查询

第1关：COUNT( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询该表中一共有多少条数据 ##########
select count(*) from tb_class;

########## 查询此表中367班有多少位学生 ##########
select classid,count(*) from tb_class where classid=367;

########## End ##########

第2关：SUM( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询所有学生总分数 ##########
select sum(score) from tb_class;

########## 查询学生语文科目的总分数 ##########
select course,sum(score) from tb_class where course="语文";


########## End ##########

第3关：AVG( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询学生语文科目的平均分数 ##########
select course,avg(score)from tb_class where course="语文";


########## 查询学生英语科目的平均分数 ##########
select course,avg(score) from tb_class where course="英语";


########## End ##########

第4关：MAX( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询语文课程中的最高分数 ##########
select course,max(score) from tb_class where course="语文";


########## 查询英语课程中的最高分数 ##########
select course,max(score) from tb_class where course="英语";


########## End ##########

第5关：MIN( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询语文课程中的最低分数 ##########
select course,min(score) from tb_class where course="语文";


########## 查询英语课程中的最低分数 ##########
select course,min(score) from tb_class where course="英语";


########## End ##########

MySQL数据库—其他函数的使用

第1关：字符函数


#请在此添加实现代码
########## Begin ##########
select CONCAT(UPPER(SUBSTR(Name,1,1)),LOWER(SUBSTR(Name,2,LENGTH(Name)))) as Name from employee;
########## End ##########

第2关：数学函数


#请在此添加实现代码
########## Begin ##########
update Score set s_score=TRUNCATE(s_score-(round(sqrt((power(4,4)-power(3,3))/power(2,2)),2)),2);
########## End ##########

第3关：日期时间函数和流程控制类函数



#请在此添加实现代码
########## Begin ##########

##########  查询学生出生年份及年龄 ##########
select year(s_birth) year,'2019-01-01'-s_birth '年龄'
from Student;
##########  查询课程的最高分、最低分、平均分和及格率 #########
select c.c_id '课程id',
c_name '课程名',
max(s_score) '最高分',
min(s_score) '最低分',
round(avg(s_score),2) '平均分',
round((count(s_score >= 60 or null)/count(s_score)) * 100,2)  '及格率'
from Score s,Course c
where s.c_id=c.c_id
group by s.c_id;
########## End ##########

第4关：自定义函数

#请在此添加实现代码
########## Begin ##########
delimiter //
create function fn_three_max(param_1 int,param_2 int,param_3 int) RETURNS int
BEGIN
        DECLARE max_val int DEFAULT 0;
        if param_1 > param_2 then
            set max_val=param_1;
        else 
            set max_val=param_2;
        end if;
        if param_3 > max_val then
            set max_val=param_3;
        end if;
        return max_val;
END
//
########## End ##########

MySQL数据库—分组选择数据

第1关：GROUP BY 与聚合函数

USE School;

#请在此处添加实现代码
########## Begin ##########

#1.查询表中2,3,4年级中分别男女的总人数
select gradeId,sex,count(*)
from student where gradeId in (2,3,4)
group by gradeId,sex;

########## End ##########

第2关：使用 HAVING 与 ORDER BY

USE School;

#请在此处添加实现代码
########## Begin ##########

#1.查询表中至少有两门课程在90分以上的学生信息
select sno,count(*)from tb_grade
where score >=90
group by sno having count(pno) >= 2;


#2.查询表中平均成绩大于90分且语文课在95分以上的学生信息
select sno,avg(score) from tb_grade where sno 
in(select sno from tb_grade where score >=95 and pno = "语文")
group by sno having avg(score) >=90;

########## End ##########