HiveSQL练习题--基础查询

最新推荐文章于 2024-05-16 08:05:51 发布

Link_999

最新推荐文章于 2024-05-16 08:05:51 发布

阅读量76

点赞数 6

分类专栏： Hive 文章标签： hive

本文链接：https://blog.csdn.net/weixin_44589713/article/details/137401058

版权

Hive 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

环境准备

创建表结构

--创建学生表
drop table if exists student_info;
create table if not exists student_info(
    stu_id string comment '学生ID',
    stu_name string comment '学生姓名',
    birthday string comment '出生日期',
    sex string comment '性别'
)
row format delimited fields terminated by ','
stored as textfile;

--创建课程表
drop table if exists course_info;
create table if not exists course_info(
    course_id string comment '课程ID',
    course_name string comment '课程名',
    tea_id string comment '任课老师ID'
)
row format delimited fields terminated by ','
stored as textfile;

--创建老师表
drop table if exists teacher_info;
create table if not exists teacher_info(
    tea_id string comment '老师ID',
    tea_name string comment '老师姓名'
)
row format delimited fields terminated by ','
stored as textfile;

--创建分数表
drop table if exists score_info;
create table if not exists score_info(
    stu_id string comment '学生ID',
    course_id string comment '课程ID',
    score int comment '成绩'
)
row format delimited fields terminated by ','
stored as textfile;

数据准备

(base) [link999@hadoop102 hive]$ mkdir test_data

vim student_info.txt
001,夏常安,1998-04-23,男
002,张保庆,1994-03-20,男
003,庄文杰,1999-09-21,男
004,林惊羽,1997-08-28,男
005,王凯莉,1996-08-12,女

vim course_info.txt
01,语文,1003
02,数学,1001
03,英语,1004
04,体育,1002
05,音乐,1002

vim teacher_info.txt
1001,张高数
1002,李体音
1003,王子文
1004,刘丽英

vim score_info.txt
001,01,94
002,01,74
004,01,85
005,01,64

在这里插入图片描述

加载数据

将数据文件分别加载至表中

load data local inpath '/opt/module/hive/test_data/course_info.txt' into table course_info;
load data local inpath '/opt/module/hive/test_data/score_info.txt' into table score_info;
load data local inpath '/opt/module/hive/test_data/student_info.txt' into table student_info;
load data local inpath '/opt/module/hive/test_data/teacher_info.txt' into table teacher_info;

基础查询

简单查询

查询姓名中带“安”的学生名单

select * from student_info where stu_name like "%安%";

在这里插入图片描述

查询姓“王”老师的个数

select count(1) from db_hive.teacher_info where tea_name like '王%';

在这里插入图片描述

检索课程编号为“04”且分数小于60的学生的分数信息，结果按分数降序排列

select * from score_info where course_id='04' and score<60 order by score desc;

在这里插入图片描述

查询数学成绩75以上的学生信息和其对应的语文学科成绩，按照学号升序排序

select t1.*, t2.score
from student_info t1
         left join score_info t2 on t1.stu_id = t2.stu_id
where t2.course_id = (select distinct course_id from course_info where course_name = '语文')
  and t2.score > 75
order by t1.stu_id;

在这里插入图片描述

分组查询

查询各科成绩最高和最低的分，以如下的形式显示：课程号、最高分、最低分

select course_id, max(score), min(score)
from score_info
group by course_id;

在这里插入图片描述

查询每门课程有多少学生参加了考试（有考试成绩）

select course_id, count(stu_id)
from score_info
where score is not null
group by course_id;

在这里插入图片描述

查询男生、女生人数

select sex, count(sex)
from student_info
group by sex;

在这里插入图片描述

对分组结果的条件查询

查询平均成绩大于60分的学生的学号和平均成绩

select stu_id, avg(score)
from score_info
group by stu_id
having avg(score) > 60;

在这里插入图片描述

查询至少考了四门课程的学生学号

select stu_id
from score_info
group by stu_id
having count(course_id) >= 4;

在这里插入图片描述

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

select avg(score) avg_score
from score_info
group by course_id
order by avg_score, course_id desc;

在这里插入图片描述

统计参加考试人数大于等于15的学科

select course_id, count(course_id)
from score_info
group by course_id
having count(course_id) > 15;

在这里插入图片描述

查询结果排序和分组指定条件

查询学生的总成绩并按照总成绩降序排序

select stu_id, sum(score) sum_score
from score_info
group by stu_id
order by sum_score desc ;

在这里插入图片描述

查询一共参加三门课程且其中一门为语文课程的学生的id和姓名

select s1.stu_id, s1.stu_name
from (select stu_id
      from score_info
      where stu_id in (select stu_id
                       from score_info
                       where course_id = (select course_id
                                          from course_info
                                          where course_name = '语文'))
      group by stu_id
      having count(score) = 3) s0
         inner join student_info s1
                    on s0.stu_id = s1.stu_id;

在这里插入图片描述

复杂查询

查询没有学全所有课的学生的学号、姓名

select sti.stu_id,
       sti.stu_name,
       count(sci.course_id)
from student_info sti
         left join
     score_info sci
     on
         sti.stu_id = sci.stu_id
group by sti.stu_id, sti.stu_name
having count(sci.course_id)<(select count(*) from course_info)

在这里插入图片描述

查询出只选修了三门课程的全部学生的学号和姓名

select s0.stu_id, s0.stu_name
from student_info s0
         left join score_info s1
                   on s0.stu_id = s1.stu_id
group by s0.stu_id,s0.stu_name
having count(s1.course_id) = 3;

在这里插入图片描述

多表查询

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

select s1.stu_id,
       s1.stu_name,
       count(s2.course_id) count_course,
       sum(s2.score) sum_score
from student_info s1
         left join score_info s2
                   on s1.stu_id = s2.stu_id
group by s1.stu_id, s1.stu_name;

在这里插入图片描述

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

select s1.stu_id,
       s1.stu_name,
       avg(score) avg_score
from student_info s1
         left join score_info s2
                   on s1.stu_id = s2.stu_id
group by s1.stu_id,
         s1.stu_name
having avg(score) > 85;

在这里插入图片描述

查询学生的选课情况：学号，姓名，课程号，课程名称

select s1.stu_id, s1.stu_name, c1.course_id, c1.course_name
from student_info s1
         left join score_info s2
                   on s1.stu_id = s2.stu_id
         left join course_info c1
                   on s2.course_id = c1.course_id;

在这里插入图片描述

查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息

select s1.stu_id, s1.stu_name, c1.course_id, c1.course_name
from student_info s1
         inner join (select *
                    from score_info
                    where score > 80
                      and course_id = '03') s2
                   on s1.stu_id = s2.stu_id
         inner join course_info c1
                   on s2.course_id = c1.course_id;

在这里插入图片描述

多表连接

课程编号为"01"且课程分数小于80，按分数降序排列的学生信息

select s1.stu_id,
       s1.stu_name,
       s1.sex,
       s1.birthday,
       s2.score
from student_info s1
         inner join score_info s2
                    on s1.stu_id = s2.stu_id
where s2.course_id = '01'
  and s2.score < 80
order by s2.score desc;

在这里插入图片描述

查询所有课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序排列

select s1.stu_name, c1.course_name, s2.score
from student_info s1
         inner join score_info s2
                    on s1.stu_id = s2.stu_id
         inner join (select stu_id from score_info
                                   group by stu_id
                                   having sum(if(score>70,0,1))=0) s3
    on s1.stu_id=s3.stu_id
         inner join course_info c1
                    on s2.course_id = c1.course_id
order by s2.score;

在这里插入图片描述

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

select s1.stu_id, s2.course_id, s1.score
from score_info s1
         inner join score_info s2
                    on s1.stu_id = s2.stu_id
                        and s1.course_id <> s2.course_id
                        and s1.score = s2.score;

在这里插入图片描述

查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

select s1.stu_id
from score_info s1
         inner join score_info s2
                    on s1.stu_id = s2.stu_id
                        and s1.course_id = '01'
                        and s2.course_id = '02'
                        and s1.score > s2.score;

在这里插入图片描述

查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

select s1.stu_id, s1.stu_name
from student_info s1
         inner join score_info s2
                    on s1.stu_id = s2.stu_id
         inner join score_info s3
                    on s1.stu_id = s3.stu_id
where s2.course_id = '01'
  and s3.course_id = '02';

在这里插入图片描述

查询学过“王子文”老师所教的所有课的同学的学号、姓名

select s1.stu_id, s1.stu_name, c1.course_name
from student_info s1
         inner join score_info s2
                    on s1.stu_id = s2.stu_id
         inner join course_info c1
                    on s2.course_id = c1.course_id
         inner join teacher_info t1
                    on c1.tea_id = t1.tea_id
where t1.tea_name = '王子文';

在这里插入图片描述

查询学过“王子文”老师所讲授的任意一门课程的学生的学号、姓名

select s0.stu_id,
       s0.stu_name
from student_info s0
where s0.stu_id in (select s1.stu_id
                        from score_info s1
                        where s1.course_id
                                  in (select c1.course_id
                                      from course_info c1
                                               inner join teacher_info t1 on c1.tea_id = t1.tea_id
                                      where t1.tea_name = '王子文'
                                      group by c1.course_id));

在这里插入图片描述

查询没学过"王子文"老师讲授的任一门课程的学生姓名

select s0.stu_id,
       s0.stu_name
from student_info s0
where s0.stu_id not in (select s1.stu_id
                        from score_info s1
                        where s1.course_id
                                  in (select c1.course_id
                                      from course_info c1
                                               inner join teacher_info t1 on c1.tea_id = t1.tea_id
                                      where t1.tea_name = '王子文'
                                      group by c1.course_id));

在这里插入图片描述

查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名

select s2.stu_id, s2.stu_name
from student_info s2
         inner join score_info s3 on s2.stu_id = s3.stu_id
where s3.course_id in (select s1.course_id
                       from score_info s1
                       where s1.stu_id = '001'
                       group by s1.course_id)
and s2.stu_id <> '001'
;

在这里插入图片描述

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

select s1.stu_id, s1.stu_name, c1.course_name, t1.avg_score as avg_score
from student_info s1
         left join score_info s2
                   on s1.stu_id = s2.stu_id
         left join course_info c1 on s2.course_id = c1.course_id
         left join (select stu_id, avg(score) avg_score from score_info group by stu_id) t1
                   on s1.stu_id = t1.stu_id
order by t1.avg_score desc
;

在这里插入图片描述

presto引擎查询遇到的问题记录

Query failed

[2024-04-05 15:04:57] [1] Query failed (#20240405_070456_00065_j56k4): line 1:1: Schema must be specified when session schema is not set

在这里插入图片描述

hive和presto关于引号使用区别

hive中字符串可以使用单引号或双引号，presto中字符串只能使用单引号

Link_999

关注

6
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
HiveSQL练习题--基础查询

【代码】HiveSQL练习题--基础查询。
复制链接

扫一扫

专栏目录