SQL学习指南（第3版） (Alan Beaulieu) (Z-Library)

【One-Line Pitch】 A practical, example-driven SQL tutorial that walks you from your first SELECT to advanced topics like subqueries, outer joins, and transactions, using a consistent sample database (Sakila) so you can focus on concepts rather than data setup. Ideal for application developers, database administrators, and anyone who wants to write reliable, cross-platform SQL. 【Book Arc】 - **Opening (~0%–10%)**: Introduces the purpose of SQL and relational databases, sets up the Sakila sample schema, and covers fundamental data types (character, numeric, temporal) plus table creation with primary and foreign keys. This stage solves the "where do I start" problem by giving you a working environment and core vocabulary. - **Early (~10%–23%)**: Dives into the anatomy of the SELECT statement—its six main clauses (select, from, where, group by, having, order by)—and shows how to retrieve, filter, and sort data. Also covers derived tables (subqueries in FROM) and temporary tables, establishing the mental model of query evaluation order. - **Early-to-Middle (~23%–39%)**: Focuses on filtering techniques: equality/inequality conditions, range conditions (BETWEEN), set membership (IN/NOT IN), pattern matching with wildcards, and the critical handling of NULL values. Includes data modification examples (DELETE/UPDATE) and warns about auto-commit behavior. - **Middle (~39%–48%)**: Explains joins in depth—inner joins, ANSI join syntax, USING vs. ON, joining the same table twice, and multi-table queries. Introduces set operations (UNION, INTERSECT, EXCEPT) with Venn-diagram intuition, noting which operators are supported by which database servers. - **Late (~48%–end)**: Covers advanced but independent topics: string and numeric functions, conditional logic with CASE expressions, subqueries (correlated and non-correlated), outer joins, transactions, indexes, constraints, and views. The final chapters address modern needs like big data, cross-platform SQL services (e.g., Apache Drill), and reporting/analysis tools. 【Key Takeaways】 - **The SELECT statement is built on six clauses with a specific evaluation order** (Early): knowing that FROM is evaluated before SELECT helps you understand derived tables and aliases. Master this skeleton first, and every other query feature becomes easier to place. - **Filtering is where precision lives** (Early): equality, range (BETWEEN), set membership (IN), and wildcard matching (LIKE) each have distinct use cases. The biggest trap is NULL—conditions like `NOT IN` or `NOT BETWEEN` silently exclude rows with NULL values, so always test with `IS NULL`. - **Joins are about relationships, not just syntax** (Middle): inner joins return only matching rows; if you need all rows from one table regardless of match, you'll need outer joins (covered later). Explicitly writing `INNER JOIN` and using `ON` instead of `USING` makes queries clearer for future maintainers. - **Set operations treat query results as mathematical sets** (Middle): UNION combines, INTERSECT finds overlap, EXCEPT subtracts. Be aware that not all operators are implemented everywhere—MySQL 8.0 lacks INTERSECT and EXCEPT, so you may need workarounds. - **Subqueries are versatile tools** (Late): they can act as data sources (derived tables), expression generators, or filtering mechanisms (with EXISTS). Correlated subqueries reference the outer query and are powerful but can be harder to reason about—run the subquery standalone to visualize its output. - **CASE expressions bring conditional logic into SQL** (Late): useful for result-set transformation, existence checks, and avoiding division-by-zero errors. This is a practical skill that separates basic query writers from those who can shape data for reporting. - **Transactions, indexes, and constraints protect data integrity** (Late): understanding auto-commit mode (MySQL default) and when to use explicit transactions prevents accidental data loss. Indexes and constraints are the backbone of performance and correctness, though the excerpts only hint at their depth. 【Reading Tips】 - **Skim the first chapter's background and jump straight to Chapter 2** if you're already familiar with relational concepts; the Sakila schema setup is essential, but the history of SQL can be skipped. - **Deep-read Chapters 3–5** (query clauses, filtering, joins) because they form the foundation; do the exercises here—they're short and reinforce the evaluation-order mental model. - **Treat Chapters 7–11 as a reference buffet** (functions, grouping, subqueries, joins revisited, conditional logic): each is fairly independent, so you can skip around based on your immediate needs, as the author explicitly encourages. - **Watch for MySQL-specific notes** (e.g., text type limits, missing INTERSECT/EXCEPT) and compare with SQL Server/Oracle examples when you need cross-platform knowledge; the book consistently shows dialect differences, which is valuable for real-world work. - **Don't skip the NULL discussions** in the filtering chapter—they're the most common source of bugs in production queries. Test your conditions against NULL explicitly to avoid silent data loss. 【Coverage Limits】 This guide synthesizes the first ~48% of the book in detail (setup, queries, filtering, joins, set operations, and string functions). Later chapters on subqueries, outer joins, transactions, indexes, views, and big-data tools are only lightly touched, as the excerpts provide limited depth there.