SQL Pocket Guide. 2nd Edition Jonathan Gennick

Spis treści książki

• SQL Pocket Guide, 2nd Edition
• SPECIAL OFFER: Upgrade this ebook with OReilly
• A Note Regarding Supplemental Files
• Praise for SQL Pocket Guide
• 1. SQL Pocket Guide
  • 1.1. Introduction
    • 1.1.1. Organization of This Book
    • 1.1.2. Feedback Needed!
    • 1.1.3. Conventions
    • 1.1.4. Acknowledgments
    • 1.1.5. Example Data
  • 1.2. Analytic Functions
  • 1.3. CASE Expressions
    • 1.3.1. Simple CASE Expressions
    • 1.3.2. Searched CASE Expressions
  • 1.4. Datatypes
    • 1.4.1. Character String Types
    • 1.4.2. Decimal Types
    • 1.4.3. Binary Integer Types
    • 1.4.4. Datetime Types
      • 1.4.4.1. Oracle
      • 1.4.4.2. DB2
      • 1.4.4.3. SQL Server
      • 1.4.4.4. MySQL
      • 1.4.4.5. PostgreSQL
  • 1.5. Dataype Conversion
    • 1.5.1. Standard CAST Function
    • 1.5.2. Standard EXTRACT Function
    • 1.5.3. Datetime Conversions (Oracle)
    • 1.5.4. Numeric Conversions (Oracle)
    • 1.5.5. Datetime Conversions (DB2)
    • 1.5.6. Numeric Conversions (DB2)
    • 1.5.7. Datetime Conversions (SQL Server)
      • 1.5.7.1. CAST and SET DATEFORMAT (SQL Server)
      • 1.5.7.2. CONVERT (SQL Server)
      • 1.5.7.3. DATENAME and DATEPART (SQL Server)
      • 1.5.7.4. DAY, MONTH, and YEAR (SQL Server)
    • 1.5.8. Numeric Conversions (SQL Server)
    • 1.5.9. Datetime Conversions (MySQL)
      • 1.5.9.1. Date and time elements (MySQL)
      • 1.5.9.2. TO_DAYS and FROM_DAYS (MySQL)
      • 1.5.9.3. Unix timestamp support (MySQL)
      • 1.5.9.4. Seconds in the day (MySQL)
      • 1.5.9.5. DATE_FORMAT and TIME_FORMAT (MySQL)
    • 1.5.10. Numeric Conversions (MySQL)
    • 1.5.11. Datetime Conversions (PostgreSQL)
    • 1.5.12. Numeric Conversions (PostgreSQL)
  • 1.6. Deleting Data
    • 1.6.1. Deleting in Order (MySQL)
    • 1.6.2. Deleting All Rows
    • 1.6.3. Deleting from Views and Subqueries
    • 1.6.4. Returning Deleted Data (Oracle, DB2)
    • 1.6.5. Double-FROM (SQL Server)
  • 1.7. Functions
    • 1.7.1. Datetime Functions (Oracle)
      • 1.7.1.1. Getting the current date and time in Oracle
      • 1.7.1.2. Rounding and truncating DATEs in Oracle
      • 1.7.1.3. Other useful Oracle datetime functions
    • 1.7.2. Datetime Functions (DB2)
    • 1.7.3. Datetime Functions (SQL Server)
    • 1.7.4. Datetime Functions (MySQL)
    • 1.7.5. Datetime Functions (PostgreSQL)
      • 1.7.5.1. Getting the current date and time in PostgreSQL
      • 1.7.5.2. Rounding and truncating in PostgreSQL
      • 1.7.5.3. Other useful PostgreSQL datetime functions
    • 1.7.6. Numeric and Math Functions (All Platforms)
    • 1.7.7. Trigonometric Functions (All Platforms)
    • 1.7.8. String Functions
      • 1.7.8.1. Searching a string
      • 1.7.8.2. Replacing text in a string
      • 1.7.8.3. Extracting a substring
      • 1.7.8.4. Finding the length of a string
      • 1.7.8.5. Concatenating strings
      • 1.7.8.6. Trimming unwanted characters
      • 1.7.8.7. Changing the case of a string
    • 1.7.9. GREATEST and LEAST (Oracle, PostgreSQL)
  • 1.8. Grouping and Summarizing
    • 1.8.1. Aggregate Functions
    • 1.8.2. GROUP BY
    • 1.8.3. Useful GROUP BY Techniques
      • 1.8.3.1. Reducing the GROUP BY list
      • 1.8.3.2. Grouping before the join
    • 1.8.4. HAVING
    • 1.8.5. GROUP BY Extensions (Oracle, DB2)
      • 1.8.5.1. ROLLUP (Oracle, DB2)
      • 1.8.5.2. CUBE (Oracle, DB2)
      • 1.8.5.3. GROUPING SETS (Oracle)
      • 1.8.5.4. Related functions (Oracle, DB2)
    • 1.8.6. GROUP BY Extensions (SQL Server)
      • 1.8.6.1. ROLLUP (SQL Server)
      • 1.8.6.2. CUBE (SQL Server)
      • 1.8.6.3. GROUPING (SQL Server)
  • 1.9. Hierarchical Queries
    • 1.9.1. Recursive WITH (SQL Server, DB2)
    • 1.9.2. CONNECT BY (Oracle)
      • 1.9.2.1. CONNECT BY, START WITH, and PRIOR (Oracle)
      • 1.9.2.2. Creative CONNECT BY (Oracle)
      • 1.9.2.3. WHERE clauses with CONNECT BY (Oracle)
      • 1.9.2.4. Joins with CONNECT BY (Oracle)
      • 1.9.2.5. Sorting CONNECT BY results (Oracle)
      • 1.9.2.6. Loops in hierarchical data (Oracle)
      • 1.9.2.7. CONNECT BY functions and operators (Oracle)
  • 1.10. Indexes, Creating
  • 1.11. Indexes, Removing
  • 1.12. Inserting Data
    • 1.12.1. Single-Row Inserts
    • 1.12.2. Multi-Row Inserts (DB2, MySQL)
    • 1.12.3. Insert Targets
    • 1.12.4. Subquery Inserts
    • 1.12.5. Returning Inserted Values (Oracle, DB2)
    • 1.12.6. Multi-Table Inserts (Oracle)
      • 1.12.6.1. Unconditional multi-table insert (Oracle)
      • 1.12.6.2. Conditional multi-table insert (Oracle)
      • 1.12.6.3. ALL versus FIRST (Oracle)
  • 1.13. Joining Tables
    • 1.13.1. The Concept of a Join
    • 1.13.2. Cross Joins
    • 1.13.3. Inner Joins
    • 1.13.4. The USING Clause (Oracle, MySQL, PostgreSQL)
    • 1.13.5. Natural Joins (Oracle, MySQL, PostgreSQL)
    • 1.13.6. Non-Equi-Joins
    • 1.13.7. Outer Joins
      • 1.13.7.1. Left outer joins
      • 1.13.7.2. Interpreting nulls in an outer join
      • 1.13.7.3. Right outer joins
      • 1.13.7.4. Full outer joins
      • 1.13.7.5. Vendor-specific outer join syntax
  • 1.14. Literals
    • 1.14.1. Text Literals
    • 1.14.2. Numeric Literals
    • 1.14.3. Datetime Literals (Oracle, MySQL, PostgreSQL)
    • 1.14.4. Datetime Interval Literals
  • 1.15. Merging Data (Oracle, DB2)
  • 1.16. Nulls
    • 1.16.1. Predicates for Nulls
    • 1.16.2. Using CASE with Nulls
    • 1.16.3. Using the COALESCE Function
    • 1.16.4. Functions for Nulls (Oracle)
    • 1.16.5. Functions for Nulls (DB2)
    • 1.16.6. Functions for Nulls (SQL Server)
    • 1.16.7. Functions for Nulls (MySQL)
    • 1.16.8. Functions for Nulls (PostgreSQL)
  • 1.17. OLAP Functions
  • 1.18. Predicates
    • 1.18.1. EXISTS Predicates
    • 1.18.2. IN Predicates
    • 1.18.3. BETWEEN Predicates
    • 1.18.4. LIKE Predicates
  • 1.19. Recursive Queries
  • 1.20. Regular Expressions
    • 1.20.1. Regular Expressions (Oracle)
    • 1.20.2. Regular Expressions (SQL Server)
    • 1.20.3. Regular Expressions (MySQL)
    • 1.20.4. Regular Expressions (PostgreSQL)
  • 1.21. Selecting Data
    • 1.21.1. The SELECT Clause
      • 1.21.1.1. Listing the columns to retrieve
      • 1.21.1.2. Taking shortcuts with the asterisk
      • 1.21.1.3. Writing expressions
      • 1.21.1.4. Specifying result-set column names
      • 1.21.1.5. Dealing with case and punctuation in names
      • 1.21.1.6. Using subqueries in a SELECT list
      • 1.21.1.7. Qualifying column names
    • 1.21.2. ALL and DISTINCT
    • 1.21.3. The FROM Clause
      • 1.21.3.1. Table aliases in the FROM clause
      • 1.21.3.2. Subqueries in the FROM clause
      • 1.21.3.3. Selecting newly inserted, updated, or deleted rows (DB2)
      • 1.21.3.4. Generating tables through the VALUES clause (DB2)
    • 1.21.4. The WHERE Clause
    • 1.21.5. The GROUP BY Clause
    • 1.21.6. The HAVING Clause
    • 1.21.7. The ORDER BY Clause
  • 1.22. Subqueries
    • 1.22.1. The WITH Clause (Oracle, SQL Server, DB2)
  • 1.23. Tables, Creating
    • 1.23.1. Creating a Table (Oracle)
    • 1.23.2. Creating a Table (DB2)
    • 1.23.3. Creating a Table (SQL Server)
    • 1.23.4. Creating a Table (MySQL)
    • 1.23.5. Creating a Table (PostgreSQL)
  • 1.24. Tables, Modifying
    • 1.24.1. Modifying a Table (Oracle)
    • 1.24.2. Modifying a Table (DB2)
    • 1.24.3. Modifying a Table (SQL Server)
    • 1.24.4. Modifying a Table (MySQL)
    • 1.24.5. Modifying a Table (PostgreSQL)
  • 1.25. Tables, Removing
  • 1.26. Transaction Management
    • 1.26.1. Autocommit Mode (SQL Server, MySQL, PostgreSQL)
    • 1.26.2. Starting a Transaction
      • 1.26.2.1. Starting a transaction (Oracle)
      • 1.26.2.2. Starting a transaction (SQL Server)
      • 1.26.2.3. Starting a transaction (MySQL)
      • 1.26.2.4. Starting a transaction (PostgreSQL)
    • 1.26.3. Ending a Transaction
    • 1.26.4. Aborting a Transaction
    • 1.26.5. Aborting to a Transaction Savepoint
  • 1.27. Union Queries
    • 1.27.1. UNION and UNION ALL
      • 1.27.1.1. UNION
      • 1.27.1.2. UNION ALL
    • 1.27.2. ORDER BY in Union Queries
    • 1.27.3. Names and Datatypes in a Union
    • 1.27.4. Order of Evaluation
    • 1.27.5. EXCEPT (or MINUS)
      • 1.27.5.1. EXCEPT (or MINUS in Oracle)
      • 1.27.5.2. EXCEPT ALL (DB2, PostgreSQL)
    • 1.27.6. INTERSECT
    • 1.27.7. INTERSECT
      • 1.27.7.1. INTERSECT ALL (DB2, PostgreSQL)
  • 1.28. Updating Data
    • 1.28.1. Simple Updates
    • 1.28.2. New Values from a Subquery
    • 1.28.3. Updating Views and Subqueries
    • 1.28.4. UPDATE FROM Clause (SQL Server, PostgreSQL)
    • 1.28.5. Returning Updated Data (Oracle, DB2)
  • 1.29. Window Functions
    • 1.29.1. Defining a Summary Window (Oracle, DB2, SQL Server)
    • 1.29.2. Ordering and Ranking Within a Window (Oracle, DB2, SQL Server)
    • 1.29.3. Comparing Values Across Rows (Oracle)
    • 1.29.4. Summarizing over a Moving Window (Oracle, DB2)
    • 1.29.5. Window Function Syntax (Oracle)
    • 1.29.6. Window Function Syntax (DB2)
    • 1.29.7. Window Function Evaluation and Placement
• About the Author
• SPECIAL OFFER: Upgrade this ebook with OReilly
• Copyright

pokaż cały spis treści

ukryj recenzje