Postgre SQL
简介
**中文社区: **http://www.postgres.cn/v2/home
**文档: **http://www.postgres.cn/docs/12/
**runoob教程 : **https://www.runoob.com/postgresql/postgresql-tutorial.html
特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS）支持大部分的SQL标准并且提供了很多其他现代特性，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等 免费
特点:
- 支持WINDOWS、Linux、UNIX、MAC OS X、BSD** **支持ACID、关联完整性、数据库事务、Unicode多国语言
- 支持临时表，而物化视图，可以使用PL/pgSQL、PL/Perl、PL/Python或其他过程语言的存储过程和触发器模拟
- 支持R-/R+tree索引、哈希索引、反向索引、部分索引、Expression 索引、GiST、GIN（用来加速全文检索）
- 支持数据域，支持存储过程、触发器、函数、外部调用、游标7）数据表分区方面，支持4种分区，即范围、哈希、混合、列表
- MySQL对于无事务的MyISAM表，采用表锁定，1个长时间运行的查询很可能会阻碍对表的更新，而PostgreSQL不存在这样的问题
** 对于web应用来说，复制的特性很重要，Mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制**。还有mysql的同步是基于binlog复制，类似oracle golden gate, 是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时pgsql还提供stream复制
锁机制
锁是数据库事务的基础，通过锁才能保证数据库在并发时能够保证数据的安全和一致，才能够达到事务的一致性和隔离性。
引入锁同样会增加性能开销和可能的死锁问题,从锁作用的对象上来说可以分为表锁、行锁两种，从排他性来说可以大体分为共享锁和排他锁,但是由于Postgresql是通过多版本的方式对数据库进行更新，它也引入了Access锁
Postgresql的表锁共有8个，而这8个锁又可以分成四个小类：普通锁、访问锁、意向锁和严格锁
事务之间互相等待对方锁定的表。解决方法是申请锁定表时不同事务之间的顺序也要一致
事务之间对锁定了同一个表，但是某个先获取了锁的事务进行了锁升级，造成了死锁。解决方法是同一个事务种要使用不同级别的锁，要先申请最高级别的锁。
SHARE:
共享锁，也就是读锁，当它加到表上之后，整个表只允许读，不允许改，如果我们为一个表创建索引（不带CONCURRENTLY）时，会创建这种锁。它与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE冲突
EXCLUSIVE::
任何的Postgresql的命令都不会加EXCLUSIVE锁，不过在一些操作时，可能会在某些系统表加上此类锁, EXCLUSIVE锁和除了ACCESS SHARE外所有锁冲突，而ACCESS SHARE锁在执行select语句会加该锁，所以一旦表t1被加了EXCLUSIVE锁，对于另一个事务的效果也是只能select，不能修改表
Access锁:
Access锁是Postgresql特有的一种锁，主要是针对Postgresql多版本更新数据的方式而创建的锁。所谓的多版本更新数据，意思是Postgresql在更改某一行数据时，不是在该行直接修改数据，而是另外复制了一个新行，修改都在新行上进行。Access锁有两种：ACCESS SHARE和ACCESS EXCLUSIVE
ACCESS SHARE: 共享锁
select语句会在对应的表上加上ACCESS SHARE类型的锁，通常情况下，任何只读取表而不修改表的查询都会请求这种锁模式。加上了该锁之后，表明即使在修改数据的情况下也允许读数据。ACCESS SHARE锁只和ACCESS EXCLUSIVE锁冲突。
ACCESS EXCLUSIVE:排他锁
ACCESS EXCLUSIVE模式与所有的模式的锁冲突，包括SHARE结尾和EXCLUSIVE结尾的锁，也就是说当一个表加上ACCESS EXCLUSIVE锁之后，该表会阻塞其它事务的任何操作。会对表加ACCESS EXCLUSIVE的操作有ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL等
意向共享锁ROW SHARE:
意向锁是我们要修改表中某一行的数据时，需要先在表上加的一种锁，表示即将要在表的部分行上加上共享锁或者排它锁。也就是说我们在为一个数据表的某些数据行加行锁时，实际上在该表上至少加了两种锁，一种是意向表锁，一种才是行锁。
意向排他锁ROW EXCLUSIVE :
该锁会在UPDATE、DELETE、INSERT命令执行时在相关表上自动创建，它和其它三种EXCLUSIVE的锁和SHARE锁冲突。
SHARE UPDATE EXCLUSIVE:
SHARE ROW EXCLUSIVE : VACUUM(不带FULL选项)、ANALYZE、CREATE INDEX CONCURRENTLY命令会创建该锁。它与SHARE UPDATE EXCLUSIVE、SHARE 、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE锁模式冲突
安装:
****官方网站 https://www.postgresql.org/download/
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
常用操作
临时表
** **分两种，一种是会话级临时表，一种是事务级临时表。在会话级临时表中，数据可以存在于整个会话的生命周期中，在事务级临时表中的数据只能存在于事务的生命周期中,不管是会话级还是事务级的临时表，当会话结束后，临时表会消失
临时表与永久表相似，数据是存储到数据库里，相当于第二次直接关联的是一个小表，查询效率大大提高，表和数据可以根据需求是否保留。
** **临时表的操作不会写入日志文件 提高了 临时表操作的速度
** **只允许当前会话框进行访问，因此不会担心死锁
** **临时表是利用了硬盘(tempdb数据库) 大数据量时适合用临时表
慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表
create temporary table tmptb as (select * from public."User");
select * from tmptb;
drop table tmptb;
插入/修改:
-- 要求你记住列的顺序
insert into table_name values(‘aaa’,’bbb’’,’ccc’);
-- 指定列
insert into table_name (name) values(‘aaa’);
-- 增加列
alter table table_name add column description text;
-- 移除列— 列中的数据将会消失。涉及到该列的表约束也会被移除。
alter table table_name drop column description;
-- 修改数据并返回字段
update test.testtb set height = 33 where id = 1 returning name , age, height, sex;
-- 更改列的默认值
alter table table_name alter column age set default 18;
-- 移除默认值
alter table table_name alter column age drop default;
-- 修改数据类型
alter table table_name alter column age type numeric(10,2);
-- 重命名列
alter table table_name rename column pro_no to product_number;
-- 重命名表
alter table table_name rename to xxx;
查询:
** **group by 必须出现在 select 后且仅有需要分组的字段 不能多余
** **可以结合视图或临时表把有用的字段结果保存二次使用
视图:
create view mytestview as select id , score from test.testtb;
select * from mytestview;
约束:
-- 增加约束:
alter table table_name add check (field_name <> ‘’);
-- 该约束会立即被检查，所以表中的数据必须在约束被增加之前就已经符合约束。
alter table table_name alter column field_name set not null;
-- 移除约束:
alter table table_name drop constralnt some_name;
约束检查:
** 注意 修改了约束可能会**导致后续数据库转储和重新加载失败
建议方法是删除约束（使用ALTER TABLE），调整函数定义，然后重新添加约束，从而对所有表行进行重新检查。
** **检查约束也可以引用多个列
** **指定一个特定列中的值必须要满足一个布尔表达式
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
-- 起名字: 默认随机名字
CREATE TABLE products (
product_no integer,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0)
);
-- 可以多重约束
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric NOT NULL CHECK (price > 0)
);
-- 唯一约束保证\在一列中或者一组列中保存的数据在表中所有行间是唯一的。
field_name integer UNIQUE, 或者 UNIQUE (product_no)
-- 一个主键约束表示可以用作表中行的唯一标识符的一个列或者一组列。这要求那些值都是唯一的并且非空。
field_name integer UNIQUE NOT NULL, 或者 PRIMARY KEY (field_name_a, field_name_c)
-- 一个外键约束指定一列（或一组列）中的值必须匹配出现在另一个表中某些行的值。
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);
COPY: 在一个文件和一个表之间复制数据
可以使用 copy 从文本装在大量数据 注意得有路径权限
copy table_name from ‘/home/user/xxx.txt’;
** **COPY TO把一个表的内容复制 到一个文件 也能复制一个 SELECT查询的结果
** COPY FROM 则从**一个文件复制数据到一个表
设置主键自增:
首先创建序列:
create sequence m_test_id_seq start 1;
注意:空表 start 可以为 1 如果有数据必须必现有数据大才可以
然后再字段默认值里设置 start 或者再 navcat 中 nextval('m_test_id_seq')
事务:
-- 开启一个事务需要将SQL命令用BEGIN和COMMIT命令包围起来
begin ;
select * from mytestview;
rollback;
commit;
-- 可以回滚到自定义保存点
begin ;
select * from mytestview;
savepoint p1;
select * from mytestview;
savepoint p2;
rollback to p2;
-- 以逗号分割字符串为数组
select unnest(string_to_array(‘aaa,bbb,ccc’,’,’);
-- 函数遍历:
create or replace function test()
returns setof text as $$
DECLARE myarray text[] := array['aa','bb','cc'];
tmpstr text;
BEGIN
foreach tmpstr in ARRAY myarray
loop
return next tmpstr;
END loop;
end;
$$ LANGUAGE plpgsql;
select test();
浙公网安备 33010602011771号