SQL for Beginners: The First 5 Queries You Will Write

SQL 入门：前 5 个查询

SQL — Structured Query Language — is the language of relational databases. Despite being over fifty years old, it remains the most widely used database language in the world, and almost every application you use every day talks to a SQL database somewhere in its stack. The good news is that the first 80 percent of SQL is just a handful of statements, and you can be productive in an afternoon. This article walks through the most common queries, explains the mental model behind them, and flags the pitfalls that bite every beginner once.

SQL（结构化查询语言）是关系型数据库的语言。虽然已有五十多年历史，但它仍是全球使用最广泛的数据库语言，你日常使用的几乎每个应用背后都连着某个 SQL 数据库。好消息是，SQL 前 80% 的内容不过是少数几条语句，一个下午就能上手。本文会带你走过最常见的查询，解释它们背后的思维模型，并指出每个初学者都至少会踩一次的坑。

The relational model in two paragraphs

两段话讲清关系模型

A relational database stores data in tables. Each table is a set of rows and columns: a row is one record (a customer, an order, a sensor reading), and a column is one attribute (name, price, timestamp). Tables can reference each other through keys: a column in one table can hold the unique identifier of a row in another. The relationship between "one customer has many orders" is expressed by storing the customer's ID in each order row.

关系型数据库把数据存在表里。每张表都是行列的集合：一行是一条记录（一个客户、一个订单、一次传感器读数），一列是一个属性（姓名、价格、时间戳）。表之间通过键相互引用：一张表中的某列保存另一张表中某行的唯一标识。"一个客户有多个订单"这种关系，就通过在每个订单行里存上客户的 ID 来表达。

SQL is the language used to read and write these tables. Most operations are queries: you describe what you want ("every order from a customer in Tokyo placed this month"), and the database engine figures out the fastest way to retrieve it. This declarative style is the heart of SQL and what makes it so productive: you rarely need to say how to find the data, just what it should look like when you get it.

SQL 是用来读写这些表的语言。多数操作都是查询：你描述自己想要什么（"本月东京客户下的所有订单"），由数据库引擎自行决定最快的取数方式。这种"声明式"风格正是 SQL 的核心，也是它高效的原因 —— 你几乎不需要告诉数据库"怎么找数据"，只需描述"拿到的数据应该长什么样"。

Query #1: SELECT, FROM, WHERE — the trio that runs the world

查询一：SELECT、FROM、WHERE —— 撑起整个世界

Every read query has the same skeleton. SELECT lists the columns you want. FROM names the table. WHERE filters rows. For example, from a table of products:

每条读查询都有同样的骨架：SELECT 列出想要的列，FROM 指定表，WHERE 过滤行。例如，从一张产品表里查：

SELECT id, name, price
FROM products
WHERE price > 50;

This returns every product priced above 50, with only three columns. The wildcard SELECT * is convenient but a code smell: it returns every column, including ones you do not need, and breaks the moment a column is added or removed. Always name the columns you actually use.

这条查询会返回所有价格高于 50 的产品，并只包含三列。虽然 SELECT * 很方便，但它是个坏味道：会返回所有列，包括你不需要的；而且一旦表结构变化，代码就会跟着崩。请总是显式列出你真正用到的列。

The WHERE clause supports comparison operators (=, <>, <, >, <=, >=), logical operators (AND, OR, NOT), and several useful patterns:

WHERE 子句支持比较运算符（=、<>、<、>、<=、>=）、逻辑运算符（AND、OR、NOT），以及几个常用模式：

• column BETWEEN x AND y — inclusive range.
• column BETWEEN x AND y —— 闭区间范围。
• column IN (1, 2, 3) — match any value in the list.
• column IN (1, 2, 3) —— 匹配列表中任意一个值。
• column LIKE 'A%' — % matches any sequence, _ matches a single character.
• column LIKE 'A%' —— % 匹配任意长度的字符序列，_ 匹配单个字符。
• column IS NULL — test for missing values. Never = NULL; it does not work.
• column IS NULL —— 测试是否为空。绝对不要用 = NULL，那行不通。

Query #2: ORDER BY and LIMIT — sorting and slicing results

查询二：ORDER BY 与 LIMIT —— 排序与切片

Without an ORDER BY, SQL does not promise any particular row order. The order can change between runs, between versions, even between consecutive identical queries. To get stable, predictable results, always sort. ORDER BY takes one or more columns and an optional direction — ASC (default) or DESC:

如果不写 ORDER BY，SQL 不保证行的任何特定顺序。顺序可能因不同执行、不同版本、甚至连续两次相同的查询而变化。要得到稳定可预测的结果，请始终排序。ORDER BY 接受一列或多列，可选方向 —— ASC（默认）或 DESC：

SELECT name, price
FROM products
WHERE category = 'books'
ORDER BY price DESC, name ASC;

When you sort by multiple columns, the first column is the primary sort, and ties are broken by the second. The example above gives the most expensive books first, and among books of the same price, alphabetical order.

按多列排序时，第一列是主排序键，遇到相同值时再按第二列比较。上面这个例子会先按价格降序返回书；同价时按书名升序。

LIMIT caps the number of rows returned. Combined with ORDER BY, it is the canonical way to get "top N" results — the ten newest posts, the five highest-spending customers, the latest 100 log lines. Note that the SQL standard uses FETCH FIRST n ROWS ONLY; LIMIT is a PostgreSQL/MySQL/SQLite extension that is also widely understood. For pagination, LIMIT n OFFSET m skips m rows and returns the next n. Beware: deep offsets on large tables are slow. Keyset pagination (WHERE id > last_seen_id LIMIT n) scales much better.

LIMIT 限制返回的行数。结合 ORDER BY，它是获取"前 N 名"的标准写法 —— 最新发布的 10 篇文章、消费最高的 5 位客户、最近的 100 条日志。SQL 标准写法是 FETCH FIRST n ROWS ONLY，LIMIT 是 PostgreSQL/MySQL/SQLite 的扩展，但也被广泛理解。分页时 LIMIT n OFFSET m 跳过 m 行并取接下来的 n 行。要注意：在大表上做深分页（高 OFFSET）会很慢；键集分页（WHERE id > last_seen_id LIMIT n）可扩展性更好。

Query #3: GROUP BY and aggregate functions

查询三：GROUP BY 与聚合函数

Aggregates collapse many rows into one. The five you will use 90 percent of the time: COUNT(*) counts rows, COUNT(column) counts non-NULL values in that column, SUM(column) adds them up, AVG(column) returns the average, and MAX/MIN find the extremes. GROUP BY tells SQL to compute aggregates per group rather than across the whole table.

聚合函数把多行压缩成一行。90% 的时间你只会用到这五个：COUNT(*) 统计行数；COUNT(column) 统计该列中非 NULL 的行数；SUM(column) 求和；AVG(column) 求平均；MAX/MIN 求最大/最小值。GROUP BY 告诉 SQL 按组计算聚合，而不是整表一起算。

SELECT category, COUNT(*) AS items, AVG(price) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price DESC;

A critical distinction: COUNT(*) counts every row in the group, including those with NULLs. COUNT(price) counts only rows where price is not NULL. If you ever wonder why a count looks too low, this is almost always the reason.

有一个关键区别：COUNT(*) 统计组内的所有行，包括含 NULL 的；COUNT(price) 只统计 price 不为 NULL 的行。如果你发现某条计数看起来比预期少，几乎总是这个原因。

HAVING filters groups after aggregation. WHERE filters rows before aggregation. A common beginner mistake is to put aggregate conditions in WHERE:

HAVING 在聚合之后过滤组；WHERE 在聚合之前过滤行。初学者常犯的一个错是把聚合条件放进 WHERE：

-- WRONG
SELECT category, COUNT(*) FROM products WHERE COUNT(*) > 5 GROUP BY category;

-- RIGHT
SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 5;

A useful idiom is the "filter then count" pattern: compute aggregates for the whole table once, then add HAVING to keep only the groups you care about.

一个常用套路是"先聚合再过滤"：先对全表计算聚合，再用 HAVING 筛选出你关心的组。

Query #4: basic JOINs

查询四：基本 JOIN

A JOIN combines rows from two tables based on a related column. The most common case is the "inner join": return only rows that have a match in both tables. Suppose you have an orders table with a customer_id column and a customers table with a primary-key id column. To list every order with the customer's name:

JOIN 根据相关列把两张表的行组合起来。最常见的就是"内连接"：只保留两张表里都能匹配上的行。假设有一张 orders 表，含 customer_id 列，还有一张 customers 表，主键是 id。要列出每个订单及对应客户名：

SELECT orders.id, customers.name, orders.total
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

A "left join" returns every row from the left table, even if there is no match in the right one; the missing columns come back as NULL. This is the right tool when you want to find rows in one table that are missing a partner in another:

"左连接"会返回左表的全部行，即使在右表里没有匹配；缺失的列以 NULL 填充。它非常适合用来找出"某张表里没有对应记录"的行：

SELECT customers.id, customers.name
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.id IS NULL;

The query above finds every customer who has never placed an order. The LEFT JOIN keeps all customers, and the WHERE orders.id IS NULL filters to the ones with no matching order. This "anti-join" pattern is one of the most useful idioms in SQL.

上面这条查询找出"从未下过单"的客户。LEFT JOIN 保留所有客户，WHERE orders.id IS NULL 把没有匹配订单的客户筛出来。这种"反连接"模式是 SQL 中最实用的套路之一。

Always qualify column names with their table in joins: orders.id rather than bare id. When tables have long names, use aliases: FROM orders o INNER JOIN customers c ON o.customer_id = c.id. It makes the query far easier to read.

在 JOIN 查询中务必给列加上表名限定：orders.id 而不是裸写 id。表名太长时可以用别名：FROM orders o INNER JOIN customers c ON o.customer_id = c.id。这样查询可读性会好得多。

Query #5: INSERT, UPDATE, DELETE — changing data

查询五：INSERT、UPDATE、DELETE —— 修改数据

Insert one row:

插入一行：

INSERT INTO products (name, price, category)
VALUES ('Espresso machine', 549.00, 'appliances');

Insert many rows from a query: use INSERT INTO ... SELECT .... This is the fastest way to bulk-load data. Update rows that match a condition — and always specify a WHERE clause:

从查询结果批量插入：使用 INSERT INTO ... SELECT ...。这是批量加载数据最快的方式。更新匹配条件的行 —— 并且务必加上 WHERE 子句：

UPDATE products
SET price = price * 0.9
WHERE category = 'books' AND published_year < 2020;

The query above gives a 10% discount to all old books. The same rule applies to DELETE: never run a bare DELETE FROM products in production. Always scope it to a specific WHERE and test the SELECT version of the same WHERE first.

上面这条查询给所有旧书打 9 折。DELETE 也是同样原则：永远不要在生产环境里直接跑 DELETE FROM products。务必限定 WHERE，并先用同样 WHERE 跑一遍 SELECT 来验证。

DELETE FROM products
WHERE discontinued = TRUE AND last_sold < '2024-01-01';

Indexes: the secret to fast queries

索引：查询速度的秘密

A table without an index forces the database to scan every row to find matches. On a million-row table, that is a million comparisons per query. An index is a separate data structure (usually a B-tree) that the database can use to look up rows by column value in roughly logarithmic time. The cost is extra storage and slower writes, since every index has to be updated when rows change.

没有索引的表，每次查询都要扫描所有行才能找到匹配。百万行的表，就意味着每次查询要比较一百万次。索引是一种独立的数据结构（通常是 B 树），让数据库按列值以对数时间查行。代价是占用更多存储空间，且每次写入都要同步更新索引。

Practical advice: index the columns that appear in your WHERE, JOIN, and ORDER BY clauses — especially the foreign-key columns used in joins. A table with no indexes on join columns will grind to a halt once it grows beyond a few thousand rows. Composite indexes (multi-column) work left-to-right: an index on (category, price) helps queries that filter on category alone, or on both, but not on price alone.

实践建议：给 WHERE、JOIN、ORDER BY 里出现的列建立索引 —— 尤其是 JOIN 中用到的外键列。一张表如果在 JOIN 列上没索引，几千行之后就会开始卡顿。复合索引（多列）按从左到右的顺序生效：建在 (category, price) 上的索引，能加速只按 category 过滤或同时按两者过滤的查询，但不能加速只按 price 过滤的查询。

SQL injection: still the #1 web vulnerability

SQL 注入：至今仍是头号 Web 漏洞

SQL injection happens when user input is concatenated into a query string, allowing an attacker to change the meaning of the SQL. The classic example: a login form that builds SELECT * FROM users WHERE name = '" + name + "' — if the attacker enters ' OR '1'='1 as their name, the query becomes WHERE name = '' OR '1'='1' and returns every user. Defenses are simple and non-negotiable:

SQL 注入发生在用户输入被直接拼接到 SQL 字符串中，使攻击者可以改变 SQL 的语义。经典例子：一个登录表单用 SELECT * FROM users WHERE name = '" + name + "' 拼接查询。如果攻击者在姓名输入框写 ' OR '1'='1，查询就变成 WHERE name = '' OR '1'='1'，返回所有用户。防御方法简单且不容商量：

• Use parameterized queries (also called prepared statements). The database treats input as data, not as SQL.
• 使用参数化查询（也叫预编译语句）。数据库会把输入当作数据而不是 SQL。
• If you must build dynamic SQL (for example, dynamic ORDER BY), never interpolate user input directly; map it to a fixed allowlist of values.
• 如果必须动态拼 SQL（比如动态 ORDER BY），千万不要把用户输入直接拼进去；用一张固定值的"白名单"做映射。
• Run database users with the minimum privileges they need; the application should not own the schema.
• 数据库用户按"最小权限"原则配置；应用账户不应拥有 schema。

Three quick pitfalls to remember

三个值得记住的坑

First, SELECT *: convenient, but a code smell. It pulls unnecessary data, breaks when columns are renamed, and makes query plans unpredictable. Second, COUNT(*) versus COUNT(column): the former counts rows, the latter counts non-NULL values in that column. Third, NULL: it is not zero, not empty string, not false. SQL uses three-valued logic (TRUE, FALSE, UNKNOWN); a comparison with NULL yields UNKNOWN, which is treated as false in WHERE. NOT (column = 1) does not return rows where column = 1 is false OR unknown — it returns only the rows where the comparison is false. WHERE column IS NULL is the only reliable way to find missing values.

第一，SELECT *：写起来方便，但味道很坏。它会拉取不需要的列；列被重命名时代码会崩；查询计划也不可预测。第二，COUNT(*) vs COUNT(column)：前者统计行数，后者统计该列中非 NULL 的行数。第三，NULL：它不是 0、不是空字符串、也不是 FALSE。SQL 使用三值逻辑（TRUE、FALSE、UNKNOWN），与 NULL 比较的结果是 UNKNOWN，在 WHERE 中被视作 false。NOT (column = 1) 并不会返回所有 "column = 1" 为 false 或 unknown 的行 —— 它只返回比较结果为 false 的行。要查找缺失值，唯一可靠的方式是 WHERE column IS NULL。

Try the tools

试试这些工具

Practice formatting SQL-adjacent data with the JSON formatter. SQL results often come out of APIs in JSON form; a quick formatter turns a wall of text into something you can actually read.

用 JSON 格式化工具 练习整理 SQL 相关数据。SQL 查询结果经常以 JSON 形式通过 API 返回，格式化器能把一坨文字变成你真正能读的样子。