hiveSQL/SQL常用句式整理（不定时更新）

业务常用整理

1. 移动数据表的部分字段到另一个数据表

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;
1
2
3
4

此种方式会覆盖原有字段值。 如果只是更新 使用下方方式 更新部分字段值与另一张表的一致

update table2.col
set col1 = table1.col1, col2 = table1.col2
FROM table1
WHERE condition
1
2
3
4

2. 数据库构建用户并给予权限

给权限：
CREATE USER dataxreader WITH ENCRYPTED PASSWORD 'qX6/!nlM9O';
ALTER USER dataxreader SET default_transaction_read_only = ON;
grant usage on schema xxx to dataxreader;
grant select on xxx.aaa to dataxreader;
grant select on xxx.bbb to dataxreader;
grant select on xxx.ccc to dataxreader;
1
2
3
4
5
6
7

3. sql语句修改 数据库表字段长度

ALTER TABLE 表名 MODIFY COLUMN 字段名称 数据类型（修改后的长度）
1

基础
子查询的语法：
（SELECT [ALL|DISTINCT]<select item list>]
FROM <table list>
[WHERE<search condition>]
[GROUP BY <group item list>]
[HAVING <group by search condition>])
注意：1.子查询的SELECT查询必须使用圆括号括起来
2.任何可以使用表达式的地方都可以使用子查询，只要它返回的是单个值
子查询常用的语法格式
WHERE 查询表达式 [NOT] IN(子查询)
WHERE 查询表达式 比较运算符 [ANY|ALL] （子查询）
WHERE [NOT] EXISTS (子查询)

1.新数据插入到表格
业务常用：
1.IF 条件语句的用法
if(bool , expr1, expr2)
如果bool为True,则取expr1，否则取expr2

2.①字符串替换函数。
regexp_replace(orig_str, ‘pattern’, ‘replacement’)
第一个参数为原始字符串orig_str. 第二个参数为要搜索的模式，第三个参数为要换为的字符串。
注意 特殊字符如‘|’需要用转义字符 ‘\\|’
②字符串截取函数
substr(string A, int start, int len)

3.列转行数据
concat_ws(‘,’, collect_list(col))
第一个参数为连接的符号，第二个参数为需要列转行的列名。

4.联结操作
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

5.空值字段赋值 nvl
nvl(string,replace_col),如果字段string为null,则返回replace_col的值

6.分组求topN
row_number()函数基于over对象分组、排序的记过，为每一行分组记录返回一个序号，该序号从1开始，依次递增，遇到新组则重新从1开始。

Select * 
from table
row_number() over(partition by item order by score desc) rank
where rank<=2
1
2
3
4

7.行转列

Select
name,
sum(case when item=数学 then score end) as math,
sum(case when item=英语 then score end) as english,
From table
Group by name
1
2
3
4
5
6

8.按照字段区间对进行数据进行分组

select 
sum(
          case 
          when score between 0 and 10 
          then 1 else 0 
          end
        ) as 优秀,
 from table
1
2
3
4
5
6
7
8

9.时间戳转换为日期/ 日期转换为时间戳
from_unixtime(bigint unixtime，string format)
unix_timestamp(string date)

10.把数据插入到指定分区。
insert overwrite table t PARTITION(idate=20200728)

注意：
***group by 子句进行分组查询 select查询的列必须包含在group by子句中或者包含在聚合函数中。

案例：

学生表 成绩表 课程表 教师表
主键，外键。

建表操作：
CREATE TABLE ‘Student’(
‘s_id’ VARCHAR(20),
‘s_name’ VARCHAR(20) NOT NULL DEFAULT ‘’,
PRIMARY KEY(‘s_id’)
)

插入数据
insert into Student values[‘01’, ‘阿花’, ‘1990-01-01’, ‘男’]；

第1题.查询课程编号为“”01“”的课程比02课程成绩高的所有学生的学号

select * from
(
select s_id,c_id,s_score from  accore where c_id='01'
)as a
inner JOIN
(select s_id,c_id,s_score from  accore where c_id='02'
)as b on a.s_id=b.s_id
where a.s_score>=b.score
1
2
3
4
5
6
7
8

第2题 查询平均成绩大于60分的学生学号和平均成绩

select s_id,avg(s_score)
from score
group by s_id having avg(s_score)>60
1
2
3

第3题 查询所有学生的学号，姓名，选课数，总成绩

select a.s_id, a.s_name, count(b.c_id), sum(case when b.s_score is NULL else b.s_score END)
from student as a
left join score as b on a.s_id=b.s_id
group by s_id
1
2
3
4

4.查询姓“张”的老师的个数

select count(t_id)
from teacher
where t_name like ‘张%’
1
2
3

5.查询没学过‘张三’老师课的学生的学号，姓名。

select s_id, s_name from student
where s_id not in(
select s_id from score
where c_id=(
select c_id from course
where t_id=(
select t_id from teacher
where t_name='张三'
)
)
)
1
2
3
4
5
6
7
8
9
10
11

6.查询学过“张三”老师所教的所有课的同学的学号，姓名

select st.s_id, st.s_name
from student as st
inner join score as s on s.s_id=st.s_id
inner join course as c on s.c_id=c.c_id
inner join teacher as t on t.t_id=c.t_id
where t.t_name='张三'
order by st.s_id
1
2
3
4
5
6
7

7.查询学过编号为01的课程并且编号为02的课程学生的学号，姓名。

select s_id, s_name from student
where s_id in
(
selcet a.s_id FROM
(select s_id from score WHERE c_id='01') as a
inner join
(select s_id from score WHERE c_id='02') as b
on a.s_id=b.s_id
)
1
2
3
4
5
6
7
8
9

8.查询课程编号为“02”的总成绩

select sum(s_score)
from score
where c_id='02'
1
2
3

9.查询所有课程成绩小于60分的学生，姓名

select s_id, s_name
from score
where max(s_score)<60
group by s_id
1
2
3
4

10.查询没有学全所有课的学生的学号，姓名。

select s_id,s_name from student
where s_id in(
select s_id from score
group by s_id having count(distinct c_id)<3    漏掉了一门课程都没有学的情况
)
select 
from student as st
left join score as sc on st.s_id=sc.s_id
group by st.s_id having count(distinct sc.c_id)<(select count(distinct c_id) from course)
1
2
3
4
5
6
7
8
9

11.查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
select s_id from score
where c_id in (
select c_id from score
where s_id=‘01’
) and s_id!=‘01’

12.查询和01号同学所学课程完全相同的其他同学的学号
select * from student
where s_id in (
select s_id FROM score
where s_id!=‘01’
GROUP BY s_id having count(distinct c_id)=(select count(distinct c_id) from score where s_id=‘01’)
) 条件1：选课数目必须相同，同时保证s_id不一样
and s_id not in (
select distinct s_id FROM score
where c_id not in(
select c_id from score
where s_id=‘01’
)
) 条件2：课程号 不在 01同学所学课程当中的 直接排除

15.查询两门及以上不及格课程的同学的学号，姓名及其平均成绩
select a.s_id, a.s_name, avg(s_score) from student as a
INNER JOIN score as b
on a,s_id=b.s_id
where s.id in (
select s_id from score
where s_score<60
group by s_id having count(distinct c_id)>=2
) 选择有两门不及格课程同学的学号
group by s_id,s_name

16.检索01课程分数小于60，按分数降序排列的学生信息
select t.*, s.c_id, s.s_score FROM student as t
inner join score as s ON t.s_id=s.s_id
where s.c_id=‘01’ and s.s_score<60
ORDER BY s.s_score DESC 降序排列

17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
s_id “学号”
,MAX(case when c_id=‘01’ THEN s_score ELSE NULL END)“语文”
,MAX(case when c_id=‘02’ THEN s_score ELSE NULL END)“数学”
,MAX(case when c_id=‘03’ THEN s_score ELSE NULL END)“英语”
,avg(s_score)
from score
group by s_id
ORDER BY avg(s_score) DESC

18.查询各科成绩最高分，最低分和平均分：以如下形式显示：
课程ip,课程name,最高分，最低分,平均分,及格率，中等率，优秀率
select s.c_id
,c.cname,
,max(s.s_score)
,min(s.score)
,avg(s.score)
,sum(case when s.s_score>=60 then 1 else 0 END)/count(s_id)
,sum(case when s.s_score>=70 and s.s_core<80 then 1 else 0 END)/count(s_id)
from score as s
inner join course as c ON s.c_id=c,c_id
GROUP by c_id

19.按各科成绩进行排序，并显示排名
over (order by 列)
窗口函数
row_number() 不会重复
dense_rank() 会重复 2 2 3
disrank() 会重复，2 2 4

rank跳跃式的排序

20.查询学生的总成绩并进行排名
select s_id"学号", sum(s_score)“总成绩”
from score
group by s_id
order by sum(s_score) DESC

21.查询不同老师所教不同课程平均分从高到低显示
select c.c_id, c.name, avg(sc.s_score) as avg_score
from score as sc
inner join course as c on sc.c_id=c.c_id
group by sc.c_id
order by avg_score DESC

相关知识
1.什么是hive
解决海量结构化日志的数据统计
数据仓库工具 提供类似SQL查询功能。 将HQL转化成MapReduce 传输–存储–计算–

操作用mapreduce写成很多模板-》模板封装在hive中-》
1）数据存储在hdfs
2）分析数据底层默认实现是mapreduce
3）执行程序运行在Yam上

hive的优缺点
优点：执行延迟高，实时性要求不高 ，支持自定义函数
缺点：迭代算法无法表达，效率较低

hive架构：
meta store 元数据
SQL Parser 解析器
Physical Plan编译器
Query Optimizer优化器
Exeution 执行器

二.hive安装
1.官网：http://hive.apache.org/
3.下载地址：http://archive.apache.org/dist/hive/

解压：unzip .zip
bin conf examples hcatalog lib LICENSE NOTICE README.txt scripts

bin/hive (启动 多了derby. , )
show database
create table student(id int, name string); row format delimited fields terminated by ‘t’
insert into table student values(1, ‘’)
导入数据 load data local inpath ’ ’ into tabel student: 本地数据

hadoop fs -put stu.txt / put文件到某目录下
查询

函数
UDF 一进一出
UDAF 多进一出 count max min
UDTF 一进多出