本文基于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.) 查询：从历史表中提取出最新最大的数据

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

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

3.) 查询：分片表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）。这意味着，只要视图存在，引用的基础表就不能被删除，因为视图依赖这些表。

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

-- 常规的插入语句
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

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

-- 将删除的内容，插入到表里
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