SQL 小抄表（带中文说明 + 示例）

1. 基础命令

• SELECT：选择要查询的列

  SELECT name, salary FROM employees;
  

• WHERE：筛选符合条件的记录

  SELECT * FROM employees WHERE age > 30;
  

• FROM：指定数据表

  SELECT * FROM employees FROM employees;
  

• AS (别名)：给列或表取临时名字

  SELECT name AS employee_name FROM employees;
  

• DISTINCT：去除重复值

  SELECT DISTINCT age FROM employees;
  

2. 数据聚合

• COUNT：统计行数

  SELECT COUNT(*) FROM employees;
  

• SUM：求和

  SELECT SUM(salary) FROM employees;
  

• AVG：平均值

  SELECT AVG(salary) FROM employees;
  

• MIN / MAX：最小值、最大值

  SELECT MIN(age), MAX(age) FROM employees;
  

• GROUP BY：分组聚合

  SELECT age, COUNT(*) FROM employees GROUP BY age;
  

• HAVING：对分组结果进行过滤

  SELECT age, COUNT(*) FROM employees GROUP BY age HAVING COUNT(*) > 2;
  

3. 数据修改

• INSERT：插入数据

  INSERT INTO employees (name, age, salary) VALUES ('Tom', 28, 5000);
  

• UPDATE：更新数据

  UPDATE employees SET salary = 6000 WHERE name = 'Tom';
  

• DELETE：删除数据

  DELETE FROM employees WHERE age < 25;
  

• TRUNCATE：清空表数据（不可回滚）

  TRUNCATE TABLE employees;
  

4. 数据过滤

• BETWEEN：范围筛选

  SELECT * FROM employees WHERE age BETWEEN 25 AND 35;
  

• LIKE：模糊匹配

  SELECT * FROM employees WHERE name LIKE 'A%';
  

• IN：多值匹配

  SELECT * FROM employees WHERE age IN (25, 30, 35);
  

• AND / OR / NOT：逻辑条件

  SELECT * FROM employees WHERE age > 30 AND salary > 5000;
  

5. 排序与限制

• ORDER BY：排序

  SELECT * FROM employees ORDER BY salary DESC;
  

• LIMIT：限制返回行数

  SELECT * FROM employees LIMIT 5;
  

• OFFSET：跳过前几行

  SELECT * FROM employees LIMIT 5 OFFSET 10;
  

6. 集合操作

• UNION：合并去重

  SELECT name FROM employees WHERE age < 30
  UNION
  SELECT name FROM employees WHERE salary > 7000;
  

• UNION ALL：合并不去重

  SELECT name FROM employees WHERE age < 30
  UNION ALL
  SELECT name FROM employees WHERE salary > 7000;
  

• INTERSECT：取交集

  SELECT name FROM employees WHERE age < 30
  INTERSECT
  SELECT name FROM employees WHERE salary > 7000;
  

• EXCEPT / MINUS：取差集

  SELECT name FROM employees WHERE age < 30
  EXCEPT
  SELECT name FROM employees WHERE salary > 7000;
  

7. 表连接

• INNER JOIN：取两表交集部分

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

• LEFT JOIN：保留左表所有数据

  SELECT e.name, d.department_name
  FROM employees e
  LEFT JOIN departments d ON e.dept_id = d.id;
  

• RIGHT JOIN：保留右表所有数据

  SELECT e.name, d.department_name
  FROM employees e
  RIGHT JOIN departments d ON e.dept_id = d.id;
  

• FULL JOIN：并集，左右表都保留

  SELECT e.name, d.department_name
  FROM employees e
  FULL JOIN departments d ON e.dept_id = d.id;
  

8. 索引

• CREATE INDEX：创建索引，加快查询

  CREATE INDEX idx_age ON employees(age);
  

• DROP INDEX：删除索引

  DROP INDEX idx_age;
  

• Unique Index：唯一索引（不允许重复值）

  CREATE UNIQUE INDEX idx_name ON employees(name);
  

9. 转换函数

• CAST：类型转换

  SELECT CAST(salary AS CHAR) FROM employees;
  

• COALESCE：返回第一个非空值

  SELECT COALESCE(middle_name, 'N/A') FROM employees;
  

10. 字符串函数

• CONCAT：拼接字符串

  SELECT CONCAT(name, ' - ', age) FROM employees;
  

• SUBSTRING：截取子串

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

• UPPER / LOWER：大小写转换

  SELECT UPPER(name) FROM employees;
  

• TRIM：去除空格

  SELECT TRIM('   Tom   ');
  

• REPLACE：替换字符

  SELECT REPLACE(name, 'a', 'A') FROM employees;
  

11. 空值处理

• ISNULL / IFNULL / NVL：替换空值

  SELECT ISNULL(middle_name, 'None') FROM employees;
  

12. 条件函数

• CASE：条件判断

  SELECT name,
         CASE WHEN salary > 7000 THEN 'High'
              ELSE 'Normal'
         END AS salary_level
  FROM employees;
  

• IIF：简写条件

  SELECT IIF(salary > 7000, 'High', 'Normal') FROM employees;
  

13. 日期与时间函数

• CURRENT_DATE：当前日期

  SELECT CURRENT_DATE;
  

• NOW / SYSDATE：当前时间戳

  SELECT NOW();
  

• DATEADD / ADD_MONTHS：增加时间

  SELECT DATEADD(MONTH, 3, hire_date) FROM employees;
  

• DATEDIFF：计算日期差

  SELECT DATEDIFF(CURRENT_DATE, hire_date) FROM employees;
  

• EXTRACT：提取年/月/日

  SELECT EXTRACT(YEAR FROM hire_date) FROM employees;
  

14. 子查询

• EXISTS：判断子查询结果是否存在

  SELECT * FROM employees e
  WHERE EXISTS (SELECT 1 FROM departments d WHERE e.dept_id = d.id);
  

• 标量子查询：返回单个值

  SELECT name, (SELECT AVG(salary) FROM employees) AS avg_salary FROM employees;
  

15. 窗口函数

• ROW_NUMBER：行号（连续编号）

  SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
  FROM employees;
  

• RANK：排名（跳号）

  SELECT name, RANK() OVER (ORDER BY salary DESC) AS rank_num
  FROM employees;
  

• DENSE_RANK：排名（不跳号）

  SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_num
  FROM employees;
  

• NTILE：分桶

  SELECT name, NTILE(4) OVER (ORDER BY salary DESC) AS quartile
  FROM employees;
  

✅ 总结：
这份小抄表涵盖了 SQL 查询、过滤、聚合、修改、索引、函数、子查询、窗口函数 等常用操作，每个功能都带有一句中文说明 + 一个最小示例，拿来就能用。