Learning MySQL and MariaDB · Heading in the Right Direction with MySQL and MariaDB by Dyer, Russell J.T. -- Read -- Imperial Library of Trantor

Index

Learning MySQL and MariaDB Dedication Foreword

Origins of MySQL State of MySQL and MariaDB Beyond the Server MariaDB: The Differences and Expectations The Future of MySQL and MariaDB Your Future in Learning MySQL and MariaDB Advice on Learning MySQL and MariaDB

Preface

Reading Strategy Text-Based Interface and Operating Systems Conventions Used in This Book Using Code Examples Safari® Books Online How to Contact Us Acknowledgments

I. The Software

1. Introduction

The Value of MySQL and MariaDB Mailing Lists and Forums Other Books and Other Publications

2. Installing MySQL and MariaDB

The Installation Packages Licensing Finding the Software Choosing a Distribution The _AMP Alternatives

Linux Binary Distributions Mac OS X Distributions Windows Distributions FreeBSD and Sun Solaris Distributions Source Distributions

Post-Installation

Special Configuration Setting Initial Password for root More on Passwords and Removing Anonymous Users Creating a User

3. The Basics and the mysql Client

The mysql Client Connecting to the Server Starting to Explore Databases

First SQL Commands Inserting and Manipulating Data A Little Complexity

Summary Exercises

II. Database Structures

4. Creating Databases and Tables

Creating a Database Creating Tables Inserting Data More Perspectives on Tables Summary Exercises

5. Altering Tables

Prudence When Altering Tables Essential Changes

Dynamic Columns

Optional Changes

Setting a Column’s Default Value Setting the Value of AUTO_INCREMENT Another Method to Alter and Create a Table Renaming a Table Reordering a Table

Indexes Summary Exercises

III. Basics of Handling Data

6. Inserting Data

The Syntax Practical Examples

The Table for Bird Orders The Table for Bird Families The Table for Birds

Other Possibilities

Inserting Emphatically Inserting Data from Another Table A Digression: Setting the Right ID Replacing Data Priorities When Inserting Data

Lowering the priority of an insert Delaying an INSERT Raising the priority of an INSERT

Summary Exercises

7. Selecting Data

Basic Selection Selecting by a Criteria Ordering Results Limiting Results Combining Tables Expressions and the Like Counting and Grouping Results Summary Exercises

8. Updating and Deleting Data

Updating Data

Updating Specific Rows Limiting Updates Ordering to Make a Difference Updating Multiple Tables Handling Duplicates

Deleting Data

Deleting in Multiple Tables

Summary Exercises

9. Joining and Subquerying Data

Unifying Results Joining Tables

Selecting a Basic Join Updating Joined Tables Deleting Within Joined Tables

Subqueries

Scalar Subqueries Column Subqueries Row Subqueries Table Subqueries Performance Considerations with Subqueries

Summary Exercises

IV. Built-In Functions

10. String Functions

Formatting Strings

Concatenating Strings Setting Case and Quotes Trimming and Padding Strings

Extracting Text Searching Strings and Using Lengths

Locating Text Within a String String Lengths Comparing and Searching Strings Replacing and Inserting into Strings

Converting String Types Compressing Strings Summary Exercises

11. Date and Time Functions

Date and Time Data Types Current Date and Time Extracting Date and Time Components Formatting Dates and Time Adjusting to Standards and Time Zones Adding and Subtracting Dates and Time Comparing Dates and Times Summary Exercises

12. Aggregate and Numeric Functions

Aggregate Functions

Counting Values Calculating a Group of Values Concatenating a Group

Numeric Functions

Rounding Numbers Rounding Only Down or Up Truncating Numbers Eliminating Negative Numbers

Summary Exercises

V. Administration and Beyond

13. User Accounts and Privileges

User Account Basics Restricting the Access of User Accounts

Username and Host SQL Privileges Database Components and Privileges

Restricting to specific databases Restricting to specific tables Restricting to specific columns

Administrative User Accounts

User Account for Making Backups User Account for Restoring Backups User Account for Bulk Importing User Account to Grant Privileges

Revoking Privileges Deleting a User Account Changing Passwords and Names

Setting a User Account Password Renaming a User Account

User Roles Summary Exercises

14. Backing Up and Restoring Databases

Making Backups

Backing Up All Databases Understanding Dump Files Backing Up Specific Databases Creating Backup Scripts Backing Up Specific Tables

Restoring Backups

Restoring a Database Restoring a Table

Modifying a dump file Restoring with a temporary database Using a limited user account

Restoring Only Rows or Columns Recovering from a Binary Log

Finding information in the binary log Extracting and executing information from the binary log

Developing a Backup Policy Summary Exercises

15. Bulk Importing Data

Preparing to Import Loading Data Basics

Watching for Warnings Checking the Accuracy of the Import Selecting Imported Data

Better Loading

Mapping Fields Setting Columns

More Field and Line Definitions

Starting, Terminating, and Escaping Replacing Data Versus Ignoring Errors

Importing from Outside MySQL

Importing Local Files Using mysqlimport Importing Without FILE Privileges

Bulk Exporting Data Summary Exercises

16. Application Programming Interfaces

Creating API User Accounts C API

Connecting to MySQL Querying MySQL Complete Minimal C API Program Compiling with C Includes

Perl DBI

Installing Connecting to MySQL Querying MySQL

Selecting data Updating data

A Full Example with Perl DBI More Information

PHP API

Installing and Configuring Connecting to MySQL Querying MySQL More Information

Python

Installing Connecting to MySQL Querying MySQL Sample Python Program More Information

Ruby API

Installing and Preparing MySQL/Ruby Connecting to MySQL Querying MySQL Sample MySQL/Ruby Program More Information

SQL Injection Summary Exercises

Index About the Author Colophon Copyright

← Prev
Back
Next →

← Prev
Back
Next →