SQL (Structured Query Language) 是關係型數據庫的標準交互語言。無論你是開發、運維、分析還是產品經理,掌握基礎 SQL 都是數據驅動決策的必備技能。
-- 基礎查詢
SELECT column1, column2 FROM table_name;
-- 全列查詢 (慎用生產環境)
SELECT * FROM table_name;
-- 條件過濾
SELECT * FROM table_name WHERE column1 = 3;
-- 多條件組合
SELECT * FROM table_name
WHERE (Age < 18 AND LastName BETWEEN 'Anderson' AND 'Miller')
OR Company LIKE '%School%';
-- 顯式列名 (推薦,順序無關、可跳過列)
INSERT INTO table_name (column1, column2, column3)
VALUES ('data1', 'data2', 'data3');
-- 全列按序插入 (不推薦,易因結構變更報錯)
INSERT INTO table_name VALUES ('data1', 'data2', 'data3');
-- 從查詢結果插入
INSERT INTO Archive_Orders (OrderID, CustomerID, OrderDate)
SELECT OrderID, CustomerID, OrderDate FROM Sales.Orders
WHERE OrderDate < '2023-01-01';
-- 基礎更新 (必加 WHERE,否則全表更新)
UPDATE table_name
SET column1 = 'new_value', column2 = 'new_value2'
WHERE column3 = 'filter_value';
-- 修正拼寫錯誤
UPDATE table_name
SET FirstName = 'Shelley'
WHERE FirstName = 'Shelly' AND LastName = 'Doll';
-- 關聯更新 (SQL Server 語法)
UPDATE t1
SET t1.ColumnA = t2.ColumnB
FROM Table1 t1
JOIN Table2 t2 ON t1.ID = t2.RefID
WHERE t2.Status = 'Active';
-- 條件刪除 (必加 WHERE)
DELETE FROM table_name WHERE column1 = 'value';
-- 關聯刪除
DELETE o FROM Sales.Orders o
JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
WHERE c.Country = 'Obsolete';
-- ⚠️ 危險:無 WHERE 刪除全表
-- DELETE FROM table_name; -- 生產環境嚴禁!
-- 軟刪除模式 (推薦)
ALTER TABLE table_name ADD IsDeleted BIT NOT NULL DEFAULT 0;
UPDATE table_name SET IsDeleted = 1 WHERE ...; -- 邏輯刪除
SELECT * FROM table_name WHERE IsDeleted = 0; -- 查詢過濾
| 類別 | 運算符 | 說明 | 示例 |
|---|---|---|---|
| 比較 | = | 等於 | Age = 18 |
| <> / != | 不等於 | Status <> 'Done' | |
| > / >= | 大於/大於等於 | Price >= 100 | |
| < / <= | 小於/小於等於 | Qty < 10 | |
| IS NULL | 空值判斷 | Region IS NULL | |
| IS NOT NULL | 非空判斷 | Email IS NOT NULL | |
| 邏輯/模式 | AND | 邏輯與 | A=1 AND B=2 |
| OR | 邏輯或 | A=1 OR B=2 | |
| NOT | 邏輯非 | NOT (A=1) | |
| LIKE | 模式匹配 | Name LIKE 'J%' | |
| BETWEEN | 範圍包含端點 | Age BETWEEN 18 AND 65 | |
| IN | 集合成員 | Status IN ('A','B') | |
| EXISTS | 子查詢存在行 | EXISTS (SELECT 1 FROM ...) |
| 函數 | 說明 | 忽略 NULL |
|---|---|---|
| COUNT(*) | 行數 | 否 |
| COUNT(col) | 非空值計數 | 是 |
| SUM(col) | 求和 | 是 |
| AVG(col) | 平均值 | 是 |
| MIN/MAX(col) | 最小/最大值 | 是 |
| STDEV/VAR | 標準差/方差 | 是 |
SELECT Department, COUNT(*) AS EmpCount, AVG(Salary) AS AvgSal
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5; -- 分組後過濾 (聚合條件)
SQL 看似簡單,精通需千錘百煉。從基礎四大操作起步,構建條件邏輯思維,逐步掌握高級分析函數,讓數據為業務發聲。