sql初学tips

建表

create table 表名(字段1 字段类型(长度) 约束,字段1 字段类型(长度) 约束,…)
【temporary table代表临时表】
【约束：not null / unique / primary key】

查询

• 基本框架
  select … from 表 where … group by … having … order by …
• 查询逻辑
  from+join→where→group by→having→select→order by→limit
  从哪些表里的→哪些内容中→按照哪个字段分组后→筛选符合哪些条件的→哪些字段→筛选过后按照哪些字段排序→然后在结果数据中显示几行内容
• 查询语句的处理
  1）having：分组后筛选，只能放在group by后面
  2）order by：默认升序，后加desc为降序
  3）distinct 字段：去重
  4）case when 条件1 then value1 when 条件2 then value2 else 赋值 end：变量的二次定义
  5）CAST(字段 as string/int/double)：用于将字段转换为string/int/double格式
  6） concat(A,B)：合并返回AB，AB可以是字段或者字符串
  7）split(字段，参数) as array：将字符串类型的字段按照参数分割为几列，并转化为array，可按照array的index查询拆分后的几列数据
  8）substr(str,截取位置,截取长度)：截取字符串指定位置开始的指定长度个字符
  9）ifnull(字段，0)：空值处理，字段值为空，填充0
  10）trim(字段)：去掉字段两边的空格
  11）round(字段，参数)：保留小数位数，参数为-1，四舍五入；参数为空，默认取整
  12） limit 截取位置，截取长度：截取几行数据，如limit 0,1代表从第一行开始截取一行记录

连接

• join：内连，两表都匹配的记录
• left join：左连，左表的全部记录与右表的匹配记录
• right join：右连，右表的全部记录与左表的匹配记录
• full outer join：全连，两表全部的记录
• cross join：两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积
• union、union all：基于列合并多张表的数据，合并列的格式必须一致
  （union去重，效率降低；union all直接追加数据）

百分位数

• precentile(column,p)：获取百分位数
• percentile(column,array(p1,p2,…))：获取几个百分位点
  注：
  column必须为int型：可用CAST(column,int)转换
  percentile_approx()：column可为数值型
  0<=p<=1：0.2指的是返回二分位数

--获取学生的top10%成绩的阈值
select percentile(CAST(score,int),0.9) as student_top10% from score
--获取学生成绩的二分位数、四分位数、九分位数
select percentile(CAST(score,int),array(0.2,0,4,0.9))