1.前置准备
数据准备
18999001 王述龙 男 1998-12-10 上海 98 100 2000
18999002 孙宇鹏 男 1999-11-17 沈阳 51 500
18999003 王应龙 男 2000-02-04 沈阳 59 100
18999004 张琼宇 女 1999-07-01 大连 89 200
18999005 宋传涵 女 1999-07-20 上海 86 100 1000
18999006 李亚楠 女 1998-01-24 杭州 97 200 2000
18999007 侯楠楠 男 2000-01-29 北京 79 200
18999008 陈姝元 女 1999-06-24 北京 96 200 1500
18999009 陆春宇 男 1998-01-18 沈阳 87 300 1000
18999010 孙云琳 女 1997-07-15 上海 56 300
18999011 尤骞梓 女 1999-04-25 杭州 86 200 1000
18999012 张爱林 男 1999-05-16 北京 92 400 1500
18999013 曹雪东 男 2000-11-20 北京 78 300
18999014 贾芸梅 女 2000-06-12 大连 88 400 1000
18999015 温勇元 男 1999-08-08 上海 65 500
18999016 张微微 女 1998-01-27 北京 90 400 1500
18999017 李君年 男 1998-03-21 上海 78 500
18999018 卢昱泽 女 1998-08-01 上海 57 500
18999019 赵旭辉 男 1999-02-18 北京 75 500
18999020 张矗年 男 1997-07-26 重庆 86 300 1000
2.流程实操
创建一个数据仓库hrsystem,并切换到该库中。
create database hrsystem;
use hrsystem;
show databases;
在hrsystem数据仓库中创建一张外部表:学生表emp。
create external table if not exists emp(
sno int comment"学号",
sname string comment"姓名",
gender string comment"性别",
bday string comment"出生日期",
area string comment"地区",
score double comment"成绩",
deptno string comment"所在学院",
scholarship double comment"奖学金"
)row format delimited fields terminated by '\t';
将外部表转换为内部表。
alter table emp set tblproperties('EXTERNAL'='FLASE');
查询表结构的详细信息。
desc formatted emp;
将本地文件/opt/datas/emp.txt导入到学生表emp中。
load data local inpath '/opt/datas/emp.txt' into table emp;
在浏览器中查看HDFS上学生表emp的数据。
查询奖学金scholarship不为空的所有学生信息。
select * from emp where scholarship is not null;
查询学生表emp中平均成绩小于70分的学号。
Having和Where的区别
where作用于表中的列,having作用于查询结果中的列
where后不能写分组函数,having后可以使用分组函数
select sno, avg(score) avg_score
from emp group by sno
having avg_score < 70;
查询学生表emp中平均成绩小于70分的部门。
select deptno, avg(score) avg_score
from emp group by deptno
having avg_score < 70;
查询出生日期中含有5的学生的姓名和生日。
select sname,bday
from emp where bday RLIKE '[5]';
查询学生表emp的信息,并按部门降序排列。
-- 内部排序
select * from emp sort by deptno desc;
select * from emp order by deptno desc;
按照学生成绩的2倍排序。
select sname, score*2 twoscore
from emp order by twoscore;
查询emp表中每个部门的平均成绩。
select deptno, avg(score) avg_score
from emp group by deptno;
查询成绩大于95分,或者系别是100的学生信息。
select *
from emp where score>95 or deptno=100;
随机抽样学生表emp中的10行记录,其中包括学生姓名sname和成绩score。
select sname,score
from emp tablesample(10 rows);
利用条件函数,查询学生表emp中不同部门男女人数。
select deptno,
sum(case gender when '男' then 1 else 0 end) male_count,
sum(case gender when '女' then 1 else 0 end) female_count
from emp
group by deptno;
了解更多知识请戳下: