一、常用数据库命令
1、查询有哪些数据库 show databases
2、查询有哪些数据表：show tables
3、显示所有函数： show functions
4、使用use databasename;可以切换到某个数据库下
示例（切换到test数据库）：use test
5、查看当前数据库：select current_database()
6、查询数据表有哪些字段及字段详情：describe tablename
示例（查询staged_employees数据表）：describe staged_employees
可以简写为desc staged_employees
结果如下：
7、查看指定数据库里有哪些数据表：SHOW TABLES IN DbName;
示例：查看xiaoxiao数据库下面有哪些表
SHOW TABLES IN xiaoxiao
8、获得表的建表语句：
示例（查看创建emailtest这个表使用的语句方法）：show create table emailtest
9、查看数据库的描述信息和文件目录位置路径信息
示例（查看datetest数据库的描述信息和文件目录位置信息）：describe database datetest;
二、创建数据库
创建xiaoxiao数据库：
create database xiaoxiao;
二、创建数据表
create table staged_employees (
id int comment 'id',
user_name string comment 'user name')
三、删除数据库
删除数据库的时候，不允许删除有数据的数据库，如果数据库里面有数据则会报错。如果要忽略这些内容，则在后面增加CASCADE关键字，则忽略报错，删除数据库。
DROP DATABASE DbName CASCADE(可选);
DROP DATABASE IF EXISTS DbName CASCADE;
三、删除数据表
drop table staged_employees
四、删除数据表中所有内容
删除emaitest数据表中的所有内容。
insert overwrite table emailtest select * from emailtest where 1=0
五、更改表名
1、更改表名
— 重命名表名 ALTER TABLE table_name RENAME TO new_table_name;
六、数据表添加字段：
alter table lemailtest add columns(time int comment 'now time')
七、HIVE统计函数
1、count(1)与count(*)得到的结果一致，包含null值。count(字段)不计算null值
2、集合统计函数
2.1 个数统计函数: count
语法: count(*), count(expr), count(DISTINCT expr[, expr_.])
返回值: int
说明: count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；count(DISTINCTexpr[, expr_.])返回指定字段的不同的非空值的个数
举例：
hive> select count(*) from lxw_dual;
20
hive> select count(distinct t) from lxw_dual;
10
2.2 总和统计函数: sum
语法: sum(col), sum(DISTINCT col)
返回值: double
说明: sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果
举例：
hive> select sum(t) from lxw_dual;
100
hive> select sum(distinct t) from lxw_dual;
70
七、插入数据到emailtest数据表中（追加数据到原有的数据表中）
读取fx01数据表数据的一行，然后插入到emailtest数据表，对emailtest数据表原有的数据不会动的。
insert into table emailtest
select * from fx01 limit 1
八、插入数据倒emailtest数据表中（覆盖原有数据表的数据，相当于把原有数据表先清空，再写入新的数据）
从fx01数据表读取数据写入到emailtest数据表，对原有emailtest数据表数据清空处理。
INSERT OVERWRITE TABLE emailtest
SELECT email,y,m,d
FROM fx01 where m=06 and d=19
九、关键词匹配——like
1、从fx01表中查找列 content中包含"制度"的行数信息
Select * from fx01 where content like '%制度%'
十、根据某个关键字去匹配，然后去设置新的关键词case…when..方法
1、case用法一：CASE 条件判断函数 CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
select policy,case policy when 'abc' then '测试' else 'ccc' end as policy from fx01 limit 6
2、case用法二：
假如要用到case when又要用到like这样的功能，即如果字符串包含‘语文’就怎么怎么样，包含‘数学’就怎么怎么样，包含‘英语’就怎么怎么样，like是用于where中的，放在case when里面是无效的，可以用instr()这个函数来查找这些字符出现的位置，代替like的功能，这样写就好呐。
case when instr(t.str,’语文’) > 0 then 0
when instr(t.str,’语文’) > 0 then 1
when instr(t.str,’语文’) > 0 then 2
else 3 end
示例：
select t1.policy,case when instr(t1.policy,'信托') > 0 then '信托'
when instr(t1.policy,'张三') > 0 then '张三1'
when instr(t1.policy,'李四') > 0 then '李四1'
when instr(t1.policy,'小明') > 0 then '小明1' else '小红' end from (select distinct policy from fx01 limit 6) t1
十一、order by——TOP N
hive实现topN，使用order by和limit组合方式实现。Order by 是进行降序排列，limit是选取多少。默认按升序排列，如果要按降序排列在末尾加上DESC，ASC是升序。
取排名TOP 5的数据。
select distinct company,count(company) as num from fx01 where m=05 group by company order by num DESC limit5
十二、数据表多表连接——join
多表连接——join两个以上的表
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2);
join支持left join（左连接）、right join（右连接）、full join（全连接）
1、两表相关联示例
select fx01_lanjie.company,fx01_lanjie.lanjie,fx01_yujing.yujing,fx01_lanjie.y,fx01_lanjie.m from fx01_lanjie left join fx01_yujing ON fx01_lanjie.company=fx01_yujing.company
2、三表相关联示例
select fx01_lanjie.company,fx01_lanjie.lanjie,fx01_yujing.yujing,fx01_fangxing.fangxing,fx01_lanjie.y,fx01_lanjie.m from fx01_lanjie left join fx01_yujing ON fx01_lanjie.company=fx01_yujing.company join fx01_fangxing on fx01_lanjie.company=fx01_fangxing.company
十三、实现某一列字段关键词统计——split+explode函数
0 stu表数据:
stu:
id name
hello,you zm2008
hello,me zm2015
1 实现单词计数: (列转行) —> split切分+explode(炸开)
1.0 数据拆分成数组
select split(id,',') from stu; 得到数组
[hello,you]
[hello,me]
1.1 继续将数组拆分(hive explode函数会将数组继续拆分成单个字符)
select explode(split(id,',')) from stu; 窗体函数
hello
you
hello
me
1.2 分组统计:
select t1.c1, count(1) from (select explode(split(id,',')) as c1 from stu) t1 group by t1.c1;
hello 2
you 1
me 1
案例实现需求：统计“关键词”这一列每个关键词出现的次数，把数字去掉，只统计中文关键词
表名：Testtable
|
ID |
keword |
y |
m |
d |
|
1 |
北京；广州；深圳；贵州 |
2017 |
2 |
8 |
|
2 |
重庆；河南 |
2017 |
2 |
5 |
|
7 |
12345555 |
2017 |
9 |
5 |
实现语句：
Select explode(split(keyword, ';')) as c1 from testtable where keword' rlike '^[//u4e00-//u9fa5]+$'
原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/194569.html