MySQL pocket reference: Includes index

134 Pages·2007·0.857 MB·English

by  Reese, George

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: [phone] [email protected].
Editor: Andy Oram Indexer: Johnna VanHoose Dinse
Production Editor: Cover Designer: Karen Montgomery
Laurel R.T. Ruma Interior Designer: David Futato
Copyeditor: Genevieved’Entremont
Proofreader: Laurel R.T. Ruma
Printing History:
February 2003: First Edition.
July 2007: Second Edition.
NutshellHandbook,theNutshellHandbooklogo,andtheO’Reillylogoare
registered trademarks of O’Reilly Media, Inc. ThePocket Reference series
designations,MySQL Pocket Reference, the image of a kingfisher, and
related trade dress are trademarks of O’Reilly Media, Inc.
Many of the designations used by manufacturers and sellers to distinguish
theirproductsareclaimedastrademarks.Wherethosedesignationsappear
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
publisherandauthorassumenoresponsibilityforerrorsoromissions,orfor
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 1
MySQL 5 2
Views 3
Triggers 3
Stored Procedures 3
Cursors 4
New Storage Engines 4
Database Events 5
Setup 5
Downloading MySQL 5
Configuration 6
Startup 8
Set the Root Password 10
Replication 10
Command-Line Tools 12
Data Types 15
Numerics 16
Strings 21
Dates 26
Complex Types 28
v
SQL 30
Case Sensitivity 31
Literals 31
Identifiers 33
Comments 34
Commands 35
Transaction Rules 86
Operators 87
Rules of Precedence 87
Arithmetic Operators 88
Comparison Operators 89
Logical Operators 91
Functions 91
Aggregate Functions 91
General Functions 93
Storage Engines 114
Stored Procedures and Functions 115
Parameters 116
Logic 117
Handlers and Conditions 122
Triggers 123
Index 125
vi | Contents
Chapter1
MySQL Pocket Reference
Introduction
When I fly across the country, I often pass the hours pro-
gramming on my PowerBook. If that programming involves
MySQL, I inevitably end up lugging around the book I co-
wrote, Managing and Using MySQL (O’Reilly). I don’t carry
aroundthebooktoshowitoff;theproblemisthatnomat-
ter how experienced you are with MySQL, you never know
whenyouwillneedtolookuptheexactsyntaxofanobscure
function or SQL statement.
The MySQL Pocket Reference is a quick reference that you
cantakewithyouanywhereyougo.Insteadofrackingyour
brainfortheexactsyntaxofavariantofALTERTABLEthatyou
generallyneveruse,youcanreachintoyourlaptopcaseand
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
fromabooksuchasManagingandUsingMySQL.ThoughI
startwithareferenceonMySQLsetup,itisdesignedtohelp
yourememberthefullprocessofMySQLconfiguration—not
to teach you the process.
1
Acknowledgments
IfirstwouldliketothankmyeditorAndyOram,asalways,
for helping me along. I would also like to thank the book’s
strongtechnicalreviewers,PaulDubois,JudithMyerson,and
TimAllwine.Finally,Iwouldliketothankmyco-authorsfor
Managing and Using MySQL, Tim King and Randy Jay
Yarger,whohelpedsetthefoundationthatmadethispocket
reference possible and necessary.
Conventions
The following conventions are used in this book:
Constant width
Used to indicate anything that might appear in a pro-
gram, including keywords, function names, SQL com-
mands, and variable names. This font is also used for
codeexamples,outputdisplayedbycommands,andsys-
tem configuration files.
Constant width bold
Used to indicate user input.
Constant width italic
Usedtoindicateanelement(e.g.,afilenameorvariable)
that you supply.
Italic
Used to indicate directory names, filenames, program
names, Unix commands, and URLs. This font is also
used to introduce new terms and for emphasis.
MySQL 5
If you have been using MySQL for a while, you really don’t
need to learn a thing about MySQL 5 to keep going. Every-
thingyouareusedtousingstillworksjustasitalwayshas.
For the most part, MySQL 5 is about adding enterprise
2 | MySQL Pocket Reference