# **【Part1 - Mysql 是什麼？Mysql 優勢、指令】**
:::info
* 什麼是資料庫管理系統(DataBase Management System, DBMS)? 資料庫(DataBase)?
* Mysql 基本觀念: ETL、ACID、CRUD、3V、Data Pipeline
* Mysql 基礎操作
- 資料庫 (CREATE、READ、DELETE)
- 資料表 (CREATE、READ、DELETE、DROP、TRUNCATE)
- 增加條件 (CONSTRAINT)
- PRIMARY KEY、FOREIGNER KEY...REFERENCES <table2_name>(table2_field)、UNIQUE
- 更改表名稱、欄位 : ALTER、ADD、REMANE
- 更改表內容 : UPDATE
- 更改表內容 : REPLACE
* [W3School SQL Tutorial](https://www.w3schools.com/sql/)
:::
## :+1: 什麼是資料庫管理系統(DataBase Management System, DBMS)? 資料庫(DataBase)?
一個資料庫系統中，可以有多個資料庫；一個資料庫中，可以有很多資料表
DBMS => databases => tables
> Mysql 主要三大資料類型: [參考](https://dev.mysql.com/doc/refman/8.0/en/integer-types.html)
> 文字、數字、日期/時間 (後面會詳細提到)
>
> 文字: **VARCHAR(225)**，會按照資料長度調整、**CHAR(225)**，固定長度
>
> 數字: **TINYINT** 微小整數，-128到127的整數、**INT 整數**、**SMALLINT** 微小整數，-32768到32767的整數、**MEDIUMINT** 中等整數，-8388608到8388607的整數、**FLOAT(10, 2)**，浮點數，儲存10進位位數，其中2位是小數、**DOUBLE(15, 4)**，雙精度浮點數，儲存15進位位數，其中4位是小數，近似值、**DECIMAL(8, 3)**，固定小數點數，存儲8位數，其中3位是小數，精確值
>
>
> 日期/時間: **DATETIME**，預設2023-09-15 14:30:00、**DATE**，2023-09-15、**TIME**，14:30:00、**YEAR**，預設為YEAR(4)，2023
<br/>
## :+1: Mysql 基本觀念: ACID、CRUD、3V [參考](https://tw.alphacamp.co/blog/mysql-intro-acid-in-databases)
### ETL (Extract, Transform, Load) 特性
- Extract (提取) : 數據庫、文件、API、爬蟲、日誌文件、公開數據...提取所需資料。資料提取可以是批次、實時更新或增量更新
- Transform (轉換) : 清理、結構化、重塑、提取轉換成目標格式
- Load (加載) : 將轉換好的資料，加載到目標數據儲存
實時更新 Apache Kafka模組 範本，假設已經有一個主題叫 orders
```=
from kafka import KafkaConsumer
# 初始化 Kafka 消費者
consumer = KafkaConsumer('orders', bootstrap_servers='kafka_server＠＠＠')
# 初始化用戶訂單總金額的字典
user_order_totals = {}
for message in consumer:
order_data = message.value.decode('utf-8').split(',')
user_id, order_amount = int(order_data[0]), float(order_data[1])
# 更新用戶訂單總金額
if user_id in user_order_totals:
user_order_totals[user_id] += order_amount
else:
user_order_totals[user_id] = order_amount
consumer.close()
```
新增更新範本
```=
import pymysql
db = pymysql.connect(
host="your_host",
user="your_user",
password="your_password",
database="your_database"
)
cursor = db.cursor()
# 新增更新：每次插入新活動記錄時更新用戶的活動次數
user_id = 123
activity_type = 'login'
query = """
INSERT INTO user_activity (user_id, activity_type)
VALUES (%s, %s);
"""
cursor.execute(query, (user_id, activity_type))
db.commit()
cursor.close()
db.close()
```
### ACID (Atomicity, Consistency, Isolation, Durability) 特性
- Atomicity (原子性) : 資料操作不可分割。一次 transaction 只能成功或失敗
- Consistency (一致性)：transaction 完成前後，資料都必須永遠符合 schema 的規則，如果錯誤，可通過調整 schema 來維護
- Isolation (隔離性)：資料庫允許多個 transactions 同時對資料交叉執行，但執行不應互相干擾、不會導致數據的不一致
- Durability (持久性)：transaction 送出成功後，對資料的操作就是永久的，不可回朔上一步驟，即使系統發生故障也應該保持
>PS Nosql like mongodb
>- Atomicity (原子性) : 資料操作不可分割。一次 transaction 只能成功或失敗
>- Consistency (一致性)：最靈活的部分，資料不需要保持一致性，取決於具體的系統需求
>- Isolation (隔離性)：Nosql比較注重效能和擴展性，可能導致一些特定情況下的數據不一致
在一些 NoSQL 數據庫中，如分佈式數據庫，通常對隔離性的要求會比傳統的關聯式數據庫要求低，是為了保證更高的效能和擴展性，例如高度分佈的環境中，多個用戶或訪問數據庫時，可能導致一些特定情況下的數據不一致
>在一些 NoSQL 數據庫中，如分佈式數據庫，通常對隔離性的要求會比傳統的關聯式數據庫要求低，是為了保證更高的效能和擴展性，例如高度分佈的環境中，多個用戶或訪問數據庫時，可能導致一些特定情況下的數據不一致
>- Durability (持久性)：transaction 送出成功後，對資料的操作就是永久的，不可回朔上一步驟，即使系統發生故障也應該保持
### CRUD (Create, Read, Update, Delete) 特性
- Create (創建) : 創建資料庫或資料表
- Read (讀取) : 讀取資料庫或資料表
- Update (更新) : 更新資料庫或資料表
- Delete (刪除) : 刪除資料庫或資料表
### 3V (Volume, Velocity, Variety) 特性
這其實是大數據的概念
- Volume (容量)：數據量大
- Velocity (速度)：數據生成、收集和傳輸的速度快
- Variety (多樣性)：數據的類型多
<br/>
### Data Pipeline
**資料管道**，是指將資料從一個地方傳輸到另一個地方的一系列處理步驟和方法，可以包括多個步驟，例如資料清洗、資料轉換、資料驗證、資料載入等。常用的工具和框架: Apache Nifi、Apache Airflow、Talend、Apache Kafka、AWS Glue
<br/>
## :+1: Mysql 基礎操作? 指令都<span style="color: blue;">大寫</span>，表名、內容用<span style="color: blue;">小寫</span>，比較好分辨
:::warning
不要用WORKBENCH視覺化介面，打指令! Coding時想不出來很麻煩~
:::
:::info
關閉自動更新 **SET SQL_SAFE_UPDATES = 0;**
預設是1，執行 UPDATE 或 DELETE 語句但沒有使用 WHERE 子句、沒有 LIMIT 子句時，MySQL 會拒絕執行並報錯
:::
### ☑️ 資料庫 (CREATE、READ、DELETE)
顯示所有資料庫
```=
SHOW DATABASES;
```
創建資料庫
```=
CREATE DATABASE <database_name>;
```
使用某資料庫
```=
USE <database_name>;
```
刪除某資料庫
```=
DROP DATABASE <database_name>;
```
<br/>
### ☑️ 資料表 (CREATE、READ、DELETE、DROP、TRUNCATE)
顯示所有資料庫中的資料表
```=
SHOW TABLES;
```
展示某資料表，* 指全部資料
```=
SELECT * FROM <table_name>;
```
展示某資料表，特定欄位
```=
SELECT name, age FROM <table_name>;
```
PS 一定要先USE <database_name>，才能SHOW TABLES、SELECT * FROM <table_name>
<br/>
展示某資料表，特定欄位要符合某條件
```=
SELECT name FROM unique_cats WHERE name='Annie';
SELECT name, age FROM unique_cats WHERE name='Annie' AND age=10;
```
創建資料表，NOT NULL 代表是必須的，DEFAULT 如果沒輸入資料就'(空白)'
```=
CREATE TABLE cats (
id INT AUTO_INCREMENT PRIMARY KEY, -- 自動遞增主鍵
name VARCHAR(50) NOT NULL,
age INT DEFAULT ''
);
```
查看資料表Schema
```=
DESC <table_name>;
= DESCRIBE <table_name>;
= SHOW COLUMS FROM <table_name>;
```
刪除資料表
```=
DROP TABLE <table_name>;
```
只刪除資料表內容
```=
DELETE FROM <table_name>;
DELETE FROM <table_name> WHERE condition;
```
> TRUNCATE 整個清乾淨，不會產生交易日誌，只保留schema，通常更快
```=
TRUNCATE TABLE employee
```
PS 假設刪除的資料中有ID AUTO_INCREMENT
DELETE FROM -> INSERT INTO，ID 會從 11 開始
TRUNCATE TABLE -> INSERT INTO，ID 會從 1 開始
<br/>
輸入表格內容，'"Blue Steele"'，表格會顯示"Blue Steele"
```=
INSERT INTO cats (name, age)
VALUES ('"Blue Steele"', 5);
```
<br/>
### ☑️ 增加條件 (CONSTRAINT)
增加新條件，給新條件取名 + 確認條件內容
```=
ALTER TABLE houses
ADD CONSTRAINT positive_pprice
CHECK (purchase_price >= 0);
```
刪除條件
```=
ALTER TABLE houses
DROP CONSTRAINT positive_pprice;
```
<br/>
### ☑️ PRIMARY KEY、FOREIGNER KEY...REFERENCES <table2_name>(table2_field)、UNIQUE
設置PRIMARY KEY，代表值為必須，也是唯一
```=
CREATE TABLE unique_cats (
cat_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT NOT NULL
);
=
CREATE TABLE unique_cats (
cat_id INT AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
age INT NOT NULL,
PRIMARY KEY (cat_id)
);
```
PS UNIQUE 唯一值，輸入第二次相同的會失敗
<br/>
設置PRIMARY KEY + FOREIGNER KEY
```=
CREATE TABLE photos (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
image_ur1 VARCHAR(255) NOT NULL,
user_id INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id) -- 定義外來鍵，連結到 users 表的 id 欄位
);
```
```=
使用了複合主鍵來確保唯一性
CREATE TABLE likes (
user_id INTEGER NOT NULL PRIMARY KEY,
photo_id INTEGER NOT NULL PRIMARY KEY,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (photo_id) REFERENCES photos(id)
);
= CREATE TABLE likes (
user_id INTEGER NOT NULL,
photo_id INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id),
FOREIGN KEY(photo_id) REFERENCES photos(id),
PRIMARY KEY(user_id, photo_id)
);
```
<br/>
### ☑️ 更改表名稱、欄位 : ALTER、ADD、REMANE
更改表格_增加新欄位
```=
ALTER TABLE <table_name> ADD COLUMN <new_col> + 條件;
```
更改表格_刪除欄位
```=
ALTER TABLE <table_name> DROP COLUMN <new_col> + 條件 SCHEMA + (位置 : AFTER somehow column、FIRST 會加在第一欄、默認最後一欄);
```
更改表格_更改欄位SCHEMA
```=
ALTER TABLE <table_name> MODIFY <col_name>
ALTER TABLE `mavenbearbuilders`.`products` MODIFY `created_at` DATETIME NOT NULL;
# 同上
ALTER TABLE `mavenbearbuilders`.`products`
CHANGE COLUMN `created_at` `created_at` DATETIME NOT NULL;
```
更改表格_更改欄位名
```=
ALTER TABLE <table_name> RENAME COLUMN <col_name> TO <new_col_name>
```
更改表格名
```=
ALTER TABLE <table_name> RENAME TO <new_table_name>
# 同上
RENAME TABLE <table_name> TO <new_table_name>
```
更改表格名+更改欄位名
```=
ALTER TABLE <table_name> CHANGE <col_name> <new_col_name> + 條件;
```
串連現有表
PS. ON DELETE CASCADE，如果父表中的一行記錄被刪除了，子表中所有與被刪除的父記錄相關聯的行，會自動被刪除
```=
CREATE TABLE order_items (
order_id INT NOT NULL, -- 外來鍵，參考 orders 表
product_id INT NOT NULL, -- 外來鍵，參考 products 表
quantity INT NOT NULL, -- 購買數量
price DECIMAL(10, 2) NOT NULL, -- 當時的商品價格
PRIMARY KEY (order_id, product_id), -- 複合主鍵
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);
```
```=
ALTER TABLE `mavenbearbuilders`.`order_items`
ADD CONSTRAINT `order_items_product_id_fk` -- 新增約束命名
FOREIGN KEY (`product_id`)
REFERENCES `mavenbearbuilders`.`products` (`product_id`)
ON DELETE CASCADE
ON UPDATE CASCADE;
```
增加 UNIQUE
```=
ALTER TABLE `mavenbearbuilders`.`products`
ADD UNIQUE INDEX `product_name_UNIQUE` (`product_name` ASC) VISIBLE;
```
<br/>
### ☑️ 更改表內容 : UPDATE
更改顯示的欄位名稱，原始 Schema不會變
```=
SELECT name AS cat_name, age AS cat_age FROM unique_cats;
```
> 永久更改
> ALTER TABLE unique_cats
CHANGE COLUMN name cat_name VARCHAR(100),
CHANGE COLUMN age cat_age INT;
更改資料內容，PS != 不等於
```=
UPDATE unique_cats SET name='Amy', age='5' WHERE name = 'Josh'
```
刪除資料某幾列
```=
DELETE FROM unique_cats WHERE name = 'Josh'
```
```
更新SOP
1. SELECT * FROM <table_name> WHERE <formula condition>; 確認更改對象
2. UPDATE <table_name> SET col1=' ', col2=' ' WHERE <formula condition>; 更改
3. SELECT * FROM <table_name>; 做最後確認
刪除SOP
1. SELECT * FROM <table_name> WHERE <formula condition>; 確認刪除對象
2. DELETE FROM <table_name> WHERE <formula condition>; 刪除
3. SELECT * FROM <table_name>; 做最後確認
```
### ☑️ 更改表內容 : REPLACE
如果表中已經存在一條記錄，會被刪除，新的數據再被插入
```=
SELECT REPLACE('Hello World', o, 00); (o換成00)
SELECT REPLACE('Hello World', ' ', ' and'); (空格換成and+空格)
```
Sign in with Wallet
Connect another wallet