Mysql Pocket Reference

MySQL
Pocket Reference

SECOND EDITION

MySQL
Pocket Reference

George Reese

Beijing • Cambridge • Farnham • Köln • Paris • Sebastopol • Taipei • Tokyo

MySQL Pocket Reference, Second Edition
by George Reese Copyright © 2007, 2003 George Reese. All rights reserved. Printed in Canada. 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 (safari.oreilly.com). For more information, contact our corporate/ institutional sales department: (800) 998-9938 or corporate@oreilly.com.

Editor: Andy Oram Production Editor:
Laurel R.T. Ruma

Indexer: Johnna VanHoose Dinse Cover Designer: Karen Montgomery InteriorDesigner: David Futato

Copyeditor: Genevieve d’Entremont Proofreader: Laurel R.T. Ruma Printing History:
February 2003: July 2007: First Edition. Second Edition.

Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are registered trademarks of O’Reilly Media, Inc. The Pocket Reference series designations, MySQL Pocket Reference, the image of a kingfisher, and related tradedress are trademarks of O’Reilly Media, Inc. Many of the designations used 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, thepublisher and author assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein.

ISBN-10: 0-596-51426-3 ISBN-13: 978-0-596-51426-6 [TM]

Contents

Introduction MySQL 5 Views Triggers Stored Procedures Cursors New Storage Engines Database Events Setup Downloading MySQL Configuration Startup Set the Root Password ReplicationCommand-Line Tools Data Types Numerics Strings Dates Complex Types

1 2 3 3 3 4 4 5 5 5 6 8 10 10 12 15 16 21 26 28

v

SQL Case Sensitivity Literals Identifiers Comments Commands Transaction Rules Operators Rules of Precedence Arithmetic Operators Comparison Operators Logical Operators Functions Aggregate Functions General Functions Storage Engines Stored Procedures and Functions ParametersLogic Handlers and Conditions Triggers Index

30 31 31 33 34 35 86 87 87 88 89 91 91 91 93 114 115 116 117 122 123 125

vi | Contents

Chapter 1

MySQL Pocket Reference

Introduction
When I fly across the country, I often pass the hours programming on my PowerBook. If that programming involves MySQL, I inevitably end up lugging around the book I cowrote, Managing and Using MySQL(O’Reilly). I don’t carry around the book to show it off; the problem is that no matter how experienced you are with MySQL, you never know when you will need to look up the exact syntax of an obscure function or SQL statement. The MySQL Pocket Reference is a quick reference that you can take with you anywhere you go. Instead of racking your brain for the exact syntax of a variant of ALTER TABLE that yougenerally never use, you can reach into your laptop case and grab this reference. As an experienced MySQL architect, administrator, or programmer, you can look to this reference. This book does not, however, teach MySQL. I expect that you have learned or are in the process of learning MySQL from a book such as Managing and Using MySQL. Though I start with a reference on MySQL setup, it is designedto help you remember the full process of MySQL configuration—not to teach you the process.

1

Acknowledgments
I first would like to thank my editor Andy Oram, as always, for helping me along. I would also like to thank the book’s strong technical reviewers, Paul Dubois, Judith Myerson, and Tim Allwine. Finally, I would like to thank my co-authors for Managing and Using MySQL, Tim King...