MySQL Cookbook_ Solutions for Database Developers and Administrators (3rd ed.) [DuBois 2014-08-18].pdf

(4144 KB) Pobierz
MySQL
Cookbook
SOLUTIONS FOR DATABASE DEVELOPERS AND ADMINISTRATORS
Paul DuBois
Re
3r
d
Ed
iti
on
te
d
da
Up
d
an
vi
se
d
MySQL Cookbook
MySQL’s popularity has brought a flood of questions about how to solve
specific problems, and that’s where this cookbook is essential. When you
need quick solutions or techniques, this handy resource provides scores
of short, focused pieces of code, hundreds of worked-out examples, and
clear, concise explanations for programmers who don’t have the time (or
expertise) to solve MySQL problems from scratch.
Ideal for beginners and professional database and web developers, this
updated third edition covers powerful features in MySQL 5.6 (and some
in 5.7). The book focuses on programming APIs in Python, PHP, Java, Perl,
and Ruby. With more than 200+ recipes, you’ll learn how to:
Use the mysql client and write MySQL-based programs
Create, populate, and select data from tables
Store, retrieve, and manipulate strings
Work with dates and times
Sort query results and generate summaries
Use stored routines, triggers, and scheduled events
Import, export, validate, and reformat data
Perform transactions and work with statistics
Process web input, and generate web content from
query results
Use MySQL-based web session management
Provide security and server administration
“
Atrueclassic,thisremains
thebestcollectionof
MySQLrecipesavailable.
Thisbookcoversbasics
neededbybeginners,
andpresentsthevery
latestdevelopmentsthat
advanceduserscanuse
todeepentheirknowledge.
Andit’scrowdedwith
tipsthatmakeiteven
morevaluabletoMySQL
professionals.
■
■
■
■
■
■
■
■
■
”
Senior Software Engineer for MySQL and
co-author of the mysqlnd PHP library
—Ulf Wendel,
■
■
Paul DuBois
is one of the primary contributors to the MySQL Reference Manual,
a renowned online manual that has supported MySQL administrators and
database developers for years. He’s a member of the MySQL documentation
team at Oracle and author of several books.
DATABA SES
Twitter: @oreillymedia
facebook.com/oreilly
CAN $83.99
US $79.99
ISBN: 978-1-449-37402-0
THIRD EDITION
MySQL Cookbook
Paul DuBois
MySQL Cookbook , Third Edition
by Paul DuBois
Copyright © 2014 Paul DuBois and O’Reilly Media, Inc.. 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://my.safaribooksonline.com). For more information, contact our corporate/
institutional sales department: 800-998-9938 or
corporate@oreilly.com.
Editors:
Andy Oram and Allyson MacDonald
Production Editor:
Nicole Shelby
Proofreader:
Kim Cofer
Indexer:
Lucie Haskins
October 2002:
November 2006:
August 2014:
First Edition
Second Edition
Third Edition
Cover Designer:
Randy Comer
Interior Designer:
David Futato
Illustrator:
Rebecca Demarest
Revision History for the Third Edition:
2014-07-25:
First release
See
http://oreilly.com/catalog/errata.csp?isbn=9781449374020
for release details.
Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are registered trademarks of O’Reilly
Media, Inc.
MySQL Cookbook,
the picture of a green anole, and related trade dress 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, 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-1-449-37402-0
[LSI]
Table of Contents
Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
1. Using the mysql Client Program. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.1. Setting Up a MySQL User Account
1.2. Creating a Database and a Sample Table
1.3. What to Do if mysql Cannot Be Found
1.4. Specifying mysql Command Options
1.5. Executing SQL Statements Interactively
1.6. Executing SQL Statements Read from a File or Program
1.7. Controlling mysql Output Destination and Format
1.8. Using User-Defined Variables in SQL Statements
2
4
6
8
13
15
17
22
2. Writing MySQL-Based Programs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
2.1. Connecting, Selecting a Database, and Disconnecting
2.2. Checking for Errors
2.3. Writing Library Files
2.4. Executing Statements and Retrieving Results
2.5. Handling Special Characters and NULL Values in Statements
2.6. Handling Special Characters in Identifiers
2.7. Identifying NULL Values in Result Sets
2.8. Techniques for Obtaining Connection Parameters
2.9. Conclusion and Words of Advice
29
42
51
65
79
89
91
95
103
3. Selecting Data from Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
3.1. Specifying Which Columns and Rows to Select
3.2. Naming Query Result Columns
3.3. Sorting Query Results
3.4. Removing Duplicate Rows
3.5. Working with NULL Values
106
108
112
113
114
iii

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin