postgresql基础知识_yyyyjinying-CSDN博客

本文链接：https://blog.csdn.net/jinying_51eqhappy/article/details/131574974

参考： https://www.runoob.com/postgresql/postgresql-schema.html
dbeave数据备份参考：https://blog.csdn.net/weixin_46990523/article/details/132025188

postgres安装

systemctl start docker
mkdir -p /export/server/pgsql/data

docker pull postgres:10.12
docker run -d --restart=always --name postgres -v /export/server/pgsql/data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=123456 -p 5432:5432 postgres:10.12

docker exec -it postgres bash
psql -p 5432 -U postgres

pgsql的配置：

修改postgres用户的密码，方便远程登录（初始登录不需要密码）
ALTER USER postgres WITH ENCRYPTED PASSWORD ‘123456’;
g_hba.conf配置文件所有用户必须密码登录：

# 所有数据库（all）、所有用户（all）、从本机（127.0.0.1/32）均可免密访问（trust）
host    all             all             0.0.0.0/0               md5

创建模式

schema \h
create schema myschema;
create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
DROP SCHEMA myschema;
DROP SCHEMA myschema CASCADE;

dbeaver导出数据
参考： https://blog.csdn.net/WTUDAN/article/details/120767542

新建用户名和密码绑定新的数据库

docker exec -it postgres bash
psql -p 5432 -U postgres
psql -p 5432 -U qardbuser -d qardb 
create user qardbuser with password 'qardbuser';
create database qardb;
GRANT ALL privileges on database qardb TO qardbuser;

-d 数据库
psql
默认的用户和数据库都是postgres

修改默认postgres用户的密码
ALTER USER postgres with encrypted password ‘postgres’;

\help select
\q 退出
\l 查看数据库
\c mydb; connected to database “mydb”

支持的命令选项

创建数据库

https://www.runoob.com/postgresql/postgresql-create-database.html

-- 创建数据库
postgres=# create database mydb;
CREATE DATABASE

-- 切换数据库
postgres=# \c mydb
-- 删除数据库
postgres=# drop database mydb;

postgres=# createdb mydb;
postgres=# create table demo(id serial primary key, name varchar(20));
postgres=# insert into demo(name) values('jack');
-- 查看所有的表
\d  
-- 查看表结构
\d demo

数据库备份命令

docker exec -it postgres bash
root@4923de46438a:/# pg_dump --help
-f --file=FILENAME ：指定输出文件地址
-F, --format=c|d|t|p ：选择文件内容格式(custom, directory, tar，plain text (default))，默认就是文本格式 -Fp

root@4923de46438a:/# pg_dump -U zjyuser -d db2 -Fp -f /var/lib/postgresql/data

pg_dump mydb > mydb.bak
pg_dump mydb > mydb.sql;
pg_dump -f /tmp/mydb.bak mydb;
psql -f /tmp/mydb.bak mydb;

pg_dumpall > pg_backup.bak
pg_dump -F t -f /tmp/mydb.tar mydb;

数据库恢复

权限管理

mydb=# create user mydb with password ‘1234567’;
psql -U mydb -d mydb
DROP USER mydb;

GRANT ALL ON COMPANY TO runoob;
授权数据库
GRANT ALL privileges on database mydb TO mydb;
授权表
GRANT ALL privileges on all tables in schema public TO mydb;

drop user mydb;

进入超级权限修改用户
revoke all privileges on database mydb from mydb;
进去数据库mydb;
revoke all privileges on all tables in schema public from mydb;

postgresql的数据类型

参考文档
https://www.runoob.com/postgresql/postgresql-data-type.html

### 数值类型
integer	4 字节	常用的整数
bigint	8 字节	大范围整数
serial	4 字节	自增整数	1 到 20亿
numeric	可变长	用户指定的精度，精确
### 货币类型
money	8 字节	货币金额类型存储带有固定小数精度的货币金额。
### 字符类型
varchar(n) 变长
char(n)  定长
text
变长，无长度限制
### 日期/时间类型
timestamp 时间戳
date 日期
time 时间
interval 时间间隔
### 布尔类型
boolean 1字节

# 其他类型
枚举类型，几何类型，网络地址类型，位串类型，文本搜索类型，UUID 类型，XML 类型，JSON 类型，数组类型，复合类型，范围类型，对象标识符类型，伪类型
UUID 被写成一个小写十六进制数字的序列，由分字符分成几组， 特别是一组8位数字+3组4位数字+一组12位数字，总共 32 个数字代表 128 位， 一个这种标准的 UUID 例子如下：

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

创建表

create table sys_user(
id INTEGER not null,
username varchar(50),
password VARCHAR(100),
PRIMARY KEY (id),
UNIQUE(username)
)
-- 不区分大小写 pgsql的语法较宽容，没有“；”也可以
drop TABLE SYS_USER;
-- 删除多个表
drop table department, company;

schema模式

PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。一个模式可以包含视图、索引、数据类型、函数和操作符等。相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。
使用模式的优势：
允许多个用户使用一个数据库并且不会互相干扰。
将数据库对象组织成逻辑组以便更容易管理。
第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。
模式类似于操作系统层的目录，但是模式不能嵌套。

-- 创建模式 
create schema myschema;
-- 制定模式下创建表
create table myschema.demo(
	id INTEGER not null,
	name text not null,
	PRIMARY key (id),
	UNIQUE(name)
)
-- depend on it 不能删除
DROP SCHEMA myschema;
-- 删除一个模式以及其中包含的所有对象：级联删除
DROP SCHEMA myschema CASCADE;

插入数据

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   JOIN_DATE      TIMESTAMP -- 想要日期时间格式的只有·时间戳·， date 日期 time 时间
   -- publication_date TIMESTAMP WITH TIME ZONE NOT NULL
);

drop table company;

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (2, 'Paul', 32, 'California', 20000.00,'2001-07-13 12:12:12');
-- 
INSERT INTO COMPANY VALUES (3, 'Paul', 32, 'California', 20000.00,DEFAULT);
INSERT INTO COMPANY VALUES (4, 'Paul', 32, 'California', 20000.00);
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 15000 WHERE ID = 1;
DELETE FROM COMPANY WHERE ID = 4;
SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
SELECT * FROM COMPANY ORDER BY id, SALARY ASC;
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
select * from company;
-- with 子查询
With CTE AS
(Select
NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select age,salary From CTE;
SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;
SELECT DISTINCT name FROM COMPANY;

修改表字段

ALTER TABLE ex.ori_foc_t3005 ALTER COLUMN flight_date TYPE date USING flight_date::date;

追加约束主键

ALTER TABLE ex.ori_foc_t2001 ADD CONSTRAINT ori_foc_t2001_pk PRIMARY KEY (uuid);

PostgreSQL 约束

创建表

-- public.meas_para_outer_dependence definition

-- Drop table

-- DROP TABLE public.meas_para_outer_dependence;

CREATE TABLE public.meas_para_outer_dependence (
	id serial4 NOT NULL, -- 主键id
	outer_value int4 NULL, -- 外部依赖的参数项：1：获取跑道长度
	meas_para_id int4 NOT NULL, -- 测量参数id
	query_script varchar(500) NOT NULL, -- 外部参数的查询脚本
	CONSTRAINT meas_para_outer_dependence_pkey PRIMARY KEY (id)
);

CREATE TABLE public.qar_debugger_csv (
	id serial4 NOT NULL, -- 主键id
	qar_id varchar(50) NOT NULL,
	csv_path varchar(500) NOT NULL, -- 外部参数的查询脚本
	update_time timestamp null,
	CONSTRAINT qar_debugger_csv_pkey PRIMARY KEY (id)
);
CREATE INDEX idx_qar_debugger_csv_qar_id ON public.qar_debugger_csv (qar_id);

-- Column comments

COMMENT ON COLUMN public.meas_para_outer_dependence.id IS '主键id';
COMMENT ON COLUMN public.meas_para_outer_dependence.outer_value IS '外部依赖的参数项：1：获取跑道长度';
COMMENT ON COLUMN public.meas_para_outer_dependence.meas_para_id IS '测量参数id';
COMMENT ON COLUMN public.meas_para_outer_dependence.query_script IS '外部参数的查询脚本';

-- Permissions

ALTER TABLE public.meas_para_outer_dependence OWNER TO qar;
GRANT ALL ON TABLE public.meas_para_outer_dependence TO qar;

复杂sql

select t1.id, count(t2.rc_type), t1.rc_type,
array_length(string_to_array(string_agg(DISTINCT t1.rc_type::text, ','), ','), 1) AS nums
from bus_qar_rc_task t1
         inner join bus_qar_rc_task_flt_detail t2
                    on t2.task_id = t1.id
where t2.rc_type !=0 -- 取非记录参数
    and t2.state in (2,3,4)
group by t1.id



-- 分组统计和字段拆分过滤统计

SELECT
    t1.id,
    COUNT(DISTINCT t2.rc_type) AS distinct_rc_type_count,
    t1.rc_type,
    array_length(array_remove(string_to_array(string_agg(DISTINCT t1.rc_type::text, ','), ','), '0'), 1) AS nums
FROM
    bus_qar_rc_task t1
INNER JOIN
    bus_qar_rc_task_flt_detail t2
ON
    t2.task_id = t1.id
WHERE
    t2.rc_type != 0 -- 取非记录参数
    AND t2.state IN (2, 3, 4)
GROUP BY
    t1.id;