ToB企服应用市场:ToB评测及商务社交产业平台

标题: hiveSQL语法 [打印本页]

---

作者: 玛卡巴卡的卡巴卡玛    时间: 2024-12-9 10:10
标题: hiveSQL语法
数据库操作

创建数据库

create database 库名;
create database if not exitst 库名; -创建之前先判定数据库是否已经存在，如果不存在才创建
create database 库名 location 'hdfs路径';  -创建数据库并指定它在hdfs上的存储位置
使用数据库

use database 库名;
检察数据库具体信息

desc database 库名;
删除数据库

drop database 库名;  -只能删除空数据库，如果数据库内里有数据表，就会报错
drop database 库名 cascade;  -逼迫删除数据库，包含数据库内里的表一起删除


数据表操作

创建数据库表

create [external] table [if not exitst] 表名(
列名 数据类型 [comment colcommrnt],...)
EXTERNAL，创建外部表 PARTITIONED BY， 分区表 CLUSTERED BY，分桶表 STORED AS，存储格式 LOCATION，存储位置
底子建表语法

只管建表语法比力复杂，现在我们暂时未打仗到分区、分桶等概念。 以是，创建一个简单的数据库表可以有如下SQL：
create table 表名(
列名 数据类型,
列名2 数据类型);
--创建一样简单的表

1. create table test(
2. id int,
3. name string,
4. gender string
5. );

复制代码

数据类型

Hive中支持的数据类型还是比力多的  其中使用比力多的类型有：
简单类型

• 布尔类型：  boolean（只有true和flase两个值）
  
• 数字类型：  float（单精度小数）int（整数）double（双精度小数）
  
• 字符串类型： char（固定长度字符串） varchar（变长字符串） string（没有限定的字符串）
  
• 日期时间类型： date（年月日时分秒毫秒）在工作中不会有人用date类型，hive都是用string存储时间
  

复杂类型

• 数组类型： array<简单类型> 雷同于python内里的列表。
  
• 如果要存储一个全部都是整数的数组    array<int>
  
• 映射类型： map<基本类型, 基本类型> 是一个键值对格式，雷同与python内里字典
  
• 要存储的是科目标分数，例如数学多少分 语文多少分   map<string, int>
  

删除表

drop table 表名;

1. #删除刚刚创建的test表
2. drop table test;

复制代码

表分类

hive中可以创建的表的类型有：

• 内部表
  
• 外部表
  
• 暂时表
  
• 分区表
  
• 分桶表
  

内部表和外部表

create table 创建的表格都是内部表，通过 create external table 创建的表格，就是外部表。
内部表又称管理表,内部表数据存储的位置由hive.metastore.warehouse.dir参数决定（默认：/user/hive/warehouse），用drop table语句删除内部表会直接删除元数据（metadata）及存储数据，因此内部表不得当和其他工具共享数据。
外部表是指表数据可以在任何位置，通过LOCATION关键字指定。 数据存储的不同也代表了这个表在理念是并不是Hive内部管理的，而是可以随意暂时链接到外部数据上的。 以是，在删除外部表的时候， 仅仅是删除元数据（表的信息），不会删除数据自己。
内部表和外部表的区别

• 内部表可以用truncate table清空，也可以用drop table删除
  
• 外部表是不能通过truncate table执行清空操作的；
  
• 外部表通过drop table删除，只会删除mysql中表格的元数据，但是无法删除hdfs内里表格的文件夹以及数据文件的；
  

因此在数据堆栈中，大数据的表格或者业务数据的表格，一样平常都会使用外部表进行创建和存储。


创建内部表

创建一个t1表

1. create table t1(
2. id int,
3. name string,
4. mobile char(11),
5. hobby array<string>,
6. score map<string,double>
7. );

复制代码

往表格中插入数据：
1.只插入简单类型的字段数据
inset into 表(列名) values(值);

1. insert into t1(id,name,mobile) values(101,'curry','18878984708');
2. insert into t1(id,name,mobile) values(102,'kobe','12343547896');

复制代码

hive数据库所有的sql操作都是高耽误的，如果操作的数据量小于128M，那么我们可以去启动本地设置的开关，加快数据的操作
本地模式开关：set hive.exec.mode.local.auto=true;

2.插入的数据包括了复杂类型
insert into 表(列名) select 查询;

1. insert into t1(id,name,mobile,hobby,score)
2. select 103,'james','15567589087',array('sing','rap'),
3. map('math',cast(98.5 as double),'chinese',cast(99 as double),'english',cast(92.5 as double));

复制代码

hive数据库中，每一次的 insert 操作，在表格的文件夹内里，都会天生一个新的当前的数据文件。


表格的分隔符

• 表格字段之间的默认分隔符是 ^A，通过键盘的 ctrl + V + A 输出的，在数据库内里的编码格式是 \001
  
• 数组和映射类型，元素之间的分隔符是 ^B，通过键盘的 ctrl + V + B 输出的，在数据库内里的编码格式是 \002
  
• 映射类型内里，关键字和值之间，分隔符是 ^C，通过键盘的 ctrl + V + C 输出的，在数据库内里的编码格式是 \003
  

在创建表格的同时，强调分隔符的类型：

1. create table t2(
2. id int,
3. name string,
4. mobile char(11),
5. hobby array<string>,
6. score map<string,int>
7. ) row format delimited fields terminated by  ','         --字段分隔符
8. collection items terminated by  '/'         --元素分隔符
9. map keys terminated by  '-';         --键值对分隔符

复制代码

插入数据

1. insert into t2
2. select 101,'aa','18899997777',array('唱歌','跳舞'),map('语文',88,'数学',91);

复制代码

将数据文件导入到hive表格中

在hive中比力少用 insert into 添加数据，我们一样平常都是用文件的方式来导入数据。

1. create table t3(
2. id int,
3. name string,
4. age int
5. )row format delimited fields terminated by  ',';

复制代码

1.直接通过hadoop fs -put命令，从linux上传数据文件到hdfs对应的表格文件夹中：

1. hadoop fs -put /usr/t3.txt /user/hive/warehouse/myhive.db/t3

复制代码

2.通过sql语句，进行数据的导入，在hive数据库内里运行的：
load data local inpath 'linux文件的位置和名字' into table 表名;

1. load data local inpath '/usr/t3.txt' into table t3;

复制代码

3.将数据文件同一的上传到hdfs的其他的文件夹中，例如 /datas，然后再从hdfs的 /datas 内里加载到表格中
load data inpath 'hdfs文件的位置和名字' into table 表名;

1. hadoop fs -put /usr/t3.txt /datas/ --先把文件上传到hdfs

复制代码

1. load data inpath '/datas/t3.txt' into table t3;

复制代码

注：t3.txt文件的分隔符和t3表格的分隔符一定要同等，如果不同等，读取到的数据都是NULL


创建外部表

1. create external table t4_ex(
2. id int,
3. name string,
4. age int
5. );
6. insert into t4_ex values(101,'aa',18);

复制代码

内部表和外部表的转换

将内部表转换成外部表：
alter table 内表 set tblproperties ('EXTERNAL'='TRUE');

1. alter table t3 set tblproperties('EXTERNAL'='TRUE');

复制代码

将外部表转换成内部表：
alter table 外表 set tblproperties ('EXTERNAL'='FALSE');

1. alter table t3 set tblproperties ('EXTERNAL'='FALSE');

复制代码

创建表格的时候，可以指定表格的文件夹在哪里

内部表和外部表都可以指定表格的文件夹的位置：location

1. create table t5(
2. id int,
3. name string,
4. age int
5. )location '/user/hive/warehouse/bigdata.db/t4_ex';

复制代码

也可以用location让多个表格来共享同一个文件夹的数据：

1. create table t6(
2. id int,
3. name string,
4. age int
5. )location '/user/hive/warehouse/bigdata.db/t4_ex';

复制代码

这种建表的方式是不会在hdfs上天生自己的表格文件夹的。

创建暂时表

暂时表的关键字：temporary table

1. create temporary table t6_tmp(
2. id int,
3. name string,
4. age int
5. );

复制代码

• 暂时表在当前的数据库中，不会在hdfs中创建表格的文件夹。
  
• 插入到暂时表的数据可以被正常的使用。
  
• 如果退出登录重新进入hive数据库，那么暂时表被删除掉了。
  
• 暂时表的数据被存放在内存中的，如果退出内存就被清空了，暂时表也被删除了。
  

创建分区表

分区表的目标：是为了加快表格查询和筛选数据的服从，是在where的阶段起到加快作用的
创建一个分区表：
create table 表名(
列名 数据类型
) partitioned by (表格中没有的新字段 数据类型);

1. create table stu_part(
2. stuid int,
3. stuname string,
4. age int
5. ) partitioned by (class string)
6. row format delimited fields terminated by ',';

复制代码

父子分区的结构：在hive内里语法没有限定分区必须是几层的结构，但项目中一样平常最多就是两层。
create table 表名(
列名 数据类型
) partitioned by (表格中没有的新字段1 数据类型, 新字段2 数据类型);


将不同的数据文件，导入到表格不同的分区中：
load data local inpath 'linux文件的位置和名字' into table 表名 partition (分区字段=分区值);

1. load data local inpath '/root/s2.dat' into table stu_part partition (class='002');

复制代码

检察表格有哪些分区：show partitions

1. show partitions stu_part;

复制代码

删除分区：drop partition

1. alter table stu_part drop partition (class='002');

复制代码

直接创建一个空分区：add partition

1. alter table stu_part add partition (class='003');

复制代码

a. 静态分区的导入方式

通过上面的load data的方法进行文件数据的导入，需要自己指定一个分区来导入文件的内容为静态分区导入。
现在有一个现成的表格，要将表格的内容导入到分区中

1. -- 创建一个普通的表格emp
2. create table emp(
3. empno int,
4. ename string,
5. job string,
6. mgr int,
7. hiredate string,
8. sal float,
9. comm float,
10. deptno int
11. ) row format delimited fields terminated by ',';
13. -- 导入表格数据
14. load data local inpath '/root/emp.txt' into table emp;

复制代码

接着创建一个emp的分区表，按照部门去创建分区：

1. -- 创建一个部门分区的emp表
2. create table emp_dept_part(
3. empno int,
4. ename string,
5. job string,
6. mgr int,
7. hiredate string,
8. sal float,
9. comm float
10. ) partitioned by (deptno int)
11. row format delimited fields terminated by ',';

复制代码

最后读取emp表的数据，按照部门，导入到emp_dept_part中不同的分区中：

• insert overwrite table 表名;          覆盖写入数据
  
• insert into table 表名;                   追加写入数据
  

1. insert overwrite table emp_dept_part partition (deptno=10)
2. select empno,ename,job,mgr,hiredate,sal,comm
3. from emp
4. where deptno=10;
6. insert overwrite table emp_dept_part partition (deptno=20)
7. select empno,ename,job,mgr,hiredate,sal,comm
8. from emp
9. where deptno=20;

复制代码

b. 动态分区的导入方式

只能通过 insert overwrite / into table 来导入数据。不能通过load data导入数据
动态导入是根据select内里字段的内容，来主动的判定数据应该放在哪个分区中。

1. --写入数据之前，动态分区还需要两个开关
2. -- 动态分区的开关
3. set hive.exec.dynamic.partition=true;
4. -- 打开动态分区中的非严格模式
5. set hive.exec.dynamic.partition.mode=nonstrict;
6. -- 导入数据
7. insert overwrite table emp_dept_part partition (deptno)
8. select empno,ename,job,mgr,hiredate,sal,comm,
9. deptno
10. from emp;

复制代码

再举一个例子：

1. -- 创建一个emp的表格，用工资等级来分区
2. create table emp_sal_part(
3. empno int,
4. ename string,
5. job string,
6. mgr int,
7. hiredate string,
8. sal float,
9. comm float,
10. deptno int
11. ) partitioned by (grade string)
12. row format delimited fields terminated by ',';
14. -- 导入emp的数据
15. insert overwrite table emp_sal_part partition (grade)
16. select emp.*,
17. case when sal<2000 then 'C'
18. when sal<3000 then 'B'
19. else 'A'
20. end grade
21. from emp;

复制代码

动态分区和静态分区的区别

• 动态分区只有insert导入数据的方式，静态可以通过insert或者load data来导入数据
  
• 动态分区在导入的时候不需要指定分区的值，通过select语句的字段来主动分区；静态分区需要自己指定分区值来导入数据
  
• 动态分区在导入的时候需要打开动态开关以及非严酷模式的开关
  
• 在分区确定和数据量一样的情况下，静态分区导入的速度比动态更快一点，但是动态导入更加方便
  

创建分桶表

分区是当表格很大的时候，为了加快where的服从，并且进行更加精细化的数据的管理，例如每天分区，或者每个地域分区，每个种别分区等。
分桶表是在数据分组统计的时候（group by），或者是在进行表格团结查询的时候，进行查询加快使用的表格结构。
分桶的操作是根据哈希算法，将分桶字段转换成数字除以分桶的数目根据余数来进行分桶划分的。
create table 表名(
列名 数据类型
)clustered by (表格已经存在的字段) into 分桶数目 buckets;

1. --创建一个分桶表
2. create table emp_clu(
3. empno int,
4. ename string,
5. job string,
6. mgr int,
7. hiredate string,
8. sal float,
9. comm float,
10. deptno int
11. ) clustered by (empno) into 4 buckets
12. row format delimited fields terminated by ',';

复制代码

• 通过数据文件来导入数据到分桶表格中（分桶表的数据导入不能使用本地模式的）
  

1. load data local inpath '/root/emp.txt' into table emp_clu;

复制代码

• 通过已有的表格进行分桶表数据的导入
  

1. insert overwrite table emp_clu select * from emp;

复制代码

分区表和分桶表区别

• 分区是用的新字段，分桶是已有字段
  
• 分区是where加快，分桶是group by 和Join加快
  
• 分区是划分不同的文件夹存储的，分桶是划分不同的文件来存储的
  
• 分区是自己指定的规则，分桶是使用哈希算法
  

为什么分桶可以加快group by的聚合盘算的速度：
如果有一个表格，只有一个文件，文件是128M，盘算的时候将这个文件当成一个整体，使用一个map和一个reduce进行数据统计的；
分桶就是将这个128M的文件拆分成多个文件来存储，例如拆分成四个桶文件，那么盘算的时候就会有4个reduce参与数据的盘算和整合，那么数据的盘算速度就会变快。

为什么分桶可以加快join的表连接的速度：
用于加快join的分桶，两个表格的分桶的数目，必须是相同的数目或者是成倍数关系的数目。
通过哈希的余数关系，镌汰join中笛卡尔积数目标效果。

一个表格的分桶数目是怎么决定的：
表格的总数据量大小/(blocksize*2)
如果有个表总数目是4个GB，那么 4096/256=16 桶


创建外部的分区表

1. create external table user_ext_part(
2. id int,
3. name string,
4. age int
5. ) partitioned by (y int)
6. row format delimited fields terminated by ','
7. location '/datas';

复制代码

datas文件夹的结构如下：

直接创建表格，指定了对应的目录，也读取不到数据，由于表格是个分区表，只有表格的结构，没有分区的结构。以是我们需要去新建分区，让分区的目录和分区的值一一对应。

1. alter table user_ext_part add partition (y=2020) location '/datas/20240701';
2. alter table user_ext_part add partition (y=2021) location '/datas/20240702';
3. alter table user_ext_part add partition (y=2022) location '/datas/20240703';

复制代码

表格的存储方式以及对应的压缩格式

检察表格建表的基本情况：show create table 表名;

1. show create table t3;

复制代码

• textfile存储类型
  

hive表格默认存储格式是textfile，存储的内容和格式其实就是一个有行和列构成的文本文档，textfile以行为单位存储的表格（行存表）
textfile比力得当中小型表格（几百万以上的数据，就是大型的表格）。
textfile就是可以直接通过load data的方式导入数据。

1. create table 表名(
2.   列名 数据类型
3. );
5. -- 也可以强调表格的存储类型
6. create table 表名(
7.   列名 数据类型
8. )stored as textfile;

复制代码

对textfile表格的格式进行数据的压缩操作：
set hive.exec.compress.output=true;
set mapred.output.compress=true;
默认的压缩格式：deflate
除了默认的压缩格式，也可以支持 gzip 或者 bzip2 两种压缩

• parquet存储格式
  

parquet也叫做拼接文件。
在重复数据特别多的表格中，parquet可以节省很多的存储空间。
不能使用load data直接导入文本的数据。

1. create table emp_parquet(
2. empno int,
3. ename string,
4. job string,
5. mgr int,
6. hiredate string,
7. sal float,
8. comm float,
9. deptno int
10. )stored as parquet;
12. insert overwrite table emp_parquet select * from emp;

复制代码

parquet格式的压缩类型叫做SNAPPY：

1. create table emp_parquet_ys(
2. empno int,
3. ename string,
4. job string,
5. mgr int,
6. hiredate string,
7. sal float,
8. comm float,
9. deptno int
10. ) stored as parquet
11. tblproperties ('parquet.compression'='SNAPPY');
13. insert overwrite table emp_parquet_ys select * from emp;

复制代码

• sequencefile存储格式
  

sequencefile叫做序列文件格式，占用的空间比textfile要稍大，查询速度比textfile要稍快。数据在数据库的内部是使用键值对的方式来存储的。
sequencefile不能直接通过load data导入数据。

1. create table emp_seq(
2. empno int,
3. ename string,
4. job string,
5. mgr int,
6. hiredate string,
7. sal float,
8. comm float,
9. deptno int
10. ) stored as sequencefile;
12. insert overwrite table emp_seq select * from emp;

复制代码

sequencefile的压缩格式叫做BLOCK。默认的存储叫做RECORD。
set mapred.output.compression.type=BLOCK;

1. create table emp_seq_ys(
2. empno int,
3. ename string,
4. job string,
5. mgr int,
6. hiredate string,
7. sal float,
8. comm float,
9. deptno int
10. ) stored as sequencefile;
12. insert overwrite table emp_seq_ys
13. select * from emp;

复制代码

• orc存储格式
  

中小型表格使用textfile，大型的表格就使用orc。
orc是一种数据列存的格式，查询的速度是最快的，表格自带压缩功能的，压缩格式叫做zlib，每一行数据单独压缩

1. create table emp_orc(
2. empno int,
3. ename string,
4. job string,
5. mgr int,
6. hiredate string,
7. sal float,
8. comm float,
9. deptno int
10. ) stored as orc;
12. insert overwrite table emp_orc
13. select * from emp;

复制代码

表格默认是不支持数据的更新update和删除delete的。

1.一个普通的表格，现在需要修改和删除数据：用insert overwrite table来覆盖

1. -- 修改SMITH工资为1000
2. insert overwrite table emp
3. select empno,ename,job,mgr,hiredate,1000,comm,deptno from emp where ename='SMITH'
4. union all
5. select * from emp where ename!='SMITH';
7. -- 删除SMITH的数据
8. insert overwrite table emp
9. select * from emp where ename!='SMITH';

复制代码

2.如果一个表格一定有修改或者删除的场景，那么就创建成orc类型的表格

1. -- 先打开数据库支持事务的开关
2. set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
3. set hive.support.concurrency=true;
5. create table emp_orc_2(
6. empno int,
7. ename string,
8. job string,
9. mgr int,
10. hiredate string,
11. sal float,
12. comm float,
13. deptno int
14. ) stored as orc
15. tblproperties('transactional'='true');
17. insert overwrite table emp_orc_2
18. select * from emp;
20. update emp_orc_2 set sal=5000 where ename='ALLEN';
21. delete from emp_orc_2 where ename='WARD';

复制代码

DDL语句

1.创建表格

1. create [external] [terporary] table 表名(
2. 列名 数据类型
3. ) partition by (分区字段 数据类型)
4. clustered by (分桶字段) into 分桶数量 buckets by '字段'
5. row format delimited fields terminated by '字段分隔符'
6. collection items terminated by '数组元素分隔符'
7. map keys terminated by '字典键值对分隔符'
8. location '创建文件夹的位置'
9. stored as 存储类型
10. tblproperties ('属性名'='值');

复制代码

2.复制表格

1. --将查询的结果复制成一个表格
2. create table 新表 as select 查询;
4. --直接复制一个表格的结构为一个新的表（只有表结构没有数据）
5. create table 新表 like 要复制的表;

复制代码

3.修改表格的结构

1. --修改字段的结构
2. 1.新增字段
3. alter table 表名 add columns(列名 数据类型)；
4. 给t10表新增gender字段和addr字段
5. alter table t10 add columns(gender char(3), addr string);
7. 2.修改字段的名字和数据类型
8. alter table 表名 change 字段 新字段 新数据类型;
9. alter table t10 change mobile mobile char(11);
10. alter table t10 change id userid string;
12. 3.不能删除表格的字段
14. 4.重命名表格
15. alter table 表名 rename to  新表名;
16. alter table t10 rename to test10;
18. --修改表格的分区信息
19. 1.新增分区
20. alter table 分区表 add partition(分区字段=分区值) [location 'hdfs路径'];
21. 2.删除分区
22. alter table 分区表 drop partition(分区字段=分区值);
23. 3.清空分区
24. alter table 分区表 truncate partition(分区字段=分区值);

复制代码

4.清空整个表格
truncate table 表名;
5.删除表格
drop table 表名;


DML

新增、修改和删除数据。
其中update和delete是不能直接使用的，只能在打开了事件属性的orc表格中可以用。
1.在简单类型中插入一行数据

• insert into 表名(列名) values(值);
  

2.在复杂类型中插入一行数据

• insert into 表名(列名) select array(),map() ...;
  

3.将一个表格的数据覆盖写入到另一个表格中

• insert overwrite table 表名 [partition (分区字段=分区值)] select 查询;
  

4.将一个表格的数据追加写入到另一个表格中

• insert into table 表名 [partition (分区字段=分区值)] select 查询;
  

5.查询一次表格，对数据进行拆分分别写入到N个不同的表格中，进行分表的操作

1. create table emp_a like emp;
2. create table emp_b like emp;
3. create table emp_c like emp;
5. from emp
6. insert overwrite table emp_a select * where empno%3=0
7. insert overwrite table emp_b select * where empno%3=1
8. insert overwrite table emp_c select * where empno%3=2;

复制代码

DQL

select 字段         4
from 表名           1
where 筛选         2
group by 分组     3
having 聚合筛选 5
order by 排序 6
limit 分页; 7

1. select deptno,sum(sal) s from emp group by deptno having s>2000;

复制代码

• limit 分页查询
  

limit  开始行号，连续的取值范围
limit 4,5   从第5行开始，连续取5行数据（开始行号是从0开始的）
limit 3  相称于 limit 0,3   开始值是0，我们可以省略

1. --查询前三行数据
2. select * from emp limit 3;
4. --查询第3行到第5行
5. select * form emp limit 2,3;

复制代码

• 排序的部门
  

hive中排序除了order by之外，还有 sort by，还有 cluster by，还有 distribute by。
--order by排序
不管表格有多大的数据量，都是将表格当成一个整体，放在一个reduce进程中进行排序的。order by排序很慢。
select * from 表 order by 列名 desc|asc;
--sort by排序
是在reduce中进行排序的，有几个reduce，就会在几个不同的范围内分别排序。当reduce等于1的时候，效果和order by 是一样的。
怎样设置reduce的数目：
set mapred.reduce.tasks;          这个开关的默认值是-1，表示没有逼迫限定，reduce的数目是跟着分桶或者文件的多少来决定的。
select * from 表 sort by 列 desc|asc;
--distribute by排序
distribute by 其实就是在查询的过程中，对表格进行分桶的含义。
distribute by 是不能单独运行的，只能和 sort by 结合使用。
select * from 表 distribute by 分桶字段 sort by 排序字段 desc|asc;
-- cluster by排序
其实就是
select * from 表 distribute by 分桶字段 sort by 按照分桶字段进行排序 asc;
select * from 表 cluster by 列;

1. select * from emp distribute by sal sort by sal;
2. select * from emp cluster by sal;
3. 两句话是一样的效果

复制代码

多表查询

1.子查询嵌套

select * from (select deptno,sum(sal) s from emp group by deptno) t;
在hive中，嵌套的子查询，必须要有个别名。
2.多表团结查询

• cross join  笛卡尔积：显示两个表所有大概的连接情况
  
• inner join 内连接：查询出两个表格之间共有的数据部门
  
• left join  外连接之左连接：查询出两个表格的共有数据，然后显示出左边表格独有的数据。
  
• right join  外连接之右连接：查询出两个表格的共有数据，然后显示出右边表格独有的数据。
  
• full join 外连接之全连接：查询出两个表格的共有数据，然后分别显示左表和右表独有的数据。
  

在Hive中，多了一个左半连接的操作：在hive2.x的版本中，不能使用子查询嵌套的语法，用left semi join对嵌套进行语法的替换操作。
select * from a left semi join b on a.xx=b.xx;   这里的*号，代表的不是a和b表的所有字段，只代表a表的所有的字段。

1. -- 查询工资比MILLER要高的员工信息
2. select * from emp where sal>(select sal from emp where ename='MILLER');
4. select * from emp a left semi join emp b on a.ename!='MILLER' and b.ename='MILLER'
5. and a.sal>b.sal;
7. 从查询的效率上讲，左半连接比嵌套查询更好一点。

复制代码

函数

聚合函数

• max()  min()  sum()  avg()  count()
  

单行函数

1. 数字类型

abs() 求绝对值
round() 四舍五入保留小数精度；如果没有小数精度，只是保留整数的部门

• round(1.234545,3)  效果为1.235
  
• round(1.2345)  效果为1
  

floor()  查询离数字最近的小于它的整数，向下取整

• floor(1.999999)  效果为1
  

ceil()  查询离数字最近的大于它的整数，向上取整

• ceil(1.9999)  效果为2
  

power()  数字的幂运算

• power(3,4)  3的4次方，效果为81
  

随机函数：rand() 取随机的一个数字，范围是0-1
取余数，模运算：%

• select 10%3;  效果为1
  

中位数的盘算：先对数组进行排序，取中间的值  percentile(列名, 0.5)

1. create table score(
2. stuid int,
3. score int
4. )row format delimited fields terminated by ',';
6. 1,90
7. 2,88
8. 3,79
9. 4,100
10. 5,92
12. select percentile(score,0.5) from score;

复制代码

2.字符串类型

substr()  连续截取字符串

• substr(字符串，开始位置，连续长度) 开始位置是负数，表示从后往前数
  
• substr(字符串，开始位置) 没有连续长度会取值到字符串的最后一位
  

length()  检察字符串的长度
replace()  字符串的替换

• replace(字符串, '旧数据', '新数据') 用新字符替换旧字符
  

regexp_replace()
translate()  用一串新的数据依次替换前面的一串旧数据
instr()
upper()
lower()
initcap()
lpad()/rpad()
trim()/ltrim()/rtrim()
concat()  拼接字符串。oracle内里只能拼接两个字段，hive中拼接的字段没有数目标限定，hive没有||的拼接符号了

1. select concat(empno,ename,job,mgr) from emp;

复制代码

concat_ws()  拼接的字段只能是字符串类型

• concat_ws(连接符号, 拼接的字段)
  

1. select concat_ws(',',ename,job,hiredate) from emp;
2. select concat_ws(',',ename,job,hiredate,cast(sal as string)) from emp;

复制代码

• concat_ws()不仅可以拼接多个字段，也可以拼接数组的类型
  

1. select concat_ws('-',array('aa','bb','cc'));

复制代码

split(字符串, '切割符号')  拆分字符串,将一个字符串切割成一个数组

1. select split('aaa-bbb-ccc-ddd','-');

复制代码

collect_list()  将一个列的数据，合并成一个数组字段

1. select collect_list(job) from emp;

复制代码

collect_set()  将一个列的数据，合并成一个数组字段并对数据进行去重的处置惩罚

1. select collect_set(job) from emp;

复制代码

辨认json数据的格式，并且对关键字的值进行提取：get_json_object(json字符串, 提取的路径描述)
get_json_object() 函数不管查询出来的数据样式是什么，类型都是字符串。

1. create table test11(
2. userid int,
3. userinfo string
4. );
6. 1^A{"username":"aa","age":18,"gender":"male"}
7. 2^A{"username":"bb","age":17,"gender":"female"}
8. 3^A{"username":"cc","age":19,"gender":"male"}
9. 4^A{"username":"dd","age":17,"gender":"female"}
11. select userid,
12. get_json_object(userinfo, '$.username')
13. from test11;

复制代码

1. create table test12(
2. id int,
3. fruits string
4. )row format delimited fields terminated by '/';
6. 1/{"dt":"20200101","datas":[{"name":"apple","price":1.8},{"name":"pear","price":2.1}]}
7. 2/{"dt":"20200102","datas":[{"name":"apple","price":1.9},{"name":"pear","price":2.0}]}
9. 查询每一天的所有的水果的price信息
10. select get_json_object(fruits, '$.datas[*].price') from test12;

复制代码

3.时间日期类型

add_months()
months_between()
last_day()
next_day()
查询当前的年月日信息： current_date

1. select current_date;

复制代码

检察当前的年月日时分秒毫秒信息： current_timestamp

1. select current_timestamp;

复制代码

检察当前的时间戳数据，累计的秒数时间： unix_timestamp()

1. select unix_timestamp();

复制代码

转换时间戳提取出时间的维度： from_unixtime()

1. select from_unixtime(1738574831,'yyyy-MM-dd HH:mm:ss');

复制代码

进行日期天数的偏移： date_add()

1. select date_add('2024-8-2',5);
2. select date_add('2024-8-2',-5);

复制代码

查询两个日期之间的天数隔断： datediff()

1. select datediff('2024-8-2','2024-7-28');

复制代码

提取日期格式内里的时间维度：year() month() day() hour() minute() second()

1. select minute(current_timestamp);
2. select year(current_timestamp);

复制代码

练习：使用时间函数，查询距离 2024-8-2 最近的上一个星期二的日期。

1. select date_add(next_day('2024-8-2','tue'),-7);

复制代码

4.数据类型转换

cast(值 as 新的类型)

1. select cast(1.666 as string);
2. select cast(1.666 as int);
3. select cast('2024-8-1' as date);

复制代码

5.数组类型

将多个数组合并转换成一个数组： array(值1，值2，值3..)



案例


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4