# PostgreSQL
###### tags: `資料庫` `筆記` `程式` `觀念`
作為兩大主流開源數據庫，MySQL 和 Postgres
## What's postgreSQL?
- 物件關聯式資料庫管理系統（ORDBMS, Object-Relational Database Management System）
- 被廣泛應用於讀寫速度至關重要且資料需要驗證的大型系統中。
- 支持各種效能優化（如地理空間資料支持，不需要讀取鎖定的資料一致性支援，如 Oracle、SQL Server）。
## 與mysql的差別
1. 開源性：PostgreSQL 是完全開源的，而 MySQL 有一部分是開源的，另一部分是商業的。
2. 許可協議：MySQL 用的是 GPL 許可協議，而 PostgreSQL 用的是 PostgreSQL 許可協議。
3. 支持的數據類型：PostgreSQL 支持更多的數據類型，包括數組、JSON 和 XML。
4. 支持的特性：PostgreSQL 支持更多的特性，包括觸發器、存儲過程、視圖、外鍵約束和規則（rule）。
5. 性能：在性能方面，MySQL 和 PostgreSQL 的表現可能有所不同，但這取決於很多因素，包括硬件、操作系統、應用程序設計和使用方式。
6. 受歡迎程度：MySQL 比 PostgreSQL 更受歡迎，因為它更流行，而且被許多大型網站和應用程序使用。
## Pgadmin 4
- pgAdmin 是一个非常流行、功能強大並且開源的 PostgreSQL 管理與開發平台。
- pgAdmin 支持 Linux、Unix、Mac OS X 以及 Windows 操作系統，可以管理 PostgreSQL 9.2 以及更高版本。
## 可用參數
```htmlembedded=
#進入資料庫
psql
psql -h localhost -p 5432 -U [username] [DbName] #直接進入指定資料庫
# help
\? # 顯示和 psql IRB 有關的指令說明
\h # 顯示和 sql 有關的指令說明
# 資料庫與資料表資訊
\du # 列出所有 roles
\l # 列出所有 databases V
\z or \d # 列出所有 tables
\d [tablename] # 列出該資料表的所有欄位
# 連線資訊
\c [db_name] # 連線到另一個資料庫
\conninfo # 連線資訊
\password [username] # 修改某 username 的密碼
CREATE DATABASE [DbName] #創造資料庫V
\c [DbName] #進入資料庫
```
## 基本語法
```htmlembedded=
# 建立資料表 名稱為weather和cities
CREATE TABLE weather (
id BIGSERIAL NOT NULL PRIMARY KEY,
city varchar(80) NOT NULL,
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
CREATE TABLE cities (
name varchar(80),
location point
);
# 刪除資料表
DROP TABLE tablename;
# 新增資料
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
# 查詢資料
SELECT * FROM weather;
輸出結果：
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)
# 限定查詢ＷＨＥＲＥ
SELECT * FROM weather
WHERE city = 'San Francisco' AND prcp > 0.0;
# 排序 ORDER BY
SELECT * FROM weather
ORDER BY city;
SELECT * FROM weather
ORDER BY city DESC; #倒序排列
# 查詢時去除重複的列(可搭配order by)
SELECT DISTINCT city
FROM weather;
# GROUP BY 搭配計算次數 算每個地點個出現幾次
SELECT city, COUNT(*) FROM weather GROUP BY city;
# GROUP BY HAVING 印出出現超過２０次的地點
SELECT city, COUNT(*) FROM weather GROUP BY city HAVING COUNT(*)>20 ORDER BY city;
# 交叉查詢
SELECT *
FROM weather, cities
WHERE city = name;
輸出結果：
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)
# 限制條件 只出現五筆資料
SELECT * FROM cities LIMIT 5;
# 限制條件 從第五筆之後開始跑5筆
SELECT * FROM cities OFFSET 5 LIMIT 5;
# 重新命名
SELECT *
FROM weather w, cities c
WHERE w.city = c.name;
SELECT cities AS weather_city From weather;
# 更新資料 ＵＰＤＡＴＥ ＳＥＴ
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '1994-11-28';
# 刪除資料 ＤＥＬＥＴＥ
DELETE FROM weather WHERE city = 'Hayward';
DELETE FROM tablename (刪除資料表內所有資料 但primary不會歸0)
DELETE TABLE tablename (刪除整張表)
# 查詢極值 找溫度最高的地點
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
```
- BIGSERIAL 自動增加的函示
- group by 和distinct的區別
- 前者通常搭配函式一起使用
- 後者也可以去除一個以上的重複資料
- 常用查詢函式count、sum、avg（平均值）、max、min
## 查詢語法
```htmlembedded=
# 篩選包含ＩＮ裡面的值（類似OR）
SELECT * FROM cities WHERE name IN ('xxx', 'ooo')
# 篩選範圍Between
SELECT * FROM cities WHERE date BETWEEN DATE '2000-01-01' AND '2021-12-31';
# 模糊查詢 LIKE (包含xxx
)
SELECT * FROM cities WHERE name LIKE '%xxx%'
SELECT * FROM cities WHERE name NOT LIKE '%xxx%'
```
- LIKE ％ ＿
- %AB%：找包含AB
- %AB : 找AB結尾
- AB％：找AB開頭
- _AB% : 找AB在第二和第三位置
- _2%3 : 找第二位置為2，並以3結束的任何值
## 數學語法
```htmlembedded=
SELECT MAX(price) FROM car;
SELECT MIN(price) FROM car;
SELECT AVG(price) FROM car;
SELECT ROUND(AVG(price)) FROM car;
SELECT SUM(price) FROM car;
#所有不同車子各自的平均（利用group by篩掉重複的項目）
SELECT id, make, AVG(price) FROM car GROUP BY make;
#新增一個打九折後的欄位並取道小數後第二位
SELECT make, price, ROUND(price * .90, 2) FROM car GROUP BY make;
id | make | price | round(new)
------+---------------+----------+----------
1 | GMC | 73902.36 | 66512.12
2 | Toyota | 59205.65 | 53285.09
3 | Lexus | 22672.01 | 20404.81
4 | Lexus | 30032.13 | 27028.92
5 | Mitsubishi | 14646.62 | 13181.96
```
## Timestamp and Date
```htmlembedded=
SELECT NOW();
now
------------------------------
2022-12-30 20:33:26.67321+08
(1 row)
SELECT NOW()::DATE;
// 2022-12-30
SELECT NOW()::TIME;
// 20:33:59.288151
#比現在多十天 （可用來算年紀）
SELECT NOW() + INTERVAL '10 DAYS';
#只取特定數字（可用YEAR or MONTH or DAY or DOW-星期）
SELECT EXTRACT(YEAR FROM NOW());
```
## 修改表格ALTER TABLE
```htmlembedded=
# 新增/刪除欄位
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
# 變更資料型態
ALTER TABLE table_name ALTER COLUMN column_name datatype;
```
## Foreign Key
```htmlembedded=
#資料表設定
car_id BIGINT REFERENCES car (id)
#JOIN
SELECT * FROM person JOIN car ON person.car_id = car.id;
SELECT * FROM person LEFT JOIN car ON person.car_id = car.id;
// 沒有外鍵的也會顯示
```
## 下載檔案
```htmlembedded=
# 將表格下載到電腦
\copy (SELECT * FROM person LEFT JOIN car ON person.car_id = car.id) TO '/Users/meme/Desktop/result.csv' DELIMITER ',' CSV HEADER;
```
## 進階語法
```htmlembedded=
#檢視表ＶＩＥＷ
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
#重複更新或新增資料
INERT INTO city (id, name) VALUE (1, 'Taipei') ON CONFLIC (id) DO NOTHING;
// INSERT 0 0
```
- 檢視表：某兩張表的內容是常用的，可以新建立檢視表，不用每次都輸入一長串的查詢語句。
其他：
[Inner Join](https://pjchender.dev/database/psql-joins/)
[PostgreSql](https://docs.postgresql.tw/tutorial/the-sql-language/creating-a-new-table)
## 匯入匯出檔案
```htmlembedded=
\i 檔案路徑
ex. \i /Users/meme/Downloads/person.sql
```
## 匯入匯出資料
```htmlembedded=
COPY School(code, city, district, name)
FROM '/Users/meme/Desktop/testtt.csv'
DELIMITER ','
CSV HEADER;
#COPY 4 回傳匯入資料數量
```
Sign in with Wallet
Connect another wallet