歡迎來到 SQL資料庫百科全書

簡介

本節提供MySQL和Oracle在Windows、Linux、MacOS的安裝指南，包含步驟、模擬截圖、參數表格和注意事項，幫助您配置資料庫環境。

mysql.user
+------------+---------+
| user       | VARCHAR |
| host       | VARCHAR |
| password   | VARCHAR |
+------------+---------+

mysql.user
+------------+---------+
| user       | VARCHAR |
| host       | VARCHAR |
| password   | VARCHAR |
+------------+---------+

mysql.user
+------------+---------+
| user       | VARCHAR |
| host       | VARCHAR |
| password   | VARCHAR |
+------------+---------+

dba_users
+------------+---------+
| username   | VARCHAR |
| user_id    | NUMBER  |
| created    | DATE    |
+------------+---------+

dba_users
+------------+---------+
| username   | VARCHAR |
| user_id    | NUMBER  |
| created    | DATE    |
+------------+---------+

dba_users
+------------+---------+
| username   | VARCHAR |
| user_id    | NUMBER  |
| created    | DATE    |
+------------+---------+

簡介

資料庫正規化（Normalization）是設計資料庫的過程，透過消除冗餘資料和確保資料一致性，提升儲存效率和查詢正確性。本節詳細說明第一至第五階正規化（1NF 至 5NF），每階包含定義、規則、範例、表格和圖示，適合初學者與進階使用者。

未正規化
+------------+--------+--------------+
| 學生ID     | 姓名   | 課程         |
+------------+--------+--------------+
| 1          | 張三   | 數學,物理    |
| 2          | 李四   | 化學,生物    |
+------------+--------+--------------+

1NF
+------------+--------+--------+
| 學生ID     | 姓名   | 課程   |
+------------+--------+--------+
| 1          | 張三   | 數學   |
| 1          | 張三   | 物理   |
| 2          | 李四   | 化學   |
| 2          | 李四   | 生物   |
+------------+--------+--------+

1NF
+------------+----------+--------+------------+
| 學生ID     | 課程ID   | 姓名   | 課程名稱   |
+------------+----------+--------+------------+
| 1          | 101      | 張三   | 數學       |
| 1          | 102      | 張三   | 物理       |
| 2          | 103      | 李四   | 化學       |
+------------+----------+--------+------------+

2NF
學生
+------------+--------+
| 學生ID     | 姓名   |
+------------+--------+
| 1          | 張三   |
| 2          | 李四   |
+------------+--------+

選課
+------------+----------+------------+
| 學生ID     | 課程ID   | 課程名稱   |
+------------+----------+------------+
| 1          | 101      | 數學       |
| 1          | 102      | 物理       |
| 2          | 103      | 化學       |
+------------+----------+------------+

2NF
+------------+----------+------------+----------+
| 學生ID     | 課程ID   | 課程名稱   | 教師     |
+------------+----------+------------+----------+
| 1          | 101      | 數學       | 王老師   |
| 1          | 102      | 物理       | 陳老師   |
+------------+----------+------------+----------+

3NF
選課
+------------+----------+
| 學生ID     | 課程ID   |
+------------+----------+
| 1          | 101      |
| 1          | 102      |
+------------+----------+

課程
+----------+------------+----------+
| 課程ID   | 課程名稱   | 教師     |
+----------+------------+----------+
| 101      | 數學       | 王老師   |
| 102      | 物理       | 陳老師   |
+----------+------------+----------+

3NF
+------------+--------+--------+
| 學生ID     | 興趣   | 課程   |
+------------+--------+--------+
| 1          | 音樂   | 數學   |
| 1          | 音樂   | 物理   |
| 1          | 運動   | 數學   |
| 1          | 運動   | 物理   |
+------------+--------+--------+

4NF
學生興趣
+------------+--------+
| 學生ID     | 興趣   |
+------------+--------+
| 1          | 音樂   |
| 1          | 運動   |
+------------+--------+

學生課程
+------------+--------+
| 學生ID     | 課程   |
+------------+--------+
| 1          | 數學   |
| 1          | 物理   |
+------------+--------+

4NF
+----------+--------+--------+
| 代理商   | 產品   | 公司   |
+----------+--------+--------+
| A1       | P1     | C1     |
| A1       | P2     | C1     |
| A2       | P1     | C2     |
+----------+--------+--------+

5NF
代理商-產品
+----------+--------+
| 代理商   | 產品   |
+----------+--------+
| A1       | P1     |
| A1       | P2     |
| A2       | P1     |
+----------+--------+

產品-公司
+--------+--------+
| 產品   | 公司   |
+--------+--------+
| P1     | C1     |
| P2     | C1     |
| P1     | C2     |
+--------+--------+

代理商-公司
+----------+--------+
| 代理商   | 公司   |
+----------+--------+
| A1       | C1     |
| A2       | C2     |
+----------+--------+

簡介

資料庫效能提升是確保快速查詢和穩定運行的關鍵。本節針對 MySQL 和 Oracle，介紹三種方法：資料庫配置優化、正規化調整、SQL 語法優化。每部分包含詳細步驟、範例、表格和圖示，幫助您提升效能。

MySQL 配置
+------------------+       +------------------+
| 硬體 (16GB RAM) | ----> | Buffer Pool (4G) |
+------------------+       +------------------+
                           | Query Cache      |
                           +------------------+
                           | Connections (500)|
                           +------------------+

Oracle 配置
+------------------+       +------------------+
| 硬體 (32GB RAM) | ----> | SGA (8G)         |
+------------------+       +------------------+
                           | PGA (2G)         |
                           +------------------+
                           | Parallel (16)    |
                           +------------------+

正規化
學生                課程
+------------+       +------------+
| student_id |<----->| course_id  |
| course_id  |       | course_name|
+------------+       +------------+

反正規化
學生
+------------+------------+
| student_id | course_name|
+------------+------------+

SELECT * FROM employees WHERE UPPER(name) = 'ZHANGSAN';

SELECT name, salary FROM employees WHERE name = 'ZhangSan';
CREATE INDEX idx_name ON employees(name);

未優化
+------------------+
| 全表掃描 (慢)    |
| employees (10M)   |
+------------------+

優化
+------------------+
| 索引查詢 (快)    |
| idx_name --------+
+------------------+

語法

SELECT 檢索資料表資料。

SELECT column1, column2 FROM table_name WHERE condition;

範例

一般

查詢員工姓名和入職日期。

SELECT name, hire_date FROM employees WHERE department = 'HR';

MySQL

查詢高薪員工，限10筆。

SELECT name, salary FROM employees WHERE salary > 50000 LIMIT 10;

Oracle

查詢前5名員工。

SELECT name, department FROM employees WHERE ROWNUM <= 5;

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
| hire_date  | DATE    |
| salary     | DECIMAL |
| department | VARCHAR |
+------------+---------+

語法

DISTINCT 去除重複行。

SELECT DISTINCT column1 FROM table_name;

範例

一般

查詢不重複部門。

SELECT DISTINCT department FROM employees;

MySQL

查詢不重複職位。

SELECT DISTINCT job_title FROM employees;

Oracle

查詢不重複城市。

SELECT DISTINCT city FROM locations;

結構

employees
+------------+---------+
| id         | INT     |
| department | VARCHAR |
| job_title  | VARCHAR |
+------------+---------+

語法

WHERE 篩選符合條件的資料。

SELECT column1 FROM table_name WHERE condition;

範例

一般

查詢高薪員工。

SELECT name, salary FROM employees WHERE salary > 50000;

MySQL

查詢2023年入職員工。

SELECT name, hire_date FROM employees WHERE YEAR(hire_date) = 2023;

Oracle

查詢IT部門員工。

SELECT name, department FROM employees WHERE department = 'IT';

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
| salary     | DECIMAL |
| hire_date  | DATE    |
| department | VARCHAR |
+------------+---------+

語法

AND/OR 組合條件。

SELECT column FROM table WHERE condition1 AND/OR condition2;

範例

一般

查詢IT部門高薪員工。

SELECT name, salary FROM employees WHERE department = 'IT' AND salary > 50000;

MySQL

查詢HR或Sales員工。

SELECT name FROM employees WHERE department = 'HR' OR department = 'Sales';

Oracle

查詢高薪或管理職。

SELECT name FROM employees WHERE salary > 70000 OR job_title = 'Manager';

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
| salary     | DECIMAL |
| department | VARCHAR |
| job_title  | VARCHAR |
+------------+---------+

語法

IN 指定多值，簡化條件。

SELECT column FROM table WHERE column IN (value1, value2);

範例

一般

查詢HR和IT部門員工。

SELECT name, department FROM employees WHERE department IN ('HR', 'IT');

MySQL

查詢特定職位員工。

SELECT name, job_title FROM employees WHERE job_title IN ('Manager', 'Developer');

Oracle

查詢特定城市客戶。

SELECT name, city FROM customers WHERE city IN ('Taipei', 'Kaohsiung');

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
| department | VARCHAR |
| job_title  | VARCHAR |
+------------+---------+

語法

BETWEEN 篩選範圍內資料。

SELECT column FROM table WHERE column BETWEEN value1 AND value2;

範例

一般

查詢薪資40000-60000的員工。

SELECT name, salary FROM employees WHERE salary BETWEEN 40000 AND 60000;

MySQL

查詢2023年入職員工。

SELECT name, hire_date FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

Oracle

查詢訂單金額範圍。

SELECT order_id, amount FROM orders WHERE amount BETWEEN 1000 AND 5000;

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
| salary     | DECIMAL |
| hire_date  | DATE    |
+------------+---------+

語法

萬用字元（%, _）與 LIKE 搭配用於模式匹配。

SELECT column FROM table WHERE column LIKE pattern;

範例

一般

查詢姓名以「張」開頭的員工。

SELECT name FROM employees WHERE name LIKE '張%';

MySQL

查詢部門含「Sale」的記錄。

SELECT name, department FROM employees WHERE department LIKE '%Sale%';

Oracle

查詢產品名稱第二字為「P」。

SELECT prod_name FROM products WHERE prod_name LIKE '_P%';

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
| department | VARCHAR |
+------------+---------+

語法

LIKE 搭配萬用字元篩選資料。

SELECT column FROM table WHERE column LIKE pattern;

範例

一般

查詢職位含「Manager」的員工。

SELECT name, job_title FROM employees WHERE job_title LIKE '%Manager%';

MySQL

查詢電子郵件以「@example.com」結尾的客戶。

SELECT name, email FROM customers WHERE email LIKE '%@example.com';

Oracle

查詢城市以「Tai」開頭。

SELECT city FROM locations WHERE city LIKE 'Tai%';

結構

customers
+-------+---------+
| id    | INT     |
| name  | VARCHAR |
| email | VARCHAR |
+-------+---------+

語法

ORDER BY 對結果排序，支援ASC/DESC。

SELECT column FROM table ORDER BY column [ASC|DESC];

範例

一般

按薪資降序查詢員工。

SELECT name, salary FROM employees ORDER BY salary DESC;

MySQL

按入職日期升序查詢。

SELECT name, hire_date FROM employees ORDER BY hire_date ASC;

Oracle

按部門和薪資排序。

SELECT name, department, salary FROM employees ORDER BY department ASC, salary DESC;

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
| salary     | DECIMAL |
| hire_date  | DATE    |
| department | VARCHAR |
+------------+---------+

語法

SQL函數處理資料，如聚合或字串操作。

SELECT function(column) FROM table;

範例

一般

計算平均薪資。

SELECT AVG(salary) AS avg_salary FROM employees;

MySQL

合併姓名和部門。

SELECT CONCAT(name, ' - ', department) AS info FROM employees;

Oracle

計算訂單總數。

SELECT COUNT(*) AS total_orders FROM orders;

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
| salary     | DECIMAL |
| department | VARCHAR |
+------------+---------+

語法

AVG 計算數值欄位平均值。

SELECT AVG(column) FROM table;

範例

一般

計算員工平均薪資。

SELECT AVG(salary) AS avg_salary FROM employees;

MySQL

計算IT部門平均薪資。

SELECT AVG(salary) AS avg_it_salary FROM employees WHERE department = 'IT';

Oracle

計算訂單平均金額。

SELECT AVG(amount) AS avg_amount FROM orders;

結構

employees
+------------+---------+
| id         | INT     |
| salary     | DECIMAL |
| department | VARCHAR |
+------------+---------+

語法

COUNT 計算符合條件的記錄數。

SELECT COUNT(column) FROM table WHERE condition;

範例

一般

計算員工總數。

SELECT COUNT(*) AS total_employees FROM employees;

MySQL

計算HR部門員工數。

SELECT COUNT(*) AS hr_count FROM employees WHERE department = 'HR';

Oracle

計算有效訂單數。

SELECT COUNT(*) AS order_count FROM orders WHERE status = 'Active';

結構

employees
+------------+---------+
| id         | INT     |
| department | VARCHAR |
+------------+---------+

語法

MAX 查找欄位最大值。

SELECT MAX(column) FROM table;

範例

一般

查找最高薪資。

SELECT MAX(salary) AS max_salary FROM employees;

MySQL

查找IT部門最高薪資。

SELECT MAX(salary) AS max_it_salary FROM employees WHERE department = 'IT';

Oracle

查找最大訂單金額。

SELECT MAX(amount) AS max_amount FROM orders;

結構

employees
+------------+---------+
| id         | INT     |
| salary     | DECIMAL |
| department | VARCHAR |
+------------+---------+

語法

MIN 查找欄位最小值。

SELECT MIN(column) FROM table;

範例

一般

查找最低薪資。

SELECT MIN(salary) AS min_salary FROM employees;

MySQL

查找HR部門最低薪資。

SELECT MIN(salary) AS min_hr_salary FROM employees WHERE department = 'HR';

Oracle

查找最小訂單金額。

SELECT MIN(amount) AS min_amount FROM orders;

結構

employees
+------------+---------+
| id         | INT     |
| salary     | DECIMAL |
| department | VARCHAR |
+------------+---------+

語法

SUM 計算數值欄位總和。

SELECT SUM(column) FROM table;

範例

一般

計算員工薪資總和。

SELECT SUM(salary) AS total_salary FROM employees;

MySQL

計算IT部門薪資總和。

SELECT SUM(salary) AS total_it_salary FROM employees WHERE department = 'IT';

Oracle

計算訂單總金額。

SELECT SUM(amount) AS total_amount FROM orders;

結構

employees
+------------+---------+
| id         | INT     |
| salary     | DECIMAL |
| department | VARCHAR |
+------------+---------+

語法

GROUP BY 按欄位分組，與聚合函數搭配。

SELECT column, AGGREGATE(column2) FROM table GROUP BY column;

範例

一般

按部門計算平均薪資。

SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department;

MySQL

按職位計算人數。

SELECT job_title, COUNT(*) AS count FROM employees GROUP BY job_title;

Oracle

按年份計算訂單總額。

SELECT TO_CHAR(order_date, 'YYYY') AS year, SUM(amount) AS total FROM orders GROUP BY TO_CHAR(order_date, 'YYYY');

結構

employees
+------------+---------+
| id         | INT     |
| department | VARCHAR |
| job_title  | VARCHAR |
| salary     | DECIMAL |
+------------+---------+

語法

HAVING 篩選 GROUP BY 結果。

SELECT column, AGGREGATE(column2) FROM table GROUP BY column HAVING condition;

範例

一般

查詢平均薪資大於55000的部門。

SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > 55000;

MySQL

查詢人數大於5的職位。

SELECT job_title, COUNT(*) AS count FROM employees GROUP BY job_title HAVING COUNT(*) > 5;

Oracle

查詢訂單總額大於10000的年份。

SELECT TO_CHAR(order_date, 'YYYY') AS year, SUM(amount) AS total FROM orders GROUP BY TO_CHAR(order_date, 'YYYY') HAVING SUM(amount) > 10000;

結構

employees
+------------+---------+
| id         | INT     |
| department | VARCHAR |
| job_title  | VARCHAR |
| salary     | DECIMAL |
+------------+---------+

語法

別名（AS）為欄位或表指定臨時名稱。

SELECT column AS alias_name FROM table;

範例

一般

為薪資取別名。

SELECT salary AS employee_salary FROM employees;

MySQL

為表取別名並查詢。

SELECT e.name AS emp_name FROM employees AS e WHERE e.department = 'HR';

Oracle

為計算欄位取別名。

SELECT COUNT(*) AS order_count FROM orders AS o;

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
| salary     | DECIMAL |
| department | VARCHAR |
+------------+---------+

語法

AS 為欄位、表或子查詢指定別名。

SELECT column AS alias_name FROM table;

範例

一般

為部門名稱取別名。

SELECT department AS dept_name FROM employees;

--------------------
| dept_name       |
| HR              |
| IT              |
--------------------

MySQL

為計算欄位取別名，查詢員工總數。

SELECT COUNT(*) AS emp_count FROM employees AS e;

--------------------
| emp_count       |
| 50              |
--------------------

Oracle

為子查詢取別名，查詢部門名稱。

SELECT dept_name FROM (SELECT department AS dept_name FROM employees) AS dept;

--------------------
| dept_name       |
| HR              |
| IT              |
--------------------

結構

employees
+------------+---------+
| id         | INT     |
| department | VARCHAR |
+------------+---------+

語法

JOIN 合併多個表的資料，通常使用 INNER JOIN 來匹配條件。

SELECT table1.column1, table2.column2 
FROM table1 
INNER JOIN table2 ON table1.key = table2.key;

範例

一般

查詢員工及其部門名稱。

SELECT e.name, d.dept_name 
FROM employees e 
INNER JOIN departments d ON e.dept_id = d.dept_id;

--------------------
| name    | dept_name  |
| 張三    | HR         |
| 趙六    | IT         |
--------------------

MySQL

查詢訂單及其客戶名稱。

SELECT o.order_id, c.name 
FROM orders o 
INNER JOIN customers c ON o.cust_id = c.id;

--------------------
| order_id | name     |
| 101      | 林八     |
| 102      | 陳七     |
--------------------

Oracle

查詢員工及其主管名稱。

SELECT e1.name AS employee, e2.name AS manager 
FROM employees e1 
INNER JOIN employees e2 ON e1.manager_id = e2.id;

--------------------
| employee | manager  |
| 李四     | 張三     |
| 王五     | 趙六     |
--------------------

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
| dept_id    | INT     |
| manager_id | INT     |
+------------+---------+
departments
+---------+---------+
| dept_id | INT     |
| dept_name | VARCHAR |
+---------+---------+

語法

OUTER JOIN 包含未匹配的資料，常用 LEFT OUTER JOIN 或 RIGHT OUTER JOIN。

SELECT table1.column1, table2.column2 
FROM table1 
LEFT OUTER JOIN table2 ON table1.key = table2.key;

範例

一般

查詢所有員工及其部門名稱（包括未分配部門的員工）。

SELECT e.name, d.dept_name 
FROM employees e 
LEFT OUTER JOIN departments d ON e.dept_id = d.dept_id;

--------------------
| name    | dept_name  |
| 張三    | HR         |
| 趙六    | IT         |
| 陳七    | NULL       |
--------------------

MySQL

查詢所有客戶及其訂單（包括無訂單的客戶）。

SELECT c.name, o.order_id 
FROM customers c 
LEFT OUTER JOIN orders o ON c.id = o.cust_id;

--------------------
| name    | order_id   |
| 林八    | 101        |
| 陳七    | NULL       |
--------------------

Oracle

查詢所有部門及其員工（包括無員工的部門）。

SELECT d.dept_name, e.name 
FROM departments d 
RIGHT OUTER JOIN employees e ON d.dept_id = e.dept_id;

--------------------
| dept_name | name     |
| HR        | 張三     |
| IT        | 趙六     |
| NULL      | 陳七     |
--------------------

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
| dept_id    | INT     |
+------------+---------+
departments
+---------+---------+
| dept_id | INT     |
| dept_name | VARCHAR |
+---------+---------+

語法

CONCAT 或 || 用於合併字串（根據資料庫不同）。

SELECT CONCAT(column1, ' ', column2) AS result FROM table;

範例

一般

合併員工姓名和部門。

SELECT CONCAT(name, ' - ', department) AS employee_info FROM employees;

--------------------
| employee_info   |
| 張三 - HR       |
| 趙六 - IT       |
--------------------

MySQL

合併客戶姓名和電子郵件。

SELECT CONCAT(name, ' (', email, ')') AS customer_info FROM customers;

--------------------
| customer_info       |
| 林八 (lin@example.com) |
| 陳七 (chen@example.com) |
--------------------

Oracle

使用 || 合併產品名稱和價格（Oracle 不支援 CONCAT 時使用 ||）。

SELECT prod_name || ' - $' || price AS product_info FROM products;

--------------------
| product_info    |
| Apple - $50     |
| Banana - $30    |
--------------------

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
| department | VARCHAR |
+------------+---------+
customers
+-------+---------+
| id    | INT     |
| name  | VARCHAR |
| email | VARCHAR |
+-------+---------+

語法

SUBSTRING 從字串中提取指定長度的片段（起始位置從 1 開始）。

SELECT SUBSTRING(column, start, length) AS result FROM table;

範例

一般

提取員工姓名的前 3 個字元。

SELECT SUBSTRING(name, 1, 3) AS short_name FROM employees;

--------------------
| short_name      |
| 張三            |
| 趙六            |
--------------------

MySQL

提取電子郵件的前 5 個字元。

SELECT SUBSTRING(email, 1, 5) AS email_part FROM customers;

--------------------
| email_part      |
| lin@e           |
| chen@           |
--------------------

Oracle

提取產品名稱的第 2 到 4 個字元。

SELECT SUBSTR(prod_name, 2, 3) AS name_part FROM products;

--------------------
| name_part       |
| ppl             |
| ana             |
--------------------

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
+------------+---------+
customers
+-------+---------+
| id    | INT     |
| email | VARCHAR |
+-------+---------+
products
+------------+---------+
| prod_id    | INT     |
| prod_name  | VARCHAR |
+------------+---------+

語法

TRIM 移除字串前後的空白（可指定移除其他字元）。

SELECT TRIM(column) AS result FROM table;

範例

一般

移除員工部門名稱前後的空白。

SELECT TRIM(department) AS clean_dept FROM employees;

--------------------
| clean_dept      |
| HR              |
| IT              |
--------------------

MySQL

移除電子郵件前後的空白。

SELECT TRIM(email) AS clean_email FROM customers;

--------------------
| clean_email     |
| lin@example.com |
| chen@example.com|
--------------------

Oracle

移除產品名稱前後的指定字元（如「-」）。

SELECT TRIM(BOTH '-' FROM prod_name) AS clean_name FROM products;

--------------------
| clean_name      |
| Apple           |
| Banana          |
--------------------

結構

employees
+------------+---------+
| id         | INT     |
| department | VARCHAR |
+------------+---------+
customers
+-------+---------+
| id    | INT     |
| email | VARCHAR |
+-------+---------+
products
+------------+---------+
| prod_id    | INT     |
| prod_name  | VARCHAR |
+------------+---------+

語法

LENGTH 計算字串的長度（返回字元數，根據資料庫不同可能使用 LEN）。

SELECT LENGTH(column) AS result FROM table;

範例

一般

計算員工姓名的長度。

SELECT name, LENGTH(name) AS name_length FROM employees;

--------------------
| name    | name_length |
| 張三    | 2           |
| 趙六    | 2           |
--------------------

MySQL

計算電子郵件地址的長度。

SELECT email, LENGTH(email) AS email_length FROM customers;

--------------------
| email           | email_length |
| lin@example.com | 15           |
| chen@example.com| 16           |
--------------------

Oracle

計算產品名稱的長度。

SELECT prod_name, LENGTH(prod_name) AS name_length FROM products;

--------------------
| prod_name | name_length |
| Apple     | 5           |
| Banana    | 6           |
--------------------

結構

employees
+------------+---------+
| id         | INT     |
| name       | VARCHAR |
+------------+---------+
customers
+-------+---------+
| id    | INT     |
| email | VARCHAR |
+-------+---------+
products
+------------+---------+
| prod_id    | INT     |
| prod_name  | VARCHAR |
+------------+---------+

語法

REPLACE 將字串中的指定部分替換為新值。

SELECT REPLACE(column, 'old', 'new') AS result FROM table;

範例

一般

將員工部門名稱中的「HR」替換為「人力資源」。

SELECT REPLACE(department, 'HR', '人力資源') AS new_dept FROM employees;

--------------------
| new_dept        |
| 人力資源        |
| IT              |
--------------------

MySQL

將電子郵件中的「example」替換為「mycompany」。

SELECT REPLACE(email, 'example', 'mycompany') AS new_email FROM customers;

--------------------
| new_email         |
| lin@mycompany.com |
| chen@mycompany.com|
--------------------

Oracle

將產品名稱中的「Apple」替換為「Orange」。

SELECT REPLACE(prod_name, 'Apple', 'Orange') AS new_name FROM products;

--------------------
| new_name        |
| Orange          |
| Banana          |
--------------------

結構

employees
+------------+---------+
| id         | INT     |
| department | VARCHAR |
+------------+---------+
customers
+-------+---------+
| id    | INT     |
| email | VARCHAR |
+-------+---------+
products
+------------+---------+
| prod_id    | INT     |
| prod_name  | VARCHAR |
+------------+---------+

語法

CREATE TABLE 建立新資料表。

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint
);

範例

一般

建立員工表。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2)
);

MySQL

建立自動遞增部門表。

CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

Oracle

建立序列產品表。

CREATE SEQUENCE prod_seq START WITH 1;
CREATE TABLE products (
    prod_id NUMBER PRIMARY KEY,
    prod_name VARCHAR2(50),
    price NUMBER(10,2)
);

結構

products
+------------+---------+
| prod_id    | NUMBER  |
| prod_name  | VARCHAR |
| price      | NUMBER  |
+------------+---------+

語法

CONSTRAINT 定義資料表規則。

CREATE TABLE table_name (
    column datatype CONSTRAINT constraint_name constraint_type
);

範例

一般

建立帶唯一約束的客戶表。

CREATE TABLE customers (
    id INT PRIMARY KEY,
    email VARCHAR(100) CONSTRAINT unique_email UNIQUE
);

MySQL

添加檢查約束。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    amount DECIMAL(10,2) CONSTRAINT chk_amount CHECK (amount > 0)
);

Oracle

建立帶非空約束的產品表。

CREATE TABLE products (
    prod_id NUMBER PRIMARY KEY,
    prod_name VARCHAR2(50) CONSTRAINT nn_name NOT NULL
);

結構

customers
+-------+---------+
| id    | INT     |
| email | VARCHAR |
+-------+---------+

語法

PRIMARY KEY 定義唯一識別列。

CREATE TABLE table_name (
    column datatype PRIMARY KEY
);

範例

一般

建立帶主鍵的員工表。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

MySQL

建立多列主鍵。

CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Oracle

使用序列生成主鍵。

CREATE SEQUENCE emp_seq START WITH 1;
CREATE TABLE employees (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50)
);

結構

employees
+-------+---------+
| id    | INT     |
| name  | VARCHAR |
+-------+---------+

語法

FOREIGN KEY 建立表間關聯。

CREATE TABLE table_name (
    column datatype,
    CONSTRAINT fk_name FOREIGN KEY (column) REFERENCES parent_table(parent_column)
);

範例

一般

建立帶外來鍵的訂單表。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    cust_id INT,
    CONSTRAINT fk_cust FOREIGN KEY (cust_id) REFERENCES customers(id)
);

MySQL

建立帶級聯刪除的外來鍵。

CREATE TABLE order_details (
    detail_id INT PRIMARY KEY,
    order_id INT,
    CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);

Oracle

建立帶外來鍵的員工表。

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    dept_id NUMBER,
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

結構

orders
+----------+-------+
| order_id | INT   |
| cust_id  | INT   |
+----------+-------+

語法

CREATE VIEW 建立虛擬表。

CREATE VIEW view_name AS SELECT column1, column2 FROM table WHERE condition;

範例

一般

建立高薪員工視圖。

CREATE VIEW high_salary_employees AS
SELECT name, salary FROM employees WHERE salary > 50000;

MySQL

建立部門員工視圖，限10筆。

CREATE VIEW dept_employees AS
SELECT name, department FROM employees WHERE department = 'IT' LIMIT 10;

Oracle

建立訂單總額視圖。

CREATE VIEW order_totals AS
SELECT order_id, SUM(amount) AS total FROM order_details GROUP BY order_id;

結構

high_salary_employees
+-------+---------+
| name  | VARCHAR |
| salary| DECIMAL |
+-------+---------+

語法

CREATE INDEX 提升查詢效率。

CREATE INDEX index_name ON table_name (column);

範例

一般

為員工表姓名欄位建立索引。

CREATE INDEX idx_name ON employees (name);

MySQL

建立唯一索引。

CREATE UNIQUE INDEX idx_email ON customers (email);

Oracle

建立多列索引。

CREATE INDEX idx_order ON orders (cust_id, order_date);

結構

employees
+-------+---------+
| id    | INT     |
| name  | VARCHAR |
+-------+---------+

語法

ALTER TABLE 修改資料表結構。

ALTER TABLE table_name ADD/MODIFY/DROP column datatype;

範例

一般

為員工表添加電子郵件欄位。

ALTER TABLE employees ADD email VARCHAR(100);

MySQL

修改欄位型態。

ALTER TABLE employees MODIFY salary DECIMAL(12,2);

Oracle

刪除欄位。

ALTER TABLE employees DROP COLUMN email;

結構

employees
+-------+---------+
| id    | INT     |
| salary| DECIMAL |
+-------+---------+

語法

ADD COLUMN 新增欄位。

ALTER TABLE table_name ADD column datatype;

範例

一般

為員工表添加電話欄位。

ALTER TABLE employees ADD phone VARCHAR(20);

MySQL

添加欄位並指定位置。

ALTER TABLE employees ADD address VARCHAR(100) AFTER name;

Oracle

添加日期欄位。

ALTER TABLE employees ADD birth_date DATE;

結構

employees
+------------+---------+
| id         | INT     |
| phone      | VARCHAR |
+------------+---------+

語法

MODIFY COLUMN 修改欄位型態或約束。

ALTER TABLE table_name MODIFY column datatype;

範例

一般

修改員工表姓名欄位長度。

ALTER TABLE employees MODIFY name VARCHAR(100);

MySQL

修改薪資欄位型態並添加約束。

ALTER TABLE employees MODIFY salary DECIMAL(12,2) NOT NULL;

Oracle

修改欄位型態。

ALTER TABLE employees MODIFY name VARCHAR2(100);

結構

employees
+-------+---------+
| id    | INT     |
| name  | VARCHAR |
+-------+---------+

語法

ALTER COLUMN 更改欄位屬性（依資料庫而異）。

ALTER TABLE table_name ALTER COLUMN column SET/DROP constraint;

範例

一般

為員工表姓名欄位添加非空約束（通用語法）。

ALTER TABLE employees MODIFY name VARCHAR(50) NOT NULL;

MySQL

移除欄位預設值。

ALTER TABLE employees ALTER COLUMN salary DROP DEFAULT;

Oracle

設置欄位預設值。

ALTER TABLE employees MODIFY salary DEFAULT 0;

結構

employees
+-------+---------+
| id    | INT     |
| name  | VARCHAR |
| salary| DECIMAL |
+-------+---------+

語法

RENAME COLUMN 更改欄位名稱。

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

範例

一般

將員工表姓名欄位改名。

ALTER TABLE employees RENAME COLUMN name TO full_name;

MySQL

改名欄位（MySQL 用 CHANGE）。

ALTER TABLE employees CHANGE name full_name VARCHAR(50);

Oracle

改名欄位。

ALTER TABLE employees RENAME COLUMN name TO full_name;

結構

employees
+------------+---------+
| id         | INT     |
| full_name  | VARCHAR |
+------------+---------+

語法

DROP COLUMN 移除欄位。

ALTER TABLE table_name DROP COLUMN column;

範例

一般

刪除員工表電話欄位。

ALTER TABLE employees DROP COLUMN phone;

MySQL

刪除地址欄位。

ALTER TABLE employees DROP COLUMN address;

Oracle

刪除日期欄位。

ALTER TABLE employees DROP COLUMN birth_date;

結構

employees
+-------+-------+
| id    | INT   |
+-------+-------+

語法

CREATE INDEX 新增索引。

CREATE INDEX index_name ON table_name (column);

範例

一般

為員工表部門欄位添加索引。

CREATE INDEX idx_dept ON employees (department);

MySQL

添加複合索引。

CREATE INDEX idx_name_salary ON employees (name, salary);

Oracle

添加唯一索引。

CREATE UNIQUE INDEX idx_email ON customers (email);

結構

employees
+------------+---------+
| id         | INT     |
| department | VARCHAR |
+------------+---------+

語法

DROP INDEX 移除索引。

DROP INDEX index_name ON table_name;

範例

一般

刪除員工表部門索引。

DROP INDEX idx_dept ON employees;

MySQL

刪除索引（MySQL 用 DROP INDEX 語法）。

ALTER TABLE employees DROP INDEX idx_name_salary;

Oracle

刪除索引。

DROP INDEX idx_email;

結構

employees
+------------+---------+
| id         | INT     |
| department | VARCHAR |
+------------+---------+

語法

ADD CONSTRAINT 新增約束。

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type;

範例

一般

為員工表添加外來鍵約束。

ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

MySQL

添加檢查約束。

ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

Oracle

添加唯一約束。

ALTER TABLE employees ADD CONSTRAINT uk_email UNIQUE (email);

結構

employees
+--------+---------+
| id     | INT     |
| dept_id| INT     |
+--------+---------+

語法

DROP CONSTRAINT 移除約束。

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

範例

一般

刪除員工表外來鍵約束。

ALTER TABLE employees DROP CONSTRAINT fk_dept;

MySQL

刪除檢查約束。

ALTER TABLE employees DROP CONSTRAINT chk_salary;

Oracle

刪除唯一約束。

ALTER TABLE employees DROP CONSTRAINT uk_email;

結構

employees
+--------+-------+
| id     | INT   |
| dept_id| INT   |
+--------+-------+

語法

DROP TABLE 刪除資料表。

DROP TABLE table_name;

範例

一般

刪除員工表。

DROP TABLE employees;

MySQL

刪除表，若存在。

DROP TABLE IF EXISTS departments;

Oracle

刪除表並清除回收站。

DROP TABLE orders PURGE;

結構

[表已刪除]

語法

TRUNCATE TABLE 清空資料表資料。

TRUNCATE TABLE table_name;

範例

一般

清空員工表資料。

TRUNCATE TABLE employees;

MySQL

清空訂單表。

TRUNCATE TABLE orders;

Oracle

清空產品表並重用空間。

TRUNCATE TABLE products REUSE STORAGE;

結構

employees
+-------+-------+
| id    | INT   |
| name  | VARCHAR |
+-------+-------+
[資料已清空]

語法

USE 選擇要操作的資料庫（主要用於 MySQL）。

USE database_name;

範例

一般

選擇公司資料庫（通用語法依賴資料庫系統）。

USE company_db;

MySQL

選擇員工資料庫。

USE employee_db;

Oracle

Oracle 使用模式切換（非 USE 語法）。

ALTER SESSION SET CURRENT_SCHEMA = hr;

結構

[資料庫已選擇]

語法

INSERT INTO 插入新記錄。

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

範例

一般

插入員工記錄。

INSERT INTO employees (id, name, salary) VALUES (1, '張三', 50000);

MySQL

插入多筆記錄。

INSERT INTO employees (id, name, salary) VALUES (2, '李四', 55000), (3, '王五', 60000);

Oracle

使用序列插入。

INSERT INTO employees (id, name, salary) VALUES (emp_seq.NEXTVAL, '趙六', 65000);

結構

employees
+-------+---------+
| id    | INT     |
| name  | VARCHAR |
| salary| DECIMAL |
+-------+---------+

語法

UPDATE 更新資料表記錄。

UPDATE table_name SET column1 = value1 WHERE condition;

範例

一般

更新員工薪資。

UPDATE employees SET salary = 60000 WHERE id = 1;

MySQL

更新多列。

UPDATE employees SET salary = 65000, department = 'IT' WHERE id = 2;

Oracle

更新符合條件的記錄。

UPDATE employees SET salary = 70000 WHERE department = 'HR';

結構

employees
+------------+---------+
| id         | INT     |
| salary     | DECIMAL |
| department | VARCHAR |
+------------+---------+

語法

DELETE FROM 刪除資料表記錄。

DELETE FROM table_name WHERE condition;

範例

一般

刪除特定員工。

DELETE FROM employees WHERE id = 1;

MySQL

刪除部門員工。

DELETE FROM employees WHERE department = 'HR';

Oracle

刪除舊訂單。

DELETE FROM orders WHERE order_date < TO_DATE('2023-01-01', 'YYYY-MM-DD');

結構

employees
+------------+---------+
| id         | INT     |
| department | VARCHAR |
+------------+---------+