一、MySQL与PostgreSQL比较
二、基础知识
三、PostgreSQL基础命令psql语法
- 命令
- psql 参数
三、使用触发器进行分表分库
- 3.1 创建主表
- 3.2 创建触发器
  - Pg定期vacuum

关系型数据库sqlserver,mysql,oracle,postgresql

一、MySQL与PostgreSQL比较

PostgreSQL由于是类似Oracle的多进程框架，所以能支持高并发的应用场景
MySQL的设计目标是成为一个快速的Web服务器后端,拥有多个存储引擎
1.快速的索引序列访问方法（ISAM）2.InnoDB引擎实现了ACID 3.提供了临时表的功能（使用MEMORY存储引擎）4.MyISAM引擎实现了高速读的数据库

PG相对于MySQL的优势：

1、在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨；
2、存储过程的功能支持要比MySQL好，具备本地缓存执行计划的能力；
3、对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强；
4、PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
5、PG的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。
6、MySQL的存储引擎插件化机制，存在锁机制复杂影响并发的问题，而PG不存在。
7、可靠性是PostgreSQL的最高优先级,擅长保护数据方面

MySQL相对于PG的优势：

1、innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀；
2、MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束；
3、MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作；
4、MySQL分区表的实现要优于PG的基于继承表的分区实现，主要体现在分区个数达到上千上万后的处理性能差异较大。
5、MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。

总结

MySQL与PostgreSQL都是免费、开源、强大、且功能丰富的数据库
PGSQL更加适合严格的企业应用场景（比如金融、电信、ERP、CRM）
MySQL更加适合业务逻辑相对简单、数据可靠性要求较低的互联网场景（比如google、facebook、alibaba）

安装教程

菜鸟教程-

二、基础知识

2.1 数据类型

数值数据类型
smallint 存储整数，小范围 2字节 -32768 至 +32767
integer 存储整数。使用这个类型可存储典型的整数 4字节 -2147483648 至 +2147483647
bigint 存储整数，大范围。 8字节 -9223372036854775808 至 9223372036854775807
real 浮点型
serial 序列类型自增 id

int4其实时默认四位不足四位补零。for example 表示一个1 为 0001 。当然同样表示一个1 加上FILL ZERO之后 int4和int8无区别。

字符串数据类型
char(size) 这里size是要存储的字符数。固定长度字符串，右边用空格填充到相等大小的字符。
varchar(size) 这里size是要存储的字符数。可变长度字符串。
text 可变长度字符串。
varchar(n) 和 char(n) 分别是 character varying(n) 和 character(n)的别名，没有声明长度的 character 等于 character(1) ；如果不带长度说明词使用 character varying，那么该类型接受任何长度的字符串。后者是 PostgreSQL 的扩展。
日期/时间数据类型
date 年月日
time 时分秒
timestamp
布尔类型
boolean 它指定true或false的状态。 1字节
特色类型
货币类型 money 货币金额 8字节 -92233720368547758.08 至 +92233720368547758.07
Array 数组类型数组类型详细介绍
inet 网络地址型
json型
xml型
几何类型几何数据类型表示二维空间对象。最根本的类型：点

2.2 日期

查询上个月日期

SELECT current_date-30 as 日期

current_date 当前日期 2020-10-27

2.3 函数

2.4 Sequence序列

sequence 是 pg 自带的高效的自增id工具（也叫序列）。
sequence 使用了轻量级锁的方式来做到高效自增id的，所以会比 UPDATE 行锁快。sequence 的返回数据类型默认是64位的整数，pg 10 可以自定 smallint, integer 或者是 bigint。

# 创建
CREATE SEQUENCE temp_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;-- 缓存一个子序列到内存
# 获取下一个sequence
select nextval('temp_seq'); # 1
# 设置 sequence 的开始值
select setval('temp_seq', 42);
# 获取下一个sequence
select nextval('temp_seq'); # 43

# 创建一个自动循环的 sequence
CREATE SEQUENCE temp_seq2 MINVALUE 18 MAXVALUE 20 CYCLE;
select nextval('temp_seq2'); # 18
select nextval('temp_seq2'); # 19
select nextval('temp_seq2'); # 20
select nextval('temp_seq2'); # 18

# 创建表的时候把类型设置为 serial 会自动创建一个 sequence
CREATE TABLE temp_seq_table (id serial);
postgres=# \d temp_seq_table
                         Table "public.temp_seq_table"
 Column |  Type   |                          Modifiers
--------+---------+-------------------------------------------------------------
 id     | integer | not null default nextval('temp_seq_table_id_seq'::regclass)

# 自动创建了 sequence temp_seq_table_id_seq

将序列设置为最大

select setval('client_queue_id_seq',(select max(id) from client_queue))

postgresql修改自增序列

----删除前先解除 id 对该序列的依赖
ALTER TABLE tablename ALTER COLUMN id SET DEFAULT null;
DROP SEQUENCE IF EXISTS sequence_name;
---- id_max 即 id 目前的最大值，可写为1，可通过 “SELECT MAX(id) FROM tablename” 得到
CREATE SEQUENCE sequence_name START WITH id_max;
ALTER TABLE tablename ALTER COLUMN id SET DEFAULT nextval('sequence_name'::regclass);

或者

SELECT setval('"public"."sequence_name"', id_max, true);

三、PostgreSQL基础命令psql语法

命令

> \l   列出所有数据库  psql -l
> \c dbname   切换数据库
> \dt 预览表
> \d  tablename 查看指定表的所有字段
> q  退出数据库
> \x 扩展显示查询语句

psql 参数

-c
–command = command
指定psql执行给定的命令字符串。可以使用-f选项以任何顺序和组合此选项。
当任意-c或-f指定，psql不从标准输入读取命令；相反，它在按顺序处理所有-c和-f选项后终止。

命令必须是服务器可完全解析的命令字符串（即不包含psql特定的功能）或单个反斜杠命令。
因此不能在-c选项中混合使用SQL和psql元命令。
-f filename
–file = filename
从文件filename中读取命令，而不是标准输入。可以使用-c选项以任何顺序重复和组合此选项。
当任意-c或-f指定，psql不从标准输入读取命令；
相反，它在按顺序处理所有-c和-f选项后终止。
除此之外，此选项在很大程度上等同于元命令\i
如果filename是- （连字符），则读取标准输入直到EOF只是或\q元命令。
这可以用于将交互式输入与来自文件的输入交叉。
但请注意，在这种情况下不能使用Readline（就像指令了-n一样）。

连接数据库

$ psql -U postgres -h localhost -p 5432 -d dbname

不输入密码直接连接

psql -h *.com -p 3433 "dbname=test_db user=post password=pw"
# 不输入密码直接连接2    不推荐
set PGPASSWORD=mypassword
psql  -f upgrade.sql -U postgres mydbname

执行sql文件

psql -d db1 -U userA -f /pathA/xxx.sql
# 执行sql文件
>\i /pathA/xxx.sql

数据备份及导出

pg_dump  -U username -h host -p 3433 -t tableName -f /tmp/***.sql dbname 

pg_dump -h host  -p 3433 -U username -t tableName dbname >/tmp/***.sql

导出导入csv文件

导出
psql  -h host -p 3433 -U username -d dbname 
-c "COPY (select * from tableName where user_id='***') to STDOUT with csv header" > ***.csv

导入
postgres=# COPY vender(sid,vender_id,vender_name,connector,created_by,created_dt,version,del_flg)
 from 'E:\Vendor.csv' WITH CSV  HEADER;

postgres=# copy temp (col1, col2, col3) FROM 'C:/Users/1.csv' DELIMITER ',' CSV ENCODING 'UTF8';

使用python导入导出

https://blog.csdn.net/u014420014/article/details/97641517#python_2

sql

创建数据库,删除数据库

create database testdb;
drop database testdb;

建表语句

CREATE TABLE public.student2(
  id integer NOT NULL,
  name character(100),
  subjects character(1),
  CONSTRAINT student2_pkey PRIMARY KEY (id)
)
WITH (       # 用于表的 WITH 子句还可以包含 OIDS=TRUE 或单独的 OIDS 来指定给新表中的每一
  OIDS=FALSE # 行都分配一个 OID(对象标识符)，或者 OIDS=FALSE 表示不分配 OID 。
);
ALTER TABLE public.student2 OWNER TO postgres;
COMMENT ON TABLE public.student2 IS '这是一个学生信息表2';
----------------------------------
CREATE SEQUENCE table_name_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
CREATE TABLE "public"."table_name" (
  "id" INTEGER  NOT NULL DEFAULT nextval( 'table_name_id_seq' :: REGCLASS),
  "name"           VARCHAR(100)      NOT NULL,
  "enterprise_id"  VARCHAR(100) COLLATE "pg_catalog"."default",
  "hit"            SMALLINT           DEFAULT 1,
  "create_time"    TIMESTAMPTZ(6)     DEFAULT now(),
  "update_time"    TIMESTAMPTZ(6)     DEFAULT now(),
  CONSTRAINT "table_name_pkey" PRIMARY KEY ("id")
);

删除表=========

drop table table_name;

改表语句

删除列

ALTER TABLE "public"."table_name" DROP COLUMN "column_name";

添加列

ALTER TABLE "public"."table_name" ADD COLUMN "column_name" int[] NOT NULL DEFAULT '{1,2,3,4,5,6}';
COMMENT ON COLUMN "public"."table_name"."column_name" IS '我是备注';

修改列类型

ALTER TABLE wordle ALTER COLUMN result TYPE  VARCHAR(7000);

重命名列

alter table public.agent_tag_analyze rename COLUMN tags TO tag_by_week;

索引

查看索引
select * from pg_indexes where tablename='wordle_stop_words';  
创建索引
CREATE INDEX idx_queue_enterprise_id ON queue(enterprise_id);

CREATE INDEX "table_column_idex" ON "public"."table" USING btree (
     "column" "pg_catalog"."int4_ops" ASC NULLS LAST
);

删除索引
DROP INDEX index;

约束

删除check约束
ALTER TABLE "dm_alarms_fct_20170613" Drop constraint "dm_alarms_fct_20170613_dm_transct_date_key_check";
删除非空约束
alter table bot alter column nickname drop not null;

查询语句
offset 0 limit 10而Mysql里面是 limit 0,10

https://www.yiibai.com/postgresql/postgresql-create-table.html

三、使用触发器进行分表分库

注释 ||表示拼接，如’a’||’b’ 等价于’ab’

3.1 创建主表

CREATE TABLE public.table_name (
    "id" bigint,
    "partition_key" date,
    "enterprise_id" varchar(255),
    "create_time" timestamp(6) with time zone DEFAULT now()
);

COMMENT ON COLUMN "public"."table_name "."id" IS '主键';
COMMENT ON COLUMN "public"."table_name "."partition_key" IS '时间分区的key值';
COMMENT ON COLUMN "public"."table_name "."enterprise_id" IS '企业ID';
COMMENT ON COLUMN "public"."asr_result"."create_time" IS '创建时间';

CREATE SEQUENCE public.table_name_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.table_name_id_seq OWNED BY public.table_name.id;

ALTER TABLE ONLY public.table_name ALTER COLUMN id SET DEFAULT nextval('public.table_name_id_seq'::regclass);

3.2 创建触发器

--动态创建分区，能够最大限度的容错
CREATE OR REPLACE FUNCTION function_name()
RETURNS TRIGGER AS $$
-- 定义变量
DECLARE date_text TEXT;      -- 'YYYY_MM_DD'字串,用做分区子表的后缀
DECLARE insert_statement TEXT;
BEGIN
    -- 定义插入语句
    SELECT to_char(NEW.partition_key, 'YYYY_MM_DD') INTO date_text;
    insert_statement := 'INSERT INTO table_name_' || date_text ||' VALUES ($1.*)';
    EXECUTE insert_statement USING NEW;
    RETURN NULL;
    EXCEPTION
    WHEN UNDEFINED_TABLE
    THEN
        EXECUTE
            'CREATE TABLE IF NOT EXISTS table_name_' || date_text
            || '(CHECK (partition_key = ''' || date_text
            || ''')) INHERITS (asr_result)';
        RAISE NOTICE 'CREATE NON-EXISTANT TABLE table_name_%', date_text;
        EXECUTE
            'CREATE INDEX table_name_partition_key_' || date_text
            || ' ON table_name_' || date_text  || '(partition_key)';
        EXECUTE
            'CREATE INDEX table_name_enterprise_id_' || date_text
            || ' ON table_name_' || date_text || '(enterprise_id)';
        EXECUTE insert_statement USING NEW;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

--挂载分区Trigger
CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW EXECUTE PROCEDURE function_name();

注意查询分表分库时,where中必须带主键

其他类似分表文章

Pg定期vacuum

https://blog.csdn.net/u011598529/article/details/49276029