MySQL Phrasebook

TheMySQL Phrasebookis a pocket guide that is jam-packed with useful and essential code "phrases" for the MySQL developer's everyday use. Packed with practical solutions for tasks that the MySQL developer must accomplish every day, it fills the need for a short, functional, to-the-point reference for MySQL. This is the guide to consult when you need an immediate, applicable solution to frequent tasks and code that is flexible and adaptable to your needs. Your time won't be wasted on more tutorials -- it simply puts essential "phrases" at your fingertips that you can take with you everywhere.

Zak Greant is a technical evangelist, author and programmer whose deep and constant love of Free Software and Open Source has made him an admired and sought-after speaker, developer and advocate. Zak currently works at eZ Systems where he helps businesses and Free/Open projects create and execute plans for their strategic, marketing, human resource, product, service, and information technology management activities.

Chris Newman is a consultant programmer specializing in database development with an Internet twist. He has extensive commercial experience of using PHP to integrate various database systems and has produced a wide range of applications for an international client base. He runs Lightwood Consultancy Ltd, the company he founded in 1999 to further his interest in online database development. Newman has served as a technical editor on several books from Sams, including PHP and MySQL Web Development, Teach Yourself PHP, MySQL & Apache All in One, and Red Hat Fedora Unleashed. He is a frequent contributor to PHPBuilder.com and also wrote SQLite and Sams Teach Yourself PHP in 10 Minutes.

Introduction

1

(1)

Omissions: What This Book Doesn't Cover

2

(1)

Conventions Used in This Book

2

(1)

The MySQL Phrasebook Website

3

(2)

Maps of MySQL

5

(6)

The MySQL RDBMS

6

(2)

The LAMP Platform

8

(1)

MySQL Table Terminology

8

(2)

Sample Tables

10

(1)

Creating and Managing Databases, Tables, and Indexes

11

(18)

Quick Guide: Databases, Tables, Columns, and Indexes

11

(4)

Creating and Managing Databases

15

(5)

Creating and Managing Tables

20

(3)

Creating and Managing Columns

23

(3)

Creating and Managing Indexes

26

(3)

Storing Data

29

(14)

Adding Data to a Table

29

(2)

Adding Many Rows in One Query Using INSERT

31

(1)

Assigning Unique Row Numbers with auto_increment

31

(2)

Inserting the Current Date and Time (Using a MySQL Function)

33

(1)

Loading SQL Commands from a File

34

(1)

Inserting Data from Another Table

35

(1)

Importing Data from Text Files

35

(4)

Inserting Data Rapidly

39

(4)

Retrieving Data: Simple Queries

43

(12)

Limiting the Number of Rows Returned

43

(1)

Sorting Result Sets

44

(2)

Ignoring Duplicate Rows

46

(1)

Finding Text That Matches a Pattern

46

(2)

Finding the Smallest, Largest, or Average Value in a Column

48

(1)

Date and Time Manipulation

49

(2)

Storing Exact Floating Point Numbers

51

(1)

Dealing with NULL

52

(1)

Sending Query Results to a File

53

(1)

Writing a BLOB to a File

54

(1)

Manipulating Data

55

(10)

Safety Tips

55

(3)

Testing Queries Inside of a Transaction

58

(1)

An Overview of Updating Column Values

59

(2)

Updating Columns with Formulas

61

(1)

Deleting Rows

61

(1)

Deleting Rows from Multiple Tables

62

(3)

User Management and Security

65

(16)

Creating Users

66

(2)

Deleting Users

68

(1)

Renaming a User

69

(1)

Using Wildcards

69

(1)

Finding Users of a Given Database or Table

70

(1)

Setting Passwords

71

(1)

Tailoring User Permissions

72

(3)

Allowing Other Users to Grant Privileges

75

(1)

Viewing a User's Privileges

75

(1)

Removing User Access

76

(1)

Disabling Network Access

77

(1)

Disabling User Authentication

77

(1)

Using SSL-Secured Connection

78

(3)

The mysql Command-line Client

81

(10)

Connecting to a MySQL Server

82

(1)

Using the Client in Batch Mode

83

(1)

Basic mysql Command-line Client Navigation

83

(2)

Erasing the Current Line or Query

85

(1)

Setting Connection Defaults with an Option File

85

(2)

Getting Help Within mysql

87

(1)

Editing Complex Commands with a Text Editor

88

(1)

Database, Table, and Column Name Completion

89

(1)

Using the Command-line History

89

(1)

Other Useful Tools

90

(1)

MySQL APIs

91

(18)

The C Language API

91

(7)

The Perl API

98

(4)

The PHP API

102

(7)

Advanced Queries

109

(12)

Joins

109

(5)

Subqueries

114

(4)

Combining Queries with UNION

118

(3)

Troubleshooting and Emergencies

121

(40)

Backing Up

121

(3)

Restoring from a Backup

124

(2)

Corrupted Table Data

126

(1)

Server Crash

127

(2)

Common Errors

129

(3)

Getting Help

132

(3)

Appendix

A A Brief MySQL Tutorial

135

(26)

Getting Started

136

(1)

Creating a Database

136

(2)

Creating Tables

138

(8)

Populating Tables with Data

146

(7)

Exploring Table Creation

153

(2)

Adding Indexes to Existing Tables

155

(1)

Finding Information About Tables

155

(4)

Creating Users

159

(2)

Index

161

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Summary

Author Biography

Table of Contents

Supplemental Materials