昨天介紹資料庫的概念，今天會開始操作 Postgres 資料庫。GOGO!
以下示範均是直接對 postgres 的 terminal 進行操作哦！
(開啟 Postgres 並點選你要的 DB 就可以開啟)

1. 切換資料庫

psql -U 帳號 -h hostname -d database

2. 查看所有表格

\dt

3. 獲得某表的詳細內容

\d 表名

4. CREATE TABLE

• 說明

CREATE TABLE IF NOT EXISTS schema_name.table_name(
    column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
);

• 範例
  PRIMARY KEY 代表主鍵值，可以用來識別該表格的唯一值，就像身分證字號。通常利用這個欄位可以跟其他表做合併。

CREATE TABLE IF NOT EXISTS public.train(
    customer_id character varying(100) PRIMARY KEY,
    sex character varying(1) NOT NULL,
    height numeric(5,2) NOT NULL,
    weight numeric(5,2) NOT NULL
);

5. INSERT

• 說明

INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

• 範例

INSERT INTO train(customer_id, sex, height, weight)
VALUES ('A123456789', 'M', '180.45', '70.7');

若一次想塞入多筆資料也可以辦得到！

INSERT INTO train(customer_id, sex, height, weight)
VALUES ('A123456789', 'M', '180.45', '70.7'),
       ('X123456789', 'M', '175.12', '65.7'),
       ('W123456789', 'W', '160.67', '45.7'),
       ('H123456789', 'W', '167.93', '52.7');

6. UPDATE

更新資料

• 說明

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

• 範例

UPDATE train set sex='F' where customer_id='A123456789';

7. SELECT

篩選資料

• 說明

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
all expression(*) [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

• 範例

select * from train where sex='M';

8. DELETE

刪除資料

• 說明

DELETE FROM [ ONLY ] table [ WHERE condition ]

• 範例

DELETE FROM train where sex='M';

9. TRUNCATE

清空一個表

• 說明

TRUNCATE [ TABLE ] name

• 範例

TRUNCATE train;

10. DROP TABLE

刪除表

• 說明

DROP TABLE name [, ...] [ CASCADE | RESTRICT ]

• 範例

DROP TABLE train;

11. GRANT

給予其他人權限

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT ALL PRIVILEGES ON train to postgres;