[Linux] PostgreSQL 9.4 簡易筆記 - 建立資料庫、使用者、索引 @ Ubuntu 14.04

安裝:

$ sudo apt-get install postgresql-9.4

列出已存在的資料庫：

$ sudo -u postgres psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

建立資料庫：

$ sudo -u postgres createdb MyDB

刪除資料庫：

$ sudo -u postgres dropdb MyDB

建立使用者(-P: 建立密碼, -l: 有登入權限[預設], -L: 禁止登入, -s: 超級使用者, -d: 建立資料庫權限)：

$ sudo -u postgres createuser -P -s -d -l root
$ sudo -u postgres createuser -s -d -l user1
$ sudo -u postgres createuser -s -l user2
$ sudo -u postgres createuser -l user3
$ sudo -u postgres createuser -d user4
$ sudo -u postgres createuser -s user5

列出使用者列表：

$ sudo -u postgres psql -c "SELECT * FROM pg_user";
 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+----------+----------+-----------
 postgres |       10 | t           | t        | t         | t       | ******** |          |
 user1    |    ####1 | t           | t        | t         | f       | ******** |          |
 user2    |    ####2 | t           | t        | t         | f       | ******** |          |
 user3    |    ####3 | f           | f        | f         | f       | ******** |          |
 user4    |    ####4 | t           | f        | f         | f       | ******** |          |
 user5    |    ####5 | t           | t        | t         | f       | ******** |          |
 root     |    ####6 | t           | t        | t         | f       | ******** |          |
(7 rows)

刪除帳號：

$ sudo -u postgres dropuser user1
$ sudo -u postgres dropuser user2
$ sudo -u postgres dropuser user3
$ sudo -u postgres dropuser user4
$ sudo -u postgres dropuser user5

遠端登入資料庫（資料庫跟使用者必須先建立）：

$ sudo -u postgres createdb helloworld
$ sudo -u postgres createuser -P -l user1
$ sudo -u postgres createuser -P -L user2

$ psql -h localhost -U user1
Password for user user1:
psql: FATAL:  database "user1" does not exist

$ psql -h localhost -U user1 -d helloworld
Password for user user1:
psql (9.4.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

helloworld=>\q

$ psql -h localhost -U user2 -d helloworld
Password for user user2:
psql: FATAL:  role "user2" is not permitted to log in

以下皆在 helloworld 資料庫行動：

$ psql -h localhost -U user1 -d helloworld
Password for user user1:
psql (9.4.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

helloworld=>

建立資料表：

helloworld=> CREATE TABLE haha ( field1 INT, field2 VARCHAR(64) );
CREATE TABLE
helloworld=> CREATE TABLE hehe ( field1 TEXT );

列出資料表：

helloworld=> \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | haha | table | user1
 public | hehe | table | user1
(2 rows)

查詢資料表定義：

helloworld=> \d haha
            Table "public.haha"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 field1 | integer               |
 field2 | character varying(64) |

刪除資料表：

helloworld=> DDROP TABLE haha;
DROP TABLE

建立索引：

helloworld=> CREATE INDEX index_test ON hehe ( field1 );
helloworld=> \d hehe
    Table "public.hehe"
 Column | Type | Modifiers
--------+------+-----------
 field1 | text |
Indexes:
    "index_test" btree (field1)

刪除索引：

helloworld=> DROP INDEX index_test;
DROP INDEX
helloworld=> \d hehe
    Table "public.hehe"
 Column | Type | Modifiers
--------+------+-----------
 field1 | text |

建立 Partial Indexes:

helloworld=> CREATE INDEX index_substring ON hehe ( substring(field1, 0, 32) );
CREATE INDEX
helloworld=> CREATE INDEX index_condition ON hehe ( field1 )
helloworld->  WHERE substring(field1, 0, 1) = 'a' ;
CREATE INDEX
helloworld=> \d hehe
    Table "public.hehe"
 Column | Type | Modifiers
--------+------+-----------
 field1 | text |
Indexes:
    "index_condition" btree (field1) WHERE "substring"(field1, 0, 1) = 'a'::text
    "index_substring" btree ("substring"(field1, 0, 32))

從 STDIN 輸入資料至 helloworld.hehe 中：

$ bash --version
GNU bash, version 4.3.30(1)-release (x86_64-pc-linux-gnu)
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>

This is free software; you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

$ cat <<EOF | psql -h localhost -U user1 -W -d helloworld
> COPY hehe (field1) FROM stdin;
> 1
> 2
> 3
> 4
> 5
> 6
> EOF
Password for user user1:
COPY 6

$ psql -h localhost -U user1 -W -d helloworld -c 'SELECT * FROM hehe;'
Password for user user1:
 field1
--------
 1
 2
 3
 4
 5
 6
(6 rows)

[Linux] 透過 sed 將 MySQL Table 資料轉成 csv 格式 @ Ubuntu 14.04

印象中之前找資料時，有發現 MySQL Server 可以輸出 CSV 格式到本地端。但是，對於無法存取到 DB Server 的檔案系統的環境時(AWS RDS)，只好手動處理一下 :P

用法：

$ mysql -h rds-server -u root -p -e "SELECT field1, field2 FROM db_name.table_name" | grep "^Keyword" | sed 's/\t/","/g' | sed 's/^/"/g' | sed 's/$/"/g' > db-table-export.csv

其中 grep 那段只是為了mysql 輸出時，略過第一行 XD

[SQLite] 跨 database (db file) 進行 Table JOIN

使用 SQLITE - Attach 這個指令即可：

$ ls
a.sqlite3 b.sqlite3
$ sqlite3
sqlite> attach 'a.sqlite3' as adb
sqlite> attach 'b.sqlite3' as bdb
sqlite> .tables
adb.user  bdb.data
sqlite> SELECT adb.user.id, adb.user.name, bdb.data.info FROM adb.user, bdb.data WHERE adb.user.id = bdb.data.id

[SQL] 透過 INNER JOIN 更新 Table 新增的欄位數值 @ MySQL 5.6

對於一些當作收集 log 用途的 table tb_log ，隨著時間增加後，通常會再整理另一個 tb_status 的 table，快速查詢各個狀態，設計上就會定期批次從 tb_log 取出資料，存進 tb_status 中。

假設 tb_log 有 5 個欄位，一開始只覺得需要 2 個欄位的資訊，就把 tb_status 設定為 2 個欄位，然而過一陣子後，想多記錄一個欄位時，只好變動 tb_status ，但新增的欄位沒有舊資料，就變成要從 tb_log 取出來再存進 tb_status 了

碰到這種問題，有一個解法就是使用 INNER JOIN 來處理：

1. 先從 tb_status 找出欄位未有值的資料
2. 從 tb_log 組出 tb_status 所需的資料
3. 透過  Update 指令更新

情況敘述：

mysql> describe tb_log;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| f0     | int(11)      | NO   | PRI | NULL    | auto_increment |
| f1     | varchar(8)   | YES  |     | NULL    |                |
| f2     | varchar(8)   | YES  |     | NULL    |                |
| f3     | varchar(8)   | YES  |     | NULL    |                |
| f4     | varchar(8)   | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

mysql> describe tb_status;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| f1     | varchar(8)   | YES  | PRI | NULL    |                |
| f2     | varchar(8)   | YES  |     | NULL    |                |
| f3     | varchar(8)   | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

其中 tb_status.f3 則是新建出來，未有資料的。

第一步：先找出 tb_status.f3 是空的（新進資料會有 f3 數值，只有舊資料沒有）

mysql> SELECT f1 WHERE f3 IS NULL;

第二步：從 tb_log 組出 f3 資料，由於 tb_log 是流水帳，且 tb_status 本身也可以從 tb_log 查詢出來的，只需組出 tb_status 需要的欄位即可：

mysql> SELECT f1, f3 FROM tb_log GROUP BY f1;

第三步，把上述兩個資料 JOIN 起來：

SELECT tb1.f1, tb2.f3 FROM
( SELECT f1 WHERE f3 IS NULL ) AS tb1, (SELECT f1, f3 FROM tb_log GROUP BY f1) AS tb2
WHERE tb1.f1 = tb2.f2;

最後，追加更新 tb_status 的用法：

UPDATE tb_status AS tb4
INNER JOIN

(
SELECT tb1.f1, tb2.f3 FROM
( SELECT f1 WHERE f3 IS NULL ) AS tb1,
(SELECT f1, f3 FROM tb_log GROUP BY f1) AS tb2
WHERE tb1.f1 = tb2.f2
) AS tb3

ON tb4.f1 = tb3.f1

SET

tb4.f3 = tb3.f3;