close

今天上SQL語法，感覺有些焦慮，想到明年四月中的報告期限，在最後的兩個禮拜可能沒辦法完全投入開發，還需要準備報告，就讓我感到時間壓力很大。我感到焦慮的原因是想嘗試的東西太多，但卻沒有辦法熟練任何工具、套件或程式語言。對我來說，能完成一個看起來完整的作品已經很難得了，但如果面試需要考一些觀念或深入的操作，我可能完全答不出來，這也是我很擔心的部分。

此外，我又感到有些迷茫，今天開始重新思考要參考 roadmap，重新規劃學習的目標和難度曲線。我上課的內容其實不是聽不懂，但當新的語法或內容加入時，會讓我感到無所適從。如果沒有實際操作，我會覺得自己在逃避，但實作時卻常常不理解背後的原理，因為只顧著寫程式，導致很矛盾。時間很趕，上課內容很緊湊，加上專注力無法持續太久，我也不想讓腦袋一直處於高壓狀態，這讓我在學習上感到掙扎。

技術上需要補的東西也很多，像是 HTML、CSS 和 JavaScript，雖然知道需要補，但光看影片並不能解決問題，因為我知道基礎但不會運用，所以我覺得需要直接實作一個真實專案。我打算先做一個攻略網站，第一版使用純 HTML、CSS 和 JavaScript。如果第一版做得不錯，也許會進行第二版，嘗試加入 React、Tailwind 和 PostgreSQL，甚至 Redis。這樣的規劃希望能讓我在期末專題時，有機會從使用過的技術中找到適合的排列組合，但我不確定這過程能不能真的達到漸進式成長。

然而，上課時專注學習有點困難，老師的聲音透過麥克風震得耳朵很痛，讓我沒辦法專心學習或看線上影片。他教的內容不是不好，但我發現自己總是在第一次課堂時抓不到重點，無法立即學會。遇到問題我又無法當下理解，結果就是自暴自棄，不太願意當場搞懂，只想帶回家研究，但這其實不是一個好的學習方式。我記錄下這些感受，是因為我覺得自己又陷入了一個學習的泥潭，或許是過去的壞習慣和學習方式的缺陷導致了現在的結果。

1. 環境設定與問題解決

安裝 XAMPP 與設定 MySQL

  • 步驟：
    1. 下載並安裝 XAMPP：從 XAMPP 官網 下載對應作業系統的版本，安裝時選擇 Apache 和 MySQL 模組。
    2. 啟動 Apache 和 MySQL：
      • 若 MySQL 無法啟動，檢查是否有其他應用程式占用了 3306 端口。
      • 修改 my.ini 將 MySQL 端口更改為 3307，然後重啟服務。
    3. 測試 phpMyAdmin：在瀏覽器中打開 http://localhost/phpmyadmin，確認資料庫管理介面是否正常顯示。

常見問題與解決方法

  • 問題 1：Apache 或 MySQL 無法啟動
    • 原因：端口衝突。
    • 解決：
      • 檢查 httpd.conf 和 my.ini 設定檔，修改端口並確保未被占用。
      • 在終端執行 netstat -an 確認服務占用情況。
  • 問題 2：phpMyAdmin 無法連線 MySQL
    • 原因：端口設置不一致。
    • 解決：確保 config.inc.php 中的 $cfg['Servers'][$i]['port'] 設置為正確的端口。

2. 資料庫設計與正規化

ER 圖與資料庫結構設計

  • 案例：用戶與訂單管理系統
    • 用戶資料表 (UserInfo)
      • uid (主鍵): CHAR(10)
      • cname: VARCHAR(50)
      • email: VARCHAR(100)
    • 訂單資料表 (Orders)
      • order_id (主鍵): INT
      • uid (外鍵): CHAR(10)
      • order_date: DATETIME

正規化範例

  • 第一正規化 (1NF)

    • 違規：

      uid         cname         phone
A01        王大明       0912345678, 0987654321

    • 修正：

      uid         cname         phone
A01        王大明       0912345678
A01        王大明       0987654321

  • 第二正規化 (2NF)

    • 違規：

      uid         cname         order_id      order_date
A01        王大明       O001          2024-01-01
      • cname 與 order_id 無直接關係。

    • 修正：將 UserInfo 和 Orders 分為兩個表，通過外鍵關聯。

3. SQL 指令基礎與實作

基礎指令

  • 查詢語法：

    SELECT * FROM UserInfo;
SELECT cname, email FROM UserInfo WHERE cname LIKE '李%';

  • 排序：

    SELECT * FROM UserInfo ORDER BY cname ASC;

JOIN 查詢

  • 內部關聯 (Inner Join)

    SELECT UserInfo.uid, cname, order_id, order_date
FROM UserInfo
JOIN Orders ON UserInfo.uid = Orders.uid;

群組與彙總

  • 統計每位用戶的訂單數量：

    SELECT uid, COUNT(order_id) AS order_count
FROM Orders
GROUP BY uid;

  • 篩選條件 (HAVING)：

    SELECT uid, COUNT(order_id) AS order_count
FROM Orders
GROUP BY uid
HAVING order_count > 5;

處理重複資料

  • DISTINCT 解決重複資料問題：

    SELECT DISTINCT cname
FROM UserInfo;

  • 拆表以處理資料重複與冗餘：

    • 多對多關係的用戶和地址：

      CREATE TABLE UserAddress (
    uid CHAR(10),
    address_id INT,
    PRIMARY KEY (uid, address_id)
);

4. 進階應用：Trigger 與 Stored Procedure

Trigger 應用

  • 新增資料自動記錄：

    CREATE TRIGGER tr_log_userinfo_insert
AFTER INSERT ON UserInfo
FOR EACH ROW
BEGIN
    INSERT INTO Log (log_time, details)
    VALUES (NOW(), CONCAT('新增用戶: ', NEW.uid));
END;

Stored Procedure 實作

  • 用戶登入驗證範例：

    DELIMITER $$
CREATE PROCEDURE sp_login(IN user_id CHAR(10), IN user_pwd VARCHAR(50))
BEGIN
    DECLARE user_exists INT;
    SELECT COUNT(*) INTO user_exists
    FROM UserInfo
    WHERE uid = user_id AND password = user_pwd;

    IF user_exists > 0 THEN
        SELECT 'Login Successful';
    ELSE
        SELECT 'Invalid Credentials';
    END IF;
END $$
DELIMITER ;

  • 多條件判斷與返回值：

    CREATE PROCEDURE sp_get_user_order(IN user_id CHAR(10))
BEGIN
    SELECT order_id, order_date
    FROM Orders
    WHERE uid = user_id;
END;

5. 時間與日期處理

處理範例

  • 查詢某時段訂單：

    SELECT *
FROM Orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

  • 計算使用者年齡：

    SELECT cname, YEAR(CURDATE()) - YEAR(birth_date) AS age
FROM UserInfo;

  • 轉換時區：

    SELECT CONVERT_TZ(order_date, 'UTC', 'Asia/Taipei')
FROM Orders;

6. 案例

案例：用戶與訂單管理系統的實作

  1. 資料庫建立：

    CREATE TABLE UserInfo (
    uid CHAR(10) PRIMARY KEY,
    cname VARCHAR(50),
    email VARCHAR(100),
    password VARCHAR(50)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    uid CHAR(10),
    order_date DATETIME,
    FOREIGN KEY (uid) REFERENCES UserInfo(uid)
);

  2. 用戶新增操作：

    INSERT INTO UserInfo (uid, cname, email, password)
VALUES ('A01', '王大明', 'david@example.com', 'password123');

  3. 查詢與統計：

    • 查詢王大明的所有訂單：

      SELECT * FROM Orders WHERE uid = 'A01';

    • 統計每位用戶的訂單金額總和：

      SELECT UserInfo.uid, cname, SUM(order_amount) AS total_spent
FROM UserInfo
JOIN Orders ON UserInfo.uid = Orders.uid
GROUP BY UserInfo.uid;

欄位與索引設計的實務建議

  • 主鍵與外鍵設計：

    • 為主鍵添加自動編號以確保唯一性。
    CREATE TABLE Products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

  • 索引應用：

    • 將經常查詢的欄位設置索引以提升效能。
    CREATE INDEX idx_cname ON UserInfo(cname);

arrow
arrow
    文章標籤
    SQL 心得 職訓 產業新尖兵 全端工程師 反省
    全站熱搜
    創作者介紹
    創作者 enjoylyingflat 的頭像
    enjoylyingflat

    enjoylyingflat的部落格

    enjoylyingflat 發表在 痞客邦 留言(0) 人氣()