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

１.内连接 inner join

-- 1.内连接查询  employee employee_address
select e.id,e.name,e_a.city,e_a.street from employee e
    inner join  employee_address e_a
        on 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 e
     join  employee_address e_a
        on e.id=e_a.id;


-- 等价于 隐式连接写法

select e.id as ID,e.name as namess,e_a.city,e_a.street from
 employee 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+UDF

2.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 e
    inner join  employee_address e_a
        on 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 e
     join  employee_address e_a
        on e.id=e_a.id;


-- 等价于 隐式连接写法

select e.id as ID,e.name as namess,e_a.city,e_a.street from
 employee 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 from
    employee 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 from
    employee 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);-- hi

select substr("liushaolaoshi",-3);-- shi

select substr("liushaolaoshi",2,2);-- iu

--分割字符串
select split("apache hive",' ');-- ["apache","hive"] [] 表示数组 apache下表是0 hive下标是1

select 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');  -- 2

select 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); -- 3

select 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;

select
       case 100
           when 50 then 'tom'
           when 100 then 'jerry'
           else 'oldsix'
end;


select
       case 100
           when 50 then 'tom'
           when 103 then 'jerry'
           else 'oldsix'
end;

-- 使用case  when then 将性别显示为英文 male female
select
 case sex
   when '男' then 'male'
   when '女' then 'female'
end
 from student_local
limit 3;


select
 case sex
   when '男' then 'male'
   else  'female'
end
 from student_local
limit 5;