SQL 查询语句之select语句举例

学生表（students） 和 成绩表（scores） 为例，介绍常用的 SELECT 语句写法：

表结构示例

基础查询

-- 1. 查询所有列
SELECT * FROM students;

-- 2. 查询指定列
SELECT name, age FROM students;

-- 3. 去重查询
SELECT DISTINCT class_id FROM students;

-- 4. 别名
SELECT name AS 姓名, age AS 年龄 FROM students;

条件查询（WHERE）

-- 5. 简单条件
SELECT * FROM students WHERE age >= 18;

-- 6. 多条件（AND/OR）
SELECT * FROM students 
WHERE age > 17 AND gender = '男';

-- 7. IN 查询
SELECT * FROM students 
WHERE class_id IN (101, 102);

-- 8. 模糊查询（LIKE）
SELECT * FROM students 
WHERE name LIKE '张%';  -- 姓张的学生

排序（ORDER BY）

-- 9. 单字段排序
SELECT * FROM students 
ORDER BY age DESC;  -- 按年龄降序

-- 10. 多字段排序
SELECT * FROM students 
ORDER BY class_id ASC, age DESC;

聚合函数

-- 11. 统计总数
SELECT COUNT(*) FROM students;

-- 12. 平均值/最大值/最小值/总和
SELECT 
    AVG(score) AS 平均分,
    MAX(score) AS 最高分,
    MIN(score) AS 最低分,
    SUM(score) AS 总分
FROM scores;

-- 13. 分组统计
SELECT class_id, COUNT(*) AS 人数
FROM students 
GROUP BY class_id;

-- 14. 分组后筛选（HAVING）
SELECT class_id, COUNT(*) AS 人数
FROM students 
GROUP BY class_id
HAVING COUNT(*) > 1;  -- 人数大于1的班级

连接查询（JOIN）

-- 15. 内连接
SELECT s.name, sc.subject, sc.score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id;

-- 16. 左连接
SELECT s.name, sc.subject, sc.score
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id;

-- 17. 多表连接
SELECT s.name, sc.subject, sc.score
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
WHERE sc.score IS NOT NULL;

子查询

-- 18. 子查询作为条件
SELECT * FROM students 
WHERE class_id = (
    SELECT class_id FROM students WHERE name = '张三'
);

-- 19. IN 子查询
SELECT * FROM students 
WHERE id IN (
    SELECT student_id FROM scores WHERE score > 85
);

分页查询

-- 20. MySQL 分页
SELECT * FROM students 
LIMIT 2 OFFSET 0;  -- 第1页，每页2条

-- 21. SQL Server 分页
SELECT * FROM students 
ORDER BY id
OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;

组合查询

-- 22. 合并结果（UNION）
SELECT name FROM students WHERE gender = '男'
UNION
SELECT name FROM students WHERE age > 18;

-- 23. 完整示例：查询每个班级的平均分
SELECT 
    s.class_id,
    AVG(sc.score) AS 班级平均分,
    COUNT(DISTINCT s.id) AS 参考人数
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
GROUP BY s.class_id
HAVING AVG(sc.score) > 80
ORDER BY 班级平均分 DESC;

实用技巧

-- 24. CASE 条件判断
SELECT 
    name,
    CASE 
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        ELSE '及格'
    END AS 等级
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id;

-- 25. 时间函数（假设有日期字段）
SELECT 
    name,
    YEAR(birth_date) AS 出生年份,
    DATEDIFF(CURDATE(), birth_date) / 365 AS 年龄
FROM students;

练习建议

需要更具体的查询示例或解释某个语法细节，可以告诉我具体场景！