当前位置：首页 > ai >正文

34-Hive SQL DML语法之查询数据-3

ai 2025/8/12 6:03:47

１.内连接 inner join

-- 1.内连接查询  employee employee_address
select e.id,e.name,e_a.city,e_a.street from employee einner join  employee_address e_aon e.id=e_a.id;-- inner join = join 注意了hql中这样写
select e.id as bianhao,e.name as xingming,e_a.city,e_a.street from employee ejoin  employee_address e_aon e.id=e_a.id;-- 等价于 隐式连接写法select e.id as ID,e.name as namess,e_a.city,e_a.street fromemployee e,employee_address e_a
where e.id=e_a.id;

在这里插入图片描述

内连接结果图：
在这里插入图片描述

2.左外连接

其他的连接大家自行学习一下
在这里插入图片描述

左外连接结果图：

在这里插入图片描述

3.Hive中的函数

 2.1函数分类内置函数
用户定义函数 （User-Defined Functions UDF）2.2内置函数
内置函数（build-in）指的是Hive开发实现好，直接可以使用的函数,也叫做内建函数。
官方文档地址：	https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF2.3 String Functions 字符串函数
字符串长度函数：length
字符串反转函数：reverse
字符串连接函数：concat
带分隔符字符串连接函数：concat_ws
字符串截取函数：substr,substring

---  2025-08-11 PM
--table1: 员工表
CREATE TABLE employee(id int comment '员工编号',name string comment '员工姓名',deg string comment '等级身份',salary int comment '工资',dept string comment '部门') row format delimited
fields terminated by ',';--table2:员工家庭住址信息表
CREATE TABLE employee_address (id int comment '员工编号',hno string comment '门牌号',street string comment '街道',city string comment '城市'
) row format delimited
fields terminated by ',';--table3:员工联系方式信息表
CREATE TABLE employee_connection (id int comment '员工编号',phno string comment '手机号',email string comment '邮件地址'
) row format delimited
fields terminated by ',';select * from liushao.employee;
--加载数据到表中
load data local inpath '/export/data/hivedata/employee.txt' into table liushao.employee;
load data local inpath '/export/data/hivedata/employee_address.txt' into table liushao.employee_address;
load data local inpath '/export/data/hivedata/employee_connection.txt' into table liushao.employee_connection;-- 查看数据是否导入成功
select * from liushao.employee;select * from liushao.employee_address;select * from liushao.employee_connection;desc employee;desc t_archer;-- 1.内连接查询  employee employee_address
select e.id,e.name,e_a.city,e_a.street from employee einner join  employee_address e_aon e.id=e_a.id;-- inner join = join 注意了hql中这样写
select e.id as bianhao,e.name as xingming,e_a.city,e_a.street from employee ejoin  employee_address e_aon e.id=e_a.id;-- 等价于 隐式连接写法select e.id as ID,e.name as namess,e_a.city,e_a.street fromemployee e,employee_address e_a
where e.id=e_a.id;-- 2.左外 连接
select e.id as bianhao,e.name as xingming,e_c.phno as shoujihao,e_c.email as youxiang fromemployee e left join employee_connection e_c
on e.id=e_c.id;-- 等价于  outer可以不写
select e.id as bianhao,e.name as xingming,e_c.phno as shoujihao,e_c.email as youxiang fromemployee e left outer join  employee_connection e_c
on e.id=e_c.id;-- 3函数
-- sum() 系统提前帮助我们写好的功能方法
-- 跑步 讲话 炒菜 打篮球-- 显示函数
show functions ;-- 查看某个函数的作用
describe function extended  avg;-- 3.1 字符串函数
select length("liushao");  --求长度select reverse("oahin");-- 拼接
select concat("qiuchang","xiaowangzi");-- 带分隔符的字符串拼接
select concat_ws('.','www',`array`('liushao','cn'));--  字符串截取
select substr("liushaolaoshi",-2);-- hiselect substr("liushaolaoshi",-3);-- shiselect substr("liushaolaoshi",2,2);-- iu--分割字符串
select split("apache hive",' ');-- ["apache","hive"] [] 表示数组 apache下表是0 hive下标是1select split("apache hive",' ')[0];select split("apache hive",' ')[1];-- ["apache","hive","abc","ddd"]-- 坐标  经度纬度 提前了解一下(112453,789456)-- 3.2 日期函数
-- 获取系统当前的日期
select  `current_date`();select unix_timestamp();select unix_timestamp("2025-08-10 18:18:26");select unix_timestamp('20250810 18:18:26','yyyMMdd HH:mm:ss');select from_unixtime(15,'yyyMMdd HH:mm:ss');-- 3.3日期比较函数
select datediff('2025-08-09','2025-08-07');  -- 2select datediff('2025-08-07','2025-08-09');-- (-2)-- 日期增加函数
select date_add('2025-08-11',6);  -- 2025-08-17-- 日期减少
select  date_sub('2025-08-11',6);-- 2025-08-05-- 3.4数学函数
-- 取整数 遵学四舍五入的原则
select round(3.1415926); -- 3select round(3.5415926);-- 4-- 指定精度的函数
select round(3.1415926,3); -- 3.142--随机数 :返回0到1这个范围的随机数
select rand();select rand(3);-- 随机种子  --得到一个稳定的随机数-- 3.5条件函数select * from student_local;-- 使用if进行性别判断  如果是男生 显示M  女生  显示W
select `if`(sex='男','M','W'),sex from student_local limit 3;-- 空值转换  第一个字符串如果是空的显示第二个值，如果第一个字符串不是空的就显示第一个
select nvl("liushao","nihao");select nvl(null,"haha");
-- 条件转换
select case 100 when 50 then 'tom' when 100 then 'jerry' else 'oldsix' end;selectcase 100when 50 then 'tom'when 100 then 'jerry'else 'oldsix'
end;selectcase 100when 50 then 'tom'when 103 then 'jerry'else 'oldsix'
end;-- 使用case  when then 将性别显示为英文 male female
selectcase sexwhen '男' then 'male'when '女' then 'female'
endfrom student_local
limit 3;selectcase sexwhen '男' then 'male'else  'female'
endfrom student_local
limit 5;