Learning MySQL by Tahaghoghi, Seyed M.M. -- Read -- Imperial Library of Trantor

Index

Learning MySQL

SPECIAL OFFER: Upgrade this ebook with O’Reilly A Note Regarding Supplemental Files Preface

Who This Book Is for What’s in the Book

Introduction Using MySQL Advanced Topics Web Database Applications with PHP Interacting with MySQL Using Perl Appendix

Conventions Used in This Book Resources Using Code Examples Safari® Enabled How to Contact Us Acknowledgments

Saied Tahaghoghi Hugh Williams

I. Introduction

1. Introduction

Why Is MySQL so Popular? Elements of MySQL and Its Environment

The LAMP Platform Structured Query Language

MySQL Software Covered in This Book The Book’s Web Site

2. Installing MySQL

Installation Choices and Platforms

Linux

Live CDs

Windows Mac OS X So, What Should I Do?

Using the Command-Line Interface

The Linux and Mac OS X Shell

Command completion and history Performing restricted operations Restricting access to files and directories

The Windows Command Prompt

Command completion and history

Using a Text Editor Following the Instructions in This Book Downloading and Verifying Files from the MySQL AB Web Site

Downloading MySQL from the MySQL AB Web Site Verifying Package Integrity with MD5

Installing Under Linux

Installing MySQL on Linux Using RPM Packages from MySQL AB Installing MySQL on Linux Using a gzipped Tar Archive from MySQL AB Installing MySQL on Linux by Compiling the Source Code from MySQL AB Installing MySQL, Apache, PHP, and Perl on Linux Using Distribution Packages

Installation on Red Hat and Fedora Core Installation on Mandriva Installing under Debian-based systems Uninstalling MySQL

Installing MySQL, Apache, PHP, and Perl on Linux Using the XAMPP Integrated Package Configuring a Newly Installed Server

Configuring a server installed using RPM or Debian packages Configuring a system-wide server installed from tarball or source Configuring a local server Configuring MySQL for automatic start

Installing Under Windows

Installing Only MySQL Using Packages from MySQL AB

Windows installation using the installer Starting and stopping MySQL as a service Starting and stopping MySQL from the command line

Installation with the “no-install” .zip Archive Installing MySQL, Apache, PHP, and Perl on Windows Using the XAMPP Integrated Package

Installing Under Mac OS X

Installing only MySQL Using the Installer from MySQL AB

Configuring the installed server

Installing Only MySQL Using the no-installer Package from MySQL AB Installing MySQL, Apache, PHP, and Perl on Mac OS X Using the XAMPP Integrated Package

Using a MySQL Installation Provided by an ISP Upgrading an Existing MySQL Server

Should I Upgrade to MySQL 5.1? How to Upgrade Steps to Upgrade an Existing MySQL Server

Configuring Access to the MySQL Server What If Things Don’t Work?

Can’t Download Files from Behind a Proxy Error Message About MySQL Executable Programs Not Being Found or Recognized Error Message Running mysql_install_db Server Doesn’t Start Client Programs Can’t Connect to the Server Server Doesn’t Stop

The Contents of the MySQL Directory Configuring and Controlling the Apache Web Server

The Apache Document Root The Apache Configuration File The Apache Error Log Starting and Stopping Apache Checking Whether Your Apache Installation Supports PHP

Setting up Perl

Checking Your Existing Setup Installing the Perl DBI and CGI Modules

Installing Perl modules under Linux Installing Perl modules under Windows Installing Perl modules under Mac OS X Problems installing the Perl modules

Resources Exercises

3. Using the MySQL Monitor

Starting the Monitor Style, Case, and Semicolons The Monitor Help Running the Monitor in Batch Mode Loading the Sample Databases MySQL Monitor Program Options Graphical Clients Exercises

II. Using MySQL

4. Modeling and Designing Databases

How Not to Develop a Database The Database Design Process The Entity Relationship Model

Representing Entities Representing Relationships Partial and Total Participation Entity or Attribute? Entity or Relationship? Intermediate Entities Weak and Strong Entities

Entity Relationship Modeling Examples

The Music Database

What it doesn’t do

The University Database

What it doesn’t do

The Flight Database

What it doesn’t do

Using the Entity Relationship Model

Mapping Entities and Relationships to Database Tables

Map the entities to database tables Map the relationships to database tables

Converting the Music Database ER Model to a Database Schema

Using Tools for Database Design Resources Exercises

5. Basic SQL

Using the Music Database The SELECT Statement and Basic Querying Techniques

Single Table SELECTs Choosing Columns Choosing Rows with the WHERE Clause

WHERE basics Combining conditions with AND, OR, NOT, and XOR

ORDER BY Clauses The LIMIT Clause Joining Two Tables

The INSERT Statement

INSERT Basics Alternative Syntaxes

The DELETE Statement

DELETE Basics Using WHERE, ORDER BY, and LIMIT Removing All Rows with TRUNCATE

The UPDATE Statement

Examples Using WHERE, ORDER BY, and LIMIT

Exploring Databases and Tables with SHOW and mysqlshow Exercises

6. Working with Database Structures

Creating and Using Databases Creating Tables

Basics Collation and Character Sets Other Features Column Types

Common column types Other integer types Other rational number types Other date and time types Other string types

Keys and Indexes The AUTO_INCREMENT Feature

The Sample Music Database Altering Structures

Adding, Removing, and Changing Columns Adding, Removing, and Changing Indexes Renaming Tables and Altering Other Structures

Deleting Structures

Dropping Databases Removing Tables

Exercises

7. Advanced Querying

Aliases

Column Aliases Table Aliases

Aggregating Data

The DISTINCT Clause The GROUP BY Clause

Other aggregate functions

The HAVING Clause

Advanced Joins

The Inner Join The Union The Left and Right Joins The Natural Join

Nested Queries

Nested Query Basics The ANY, SOME, ALL, IN, and NOT IN Clauses

Using ANY and IN Using ALL Writing row subqueries

The EXISTS and NOT EXISTS Clauses

EXISTS and NOT EXISTS basics Correlated subqueries

Nested Queries in the FROM Clause

User Variables Transactions and Locking Table Types

MyISAM Memory or Heap InnoDB

Transaction examples

BDB

Exercises

8. Doing More with MySQL

Inserting Data Using Queries Loading Data from Comma-Delimited Files Writing Data into Comma-Delimited Files Creating Tables with Queries Updates and Deletes with Multiple Tables

Deletion Updates

Replacing Data The EXPLAIN Statement Exercises

9. Managing Users and Privileges

Understanding Users and Privileges Creating and Using New Users Privileges The GRANT OPTION Privilege How Privileges Interact Users and Hosts

Local and Remote Users Creating a New Remote User Anonymous Users Which User Is Connected?

Checking Privileges

mysqlaccess

Configuring mysqlaccess

Revoking Privileges Removing Users Understanding and Changing Passwords The Default Users

Default User Configuration

Linux and Mac OS X Windows

Securing the Default Users

Devising a User Security Policy

Choosing Users and Privileges More Security Tips Resource-Limit Controls The mysql_setpermission Program

Managing Privileges with SQL

The user Table The db Table The tables_priv Table The columns_priv Table The host Table Activating Privileges

Privileges and Performance Resetting Forgotten MySQL Passwords Exercises

III. Advanced Topics

10. Backups and Recovery

Dumping a Database as SQL Statements

mysqldump Options

Loading Data from an SQL Dump File mysqlhotcopy Scheduling Backups

Linux and Mac OS X Windows XP General Backup Tips

The Binary Log Checking and Repairing Corrupted Tables

mysqlcheck myisamchk

Re-Creating Damaged Grant Tables Resources Exercises

11. Using an Options File

Configuring Options for the MySQL Monitor Structure of the Options File Scope of Options Search Order for Options Files Determining the Options in Effect Exercises

12. Configuring and Tuning the Server

The MySQL Server Daemon

MySQL Server Options Examples

Server Variables

The Slow Query Log Query Caching The Old Variables Format

Checking Server Settings Other Things to Consider Resources Exercises

IV. Web Database Applications with PHP

13. Web Database Applications

Building a Web Database Application

How Web Software Works Three-Tier Architectures

The Apache Web Server

Web Server Index Files

Introducing PHP

Example: Displaying the Artists from the Music Collection

Using a PHP-Enabled Web Hosting Site Resources Exercises

14. PHP

Language Basics

Strings Arrays Manipulating Variables Displaying Information Conditional Statements Loops Functions

Passing variables by reference

Handling Errors in PHP

Accessing MySQL Using PHP

The Original PHP MySQL Library The PHP Improved MySQL Library What’s New in MySQLi Accessing Query Results with mysql_fetch_array() and mysqli_fetch_array() Finding the Number of Changed Rows Using mysql_affected_rows and mysqli_affected_rows Handling MySQL Errors

Handling errors using the MySQLi library Handling errors using the older MySQL library Handling errors in production code Writing files and sending emails using PHP

Modularizing Code

Protecting Script and Header Files

Processing and Using User Data The PHP Predefined Superglobal Variables Untainting User Data

Limiting the Size and Type of Input Data Abusing Calls to Shell Commands Preventing SQL Injection Attacks Using Data from the Client

Sessions The Reload Problem Using PHP for Command-Line Scripts

Using Command-Line Arguments

Resources Exercises

15. A PHP Application: The Wedding Gift Registry

Designing and Creating the Wedding Database The Login Form

Using One Script for the Form and for Processing

Passing a Message to a Script Logging Users In and Out

Verifying New Users Authenticating the User Starting the User Session Logging the User Out

The db.php Include File Editing the List of Gifts

Restricting Edit Access Role-Based Authentication The Gift Editing Form Deleting a Gift Processing the Submitted Form

Loading Sample Gifts Listing Gifts for Selection Selecting and Deselecting Gifts

Adding a Gift Removing a Gift

Resources Exercises

V. Interacting with MySQL Using Perl

16. Perl

Writing Your First Perl Program Scripting With Perl

Mathematical Operators

Operator precedence