SQL 語言核心分類

MySQL 的 SQL 指令主要分為三大類，掌握這些分類有助於理解各語句的用途：

• DDL（資料定義語言）：CREATE、ALTER、DROP、DECLARE — 用於定義資料庫結構。
• DML（資料操作語言）：SELECT、DELETE、UPDATE、INSERT — 用於操作資料內容。
• DCL（資料控制語言）：GRANT、REVOKE、COMMIT、ROLLBACK — 用於權限控制與事務管理。

基礎資料庫與資料表操作

建立與刪除資料庫

CREATE DATABASE database_name;
DROP DATABASE dbname;

備份 SQL Server 資料庫

USE master;
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7\backup\MyNwind_1.dat';
BACKUP DATABASE pubs TO testBack;

建立資料表

CREATE TABLE tabname (
  col1 type1 [NOT NULL] [PRIMARY KEY],
  col2 type2 [NOT NULL], ...
);

根據現有資料表建立新表：

-- 方法 A：複製結構
CREATE TABLE tab_new LIKE tab_old;

-- 方法 B：複製結構與部分資料
CREATE TABLE tab_new AS SELECT col1, col2 FROM tab_old DEFINITION ONLY;

刪除資料表

DROP TABLE tabname;

修改資料表結構

• 新增欄位：ALTER TABLE tabname ADD COLUMN col type;（注意：新增後無法刪除，DB2 中資料型態也無法變更，僅能增加 VARCHAR 長度）
• 主鍵管理：

  ALTER TABLE tabname ADD PRIMARY KEY (col);
  ALTER TABLE tabname DROP PRIMARY KEY (col);

• 索引管理：

  CREATE [UNIQUE] INDEX idxname ON tabname (col...);
  DROP INDEX idxname;

  索引不可修改，需刪除後重建。

• 視圖管理：

  CREATE VIEW viewname AS SELECT statement;
  DROP VIEW viewname;

核心 DML 基礎語句

操作	語法範例
查詢	SELECT * FROM table1 WHERE 範圍
新增	INSERT INTO table1 (field1, field2) VALUES (value1, value2)
刪除	DELETE FROM table1 WHERE 範圍
更新	UPDATE table1 SET field1 = value1 WHERE 範圍
模糊查詢	SELECT * FROM table1 WHERE field1 LIKE '%value1%'
排序	SELECT * FROM table1 ORDER BY field1, field2 [DESC]
統計函數	COUNT(*)、SUM(field1)、AVG(field1)、MAX(field1)、MIN(field1)

進階查詢運算子

集合運算

• UNION：合併兩結果集並去除重複行；UNION ALL 保留重複行。
• EXCEPT：回傳僅存在於第一個集合的行；EXCEPT ALL 保留重複行。
• INTERSECT：回傳兩集合皆存在的行；INTERSECT ALL 保留重複行。

使用運算子時，查詢結果的欄位數量與型態必須一致。

外部連接查詢

• LEFT OUTER JOIN：包含左表所有行及右表匹配行。

  SELECT a.a, a.b, a.c, b.c, b.d, b.f
  FROM a LEFT OUTER JOIN b ON a.a = b.c;

• RIGHT OUTER JOIN：包含右表所有行及左表匹配行。
• FULL OUTER JOIN：包含兩表所有行。

實用進階 SQL 技巧

資料表複製與跨庫操作

-- 僅複製結構
SELECT * INTO b FROM a WHERE 1<>1;
SELECT TOP 0 * INTO b FROM a;

-- 複製資料
INSERT INTO b (a, b, c) SELECT d, e, f FROM a;

-- 跨資料庫複製
INSERT INTO b (a, b, c) SELECT d, e, f FROM b IN '具體資料庫' WHERE 條件;

子查詢與相關查詢

SELECT a, b, c FROM a WHERE a IN (SELECT d FROM b);
SELECT a.title, a.username, b.adddate
FROM table a,
     (SELECT MAX(adddate) adddate FROM table WHERE table.title = a.title) b;

分頁查詢優化

-- SQL Server 風格分頁
SELECT TOP 10 b.*
FROM (
  SELECT TOP 20 主鍵欄位, 排序欄位 FROM 表名 ORDER BY 排序欄位 DESC
) a, 表名 b
WHERE b.主鍵欄位 = a.主鍵欄位
ORDER BY a.排序欄位;

其他常用技巧

• 隨機取樣：SELECT TOP 10 * FROM tablename ORDER BY NEWID() (SQL Server) / SELECT * FROM 表名 ORDER BY RAND() LIMIT n (MySQL)
• 刪除重複記錄：DELETE FROM tablename WHERE id NOT IN (SELECT MAX(id) FROM tablename GROUP BY col1, col2,...)
• 列出所有資料表：SELECT name FROM sysobjects WHERE type = 'U'
• 列出表欄位：SELECT name FROM syscolumns WHERE id = OBJECT_ID('TableName')
• 條件匯總：使用 CASE WHEN 實現多重條件統計。
• 截斷長字串顯示：

  -- SQL Server
  SELECT CASE WHEN LEN(field) > 10 THEN LEFT(field, 10) + '...' ELSE field END AS news_name
  FROM tablename;
  
  -- Access
  SELECT IIF(LEN(field) > 2, LEFT(field, 2) + '...', field) FROM tablename;

ADO 執行與事務處理

Execute 方法

• 查詢語句：Set 物件變數 = 連線物件.Execute("SQL 查詢語句") — 回傳 Recordset。
• 動作語句：連線物件.Execute "SQL 動作語句" [, RecordsAffected] [, Option] — 無 Recordset 回傳，可取得影響筆數。

事務控制

BeginTrans   -- 開始事務
CommitTrans  -- 提交事務
RollbackTrans -- 回滾事務

事務將一組操作視為整體，僅當全部成功才提交；若有失敗則回滾至原狀態。可透過連線物件的 Errors 集合判斷成敗。