Skip to content

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 个练习

练习 SELECT 查询

查阅不认识的术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

查看术语表

动手做项目

通过引导式项目应用所学,含起始代码与解决方案。

查看项目

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误