---
tags: 前端
---
# 資料庫筆記

SQL操作備忘錄
SELECT * FROM Member WHERE account = {account};
UPDATE Member SET password = {password} WHERE id = {id};
REPLACE INTO Member (id, password) VALUES ({id},{password});
DELETE FROM Member WHERE id = {id};
INSERT INTO Member(...) VALUES (...);
CREATE TABLE IF NOT EXISTS Member (...);
[INTEGER|REAL|TEXT|BLOB] [NOT NULL|PRIMARY KEY|AUTOINCREMENT|UNIQUE|DEFAULT] [CURRENT_TIMESTAMP]
FOREIGN KEY (...) REFERENCES [TABLE] (...) ON DELETE CASCADE ON UPDATE NO ACTION
CREATE TRIGGER [TRIGGER] [BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE] ON [TABLE] WHEN [condition]
BEGIN TRANSACTION; END; COMMIT;
CREATE UNIQUE INDEX id ON Member (...);
ALTER TABLE [TABLE] RENAME [COLUMN?] TO [TABLE|COLUMN];
DROP TABLE|TRIGGER [TABLE|TRIGGER];

### 資料庫圖形化介面
* [HeidiSQL](https://www.heidisql.com/)
* [DBeaver](https://dbeaver.io/?ref=eversql.com)
* [MySQL Workbench](https://www.mysql.com/products/workbench/)
### [MySQL](https://dev.mysql.com/downloads/mysql/)指令
|動作|指令|
|-|-|
|秀出所有的資料庫|show databases;|
|建立新的資料庫|CREATE [db];|
|刪除資料庫|DROP DATABASE [db];|
|進入資料庫|USE [db];|
|秀出所有已經建立的資料表|SHOW TABLES;|
|查詢某個資料表的資料格式|DESCRIBE [table];|
|建立新資料表|CREATE TABLE [table] (sid int);|

### [PostgreSQL](https://www.enterprisedb.com/downloads/postgres-postgresql-downloads)指令
|動作|指令|
|-|-|
|新增資料庫|CREATE DATABASE mytestdb;|
|進入資料庫|\c mytestdb|
|列出所有使用者|\du|
|列出所有DB|\l|
|列出所有table|\dt|

### 伺服器端架postgresql(ubuntu)
| 動作 | 指令 |
| -------- | -------- |
|安裝postgresql|sudo apt install postgresql postgresql-contrib|
|設定postgres密碼|sudo passwd postgres|
|進入postgres命令列|su - postgres|
|進入DB操作|psql|
|更改使用者密碼|ALTER USER postgres WITH PASSWORD '自訂新密碼';|
|離開DB操作|\q|
|離開postgres命令列|exit|
|設定開機自動執行|update-rc.d postgresql enable|
|啟動資料庫伺服|service postgresql start|
|開啟防火牆資料庫對外連接阜|ufw allow 5432/tcp|
|開啟防火牆|ufw enable|
|編輯(版本9.5)|nano /etc/postgresql/9.5/main/pg_hba.conf|
|在最後一行加入|host all all 0.0.0.0/0 trust|
|編輯(版本9.5)|nano /etc/postgresql/9.5/main/postgresql.conf|
|找到註解並修改|listen_address = '\*' |
|重啟伺服|service postgresql restart|

### 管理SCHEMA

PRIMARY KEY  主鍵
NOT NULL     不允許有空值
CHECK        限制特定條件
UNIQUE       不允許重複

CREATE TABLE [mytable] (
    sid INT PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    date DATETIME CHECK(date>'2017-01-01'),
    addr TEXT UNIQUE
);

外來鍵(別人沒有的我不能有)
CREATE TABLE [mytable2] (
    id INT,
    sid INT,
    my_primary_key(id),
    my_foreign_key(sid) REFERENCE [mytable] (sid)
);

|刪除資料表|DROP TABLE [table];|
|-|-|
|更動資料表(刪除某欄)|ALTER TABLE [table] DROP [old_column];|
|更動資料表(新增某欄)|ALTER TABLE [table] ADD [new_column] CHAR(10) AUTO_INCREMENT PRIMARY KEY;|
|更動資料表(更改欄位名稱)|ALTER TABLE [table] CHANGE [old_name] [new_name];
|更動某欄資料格式|ALTER TABLE [table] CHANGE [column] CHAR(20);|
|重新命名資料表|RENAME [old_table_name] TO [new_table_name];|

### CRUD(create, read, update, delete)
|新增資料|INSERT INTO [table] (sid,date,name) VALUE (11,'2017-01-01','[value]');|
|-|-|
|跳過前2筆後的10筆|SELECT * FROM [table] LIMIT 10 OFFSET 2;|
|選取多筆特定的欄位|SELECT [column1], [column2] FROM [table];|
|更新資料|UPDATE [table] SET [column]='[value]' WHERE [column] = '[value]';|
|刪除資料|DELETE FROM [table] WHERE [column] > [value];|
|清空資料表中所有資料|TRUNCATE TABLE [table];|

### 特定函數
|平均|出現次數|最大|最小|總和|字數|去除左右邊的空白|唯一值|
|-|-|-|-|-|-|-|-|
|AVG|COUNT|MAX|MIN|SUM|LENGTH|TRIM|DISTINCT|

|相等|不等於|與|或|任意個任意值|一個任意值|
|-|-|-|-|-|-|-|
|=|<>|AND|OR|%|_ |NOT|IN|BETWEEN|

(COUNT 不包含空值)
SELECT COUNT([column]) FROM [table];
(相等是用=而非==雙等號)
SELECT * FROM [table] WHERE [column] = [value];
SELECT * FROM [table] WHERE [column] LIKE '%[text]%';
SELECT * FROM [table] WHERE [column] NOT IN ('[value1]','[value2]','[value3]');
SELECT * FROM [table] WHERE [column] BETWEEN '2017-01-01' AND '2017-12-31';

### 條件篩選
|ORDER BY 重新排序，ASC升冪由小到大(預設)、DESC降冪由大到小|SELECT * FROM [table] ORDER BY [column] DESC;|
|-|-|
|群組化運算|SELECT * FROM [table] GROUP BY [column];|
|群組化後篩選|SELECT * FROM [table] GROUP BY [column] HAVING AVG([column]) > 666;|
|AS 別名，當覺得指令或名稱很長時可使用|SELECT COUNT([abbr].[column]) AS [abbr] FROM [table] As [abbr] GROUP BY [abbr].[column];|
|CASE WHEN THEN END 條件下更換資料|SELECT [column] CASE [any_column] WHEN '[specified_value]' THEN [any_column] * 2 END FROM [mytable];|
|VIEW虛擬資料表，想要測試SQL但不想要動到資料庫時可使用|CREATE VIEW [view_table] AS SELECT * FROM [table];<br>SELECT * FROM [view_table];|

### 資料表合併
|UNION聯集且不包含重複的值 |SELECT [column] FROM [table1] UNION SELECT [column] FROM [table2];|
|-|-|
|UNOIN ALL聯集且包含重複的值|SELECT [column] FROM [table1] WHERE EXISTS (SELECT [column] FROM [table2]);|
|INTERSECT交集|SELECT [column] FROM [table1] INTERSECT SELECT [column] FROM [table2];|
|MINUS減集(前者有後者沒有的)|SELECT [column] FROM [table1] MINUS SELECT [column] FROM [table2];|
|LEFT JOIN|SELECT [columns] FROM [table1] LEFT JOIN [table2] ON [columns]|
|CROSS JOIN排列組合||

### 檔案I/O
|執行SQL檔|source C:/[directory]/[.sql]|
|-|-|
|匯入CSV檔(MySQL)|LOAD DATA INFILE '[.csv]' INTO TABLE [table] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;|
|匯入CSV檔(PGSQL)|COPY DB.表格(欄位) FROM '文字檔的路徑' WITH CSV HEADER DELIMITER AS ',';|
|輸出csv檔|SELECT * FROM [table] INTO OUTFILE '[.csv]' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';|
# 資料表合併
#### 居住城市資料表
|索引 |名字 |城市|
|---|---|---|
|0 |我 |新竹|
|1 |你 |台中|
|2 |他 |台北|
|3 |她 |高雄|
#### 學校IP資料表
|IP |學校 |城市|
|---|---|---|
|113|交大 |新竹|
|114|清大 |新竹|
|112|台大 |台北|
|116|成大 |台南|
|120|中興 |台中|

#### Inner join(natural join)
|索引|名字|城市|IP|學校|
|---|---|---|---|---|
|0|我|新竹|113|交大|
|0|我|新竹|114|清大|
|1|你|台中|120|中興|
|2|他|台北|112|台大|

#### Left join(left outer join)
|索引|名字|城市|IP|學校|
|---|---|---|---|---|
|0|我|新竹|113|交大|
|0|我|新竹|114|清大|
|1|你|台中|120|中興|
|2|他|台北|112|台大|
|3|她|高雄| | |

#### Right join(right outer join)
|索引|名字|城市|IP|學校|
|---|---|---|---|---|
|0|我|新竹|113|交大|
|0|我|新竹|114|清大|
|1|你|台中|120|中興|
|2|他|台北|112|台大|
| | |台南|116|成大|

#### Full join(outer join)
|索引|名字|城市|IP|學校|
|---|---|---|---|---|
|0|我|新竹|113|交大|
|0|我|新竹|114|清大|
|1|你|台中|120|中興|
|2|他|台北|112|台大|
|3|她|高雄| | |
| | |台南|116|成大|

#### Cross join(共20欄)
|索引|名字|城市|IP|學校|
|---|---|---|---|---|
|0|我|新竹|113|交大|
|0|我|新竹|114|清大|
|0|我|台中|120|中興|
|0|我|台北|112|台大|
|0|我|台南|116|成大|
|1|你|新竹|113|交大|
|1|你|新竹|114|清大|
|1|你|台中|120|中興|
|1|你|台北|112|台大|
|1|你|台南|116|成大|
|…|…|…|…|…|