Hive SQL 练习(这个秒退是怎么回事啊?写了半天 东西都没了,瞬间整个人都凌乱了)
首先将练习用的两张表 上传到hdfs上命令如下:hadoop fs -put /opt/dep.txt /dep.txthadoop 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 delimitedfields terminated by 't' row format delimited fileds terminated by 't' 表示将传进来的文件按照空格分割 写入到表格中 闯入文件 employee.txtload 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_idfrom employee dw_employee 为创建好的更改好字段的表select * from dw_employee查询表结构语句describe formatted dw_employee同理更改第二张表的表结构create table dw_department asselect cast(dep_id as int)dep_id,dep_name,addressfrom 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_copycreate 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 降序 descselect * 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 求出每个部门的最高薪水、最低薪水、平均薪水、总薪水、总人数selectcount(*) ,sum(salary) ,avg(salary) ,max(salary) ,min(salary) from dw_employeegroup by dep_id; ----求出总人数超过两人的部门的最高 最低 平均 总薪水 selectcount(*) ,sum(salary) ,avg(salary) ,max(salary) ,min(salary) from dw_employeegroup 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=falseselect a.* ,b.* from dw_employee a, (select avg(salary)avesalary from dw_employee)bwhere 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 existsunion 去重union all 不去重 薪水大于8000或者小于2000或者等于5000的员工select * from dw_employee where salary>8000unionselect * from dw_employee where salary <2000unionselect * from dw_employee where salary=5000 交集 intersect 差集 minus lefter outer join right outer join
)
2C9%5BUV9(~OB6%24S_thumb.jpg)
