引言
PostGre数据库是常用的RDBMS,这里对其用法进行总结。
常用语法
引用自 https://blog.csdn.net/meser88/article/details/98170744
-- 建表
drop table if exists t_test;
create table t_test (
id SERIAL PRIMARY KEY -- 主键,自增,但删除数据后id会断层显示
, job_name text
, timest int
, width decimal
, create_time timestamp
)
;
-- 新增注释
comment on table t_test is '播报任务明细表';
comment on column t_test.id is '任务id';
comment on column t_test.job_name is '任务名称';
comment on column t_test.timest is '播报日期间隔的枚举值';
comment on column t_test.width is '字段宽度比例';
comment on column t_test.create_time is '任务创建者';
-- 主键 (如果建表语句里面没添加主键就执行该语句)
alter table public.user add constraint user_pkey primary key (id);
alter table public.user add primary key (id); -- 单一主键
alter table public.user add primary key (id, name); -- 复合主键
alter table public.user drop constraint table_name_pkey; -- 删除主键
-- 查询该表的主键名称
SELECT
pg_constraint.conname AS pk_name
FROM
pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
WHERE
pg_class.relname = 'table_name'
AND pg_constraint.contype = 'p';
-- 索引或唯一索引
drop index if exists index_name;
create index index_name on t_test(id);
drop index if exists user_id_no;
create unique index user_id_no on user (id_no);
CREATE INDEX index_name -- 复合索引
ON t_user (id, name, city);
-- 查看表的索引名称
select * from pg_indexes where tablename = '';
-- 授权
grant all on table public.user to mydata;
grant select, update, insert, delete on table public.user to mydata_dml;
grant select on table public.user to mydata_qry;
-- 当插入pg表的数据,包含单引号时,需要使用 $$ 符号包裹内容。例如:
insert t_test (
sql_text
)
select
$$select * from t_test where dtstatdate >= '%s'$$ -- 这里有引号
from t_test
;
-- 模糊查看表名含有mesong关键字的表
select distinct table_name from information_schema.columns
where table_name like '%mesong%';
-- 换行符。反斜杠在PostGre中没有特殊含义,因此\n无法表示反斜杠的意思
select 'test line 1
test line 2' -- 换行的写法,方法一。直接在sql语句中换行
select 'test line 1' || E'\n' || 'test line 2'; -- 换行的写法,方法二。使用扩展常量
select 'test line 1' || chr(10) || 'test line 2'; -- 换行的写法,方法三。使用chr函数,推荐
-- 保留两位有效数字。例如,将0.108527946208801转化为10.85%
select cast(0.108527946208801*100 as decimal(18, 2)) || '%' -- 方法一
select round(cast(0.108527946208801*100 as numeric), 2) || '%' -- 方法二
-- 时间日期处理函数
select dtdate + interval '1 year'; -- 新增1年。注意,这里的dtdate一定需要是date类型,text类型不行
select dtdate + interval '1 month'; -- 新增1月
select dtdate + interval '1 week'; -- 新增1周
select dtdate + interval '1 day'; -- 新增1日
select extract(year from now()); -- 返回年份,例如2022
select extract(month from now()); -- 返回第几个月份,例如10,表示十月
select extract(week from now()); -- 返回第几周
select date('2021-01-01'); -- 将数据转换为date格式
select current_date -- date类型,返回类似"2022-07-08"
select to_char(current_date, 'yyyyMMdd') -- text类型,返回类似"20220708"
select to_char(current_date + interval '-10 day', 'yyyyMMdd') -- text类型,返回类似"20220628",取10天前的文本类型日期
select date_trunc('week', current_date) -- date类型,返回类似"2022-07-04 00:00:00+08"。返回当周周一
-- 增删字段
alter table t_test add name text;
alter table t_test drop column if exists name;
-- 特性化函数
select string_agg(id, chr(10)) from t -- 将多行数据中的id合并到一个单元格,并加上分隔符chr(10)
-- 查询postgre表的大小
select pg_size_pretty(pg_table_size('demo')) as size;
-- dblink特性,在一个db上读取另外一个db的信息
select
city
, province
, district
from dblink(
'dbname=${db_name} host=${host} user=${host} password=${password}'::text
, 'select * from ${table_name}'::text
) t (city text, province text, district text)
limit 100
-- 查询视图创建的语句
select pg_get_viewdef('viewname', true)
安装
其实现在使用各类db,一般直接申请云上设施的SASS服务即可,现在不再建议在本机手动安装Postgre了。这里仅做记录罢了。
Windows
安装之后会创建默认数据库,库名为 postgres
默认账号为 postgres,密码为 root
Mac
安装参考链接,比较简单。Mac下的Navicat破解比较麻烦,因此推荐直接使用自带的 pgAdmin4,基本的功能都能满足,开箱即用。
Linux(CentOS)
总的来说,在Linux安装Postgre比较麻烦。如果只是为了开发测试,建议安装在本机即可。
# 查看Linux版本
uname -a
# 在CentOS安装PostGre
yum install postgresql postgresql-server
# 用postgres用户初始化数据库
su - postgres
# 初始化PostgreSQL
initdb
# 离开
exit
# 添加PostgreSQL自启动菜单
systemctl enable postgresql
# 启动PostgreSQL
systemctl start postgresql
# 切换用户。postgres 用户是默认自动创建的
su - postgres
# 进去Postgre SQL Shell
psql
# 显示当前用户
select * from current_usert;
# 显示所有用户
\du
# 在psql中进行下列操作
postgres# CREATE USER root WITH PASSWORD 'root';
postgres# CREATE DATABASE root;
postgres# GRANT ALL PRIVILEGES ON DATABASE root to root;
postgres# ALTER USER postgres WITH PASSWORD 'root';
# 退出
\q
# 下次这样登陆
psql -d root -h localhost -p 5432 -U root -W root
查看视图创建语句
在PgAdmin3客户端查看视图的创建语句