十一、Hive基本查询操作（一）
第1关：where操作
任务描述
本关任务：使用where和like求出编程要求中所给需求。
相关知识
where
将不满足条件的行过滤，在SQL语句中执行顺序优先于group by。
having
对where的一个补充，过滤成组后的数据，执行顺序后于group by。
like
like 操作符用于在WHERE子句中搜索列中的指定模式。%代表任意多个字符。
假设存在student表：
name age
bob 22
cindy 27
herry 26
可以使用where过滤查询出成绩大于25岁的学生名字。
select name from student where age>25;
输出：
cindy
herry
编程要求
在右侧编辑器中补充SQL，查询出工作职责涉及hive的并且工资大于8000的公司名称以及工作经验。（其中库名：db1，表名：table1）
student表结构：
INFO TYPE
eduLevel_name String
company_name String
jobName String
salary int
city_code int
responsibility String
workingExp String
本地部分文件内容：
本科,北京联通支付有限公司,大数据开发工程师,10000,530,熟练使用hive等,1-3年
专科,北京联科数创科技有限公司,大数据分析师,8000,530,熟练使用MySQL等数据库,1-3年`
本科,湖南智湘赢播网络技术有限公司,大数据开发工程师,16000,749,熟练使用spark等,3-5年
测试说明
平台会对你编写的代码进行测试：
预期输出：
1-3年 北京联通支付有限公司
1-3年 深圳市德科信息技术有限公司广州分公司
开始你的任务吧，祝你成功！
----------禁止修改----------
create database if not exists db1;
use db1;
create table if not exists table1(
eduLevel_name string comment '学历',
company_name string comment '公司名',
jobName string comment '职位名称',
salary int comment '薪资',
city_code int comment '城市编码',
responsibility string comment '岗位职责',
workingExp string comment '工作经验'
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
truncate table table1;
load data local inpath '/root/aaa.txt' into table table1;
----------禁止修改----------
----------Begin----------
SELECT workingExp, company_name
FROM table1
WHERE responsibility LIKE '%hive%' AND salary > 8000;
----------End----------
第2关：group by操作
任务描述
本关任务：实现不同工作年限的平均工资需求。
相关知识
group by
group by表示按照某些字段的值进行分组，有相同的值放到一起，需要注意的是select后面的非聚合列必须出现在group by中；
假设存在st表：
city salary job
长沙 7000 大数据开发
北京 10000 大数据开发
广州 11000 大数据开发
长沙 7000 大数据开发
可以使用group by求出不同省份的平均工资：
select city,avg(salary)from st group by city;
输出：
长沙 7000
北京 10000
广州 11000
编程要求
在右侧编辑器中补充SQL，计算不同工作年限以及其平均工资并且过滤出平均工资大于10000的。（其中库名：db1，表名：table1）
table1表结构：
INFO TYPE
eduLevel_name String
company_name String
jobName String
salary int
city_code int
responsibility String
workingExp String
本地部分文件内容：
本科,北京联通支付有限公司,大数据开发工程师,10000,530,熟练使用hive等,1-3年
专科,北京联科数创科技有限公司,大数据分析师,8000,530,熟练使用MySQL等数据库,1-3年
本科,湖南智湘赢播网络技术有限公司,大数据开发工程师,16000,749,熟练使用spark等,3-5年
测试说明
平台会对你编写的代码进行测试：
预期输出：
17000.0 3-5年
20000.0 5-10年
开始你的任务吧，祝你成功！
----------禁止修改----------
create database if not exists db1;
use db1;
create table if not exists table1(
eduLevel_name string comment '学历',
company_name string comment '公司名',
jobName string comment '职位名称',
salary int comment '薪资',
city_code int comment '城市编码',
responsibility string comment '岗位职责',
workingExp string comment '工作经验'
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
truncate table table1;
load data local inpath '/root/t1.txt' into table table1;
----------禁止修改----------
----------Begin----------
SELECT AVG(salary) AS avg_salary, workingExp
FROM table1
GROUP BY workingExp
HAVING AVG(salary) > 10000;
----------End----------
第3关：join操作
任务描述
本关任务：通过关联求出每个城市名的平均工资。
相关知识
Hive只支持等值连接，即ON子句中只能使用等号连接
假设存在表a:
id name
1 bob
2 lily
3 herry
表b:
cid score
1 80
2 90
5 60
内连接(JOIN)
内连接指的是把符合两边连接条件的数据查询出来：
select a.name,b.score from a join b on a.id=b.cid;
输出结果：
bob 80
lily 90
左外连接（LEFT OUTER JOIN）
左表全部查询出来，右表不符合连接条件的显示为空：
select a.name,b.score from a left outer join b on a.id=b.cid;
输出结果：
bob 80
lily 90
herry null
右外连接（RIGHT OUTER JOIN）
右表全部查询出来，左表不符合连接条件的显示为空：
select a.name,b.score from a right outer join b on a.id=b.cid;
输出结果：
bob 80
lily 90
null 60
全外连接（FULL OUTER JOIN）
左右表符合连接条件和不符合连接条件的都查出来，不符合的显示空：
select a.name,b.score from a full outer join b on a.id=b.cid;
输出结果：
bob 80
lily 90
herry null
null 60
左半开连接（LEFT SEMI JOIN)
查询出满足连接条件的左边表记录，需要注意的是select和where语句中都不能使用右表的字段。
Hive不支持右半开连接：
select a.name from a LEFT SEMI JOIN b on a.id=b.cid;
输出结果：
bob
lily
编程要求
在右侧编辑器中补充SQL，求出表table2中所有城市名的平均工资。（其中库名：db1，表名：table1，表名：table2）
表table1结构：
INFO TYPE
eduLevel_name String
company_name String
jobName String
salary int
city_code int
responsibility String
workingExp String
table1本地部分文件内容：
本科,北京联通支付有限公司,大数据开发工程师,10000,530,熟练使用hive等,1-3年
专科,北京联科数创科技有限公司,大数据分析师,8000,530,熟练使用MySQL等数据库,1-3年
本科,湖南智湘赢播网络技术有限公司,大数据开发工程师,16000,749,熟练使用spark等,3-5年
表table2结构：
INFO TYPE
city_code int
city_name String
table2本地部分文件内容：
538,上海
653,杭州
749,长沙
763,广州
测试说明
平台会对你编写的代码进行测试：
预期输出：
8000.0 上海
9000.0 北京
NULL 天津
12000.0 广州
7500.0 杭州
10000.0 深圳
12000.0 长沙
开始你的任务吧，祝你成功！
----------禁止修改----------
create database if not exists db1;
use db1;
create table if not exists table1(
eduLevel_name string comment '学历',
company_name string comment '公司名',
jobName string comment '职位名称',
salary int comment '薪资',
city_code int comment '城市编码',
responsibility string comment '岗位职责',
workingExp string comment '工作经验'
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
truncate table table1;
load data local inpath '/root/t2.txt' into table table1;
create table if not exists table2(
city_code int comment '城市编码',
city_name string comment '城市名'
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
truncate table table2;
load data local inpath '/root/t22.txt' into table table2;
----------禁止修改----------
----------Begin----------
SELECT AVG(t1.salary) AS avg_salary, t2.city_name
FROM table2 t2
LEFT JOIN table1 t1 ON t2.city_code = t1.city_code
GROUP BY t2.city_name
ORDER BY t2.city_name;
----------End----------
有任何问题都可以随时关注私信！
