Hive 05 数据查询

1 简单查询

select * from table-name;
select sid,sname from table-name;
select sid , sname , sal , sal*12 from table-name
select sid , sname , sal , comm, sal*12+nvl(comm,0) from table-name	// nvl() 把空值转换成指定的值
select * from table-name where sname is null;
// 使用distinct来去掉重复的记录
select distinct sname, sal from table-name;

2 过滤和排序

select * from emp where id = 10 ;
select * from emp where deptno=10 and sal<2000;
// 模糊查询：查询名字以s打头的员工
select empno,ename,sal from emp where ename like 's%';
// 模糊查询，查询名字含有下划线的员工
select empno,ename,sal from emp where ename like '%\\_%';

// order by + 列/表达式/别名/序号
select * from table-name order by sal;

// 使用序号，开启参数
set hive.groupby.orderby.position.alias=true;
select empno,ename,sal,sal*12 annsal from emp order by 4;