Learning MySQL

Learning MySQL

by Saied M.M. Tahaghoghi, Hugh E. Williams
Released November 2006
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596008642

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
Start your free trial

Book description

Whether you're running a business, keeping track of members andmeetings for a club, or just trying to organize a large and diversecollection of information, you'll find the MySQL database engineuseful for answering questions such as:

  • Which are my top ten fastest-selling products?
  • How frequently does this person come to our facility?
  • What was the highest, lowest, and average score of the team last season?


MySQL, the most popular open-source database, offers the power ofa relational database in a package that's easy to set up andadminister, and Learning MySQL provides all the tools you need toget started. This densely packed tutorial includes detailedinstructions to help you set up and design an effective database,create powerful queries using SQL, configure MySQL for improvedsecurity, and squeeze information out of your data.



After covering the basics, the book travels far into MySQL'ssubtleties, including complex queries and joins, how to interact withthe database over the Web using PHP or Perl, and importanthouse-keeping such as backups and security.



Topic include:

  • Installation on Linux, Windows, and Mac OS X
  • Basic and advanced querying using SQL
  • User management and security
  • Backups and recovery
  • Tuning for improved efficiency
  • Developing command-line and web database applications using thePHP and Perl programming languages


The authors, Saied Tahaghoghi and Hugh E. Williams, have careers inacademia and business, and share a keen interest in research intosearch technologies.



Whether you've never touched a database or have already completedsome MySQL projects, you'll find insights in Learning MySQLthat will last a career.

Table of contents

  1. Learning MySQL
  2. A Note Regarding Supplemental Files
  3. Preface
    1. Who This Book Is for
    2. What’s in the Book
      1. Introduction
      2. Using MySQL
      3. Advanced Topics
      4. Web Database Applications with PHP
      5. Interacting with MySQL Using Perl
      6. Appendix
    3. Conventions Used in This Book
    4. Resources
    5. Using Code Examples
    6. Safari® Enabled
    7. How to Contact Us
    8. Acknowledgments
      1. Saied Tahaghoghi
      2. Hugh Williams
  4. I. Introduction
    1. 1. Introduction
      1. Why Is MySQL so Popular?
      2. Elements of MySQL and Its Environment
        1. The LAMP Platform
        2. Structured Query Language
      3. MySQL Software Covered in This Book
      4. The Book’s Web Site
    2. 2. Installing MySQL
      1. Installation Choices and Platforms
        1. Linux
          1. Live CDs
        2. Windows
        3. Mac OS X
        4. So, What Should I Do?
      2. Using the Command-Line Interface
        1. The Linux and Mac OS X Shell
          1. Command completion and history
          2. Performing restricted operations
          3. Restricting access to files and directories
        2. The Windows Command Prompt
          1. Command completion and history
      3. Using a Text Editor
      4. Following the Instructions in This Book
      5. Downloading and Verifying Files from the MySQL AB Web Site
        1. Downloading MySQL from the MySQL AB Web Site
        2. Verifying Package Integrity with MD5
      6. Installing Under Linux
        1. Installing MySQL on Linux Using RPM Packages from MySQL AB
        2. Installing MySQL on Linux Using a gzipped Tar Archive from MySQL AB
        3. Installing MySQL on Linux by Compiling the Source Code from MySQL AB
        4. Installing MySQL, Apache, PHP, and Perl on Linux Using Distribution Packages
          1. Installation on Red Hat and Fedora Core
          2. Installation on Mandriva
          3. Installing under Debian-based systems
          4. Uninstalling MySQL
        5. Installing MySQL, Apache, PHP, and Perl on Linux Using the XAMPP Integrated Package
        6. Configuring a Newly Installed Server
          1. Configuring a server installed using RPM or Debian packages
          2. Configuring a system-wide server installed from tarball or source
          3. Configuring a local server
          4. Configuring MySQL for automatic start
      7. Installing Under Windows
        1. Installing Only MySQL Using Packages from MySQL AB
          1. Windows installation using the installer
          2. Starting and stopping MySQL as a service
          3. Starting and stopping MySQL from the command line
        2. Installation with the “no-install” .zip Archive
        3. Installing MySQL, Apache, PHP, and Perl on Windows Using the XAMPP Integrated Package
      8. Installing Under Mac OS X
        1. Installing only MySQL Using the Installer from MySQL AB
          1. Configuring the installed server
        2. Installing Only MySQL Using the no-installer Package from MySQL AB
        3. Installing MySQL, Apache, PHP, and Perl on Mac OS X Using the XAMPP Integrated Package
      9. Using a MySQL Installation Provided by an ISP
      10. Upgrading an Existing MySQL Server
        1. Should I Upgrade to MySQL 5.1?
        2. How to Upgrade
        3. Steps to Upgrade an Existing MySQL Server
      11. Configuring Access to the MySQL Server
      12. What If Things Don’t Work?
        1. Can’t Download Files from Behind a Proxy
        2. Error Message About MySQL Executable Programs Not Being Found or Recognized
        3. Error Message Running mysql_install_db
        4. Server Doesn’t Start
        5. Client Programs Can’t Connect to the Server
        6. Server Doesn’t Stop
      13. The Contents of the MySQL Directory
      14. Configuring and Controlling the Apache Web Server
        1. The Apache Document Root
        2. The Apache Configuration File
        3. The Apache Error Log
        4. Starting and Stopping Apache
        5. Checking Whether Your Apache Installation Supports PHP
      15. Setting up Perl
        1. Checking Your Existing Setup
        2. Installing the Perl DBI and CGI Modules
          1. Installing Perl modules under Linux
          2. Installing Perl modules under Windows
          3. Installing Perl modules under Mac OS X
          4. Problems installing the Perl modules
      16. Resources
      17. Exercises
    3. 3. Using the MySQL Monitor
      1. Starting the Monitor
      2. Style, Case, and Semicolons
      3. The Monitor Help
      4. Running the Monitor in Batch Mode
      5. Loading the Sample Databases
      6. MySQL Monitor Program Options
      7. Graphical Clients
      8. Exercises
  5. II. Using MySQL
    1. 4. Modeling and Designing Databases
      1. How Not to Develop a Database
      2. The Database Design Process
      3. The Entity Relationship Model
        1. Representing Entities
        2. Representing Relationships
        3. Partial and Total Participation
        4. Entity or Attribute?
        5. Entity or Relationship?
        6. Intermediate Entities
        7. Weak and Strong Entities
      4. Entity Relationship Modeling Examples
        1. The Music Database
          1. What it doesn’t do
        2. The University Database
          1. What it doesn’t do
        3. The Flight Database
          1. What it doesn’t do
      5. Using the Entity Relationship Model
        1. Mapping Entities and Relationships to Database Tables
          1. Map the entities to database tables
          2. Map the relationships to database tables
        2. Converting the Music Database ER Model to a Database Schema
      6. Using Tools for Database Design
      7. Resources
      8. Exercises
    2. 5. Basic SQL
      1. Using the Music Database
      2. The SELECT Statement and Basic Querying Techniques
        1. Single Table SELECTs
        2. Choosing Columns
        3. Choosing Rows with the WHERE Clause
          1. WHERE basics
          2. Combining conditions with AND, OR, NOT, and XOR
        4. ORDER BY Clauses
        5. The LIMIT Clause
        6. Joining Two Tables
      3. The INSERT Statement
        1. INSERT Basics
        2. Alternative Syntaxes
      4. The DELETE Statement
        1. DELETE Basics
        2. Using WHERE, ORDER BY, and LIMIT
        3. Removing All Rows with TRUNCATE
      5. The UPDATE Statement
        1. Examples
        2. Using WHERE, ORDER BY, and LIMIT
      6. Exploring Databases and Tables with SHOW and mysqlshow
      7. Exercises
    3. 6. Working with Database Structures
      1. Creating and Using Databases
      2. Creating Tables
        1. Basics
        2. Collation and Character Sets
        3. Other Features
        4. Column Types
          1. Common column types
          2. Other integer types
          3. Other rational number types
          4. Other date and time types
          5. Other string types
        5. Keys and Indexes
        6. The AUTO_INCREMENT Feature
      3. The Sample Music Database
      4. Altering Structures
        1. Adding, Removing, and Changing Columns
        2. Adding, Removing, and Changing Indexes
        3. Renaming Tables and Altering Other Structures
      5. Deleting Structures
        1. Dropping Databases
        2. Removing Tables
      6. Exercises
    4. 7. Advanced Querying
      1. Aliases
        1. Column Aliases
        2. Table Aliases
      2. Aggregating Data
        1. The DISTINCT Clause
        2. The GROUP BY Clause
          1. Other aggregate functions
        3. The HAVING Clause
      3. Advanced Joins
        1. The Inner Join
        2. The Union
        3. The Left and Right Joins
        4. The Natural Join
      4. Nested Queries
        1. Nested Query Basics
        2. The ANY, SOME, ALL, IN, and NOT IN Clauses
          1. Using ANY and IN
          2. Using ALL
          3. Writing row subqueries
        3. The EXISTS and NOT EXISTS Clauses
          1. EXISTS and NOT EXISTS basics
          2. Correlated subqueries
        4. Nested Queries in the FROM Clause
      5. User Variables
      6. Transactions and Locking
      7. Table Types
        1. MyISAM
        2. Memory or Heap
        3. InnoDB
          1. Transaction examples
        4. BDB
      8. Exercises
    5. 8. Doing More with MySQL
      1. Inserting Data Using Queries
      2. Loading Data from Comma-Delimited Files
      3. Writing Data into Comma-Delimited Files
      4. Creating Tables with Queries
      5. Updates and Deletes with Multiple Tables
        1. Deletion
        2. Updates
      6. Replacing Data
      7. The EXPLAIN Statement
      8. Exercises
    6. 9. Managing Users and Privileges
      1. Understanding Users and Privileges
      2. Creating and Using New Users
      3. Privileges
      4. The GRANT OPTION Privilege
      5. How Privileges Interact
      6. Users and Hosts
        1. Local and Remote Users
        2. Creating a New Remote User
        3. Anonymous Users
        4. Which User Is Connected?
      7. Checking Privileges
        1. mysqlaccess
          1. Configuring mysqlaccess
      8. Revoking Privileges
      9. Removing Users
      10. Understanding and Changing Passwords
      11. The Default Users
        1. Default User Configuration
          1. Linux and Mac OS X
          2. Windows
        2. Securing the Default Users
      12. Devising a User Security Policy
        1. Choosing Users and Privileges
        2. More Security Tips
        3. Resource-Limit Controls
        4. The mysql_setpermission Program
      13. Managing Privileges with SQL
        1. The user Table
        2. The db Table
        3. The tables_priv Table
        4. The columns_priv Table
        5. The host Table
        6. Activating Privileges
      14. Privileges and Performance
      15. Resetting Forgotten MySQL Passwords
      16. Exercises
  6. III. Advanced Topics
    1. 10. Backups and Recovery
      1. Dumping a Database as SQL Statements
        1. mysqldump Options
      2. Loading Data from an SQL Dump File
      3. mysqlhotcopy
      4. Scheduling Backups
        1. Linux and Mac OS X
        2. Windows XP
        3. General Backup Tips
      5. The Binary Log
      6. Checking and Repairing Corrupted Tables
        1. mysqlcheck
        2. myisamchk
      7. Re-Creating Damaged Grant Tables
      8. Resources
      9. Exercises
    2. 11. Using an Options File
      1. Configuring Options for the MySQL Monitor
      2. Structure of the Options File
      3. Scope of Options
      4. Search Order for Options Files
      5. Determining the Options in Effect
      6. Exercises
    3. 12. Configuring and Tuning the Server
      1. The MySQL Server Daemon
        1. MySQL Server Options
        2. Examples
      2. Server Variables
        1. The Slow Query Log
        2. Query Caching
        3. The Old Variables Format
      3. Checking Server Settings
      4. Other Things to Consider
      5. Resources
      6. Exercises
  7. IV. Web Database Applications with PHP
    1. 13. Web Database Applications
      1. Building a Web Database Application
        1. How Web Software Works
        2. Three-Tier Architectures
      2. The Apache Web Server
        1. Web Server Index Files
      3. Introducing PHP
        1. Example: Displaying the Artists from the Music Collection
      4. Using a PHP-Enabled Web Hosting Site
      5. Resources
      6. Exercises
    2. 14. PHP
      1. Language Basics
        1. Strings
        2. Arrays
        3. Manipulating Variables
        4. Displaying Information
        5. Conditional Statements
        6. Loops
        7. Functions
          1. Passing variables by reference
        8. Handling Errors in PHP
      2. Accessing MySQL Using PHP
        1. The Original PHP MySQL Library
        2. The PHP Improved MySQL Library
        3. What’s New in MySQLi
        4. Accessing Query Results with mysql_fetch_array() and mysqli_fetch_array()
        5. Finding the Number of Changed Rows Using mysql_affected_rows and mysqli_affected_rows
        6. Handling MySQL Errors
          1. Handling errors using the MySQLi library
          2. Handling errors using the older MySQL library
          3. Handling errors in production code
          4. Writing files and sending emails using PHP
      3. Modularizing Code
        1. Protecting Script and Header Files
      4. Processing and Using User Data
      5. The PHP Predefined Superglobal Variables
      6. Untainting User Data
        1. Limiting the Size and Type of Input Data
        2. Abusing Calls to Shell Commands
        3. Preventing SQL Injection Attacks
        4. Using Data from the Client
      7. Sessions
      8. The Reload Problem
      9. Using PHP for Command-Line Scripts
        1. Using Command-Line Arguments
      10. Resources
      11. Exercises
    3. 15. A PHP Application: The Wedding Gift Registry
      1. Designing and Creating the Wedding Database
      2. The Login Form
        1. Using One Script for the Form and for Processing
      3. Passing a Message to a Script
      4. Logging Users In and Out
        1. Verifying New Users
        2. Authenticating the User
        3. Starting the User Session
        4. Logging the User Out
      5. The db.php Include File
      6. Editing the List of Gifts
        1. Restricting Edit Access
        2. Role-Based Authentication
        3. The Gift Editing Form
        4. Deleting a Gift
        5. Processing the Submitted Form
      7. Loading Sample Gifts
      8. Listing Gifts for Selection
      9. Selecting and Deselecting Gifts
        1. Adding a Gift
        2. Removing a Gift
      10. Resources
      11. Exercises
  8. V. Interacting with MySQL Using Perl
    1. 16. Perl
      1. Writing Your First Perl Program
      2. Scripting With Perl
        1. Mathematical Operators
          1. Operator precedence
        2. More on Variables
          1. Single and double quotes
        3. Arrays and Hashes
        4. Control Structures: Loops and Conditionals
        5. Iterating Through Arrays and Hashes
        6. Conditional Statements
        7. Reading Input from the Command Line and from Files
          1. Reading in values from the command line
          2. Reading in values from a file
          3. Reading in values from standard input
          4. Writing values to a file or standard output
        8. Writing Your Own Perl Functions
      3. Resources
      4. Exercises
    2. 17. Using Perl with MySQL
      1. Connecting to the MySQL Server and Database
      2. Handling Errors When Interacting with the Database
      3. Using Queries That Return Answer Sets
      4. Using Queries That Don’t Return Answer Sets
      5. Binding Queries and Variables
        1. Binding Variables to a Query
        2. Binding Query Results to Variables
        3. The Complete Script Using Both Types of Binding
      6. Importing and Exporting Data
      7. Handling NULL Values
      8. Resources
      9. Exercises
    3. 18. Serving Perl Pages to the Web
      1. The Perl CGI Module
      2. Processing User Input
        1. Using One Script for the Form and for Processing
      3. A Note on mod_perl
      4. Perl Security
      5. Resources
      6. Exercises
  9. VI. Appendix
    1. A. The Wedding Registry Code
  10. Index
  11. About the Authors
  12. Colophon
  13. Copyright

Product information

  • Title: Learning MySQL
  • Author(s): Saied M.M. Tahaghoghi, Hugh E. Williams
  • Release date: November 2006
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596008642