首先将练习用的两张表 上传到hdfs上
命令如下:
hadoop fs -put /opt/dep.txt /dep.txt
hadoop fs -put /opt/employee.txt /employee.txt
然后开始写练习
创建数据库
create database bs17;
use bs17;
创建员工信息表
create table employee(
emp_id string
,emp_name string
,status string
,salary string
,status_salary string
,in_work_date string
,leader_id string
,dep_id string
)row format delimited
fields terminated by '\t'
row format delimited fileds terminated by '\t' 表示将传进来的文件按照空格分割 写入到表格中
闯入文件 employee.txt
load data inpath '/employee.txt' overwrite into table employee;
查询表
select * from employee;
同理创建第二个表
create table department(
dep_id string
,dep_name string
,address string
)row format delimited
fields terminated by '\t'
传入文件 以空格分割写入到表格中
load data inpath '/dep.txt'overwrite into table department;
查询表
select * from department
之前的创建表 都是采用string字段 这样做的原因是防止传入的数据损失精度失真 以原文件的格式类型为准 txt 通用 string
接下来通过 as 来更改表结构 将字段改为所需要的
create table dw_employee as select
cast(emp_id as int) emp_id
,emp_name
,status
,cast(salary as double)salary
,cast(status_salary as double)status_salary
,cast(in_work_date as date)in_work_date
,cast(leader_id as int)leader_id
,cast(dep_id as int)dep_id
from employee
dw_employee 为创建好的更改好字段的表
select * from dw_employee
查询表结构语句
describe formatted dw_employee
同理更改第二张表的表结构
create table dw_department as
select cast(dep_id as int)dep_id
,dep_name
,address
from department
select * from dw_department
除了 通过 as 复制表 之外 还可以通过 like 来克隆 不过 like 克隆的只有表结构 没有数据
create table employee_clone like employee;
select * from employee_clone
查找employee 表中的 id name 月薪 季薪 年薪
select emp_id
,emp_name
,salary mon_salary
,salary*3 season_salary
,salary*12 year_salary
from dw_employee
查找员工的月工资 月薪加奖金
select emp_id
,emp_name
,salary+status_salary getMoney
from dw_employee
插入数据 id name 其他为null
insert into dw_employee(emp_id,emp_name) values(1111,'aaaa')
nvl 表示条件 如果有字段信息 则表示字段信息 如果为null 则用后的表示
如有职位 则表示 没有则用普通员工代替
如果有入职日期 则表示 没有 则用2015-5-1表示
select emp_id
,emp_name
,nvl(status,'普通员工') job
,nvl(in_work_date,'2015-5-1') in_work_date
from dw_employee
复制员工表，表名为emp_copy
create table emp_copy as select * from dw_employee
机构中有多少种职位(就是将所有的status 聚合展示出来 展示出来几种就有几种)
select distinct status from dw_employee
薪水高于6000的员工
select * from dw_employee where salary>6000
职位是analyst 的员工
select * from dw_employee where status ='analyst'
以小写格式展示职位信息（lower()）
select emp_id
,emp_name
,lower(status)
from dw_employee
忽略大小写匹配职位等于‘ANALYST’的记录
select * from dw_employee where upper(status)='ANALYST'
查询出薪水在5000到8000之间的员工（between and）
select * from dw_employee where salary between 5000 and 8000;
查询出2016年入职的员工
select * from dw_employee where year(in_work_date)=2016
薪水不在5000到8000的员工
select * from dw_employee where salary not between 5000 and 8000;
查询出职位是Manager或者analyst的员工
select * from dw_employee where status in(Manager,analyst);
模糊查询like %
查询出没有岗位工资的员工
select * from dw_employee where status_salary is null;
查询有岗位工资的员工
select * from dw_employee where status_salary is not null;
查询出不在10部门和不再30部门的员工
select * from where dw_employee where dep_id not in(10,30)
DDL 数据定义语言
insert select
create table employee_leader like dw_employee
insert into dw_employee_leader select * from dw_employee where leader_id is null
insert overwrite table dw_employee_leader select * from dw_employee where leader_id is null
排序问题
oder by 升序 asc 降序 desc
select * from dw_employee order by salary desc;
hive 的order by 全排序是通过只设置一个reducer 节点的方式来实现的
通过hive可以很轻松的实现二次排序
select * from dw_employee order by salary desc,salary_status desc;
查询多少个员工
select count(*) from dw_employee
分组表达式 Group by 聚合函数 Count max min avg sum
查询有多少个员工
select count(*) from dw_employee
查询有多少个姓张的员工
select count(*) from dw_employee where emp_name like '%张%'
计算员工的总薪水是多少
select sum(salary) from dw_employee
计算员工的人数总和、薪水综合、平均薪水，最高薪水、最低薪水
select
count(*)
,sum(salary)
,avg(salary)
,max(salary)
,min(salary)
from dw_employee
求出每个部门的最高薪水、最低薪水、平均薪水、总薪水、总人数
select
count(*)
,sum(salary)
,avg(salary)
,max(salary)
,min(salary)
from dw_employee
group by dep_id;
----求出总人数超过两人的部门的最高 最低 平均 总薪水
select
count(*)
,sum(salary)
,avg(salary)
,max(salary)
,min(salary)
from dw_employee
group by dep_id;
having count(*)>2
having 的作用 是在聚合后做一次删选 Having 用来对分组后的结果进行进一步的过滤
select * from dw_employee where salary in (select max(salary) from dw_employee)
-----in 在后面添加子查询结果作为判断条件 如果子查询中有索引的话 in 是用不到这个索引 因此 in对接子查询的效率最低
子查询
查询出最高薪水的人的信息
select * from dw_employee where salary in (select max(salary) from dw_employe)
最低薪水的人
select * from dw_employee where salary in (select min(salary) from dw_employe)
工资高于平均薪水的人的信息
目前 hive目前不支持子查询的非等值查询
所以使用 笛卡尔 乘积的方式
因为参数原因 不允许使用笛卡尔 乘积 所以需要通过指令设置一下让它允许
set hive.strict.checks.cartesian.product=false
select a.*
,b.*
from dw_employee a, (select avg(salary)avesalary from dw_employee)b
where a.salary >b.avgsalary
谁的薪水比小赵的薪水高
select a.*
from dw_employee a,(select salary from dw_employee where emp_name='小赵')b
where a.salary>b.salary
研发部有哪些职位
select status
from dw_employee a
where exists(select dep_id
from department b
where dep_name ='研发部'
and a.dep_id =b.dep_id)
ALL 子查询 Any(子查询)
谁是妖姬的同部门的同事
select * from dw_employee where dep_id in(select dep_id from dw_employee where emp_name ='妖姬')
如果有两个叫妖姬 谁是妖姬的同事???
inner join 是两张表根据关联条件 相互过滤 能够关联上的数据才会出现在结果集中
Exists
not exists
union 去重
union all 不去重
薪水大于8000或者小于2000或者等于5000的员工
select * from dw_employee where salary>8000
union
select * from dw_employee where salary <2000
union
select * from dw_employee where salary=5000
交集 intersect
差集 minus
lefter outer join right outer join