【postgres】基础操作命令

数据库连接

使用主机连接

psql -h 192.168.xx.xx -U {dbuser} -p {dbport}-d {dbname}

使用socket连接

psql -h {socketdir} -p {dbport} -d {dbname} -U {dbuser}

psql 工具命令行连接参数说明

数据库

查看所有数据库

\l

删除数据库

drop database test001;

表

创建表

create table three_kingdoms_people(
	id serial primary key,
	name text not null,
    factions char(4)
);

插入数据

insert into three_kingdoms_people (name, factions) 
values ('刘备', '蜀'), ('曹操', '魏'), ('孙权', '吴'), ('关羽', '蜀'), ('夏侯惇', '魏'), ('周瑜', '吴');

查看所有表

\d

查看表结构，会列出指定表的详细结构信息，包括列名、数据类型、约束等。

\d {db_name}

修改表名

alter table {tbname} rename to {newtbname};

添加字段

alter table {tbname} add column {columnname} varchar(50);

删除字段

alter table {tbname} drop {columnname};

修改字段名

alter table {tbname} rename {columnname} to {newcolumnname};

往一个字段中添加一个json

update school set info = '{"name": "qinghua"}'::jsonb where id=1;

修改一个字段中的json

update school set info = info ::jsonb || '{"address": "beijing"}'::jsonb where id=1;
update school set info = info ::jsonb || '{"name": "beida"}'::jsonb where id=1;

查询
注意单引号、双引号的区别。
单引号：单引号括起来的东西，表示字符串。
双引号：双引号只能用在列名上。

-- 返回的字段名是name
select 'hello, world' name;

-- 返回的字段名是name
select 'hello, world' Name;

-- 返回的字段名是Name
select 'hello, world' "Name";

查询的字段内容是字典，怎么过滤查询

举例：school表有2条记录，其中的info字段是text类型，存的内容是
记录1： {“name”: “qinghua”, “city”: “bj”, “famous”: false}
记录2：{“name”: “jialidun”, “city”: “ss”, “famous”: true}
要求：查询出city为bj，并且famous是false的记录

SELECT * FROM school 
WHERE (info::json->>'city') = 'bj' 
AND info::jsonb ? 'famous'
AND (info::json->>'famous')::boolean = false;

其中info::jsonb ? 'famous' 表示字典里是否有famous这个字段，如果条件想写为info不包含famous键，写法为not (info::jsonb ? 'famous')

存储过程

call process_test();

函数

select * from func_test();

自带函数

concat_ws

SELECT concat_ws ( ', ', 'hello', 'world!' );

拼接效果：hello, world!

array_agg

array_agg是一个聚合函数，将一组输入返回一个数组，看例子：
有一张三国人物表，按照阵营聚合人物，比如: 蜀国：刘备,关羽，把每个阵营的人都聚合起来。

按阵营聚合:

select factions, array_agg(name) from three_kingdoms_people tkp group by factions ;

聚合时按名称排序

select factions, array_agg(name order by name asc) from three_kingdoms_people tkp group by factions ;

将数组转换为字符串，用’,'拼接

select factions, array_to_string(array_agg(name), ',') from three_kingdoms_people tkp group by factions ;

string_agg

和array_agg很相似，但是不用配合array_to_string，就可以直接将数组用指定分隔符连接。

select factions, string_agg(name, ',' order by name desc) from three_kingdoms_people tkp group by factions ;