业务常用整理
- 移动数据表的部分字段到另一个数据表
此种方式会覆盖原有字段值。 如果只是更新 使用下方方式 更新部分字段值与另一张表的一致
- 数据库构建用户并给予权限
- sql语句修改 数据库表字段长度
基础
子查询的语法：
（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开始。
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课程成绩高的所有学生的学号
第2题 查询平均成绩大于60分的学生学号和平均成绩
第3题 查询所有学生的学号，姓名，选课数，总成绩
4.查询姓“张”的老师的个数
5.查询没学过‘张三’老师课的学生的学号，姓名。
6.查询学过“张三”老师所教的所有课的同学的学号，姓名
7.查询学过编号为01的课程并且编号为02的课程学生的学号，姓名。
8.查询课程编号为“02”的总成绩
9.查询所有课程成绩小于60分的学生，姓名
10.查询没有学全所有课的学生的学号，姓名。
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文件到某目录下
查询