HQL查询

2023-06-03 03:53| 来源: 网络整理| 查看: 265

HQL查询 1.1 查询语法 [WITH CommonTableExpression (,CommonTableExpression)*] SELECT [ALL | DISTINCT]select_expr, select_expr,... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [ORDER BY col_list] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]] [LIMIT [offset,] rows]; 语法顺序 select .. distinct .. from .. where .. group by .. having .. order by .. limit 执行顺序 from .. where .. group by .. select .. having .. distinct .. order by .. limit Map阶段 1.执行 from，进行表的查找与加载； 2.执行 where 谓词下推 3.执行 join 操作 4.执行输出列的数据 select 字段和分组字段 5.map端哈希分组输出 reduce阶段 1.执行reduce端合并分组 2.执行select 3.执行having过滤

注意：（1）SQL 语言大小写不敏感。（2）SQL 可以写在一行或者多行（3）关键字不能被缩写也不能分行（4）各子句一般要分行写。（5）使用缩进提高语句的可读性。

1.2 基础查询回顾 1.2.1 准备数据 1,联想,5000,c001 2,海尔,3000,c001 3,雷神,5000,c001 4,JACK JONES,800,c002 5,真维斯,200,c002 6,花花公子,440,c002 7,劲霸,2000,c002 8,香奈儿,800,c003 9,相宜本草,200,c003 10,面霸,5,c003 11,好想你枣,56,c004 12,香飘飘奶茶,1,c005 13,果9,1 创建表 create table t_product( pid int, pname string, price double, cid string )row format delimited fields terminated by ","; load data local inpath "/root/product.txt" into table t_product; 1.2.2 简单查询 -- 查询常量值 SELECT 100; SELECT 'hello'; -- 查询表达式 SELECT 100%98; -- 查询函数 SELECT `current_date`(); SELECT VERSION(); -- 两个操作数都为数值型，则做加法运算 SELECT 100+90; -- 只要其中一方为字符型，试图将字符型数值转换成数值型如果转换成功，则继续做加法运算 SELECT '123'+90; -- 如果转换失败，则将字符型数值转换成null SELECT 'hello'+90; -- 只要其中一方为NULL，则结果肯定为NULL SELECT NULL+10; -- 连接字符串 SELECT CONCAT('a','b','c'); -- 查询所有的商品.开发时尽量别写 * select * from t_product; -- 查询商品名和商品价格. select pname,price from t_product; -- 别名查询.使用的关键字是as（as可以省略的）.表别名: select * from t_product as p; -- 别名查询.使用的关键字是as（as可以省略的）.列别名: select pname pn from t_product; -- 去掉重复值. select distinct price from t_product; -- 查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示. select pname,price+10 from t_product; 1.2.3 条件查询 -- 查询商品名称为“花花公子”的商品所有信息： SELECT * FROM t_product WHERE pname = '花花公子'; -- 查询价格为800商品 SELECT * FROM t_product WHERE price = 800; -- 查询价格不是800的所有商品 SELECT * FROM t_product WHERE price != 800; SELECT * FROM t_product WHERE price 800; SELECT * FROM t_product WHERE NOT(price = 800); -- 查询商品价格大于60元的所有商品信息 SELECT * FROM t_product WHERE price > 60; -- 查询商品价格在200到1000之间所有商品 SELECT * FROM t_product WHERE price >= 200 AND price 200; -- 查询分类为'c001'的所有商品的总和 SELECT SUM(price) FROM t_product WHERE cid = 'c001'; -- 查询分类为'c002'所有商品的平均价格 SELECT AVG(price) FROM t_product WHERE cid = 'c002'; -- 查询商品的最大价格和最小价格 SELECT MAX(price),MIN(price) FROM t_product; 1.2.5 分组 having与where的区别: - having是在分组后对数据进行过滤. where是在分组前对数据进行过滤 - having后面可以使用分组函数(统计函数) where后面不可以使用分组函数。 -- 统计各个分类商品的个数 SELECT category_id ,COUNT(*) FROM t_product GROUP BY category_id ; -- 统计各个分类商品的个数,且只显示个数大于1的信息 SELECT cid ,COUNT(*) FROM t_product GROUP BY cid HAVING COUNT(*) > 1; -- 这种更好一些 having后直接得出结果避免2次计算 SELECT cid ,COUNT(*) productcount FROM t_product GROUP BY cid HAVING productcount > 1; 1.2.6 分页 -- 0索引开始取两条 select * from t_product limit 0,2; select * from t_product limit 2; -- 2索引开始取两条 select * from t_product limit 2,2; (当前页-1)*pageSize 1.2.7 union / union all -- union去重 select * from t_product where pid 3000) select * from t1 union select * from t2; --建表 create table p1 like t_product; insert overwrite table p1 select * from t_product where price > 3000; with t1 as (select * from t_product where price > 3000) insert overwrite table p1 select * from t1; create table p2 as with t1 as (select * from t_product where price > 3000) select * from t1; 1.4.4 case when语法 case 字段 when 值 then when 值 then when 值 then else end as 别名 case when expr1 and/or expr2 then when expr1 and/or expr2 then when expr1 and/or expr2 then else end as 笔名 select *, `if`(sex == '男', 'man', 'woman') from t_user; -- 第一种格式 select *, case sex when '男' then 'man' when '女' then 'woman' else 'other' end as english_sex from t_user; -- 第二种格式 select *, case when sex == '男' then 'man' when sex == '女' then 'woman' else 'other' end as english_sex from t_user; select *, `if`(age >= 10 and age < 20, '10~20', `if`(age >= 20 and age < 30, '20~30', `if`(age > 30 and age < 40, '30~40', 'other'))) from t_user; select *, case when age >= 10 and age < 20 then '10~20' when age >= 20 and age < 30 then '20~30' when age >= 30 and age < 40 then '30~40' else 'other' end as age_stage from t_user; 1.4.5 查询练习

练习1

2022-08-07 1,liuyan 2,tangyan 3,jinlian 4,dalang 5,ximenqing 2022-08-08 1,liuyan 2,tangyan 4,dalang 6,wusong -- 创建分区表记录每天用户登陆信息 create table tb_login ( uid int, name string ) partitioned by (dt string) row format delimited fields terminated by ","; load data local inpath '/root/2022-08-07' into table tb_login partition (dt = '2022-08-07'); load data local inpath '/root/2022-08-08' into table tb_login partition (dt = '2022-08-08'); select * from tb_login; -- 查询7号和8号都登陆人的信息 select t1.uid, t1.name from (select * from tb_login where dt = '2022-08-07') t1 join (select * from tb_login where dt = '2022-08-08') t2 on t1.uid = t2.uid; -- 查询7号登陆 8号没登录人的信息 select * from (select * from tb_login where dt = '2022-08-07') t1 left join (select * from tb_login where dt = '2022-08-08') t2 on t1.uid = t2.uid where t2.uid is null; -- 查询8号登陆 7号没登陆人的信息 select t2.* from (select * from tb_login where dt = '2022-08-07') t1 right join (select * from tb_login where dt = '2022-08-08') t2 on t1.uid = t2.uid where t1.uid is null; -- 查询7号登陆 8号没登陆和 8号登陆 7号没登陆人的信息 select * from (select * from tb_login where dt = '2022-08-07') t1 full join (select * from tb_login where dt = '2022-08-08') t2 on t1.uid = t2.uid where t1.uid is null or t2.uid is null;

练习2

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IYB7fiwW-1684578013741)(img/17_hangzhuanlie.png)]

建表准备数据

vi mark.txt liuyan,语文,100 liuyan,数学,99 liuyan,英语,100 tangyan,语文,80 tangyan,数学,98 tangyan,英语,60 create table stu_mark ( sname string, subject string, score double ) row format delimited fields terminated by ","; load data local inpath '/root/mark.txt' into table stu_mark; select * from stu_mark; -- 使用case when 查询每个人的语文成绩 SELECT sname, case subject WHEN '语文' THEN score else 0 END as `语文` FROM stu_mark ; -- 查询每个人的每科成绩 SELECT sname, case subject WHEN '语文' THEN score else 0 END as `语文`, case subject WHEN '数学' THEN score else 0 END as `数学`, case subject WHEN '英语' THEN score else 0 END as `英语` FROM stu_mark ; -- 得到最终结果 SELECT sname, max(case subject WHEN '语文' THEN score else 0 END) as `语文`, max(case subject WHEN '数学' THEN score else 0 END) as `数学`, max(case subject WHEN '英语' THEN score else 0 END) as `英语` FROM stu_mark group by sname;

练习3

yg.txt uid,name,age,gender 1,liuyan,23,M 2,tangyan,33,F 3,jinlian,28,M 4,dalang,37,F 5,ximenqing,17,M bm.txt bid,bname 1,财务部 2,销售部 3,后勤部 4,技术部 gz.txt uid,jb,jj,tc,bid 1,2000,3000,5000,1 2,1000,4000,1000,2 3,5000,1000,5000,1 4,4000,300,7000,3 -- 创建员工表 create table yg ( uid int, name string, age int, gender string ) row format delimited fields terminated by ','; -- 加载员工数据 load data local inpath '/root/yg.txt' into table yg; -- 创建部门表 create table bm ( bid int, bname string ) row format delimited fields terminated by ','; -- 加载部门数据 load data local inpath '/root/bm.txt' into table bm; -- 创建工资表 create table gz ( uid int, jb double, jj double, tc double, bid int ) row format delimited fields terminated by ','; -- 加载工资数据 load data local inpath '/root/gz.txt' into table gz; select * from yg; select * from gz; select * from bm;

1.查询每个部门的员工数男员工个数女员工个数显示部门名称

-- 查询每个员工的工号姓名性别部门id 部门名称. select yg.uid, yg.name, yg.gender, bm.bid, bm.bname from yg inner join gz on yg.uid = gz.uid inner join bm on gz.bid = bm.bid; -- 查询每个部门的总人数部门名称部门总人数 with t1 as (select yg.uid, yg.name, yg.gender, bm.bname from yg inner join gz on yg.uid = gz.uid inner join bm on gz.bid = bm.bid) select t1.bname, count(bname) `总人数` from t1 group by t1.bname; -- 查询每个部门的总人数部门名称部门总人数男员工个数女员工个数 with t1 as (select yg.uid, yg.name, yg.gender, bm.bname from yg inner join gz on yg.uid = gz.uid inner join bm on gz.bid = bm.bid) select t1.bname, count(bname) `总人数`, sum(`if`(gender = 'M', 1, 0)) `男`, sum(`if`(gender = 'F', 1, 0)) `女` from t1 group by t1.bname; ------------------------------------------------------------------------------------------------ -- 查询每个部门的总人数男员工个数女员工个数 select bid, count(bid) total_num, sum(`if`(gender = 'M', 1, 0)) nan, sum(`if`(gender = 'F', 1, 0)) nv from yg inner join gz on yg.uid = gz.uid group by bid; -- 显示部门名称 with t1 as (select bid, count(bid) total_num, sum(`if`(gender = 'M', 1, 0)) nan, sum(`if`(gender = 'F', 1, 0)) nv from yg inner join gz on yg.uid = gz.uid group by bid) select bm.bname, t1.* from t1 inner join bm on t1.bid = bm.bid; +-----------+---------+---------------+---------+--------+ | bm.bname | t1.bid | t1.total_num | t1.nan | t1.nv | +-----------+---------+---------------+---------+--------+ | 财务部 | 1 | 2 | 2 | 0 | | 销售部 | 2 | 1 | 0 | 1 | | 后勤部 | 3 | 1 | 0 | 1 | +-----------+---------+---------------+---------+--------+

2.查询每种性别的总工资

-- 查询每个员工的工资工号姓名性别基本工资奖金提成 select yg.uid, yg.name, gz.jb, gz.jj, gz.tc from yg left join gz on yg.uid = gz.uid; -- 查询每个员工的总工资工号姓名性别总工资 select yg.uid, yg.name, yg.gender, (gz.jb + gz.jj + gz.tc) sal from yg left join gz on yg.uid = gz.uid; -- 查询每种性别的总工资 select yg.gender, sum(gz.jb + gz.jj + gz.tc) total_sal from yg left join gz on yg.uid = gz.uid group by gender; with t1 as (select yg.uid, yg.name, yg.gender, (gz.jb + gz.jj + gz.tc) sal from yg left join gz on yg.uid = gz.uid) select gender, sum(sal) from t1 group by gender; +------------+------------+ | yg.gender | total_sal | +------------+------------+ | F | 17300.0 | | M | 21000.0 | +------------+------------+

3.查询每个员工的中文性别,年龄阶段

-- 查询每个员工信息及中文性别 -- if select *, if(gender == 'M', '男', '女') as ch_gender from yg; -- case when 第一种格式 select *, case gender when 'M' then '男' else '女' end as ch_gender from yg; -- case when 第二种格式 select *, case when gender == 'M' then '男' else '女' end as ch_gender from yg; -- 查询每个员工信息及年龄阶段 -- if select *, if(age >= 10 and age 20 and age 30 and age = 10 and age < 20 then '10~20' when age >= 20 and age < 30 then '20~30' when age >= 30 and age < 40 then '30~40' else 'other' end as age_stage from yg; -- 查询每个员工信息中文性别年龄阶段 select *, if(gender == 'M', '男', '女') as ch_gender, case when age >= 10 and age < 20 then '10~20' when age >= 20 and age < 30 then '20~30' when age >= 30 and age < 40 then '30~40' else 'other' end as age_stage from yg; with t1 as (select *, if(gender == 'M', '男', '女') as ch_gender from yg) select *, case when age >= 10 and age < 20 then '10~20' when age >= 20 and age < 30 then '20~30' when age >= 30 and age < 40 then '30~40' else 'other' end as age_stage from t1;

4.查询每个年龄段的总工资

-- 年龄段总工资 select t1.age_stage, sum(gz.jb + gz.jj + gz.tc) from (select *, case when age >= 10 and age < 20 then '10~20' when age >= 20 and age < 30 then '20~30' when age >= 30 and age < 40 then '30~40' else 'other' end as age_stage from yg) t1 left join gz on t1.uid = gz.uid group by t1.age_stage; -- cte with t1 as (select *, case when age >= 10 and age < 20 then '10~20' when age >= 20 and age < 30 then '20~30' when age >= 30 and age < 40 then '30~40' else 'other' end as age_stage from yg) select t1.age_stage, sum(gz.jb + gz.jj + gz.tc) from t1 left join gz on t1.uid = gz.uid group by t1.age_stage; +---------------+----------+ | t1.age_stage | _c1 | +---------------+----------+ | 10~20 | NULL | | 20~30 | 21000.0 | | 30~40 | 17300.0 | +---------------+----------+

5 求每个人名字工资组成部分中占比最高的工资类型

-- 查询工资表中每个人基本工资奖金提成中的最高工资是多少 select *, greatest(jb, jj, tc) max_sal from gz; -- 查询工资表中每个人基本工资奖金提成最多的是哪种类型 select *, greatest(jb, jj, tc) max_sal, case when greatest(jb, jj, tc) == jb then 'jb' when greatest(jb, jj, tc) == jj then 'jj' when greatest(jb, jj, tc) == tc then 'tc' end as max_type from gz; select *, greatest(jb, jj, tc) max_sal, case greatest(jb, jj, tc) when jb then 'jb' when jj then 'jj' when tc then 'tc' end as max_type from gz; -- 查询每个员工的工号姓名最高工资类型及多少 with t1 as (select *, greatest(jb, jj, tc) max_sal, case when greatest(jb, jj, tc) == jb then 'jb' when greatest(jb, jj, tc) == jj then 'jj' when greatest(jb, jj, tc) == tc then 'tc' end as max_type from gz) select yg.uid, yg.name, t1.max_sal, t1.max_type from yg left join t1 on yg.uid = t1.uid; +---------+------------+-------------+--------------+ | yg.uid | yg.name | t1.max_sal | t1.max_type | +---------+------------+-------------+--------------+ | 1 | liuyan | 5000.0 | tc | | 2 | tangyan | 4000.0 | jj | | 3 | jinlian | 5000.0 | jb | | 4 | dalang | 7000.0 | tc | | 5 | ximenqing | NULL | NULL | +---------+------------+-------------+--------------+

【本文地址】

公司简介

联系我们