SQL 入门

SQL（结构化查询语言）用于操作关系型数据库。无论使用 SQLite、PostgreSQL、MySQL 还是 SQL Server，大多数基本命令都相同。本文使用不需要服务器的 SQLite，完成创建表、添加数据、查询、连接表、分组以及修改数据的操作。

本文所有命令都使用 SQLite 3.50 运行，展示的结果为真实输出。

需要准备的东西

SQLite（用 `sqlite3 --version` 检查），或免费的 DB Browser for SQLite

表之间的关系

示意图：students、classes 和 scores 三张表通过键相互关联

创建表并添加数据

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  class_id INTEGER
);
CREATE TABLE classes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);
CREATE TABLE scores (
  student_id INTEGER REFERENCES students(id),
  subject TEXT,
  score INTEGER
);
INSERT INTO classes VALUES (1, 'ม.4/1'), (2, 'ม.4/2');
INSERT INTO students VALUES (1, 'สมชาย', 1), (2, 'สมหญิง', 1), (3, 'สมศักดิ์', 2);
INSERT INTO scores VALUES (1, 'คณิต', 85), (1, 'อังกฤษ', 70), (2, 'คณิต', 72), (3, 'คณิต', 45), (3, 'อังกฤษ', 60);

`PRIMARY KEY` 为每一行提供唯一的键。
`NOT NULL` 表示该值必须存在。
`REFERENCES` 连接表，因此 `scores.student_id` 必须在 `students` 中存在。

使用 SELECT 和 WHERE 查询

SELECT name FROM students WHERE class_id = 1;

使用 JOIN 连接表

SELECT s.name, c.title FROM students s JOIN classes c ON c.id = s.class_id ORDER BY s.name;

`JOIN` 把两张表中键相符的行组合在一起。`s` 和 `c` 是表的简写。

使用 GROUP BY 分组

SELECT subject, AVG(score) AS avg_score, COUNT(*) AS n FROM scores GROUP BY subject ORDER BY subject;

`AVG`、`COUNT`、`SUM` 等聚合函数会作用于每个分组。分组之后要筛选，使用 `HAVING`：

SELECT student_id, SUM(score) AS total FROM scores GROUP BY student_id HAVING SUM(score) >= 100;

修改与删除数据

UPDATE scores SET score = score + 5 WHERE student_id = 3 AND subject = 'คณิต';
DELETE FROM scores WHERE score < 50;
SELECT student_id, subject, score FROM scores ORDER BY student_id, subject;

`UPDATE` 把 45 改为 50，因此随后的 `DELETE` 不会删除任何行。注意语句按书写顺序执行。

真实结果

name
------
สมชาย
สมหญิง

name      title
--------  -----
สมชาย     ม.4/1
สมศักดิ์     ม.4/2
สมหญิง     ม.4/1

subject  avg_score         n
-------  ----------------  -
คณิต      67.3333333333333  3
อังกฤษ    65.0              2

student_id  total
----------  -----
1           155
3           105

student_id  subject  score
----------  -------  -----
1           คณิต      85
1           อังกฤษ    70
2           คณิต      72
3           คณิต      50
3           อังกฤษ    60

常见错误

**在 `UPDATE` 或 `DELETE` 中忘记 `WHERE`：** 会修改或删除所有行。先用相同条件执行 `SELECT` 检查。
**把聚合结果的条件写在 `WHERE` 中：** 对总和进行的条件应使用 `HAVING`。
**文本值使用双引号：** 标准 SQL 使用单引号 `'...'` 表示文本。
**以为默认会按顺序返回：** 不指定 `ORDER BY` 时，行的顺序不能保证。

下一步

设计自己的表，例如商品和订单，并编写 `JOIN` 计算每种商品的销售总额。然后学习索引和事务，它们在数据增多后非常重要。

总结

SQL 用 `CREATE` 定义结构，用 `INSERT`、`UPDATE` 和 `DELETE` 管理数据，并用 `SELECT` 配合 `WHERE`、`JOIN`、`GROUP BY` 和 `HAVING` 进行查询。在小型样本数据上测试，可以清楚地看到每条语句的效果。