数据库杂记

1. PostgreSQL

1.1 安装 UUID 生成扩展

-- 安装 UUID 生成扩展
create extension "pgcrypto";
-- 生成 UUID
select gen_random_uuid() uuid;
-- 删除 UUID 生成扩展
drop extension "pgcrypto";

1.2 根据多个字段去重，并删除重复数据(保留一条)

根据去重字段分组，ID 最小的一条数据保留，其他重复数据删除。

select * 
from student_info 
where (sno, sname) in (select sno, sname from student_info group by sno, sname having count(1) > 1)
and id not in (select min(id) from student_info group by sno, sname having count(1) > 1);

1.3 表字段更新

1.3.1 同一张表用一个字段更新另一个字段

-- 方法一
update student_info t1 set sname = t2.snickname from student_info t2 where t1.id = t2.id;
-- 方法二
update student_info t1 set sname = (select snickname from student_info where id = t1.id);

1.3.2 两张表关联更新

update student_info t1 set score_sum = t2.score_sum from score_info t2 where t1.sno = t2.sno;

注：学生表(student_info) 和 成绩表(score_info) 之间通过 sno 关联。

1.3.3 多张表关联更新

update student_info t1
set grade_name = t2.grade_name, total_score = t2.score_sum
from (
	select tmp1.sno, tmp2.grade_name, tmp1.score_sum
	from score_info tmp1
	inner join grade_info tmp2 on tmp1.gno = tmp2.gno
) t2
where t1.sno = t2.sno;

注：学生表(student_info) 和 成绩表(score_info) 之间通过 sno 关联；年级表(grade_info) 和 成绩表(score_info) 之间通过 gno 关联。

1.4 替换一个字段的部分内容

需求：现有一学号为 20211201，要求先在学号前面加上 DX，然后把 DX 替换为 空字符串，最终使该学号保持不变。

-- 在学号前面加上 DX (两种方法)
update student_info set sno = concat('DX', sno) where sno = '20211201';
update student_info set sno = 'DX' || sno where sno = '20211201';
-- 把 DX 替换为 空字符串(两种方法)
update student_info set sno = replace(sno, E'DX', '') where sno = 'DX20211201';
update student_info set sno = replace(sno, E'DX', '') where sno like E'%20211201';

1.5 计算百分比并保留两位小数

需求：计算 1 号学生英语成绩占语文、数学、英语三科成绩的百分比。

select round(coalesce(t2.english_score, 0::bigint)::numeric * 100::numeric / (t2.chinese_score + t2.math_score + t2.english_score)::numeric, 2) || '%'::text as percentage_of_english_score
from student_info t1
left join score_info t2 on t1.sno = t2.sno 
where t1.sno = '1';

1.6 类型转换

1.6.1 基本类型之间的转换

PostgreSQL 所有基本类型之间的转换只需要在字段名称后面加上 :数据类型 即可。

-- 日期转换为 varchar
select admission_time::varchar from student_info where sno = '1';
-- 字符串转换为 float
select chinese_score::float from score_info where sno = '1';

1.6.2 字符串转换为时间

select * from student_info where admission_time = to_date('2021-09-01 00:00:00', 'YYYY-MM-dd HH24:mi:ss');
select * from student_info where admission_time = to_date('2021-09-01', 'YYYY-MM-dd');
select * from student_info where admission_time = to_date('2021-09', 'YYYY-MM');

1.7 操作用逗号分隔的字符串(当然可以是其他符号分隔)

1.7.1 某一字段的所有结果之间用逗号分隔

通过 string_agg(text, text) 函数即可实现。如，查询所有学生的学号并用逗号分隔

select string_agg(sno, ',') all_sno from student_info;

1.7.2 逗号分隔的字符串转换为表格

通过 regexp_split_to_table(text, text) 函数即可实现。

select regexp_split_to_table('1,2,3,4,5,6', ',') sno;

1.8 判断一个字段是否包含某个字符

select position('.' in '33.33') > 0;	// 33.33 包含字符 '.'，返回值大于 0，结果为 true
select position('.' in '33') = 0;		// 33 不包含字符  '.'，返回值等于 0，结果为 true

1.9 创建只读用户

-- 创建只读角色
CREATE ROLE 角色名称;

-- 对现有表授予访问权限
GRANT USAGE ON SCHEMA public TO 角色名称;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO 角色名称;

-- 对后面新增的表授予访问权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO 角色名称;

-- 创建用户并分配只读角色
CREATE USER 用户名称 WITH PASSWORD '密码';
GRANT 角色名称 TO 用户名称;

1.10 查看用户权限

-- 查看表权限
select * from information_schema.table_privileges where grantee='用户名称';

-- 查看 usage 权限表
select * from information_schema.usage_privileges where grantee='用户名称';

-- 查看存储过程(函数)相关权限表
select * from information_schema.routine_privileges where grantee='用户名称';