Learning SQL

Spis treści książki

• Learning SQL
• SPECIAL OFFER: Upgrade this ebook with OReilly
• A Note Regarding Supplemental Files
• Preface
  • Why Learn SQL?
  • Why Use This Book to Do It?
  • Structure of This Book
  • Conventions Used in This Book
  • How to Contact Us
  • Using Code Examples
  • Safari Enabled
  • Acknowledgments
• 1. A Little Background
  • 1.1. Introduction to Databases
    • 1.1.1. Nonrelational Database Systems
    • 1.1.2. The Relational Model
    • 1.1.3. Some Terminology
  • 1.2. What Is SQL?
    • 1.2.1. SQL Statement Classes
    • 1.2.2. SQL: A Nonprocedural Language
    • 1.2.3. SQL Examples
  • 1.3. What Is MySQL?
  • 1.4. Whats in Store
• 2. Creating and Populating a Database
  • 2.1. Creating a MySQL Database
  • 2.2. Using the mysql Command-Line Tool
  • 2.3. MySQL Data Types
    • 2.3.1. Character Data
      • 2.3.1.1. Character sets
      • 2.3.1.2. Text data
    • 2.3.2. Numeric Data
    • 2.3.3. Temporal Data
  • 2.4. Table Creation
    • 2.4.1. Step 1: Design
    • 2.4.2. Step 2: Refinement
    • 2.4.3. Step 3: Building SQL Schema Statements
  • 2.5. Populating and Modifying Tables
    • 2.5.1. Inserting Data
      • 2.5.1.1. Generating numeric key data
      • 2.5.1.2. The insert statement
    • 2.5.2. Updating Data
    • 2.5.3. Deleting Data
  • 2.6. When Good Statements Go Bad
    • 2.6.1. Nonunique Primary Key
    • 2.6.2. Nonexistent Foreign Key
    • 2.6.3. Column Value Violations
    • 2.6.4. Invalid Date Conversions
  • 2.7. The Bank Schema
• 3. Query Primer
  • 3.1. Query Mechanics
  • 3.2. Query Clauses
  • 3.3. The select Clause
    • 3.3.1. Column Aliases
    • 3.3.2. Removing Duplicates
  • 3.4. The from Clause
    • 3.4.1. Tables
      • 3.4.1.1. Subquery-generated tables
      • 3.4.1.2. Views
    • 3.4.2. Table Links
    • 3.4.3. Defining Table Aliases
  • 3.5. The where Clause
  • 3.6. The group by and having Clauses
  • 3.7. The order by Clause
    • 3.7.1. Ascending Versus Descending Sort Order
    • 3.7.2. Sorting via Expressions
    • 3.7.3. Sorting via Numeric Placeholders
  • 3.8. Exercises
    • 3.8.1. 3-1
    • 3.8.2. 3-2
    • 3.8.3. 3-3
    • 3.8.4. 3-4
• 4. Filtering
  • 4.1. Condition Evaluation
    • 4.1.1. Using Parentheses
    • 4.1.2. Using the not Operator
  • 4.2. Building a Condition
  • 4.3. Condition Types
    • 4.3.1. Equality Conditions
      • 4.3.1.1. Inequality conditions
      • 4.3.1.2. Data modification using equality conditions
    • 4.3.2. Range Conditions
      • 4.3.2.1. The between operator
      • 4.3.2.2. String ranges
    • 4.3.3. Membership Conditions
      • 4.3.3.1. Using subqueries
      • 4.3.3.2. Using not in
    • 4.3.4. Matching Conditions
      • 4.3.4.1. Using wildcards
      • 4.3.4.2. Using regular expressions
  • 4.4. NULL: That Four-Letter Word
  • 4.5. Exercises
    • 4.5.1. 4-1
    • 4.5.2. 4-2
    • 4.5.3. 4-3
    • 4.5.4. 4-4
• 5. Querying Multiple Tables
  • 5.1. What Is a Join?
    • 5.1.1. Cartesian Product
    • 5.1.2. Inner Joins
    • 5.1.3. The ANSI Join Syntax
  • 5.2. Joining Three or More Tables
    • 5.2.1. Using Subqueries as Tables
    • 5.2.2. Using the Same Table Twice
  • 5.3. Self-Joins
  • 5.4. Equi-Joins Versus Non-Equi-Joins
  • 5.5. Join Conditions Versus Filter Conditions
  • 5.6. Exercises
    • 5.6.1. 5-1
    • 5.6.2. 5-2
    • 5.6.3. 5-3
• 6. Working with Sets
  • 6.1. Set Theory Primer
  • 6.2. Set Theory in Practice
  • 6.3. Set Operators
    • 6.3.1. The union Operator
    • 6.3.2. The intersect Operator
    • 6.3.3. The except Operator
  • 6.4. Set Operation Rules
    • 6.4.1. Sorting Compound Query Results
    • 6.4.2. Set Operation Precedence
  • 6.5. Exercises
    • 6.5.1. 6-1
    • 6.5.2. 6-2
    • 6.5.3. 6-3
• 7. Data Generation, Conversion, and Manipulation
  • 7.1. Working with String Data
    • 7.1.1. String Generation
      • 7.1.1.1. Including single quotes
      • 7.1.1.2. Including special characters
    • 7.1.2. String Manipulation
      • 7.1.2.1. String functions that return numbers
      • 7.1.2.2. String functions that return strings
  • 7.2. Working with Numeric Data
    • 7.2.1. Performing Arithmetic Functions
    • 7.2.2. Controlling Number Precision
    • 7.2.3. Handling Signed Data
  • 7.3. Working with Temporal Data
    • 7.3.1. Dealing with Time Zones
    • 7.3.2. Temporal Data Generation
      • 7.3.2.1. String representations of temporal data
      • 7.3.2.2. String-to-date conversions
      • 7.3.2.3. Functions for generating dates
    • 7.3.3. Temporal Data Manipulation
      • 7.3.3.1. Temporal functions that return dates
      • 7.3.3.2. Temporal functions that return strings
      • 7.3.3.3. Temporal functions that return numbers
  • 7.4. Conversion Functions
  • 7.5. Exercises
    • 7.5.1. 7-1
    • 7.5.2. 7-2
    • 7.5.3. 7-3
• 8. Grouping and Aggregates
  • 8.1. Grouping Concepts
  • 8.2. Aggregate Functions
    • 8.2.1. Implicit Versus Explicit Groups
    • 8.2.2. Counting Distinct Values
    • 8.2.3. Using Expressions
    • 8.2.4. How Nulls Are Handled
  • 8.3. Generating Groups
    • 8.3.1. Single-Column Grouping
    • 8.3.2. Multi-Column Grouping
    • 8.3.3. Grouping via Expressions
    • 8.3.4. Generating Rollups
  • 8.4. Group Filter Conditions
  • 8.5. Exercises
    • 8.5.1. 8-1
    • 8.5.2. 8-2
    • 8.5.3. 8-3
  • 8.6. 8-4 (Extra Credit)
• 9. Subqueries
  • 9.1. What Is a Subquery?
  • 9.2. Subquery Types
  • 9.3. Noncorrelated Subqueries
    • 9.3.1. Multiple-Row, Single-Column Subqueries
      • 9.3.1.1. The in operator
      • 9.3.1.2. The all operator
      • 9.3.1.3. The any operator
    • 9.3.2. Multicolumn Subqueries
  • 9.4. Correlated Subqueries
    • 9.4.1. The Exists Operator
    • 9.4.2. Data Manipulation Using Correlated Subqueries
  • 9.5. When to Use Subqueries
    • 9.5.1. Subqueries as Data Sources
      • 9.5.1.1. Table fabrication
      • 9.5.1.2. Task-oriented subqueries
    • 9.5.2. Subqueries in Filter Conditions
    • 9.5.3. Subqueries as Expression Generators
  • 9.6. Subquery Wrap-up
  • 9.7. Exercises
    • 9.7.1. 9-1
    • 9.7.2. 9-2
    • 9.7.3. 9-3
    • 9.7.4. 9-4
• 10. Joins Revisited
  • 10.1. Outer Joins
    • 10.1.1. Left Versus Right Outer Joins
    • 10.1.2. Three-Way Outer Joins
    • 10.1.3. Self Outer Joins
  • 10.2. Cross Joins
  • 10.3. Natural Joins
  • 10.4. Exercises
    • 10.4.1. 10-1
    • 10.4.2. 10-2
    • 10.4.3. 10-3
    • 10.4.4. 10-4 (Extra Credit)
• 11. Conditional Logic
  • 11.1. What Is Conditional Logic?
  • 11.2. The Case Expression
    • 11.2.1. Searched Case Expressions
    • 11.2.2. Simple Case Expressions
  • 11.3. Case Expression Examples
    • 11.3.1. Result Set Transformations
    • 11.3.2. Selective Aggregation
    • 11.3.3. Checking for Existence
    • 11.3.4. Division by Zero Errors
    • 11.3.5. Conditional Updates
    • 11.3.6. Handling Null Values
  • 11.4. Exercises
    • 11.4.1. 11-1
    • 11.4.2. 11-2
• 12. Transactions
  • 12.1. Multiuser Databases
  • 12.2. What Is a Transaction?
    • 12.2.1. Starting a Transaction
    • 12.2.2. Ending a Transaction
    • 12.2.3. Transaction Savepoints
• 13. Indexes and Constraints
  • 13.1. Indexes
    • 13.1.1. Index Creation
      • 13.1.1.1. Unique indexes
      • 13.1.1.2. Multi-column indexes
    • 13.1.2. Types of Indexes
      • 13.1.2.1. B-tree indexes
      • 13.1.2.2. Bitmap indexes
      • 13.1.2.3. Text indexes
    • 13.1.3. How Indexes Are Used
    • 13.1.4. The Downside of Indexes
  • 13.2. Constraints
    • 13.2.1. Constraint Creation
    • 13.2.2. Constraints and Indexes
    • 13.2.3. Cascading Constraints
• A. ER Diagram for Example Database
• B. MySQL Extensions to the SQL Language
  • B.1. Select Extensions
    • B.1.1. The limit Clause
      • B.1.1.1. Combining the limit clause with the order by clause
      • B.1.1.2. limit's optional second parameter
      • B.1.1.3. Ranking queries
    • B.1.2. The into outfile Clause
  • B.2. Combination Insert/Update Statements
  • B.3. Ordered Updates and Deletes
  • B.4. Multitable Updates and Deletes
• C. Solutions to Exercises
  • C.1. Chapter 3
    • C.1.1. 3-1
    • C.1.2. 3-2
    • C.1.3. 3-3
    • C.1.4. 3-4
  • C.2. Chapter 4
    • C.2.1. 4-1
    • C.2.2. 4-2
    • C.2.3. 4-3
    • C.2.4. 4-4
  • C.3. Chapter 5
    • C.3.1. 5-1
    • C.3.2. 5-2
    • C.3.3. 5-3
  • C.4. Chapter 6
    • C.4.1. 6-1
    • C.4.2. 6-2
    • C.4.3. 6-3
  • C.5. Chapter 7
    • C.5.1. 7-1
    • C.5.2. 7-2
    • C.5.3. 7-3
  • C.6. Chapter 8
    • C.6.1. 8-1
    • C.6.2. 8-2
    • C.6.3. 8-3
  • C.7. 8-4 (Extra Credit)
  • C.8. Chapter 9
    • C.8.1. 9-1
    • C.8.2. 9-2
    • C.8.3. 9-3
    • C.8.4. 9-4
  • C.9. Chapter 10
    • C.9.1. 10-1
    • C.9.2. 10-2
    • C.9.3. 10-3
    • C.9.4. 10-4 (Extra Credit)
  • C.10. Chapter 11
    • C.10.1. 11-1
    • C.10.2. 11-2
• D. Further Resources
  • D.1. Advanced SQL
  • D.2. Database Programming
  • D.3. Database Design
  • D.4. Database Tuning
  • D.5. Database Administration
  • D.6. Report Generation
• About the Author
• Colophon
• SPECIAL OFFER: Upgrade this ebook with OReilly
• Copyright

pokaż cały spis treści

ukryj recenzje