彥霖 實驗筆記

CREATE EXTENSION pgcrypto;

-- 再建立 test_user 表格

CREATE TABLE test_user(id SERIAL, username VARCHAR(32), password TEXT);

CREATE unique index idx_test_user_username on test_user using btree (username);

雖然我標題寫 Json、Jsonb 操作，但實際上我這裡只會用 Jsonb 來示範

會用 Jsonb 實際上就會用 Json ，因為 Jsonb 效能比較好 (資料無排序)，但如果你要有排序功能，可以用傳統 Json 來操作

前置作業重點：

插入 Jsonb 無法用 INSERT INTO ，因為他找不到 資料 插....，所以要先用 INSERT INTO 先插入一個 空 資料，這樣未來 UPDATE 才找的到洞插入...

更多資源可以參考 PostgreSQL 官方的 Json 文件：http://www.postgresql.org/docs/9.5/static/function...

範例：

CREATE OR REPLACE FUNCTION x() RETURNS TABLE(a INT, b TEXT, c TEXT) AS
$$
    DECLARE data TEXT := 'Loli';
    BEGIN
        RETURN QUERY SELECT 5, 'Hello'::TEXT, data;
    END;
$$
LANGUAGE plpgsql;

-- 判斷表是否已經有資料了? 沒有的話就做初始化動作，也就是插入 {} 大括號，以初始化 location-Jsonb

IF NOT EXISTS (SELECT * FROM location) THEN
    INSERT INTO location VALUES ('{}');
END IF;

查看擴充套件

select * from pg_extension;

列出可安裝清單，uuid-ossp 會出現在裡面

select * from pg_available_extensions;

安裝

官方文件：陣列 http://www.postgresql.org/docs/9.5/static/functions-array.html


建立 run () 涵式：

整數陣列：

CREATE OR REPLACE FUNCTION run(data int[]) RETURNS SETOF int AS
    $BODY$
        DECLARE i int;

1. 用現在時間寫入 Json 

SELECT jsonb_set('{"A":"B"}', '{"A"}', to_jsonb(now()));

2. Json 時間比大小：

假設我們有以下資料

SELECT * FROM json;
                                 data
------------------------------------------------------------------------------------

這是 PostgreSQL 9.5 版以上才有的語法
詳細可參考這裡：http://www.postgresql.org/docs/9.5/static/functions-json.html

1. 首先我們先建立一個 Json 表格
CREATE TABLE json (data JSONB);

2. 插入 Json 資料
INSERT INTO json VALUES ('{"A":"B"}');

3. 查看我們剛剛的資料

1. 先建立一個 users 表格：

CREATE TABLE users (
    id    serial    PRIMARY    KEY,
    username    TEXT    NOT    NULL,
    passwd    TEXT
);

2. 然後連續插入三次

crypt 就算插入相同的文字，也會顯示不同密碼


CREATE EXTENSION pgcrypto;

AES 加密：
create table demo(pw bytea);
insert into demo(pw) values ( encrypt( 'data', 'key', 'aes') );

AES 解密：

CREATE OR REPLACE FUNCTION run() RETURNS TEXT AS
    $BODY$
        DECLARE k INT;
        BEGIN
            k := 0; -- 第一次宣告需要使用冒號等於
            FOR i IN 1..10 LOOP
                k = k + 1;
            END LOOP;
            RETURN '總共有 ' || k || ' 筆資料。'; -- 兩條直線代表字串串接，類似 Java、C# 的 +
        END

CREATE FUNCTION max (a INT, b INT) RETURNS INT AS
    $BODY$
        BEGIN
            IF (a > b) THEN
                RETURN a;
            ELSE
                RETURN b;
            END IF;
        END
    $BODY$

以下教大家如何用 PostgreSQL 操作 JSON

算是 SQL 與 NoSQL 結合的使用
(以下示範：新增、查詢

修改 pg_hba.conf 檔案

加入可訪問的 IP 地址，如下：

-------------------------------------------

# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.1.10/32 md5

SELECT count(*) FROM data;

select extract(epoch FROM (time0 - time1)) // 取得時間差 (秒數)

select age(now(), timestamp '1989-02-05'); // 取得時間差 (年齡)

ALTER DATABASE <資料庫名稱> SET BYTEA_OUTPUT TO 'escape';

後面的單引號可以輸入 hex 或 escape ，由於預設是 hex 十六進制
所以由程式讀取 ( 如：C#、Java ) 會與放入前數值不一樣，且長度也不一樣
這時我們就需要事前使用這個語法，來改變資料庫輸出格式

我目前有兩個資料庫，一個是 postgres 另一個是 test 資料庫。今天我想要備份 test 資料庫，且 test 資料庫裡面有 my 資料表。

今天我只要在 test 資料庫上 按下滑鼠右鍵，選擇裡面的備份。

接著會進入這裡，選擇好你要備份的路徑，編碼方式、備份格式...等等

假設我新建一個表：CREATE TABLE my ( use   text,  pw   text );

插入一個資料 aaa111aaa 到 use 欄，bbb222bbb 到 pw 欄 且是經過 MD5 加密的：

INSERT INTO my (use,pw) VALUES (md5('aaa111aaa'),md5('bbb222bbb'));

此時我們搜尋 my 表：SELECT * FROM my ; 會得到以下數據：

use                                                         |                    pw
--------------------------------------------------+-------------------------------------------------
6248568d882f68e086bc1b557eba13a6 | 199d0afd164d634563fbfec5dfa29f7d

由上我們可以發現原先資料 aaa111aaa 與 bbb222bbb 資料都被加密了。

SQL99是數據庫的一個ANSI/ISO標準。這個標準的前身是SQL92 ANSI/ISO標準，而SQL92之前還有一個SQL89 ANSI/ISO標準。它定義了一種語言（SQL）以及數據庫的行為（事務、隔離級別等）。你知道許多商業數據庫至少在某種程度上是符合SQL99的嗎？不過，這對於查詢和應用的可移植性沒有多大的意義，這一點你也清楚嗎？
SQL92標準有4個層次：
q 入門級（Entry level）。這是大多數開發商符合的級別。這一級只是對前一個標準SQL89稍做修改。所有數據庫開發商都不會有更高的級別，實際上，美國國家標準和技術協會NIST（National Institute of Standards and Technology，這是一家專門檢驗SQL合規性的機構）除了驗證入門級外，甚至不做其他的驗證。 Oracle 7.0於1993年通過了NIST的SQL92入門級合規性驗證，那時我也是小組中的一個成員。如果一個數據庫符合入門級，它的特性集則是Oracle 7.0的一個功能子集。
q 過渡級。這一級在特性集方面大致介於入門級和中間級之間。
q 中間級。這一級增加了許多特性，包括（以下所列並不完整）：
n 動態SQL
n 級聯DELETE以保證引用完整性
n DATE和TIME數據類型
n 域
n 變長字符串