MySQL® Crash Course

MySQL® Crash Course

Released December 2005
Publisher(s): Sams
ISBN: 9780672327124

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

MySQL is one of the most popular database management systems available, powering everything from Internet powerhouses to individual corporate databases to simple end-user applications, and everything in between. And this book will teach you all you need to know to be immediately productive with MySQL. By working through 30 highly focused hands-on lessons, your MySQL Crash Course will be both easier and more effective than you'd have thought possible. Learn how to:

  • Retrieve and sort data

  • Filter data using comparisons, regular expressions, full text search, and much more

  • Join relational data

  • Create and alter tables

  • Insert, update, and delete data

  • Leverage the power of stored procedures and triggers

  • Use views and Cursors

  • Manage transactional processing

  • Create user accounts and manage security via access control

Ben Forta is Macromedia's Senior Technical Evangelist, and has almost 20 years of experience in the computer industry in product development, support, training, and product marketing. Ben is the author of the best-selling Sams Teach Yourself SQL in 10 Minutes (now in its third edition, and translated into over a dozen languages), ColdFusion Web Application Construction Kit, and Advanced ColdFusion Development (both published by Que Publishing), Sams Teach Yourself Regular Expressions in 10 Minutes, as well as books on SQL, Flash, JSP, HomeSite, WAP, Windows 2000, and other subjects.

Table of contents

  1. Copyright
  2. About the Author
  3. Acknowledgments
  4. We Want to Hear from You!
  5. Introduction
    1. Who Is This Book For?
    2. Companion Website
    3. Conventions Used in This Book
  6. 1. Understanding SQL
    1. Database Basics
      1. What Is a Database?
      2. Tables
      3. Columns and Datatypes
      4. Rows
      5. Primary Keys
    2. What Is SQL?
    3. Try It Yourself
    4. Summary
  7. 2. Introducing MySQL
    1. What Is MySQL?
      1. Client-Server Software
      2. MySQL Versions
    2. MySQL Tools
      1. mysql Command-Line Utility
      2. MySQL Administrator
      3. MySQL Query Browser
    3. Summary
  8. 3. Working with MySQL
    1. Making the Connection
    2. Selecting a Database
    3. Learning About Databases and Tables
    4. Summary
  9. 4. Retrieving Data
    1. The SELECT Statement
    2. Retrieving Individual Columns
    3. Retrieving Multiple Columns
    4. Retrieving All Columns
    5. Retrieving Distinct Rows
    6. Limiting Results
    7. Using Fully Qualified Table Names
    8. Summary
  10. 5. Sorting Retrieved Data
    1. Sorting Data
    2. Sorting by Multiple Columns
    3. Specifying Sort Direction
    4. Summary
  11. 6. Filtering Data
    1. Using the WHERE Clause
    2. The WHERE Clause Operators
      1. Checking Against a Single Value
      2. Checking for Nonmatches
      3. Checking for a Range of Values
      4. Checking for No Value
    3. Summary
  12. 7. Advanced Data Filtering
    1. Combining WHERE Clauses
      1. Using the AND Operator
      2. Using the OR Operator
      3. Understanding Order of Evaluation
    2. Using the IN Operator
    3. Using the NOT Operator
    4. Summary
  13. 8. Using Wildcard Filtering
    1. Using the LIKE Operator
      1. The Percent Sign (%) Wildcard
      2. The Underscore (_) Wildcard
    2. Tips for Using Wildcards
    3. Summary
  14. 9. Searching Using Regular Expressions
    1. Understanding Regular Expressions
    2. Using MySQL Regular Expressions
      1. Basic Character Matching
      2. Performing OR Matches
      3. Matching One of Several Characters
      4. Matching Ranges
      5. Matching Special Characters
      6. Matching Character Classes
      7. Matching Multiple Instances
      8. Anchors
    3. Summary
  15. 10. Creating Calculated Fields
    1. Understanding Calculated Fields
    2. Concatenating Fields
      1. Using Aliases
    3. Performing Mathematical Calculations
    4. Summary
  16. 11. Using Data Manipulation Functions
    1. Understanding Functions
    2. Using Functions
      1. Text Manipulation Functions
      2. Date and Time Manipulation Functions
      3. Numeric Manipulation Functions
    3. Summary
  17. 12. Summarizing Data
    1. Using Aggregate Functions
      1. The AVG() Function
      2. The COUNT() Function
      3. The MAX() Function
      4. The MIN() Function
      5. The SUM() Function
    2. Aggregates on Distinct Values
    3. Combining Aggregate Functions
    4. Summary
  18. 13. Grouping Data
    1. Understanding Data Grouping
    2. Creating Groups
    3. Filtering Groups
    4. Grouping and Sorting
    5. SELECT Clause Ordering
    6. Summary
  19. 14. Working with Subqueries
    1. Understanding Subqueries
    2. Filtering by Subquery
    3. Using Subqueries As Calculated Fields
    4. Summary
  20. 15. Joining Tables
    1. Understanding Joins
      1. Understanding Relational Tables
      2. Why Use Joins?
    2. Creating a Join
      1. The Importance of the WHERE Clause
      2. Inner Joins
      3. Joining Multiple Tables
    3. Summary
  21. 16. Creating Advanced Joins
    1. Using Table Aliases
    2. Using Different Join Types
      1. Self Joins
      2. Natural Joins
      3. Outer Joins
    3. Using Joins with Aggregate Functions
    4. Using Joins and Join Conditions
    5. Summary
  22. 17. Combining Queries
    1. Understanding Combined Queries
    2. Creating Combined Queries
      1. Using UNION
      2. UNION Rules
      3. Including or Eliminating Duplicate Rows
      4. Sorting Combined Query Results
    3. Summary
  23. 18. Full-Text Searching
    1. Understanding Full-Text Searching
    2. Using Full-Text Searching
      1. Enabling Full-Text Searching Support
      2. Performing Full-Text Searches
      3. Using Query Expansion
      4. Boolean Text Searches
      5. Full-Text Search Usage Notes
    3. Summary
  24. 19. Inserting Data
    1. Understanding Data Insertion
    2. Inserting Complete Rows
    3. Inserting Multiple Rows
    4. Inserting Retrieved Data
    5. Summary
  25. 20. Updating and Deleting Data
    1. Updating Data
    2. Deleting Data
    3. Guidelines for Updating and Deleting Data
    4. Summary
  26. 21. Creating and Manipulating Tables
    1. Creating Tables
      1. Basic Table Creation
      2. Working with NULL Values
      3. Primary Keys Revisited
      4. Using AUTO_INCREMENT
      5. Specifying Default Values
      6. Engine Types
    2. Updating Tables
    3. Deleting Tables
    4. Renaming Tables
    5. Summary
  27. 22. Using Views
    1. Understanding Views
      1. Why Use Views
      2. View Rules and Restrictions
    2. Using Views
      1. Using Views to Simplify Complex Joins
      2. Using Views to Reformat Retrieved Data
      3. Using Views to Filter Unwanted Data
      4. Using Views with Calculated Fields
      5. Updating Views
    3. Summary
  28. 23. Working with Stored Procedures
    1. Understanding Stored Procedures
    2. Why Use Stored Procedures
    3. Using Stored Procedures
      1. Executing Stored Procedures
      2. Creating Stored Procedures
      3. Dropping Stored Procedures
      4. Working with Parameters
      5. Building Intelligent Stored Procedures
      6. Inspecting Stored Procedures
    4. Summary
  29. 24. Using Cursors
    1. Understanding Cursors
    2. Working with Cursors
      1. Creating Cursors
      2. Opening and Closing Cursors
      3. Using Cursor Data
    3. Summary
  30. 25. Using Triggers
    1. Understanding Triggers
    2. Creating Triggers
    3. Dropping Triggers
    4. Using Triggers
      1. INSERT Triggers
      2. DELETE Triggers
      3. UPDATE Triggers
      4. More on Triggers
    5. Summary
  31. 26. Managing Transaction Processing
    1. Understanding Transaction Processing
    2. Controlling Transactions
      1. Using ROLLBACK
      2. Using COMMIT
      3. Using Savepoints
      4. Changing the Default Commit Behavior
    3. Summary
  32. 27. Globalization and Localization
    1. Understanding Character Sets and Collation Sequences
    2. Working with Character Set and Collation Sequences
    3. Summary
  33. 28. Managing Security
    1. Understanding Access Control
    2. Managing Users
      1. Creating User Accounts
      2. Deleting User Accounts
      3. Setting Access Rights
      4. Changing Passwords
    3. Summary
  34. 29. Database Maintenance
    1. Backing Up Data
    2. Performing Database Maintenance
    3. Diagnosing Startup Problems
    4. Review Log Files
    5. Summary
  35. 30. Improving Performance
    1. Improving Performance
    2. Summary
  36. A. Getting Started with MySQL
    1. What You’ll Need
    2. Obtaining the Software
    3. Installing the Software
    4. Preparing for Your Chapters
  37. B. The Example Tables
    1. Understanding the Sample Tables
      1. Table Descriptions
      2. The vendors Table
      3. The products Table
      4. The customers Table
      5. The orders Table
      6. The orderitems Table
      7. The productnotes Table
    2. Creating the Sample Tables
  38. C. MySQL Statement Syntax
    1. ALTER TABLE
    2. COMMIT
    3. CREATE INDEX
    4. CREATE PROCEDURE
    5. CREATE TABLE
    6. CREATE USER
    7. CREATE VIEW
    8. DELETE
    9. DROP
    10. INSERT
    11. INSERT SELECT
    12. ROLLBACK
    13. SAVEPOINT
    14. SELECT
    15. START TRANSACTION
    16. UPDATE
  39. D. MySQL Datatypes
    1. String Datatypes
    2. Numeric Datatypes
    3. Date and Time Datatypes
    4. Binary Datatypes
  40. E. MySQL Reserved Words
  41. Developer’s Library

Product information

  • Title: MySQL® Crash Course
  • Author(s):
  • Release date: December 2005
  • Publisher(s): Sams
  • ISBN: 9780672327124