SQL复习整理

一、最基本

　　1. 选择 select　　2. 表连接 join     3. 合并 union 

二、最常用

　　1. 去重 distinct 　　2. 聚合函数 max / min / sum / count + group by　　3. 筛选 where / having

　　4. 排序 order by　　5. 条件函数 case when　　6. 字符串 concat / split / substr

三、基础进阶

　　1. 百分位数 percentile　　2. 时间函数 to_date　　3. 分组排序 row_number

四、增删改

　　1. 增 insert　　2.删 delete　　3. 改 update

一、最基本

1. 选择 select

-- 从table_1中选择a这一列
select a from table_1

2. 表连接 join

-- table_1中有id,age;
-- table_2中有id，sex。想取出id,age,sex 三列信息
select a.id,a.age,b.sex from
(select id,age from table_1) a      -- 将select之后的内容存为临时表a
join
(select id,sex from table_2) b       -- 将select之后的内容存为临时表b
on a.id = b.id        -- 根据主键id连接起来

join 默认是 inner join（内连接），找出左右都可匹配的记录。

left join：左连接，以左表为准，逐条去右表找可匹配字段，如果有多条会逐次列出，如果没有找到则是NULL。

right join：右连接，以右表为准，逐条去左表找可匹配字段，如果有多条会逐次列出，如果没有找到则是NULL。

full outer join: 全连接，包含两个表的连接结果，左表缺失或右表缺失的数据会填充NULL。

每种join 都有on , on的是左表和右表中都有的字段。join 之前要确保关联键是否去重，是不是刻意保留非去重结果。

3. 合并 union 

union 用于合并两个或多个 select 语句的结果集。

-- 去重，合并两张表的数据
select * from 
(
select id from table_1
UNION
select id from table_2
)t;

注意地方：union 内部的 select 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

与 union all 区别：union的过程中会去重并降低效率，union all 直接追加数据。union 前后是两段select 语句而非结果集。

二、最常用

1. 去重 distinct

 distinct 会对结果集去重

-- 统计不同的 id 的个数
select count(*) from
(select distinct id from table_1) tb

2. 聚合函数 max / min / sum / count + group by

聚合函数帮助我们进行基本的数据统计，例如计算最大值、最小值、平均值、总数、求和

-- 按性别分组，统计（男，女）不同的id个数
select count(distinct id) from table_1 group by sex

-- 统计最大/最小/平均年龄
select max(age),min(age),avg(age) from table_1 group by id

3. 筛选 where / having

having 语句通常与 group by 语句联合使用，用来过滤由 group by 语句返回的记录集。

having 语句的存在弥补了 where 关键字不能与聚合函数联合使用的不足。

-- 统计A公司的男女人数
select count(distinct id) from table_1 where company = 'A' group by sex
        
-- 统计各公司的男性平均年龄，并且仅保留平均年龄30岁以上的公司
select company, avg(age) from table_1 where sex = 'M' 
group by company having avg(age) > 30;

4. 排序 order by

默认升序，降序DESC

LIMIT 子句用于强制 SELECT 语句返回指定的记录数。

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。

如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

-- 按年龄降序排序取最年迈的10个人(前10)
select id,age from table_1 order by age DESC limit 10

5. 条件函数 case when

case 列名

　　　　when   条件值1   then  选项1

　　　　when   条件值2    then  选项2.......

　　　　else     默认值      end

其中else 可以省，但是end不可以省。

select name as '名字',
(case sex when 0 then '女' else '男' end) as '性别'
from student;

CAST 也常用于string/int/double型的转换。

-- 收入区间分组
select id,
(case when CAST(salary as float)<50000 Then '0-5万'
when CAST(salary as float)>=50000 and CAST(salary as float)<100000 then '5-10万'
when CAST(salary as float) >=100000 and CAST(salary as float)<200000 then '10-20万'
when CAST(salary as float)>200000 then '20万以上'
else NULL end)
from table_1;

6. 字符串 concat / split / substr

concat(A,B) 返回将A和B按顺序连接在一起的字符串

split(str, regex)用于将string类型数据按regex提取，分隔后转换为array

substr(str,index,len) 截取字符串，开始位置为 index，长度 len

-- 得到 www.baidu.com
select concat('www','.baidu','.com') from baidu

-- 以","为分隔符分割字符串，并转化为array
select split("1,2,3",",") as value_array from table_1;

-- 结合array index, 将原始字符串分割为3列
select value_array[0],value_array[1],value_array[2] from
(select split("1,2,3",",") as value_array from table_1) t

-- 得到"cd"
select substr('abcde',3,2) from string

三、基础进阶

1. 百分位数 percentile

-- 获取 income 字段的 top10% 的阈值
select percentile(CAST (salary AS int),0.9) as top10_threshold from table_1;

-- 获取 income 字段的10个百分位点
select percentile(CAST (salary AS int),array(0.0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1.0)) as income_percentiles
from table_1;

2. 时间函数 to_date

to_date函数可以把时间的字符串形式转化为时间类型，再进行后续的计算

-- 转换为时间数据的格式
select to_date("1970-01-01 00:00:00") as start_time from table_1;

-- 计算数据到当前时间的天数差
 select datediff('2019-12-30','2016-12-23');

3. 分组排序 row_number

-- 按照字段salary倒序编号
select *, row_number() over (order by salary desc) as row_num from table_1;

四、增删改

1. 增 insert　　

（1）使用insert插入单行数据：

语法：insert [into] <表名> [列名] values <列值>

insert into Strdents (姓名,性别,出生日期) values ('王伟华','男','1983/6/15')

注意：如果省略列名，将依次插入所有列

（2）使用insert,select语句将现有表中的数据添加到已有的新表中

语法：insert into <已有的新表> <列名> select <原表列名> from <原表名>

insert into addressList ('姓名','地址','电子邮件') select name,address,email from Strdents

注意：查询得到的数据个数、顺序、数据类型等，必须与插入的项保持一致

2.删 delete

（1）使用delete删除表中某些数据

语法：delete from <表名> [where <删除条件>]

-- 删除某些行
delete from a where name='王伟华'

（2）使用truncate table 删除整个表的数据

语法：truncate table <表名>

truncate table tabel_1

3. 改 update

 语法：update <表名> set <列名=更新值> [where <更新条件>]

set后面可以紧随多个数据列的更新值（非数字要引号）；where子句是可选的（非数字要引号），用来限制条件，如果不选则整个表的所有行都被更新

update tabel_1 set 年龄=18 where 姓名='王五'