[Part 1] MySQL 教學入門：資料庫概念、ACID 原則與 SQL 指令完整解析

Contents 目錄

什麼是資料庫管理系統(DataBase Management System, DBMS)? 資料庫(DataBase)?

一個資料庫系統中，可以有多個資料庫；一個資料庫中，可以有很多資料表
DBMS => databases => tables

Mysql 主要三大資料類型: 參考
文字、數字、日期/時間 (後面會詳細提到)

文字: 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

Mysql 基本觀念: ACID、CRUD、3V 參考

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 (多樣性)：數據的類型多

Data Pipeline

資料管道，是指將資料從一個地方傳輸到另一個地方的一系列處理步驟和方法，可以包括多個步驟，例如資料清洗、資料轉換、資料驗證、資料載入等。常用的工具和框架: Apache Nifi、Apache Airflow、Talend、Apache Kafka、AWS Glue

Mysql 基礎操作

之前上國外線上課，發現他們指令都大寫，表名、內容用小寫，比較好分辨，我就照這個習慣了

關閉自動更新 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>;

☑️ 資料表 (CREATE、READ、DELETE、DROP、TRUNCATE)

顯示所有資料庫中的資料表

SHOW TABLES;

展示某資料表，* 指全部資料

SELECT * FROM <table_name>;

展示某資料表，特定欄位

SELECT name, age FROM <table_name>;

PS 一定要先USE ，才能SHOW TABLES、SELECT * FROM

展示某資料表，特定欄位要符合某條件

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 開始

輸入表格內容，'”Blue Steele”‘，表格會顯示”Blue Steele”

INSERT INTO cats (name, age)
VALUES ('"Blue Steele"', 5);

☑️ 增加條件 (CONSTRAINT)

增加新條件，給新條件取名 + 確認條件內容

ALTER TABLE houses
ADD CONSTRAINT positive_pprice 
CHECK (purchase_price >= 0);

刪除條件

ALTER TABLE houses 
DROP CONSTRAINT positive_pprice;

☑️ PRIMARY KEY、FOREIGNER KEY…REFERENCES (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 唯一值，輸入第二次相同的會失敗

設置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)
);

☑️ 更改表名稱、欄位 : 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;

☑️ 更改表內容 : 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+空格)

•【Part 1】MySQL 教學入門：資料庫概念、ACID 原則與 SQL 指令完整解析

什麼是資料庫管理系統(DataBase Management System, DBMS)? 資料庫(DataBase)?

Mysql 基本觀念: ACID、CRUD、3V 參考

ETL (Extract, Transform, Load) 特性

ACID (Atomicity, Consistency, Isolation, Durability) 特性

CRUD (Create, Read, Update, Delete) 特性

3V (Volume, Velocity, Variety) 特性

Data Pipeline

Mysql 基礎操作

☑️ 資料庫 (CREATE、READ、DELETE)

☑️ 資料表 (CREATE、READ、DELETE、DROP、TRUNCATE)

☑️ 增加條件 (CONSTRAINT)

☑️ PRIMARY KEY、FOREIGNER KEY…REFERENCES (table2_field)、UNIQUE

☑️ 更改表名稱、欄位 : ALTER、ADD、REMANE

☑️ 更改表內容 : UPDATE

☑️ 更改表內容 : REPLACE

Catalina

發佈留言取消回覆

什麼是資料庫管理系統(DataBase Management System, DBMS)? 資料庫(DataBase)?

Mysql 基本觀念: ACID、CRUD、3V 參考

ETL (Extract, Transform, Load) 特性

ACID (Atomicity, Consistency, Isolation, Durability) 特性

CRUD (Create, Read, Update, Delete) 特性

3V (Volume, Velocity, Variety) 特性

Data Pipeline

Mysql 基礎操作

☑️ 資料庫 (CREATE、READ、DELETE)

☑️ 資料表 (CREATE、READ、DELETE、DROP、TRUNCATE)

☑️ 增加條件 (CONSTRAINT)

☑️ PRIMARY KEY、FOREIGNER KEY…REFERENCES (table2_field)、UNIQUE

☑️ 更改表名稱、欄位 : ALTER、ADD、REMANE

☑️ 更改表內容 : UPDATE

☑️ 更改表內容 : REPLACE

Catalina

相關文章

• Spark 實戰：大數據效能調校、資料傾斜處理與 Iceberg 資料湖整合

• Airflow 3 實戰：Docker + PostgreSQL / SQLite / MongoDB Atlas 寫入與 Kafka 事件流處理

• Apache Airflow 工作流

•【Part 5】MySQL JOIN 教學：INNER、LEFT、RIGHT、FULL、CROSS JOIN 完整範例與VIEW虛擬表解析

發佈留言取消回覆

目前趨勢