SELECT 查询
Master SELECT 语句: 选择列, limiting 行, 排序, and aliases.
你将学到
- 如何 选择特定列 相反的 SELECT *
- Using LIMIT 到 restrict 数字的 行 returned
- 排序 results 使用 ORDER BY (ASC and DESC)
- Renaming 列 in 输出使用 AS aliases
- 移除 duplicates 使用 DISTINCT
概念讲解
SELECT 语句
SELECT is the most-used SQL statement. Its basic form is SELECT 列 FROM 表. You can list specific columns (SELECT name, email FROM 用户) or use * to get all columns. In production code, prefer listing columns explicitly — SELECT * is fine for exploration but returns unnecessary data and breaks if the schema changes.
LIMIT
Large tables can have millions of rows. LIMIT restricts how many rows the query returns. This is essential for performance and for pagination — page 1 shows rows 1–20, page 2 shows 21–40, and so on. In PostgreSQL and MySQL the syntax is LIMIT n; in SQL Server it is TOP n; in Oracle it is FETCH FIRST n ROWS ONLY.
ORDER BY
ORDER BY sorts the result set. You can sort by one or more columns, ascending (ASC, the default) or descending (DESC). Sorting is applied after filtering but before LIMIT. You can sort by a column that is not in the SELECT list.
Aliases with AS
AS renames a column or table in the output. This makes results more readable and is required when selecting expressions. For example, SELECT price * 1.2 AS price_with_tax FROM products. The AS keyword is optional — price * 1.2 price_with_tax works too, but AS is clearer.
DISTINCT
DISTINCT removes duplicate rows from the result. SELECT DISTINCT 分类 FROM products returns each category only once. Use it sparingly — it requires a sort internally and can be slow on large datasets.
示例代码
-- Select specific columns, sorted by price descending
SELECT name, price, in_stock
FROM products
ORDER BY price DESC
LIMIT 10;
-- Use an alias for a computed column
SELECT name, price, price * 1.08 AS price_with_tax
FROM products
WHERE in_stock = TRUE
ORDER BY price_with_tax ASC
LIMIT 5;
-- Get unique categories
SELECT DISTINCT category FROM products ORDER BY category;
Three 常见模式: 顶部-N 查询使用 ORDER BY + LIMIT, computed 列使用 alias, and DISTINCT 到列表唯一值.
动手试一试
- SQL Formatter
格式 multi-clause SELECT 语句 for readability.
- JSON Formatter
查询 results 经常 come 后面 as JSON — 格式它们这里.
常见错误
错误写法
Using SELECT * in production code
正确做法
列表仅 列你 需要. SELECT * transfers 更多数据 than 必要 and silently breaks 如果列 是添加 or 移除.
错误写法
Forgetting 那个 LIMIT 不使用 ORDER BY 是非-deterministic
正确做法
不使用 ORDER BY, 数据库可能返回行 in 任何顺序. 总是 pair LIMIT 使用 ORDER BY for consistent pagination.
练习 SELECT 查询
20 个练习
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。