1.环境准备
1.1建表语句
create database if not exists db_hive;
use db_hive;
--创建学生表
drop table if exists student;
create table if not exists student(
stu_id string COMMENT '学生id',
stu_name string COMMENT '学生姓名',
birthday date COMMENT '出生日期',
sex string COMMENT '性别'
)
row format delimited fields terminated by ','
stored as textfile;
--创建课程表
drop table if exists course;
create table if not exists course(
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;
create table if not exists teacher(
tea_id string COMMENT '老师id',
tea_name string COMMENT '老师姓名'
)
row format delimited fields terminated by ','
stored as textfile;
--创建分数表
drop table if exists score;
create table if not exists score(
stu_id string COMMENT '学生id',
course_id string COMMENT '课程id',
grade int COMMENT '成绩'
)
row format delimited fields terminated by ','
stored as textfile;
1.2数据准备
-
创建/opt/module/data目录,将如下4个文件放到/opt/module/data目录下
以下是部分数据(自己准备数据)cat student.txt 001,彭于晏,1995-05-16,男 002,胡歌,1994-03-20,男 003,周杰伦,1995-04-30,男 004,刘德华,1998-08-28,男 005,唐国强,1993-09-10,男 cat course.txt 01,语文,1003 02,数学,1001 03,英语,1004 04,体育,1002 05,音乐,1002 [hyj@hadoop102 data]$ cat teacher.txt 1001,张高数 1002,李体音 1003,王子文 1004,刘丽英 cat score.txt 001,01,94 002,01,74 004,01,85 005,01,64 006,01,71 007,01,48 008,01,56 009,01,75
1.3数据准备
- 创建/opt/module/data目录,将如下4个文件放到/opt/module/data目录下
以下是部分数据(自己准备数据)
load data local inpath '/opt/module/data/student.txt' into table student;
load data local inpath '/opt/module/data/course.txt' into table course;
load data local inpath '/opt/module/data/teacher.txt' into table teacher;
load data local inpath '/opt/module/data/score.txt' into table score;
2.查询
2.1 查询姓名中带“华”的学生名单
%代表任意个字符(0个或多个)
-代表一个字符
select * from student where stu_name like ‘%华%’;
2.2 查询姓“王”老师的个数
select count(*) from teacher where tea_name like ‘王%’;
2.3 检索课程编号为“04”且分数小于60的学生学号,结果按分数降序排列
select stu_id from score where course_id=‘04’ and grade < 60 order by grade desc;
2.4 查询语文成绩 < 90分的学生和其对应的成绩,按照学号升序排序
select s#### 2.stu_id,s#### 2.stu_name,s1.grade from course c,score s1,student s2 where c.course_id=s1.course_id
and c.course_name=“语文” and s1.stu_id=s#### 2.stu_id and s1.grade < 90 order by s#### 2.stu_id;
2.5 查询各学生的年龄(精确到月份)
获取年份
select year(‘2023-05-21’);
获取当前日期
select current_date();
获取月份
select month(‘2023-05-21’);
if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select
stu_name,
concat(
if(bir_year>=0,bir_year,bir_year-1),‘岁’,
if(bir_month>=0,bir_month,bir_month+12),‘个月’
) as age
from (
select
stu_name,
year(current_date())-year(birthday) bir_year,
month(current_date())-month(birthday) bir_month
from student
) t;
2.6 查询本月过生日的学生
select * from student where month(birthday)=month(current_date());
2.7 查询课程编号为“04”的总成绩
方法一:
select ‘04’ as course_id,sum(grade) as grade_sum from score where course_id=‘04’;
方法二:
select course_id,sum(grade) as grade_sum from score where course_id=‘04’ group by course_id;
2.8 查询参加考试的学生个数
思路:对成绩表中的学号做去重并count
select count(distinct(stu_id)) from score;
–或
select count(*) from (select distinct(stu_id) from score) t;
2.9 查询各科成绩最高和最低的分,以如下的形式显示:课程号,最高分,最低分
思路:按照学科分组并使用max和min。
select course_id,max(grade),min(grade) from score g