SQL补充

SQL查漏补缺

1.基本语句

创建 create table 表名(int check(限制数值范围)，decimal(精确,小数点位), primary key()，foreign key(外键 reference主键表(列名))）

插入语句insert into table(字段) value(值)

更新语句update table set col=‘’ where

删除语句delete from

视图create view 视图名 as select 

exists 检查返回外表是否有匹配的内表数据 

concat合并两个单元格

cast(字段 as 格式)

limit y offset x 跳过x条取y条

collect_list 列聚合为数组，不去重

collect_set 列聚合为数组，去重

concat_ws("",str,str) 拼接字符串

split(gamelist,",") 按逗号对str切割

explode() 将数组结构字段转换成多行 

LATERAL VIEW 结合explode使用 UDTF函数(一行输入多行输出)

2.扩展

语文数学英语三科分数均超过80份的名字

select name from s where score>=80
group by name having count(name)=3

select name from s
group by name having min(score)>=80

行列互换

年         季度        销售量
1991       1             11
1991       2             12
1991       3             13
1991       4             14
1992       1              21
1992       2              22
1992       3              23
1992       4              24

select year,
sum(case when month='1' then amount else 0 end) m1,
sum(case when month='2' then amount else 0 end) m2
from y
group by year

列转行 

uid game_list　　　　uid game

a    .......　　　　　　a .

b    .......　　　　　　a .

select uid,game from user_game

LATERAL VIEW explode(split(game_list,",")) tmpTable as game

删除自动编号不同但其他信息相同的内容

id 号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69

delete from student2 where id not in(select mid from (select min(id) mid
from student2 group by name) as t);

区间统计

select kechen,
(case when score>=80 then '优秀'
when score>=60 then '及格'
when score<60 then '不及格' END)
from s
group by kechen