MySQL SELECT 查詢指定列數據完整指南

基本語法

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column [ASC|DESC]]
[LIMIT offset, count];

核心用法示例

1. 查詢指定列

-- 查詢 test 表的 t_name 和 t_birth 列
SELECT t_name, t_birth FROM test;

2. 條件過濾指定列

-- 查詢 t_name='name2' 的記錄的指定列
SELECT t_name, t_birth FROM test WHERE t_name = 'name2';

-- 結果示例
+-------+------------+
| t_name | t_birth    |
+-------+------------+
| name2  | 2013-01-01 |
+-------+------------+

3. 列別名與表達式

SELECT 
    t_name AS username,
    t_birth AS birth_date,
    YEAR(t_birth) AS birth_year,
    CONCAT('User: ', t_name) AS description
FROM test
WHERE t_name LIKE 'name%';

4. 去重查詢

SELECT DISTINCT department FROM employees;

5. 排序與分頁

SELECT id, name, score 
FROM students 
WHERE class = 'A' 
ORDER BY score DESC, id ASC 
LIMIT 10 OFFSET 0;  -- 第1頁，每頁10條

進階技巧

條件列輸出 (CASE WHEN)

SELECT 
    name,
    score,
    CASE 
        WHEN score >= 90 THEN '優秀'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade_level
FROM exam_results;

聚合統計

SELECT 
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

性能優化建議

• 避免 SELECT *：明確列出需要的列，減少網絡傳輸、利用覆蓋索引
• WHERE 列加索引：高頻查詢條件列建立索引
• LIMIT 分頁優化：大偏移量改用游標分頁 WHERE id > last_id LIMIT 10
• 類型匹配：WHERE 條件值類型與列類型一致，隱式轉換導致索引失效

總結

SELECT 查詢指定列是 SQL 最基礎也最核心的操作。掌握 列選擇、別名、條件過濾、排序分頁、聚合統計 等組合技巧，能高效解決 90% 的業務查詢需求。記住核心原則：只取所需、善用索引、合理分頁。