33-Hive SQL DML语法之查询数据-2

1. 解决hive中注释中文乱码的问题

-- 注意了需要在sqlyog中执行下面的代码!!!
use hive3;
show tables;

alter table hive3.COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table hive3.TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
alter table hive3.PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
alter table hive3.PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
alter table hive3.INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

2.授课hql语句

--  2025-08-11 授课

select  * from t_usa_covid19;

-- 1.根据州进行state  分组统计 统计每个州有多少个县 county
-- select  count(county) from t_usa_covid19
-- where  count_date="2021-01-28"
-- group by state;

select  count(county) from t_usa_covid19
 where  count_date="2021-01-28"
 group by state;


-- 想看一下统计的结果是属于哪一个州的
select state,count(county) as county_nums from t_usa_covid19
 where  count_date="2021-01-28"
 group by state;



-- 2.想看一下每个县的死亡病例，很简单 ，刚才的语句添加一个字段就行了？？？？
-- 下面的hql会报错
-- Error while compiling statement: FAILED:
-- SemanticException [Error 10025]: Line 1:42 Expression not in GROUP BY key 'deaths'
select state,count(county) as county_nums,deaths from t_usa_covid19
 where  count_date="2021-01-28"
 group by state;

-- 解决方案
select state,count(county) as county_nums,sum(deaths) from t_usa_covid19
 where  count_date="2021-01-28"
 group by state;

--3、having
--统计2021-01-28死亡病例数大于10000的州
--count_date string,
   -- county     string,
   -- state      string,
   -- fips       int,
   -- cases      int,
   -- deaths     int

select state,sum(deaths) from t_usa_covid19
where  count_date="2021-01-28" and sum(deaths)>10000
group by state;
-- 结论：where 语句中不能使用聚合函数 语法报错


select state,sum(deaths) from t_usa_covid19
where  count_date="2021-01-28"
group by state
having sum(deaths)>10000 ;


--  上面的sql计算了两次，性能不高 ，我们改写sql让它计算一次 性能能提高
select state,sum(deaths) as cnts  from t_usa_covid19
where  count_date="2021-01-28"
group by state
having cnts>10000 ;


-- order by
-- 4.根据确诊病例数升序，查询返回结果
select * from t_usa_covid19 order by cases;

-- 根据确诊病例数降序显示
select * from t_usa_covid19 order by cases desc ;

-- 根据死亡病例数倒序排列 ，查询返回加州每个县的结果
select * from t_usa_covid19
where state="California" order by deaths desc;


-- 5. 加州返回结果集 从第一行开始 共三行
select  * from  t_usa_covid19
where count_date="2021-01-28"
and state="California" limit 0,3;-- 分页建议这样写

-- 得出结论  数据索引是从0开始的

select  * from  t_usa_covid19
where count_date="2021-01-28"
and state="California" limit 3;

select  state,sum(deaths) as cnts  from  t_usa_covid19
where count_date="2021-01-28"
group by state
having cnts >10000
limit 2;

---  2025-08-11 PM
--table1: 员工表
CREATE TABLE employee(
   id int,
   name string,
   deg string,
   salary int,
   dept string
 ) row format delimited
fields terminated by ',';

--table2:员工家庭住址信息表
CREATE TABLE employee_address (
    id int,
    hno string,
    street string,
    city string
) row format delimited
fields terminated by ',';

--table3:员工联系方式信息表
CREATE TABLE employee_connection (
    id int,
    phno string,
    email string
) row format delimited
fields terminated by ',';


select * from liushao.employee;
--加载数据到表中
load data local inpath '/export/data/hivedata/employee.txt' into table liushao.employee;

3. hql中HAVING与WHERE区别

having是在分组后对数据进行过滤
where是在分组前对数据进行过滤
having后面可以使用聚合函数
where后面不可以使用聚合函数