★ FEATURED ARTICLE SQL 最适合边查边学。本文用两张小表串起常见操作示例以 SQLite 为执行环境其他数据库在分页、标识符引用和事务细节上可能不同。字符串统一使用英文单引号逗号和分号使用半角字符。SELECT、CREATE TABLE、UPDATE是语句AVG()、COUNT()才是函数。1. 先准备两张表请在练习数据库中执行数据均为虚构。CREATE TABLE clubs ( club_id INTEGER PRIMARY KEY, club_name TEXT NOT NULL ); CREATE TABLE students ( student_id INTEGER PRIMARY KEY, name TEXT NOT NULL, class_name TEXT NOT NULL, score REAL, club_id INTEGER ); INSERT INTO clubs (club_id, club_name) VALUES (101, 吉他社), (102, 篮球社), (103, 舞蹈社); INSERT INTO students (student_id, name, class_name, score, club_id) VALUES (1, 张明, 一班, 88, 101), (2, 李华, 一班, 92, 102), (3, 张小雨, 二班, 79, NULL), (4, 陈晨, 二班, NULL, 101), (5, 王宁, 二班, 85, 103);NULL表示缺失或未知在这里代表成绩尚未录入或未填社团。主键标识每条记录不应使用可能重名的姓名代替学生编号。2. SELECT、WHERE 与排序SELECT name, class_name, score FROM students WHERE class_name 一班 ORDER BY score DESC, student_id ASC;WHERE筛选行ORDER BY决定结果顺序。上例先按成绩降序再按编号升序处理并列。没有ORDER BY不要依赖返回顺序。ASC、DESC只作用于各自前面的排序表达式。ORDER BY class_name, score DESC表示班级升序、成绩降序。3. LIMIT 与 OFFSETSELECT student_id, name, score FROM students WHERE score IS NOT NULL ORDER BY score DESC, student_id ASC LIMIT 2 OFFSET 1;这会跳过排序后的第一行再取两行。分页要有稳定排序否则容易重复或漏行。大表的深分页还可能产生性能问题应结合索引与实际查询计划评估。4. 条件组合与 LIKESELECT name, score FROM students WHERE score BETWEEN 80 AND 90 AND (class_name 一班 OR class_name 二班) ORDER BY student_id; SELECT name FROM students WHERE name LIKE 张% ORDER BY student_id; SELECT name FROM students WHERE name LIKE 张_ ORDER BY student_id;BETWEEN包含两端。%匹配零个或多个字符_匹配单个字符因此第二个姓名查询只返回“张明”不会返回“张小雨”。LIKE用于模式匹配不负责排序。多个条件组合时用括号明确含义。检查空值要用IS NULL或IS NOT NULL不能使用 NULL。score 80也不会自动把缺失成绩包含进来。5. 聚合COUNT 的分母是什么SELECT COUNT(*) AS student_count, COUNT(score) AS known_score_count, COUNT(DISTINCT club_id) AS distinct_clubs, AVG(score) AS mean_score, MIN(score) AS min_score, MAX(score) AS max_score FROM students;本例有 5 行、4 个非空成绩、3 个不同的非空社团编号平均成绩为 86。AVG(score)忽略空值不能把它解释成所有学生都已参加考试后的平均分。6. GROUP BY 与 HAVINGSELECT class_name, COUNT(*) AS student_count, COUNT(score) AS known_score_count, ROUND(AVG(score), 2) AS mean_score FROM students GROUP BY class_name HAVING AVG(score) 80 ORDER BY AVG(score) DESC, class_name ASC;WHERE在分组前筛选行HAVING在分组后筛选组。两者位置不同结果含义也不同。这里在HAVING中重复聚合表达式避免依赖某些数据库允许使用 SELECT 别名的扩展。7. INNER JOIN 与 LEFT JOINSELECT s.student_id, s.name, c.club_name FROM students AS s INNER JOIN clubs AS c ON s.club_id c.club_id ORDER BY s.student_id; SELECT s.student_id, s.name, c.club_name FROM students AS s LEFT JOIN clubs AS c ON s.club_id c.club_id ORDER BY s.student_id;内连接返回 4 行左连接保留全部 5 位学生未填社团者的社团名称为空。JOIN 按键关联数据不是简单把两张表上下拼接。如果关联键在另一张表出现多次一条学生记录可能被扩展成多行。做汇总前先检查关联关系避免重复计数。8. 更新先定位再验收SELECT * FROM students WHERE student_id 4; BEGIN TRANSACTION; UPDATE students SET score 82 WHERE student_id 4; SELECT student_id, name, score FROM students WHERE student_id 4; ROLLBACK;这个练习在查看修改结果后回滚成绩恢复为空。实际需要保留修改时在核实后使用COMMIT。数据库和操作类型对事务的支持不同不要据此假定所有操作都可回滚。DELETE FROM students WHERE ...删除符合条件的行DROP TABLE students删除表本身。练习前先明确操作对象缺少 WHERE 的 UPDATE 或 DELETE 会影响整张表。9. Python 中使用参数绑定import sqlite3 def find_class(conn, class_name): return conn.execute( SELECT name, score FROM students WHERE class_name ? ORDER BY student_id, (class_name,), ).fetchall()不要把用户输入拼进 SQL 字符串。占位符绑定适用于值表名、列名等标识符通常需要程序白名单选择。其他驱动的占位符形式可能不同应查看相应文档。继续练习时可以尝试统计“每个社团的已知成绩均值”并解释未入社团与缺失成绩怎样影响结果。能解释查询的分母与关联关系才算理解了这些语句。参考SQLite SELECT、Python sqlite3。
阅读完成 · 觉得有帮助？
)
