hive在查询中的过滤
explain select * from emp where deptno = 10 and sal < 2000; --显示具体过程
--查询10号部门的员工
select * from emp where deptno = 10;
--查询名叫KING的员工
select * from emp where ename = ‘KING’; ---注意名字的大小写
--查询部门10号、薪水小于2000的员工
select * from emp where deptno = 10 and sal < 2000;
--模糊查询:查询名字以S开头的员工
select * from emp where ename like 'S%';
--模糊查询:查询名字以_(下划线))的员工
select * from emp where ename like '%_%'; ---错误
select * from emp where ename like '%\\_%';
---------------------hive在查询中的排序
--查询员工信息:员工号、姓名、姓名、月薪,按照月薪排序
select empno,ename,sal from emp order by sal desc|asc; ---列名
---order by 后面可以跟:列名,表达式,别名,序号
select empno,ename,sal * 12 from emp order by sal * 12 desc|asc; ---表达式
select empno,ename,sal * 12 as year_sal from emp order by year_sal desc|asc; ---别名
set hive.groupby.orderby.position.alias=true ----序号设置
select empno,ename,sal * 12 from emp order by 4; ---序号
---查询员信息:按照奖金排序
select empno,ename,sal,comm from emp order by comm desc;
---null 排序:升序:最前面;
降序:最后面