SQL 语法参考

那个 SQL 关键字如何使用？

使用 Metabase 学习 SQL
免费下载 Metabase，或免费试用 Metabase Cloud

SQL ALL

检查子查询中的所有值是否符合条件。

WITH widget_prices AS (
  SELECT price
  FROM products
  WHERE category = 'Widget'
)
SELECT
  title,
  price,
  category
FROM
  products
WHERE
  price > ALL (SELECT price FROM widget_prices);

SQL AND

筛选满足多个条件的行。

SELECT
  *
FROM
  products
WHERE
  category = 'Gizmo'
  AND price > 50;

SQL ANY

检查子查询中的任何值是否符合条件。 ANY 和 SOME 是一回事。

SELECT
  title,
  price
FROM
  products
WHERE
  price > ANY (
    SELECT price
    FROM products
    WHERE category = 'Widget'
  );

SQL ARRAY

允许您处理值数组。(语法因数据库而异，但这里有一个 Postgres 风格的示例。)

SELECT ARRAY[price, 100, 200] AS price_array
FROM products
LIMIT 1;

SQL AVG

计算数值列的平均值。

SELECT
  AVG(price)
FROM
  products;

SQL AS

为结果中的列创建别名。

SELECT
  title AS "Product Name",
  category AS "Product Category"
FROM
  products;

SQL BETWEEN

检查值是否在一个范围内（包含）。

SELECT
  *
FROM
  products
WHERE
  price BETWEEN 10 AND 20;

SQL CASE

根据条件返回值，类似于 if-else 语句。

SELECT
  title,
  CASE
    WHEN price > 100 THEN 'Expensive'
    ELSE 'Affordable'
  END AS price_category
FROM
  products;

SQL CAST

将一个值从一种数据类型转换为另一种。

CAST(24.99 AS VARCHAR) AS price_text,
CAST('2024-01-01' AS DATE) AS order_date

SQL COUNT

计算行数。

SELECT
  COUNT(*)
FROM
  orders;

SQL CURRENT_DATE

返回系统的当前日期。

SELECT
  CURRENT_DATE AS today,
  CURRENT_DATE - INTERVAL '1' DAY AS yesterday,
  CURRENT_DATE + INTERVAL '1' DAY AS tomorrow;

参见 INTERVAL。

SQL DATE

从字符串创建日期值或从时间戳中提取日期部分。

-- Create a date from a string
SELECT DATE '2024-05-04' AS specific_date;

大多数数据库会将 '2025-05-04' 推断为日期，但最好还是明确指定。

SQL CURRENT_TIME

返回系统的当前时间。

SELECT
  CURRENT_TIME AS right_now,
  CURRENT_TIME + INTERVAL '30' MINUTE AS thirty_mins_later;

参见 INTERVAL。

SQL DISTINCT

仅返回唯一值。

SELECT
  DISTINCT category
FROM
  products;

SQL EXCEPT

返回第一个查询中存在但第二个查询中不存在的行。

SELECT
  id
FROM
  people
EXCEPT
SELECT
  user_id
FROM
  orders;

SQL EXISTS

检查子查询是否返回任何行。

SELECT
  name
FROM
  people
WHERE
  EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.user_id = people.id
  );

SQL EXTRACT

从日期或时间戳中提取特定部分（年、月、日等）。

SELECT
  created_at as "timestamp",
  EXTRACT(YEAR FROM created_at) AS "year",
  EXTRACT(MONTH FROM created_at) AS "month",
  EXTRACT(DAY FROM created_at) AS "day",
  EXTRACT(HOUR FROM created_at) AS "hour",
  EXTRACT(DOW FROM created_at) AS "day of week"
FROM
  orders;

这将产生

| timestamp                  | year  | month | day | hour | day of week |
| -------------------------- | ----- | ----- | --- | ---- | ----------- |
| February 11, 2025, 9:40 PM | 2,025 | 2     | 11  | 21   | 3           |
| May 15, 2024, 8:04 AM      | 2,024 | 5     | 15  | 8    | 4           |
| December 6, 2025, 10:22 PM | 2,025 | 12    | 6   | 22   | 7           |
| August 22, 2025, 4:30 PM   | 2,025 | 8     | 22  | 16   | 6           |
| October 10, 2024, 3:34 AM  | 2,024 | 10    | 10  | 3    | 5           |
| November 6, 2025, 4:38 PM  | 2,025 | 11    | 6   | 16   | 5           |

SQL FROM

指定要查询的表。

SELECT
  *
FROM
  products;

SQL GROUP BY

将指定列中具有相同值的行分组。

SELECT
  category,
  COUNT(*)
FROM
  products
GROUP BY
  category;

SQL HAVING

在聚合后筛选组（与 GROUP BY 一起使用）。

SELECT
  category,
  COUNT(*)
FROM
  products
GROUP BY
  category
HAVING
  COUNT(*) > 2;

SQL IN

检查值是否与列表或子查询中的任何值匹配。

使用值列表

SELECT
  *
FROM
  products
WHERE
  category IN ('Gizmo', 'Widget');

使用子查询

SELECT
  *
FROM
  orders
WHERE
  user_id IN (
    SELECT id
    FROM people
    WHERE state = 'VT'
  );

SQL INTERVAL

指定日期/时间计算的时间段。常见单位包括年、月、周、日、小时、分钟和秒。

您可以使用 INTERVAL 添加天数

SELECT
  created_at,
  -- Add 7 days to a date
  created_at + INTERVAL '7' DAY AS next_week
FROM
  orders;

减去月数

SELECT
  created_at,
  -- Subtract 2 months from a date
  created_at - INTERVAL '2' MONTH AS two_months_ago
FROM
  orders;

SQL 日期时间单位包括

年
月
日
小时
分钟- 秒

SQL INNER JOIN

当两个表中都有匹配项时返回行。

SELECT
  orders.id,
  products.title
FROM
  orders
INNER JOIN products ON orders.product_id = products.id;

SQL INTERSECT

返回在两个查询中都出现的行。

SELECT
  id
FROM
  people
INTERSECT
SELECT
  user_id
FROM
  orders;

SQL IS NULL

检查缺失（空）值。

SELECT
  *
FROM
  products
WHERE
  vendor IS NULL;

SQL JOIN

根据相关列，组合来自两个或多个表的行。

SELECT
  orders.id,
  products.title
FROM
  orders
JOIN products ON orders.product_id = products.id;

SQL LEFT JOIN

返回左表的所有行，以及右表中匹配的行。

SELECT
  people.name,
  orders.id
FROM
  people
LEFT JOIN orders ON people.id = orders.user_id;

SQL LIKE

通过模式匹配筛选行。

SELECT
  *
FROM
  products
WHERE
  title LIKE '%Wool%';

SQL LIMIT

限制返回的行数。

SELECT
  *
FROM
  products
LIMIT
  3;

SQL MAX

返回列中的最大值。

SELECT
  MAX(price)
FROM
  products;

SQL MIN

返回列中的最小值。

SELECT
  MIN(price)
FROM
  products;

SQL MOD

返回除法余数的数学函数。可用于抽样具有随机分布 ID 的行。

SELECT
  *
FROM
  products
WHERE
  MOD(id, 10) = 3;

SQL NOT

否定条件。

SELECT
  *
FROM
  products
WHERE
  NOT category = 'Gizmo';

SQL NULL

表示缺失或未知数据。

SELECT
  *
FROM
  products
WHERE
  vendor IS NULL;

SQL ON

指定表之间的连接条件。

SELECT
  orders.id,
  products.title
FROM
  orders
JOIN products ON orders.product_id = products.id;

SQL OR

筛选至少一个条件为真的行。

SELECT
  *
FROM
  products
WHERE
  category = 'Gizmo'
  OR price > 100;

SQL ORDER BY

按一列或多列对结果集排序。

SELECT
  title,
  price
FROM
  products
ORDER BY
  price DESC;

SQL RIGHT JOIN

返回右表的所有行，以及左表中匹配的行。

SELECT
  orders.id,
  people.name
FROM
  orders
RIGHT JOIN people ON orders.user_id = people.id;

SQL SELECT

指定要从表中返回的列。

-- Get all columns
SELECT
  *
FROM
  products;

-- Get specific columns
SELECT
  title,
  category
FROM
  products;

SQL SOME

与 ANY 相同 - 检查子查询中的任何值是否符合条件。

SELECT
  title,
  price
FROM
  products
WHERE
  price > SOME (
    SELECT price
    FROM products
    WHERE category = 'Widget'
  );

SQL SUM

将列中的值相加。

SELECT
  SUM(price)
FROM
  products;

SQL UNION

合并两个查询的结果（删除重复项）。

SELECT
  title
FROM
  products
UNION
SELECT
  name
FROM
  people;

SQL UNION ALL

合并两个查询的结果并保留所有行，甚至重复项。因此，如果相同的值在两个查询中都出现，您将看到两次。

SELECT
  title
FROM
  products
UNION ALL
SELECT
  name
FROM
  people;

SQL WHERE

根据指定条件筛选行。

SELECT
  *
FROM
  products
WHERE
  MOD(id, 10) = 3;

SQL WITH

定义一个您可以在查询中使用的公用表表达式（CTE）。它类似于一个临时结果集。

WITH expensive_products AS (
  SELECT *
  FROM products
  WHERE price > 100
)
SELECT title FROM expensive_products;

商业智能

嵌入式分析

文档

学习

课程

Metabase 基础知识

提升您的数据技能

速查表