SQL 查询语法笔记
整理一下关系型数据库中常用的 SELECT 查询语法,以便日常需要时快速翻看。例子都用一个虚构的学生表 students。
基本查询
SELECT * FROM students;
SELECT id, name, age FROM students;
SELECT name AS student_name FROM students;条件筛选(WHERE)
SELECT * FROM students WHERE age > 18;
SELECT * FROM students WHERE name = '小明';
SELECT * FROM students WHERE age BETWEEN 18 AND 22;
SELECT * FROM students WHERE name LIKE '王%';
SELECT * FROM students WHERE city IN ('北京', '上海');
SELECT * FROM students WHERE age IS NULL;注意 NULL 不能用 = NULL 判断,必须用 IS NULL。
排序(ORDER BY)
SELECT * FROM students ORDER BY age; -- 升序(默认)
SELECT * FROM students ORDER BY age DESC; -- 降序
SELECT * FROM students ORDER BY age DESC, name ASC; -- 多字段限制条数(LIMIT)
SELECT * FROM students LIMIT 10; -- 前 10 条
SELECT * FROM students LIMIT 10 OFFSET 20;-- 跳过 20 条取 10 条聚合与分组
SELECT COUNT(*) FROM students;
SELECT AVG(age), MAX(age), MIN(age) FROM students;
SELECT city, COUNT(*) FROM students GROUP BY city;
SELECT city, COUNT(*) AS n FROM students
GROUP BY city
HAVING n > 5;WHERE 在分组前过滤,HAVING 在分组后过滤。这是初学时最容易搞混的点。
联接(JOIN)
假设有两张表:students(学生) 和 scores(成绩),通过 student_id 关联。
-- 内连接(只保留两边都有的)
SELECT s.name, sc.subject, sc.score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id;
-- 左连接(保留左表全部,右表没匹配的为 NULL)
SELECT s.name, sc.score
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id;去重
SELECT DISTINCT city FROM students;子查询
-- 查比平均年龄大的学生
SELECT * FROM students
WHERE age > (SELECT AVG(age) FROM students);
-- 查在 scores 表里有记录的学生
SELECT * FROM students
WHERE id IN (SELECT student_id FROM scores);常用约定
- SQL 关键字习惯大写,字段和表名小写,提高可读性。
- 每条 SQL 末尾加分号;多条语句拼一起时是必须的。
- 注释:
-- 单行或/* 多行 */。 - 不同数据库(MySQL / PostgreSQL / SQLite)在分页、字符串拼接、日期函数等细节上有差异,具体场景查对应文档。
小结
SELECT 的执行顺序大致是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。理解这个顺序对写复杂查询很有帮助,尤其是当出现"无法在 WHERE 里用别名"这类错误时,记住这个顺序就能避免。