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

1 -- table_1中有id,age;
2 -- table_2中有id，sex。想取出id,age,sex 三列信息
3 select a.id,a.age,b.sex from
4 (select id,age from table_1) a      -- 将select之后的内容存为临时表a
5 join
6 (select id,sex from table_2) b       -- 将select之后的内容存为临时表b
7 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 语句的结果集。

1 -- 去重，合并两张表的数据
2 select * from 
3 (
4 select id from table_1
5 UNION
6 select id from table_2
7 )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

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

1 -- 按性别分组，统计（男，女）不同的id个数
2 select count(distinct id) from table_1 group by sex
3 
4 -- 统计最大/最小/平均年龄
5 select max(age),min(age),avg(age) from table_1 group by id

3. 筛选 where / having

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

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

1 -- 统计A公司的男女人数
2 select count(distinct id) from table_1 where company = 'A' group by sex
3         
4 -- 统计各公司的男性平均年龄，并且仅保留平均年龄30岁以上的公司
5 select company, avg(age) from table_1 where sex = 'M' 
6 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不可以省。

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

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

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

6. 字符串 concat / split / substr

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

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

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

 1 -- 得到 www.baidu.com
 2 select concat('www','.baidu','.com') from baidu
 3 
 4 -- 以","为分隔符分割字符串，并转化为array
 5 select split("1,2,3",",") as value_array from table_1;
 6 
 7 -- 结合array index, 将原始字符串分割为3列
 8 select value_array[0],value_array[1],value_array[2] from
 9 (select split("1,2,3",",") as value_array from table_1) t
10 
11 -- 得到"cd"
12 select substr('abcde',3,2) from string

三、基础进阶

1. 百分位数 percentile

1 -- 获取 income 字段的 top10% 的阈值
2 select percentile(CAST (salary AS int),0.9) as top10_threshold from table_1;
3 
4 -- 获取 income 字段的10个百分位点
5 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
6 from table_1;

2. 时间函数 to_date

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

1 -- 转换为时间数据的格式
2 select to_date("1970-01-01 00:00:00") as start_time from table_1;
3 
4 -- 计算数据到当前时间的天数差
5  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 姓名='王五'

相关推荐