大数据开发第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)