SQL进阶教程 (MICK)（Z-Library）

【One-Line Pitch】 A practical bridge from everyday SQL to genuinely set-based thinking: it turns familiar clauses like CASE, HAVING, and EXISTS into sharper tools, then explains why the relational model works the way it does. Best for developers and DBAs who already write SQL but want to reason about it rather than memorize it. 【Book Arc】 - **Opening (~0%–15%)**: Starts with "unusual" uses of ordinary SQL—CASE expressions for flexible grouping and constraints, self-joins for ranking and deduplication, and the shift from row-by-row thinking to set thinking. Solves the problem of writing correct SQL for awkward reporting and ranking tasks. - **Early (~15%–35%)**: Moves into three-valued logic and NULL, HAVING as a tool for inspecting whole sets, and outer joins for building cross-tab and horizontal layouts. Solves the "why is my result empty / wrong?" class of bugs. - **Middle (~35%–55%)**: Covers set operations (UNION/INTERSECT/EXCEPT, set equality, relational division), EXISTS and quantified predicates, and handling ordered data such as sequences and dates. Solves problems that resist simple joins or aggregates. - **Late (~55%–75%)**: Applies the same techniques to cumulative sums, moving averages, overlapping intervals, and other window-like calculations done without window functions. Solves time-series and interval questions in portable SQL. - **Ending (~75%–100%)**: Shifts from technique to theory—the history of relational databases, Codd and Date's ideas, logic and set theory behind SQL, the "abolish NULL" debate, and recursion/hierarchies. Solves the "why does SQL look like this?" question and prepares readers for deeper study. 【Key Takeaways】 - **CASE is a general-purpose expression, not just a display formatter** (Opening): it can drive GROUP BY, embed logical implications in CHECK constraints, and replace rigid view-based mappings. - **Self-joins and correlated subqueries are how SQL ranks and compares rows** (Opening): treating one table as two named sets is the mental move that unlocks ranking, deduplication, and row-to-row comparison. - **NULL follows three-valued logic, and that changes query results** (Early): NOT IN with a NULL, ALL versus MIN/MAX, and empty-set inputs all behave counterintuitively; the book explains the truth tables behind them. - **HAVING inspects the set, WHERE inspects the elements** (Early): this distinction is the key to questions like "which groups are entirely complete?" and to finding gaps in sequences. - **Outer joins and scalar subqueries build cross-tabs and horizontal reports** (Early): the book shows both approaches and their trade-offs in readability and performance. - **Set operations have algebraic properties worth knowing** (Middle): UNION and INTERSECT are idempotent, EXCEPT is not, and set equality can be tested via idempotence or one-to-one mapping. - **EXISTS expresses quantification, including universal claims via double negation** (Middle): "all rows satisfy X" becomes "no row fails X," a pattern that generalizes to many puzzles. - **Ordered data can be handled with sets and predicates** (Middle/Late): generating sequences, finding gaps, cumulative sums, and overlapping intervals are all done without procedural loops. 【Reading Tips】 - Read Part 1 sequentially; each section builds on the previous technique, and the exercises at section ends are worth doing rather than skimming. - Deep-read the NULL and three-valued logic sections—they explain many real-world bugs and are referenced repeatedly later. - Skim the historical and philosophical chapters on a first pass, then return after finishing Part 1; they make more sense once the techniques are familiar. - Keep a scratch database handy; most examples are short enough to run and modify, which is where the learning happens. - Treat the exercises and answers as a self-check, not optional extras—they reveal whether you actually absorbed the set-based reasoning. 【Coverage Limits】 This guide is based on stratified excerpts covering roughly the first half of the book in detail, with later chapters represented mainly by table-of-contents entries and brief mentions. Specific examples, chapter numbering, and the full depth of the relational-history chapters are not fully covered here.