PHP - SQL 語法與資料庫操作

---

課程簡介

SQL（Structured Query Language）是一種專門用於操作資料庫的語言。本課程將介紹常見的 SQL 語法，包括建立資料表、插入資料、查詢、更新與刪除操作（CRUD），以及索引與約束的應用，幫助你更高效地管理資料庫。

---

資料庫基本操作

1. 建立資料庫

CREATE DATABASE example_db;

2. 使用資料庫

USE example_db;

3. 刪除資料庫

DROP DATABASE example_db;

---

資料表操作

1. 建立資料表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 修改資料表結構

新增欄位

ALTER TABLE users ADD COLUMN age INT;

刪除欄位

ALTER TABLE users DROP COLUMN age;

修改欄位類型

ALTER TABLE users MODIFY COLUMN username VARCHAR(100);

3. 刪除資料表

DROP TABLE users;

---

資料操作（CRUD）

1. 插入資料

INSERT INTO users (username, email) VALUES ('Alice', 'alice@example.com');

2. 查詢資料

基本查詢

SELECT * FROM users;

篩選條件

SELECT * FROM users WHERE username = 'Alice';

排序

SELECT * FROM users ORDER BY created_at DESC;

限制與偏移

SELECT * FROM users LIMIT 5 OFFSET 10;

3. 更新資料

UPDATE users SET email = 'newemail@example.com' WHERE username = 'Alice';

4. 刪除資料

DELETE FROM users WHERE username = 'Alice';

---

索引與約束

1. 索引

索引可以加速資料查詢。

建立索引

CREATE INDEX idx_username ON users (username);

刪除索引

DROP INDEX idx_username ON users;

2. 常見約束

• PRIMARY KEY：主鍵，唯一標識每一筆記錄。
• UNIQUE：保證某欄位值的唯一性。
• NOT NULL：禁止欄位為空值。
• FOREIGN KEY：建立表之間的關聯。

範例：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

---

高級操作

1. 聚合函數

• COUNT：計算記錄數量。
• SUM：計算總和。
• AVG：計算平均值。
• MAX 和 MIN：找出最大或最小值。

範例：

SELECT COUNT(*) AS total_users FROM users;
SELECT AVG(age) AS average_age FROM users;

2. 分組與分組條件

SELECT username, COUNT(*) AS post_count
FROM posts
GROUP BY username
HAVING post_count > 5;

3. 多表聯合查詢

• INNER JOIN：取交集。
• LEFT JOIN：保留左表的所有記錄。
• RIGHT JOIN：保留右表的所有記錄。

範例：

SELECT users.username, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id;

---

教學練習

練習 1：建立與操作資料表

1. 建立一個名為 products 的表，包含 id、name 和 price 欄位。
2. 插入至少三筆資料。
3. 查詢所有資料，並按照價格降序排列。

---

練習 2：資料分析與聚合

1. 計算 users 表中共有多少使用者。
2. 使用分組查詢計算每位使用者的訂單數量。

---

練習 3：聯合查詢

建立 posts 表，並實現查詢使用者名稱與他們的文章標題。

---

教學重點

• 掌握 SQL 的基本語法與資料庫操作技巧。
• 熟悉資料表的設計與修改，理解約束的應用。
• 學會進行多表聯合查詢與聚合分析。

---