一、常用數(shù)據(jù)庫命令

1、查詢有哪些數(shù)據(jù)庫show databases

2、查詢有哪些數(shù)據(jù)表：show tables

3、顯示所有函數(shù)：show functions

4、使用use databasename;可以切換到某個數(shù)據(jù)庫下

示例(切換到test數(shù)據(jù)庫)：usetest

5、查看當(dāng)前數(shù)據(jù)庫：select current_database()

6、查詢數(shù)據(jù)表有哪些字段及字段詳情：describe tablename

示例(查詢staged_employees數(shù)據(jù)表)：describe staged_employees

可以簡寫為desc staged_employees

結(jié)果如下：

7、查看指定數(shù)據(jù)庫里有哪些數(shù)據(jù)表：SHOW TABLES IN DbName;

示例：查看xiaoxiao數(shù)據(jù)庫下面有哪些表

SHOW TABLES IN xiaoxiao

8、獲得表的建表語句：

示例(查看創(chuàng)建emailtest這個表使用的語句方法)：show create table emailtest

9、查看數(shù)據(jù)庫的描述信息和文件目錄位置路徑信息

示例(查看datetest數(shù)據(jù)庫的描述信息和文件目錄位置信息)：describe databasedatetest;

二、創(chuàng)建數(shù)據(jù)庫

創(chuàng)建xiaoxiao數(shù)據(jù)庫：

create database xiaoxiao;

二、創(chuàng)建數(shù)據(jù)表

create ? table staged_employees (

id int ? comment 'id',

user_name ? string comment 'user name')

三、刪除數(shù)據(jù)庫

刪除數(shù)據(jù)庫的時候，不允許刪除有數(shù)據(jù)的數(shù)據(jù)庫，如果數(shù)據(jù)庫里面有數(shù)據(jù)則會報錯。如果要忽略這些內(nèi)容，則在后面增加CASCADE關(guān)鍵字，則忽略報錯，刪除數(shù)據(jù)庫。

DROP DATABASE DbName CASCADE(可選);

DROP DATABASE IF EXISTS DbName CASCADE;

三、刪除數(shù)據(jù)表

drop table staged_employees

四、刪除數(shù)據(jù)表中所有內(nèi)容

刪除emaitest數(shù)據(jù)表中的所有內(nèi)容。

insert overwrite table emailtest select * from emailtest where 1=0

五、更改表名

1、更改表名

-- 重命名表名 ALTER TABLE table_name RENAME TO new_table_name;

六、數(shù)據(jù)表添加字段：

alter table lemailtest add columns(time int comment 'now time')

七、HIVE統(tǒng)計函數(shù)

1、count(1)與count(*)得到的結(jié)果一致，包含null值。count(字段)不計算null值

2、集合統(tǒng)計函數(shù)

2.1個數(shù)統(tǒng)計函數(shù): count

語法: count(*), count(expr), count(DISTINCT expr[, expr_.])

返回值: int

說明: count(*)統(tǒng)計檢索出的行的個數(shù)，包括NULL值的行；count(expr)返回指定字段的非空值的個數(shù)；count(DISTINCTexpr[, expr_.])返回指定字段的不同的非空值的個數(shù)

舉例：

hive> select count(*) from lxw_dual;

20

hive> select count(distinct t) from lxw_dual;

10

2.2總和統(tǒng)計函數(shù): sum

語法: sum(col), sum(DISTINCT col)

返回值: double

說明: sum(col)統(tǒng)計結(jié)果集中col的相加的結(jié)果；sum(DISTINCT col)統(tǒng)計結(jié)果中col不同值相加的結(jié)果

舉例：

hive> select sum(t) from lxw_dual;

100

hive> select sum(distinct t) from lxw_dual;

70

七、插入數(shù)據(jù)到emailtest數(shù)據(jù)表中(追加數(shù)據(jù)到原有的數(shù)據(jù)表中)

讀取fx01數(shù)據(jù)表數(shù)據(jù)的一行，然后插入到emailtest數(shù)據(jù)表，對emailtest數(shù)據(jù)表原有的數(shù)據(jù)不會動的。

insert into table emailtest

select * from fx01 limit 1

八、插入數(shù)據(jù)倒emailtest數(shù)據(jù)表中(覆蓋原有數(shù)據(jù)表的數(shù)據(jù)，相當(dāng)于把原有數(shù)據(jù)表先清空，再寫入新的數(shù)據(jù))

從fx01數(shù)據(jù)表讀取數(shù)據(jù)寫入到emailtest數(shù)據(jù)表，對原有emailtest數(shù)據(jù)表數(shù)據(jù)清空處理。

INSERT OVERWRITE TABLE emailtest

SELECT email,y,m,d

FROMfx01where m=06 and d=19

九、關(guān)鍵詞匹配——like

1、從fx01表中查找列content中包含"制度"的行數(shù)信息

Select* from fx01 where content like '%制度%'

十、根據(jù)某個關(guān)鍵字去匹配，然后去設(shè)置新的關(guān)鍵詞case…when..方法

1、case用法一：CASE? 條件判斷函數(shù) 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這樣的功能，即如果字符串包含‘語文’就怎么怎么樣，包含‘?dāng)?shù)學(xué)’就怎么怎么樣，包含‘英語’就怎么怎么樣，like是用于where中的，放在case when里面是無效的，可以用instr()這個函數(shù)來查找這些字符出現(xiàn)的位置，代替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實現(xiàn)topN，使用order by和limit組合方式實現(xiàn)。Order by是進(jìn)行降序排列，limit是選取多少。默認(rèn)按升序排列，如果要按降序排列在末尾加上DESC，ASC是升序。

取排名TOP 5的數(shù)據(jù)。

select distinct company,count(company) as num from fx01 where m=05 group by company order by num DESC limit5

十二、數(shù)據(jù)表多表連接——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、兩表相關(guān)聯(lián)示例

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、三表相關(guān)聯(lián)示例

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

十三、實現(xiàn)某一列字段關(guān)鍵詞統(tǒng)計——split+explode函數(shù)

0stu表數(shù)據(jù):

stu:

idname

hello,youzm2008

hello,mezm2015

1實現(xiàn)單詞計數(shù):(列轉(zhuǎn)行)---> split切分+explode(炸開)

1.0數(shù)據(jù)拆分成數(shù)組

select split(id,',') fromstu;得到數(shù)組

[hello,you]

[hello,me]

1.1繼續(xù)將數(shù)組拆分(hive explode函數(shù)會將數(shù)組繼續(xù)拆分成單個字符)

select explode(split(id,','))from stu;窗體函數(shù)

hello

you

hello

me

1.2分組統(tǒng)計:

select t1.c1, count(1) from (select explode(split(id,',')) as c1from stu)t1 group by t1.c1;

hello 2

you 1

me 1

案例實現(xiàn)需求：統(tǒng)計“關(guān)鍵詞”這一列每個關(guān)鍵詞出現(xiàn)的次數(shù)，把數(shù)字去掉，只統(tǒng)計中文關(guān)鍵詞

表名：TesttableIDkewordymd

1北京；廣州；深圳；貴州201728

2重慶；河南201725

712345555201795

實現(xiàn)語句：

Select explode(split(keyword, ';')) asc1from testtable? where keword' rlike '^[\\u4e00-\\u9fa5]+$'

總結(jié)

以上是生活随笔為你收集整理的hive插入多条数据sql_HIVE sql使用总结的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。