Hive SQL 练习(这个秒退是怎么回事啊?写了半天东西都没了,瞬间整个人都凌乱了)...

首先将练习用的两张表上传到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

row format delimited fileds terminated by 't' 表示将传进来的文件按照空格分割 写入到表格中

相关文章：

row format delimited fileds terminated by 't' 表示将传进来的文件按照空格分割写入到表格中