SQL • 無限: 2016

2016年7月22日星期五

PostgreSQL 監控程式資源

使用套件

POWA

使用原生程式

最佳化工具

PostgreSQL Wiki

部落客文章

https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/
http://www.butleranalytics.com/5-free-postgresql-dashboard-software/

官網記錄

https://www.postgresql.org/download/products/5-reporting-tools/

2016年7月18日星期一

PostgreSQL 備份與復原

目標設定：

PostgreSQL 的備份資料庫指令：pg_dump

pg_dump [資料庫名稱] -U [使用者帳號] -f [備份檔名稱.備份日期.sql]

PostgreSQL 的回復資料庫指令：psql

psql -f [備份檔名稱.備份日期.sql] [資料庫] [使用者名稱]

操作練習流程

先轉成 postgres 使用者：
```
# su - postgres
```

備份 student 資料庫：

$ pg_dump student -U student -f student_20160718.tar.gz

回復 student 資料庫：

$psql -f student_20160718.tar.gz student student

查看 student 資料庫：

$psql -U student
student=# \c student
student=# \d

PgHero 的安裝與使用

PgHero介紹：

用來分析與監控 PostgreSQL 內不良的 SQL 語法
- 軟體下載與安裝網址：https://github.com/ankane/pghero

快速安裝流程

下載安裝的認證 key：

# rpm --import https://rpm.packager.io/key

編寫 repo 檔案：

# vim /etc/yum.repos.d/pghero.repo
[pghero]
name=Repository for pghero/pghero application.
baseurl=https://rpm.packager.io/gh/pghero/pghero/centos7/master
enabled=1

安裝 PgHero ：
```
# yum install pghero
```

連結需要分析的資料庫：

# pghero config:set DATABASE_URL=postgres://user:password@hostname:5432/dbname
(例如：)
pghero config:set DATABASE_URL=postgres://postgres:123456@127.0.0.1:5432/student

2016年7月13日星期三

Pgpool-II 相關指令的使用

指令介紹：

利用 psql 指令的 show ，帶出 pgpool-II 的指令：
- pool_status
- pool_nodes
- pool_processes
- pool_pools
- pool_version

操作練習

登入 psql 操作介面：

#psql -p 5432 -h 192.168.1.63 -Upgpool postgres
(輸入密碼之後，出現 psql 提示字元)
#postgres=

顯示 pgpool-II 組態值：

postgres=# show pool_status;
 (會顯示所有 pgpool.conf 設定內容)

顯示 pool 內各節點所扮演的角色：

postgres=# show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role  | select_cnt
---------+----------+------+--------+-----------+--------+------------
 0       | h4       | 5432 | 2      | 0.500000  | master | 386
 1       | h5       | 5432 | 2      | 0.500000  | slave  | 386
(2 rows)

※status 值說明：1:節點已啟動，但沒有連接。2:節點已啟動，有連接。3:節點 down。

顯示 pool 內，各 process 使用情形：

postgres=# show pool_processes;
 pool_pid |     start_time      | database | username |     create_time     | pool_counter
----------+---------------------+----------+----------+---------------------+--------------
   : (省略)
 17377    | 2016-07-13 15:19:05 | postgres | pgpool   | 2016-07-13 15:36:32 | 1
   : (省略)

顯示各 pool 使用情形：
```
postgres=# show pool_pools;
```

顯示 pgpool-II 版次：

postgres=# show pool_version;
   pool_version
-------------------
 3.5.3 (ekieboshi)
(1 row)

2016年7月11日星期一

PostgreSQL 修改表格語法(二)

目標說明

表格建立的SQL語法，請參考這一篇。
表格修改的SQL語法，請參考這一篇。
本篇著重於主鍵、外來健的新增、修改與刪除。

主健、外來健新增的方式如下：

CREATE TABLE table_name (
   column_name data_type column_constraint table_constraint,
   [ ... ]
)

其中 column_constraint 是:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

並且 table_constraint 是:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH 運算子 [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ...
] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

主健、外來健修改的方式如下：

ALTER TABLE action [, ... ];

其中 action 是:
    ADD [ COLUMN ] column_name data_type CHECK column_constraint
    ADD table_constraint [ NOT VALID ]
    ADD table_constraint_using_index
    DROP CONSTRAINT column_constraint

並且 table_constraint_using_index 是:

    [ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY } USING INDEX index_name
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

連結 student 資料庫：

postgres=# \c student
已經連線到資料庫 "student"，使用者 "postgres"。

修改 score 表內的 student_id 為 Primary key：

student=# ALTER TABLE score ADD PRIMARY KEY (student_id);
注意:  ALTER TABLE / ADD PRIMARY KEY 將會建立隱含索引 "score_pkey" (適用於資料表 "score")
ALTER TABLE
student=#

將 score 表內的 student_id 關聯至 baseData表格內的 id：

student=# ALTER TABLE score ADD FOREIGN KEY (student_id) REFERENCES basedata(id);
ALTER TABLE

查詢 score 表格目前情狀：

student=# \d score
            資料表 "public.score"
    欄位    |         型別          | 修飾詞
------------+-----------------------+---------
 student_id | character varying(11) | 非 Null
 subject_id | character varying(11) |
 score      | integer               |
索引:
    "score_pkey" PRIMARY KEY, btree (student_id)
檢查條件約束
    "score_check" CHECK (score >= 0 AND score <= 100)
外鍵條件約束:
    "score_student_id_fkey" FOREIGN KEY (student_id) REFERENCES basedata(id)

student=#

刪除 score 表格主鍵：

student=# ALTER TABLE score DROP CONSTRAINT "score_pkey";
ALTER TABLE

新增成績資料到 score 表格：

student=# INSERT INTO score(student_id,subject_id,score)
student-# VALUES('S0001','AT0001',89);
INSERT 0 1
student=# INSERT INTO score(student_id,subject_id,score)
student-# VALUES('S0001','AT0002',90);
INSERT 0 1

查詢 score 資料表內容:

student=# SELECT * FROM score;
 student_id | subject_id | score
------------+------------+-------
 S0001      | AT0001     |    89
 S0001      | AT0002     |    90
(2 筆資料列)

新增不存在的學生成績資料到 score 表格：

student=# INSERT INTO score(student_id,subject_id,score)
VALUES('S0005','AT0002',90);
錯誤:  資料表 "score" 的插入或更新違反外鍵限制 "score_student_id_fkey"
詳細資料: 索引鍵 (student_id)=(S0005) 沒有出現在資料表 "basedata"。
student=#

PostgreSQL 修改表格語法(一)

基本語法

查詢資料表中的資料 SQL 語法：

ALTER TABLE [ IF EXISTS ] [ ONLY ] 名稱 [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] 名稱 [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] 名稱 [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] 名稱
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] 名稱
    SET SCHEMA new_schema

其中 action 是:

    ADD [ COLUMN ] column_name data_type [ COLLATE 定序 ] [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE 定序 ] [ USING expression ]
    ALTER [ COLUMN ] column_name SET DEFAULT expression
    ALTER [ COLUMN ] column_name DROP DEFAULT
    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column_name SET STATISTICS 整數
    ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
    ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
    ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD table_constraint [ NOT VALID ]
    ADD table_constraint_using_index
    VALIDATE CONSTRAINT constraint_name
    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    DISABLE RULE rewrite_rule_name
    ENABLE RULE rewrite_rule_name
    ENABLE REPLICA RULE rewrite_rule_name
    ENABLE ALWAYS RULE rewrite_rule_name
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITH OIDS
    SET WITHOUT OIDS
    SET ( storage_parameter = value [, ... ] )
    RESET ( storage_parameter [, ... ] )
    INHERIT parent_table
    NO INHERIT parent_table
    OF type_name
    NOT OF
    OWNER TO new_owner
    SET TABLESPACE new_tablespace

並且 table_constraint_using_index 是:

    [ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY } USING INDEX index_name
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

連結 student 資料庫：

postgres=# \c student
已經連線到資料庫 "student"，使用者 "postgres"。

查詢資料庫內的資料表：

student=# \d
             relation 清單
 架構模式 |   名稱   | 型別  |  擁有者
----------+----------+-------+----------
 public   | baselist | table | postgres
(1 筆資料列)

查詢資料表 baselist 的欄位：

student=# \d baselist
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

新增欄位 age ，並且加入限制 0~200 條件：

student=# ALTER TABLE baselist ADD COLUMN age int CHECK (age >=0 AND age <= 200);
ALTER TABLE

查詢資料表 baselist 的欄位：

student=# \d baselist
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
 age     | integer                |
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)
檢查條件約束
    "baselist_age_check" CHECK (age >= 0 AND age <= 200)

故意加入一個超出範圍值的資料：

student=# UPDATE baselist SET age=201 WHERE id='S0001';
錯誤:  關係 "baselist" 的新資料列違反檢查限制 "baselist_age_check"
詳細資料: Failing row contains (S0001, Peter, 0912345678, Hd. Rd., No. 4321, 201).

加入一個合理範圍值的資料：

student=# UPDATE baselist SET age=20 WHERE id='S0001';
UPDATE 1
student=# SELECT * FROM baselist WHERE id='S0001';
  id   | name  |   phones   |      address      | age
-------+-------+------------+-------------------+-----
 S0001 | Peter | 0912345678 | Hd. Rd., No. 4321 |  20
(1 筆資料列)

修改表格欄位名稱 age ，成為 era ：

student=# ALTER TABLE baselist RENAME COLUMN age TO era;
ALTER TABLE
student=# \d baselist;
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
 era     | integer                |
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)
檢查條件約束
    "baselist_age_check" CHECK (era >= 0 AND era <= 200)

刪除檢查限制：

student=# ALTER TABLE baselist DROP CONSTRAINT "baselist_age_check";
ALTER TABLE

刪除 baselist 表格內的 era 欄位：

student=# ALTER TABLE baselist DROP COLUMN era;
ALTER TABLE

查詢 baselist 表格內容：

student=# SELECT * FROM baselist;
  id   |  name   |   phones   |      address
-------+---------+------------+-------------------
 S0002 | Jupiter | 0987654321 | Rd. Le, No. 123
 S0001 | Peter   | 0912345678 | Hd. Rd., No. 4321
(2 筆資料列)

修改表格 baselist 名稱，成為 baseData 表格名稱：

student=# ALTER TABLE baselist RENAME TO baseData;
ALTER TABLE
student=# \d baseData
          資料表 "public.basedata"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

PostgreSQL 查詢資料語法

基本語法

查詢資料表中的資料 SQL 語法：

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING 運算子 ] [ NULLS { FIRST | LAST }
 ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

其中 from_item 可以是:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...]
 | column_definition [, ...] ) ]
    function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join
_column [, ...] ) ]

並且 with_query 是:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert |
update | delete )

TABLE [ ONLY ] table_name [ * ]

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

連結 student 資料庫：

postgres=# \c student
已經連線到資料庫 "student"，使用者 "postgres"。

查詢資料庫內的資料表：

student=# \d
             relation 清單
 架構模式 |   名稱   | 型別  |  擁有者
----------+----------+-------+----------
 public   | baselist | table | postgres
(1 筆資料列)

查詢資料表 baselist 的欄位：

student=# \d baselist
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

查詢資料表 baselist 的全部資料：

student=# SELECT * FROM baselist;
  id   |  name   |   phones   |      address
-------+---------+------------+-------------------
 S0001 | Peter   | 0912345678 | Hd. Rd., No. 4321
 S0002 | Jupiter | 0987654321 | Rd. Le, No. 123
(2 筆資料列)

以 phones 欄位逆向排序，查詢資料表 baselist 的全部資料：

student=# SELECT * FROM baselist ORDER BY phones DESC;
  id   |  name   |   phones   |      address
-------+---------+------------+-------------------
 S0002 | Jupiter | 0987654321 | Rd. Le, No. 123
 S0001 | Peter   | 0912345678 | Hd. Rd., No. 4321
(2 筆資料列)

以 name 欄位為查詢條件，查詢資料表 baselist 內的資料：

student=# SELECT * FROM baselist WHERE name='Peter';
  id   | name  |   phones   |      address
-------+-------+------------+-------------------
 S0001 | Peter | 0912345678 | Hd. Rd., No. 4321
(1 筆資料列)

使用萬用字元 '%' 的查詢方式：

student=# SELECT * FROM baselist WHERE name LIKE 'Pe%';
  id   | name  |   phones   |      address
-------+-------+------------+-------------------
 S0001 | Peter | 0912345678 | Hd. Rd., No. 4321
(1 筆資料列)

只列出部份欄位的查詢方式：

student=# SELECT name,phones FROM baselist WHERE name LIKE 'Pe%';
 name  |   phones
-------+------------
 Peter | 0912345678
(1 筆資料列)

PostgreSQL 新增、修改與刪除資料

基本語法

新增資料表中的資料 SQL 語法：

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

刪除資料表中的資料 SQL 語法：

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING using_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

更新資料表中的資料 SQL 語法：

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

連結 student 資料庫：

postgres=# \c student
已經連線到資料庫 "student"，使用者 "postgres"。

查詢資料庫內的資料表：

student=# \d
             relation 清單
 架構模式 |   名稱   | 型別  |  擁有者
----------+----------+-------+----------
 public   | baselist | table | postgres
(1 筆資料列)

查詢資料表 baselist 的欄位：

student=# \d baselist
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

插入一筆資料：

student=# INSERT INTO baselist(id,name,phones,address)
student-# VALUES('s0002','Jupiter','0987654321','test Rd., No. 123');

INSERT 0 1

查詢 baselist 資料表內容：

student=# SELECT * FROM baselist;
  id   |  name   |   phones   |      address
-------+---------+------------+-------------------
 S0001 | Peter   | 0912345678 | test. No. 32
 s0002 | Jupiter | 0987654321 | test Rd., No. 123
(2 筆資料列)

刪除一筆 id 為 s0002 的資料：

student=# DELETE FROM baselist WHERE id = 's0002';
DELETE 1

更新除一筆 id 為 S0001 的資料內容：

student=# UPDATE baselist SET address='Hd. Rd., No. 4321' WHERE id = 'S0001';
UPDATE 1

查詢 baselist 資料表內容：

student=# SELECT * FROM baselist;
  id   | name  |   phones   |      address
-------+-------+------------+-------------------
 S0001 | Peter | 0912345678 | Hd. Rd., No. 4321
(1 筆資料列)

參考文獻：

https://www.howtoforge.com/tutorial/virtual-hosting-with-vsftpd-and-mysql-on-ubuntu-15.10/

2016年7月7日星期四

PostgreSQL 監控、分析與統計工具

原始工具：

https://wiki.postgresql.org/wiki/9.1%E7%AC%AC%E4%BA%8C%E5%8D%81%E4%B8%83%E7%AB%A0
http://twpug.net/docs/postgresql-doc-8.0-zh_TW/monitoring-stats.html
pghero -- https://github.com/ankane/pghero
pgFouine -- http://pgfouine.projects.pgfoundry.org/

商用工具：

https://docs.citusdata.com/en/v5.1/installation/production_rhel.html
https://www.compose.com/articles/loading-google-analytics-data-to-postgresql-using-python/
http://chuansong.me/n/615176
http://www.it610.com/article/2662277.htm
http://itindex.net/detail/47304-postgres-%E6%95%B0%E6%8D%AE%E5%BA%93-%E5%88%86%E6%9E%90

問題與討論

索引問題：http://stackoverflow.com/questions/3318727/postgresql-index-usage-analysis

2016年7月5日星期二

PostgreSQL 有用的文章

http://conbar.pixnet.net/blog/post/9618904-(%E6%95%B4%E7%90%86%E4%B8%AD)enterprisedb-vs.-oracle%E8%B3%87%E6%96%99%E5%BA%AB%E7%9B%B8%E7%95%B0%E8%99%95%E6%AF%94%E8%BC%83--
http://postgresql-chinese.blogspot.tw/2007/03/postgresql-tablespace.html
http://postgresql.wisdomfish.org/zi-liao-ku-de-he-xin-li-lun-yu-shi-wu/ch-zi-liao-ku-lun-shu

2016年7月4日星期一

Psql 基本操作方式(一)

基本操作方式(一)-- 外部操作

切換成 postgres 使用者：
```
#su - postgres
```
資料庫查詢：
```
$psql -l
```

直接連結指定的資料庫：

(指定的格式： psql -h [主機IP或名稱] -p [port 號] [資料庫名稱] [使用者名稱])
$psql -h 192.168.5.104 -p 5432 student postgres
用戶 postgres 的密碼:
psql (9.2.15)
輸入 "help" 顯示說明。

student=#

另一種直接連結指定資料庫的方式：

$export PGDATABASE=student
$export PGHOST=192.168.5.104
$export PGPORT=5432
$export PGUSER=postgres
$psql
密碼:
psql (9.2.15)
輸入 "help" 顯示說明。

student=#

基本操作方式(二)-- Psql 介面操作

進入 psql 操作介面方式：

#su - postgres
$psql
postgres=#

(上列的 postgres 是指 postgres 資料庫！)

利用 help 可顯示說明：

postgres=# help
您正在使用 PostgreSQL 指令列介面 psql。
輸入: \copyright 顯示發行條款
       \h 顯示 SQL 指令的說明
       \? 顯示 psql 指令的說明
       \g 或者以分號 (;) 結尾以執行查詢
       \q 結束
postgres=#

切換資料庫：

postgres=# \c student
已經連線到資料庫 "student"，使用者 "postgres"。

student=#

資料表查詢：

student=# \d baselist
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

索引 Key 查詢：

student=# \d baselist_pkey
     索引 "public.baselist_pkey"
 欄位 |         型別          | 定義
------+-----------------------+------
 id   | character varying(12) | id
主鍵, btree, 給資料表 "public.baselist"

可配合萬用字元查詢：

student=# \d b*
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

     索引 "public.baselist_pkey"
 欄位 |         型別          | 定義
------+-----------------------+------
 id   | character varying(12) | id
主鍵, btree, 給資料表 "public.baselist"

student=#

開啟計時功能：

student=# \timing on
啟用計時功能.
student=# select * from baselist;
 id | name | phones | address
----+------+--------+---------
(0 筆資料列)

時間: 1.482 ms

列出所有可用的 schema：

student=# \dn
    schema 清單
  名稱  |  擁有者
--------+----------
 public | postgres
(1 筆資料列)

顯示可用的 tablespace ：

student=# \db
           表空間列表
    名稱    |  擁有者  | 所在地
------------+----------+--------
 pg_default | postgres |
 pg_global  | postgres |
(2 筆資料列)

顯示角色、使用者：

student=# \du
                        角色清單
 角色名稱 |               屬性                | 成員屬於
----------+-----------------------------------+----------
 postgres | 超級用戶, 建立角色, 建立 DB, 複製 | {}

student=# \dg
                        角色清單
 角色名稱 |               屬性                | 成員屬於
----------+-----------------------------------+----------
 postgres | 超級用戶, 建立角色, 建立 DB, 複製 | {}

表格存取權限情況：

student=# \dp baselist
                        存取權限
 架構模式 |   名稱   | 型別  | 存取權限 | 資料行存取權限
----------+----------+-------+----------+----------------
 public   | baselist | table |          |
(1 筆資料列)

離開 psql 操作介面：
```
postgres=# \q
```

PostgreSQL 新增、刪除資料表

基本語法

建立資料表基本 SQL 語法：

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name (
 [
  { column_name data_type [ COLLATE 定序 ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] 
   }
    [, ... ]
 ] )

[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    OF type_name [ (
  { column_name WITH OPTIONS [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]

[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

其中 column_constraint 是:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

並且 table_constraint 是:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH 運算子 [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ...
] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

並且 like_option 是:

{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }

UNIQUE, PRIMARY KEY, and EXCLUDE 條件約束的 index_parameters 是:

[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

EXCLUDE 約束條件的 exclude_element 是:

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

刪除資料表基本 SQL 語法：

DROP TABLE [ IF EXISTS ] 名稱 [, ...] [ CASCADE | RESTRICT ]

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

新增 test 資料表：

postgres=# CREATE TABLE test(
postgres(# id varchar(10),
postgres(# name varchar(100),
postgres(# score int
postgres(# );

(請注意 "=" 與 '(' 的不同)

新增完之後，可查詢資料表：
```
postgres=# \d

postgres=# \d test
```
刪除 test 資料表：
```
postgres=# \d test
```

新增 score 資料表：

student=# CREATE TABLE score(
student(#student_id varchar(11),
student(#subject_id varchar(11),
student(#score int CONSTRAINT score_check CHECK (score >=0 AND score <=100));

CREATE TABLE
student=#

參考文獻：

http://netkiller.sourceforge.net/postgresql/key.html

2016年7月3日星期日

PostgreSQL 建立、刪除資料庫

基本語法

建立資料庫基本 SQL 語法：

CREATE DATABASE name
   [  [WITH] [OWNER [=] user_name]
      [TEMPLATE [=] template]
      [ENCODING [=] encoding]
      [LC_COLATE [=] lc_collate]
      [TABLESPACE [=] tablespace]
      [CONNECTION LIMIT [=] connlimit]
   ]

刪除資料庫基本 SQL 語法：
```
DROP DATABASE [ IF EXISTS ] 名稱
```

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

新增 student 資料庫：

postgres=# CREATE DATABASE student
postgres-# TEMPLATE = postgres ;

(請注意 "=" 與 '-' 的不同)

新增完之後，可查詢資料庫：
```
postgres=# \l
```
刪除 student 資料庫：
```
DROP DATABASE student;
```

參考文獻：

https://www.howtoforge.com/tutorial/virtual-hosting-with-vsftpd-and-mysql-on-ubuntu-15.10/

PostgreSQL 9.2 相關設定檔

基本設定檔

/var/lib/pgsql/data/pg_hba.conf --> 客戶端(帳密)驗證檔
查看內容：

#vim /var/lib/pgsql/data/pg_hba.conf
(設定格式：)
[連線類型] [資料庫名稱] [使用者名稱] [連線位置] [驗證方式] [其他選項]
(範例：)
host     all      all        127.0.0.1/32       md5
host     all      all        ::1/128            md5

/var/lib/pgsql/data/postgresql.conf --> PostgreSQL Server 運作組態檔
查看內容：

#vim /var/lib/pgsql/data/postgresql.conf
(參數說明：)
listen_addresses = '*'   ##監聴本機上的 IP 位置
port = 5432              ##監聴本機上的 port 號
max_connections = 2000   ##最大連線數設定值
shared_buffers = 256MB
work_mem = 32MB
maintenance_work_mem = 128MB
max_stack_depth = 6MB

※基本安裝：

請參考這一篇文件，進行基本安裝。
請參考這一篇文件，進行 phpPgAdmin 安裝。
請參考這一篇文件，安裝 pgAdmin III。

若有啟動錯誤，必須修正下列項目：

#vim /etc/sysctl.conf
kernel.sem = 250 32000 100 512

(存檔後，執行下列項目：)
#sysctl -p

參考文獻：

https://www.howtoforge.com/tutorial/virtual-hosting-with-vsftpd-and-mysql-on-ubuntu-15.10/

2016年6月22日星期三

SQL 開站說明

開站宗旨：

為了要記住 SQL 語言與相關的操作方式，特開此站！欽此！

訂閱：文章 (Atom)

2016年7月22日 星期五

2016年7月18日 星期一

2016年7月13日 星期三

2016年7月11日 星期一

2016年7月7日 星期四

2016年7月5日 星期二

2016年7月4日 星期一

2016年7月3日 星期日

2016年6月22日 星期三

2016年7月22日星期五

2016年7月18日星期一

2016年7月13日星期三

2016年7月11日星期一

2016年7月7日星期四

2016年7月5日星期二

2016年7月4日星期一

2016年7月3日星期日

2016年6月22日星期三