Learning MySQL and MariaDB

Learning MySQL and MariaDB

by Russell J.T. Dyer
Released March 2015
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781449362904

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.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

If you’re a programmer new to databases—or just new to MySQL and its community-driven variant, MariaDB—you’ve found the perfect introduction. This hands-on guide provides an easy, step-by-step approach to installing, using, and maintaining these popular relational database engines.

Author Russell Dyer, Curriculum Manager at MariaDB and former editor of the MySQL Knowledge Base, takes you through database design and the basics of data management and manipulation, using real-world examples and many practical tips. Exercises and review questions help you practice what you’ve just learned.

  • Create and alter MySQL tables and specify fields and columns within them
  • Learn how to insert, select, update, delete, join, and subquery data, using practical examples
  • Use built-in string functions to find, extract, format, and convert text from columns
  • Learn functions for mathematical or statistical calculations, and for formatting date and time values
  • Perform administrative duties such as managing user accounts, backing up databases, and importing large amounts of data
  • Use APIs to connect and query MySQL and MariaDB with PHP and other languages

Publisher resources

View/Submit Errata

Table of contents

  1. Dedication
  2. Foreword
    1. Origins of MySQL
    2. State of MySQL and MariaDB
    3. Beyond the Server
    4. MariaDB: The Differences and Expectations
    5. The Future of MySQL and MariaDB
    6. Your Future in Learning MySQL and MariaDB
    7. Advice on Learning MySQL and MariaDB
  3. Preface
    1. Reading Strategy
    2. Text-Based Interface and Operating Systems
    3. Conventions Used in This Book
    4. Using Code Examples
    5. Safari® Books Online
    6. How to Contact Us
    7. Acknowledgments
  4. I. The Software
    1. 1. Introduction
      1. The Value of MySQL and MariaDB
      2. Mailing Lists and Forums
      3. Other Books and Other Publications
    2. 2. Installing MySQL and MariaDB
      1. The Installation Packages
      2. Licensing
      3. Finding the Software
      4. Choosing a Distribution
      5. The _AMP Alternatives
        1. Linux Binary Distributions
        2. Mac OS X Distributions
        3. Windows Distributions
        4. FreeBSD and Sun Solaris Distributions
        5. Source Distributions
      6. Post-Installation
        1. Special Configuration
        2. Setting Initial Password for root
        3. More on Passwords and Removing Anonymous Users
        4. Creating a User
    3. 3. The Basics and the mysql Client
      1. The mysql Client
      2. Connecting to the Server
      3. Starting to Explore Databases
        1. First SQL Commands
        2. Inserting and Manipulating Data
        3. A Little Complexity
      4. Summary
      5. Exercises
  5. II. Database Structures
    1. 4. Creating Databases and Tables
      1. Creating a Database
      2. Creating Tables
      3. Inserting Data
      4. More Perspectives on Tables
      5. Summary
      6. Exercises
    2. 5. Altering Tables
      1. Prudence When Altering Tables
      2. Essential Changes
        1. Dynamic Columns
      3. Optional Changes
        1. Setting a Column’s Default Value
        2. Setting the Value of AUTO_INCREMENT
        3. Another Method to Alter and Create a Table
        4. Renaming a Table
        5. Reordering a Table
      4. Indexes
      5. Summary
      6. Exercises
  6. III. Basics of Handling Data
    1. 6. Inserting Data
      1. The Syntax
      2. Practical Examples
        1. The Table for Bird Orders
        2. The Table for Bird Families
        3. The Table for Birds
      3. Other Possibilities
        1. Inserting Emphatically
        2. Inserting Data from Another Table
        3. A Digression: Setting the Right ID
        4. Replacing Data
        5. Priorities When Inserting Data
          1. Lowering the priority of an insert
          2. Delaying an INSERT
          3. Raising the priority of an INSERT
      4. Summary
      5. Exercises
    2. 7. Selecting Data
      1. Basic Selection
      2. Selecting by a Criteria
      3. Ordering Results
      4. Limiting Results
      5. Combining Tables
      6. Expressions and the Like
      7. Counting and Grouping Results
      8. Summary
      9. Exercises
    3. 8. Updating and Deleting Data
      1. Updating Data
        1. Updating Specific Rows
        2. Limiting Updates
        3. Ordering to Make a Difference
        4. Updating Multiple Tables
        5. Handling Duplicates
      2. Deleting Data
        1. Deleting in Multiple Tables
      3. Summary
      4. Exercises
    4. 9. Joining and Subquerying Data
      1. Unifying Results
      2. Joining Tables
        1. Selecting a Basic Join
        2. Updating Joined Tables
        3. Deleting Within Joined Tables
      3. Subqueries
        1. Scalar Subqueries
        2. Column Subqueries
        3. Row Subqueries
        4. Table Subqueries
        5. Performance Considerations with Subqueries
      4. Summary
      5. Exercises
  7. IV. Built-In Functions
    1. 10. String Functions
      1. Formatting Strings
        1. Concatenating Strings
        2. Setting Case and Quotes
        3. Trimming and Padding Strings
      2. Extracting Text
      3. Searching Strings and Using Lengths
        1. Locating Text Within a String
        2. String Lengths
        3. Comparing and Searching Strings
        4. Replacing and Inserting into Strings
      4. Converting String Types
      5. Compressing Strings
      6. Summary
      7. Exercises
    2. 11. Date and Time Functions
      1. Date and Time Data Types
      2. Current Date and Time
      3. Extracting Date and Time Components
      4. Formatting Dates and Time
      5. Adjusting to Standards and Time Zones
      6. Adding and Subtracting Dates and Time
      7. Comparing Dates and Times
      8. Summary
      9. Exercises
    3. 12. Aggregate and Numeric Functions
      1. Aggregate Functions
        1. Counting Values
        2. Calculating a Group of Values
        3. Concatenating a Group
      2. Numeric Functions
        1. Rounding Numbers
        2. Rounding Only Down or Up
        3. Truncating Numbers
        4. Eliminating Negative Numbers
      3. Summary
      4. Exercises
  8. V. Administration and Beyond
    1. 13. User Accounts and Privileges
      1. User Account Basics
      2. Restricting the Access of User Accounts
        1. Username and Host
        2. SQL Privileges
        3. Database Components and Privileges
          1. Restricting to specific databases
          2. Restricting to specific tables
          3. Restricting to specific columns
      3. Administrative User Accounts
        1. User Account for Making Backups
        2. User Account for Restoring Backups
        3. User Account for Bulk Importing
        4. User Account to Grant Privileges
      4. Revoking Privileges
      5. Deleting a User Account
      6. Changing Passwords and Names
        1. Setting a User Account Password
        2. Renaming a User Account
      7. User Roles
      8. Summary
      9. Exercises
    2. 14. Backing Up and Restoring Databases
      1. Making Backups
        1. Backing Up All Databases
        2. Understanding Dump Files
        3. Backing Up Specific Databases
        4. Creating Backup Scripts
        5. Backing Up Specific Tables
      2. Restoring Backups
        1. Restoring a Database
        2. Restoring a Table
          1. Modifying a dump file
          2. Restoring with a temporary database
          3. Using a limited user account
        3. Restoring Only Rows or Columns
        4. Recovering from a Binary Log
          1. Finding information in the binary log
          2. Extracting and executing information from the binary log
      3. Developing a Backup Policy
      4. Summary
      5. Exercises
    3. 15. Bulk Importing Data
      1. Preparing to Import
      2. Loading Data Basics
        1. Watching for Warnings
        2. Checking the Accuracy of the Import
        3. Selecting Imported Data
      3. Better Loading
        1. Mapping Fields
        2. Setting Columns
      4. More Field and Line Definitions
        1. Starting, Terminating, and Escaping
        2. Replacing Data Versus Ignoring Errors
      5. Importing from Outside MySQL
        1. Importing Local Files
        2. Using mysqlimport
        3. Importing Without FILE Privileges
      6. Bulk Exporting Data
      7. Summary
      8. Exercises
    4. 16. Application Programming Interfaces
      1. Creating API User Accounts
      2. C API
        1. Connecting to MySQL
        2. Querying MySQL
        3. Complete Minimal C API Program
        4. Compiling with C Includes
      3. Perl DBI
        1. Installing
        2. Connecting to MySQL
        3. Querying MySQL
          1. Selecting data
          2. Updating data
        4. A Full Example with Perl DBI
        5. More Information
      4. PHP API
        1. Installing and Configuring
        2. Connecting to MySQL
        3. Querying MySQL
        4. More Information
      5. Python
        1. Installing
        2. Connecting to MySQL
        3. Querying MySQL
        4. Sample Python Program
        5. More Information
      6. Ruby API
        1. Installing and Preparing MySQL/Ruby
        2. Connecting to MySQL
        3. Querying MySQL
        4. Sample MySQL/Ruby Program
        5. More Information
      7. SQL Injection
      8. Summary
      9. Exercises
  9. Index
  10. About the Author
  11. Colophon
  12. Copyright

Product information

  • Title: Learning MySQL and MariaDB
  • Author(s): Russell J.T. Dyer
  • Release date: March 2015
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781449362904