PostgreSQL入門教程

image

PostgreSQL介紹

什么是PostgreSQL

PostgreSQL是一個(gè)功能強(qiáng)大的開源對(duì)象關(guān)系數(shù)據(jù)庫管理系統(tǒng)(ORDBMS)。 用于安全地存儲(chǔ)數(shù)據(jù); 支持最佳做法，并允許在處理請(qǐng)求時(shí)檢索它們。

PostgreSQL是跨平臺(tái)的，可以在許多操作系統(tǒng)上運(yùn)行，如Linux，F(xiàn)reeBSD，OS X，Solaris和Microsoft Windows等。

常用于聯(lián)機(jī)事務(wù)處理OLTP（on-line transaction processing）、聯(lián)機(jī)分析處理OLAP（On-Line Analytical Processing）。

PostgreSQL特點(diǎn)

  • 穩(wěn)定

得益于PostgreSQL的多進(jìn)程架構(gòu)，一個(gè)連接的異常并不影響主進(jìn)程和其他連接，從而帶來不錯(cuò)的穩(wěn)定性

  • 高性能

TPCC的性能測試顯示PostgreSQL的性能與商業(yè)數(shù)據(jù)庫基本在同一個(gè)層面上

  • 豐富

查詢豐富：且不說HASH\Merge\NestLoop JOIN，還有遞歸、樹形（connect by）、窗口、rollup\cube\grouping sets、物化視圖、SQL標(biāo)準(zhǔn)等，還有各種全文檢索、正則表達(dá)式、模糊查詢、相似度等。在這些之外，最重要的是PostgreSQL強(qiáng)大的基于成本的優(yōu)化器，結(jié)合并行執(zhí)行（并行掃瞄、并行JOIN等）和多種成本因子，帶來各種各樣豐富靈活高效的查詢支持。

類型豐富：如高精度numeric, 浮點(diǎn), 自增序列，貨幣，字節(jié)流，時(shí)間，日期，時(shí)間戳，布爾， 枚舉，平面幾何，立體幾何，多維幾何，地球，PostGIS，網(wǎng)絡(luò)，比特流，全 文檢索，UUID，XML，JSON，數(shù)組，復(fù)合類型，域類型，范圍，樹類型，化 學(xué)類型，基因序列，F(xiàn)DW, 大對(duì)象, 圖像等。

[PS: 這里的數(shù)組，可以讓用戶像操作JAVA中的數(shù)組一樣操作數(shù)據(jù)庫中的數(shù)據(jù)，如 item[0][1]即表示二維數(shù)組中的一個(gè)元素，而item可以作為表的一個(gè)字段。]

或者，如果以上不夠滿足，你可以自定義自己的類型（create type），并且可以針對(duì)這些類型進(jìn)行運(yùn)算符重載，比如實(shí)現(xiàn)IP類型的加減乘除（其操作定義依賴于具體實(shí)現(xiàn)，意思是：你想讓IP的加法是什么樣子就是什么樣子）。

另外還有各種索引的類型，如btree, hash, gist, sp-gist, gin, brin , bloom , rum 索引等。你甚至可以為自己定義的類型定制特定的索引和索引掃瞄。

功能豐富：PostgreSQL有一個(gè)無與倫比的特性——插件。其利用內(nèi)核代碼中的Hook，可以讓你在不修改數(shù)據(jù)庫內(nèi)核代碼的情況下，自主添加任意功能，如PostGIS、JSON、基因等，都是在插件中做了很多的自定義而又不影響任何內(nèi)核代碼從而滿足豐富多樣的需求。而PostgreSQL的插件，不計(jì)其數(shù)。

FDW機(jī)制更讓你可以在同一個(gè)PostgreSQL中像操作本地表一樣訪問其他數(shù)據(jù)源，如Hadoop、MySQL、Oracle、Mongo等，且不會(huì)占用PG的過多資源。比如阿里開發(fā)的OSS_FDW就用于實(shí)現(xiàn)對(duì)OSS的讀寫。

PostgreSQL語法

PostgreSQL支持大部分SQL:2011特性[1]。
在psql中可以使用\help命令查看所有PostgreSQL語句的語法。使用\help &可查看特定語句語法。

postgres=# \help WITH
Command:     WITH
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP L
OCKED ] [...] ]
  • INSERT
  • SELECT
  • UPDATE
  • DELETE
  • ORDER BY子句
  • GROUP BY子句
  • HAVING子句
  • WHERE條件查詢
  • 表連接
  • 等

https://www.yiibai.com/postgresql/postgresql-syntax.html

PostgreSQL常用函數(shù)及操作符

字符串函數(shù)及操作符

  • ||
語句 結(jié)果
'Value: ' || 42 Value: 42
'String' || null null
  • concat(), concat_ws()
語句 結(jié)果
concat('abc', null, 22) abc22
concat_ws(',', 'abc', null, 33) abc,33
  • position()
語句 結(jié)果
position('om' in 'Thomas') 3
  • substring()
語句 結(jié)果
substring('Thomas' from 2 for 3) hom
substring('Thomas' from '^..') Th

https://www.postgresql.org/docs/10/static/functions-string.html

模式匹配

  • LIKE

全量匹配（除非有%符號(hào)）

語句 結(jié)果
'abc' LIKE '_b%' t
'abc' NOT LIKE '%c' f
  • SIMILAR TO正則表達(dá)式

借用LIKE和部分POSIX正則表達(dá)式，全量匹配（除非有%符號(hào)）

可用的POSIX正則表達(dá)式：| * + ? {m} {m,} {m,n}

語句 結(jié)果
'abc' SIMILAR TO 'a' f
'abc' SIMILAR TO '%(b|d)%' t
'abc' SIMILAR TO '(b|c)%' f
  • POSIX正則表達(dá)式
語句 結(jié)果
'thomas' ~ '.*thomas.*' t
'thomas' ~* '.*Thomas.*' t
'thomas' !~ '.*Thomas.*' t
'thomas' !~* '.*vadim.*' t

https://www.postgresql.org/docs/10/static/functions-matching.html

數(shù)據(jù)類型格式化函數(shù)

  • to_char()
語句 結(jié)果
to_char(current_timestamp, 'HH24:MI:SS') 07:34:48
to_char(-0.1, '99.99') ' -.10'
to_char(1::numeric/2::numeric, 'FM990.00'); '0.50'
  • to_date()
語句 結(jié)果
to_date('05 Dec 2000', 'DD Mon YYYY') 2000-12-05
  • to_number()
語句 結(jié)果
to_number('12,454.8-', '99G999D9S') -12454.8
  • to_timestamp()
語句 結(jié)果
to_timestamp('05 Dec 2000', 'DD Mon YYYY') 2000-12-05 00:00:00+00

日期、時(shí)間操作符及函數(shù)

  • + - * /
語句 結(jié)果
date '2001-09-28' + integer '7' date '2001-10-05'
date '2001-09-28' + time '03:00' timestamp '2001-09-28 03:00:00'
date '2001-09-28' - interval '1 hour' timestamp '2001-09-27 23:00:00'
900 * interval '1 second' interval '00:15:00'
interval '1 hour' / double precision '1.5' interval '00:40:00'

  • current_date, current_time, current_timestamp

  • date_part()

語句 結(jié)果
date_part('hour', timestamp '2001-02-16 20:38:40') 20
date_part('month', interval '2 years 3 months') 3
  • make_date(), make_time(), make_timestamp()
語句 結(jié)果
make_date(2013, 7, 15) date '2013-07-15'
make_time(8, 15, 23.5) time '08:15:23.5'
make_timestamp(2013, 7, 15, 8, 15, 23.5) timestamp '2013-07-15 08:15:23.5'

條件表達(dá)式

  • CASE
CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

CASE expression
    WHEN value THEN result
    [WHEN ...]
    [ELSE result]
END
  • COALESCE
COALESCE(value [, ...])
  • NULLIF
NULLIF(value1, value2)
語句 結(jié)果
SELECT CASE WHEN 3>2 THEN 'A' ELSE 'B' END 'A'
SELECT COALESCE(NULL,'A','B') 'A'
SELECT NULLIF('A','A') NULL
SELECT NULLIF('A','B') 'A'

聚集函數(shù)

  • avg,count,max,min,sum

集合生成函數(shù)

  • generate_series()
SELECT * FROM generate_series(2,4);
 generate_series
-----------------
               2
               3
               4
(3 rows)

SELECT generate_series(1.1, 4, 1.3);
 generate_series 
-----------------
             1.1
             2.4
             3.7
(3 rows)

SELECT * FROM generate_series('2008-03-01 00:00'::timestamp, '2008-03-02 12:00', '10 hours');
   generate_series
---------------------
 2008-03-01 00:00:00
 2008-03-01 10:00:00
 2008-03-01 20:00:00
 2008-03-02 06:00:00
(4 rows)

with子句（通用數(shù)據(jù)表表達(dá)式）

輔助報(bào)表與查詢

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

數(shù)據(jù)修改語句

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

自定義函數(shù)及plpgsql

語法

CREATE [OR REPLACE] FUNCTION function_name(參數(shù)逗號(hào)隔開) 
RETURNS 返回值類型 AS $body$
    DECLARE //聲明變量
        變量名 變量類型 :=變量值;
        例如：name char(20):='su';
    BEGIN  
        /*函數(shù)體；包括DML語句；
        特別注意：如果有返回值，要省略最后的return語句的話，則在函數(shù)體的最后必須是一個(gè)select語句且返回類型必須和上面聲明的類型一樣；
        */
        RETURN 變量名//和上面返回的變量一樣；
    END
    $body$
LANGUAGE plpgsql;

示例

create or replace function f_test() returns void as $$
declare
    begin
        for i in 1..1000 loop
            execute 'insert into test select generate_series('||(i-1)*40000+1||','||i*40000||'),(random()::float)*'||i*0.0001||',clock_timestamp()';
        end loop;
    end;
$$ language plpgsql;

PostgreSQL實(shí)戰(zhàn)

自增主鍵的實(shí)現(xiàn)

  • 使用Sequence
CREATE SEQUENCE users_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
 
alter table users alter column id set default nextval('users_id_seq');
  • 使用serial
CREATE TABLE users
(
id SERIAL primary key ,
name character varying,
password character varying
)

PostgreSQL會(huì)自動(dòng)創(chuàng)建名為users_id_seq的序列，且MAXVALUE=2^63-1

多字段去重統(tǒng)計(jì)的實(shí)現(xiàn)

MySQL中可以使用COUNT(DISTINCT expr,[expr...])統(tǒng)計(jì)多字段不同值的非空行數(shù)

PostgreSQL可以使用如下方式實(shí)現(xiàn)：

SELECT count(DISTINCT column1||'@'||column2) from table;

進(jìn)階

  • 索引[2]

多列索引、復(fù)合索引、gin、bloom、前后模糊查詢、正則查詢

  • 相似度分析[3]

相近、相仿、距離接近、性格接近等等類似這樣的需求，對(duì)數(shù)據(jù)進(jìn)行篩選

  • 全文檢索[4]

參考資料

官方文檔：https://www.postgresql.org/docs/10/static/index.html

https://www.yiibai.com/postgresql

[1]https://www.postgresql.org/docs/10/static/features.html

[2]https://github.com/digoal/blog/blob/master/201612/20161231_01.md
https://github.com/digoal/blog/blob/master/201702/20170205_01.md

[3]https://yq.aliyun.com/articles/59212
[4]https://www.postgresql.org/docs/10/static/functions-textsearch.html
http://blog.chinaunix.net/uid-20726500-id-4820580.html

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 關(guān)于Mongodb的全面總結(jié) MongoDB的內(nèi)部構(gòu)造《MongoDB The Definitive Guide》...
    中v中閱讀 32,303評(píng)論 2贊 89
  • Spring Cloud為開發(fā)人員提供了快速構(gòu)建分布式系統(tǒng)中一些常見模式的工具（例如配置管理，服務(wù)發(fā)現(xiàn)，斷路器，智...
    卡卡羅2017閱讀 136,554評(píng)論 19贊 139
  • 轉(zhuǎn) # https://www.cnblogs.com/easypass/archive/2010/12/ 08/...
    呂品?閱讀 10,123評(píng)論 0贊 44
  • 八月參加了小鹿的21天早餐館打卡，學(xué)到了許多關(guān)于早餐制作和手機(jī)拍照的知識(shí)，也結(jié)交了許多熱愛生活的小伙伴。 始終相信...
    劉元元閱讀 349評(píng)論 4贊 7
  • 【寫在前面】 衡量一個(gè)人是否聰明的標(biāo)準(zhǔn)：大腦里是否有足夠多清晰、準(zhǔn)確、正確的概念，這些概念是否有清晰、準(zhǔn)確、正確的...
    廢材小丟丟閱讀 415評(píng)論 0贊 0

友情鏈接更多精彩內(nèi)容