SQL 语法参考

从基本SELECT到窗口函数和CTE — 每个主要SQL命令和子句的语法、示例和说明。

45个条目

Basic SELECTSELECT

SELECT col1, col2 FROM table;

SELECT name, email FROM users;

ℹ Use * to select all columns (avoid in production)

SELECT with aliasSELECT

SELECT col AS alias FROM table;

SELECT first_name AS name, created_at AS joined FROM users;

SELECT DISTINCTSELECT

SELECT DISTINCT col FROM table;

SELECT DISTINCT country FROM customers;

WHERE clauseSELECT

SELECT ... FROM table WHERE condition;

SELECT * FROM orders WHERE status = 'pending' AND total > 100;

ORDER BYSELECT

SELECT ... ORDER BY col ASC|DESC;

SELECT name, price FROM products ORDER BY price DESC;

LIMIT / OFFSETSELECT

SELECT ... LIMIT n OFFSET m;

SELECT * FROM posts LIMIT 20 OFFSET 40;

ℹ SQL Server: use TOP n or FETCH NEXT

LIKE pattern matchSELECT

WHERE col LIKE 'pattern'

SELECT * FROM users WHERE email LIKE '%@gmail.com';

IN listSELECT

WHERE col IN (v1, v2, ...)

SELECT * FROM orders WHERE status IN ('pending', 'processing');

BETWEEN rangeSELECT

WHERE col BETWEEN v1 AND v2

SELECT * FROM orders WHERE total BETWEEN 100 AND 500;

IS NULL checkSELECT

WHERE col IS NULL | IS NOT NULL

SELECT * FROM users WHERE deleted_at IS NULL;

CASE expressionSELECT

CASE WHEN cond THEN val ... ELSE val END

SELECT name, CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END AS type FROM users;

COUNTAggregates

SELECT COUNT(*) | COUNT(col) FROM table;

SELECT COUNT(*) AS total, COUNT(email) AS with_email FROM users;

SUM / AVG / MIN / MAXAggregates

SELECT SUM(col), AVG(col), MIN(col), MAX(col) FROM table;

SELECT SUM(total) AS revenue, AVG(total) AS avg_order FROM orders;

GROUP BYAggregates

SELECT col, AGG(x) FROM table GROUP BY col;

SELECT country, COUNT(*) AS users FROM users GROUP BY country;

HAVINGAggregates

... GROUP BY col HAVING AGG(x) > n

SELECT country, COUNT(*) as cnt FROM users GROUP BY country HAVING COUNT(*) > 100;

GROUP BY with ORDERAggregates

... GROUP BY col ORDER BY AGG(x) DESC

SELECT status, COUNT(*) cnt FROM orders GROUP BY status ORDER BY cnt DESC;

INNER JOINJOINs

SELECT ... FROM a INNER JOIN b ON a.id = b.a_id;

SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;

LEFT JOINJOINs

SELECT ... FROM a LEFT JOIN b ON a.id = b.a_id;

SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id;

RIGHT JOINJOINs

SELECT ... FROM a RIGHT JOIN b ON a.id = b.a_id;

SELECT o.id, u.name FROM orders o RIGHT JOIN users u ON o.user_id = u.id;

FULL OUTER JOINJOINs

SELECT ... FROM a FULL OUTER JOIN b ON condition;

SELECT a.id, b.id FROM table_a a FULL OUTER JOIN table_b b ON a.key = b.key;

ℹ Not supported in MySQL 8 — use UNION of LEFT + RIGHT JOIN

SELF JOINJOINs

SELECT ... FROM t AS a JOIN t AS b ON a.col = b.col;

SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

CROSS JOINJOINs

SELECT ... FROM a CROSS JOIN b;

SELECT colors.name, sizes.label FROM colors CROSS JOIN sizes;

ℹ Returns Cartesian product — rows(a) × rows(b)

Subquery in WHERESubqueries

WHERE id IN (SELECT id FROM ...)

SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

Scalar subquerySubqueries

SELECT (SELECT COUNT(*) FROM ...) AS n

SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count FROM users u;

EXISTSSubqueries

WHERE EXISTS (SELECT 1 FROM ...)

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

CTE (WITH clause)Subqueries

WITH cte AS (SELECT ...) SELECT * FROM cte;

WITH recent AS (SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days') SELECT user_id, COUNT(*) FROM recent GROUP BY user_id;

Recursive CTESubqueries

WITH RECURSIVE cte AS (base UNION ALL recursive) ...

WITH RECURSIVE nums(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n < 10) SELECT n FROM nums;

ROW_NUMBER()Window Fns

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

SELECT name, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank FROM employees;

RANK / DENSE_RANKWindow Fns

RANK() OVER (ORDER BY col DESC)

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS pay_rank FROM employees;

ℹ RANK skips numbers on ties; DENSE_RANK does not

LAG / LEADWindow Fns

LAG(col, n) OVER (ORDER BY ...)

SELECT date, revenue, LAG(revenue,1) OVER (ORDER BY date) AS prev_revenue FROM daily_sales;

SUM() OVER (running total)Window Fns

SUM(col) OVER (ORDER BY col ROWS UNBOUNDED PRECEDING)

SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM transactions;

NTILE(n)Window Fns

NTILE(n) OVER (ORDER BY col)

SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;

INSERT single rowDML

INSERT INTO table (col1, col2) VALUES (v1, v2);

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');

INSERT multiple rowsDML

INSERT INTO table (cols) VALUES (r1), (r2), ...;

INSERT INTO tags (name) VALUES ('sql'), ('database'), ('tutorial');

INSERT … SELECTDML

INSERT INTO t1 (cols) SELECT cols FROM t2;

INSERT INTO archive SELECT * FROM orders WHERE created_at < '2023-01-01';

UPDATEDML

UPDATE table SET col = val WHERE condition;

UPDATE users SET status = 'inactive', updated_at = NOW() WHERE last_login < NOW() - INTERVAL '90 days';

DELETEDML

DELETE FROM table WHERE condition;

DELETE FROM sessions WHERE expires_at < NOW();

ℹ Always include WHERE — omitting it deletes all rows

UPSERT (ON CONFLICT)DML

INSERT … ON CONFLICT (col) DO UPDATE SET …

INSERT INTO settings (key, value) VALUES ('theme', 'dark') ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

ℹ PostgreSQL syntax. MySQL uses ON DUPLICATE KEY UPDATE.

TRUNCATEDML

TRUNCATE TABLE table;

TRUNCATE TABLE temp_imports;

ℹ Faster than DELETE; resets auto-increment; cannot be rolled back in some DBs

CREATE TABLEDDL

CREATE TABLE name (col type constraints, ...);

CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW());

ALTER TABLE ADD COLUMNDDL

ALTER TABLE t ADD COLUMN col type;

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

ALTER TABLE RENAMEDDL

ALTER TABLE t RENAME COLUMN old TO new;

ALTER TABLE users RENAME COLUMN username TO display_name;

CREATE INDEXDDL

CREATE INDEX name ON table (col);

CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

DROP TABLEDDL

DROP TABLE [IF EXISTS] table;

DROP TABLE IF EXISTS temp_staging;

FOREIGN KEYDDL

REFERENCES other_table(col) ON DELETE action

CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON DELETE CASCADE);

SQL子句执行顺序

FROM→

JOIN→

WHERE→

GROUP BY→

HAVING→

SELECT→

DISTINCT→

ORDER BY→

LIMIT

这是逻辑的处理顺序。WHERE在SELECT之前运行，因此无法在以下位置引用SELECT别名 WHERE子句 — 改用CTE或子查询。HAVING可以过滤 GROUP BY聚合上。

NULL处理规则

表达式	结果	注意事项
NULL = NULL	FALSE / UNKNOWN	Use IS NULL instead
NULL != NULL	UNKNOWN	Any comparison with NULL is UNKNOWN
NULL + 5	NULL	Arithmetic on NULL propagates NULL
COALESCE(NULL, 'x')	'x'	Returns first non-NULL value
NULLIF(a, a)	NULL	Returns NULL if both args are equal
COUNT(*)	Counts all rows	COUNT(col) skips NULLs

SQL快速参考常见问题

SQL子句的执行顺序是什么？

SQL子句按以下逻辑顺序执行：FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET。这很重要，因为WHERE不能引用SELECT别名，但HAVING可以引用GROUP BY列。

INNER JOIN和LEFT JOIN有什么区别？

INNER JOIN仅返回两个表中连接条件匹配的行。LEFT JOIN（LEFT OUTER JOIN）返回左表中的所有行，对不匹配的右表列返回NULL。当您需要左表中的所有记录，无论右侧是否有匹配时，使用LEFT JOIN。

SQL中的窗口函数是什么？

窗口函数在与当前行相关的一组行中计算值，而不像GROUP BY聚合那样将其折叠为单行。常见的窗口函数：ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER()。它们使用OVER()子句来定义窗口。

什么是CTE（公共表表达式）？

CTE是在SELECT, INSERT, UPDATE或DELETE语句之前使用WITH关键字定义的命名临时结果集。CTE比嵌套子查询更易读，还可以是递归的（WITH RECURSIVE），用于组织架构或类别树等层次数据。

NULL处理规则

表达式	结果	注意事项
NULL = NULL	FALSE / UNKNOWN	Use IS NULL instead
NULL != NULL	UNKNOWN	Any comparison with NULL is UNKNOWN
NULL + 5	NULL	Arithmetic on NULL propagates NULL
COALESCE(NULL, 'x')	'x'	Returns first non-NULL value
NULLIF(a, a)	NULL	Returns NULL if both args are equal
COUNT(*)	Counts all rows	COUNT(col) skips NULLs

SQL 语法 参考

SQL子句执行顺序

NULL处理规则

SQL快速参考常见问题

SQL子句的执行顺序是什么？

INNER JOIN和LEFT JOIN有什么区别？

SQL中的窗口函数是什么？

什么是CTE（公共表表达式）？

References & cheat sheets

SQL 语法 参考

SQL子句执行顺序

NULL处理规则

SQL快速参考常见问题

SQL子句的执行顺序是什么？

INNER JOIN和LEFT JOIN有什么区别？

SQL中的窗口函数是什么？

什么是CTE（公共表表达式）？

SQL 语法参考

SQL 语法参考