PG数据库高级用法_pg 语法

PG数据库高级用法

DOCKER 安装：

1、docker pull postgres:10.0

2、docker run --name progres -v /home/campus/progres/:/var/lib/postgresql/data/ -e POSTGRES_PASSWORD=postgres -p 25432:5432 -d postgres:10

3、访问数据库
psql -h 172.17.0.1 -p 5432 -U postgres

查看数据库
\l
切换数据库
\c pcfollow_development
查看当前数据库下面所有数据库表
\d
查询语句
SELECT * FROM users where username='sysadmin';

JOIN使用

和mysql等数据库类似，PG数据库支持内连接，左连接，右链接；

支持：

全外连接FULL OUTER JOIN

交叉连接CROSS JOIN ：把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行；

内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。

当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。

内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。

INNER 关键字是可选的。

左外连接，首先执行一个内连接。然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。因此，连接的表在 T1 中每一行至少有一行；

外连接

首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。

下面是外连接的基本语法：

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...

UNIONS 基础语法如下：

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

UNION

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

Union all 允许重复行

触发器

PostgreSQL 触发器可以在下面几种情况下触发：

• 在执行操作之前（在检查约束并尝试插入、更新或删除之前）。
• 在执行操作之后（在检查约束并插入、更新或删除完成之后）。
• 更新操作（在对一个视图进行插入、更新、删除时）。

•       触发器的 FOR EACH ROW 属性是可选的，如果选中，当操作修改时每行调用一次；相反，选中 FOR EACH STATEMENT，不管修改了多少行，每个语句标记的触发器执行一次。

•       WHEN 子句和触发器操作在引用 NEW.column-name 和 OLD.column-name 表单插入、删除或更新时可以访问每一行元素。其中 column-name 是与触发器关联的表中的列的名称。

•       如果存在 WHEN 子句，PostgreSQL 语句只会执行 WHEN 子句成立的那一行，如果没有 WHEN 子句，PostgreSQL 语句会在每一行执行。

•       BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。

•       要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 tablename，而不是 database.tablename。

•       当创建约束触发器时会指定约束选项。这与常规触发器相同，只是可以使用这种约束来调整触发器触发的时间。当约束触发器实现的约束被违反时，它将抛出异常。

runoobdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

auditlogfunc() 是 PostgreSQL 一个程序，其定义如下：

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$

   BEGIN

      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);

      RETURN NEW;

   END;

$example_table$ LANGUAGE plpgsql;

删除触发器

删除触发器基础语法如下：

drop trigger ${trigger_name} on ${table_of_trigger_dependent};

删除本文上表 company 上的触发器 example_trigger 的指令为：

drop trigger example_trigger on company;

索引类型

单列索引

单列索引是一个只基于表的一个列上创建的索引，基本语法如下：

CREATE INDEX index_name

ON table_name (column_name);

组合索引

组合索引是基于表的多列上创建的索引，基本语法如下：

CREATE INDEX index_name

ON table_name (column1_name, column2_name);

不管是单列索引还是组合索引，该索引必须是在 WHEHE 子句的过滤条件中使用非常频繁的列。

如果只有一列被使用到，就选择单列索引，如果有多列就使用组合索引。

唯一索引

使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：

CREATE UNIQUE INDEX index_name

on table_name (column_name);

局部索引

局部索引 是在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行。基础语法如下：

CREATE INDEX index_name

on table_name (conditional_expression);

隐式索引

隐式索引 是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

什么情况下要避免使用索引？

虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。

使用索引时，需要考虑下列准则：

索引不应该使用在较小的表上。

索引不应该使用在有频繁的大批量的更新或插入操作的表上。

索引不应该使用在含有大量的 NULL 值的列上。

索引不应该使用在频繁操作的列上。

ALTER TABLE 命令

在 PostgreSQL 中，ALTER TABLE 命令用于添加，修改，删除一张已经存在表的列。

另外你也可以用 ALTER TABLE 命令添加和删除约束。

语法

用 ALTER TABLE 在一张已存在的表上添加列的语法如下：

ALTER TABLE table_name ADD column_name datatype;

在一张已存在的表上 DROP COLUMN（删除列），语法如下：

ALTER TABLE table_name DROP COLUMN column_name;

修改表中某列的 DATA TYPE（数据类型），语法如下：

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

给表中某列添加 NOT NULL 约束，语法如下：

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

给表中某列 ADD UNIQUE CONSTRAINT（ 添加 UNIQUE 约束），语法如下：

ALTER TABLE table_name

ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

给表中 ADD CHECK CONSTRAINT（添加 CHECK 约束），语法如下：

ALTER TABLE table_name

ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

给表 ADD PRIMARY KEY（添加主键），语法如下：

ALTER TABLE table_name

ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

DROP CONSTRAINT （删除约束），语法如下：

ALTER TABLE table_name

DROP CONSTRAINT MyUniqueConstraint;

如果是 MYSQL ，代码是这样：

ALTER TABLE table_name

DROP INDEX MyUniqueConstraint;

DROP PRIMARY KEY （删除主键），语法如下：

ALTER TABLE table_name

DROP CONSTRAINT MyPrimaryKey;

如果是 MYSQL ，代码是这样：

ALTER TABLE table_name

DROP PRIMARY KEY;

LOCK（锁）

锁主要是为了保持数据库数据的一致性，可以阻止用户修改一行或整个表，一般用在并发较高的数据库中。

在多个用户访问数据库的时候若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

数据库中有两种基本的锁：排它锁（Exclusive Locks）和共享锁（Share Locks）。

如果数据对象加上排它锁，则其他的事务不能对它读取和修改。

如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。

LOCK 命令语法

LOCK 命令基础语法如下：

LOCK [ TABLE ]

name

 IN

lock_mode

• name：要锁定的现有表的名称（可选模式限定）。如果只在表名之前指定，则只锁定该表。如果未指定，则锁定该表及其所有子表（如果有）。
• lock_mode：锁定模式指定该锁与哪个锁冲突。如果没有指定锁定模式，则使用限制最大的访问独占模式。可能的值是：ACCESS SHARE，ROW SHARE， ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE， SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，ACCESS EXCLUSIVE

常用函数

PostgreSQL 内置函数也称为聚合函数，用于对字符串或数字数据执行处理。

下面是所有通用 PostgreSQL 内置函数的列表：

COUNT 函数：用于计算数据库表中的行数。

MAX 函数：用于查询某一特定列中最大值。

MIN 函数：用于查询某一特定列中最小值。

AVG 函数：用于计算某一特定列中平均值。

SUM 函数：用于计算数字列所有值的总和。

ARRAY 函数：用于输入值(包括null)添加到数组中。

Numeric 函数：完整列出一个 SQL 中所需的操作数的函数。

String 函数：完整列出一个 SQL 中所需的操作字符的函数。

下面是PostgreSQL中提供的数学函数列表，需要说明的是，这些函数中有许多都存在多种形式，区别只是参数类型不同。除非特别指明，任何特定形式的函数都返回和它的参数相同的数据类型。

三角函数列表

字符串函数和操作符

下面是 PostgreSQL 中提供的字符串操作符列表：

类型转换相关函数

窗口函数：

窗口函数和前面讲的聚合函数时一样的。但是窗口函数要更好用，聚合函数只有一行输出，而窗口函数可以有任意行输出。

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
                               ———————————————————————————————————————
 
 
depname    | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

1. 窗口函数之后总是紧跟一个OVER子句，这正是它与普通函数和非窗口的聚合函数的区别；

2. OVER子句决定如何分割表中的行，也就是窗口函数要处理的数据集，称为窗口；

3. PARTITION BY子句将所有行进行分组，所有与PARTITION BY后跟的表达式有相同值的行都被分到同一组；

4. 窗口函数的输入就是和当前行分到同一组的所有行。

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
 
 
 depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)

1. rank函数用来计算排名。

2. OVER表示rank是窗口函数。

3. PARTITION BY depname将行按depname分组，在每个分组内用rank函数计算排名。

4. ORDER BY salary DESC将分组内的行按salary从高到低（倒序）排序。

5. 正是有ORDER BY字句的存在，所以rank函数才没有带入任何参数，排名规则有ORDER BY代劳了。

6. 相同行的数量却会累加到下一个不同的行。

对于每一行，所有与该行在同一个分组内的行的集合，称为该行的窗口框架。

不使用OREDER BY默认窗口框架包括分组内的所有行。这时窗口框架和分组就是一样的

SELECT salary, sum(salary) OVER () FROM empsalary;
 
 salary |  sum  
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100

OVER子句也可以是空。没有了ORDER BY意味着窗口框架就是整个分组；没有了PARTITION BY意味着分组就是整张表（因为没有WHERE子句等进行筛选）。

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
 
 salary |  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100

有了ORDER BY情况就大不一样的。首先分组还是整张表，但是窗口框架变成了第一行至当前行之间的行。每次求和都是当前行到第一行之间的工资的和，所以每次计算sum都不一样。

相同的行，它们属于同一个窗口框架，所以求和时它们都会参与计算。

窗口函数的执行在WHERE、GROUP BY、HAVING以及聚合函数之后。这意味着两点：

1. 窗口函数不能用于WHERE、GROUP BY以及HAVING子句中；
2. 聚合函数可以作为窗口函数的参数，反之不行。

事实上，窗口函数只能用于SELECT的查询列表以及ORDER BY子句中。

同聚合函数一样，如果需要在窗口函数执行之后再进行过滤（WHERE）或分组（GROUP BY）操作，必须使用嵌套的SELECT子句。

SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
     FROM empsalary) AS ss
WHERE pos < 3;

个窗口函数要处理同一个窗口时

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

继承

只需要在创建表的时候用INHERITS关键字指定从哪个表继承就OK了。

CREATE TABLE cities (
  name       text,
  population real,
  altitude   int     -- (in ft)
);
 
CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);
 
 
INSERT INTO cities      VALUES('Las Vegas', 100000, 2174);
INSERT INTO cities      VALUES('Mariposa',  121000, 1953);
INSERT INTO capitals VALUES('Madison',     2105000,  845);
 
SELECT name, altitude FROM cities WHERE altitude > 500;
 
   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
(3 rows)
 
SELECT name, altitude FROM ONLY cities WHERE altitude > 500;
 
 name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 

ONLY关键字还可以用于UPDATE和DELETE语句。

获取数据库外部数据fwd

功能：

可以取得关系数据库的数据，比如Oracle,MySQL,ODBC.

还可以取得NOSQL数据库的数据。比如CouchDB，Redis

还可以直接访问text文本文件，csv文件。

还可以访问twitter的数据。等等。

 1、添加file_fdw扩展

create extension file_fdw ;

2、创建表并导入数据

create table XXX.....

insert into ....

通过copy拷贝成文件

copy tb10 to '/home/postgres/tb10.csv';

3、创建server(外部服务器)

create server server_file_fdw foreign data wrapper file_fdw;
 
 
 
CREATE SERVER servername [ TYPE 'servertype' ] [ VERSION 'serverversion' ]
    FOREIGN DATA WRAPPER fdwname
    [ OPTIONS ( option 'value' [, ... ] ) ]
 
 
ALTER SERVER servername [ VERSION 'newversion' ]
    [ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) ]
ALTER SERVER servername OWNER TO new_owner
 
 
修改server:https://www.postgresql.org/docs/8.4/sql-alterserver.html
添加server:https://www.postgresql.org/docs/8.4/sql-createserver.html

4、创建外部表 

create foreign table foreign_tb10 (id integer,name character varying,password character varying)server server_file_fdw  options (filename '/home/postgres/tb10.csv');
 
执行计划：
 
explain select * from foreign_tb10 order by id limit 10;

https://blog.csdn.net/z2916310960/article/details/64922503?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-4.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-4.channel_param

RESTFUL  API 实现：http://postgrest.org/en/v5.1/tutorials/tut0.html

1、确认安装PG数据库；

2、安装PostgREST

Note
 
PostgREST requires libpq, the PostgreSQL C library, to be installed on your system. Without the library you’ll get an error like “error while loading shared libraries: libpq.so.5.” Here’s how to fix it:
 
Ubuntu or Debian
sudo apt-get install libpq-dev
Fedora, CentOS, or Red Hat
sudo yum install postgresql-libs
OS X
brew install postgresql
Windows
All of the DLL files that are required to run PostgREST are available in the windows installation of PostgreSQL server. Once installed they are found in the BIN folder, e.g: C:\Program Files\PostgreSQL\10\bin. Add this directory to your PATH variable. Run the following from an administrative command prompt (adjusting the actual BIN path as necessary of course)
 
setx /m PATH "%PATH%;C:\Program Files\PostgreSQL\10\bin"

# download from https://github.com/PostgREST/postgrest/releases/latest
tar xJf postgrest-<version>-<platform>.tar.xz

启动： 

./postgrest 或 postgrest.exe   on Windows

3、创建数据库API

Connect to the SQL console (psql) inside the container. To do so, run this from your command line:
 
sudo docker exec -it tutorial psql -U postgres
You should see the psql command prompt:
 
psql (9.6.3)
Type "help" for help.
 
postgres=#
The first thing we’ll do is create a named schema for the database objects which will be exposed in the API. We can choose any name we like, so how about “api.” Execute this and the other SQL statements inside the psql prompt you started.
 
create schema api;
Our API will have one endpoint, /todos, which will come from a table.
 
create table api.todos (
  id serial primary key,
  done boolean not null default false,
  task text not null,
  due timestamptz
);
 
insert into api.todos (task) values
  ('finish tutorial 0'), ('pat self on back');
Next make a role to use for anonymous web requests. When a request comes in, PostgREST will switch into this role in the database to run queries.
 
create role web_anon nologin;
 
grant usage on schema api to web_anon;
grant select on api.todos to web_anon;
The web_anon role has permission to access things in the api schema, and to read rows in the todos table.
 
It’s a good practice to create a dedicated role for connecting to the database, instead of using the highly privileged postgres role. So we’ll do that, name the role authenticator and also grant him the ability to switch to the web_anon role :
 
create role authenticator noinherit login password 'mysecretpassword';
grant web_anon to authenticator;
Now quit out of psql; it’s time to start the API!
 
\q

4、 Run PostgREST

Create a file tutorial.conf with this inside:

db-uri = "postgres://authenticator:mysecretpassword@localhost:5433/postgres"
db-schema = "api"
db-anon-role = "web_anon"

run the server:

./postgrest tutorial.conf

5、测试

curl http://localhost:3000/todos

The API replies:
 
[
  {
    "id": 1,
    "done": false,
    "task": "finish tutorial 0",
    "due": null
  },
  {
    "id": 2,
    "done": false,
    "task": "pat self on back",
    "due": null
  }
]
With the current role permissions, anonymous requests have read-only access to the todos table. If we try to add a new todo we are not able.
 
curl http://localhost:3000/todos -X POST \
     -H "Content-Type: application/json" \
     -d '{"task": "do bad thing"}'
Response is 401 Unauthorized:
 
{
  "hint": null,
  "details": null,
  "code": "42501",
  "message": "permission denied for relation todos"
}
There we have it, a basic API on top of the database! In the next tutorials we will see how to extend the example with more sophisticated user access controls, and more tables and queries.
 
Now that you have PostgREST running, try the next tutorial, Tutorial 1 - The Golden Key

按照官网一步步执行即可：

官网：http://postgrest.org/en/v7.0.0/

具体高级查询语句参见：http://postgrest.org/en/v5.1/api.html

官方链接：https://www.yiibai.com/manual/postgresql/features.html