//显示Hive内置函数
show functions;
//查询多个字段用逗号分隔
select name, money from sale limit 10;
//数值类型可以进行加减乘除
select money + 1 from sale limit 10;
//平方
select pow(money,2) from sale limit 10;
//模（取余）
select pmod(mon+1,2) from sale limit 10;
//强制类型转换
select cast(money as double) from sale limit 10;
select cast(money as double) from sale where cast(substr(time,9,2) as int) >=7 and cast(substr(time,9,2) as int) <=9
//统计前10条的数据的个数
select count(*) from sale limit 10;
//将string类型的值连接起来
select contact(id, age) from sale limit 10;
//查询json格式的数据
Select get_json_object(‘{“name”:“zhangsan”,“age”:“18”}’,‘$.age’) from sale;
//字符串的长度
select length (id) from sale limit 10;
//查询字uid中第9个位置之后字符串ee第一次出现的位置
select locate (“ee”,id,9) from sale limit 10;
//表的嵌套(具体实现功能自己编写，可以分组，排序，多次嵌套等等)
select count(distinct t.id) as cn from (select * from sale where price <= 300 and order = 1) t;
select t.id from(select uid,count(*) as cn from (select * from sale where price <300)e group by e.id having cn>2) t;
select id,count(*) as cn from sale group by id, keyword having cn>=3 and keyword like ‘%浅浅%’;
select id,count(*) as cn from sale where keyword like ‘%浅浅%’ group by id, having cn>=3; //(先过滤再分组)
//like的使用，模糊查询，%表示任意长度的字符
select * from a where url like ‘http%’ limit 10;
//group by 分组查询
select price, order,count(*) as cn from sale group by price, order;
//having分组筛选，必须用于group by分组查询之上
select price, order,count(*) as cn from sale group by price, order having cn>=3;
//取出表中的前10行数据放到sale2表中
create table sale2 as select * from sale limit 10;//（sale2不存在的情况下）
insert overwrite table sale2 select * from sale limit 10;//（sale2已经存在的情况下）
//join…on，查询两表之间的共同项，相当于两表的交集
select * from sale1 m join sale2 n on m.id=n.id;
select m.id,n.keyword from sale1 m join sale2 n on m.id=n.id;
//left outer join…on(左外连接，跟左边的表条目相同，join不上的为null)
select * from sale1 m left join sale2 n on m.id=n.id;
//right outer join…on(右外连接，跟右边的表条目相同，join不上的为null)
select * from sale1 m right join sale2 n on m.id=n.id;
//降序
select id,count(*) as cn from sale group by id order by cn desc
//创建视图
create view 视图名 as select * from 表 where……
//删除视图
drop view 视图名
浙公网安备 33010602011771号