本文基于PostgreSQL

一、单表操作

1.) 更新：更新表中字段A的值为字段B的倍数

1	update test set money = age * 10

2.) 插入：若冲突则更新

以postgresql为例

-- 插入时若冲突则更新
insert into ta(id,name,age) value(?,?,?) on conflict(id) do update set name=?,age=?
-- 插入时若冲突则啥也不干
insert into ta(id,name,age) value(?,?,?) on conflict(id) do nothing

id需要添加唯一约束

3.) case when else end

这个应该算是个基操，但是我用的还是比较少。因此记录

select case
when sname like '熊%' then '熊'
when sname like '红%' then '红'
else '百家姓'
end as "姓氏" 
from student limit 1000;

二、多表操作

1.) 更新：从历史表数据提取最新数据更新到实时表

有两张表，一张表是new_info一张表是history_info，现在需要从history_info中，将每条数据的最新值更新到new_info

方式一：子查询。效率低

update new_info as n set
money = (select money from history_info hi where hi.id=n.id order by hi.time desc limit 1),
age = (select age from history_info hi where hi.id=n.id order by hi.time desc limit 1),
time = (select time from history_info hi where hi.id=n.id order by hi.time desc limit 1)

方式二：多表联动update。效率高

update new_info as n set
money = m.money,
age = m.age 
from (select id,money,age,time from history_info hi where (id,time) in (select id,max(time) from history_info group by id) ) as m
where n.id=m.id

注意要set的值，一定不要使用.用法，否则会报错。

2.) 更新：计算Point表所属的Polygon

我有两张表，分别为t_polygon面数据、t_point点数据。现在需要计算出t_point的所属的面。

create table t_polygon(
name varchar,
geom geometry
);
create table t_point(
name varchar,
geom geometry,
polygon_name varchar
);
INSERT INTO t_polygon ("name", geom) VALUES('上海', 'SRID=4326;POLYGON ((121.03687783916614 31.35143857490192, 121.09979860176381 30.916327066770677, 121.84656771248656 30.93621266279581, 121.70416817564622 31.27646431189153, 121.2852491280545 31.41787528457955, 121.03687783916614 31.35143857490192))'::geometry);
INSERT INTO t_polygon ("name", geom) VALUES('台湾', 'SRID=4326;POLYGON ((121.19607835215324 25.042293758294534, 121.77524881337806 25.129716861505443, 121.958651447267 24.7708865549712, 121.78490021610654 24.208687567961775, 121.23469045857911 22.730079110419624, 120.9451038360713 22.31099356753988, 120.88718799625815 21.962283602183632, 120.6169088188587 22.042832668743173, 120.36593430277674 22.489483511080607, 120.17287748236848 22.970251783151056, 120.15357282105094 23.360731469806055, 120.58794997102186 24.39343611664809, 120.93545150541252 24.779651312622903, 121.19607835215324 25.042293758294534))'::geometry);
INSERT INTO t_point ("name", geom, polygon_name) VALUES('1', 'SRID=4326;POINT (121.40122844116506 31.140063768088478)'::geometry, NULL);
INSERT INTO t_point ("name", geom, polygon_name) VALUES('2', 'SRID=4326;POINT (120.99973936203358 23.673676426717503)'::geometry, NULL);
INSERT INTO t_point ("name", geom, polygon_name) VALUES('3', 'SRID=4326;POINT (121.19607835215324 25.042293758294534)'::geometry, NULL);
INSERT INTO t_point ("name", geom, polygon_name) VALUES('4', 'SRID=4326;POINT (109.31110958420386 25.607382997704818)'::geometry, NULL);

常用空间关系函数比较。

函数	作用	是否允许在边界上	关系
`ST_Intersects(A, B)`	判断 `A` 和 `B` 是否相交	允许	相交即可
`ST_Covers(A, B)`	`A` 是否完全覆盖 `B`	允许	`B` 在 `A` 内部或边界上
`ST_Contains(A, B)`	`A` 是否严格包含 `B`	不允许	`B` 只能在 `A` 内部
`ST_Within(A, B)`	`A` 是否在 `B` 内部	不允许	`B` 严格包围 `A`

方式一：子查询

update t_point as d
set polygon_name = (
select m.name from t_polygon as m where st_covers(m.geom,d.geom) limit 1
);

方式二：联动update

update t_point as d
set polygon_name = m.name 
from t_polygon m 
where st_covers(m.geom,d.geom);

3.) 查询：从历史表中提取出最新最大的数据

我有一张学生成绩单表。结构如下

CREATE TABLE scores (
	id varchar NULL, -- 学生学号
	"name" varchar NULL, -- 学生姓名
	score int8 NULL, -- 学生分数
	subject varchar NULL, -- 考试科目
	createtime int8 NULL -- 考试时间
);
COMMENT ON COLUMN public.scores.id IS '学生学号';
COMMENT ON COLUMN public.scores."name" IS '学生姓名';
COMMENT ON COLUMN public.scores.score IS '学生分数';
COMMENT ON COLUMN public.scores.subject IS '考试科目';
COMMENT ON COLUMN public.scores.createtime IS '考试时间';

需求：查询出每个学生最近考试成绩最高的那一次，要求将所有字段输出。

实现

with MaxScorePerStudent as (
select
	id,
	name,
	score,
	subject,
	createtime,
	row_number() over (
partition by id
order by
	score desc,
	createtime desc
) as row_num
from
	scores
where
	score > 80

)

select
	id,
	name,
	score,
	subject,
	createtime
from
	MaxScorePerStudent
where
	row_num = 1

4.) 查询：分片表union成视图，进行数据分析

学生成绩表，每天会将昨天的历史成绩归档到 scores$yyyyMMdd 的表中。

但是，在一些场景中，还是需要将这些分片表合并成视图后，进行数据分析。此处最简单的做法就是 union all 实现。

先了解 union all 与 union 的区别。

特性	UNION ALL	UNION
去重	不去除重复行，保留所有行	去除重复行，只保留唯一的行
性能影响	比较低，因为不需要额外的去重操作	比较高，因为需要额外的去重操作
结果集行数	结果集行数等于所有查询结果行数总和	结果集行数可能少于所有查询结果行数总和，取决于去重操作
使用场景	当不需要去重并且需要尽快获取结果时使用	当需要合并结果并确保唯一行时使用
语法	`SELECT ... FROM ... UNION ALL SELECT ... FROM ...`	`SELECT ... FROM ... UNION SELECT ... FROM ...`

实现

-- 创建一个函数来动态生成 UNION ALL 查询语句
CREATE OR REPLACE FUNCTION generate_union_all_view()
RETURNS VOID AS $$
DECLARE
    query_text TEXT := '';
    today DATE := current_date;
    archive_date DATE;
BEGIN
    FOR archive_date IN SELECT generate_series((today - interval '30 days')::date, today, interval '1 day')::date
    LOOP
        query_text := query_text || 'SELECT * FROM scores$' || to_char(archive_date, 'YYYYMMDD') || ' UNION ALL ';
    END LOOP;

    -- 去除末尾的 ' UNION ALL ' 部分
    query_text := left(query_text, length(query_text) - length(' UNION ALL '));

    -- 创建视图
    EXECUTE 'CREATE OR REPLACE VIEW scores_archive AS ' || query_text;
END;
$$ LANGUAGE plpgsql;

-- 调用函数生成并创建视图
SELECT generate_union_all_view();

视图会对其引用的基础表进行强连接（strong reference）。这意味着，只要视图存在，引用的基础表就不能被删除，因为视图依赖这些表。

5.) 查询插入：将某表查询出来的数据插入新表

-- 常规的插入语句
insert into test_data(name,age) values('1',1),('2',2)
-- 将查询出来的内容，插入到表里
insert into test_data (name, age)
select ('Name_',generate_series(1, 2000)),  (random() * 100)::int4

6.) 删除插入：删除的数据返回为一张新表

-- 将删除的内容，插入到表里
with deleted_rows as (
  delete from test_data
  where id in (select id from test_data limit 1) 
  returning name,age
)
insert into test_data (name, age)
select name,age from deleted_rows;

那些年，我写的冷门SQL