大資料開發第10課 hive DQL 資料查詢語言

# 大資料開發第10課 hive DQL 資料查詢語言
* 内置運算符&内置函數 
  * 關系運算符
    * 等值比較: = 、== 
    * 不等值比較: <> 、!=
    * 小于比較: <
    * 小于等于比較: <=
    * 大于比較: >
    * 大于等于比較: >=
    * 空值判斷: IS NULL
    * 非空判斷: IS NOT NULL
    * IKE比較: LIKE
    * AVA的LIKE操作: RLIKE
    * REGEXP操作: REGEXP 
  * 算術運算符
    * 加法操作: +
    * 減法操作: -
    * 乘法操作: *
    * 除法操作: /
    * 取整操作: div
    * 取餘操作: %
    * 位與操作: &
    * 位或操作: |
    * 位異或操作: ^
    * 位取反操作: ~
  * 邏輯運算符 
    * 與操作: A AND B
    * 或操作: A OR B
    * 非操作: NOT A 、!A
    * 在:A IN (val1, val2, ...)
    * 不在:A NOT IN (val1, val2, ...)
    * 邏輯是否存在: [NOT] EXISTS (subquery)
4. SELECT [ALL | DISTINCT] select_expr, select_expr, ...
   FROM table_reference
   [WHERE where_condition]
   [GROUP BY col_list]
   [HAVING having_condition]
   [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]
   [LIMIT number];

5. hive語句執行順序
** from --> where --> select --> group by -->聚合函數-->having -->order by -->limit           

show databases ;
--drop database aj_shop cascade ;
--drop database online_edu_test cascade ;
-- hive -f xx.sql
show tables;
desc order_product;
show create table order_product;
-- 1建立一個普通表
create table if not exists order_product_pt(
                                               id int,
                                               orderId bigint comment '訂單id',
                                               productId bigint comment '商品id',
                                               productNum bigint comment '商品數量',
                                               productPrice decimal comment '商品價格',
                                               money decimal comment '付款金額',
                                               extra string comment '額外資訊',
                                               createTime string comment '建立時間'
) row format delimited fields terminated by '\t';
--2.向普通表加資料
load data local inpath '/root/data/aj_shop/order_product.txt' overwrite into table order_product_pt;
--3 開啟動态分區
set hive.exec.dynamic.partition.mode=nonstrict;
--4 普通表資料導入分區表
insert overwrite table order_product partition (year) select *,substring(createTime,1,4) as year from order_product_pt;

--訂單付費表
load data local inpath '/root/data/aj_shop/payments.txt' overwrite into table payments;
--商品類目表
create table if not exists product_category_pt(
                                               catId int comment '品類id',
                                               parentId int comment '父id',
                                               catName string comment '分類名稱',
                                               isShow tinyint comment '是否顯示 0：隐藏 1:顯示',
                                               isDel tinyint comment '删除标志 1:有效 -1:删除',
                                               createTime string comment '建立時間',
                                               level int comment '級别'
)
    row format delimited fields terminated by '\t';

load data local inpath '/root/data/aj_shop/product_category.txt' overwrite into table product_category_pt;
insert overwrite table product_category partition (level) select * from product_category_pt;

--商品資訊表
load data local inpath '/root/data/aj_shop/product_info.txt' overwrite into table product_info;

use myhive;
select * from sales_info;
explain select sku_id,count(1) as cnt from sales_info
where sku_id>2
group by sku_id
having cnt>1
order by cnt desc;
--顯示所有的函數和運算符
show functions;
--檢視運算符或者函數的使用說明
describe function +;
--使用extended 可以檢視更加詳細的使用說明
describe function extended +;
--- 省略from 練習測試内置的運算符、函數的功能
select 1+1;
--建立一張虛表dual來滿足于測試需求
--1、建立表dual
create table dual(id string);
--2、加載一個檔案dual.txt到dual表中
--dual.txt隻有一行内容：内容為一個空格
--3、在select查詢語句中使用dual表完成運算符、函數功能測試
select 1+1 from dual;


--關系運算符
--is null空值判斷
select 1 from dual where 'ajtest' is null;

--is not null 非空值判斷
select 1 from dual where 'ajtest' is not null;

--like比較： _表示任意單個字元 %表示任意數量字元
--否定比較：NOT A like B
select 1 from dual where 'ajtest' like 'it_';
select 1 from dual where 'ajtest' like 'it%';
select 1 from dual where not 'ajtest' like 'hadoo_';

--rlike：确定字元串是否比對正規表達式，是REGEXP_LIKE()的同義詞。
select 1 from dual where 'ajtest' rlike '^i.*t#39;;
select 1 from dual where '123456' rlike '^\\d+#39;;  --判斷是否全為數字
select 1 from dual where '123456aa' rlike '^\\d+#39;;

--regexp：功能與rlike相同 用于判斷字元串是否比對正規表達式
select 1 from dual where 'ajtest' regexp '^i.*t#39;;

--算術運算符
--取整操作: div  給出将A除以B所得的整數部分。例如17 div 3得出5。
select 17 div 3;

--取餘操作: %  也叫做取模  A除以B所得的餘數部分
select 17 % 3;

--位與操作: &  A和B按位進行與操作的結果。 與表示兩個都為1則結果為1
select 4 & 8 from dual;  --4轉換二進制：0100 8轉換二進制：1000
select 6 & 4 from dual;  --4轉換二進制：0100 6轉換二進制：0110

--位或操作: |  A和B按位進行或操作的結果  或表示有一個為1則結果為1
select 4 | 8 from dual;
select 6 | 4 from dual;

--位異或操作: ^ A和B按位進行異或操作的結果 異或表示兩個不同則結果為1
select 4 ^ 8 from dual;
select 6 ^ 4 from dual;

--邏輯運算符
--與操作: A AND B   如果A和B均為TRUE，則為TRUE，否則為FALSE。如果A或B為NULL，則為NULL。
select 1 from dual where 3>1 and 2>1;
--或操作: A OR B   如果A或B或兩者均為TRUE，則為TRUE，否則為FALSE。
select 1 from dual where 3>1 or 2!=2;
--非操作: NOT A 、!A   如果A為FALSE，則為TRUE；如果A為NULL，則為NULL。否則為FALSE。
select 1 from dual where not 2>1;
select 1 from dual where !2=1;
--在:A IN (val1, val2, ...)  如果A等于任何值，則為TRUE。
select 1 from dual where 11 in(11,22,33);
--不在:A NOT IN (val1, val2, ...) 如果A不等于任何值，則為TRUE
select 1 from dual where 11 not in(22,33,44);
--邏輯是否存在: [NOT] EXISTS (subquery) 如果子查詢傳回至少一行，則為TRUE。
--select A.* from A where exists (select B.id from B where A.id = B.id)