Comprehensive Guide to SQL Basics, Advanced Queries, and Performance Optimization

Introduction: The article introduces the logical order of SQL statements versus their written order, illustrating the SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT flow.

Core rules: Explanation of each clause's purpose, noting that execution order differs from writing order (e.g., FROM executes before SELECT ).

Basic statement templates: Detailed syntax for SELECT , INSERT (including full‑field, specified‑field, batch, select‑into, and subquery inserts), UPDATE (with mandatory WHERE ), and DELETE (with safety warnings).

INSERT INTO 用户 VALUES (1, '张三', '杭州');
INSERT INTO 用户(姓名, 城市) VALUES ('李四', '北京');
INSERT INTO 订单(用户ID, 商品) VALUES (101, 'iPhone15'), (102, 'iPadPro');
INSERT INTO 高消费用户 SELECT 用户ID FROM 订单 GROUP BY 用户ID HAVING SUM(金额) > 10000;

Practical scenarios: Case 1 demonstrates e‑commerce user behavior analysis using multi‑table joins, aggregation, and filtering; Case 2 shows data cleaning and migration with INSERT‑SELECT , transaction control, and conditional DELETE .

SELECT u.用户ID, u.姓名, COUNT(o.订单ID) AS 订单总数, AVG(o.金额) AS 平均客单价, MAX(o.金额) AS 最高消费额
FROM 用户 u INNER JOIN 订单 o ON u.用户ID = o.用户ID
WHERE u.城市 = '杭州' AND o.商品类别 = '电子产品' AND o.创建时间 BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY u.用户ID HAVING 订单总数 >= 3 ORDER BY 最高消费额 DESC LIMIT 10;

Advanced syntax library: A matrix of join types (INNER, LEFT, RIGHT, FULL OUTER, CROSS) with examples and use cases, followed by a table of subquery categories (scalar, column, row, correlated) with sample code.

-- Scalar subquery example
SELECT (SELECT MAX(金额) FROM 订单) AS 最高消费;
-- Column subquery example
WHERE 用户ID IN (SELECT DISTINCT 用户ID FROM 高消费用户);
-- Row subquery example
WHERE (城市, 年龄) = (SELECT 城市, AVG(年龄) FROM 用户 GROUP BY 城市);
-- Correlated subquery example
WHERE EXISTS (SELECT 1 FROM 订单 WHERE 用户ID = 外部表.用户ID);

Pitfalls and performance tuning: Common mistakes such as missing GROUP BY fields, misuse of aggregates in WHERE , and overusing SELECT * ; index creation examples for covering, composite, and functional indexes, plus experimental results showing a speedup from 2.1 s to 0.03 s on a million‑row table.

CREATE INDEX idx_user_city ON 用户(城市) INCLUDE (姓名, 注册时间);
CREATE INDEX idx_orders_search ON 订单(用户ID DESC, 创建_time ASC);
CREATE INDEX idx_email_domain ON 用户(SUBSTRING_INDEX(email, '@', -1));

Learning path: Recommended stages from mastering basic SELECT queries, through advanced joins and subqueries, to full‑scale database projects and high‑level topics like execution‑plan analysis, index optimization strategies, and sharding.