MYSQL IN A NUTSHELL Other resources from O’Reilly Related titles Learning MySQL MySQL Stored Procedure Learning PHP & MySQL Programming Learning PHP 5 PHP Cookbook Learning Perl PHP in a Nutshell Learning SQL Programming PHP MySQL Cookbook Programming the Perl DBI MySQL Pocket Reference SQL Pocket Guide oreilly.com oreilly.com is more than a complete catalog of O’Reilly books. You'll also find links to news, events, articles, weblogs, sample chapters, and code examples. oreillynet.comistheessentialportalfordevelopersinterestedin openandemergingtechnologies,includingnewplatforms,pro- gramming languages, and operating systems. Conferences O’Reilly Media, Inc. brings diverse innovators together to nur- turetheideasthatsparkrevolutionaryindustries.Wespecialize indocumentingthelatesttoolsandsystems,translatingthein- novator’sknowledgeintousefulskillsforthoseinthetrenches. Visitconferences.oreilly.com for our upcoming events. Safari Bookshelf (safari.oreilly.com) is the premier online refer- ence library for programmers and IT professionals. Conduct searchesacrossmorethan1,000books.Subscriberscanzeroin on answers to time-critical questions in a matter of seconds. Read the books on your Bookshelf from cover to cover or sim- ply flip to the page you need. Try it today for free. MYSQL IN A NUTSHELL Second Edition Russell J.T. Dyer Beijing • Cambridge • Farnham • Köln • Paris • Sebastopol • Taipei • Tokyo MySQL in a Nutshell, Second Edition by Russell J.T. Dyer Copyright © 2008 Russell J.T. Dyer. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472 O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://safari.oreilly.com). For more information, contact our corporate/institutional sales department: (800) 998-9938 or [email protected]. Editor: Andy Oram Indexer: Ellen Troutman Zaig Copy Editor: Sarah Schneider Cover Designer: Karen Montgomery Production Editor: Sarah Schneider Interior Designer: David Futato Proofreader: Genevieve d’Entremont Printing History: April 2008: Second Edition May 2005: First Edition Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are registered trade- marks of O’Reilly Media, Inc. MySQL in a Nutshell, the image of a pied kingfisher, and related trade dress are trademarks of O’Reilly Media, Inc. Many of the designations uses by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and O’Reilly Media, Inc. was aware of a trademark claim, the designations have been printed in caps or initial caps. While every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein. ISBN: 978-0-596-51433-4 [C] 1210161771 To my friend Richard Stringer, for encouraging me in literature, liberalism, and writing, and for helping me to become the person I was meant to be. Table of Contents Preface ............................................................. xi Part I. Introduction and Tutorials 1. Introduction to MySQL ............................................ 3 The Value of MySQL 3 The MySQL Package 4 Licensing 4 Mailing Lists 5 Books and Other Publications 6 2. Installing MySQL ................................................. 7 Choosing a Distribution 7 Unix Source Distributions 8 Unix Binary Distributions 10 Linux RPM Distributions 11 Macintosh OS X Distributions 12 Novell NetWare Distributions 14 Windows Distributions 15 Postinstallation 16 3. MySQL Basics ................................................... 19 The mysql Client 19 Creating a Database and Tables 20 Show Me 23 Inserting Data 23 Selecting Data 24 vii Ordering, Limiting, and Grouping 25 Analyzing and Manipulating Data 27 Changing Data 29 Deleting Data 30 Searching Data 31 Importing Data in Bulk 32 Command-Line Interface 34 Conclusion 35 Part II. SQL Statements and Functions 4. Security and User Statements and Functions ........................ 39 Statements and Functions 40 SQL Statements in Alphabetical Order 40 Functions in Alphabetical Order 50 5. Database and Table Schema Statements ............................ 57 Statements and Clauses in Alphabetical Order 57 6. Data Manipulation Statements and Functions ...................... 113 Statements and Functions Grouped by Characteristics 113 Statements and Clauses in Alphabetical Order 114 Functions in Alphabetical Order 152 7. Table and Server Administration Statements and Functions .......... 155 Statements and Clauses in Alphabetical Order 155 Functions in Alphabetical Order 172 8. Replication Statements and Functions ............................ 175 Merits of Replication 175 Replication Process 176 The Replication User Account 183 Configuring the Servers 185 Copying Databases and Starting Replication 187 Starting Replication 189 Backups with Replication 190 SQL Statements and Functions in Alphabetical Order 191 Replication States 198 9. Stored Routines Statements ..................................... 203 Statements in Alphabetical Order 203 viii | Table of Contents