---
# System prepended metadata

title: PostgreSQL 學習筆記

---

# PostgreSQL 學習筆記

## 安裝與設定
:::warning
以macOS的homebrew示範
:::
養成習慣先更新一下安裝工具，再執行安裝作業
```zsh
brew update && brew install postgresql
```

檢查安裝版本
```zsh
postgres -V
```

透過homebrew啟動服務
```zsh
brew services start postgresql
```

關閉服務
```zsh
brew services stop postgresql
```

檢查服務清單
```zsh
brew service list
```

初次登入postgreSQL
```zsh
sudo -u postgres psql
```


## Ubuntu Install PostgreSQL
在基於 Debian Linux 環境下需安裝 libpq-dev 的依賴套件，此套件提供了 PostgreSQL 的開發檔案和庫，以便於編譯 Python 的 PostgreSQL 連接器。
```bash=
sudo apt-get update
sudo apt-get install libpq-dev
```


參考文章
[Ubuntu Install and configure PostgreSQL](https://ubuntu.com/server/docs/databases-postgresql)

## 權限設定
***TODO***

## 建立資料庫

```sql
CREATE DATABASE testdb_1
```

## 建立表

我需要建立一張表叫做users
並且定義三個欄位分別叫做id, username, password
依序列出各個欄位資料類型（整數、字串、二進制數據等等）以及限制（不得為空、唯一值、主鍵、自動增長等等）
以下是簡單的範例：
```sql=
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
username VARCHAR(100) NOT NULL UNIQUE,
password BYTEA NOT NULL
);
```
![image](https://hackmd.io/_uploads/HkEAisHrT.png)

> MySQL, PostgreSQL Schema差異
> 二進制資料類型 BLOB, BYTEA
> 自動增加 AUTO_INCREMENT, SERIAL
> 



## CRUD SQL Query
### 新增資料：

```sql=
INSERT INTO users (username, password)
VALUES ('user001', 'password');
```
> id欄位已設定自動增加，故不須特別輸入內容

表示一筆資料被新增
![image](https://hackmd.io/_uploads/ryOmaiSBp.png)

透過查詢語法可以確定是剛剛新增的一筆資料
![image](https://hackmd.io/_uploads/S1UrajBrp.png)


```sql=
INSERT INTO users (username, password)
VALUES ('user002', 'password'), ('user003','password');
```

當我需要同時新增多筆資料，可以在VALUES後面放入多組資料
![image](https://hackmd.io/_uploads/r1yk0sHrT.png)

一樣透過查詢語法來找出剛剛新增的兩筆資料
![image](https://hackmd.io/_uploads/HyxGCjrSp.png)



### 查詢資料：
``*``字號表示所有欄位，意指查詢users這張表中的所有欄位。
```sql!
SELECT * FROM users;
```

針對username 欄位查詢
```sql!
SELECT username FROM users;
```
![image](https://hackmd.io/_uploads/Hy_EyhHBp.png)


針對user002 這筆資料查詢
```sql!
SELECT * FROM users WHERE username = 'user002';
```
![image](https://hackmd.io/_uploads/HyZGx2HSa.png)

### 更改資料：
三個要注意的點是 
1. 要編輯哪個table
2. 要變更什麼值
3. 哪筆資料要變更
```sql=
UPDATE users
SET username = 'newuser_002'
WHERE username = 'user002';
```
表示一筆資料被變更
![image](https://hackmd.io/_uploads/Skd2e3SSa.png)


### 刪除資料：

```sql=
DELETE FROM users
WHERE username = 'newuser_002';
```
表示一筆資料被刪除
![image](https://hackmd.io/_uploads/rkXlzhSHp.png)


## 指令小抄
列出所有權限清單(List of roles)
```sql
\du
```
![image](https://hackmd.io/_uploads/HkswJaH2T.png)

列出所有database(List of databases)
```sql
\l
```
![image](https://hackmd.io/_uploads/S1sckTBhT.png)

切換至 [db_name] database
```sql
\c [db_name]
```
列出所有table
```sql
\dt
```
![image](https://hackmd.io/_uploads/HJcM16B3p.png)

列出 [table_name] Schema
```sql
\d [table_name]
```
![image](https://hackmd.io/_uploads/r1hZg6r26.png)

列出
```sql
\df
```

## BP Select 練習
```sql=
testdb=# select count (*) from aws_rowdata_item;
 count
-------
    66
(1 row)

testdb=# select rowdata, count (*) from aws_rowdata_item group by rowdata;
                       rowdata                        | count
------------------------------------------------------+-------
 npik5v4ki7kifz43t7pkqgkt7hfuhxxmg4m6rvjej5bowfpxt35a |    33
 q4rf7k5pgrrbbudrcjzpkdfhwpprovpgwklgdnw7xy5shpny25ha |    33
(2 rows)

testdb=# select column_start, count (*) from aws_rowdata_item group by column_start;
 column_start | count
--------------+-------
 identity     |    94
 product      |   487
 lineItem     |   707
 reservation  |    47
 bill         |   239
 pricing      |   308
 resourceTags |    27
(7 rows)

testdb=# select value from aws_rowdata_item where rowdata = 'q4rf7k5pgrrbbudrcjzpkdfhwpprovpgwklgdnw7xy5shpny25ha' and (column_end = 'BillType' or column_end = 'UsageType');            value
-----------------------------
 Anniversary
 APS3-SoftwareUsage:c5.large
 Anniversary
 APS3-SoftwareUsage:c5.large
(4 rows)
```

## 以 lineItem/UsageAccountId 查詢
```sql!
// 查詢 LineItem/UsageAccountId 的所有資料
select * from aws_rawdata_item where rawdata in ( select rawdata from aws_rawdata_item where column_start = 'lineItem' and column_end = 'UsageAccountId' and value = '193727796239');

// 查詢屬於 LineItem/UsageAccountId 的每個 rawdata 有幾段資料
select rawdata, count (*) from aws_rawdata_item where rawdata in (select rawdata from aws_rawdata_item where column_start = 'lineItem' and column_end = 'UsageAccountId' and value = '193727796239') group by rawdata;


```

# BP
login cmd
```bash!
psql -h localhost -p 5432 -U userbp -d bp
```
