The Definitive Guide to MySQL

802 Pages·2004·23.867 MB·English

by  Michael Kofler (auth.)

Praise for The Definitive Guide to MySQL, Second Edition:
"In short, it is one of the most exhaustive books on the subject and provides every
thingfor a new user as well as complete reference coverage for an experienced user,
programmer, or administrator. The Definitive Guide to MySQL, Second Edition is
highly recommended for anyone interested in learning, administering, or program
ming MySQL."
-Harold McFarland, The Midwest Book Review (www.midwestbookreview.com)
Praise for the first edition of The Definitive Guide to MySQL:
"There are a number ofb ooks written on the subject ofMySQL databases. I have had
the opportunity to read some and have found many oft hem lacking and confusing.
I happened to get a glimpse ofa book simply called MySQL, it seemed quite inter
esting and as I was about to build a dynamically driven website for a client I felt I
should get this book as the others I have seen were not up to the job. As I suspected
from my chancery glimpse, it was what I needed.
"MySQL by Michael Kofler is a comprehensive guide to MySQL. The book explains
. how to install and configure MySQL on both Linux/UNIX and also on MS
Windows NTI200019x environments. Kofler presents some of the issues one should
consider when using MySQL. The books guides you through SQL programming
and MySQL administration."
-M. Williams, Kemputing (http://kemputing.net)
"Whatever the secret formula that goes into Apress books, the result produced is
clear, thorough, useful, powerful books that are easy to read, grasp, and apply. The
titles are focused on explodingly popular computer topics. MySQL by Michael
Kofler lives up to the high Apress standard."
-International News Agency (www.inewsagency.com)
The Definitive Guide
to MySQl
Second Edition
MICHAEL KOFLER
Translated by DAVID KRAMER
APress Media, LLC
The Definitive Guide to MySQL, Second Edition
Copyright ©2004 by Michael Kofler
Originally published by Apress in 2004
All rights reserved. No part of this work may be reproduced or transmitted in any fonn or by
any means, elecuonic or mechanical, including photocopying. recording. or by any infonnation
storage or retrieval system, without the prior written pennission of the copyright owner and the
publisher.
ISBN 978-1-59059-144-4 ISBN 978-1-4302-0669-9 (eBook)
DOI 10.1007/978-1-4302-0669-9
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names only in an editorial fashion and to the
benefit of the trademark owner, with no intention of infringement of the trademark.
Translator, Editor, and Compositor: David Kramer
Editorial Board: Steve Anglin, Dan Appleman, Craig Berry, Gary Cornell, Tony Davis, Jason
Gilmore, Steven Rycroft, Julian Skinner, Martin Streicher, Jim Sumser, Karen Watterson, Gavin
Wray, John Zukowski
Assistant Publisher: Grace Wong
Production Manager: Kart Brooks
Proofreader: Elizabeth Berry
Cover Designer: Kurt Krames
Manufacturing Manager: Tom Debolski
In the United States: phone 1-800-SPRINGER, email orders~pringer-ny.cOln, or visit
http://_.springer-ny.com. Outside the United States: fax [phone], email
orderstlspringer. de, or visit http://_.springer .de.
For infonnation on translations, please contact Apress directly at 2560 Ninth Street, SUite 219,
BerkeJey, CA 94710. Phone [phone], fax [phone], email info{lapress ,COlI, or visit
http://_ ...p ress .COlLt
The infonnation in this book is distributed on an ~as is» basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author nor Apress shall
have any liability to any person or entity with respect to any loss or damage caused or alleged to
be caused directly or indirectly by the infonnation contained in this work.
Contents at a Glance
About the Author xi
About the Translator xii
Preface xiii
I Introduction 1
1 What Is MySQU 3
2 The Test Environment 21
3 Introductory Example (An Opinion Poll) 67
4 User Interfaces 85
II Fundamentals 83
5 Database Design 121
6 An Introduction to SQL 177
7 SQLRecipes 199
8 InnoDB Tables and Transactions 239
9 Security 261
10 Administration and Server Configuration 305
III Programming 373
11 PUP: Fundamentals and Programming Techniques 375
12 PUP: Library Management 413
13 PUP: Discussion Forum 467
14 Perl 495
15 Java, JSP' JDBC 523
16 CandC++ 555
17 Visual Basic, C#, ODBC 577
IV Reference 639
18 SQL Reference 641
19 MySQLToois 703
20 API Reference 733
V Appendices 759
A Glossary 761
B MySQL4.1 767
C Example Files 781
D Bibliography 783
Index 785
ill
Contents
About the Author xi
About the Translator xii
Preface xiii
What Does This Book Offer? . xiii
What Is New in the Second Edition? xiv
What Does This Book Not Offer? xv
Example Programs, Source Code xv
Versions .. xvi
Notation ... xvii
Commands xviii
Abbreviations xviii
I Introduction 1
1 WhatIsMySQU 3
What Is a Database? . 4
MySQL ....... . 6
Features of MySQL 7
Limitations ofMySQL . 9
MySQL Version Numbers 11
MySQL Licensing . . . 14
Alternatives to MySQL . 18
Summary ....... 19
2 The Test Environment 21
Wmdows or Unix/Linux? 22
MySQL (Server Installation) . 23
MySQL (Client Installation) 37
Apache ... . 38
PHP .... . 46
phpMyAdmin 53
Perl ...... 55
Connector/ODBC 62
Editors ...... 63
Using MySQL with an ISP 64
v
Contents
3 Introductory Example (An Opinion Poll) 67
Overview ....... . 68
Database Development . . . . . . . . . . 69
The Questionnaire. . . . . . . . . . . . . 75
Questionnaire Evaluation and Displaying Results 76
Ideas for Improvements . . . . . . . . . . . . . . 80
II Fundamentals 83
4 User Interfaces 85
Overview ........... . 86
mysql ............. . 87
WinMySQLadmin (Windows) . 93
MySQL Control Center (Windows, Linux) 97
phpMyAdmin (HTML) ......... . 101
5 Database Design 121
Introduction . . . 122
Database Theory 123
MySQL Data Types 137
Indexes ..... . 146
MySQL Table Types 151
Creating Databases, Tables, and Indexes 155
Example mylibrary (Library) ..... 158
Example myforum (Discussion Group) 170
Example exceptions (Special Cases) . 174
6 An Introduction to SQL 177
Introduction . . . . . . . 178
Simple Queries (SELECT) 179
Queries with Related Tables . 184
Uniting Query Results . . . . 188
Grouped Queries, Aggregate Functions . 189
Altering Data (INSERT, UPDATE, and DELETE) 191
7 SQL Recipes 199
Character Strings ... . . . . . . . . 200
Date and Time . . . . . . . . . . . . . 204
Variables and Conditionals (IF, CASE) 207
Copying Data from One Table to Another. 210
Pivot Tables . . . . . . . . . . 212
SubSELECTs . . . . . . . . . . 216
Ensuring the Integrity of Data. 219
Tracking Down Duplicates .. 224
Bringing a Table into Normal Form . 225
Processing the First or Last n Records 227
Selecting Random Records 229
Full-Text Search . . . . . . . . . . . . 231
vi
Contents
8 InnoDB Tables and 1ransactlons 239
Introduction . . . . . . . . . . . . . . . . . ...... 240
First Experiments with InnoDB Tables 244
'Ii'ansactions . . . . . . . . . . . . . . 245
Integrity'Rules (Foreign Key Constraints) . 255
9 Security 261
Introduction . 262
FirstAid ... 267
The Internal Workings of the Access System 274
Tools for Setting Access Privileges. . . . . 290
Problems with Establishing a Connection . 296
System Security . . . . . . . . . . . . . . . 301
10 Administration and Server Configuration 305
Basic Administration 306
Backups .............. . 310
Database Migration . . . . . . . . . 316
Importing and Exporting Text Files . 320
International Customization, Character Sets . 331
Logging ............ . 335
Replication ............ . 342
Maintenance ofMyISAM Tables . 353
Administration ofInnoDB Tables . 357
Server 1\ming . . . . . . . . 365
ISP Database Administration 369
III Programming 373
11 PHP: Fundamentals and Programming Techniques 375
Introduction . . . . . . . . . . . . . . . . . . . . . 376
Establishing a Connection to the Database . . . . 378
Executing SQL Commands . . . . . . . . . . 383
SELECT Queries . . . . . . . . . . . . . . . . . . 385
Changes to Data (INSERT, UPDATE, DELETE) . . 390
Character Strings, BWBs, DATEs, SETs, ENUMs,
andNULL. . . . . . .. . .... 391
Error Checking ....... . 403
Storing and Displaying Images 404
12 PHP: UbraryManagement 413
Introduction . . . . . . . . . . . . . . . . . . . 414
Auxiliary Functions (mylibraryconnect.inc.php) 414
Book Search (jind.php) ..... . . . . . . . . 416
Simple Input of New Books (simpleinput.php) . 430
Convenient Input of New Book Data (input. php) . 433
Managing the Book Categories (categories.php) 455
Ideas for Improvements and Extensions . . . . . 465
vii
Contents
13 PUP: Discussion Forum 467
How to Run It . . . . . . 468
Overview of the Program Code 471
Registration (forumlogin. php) . 473
Defining a New User (forumnewlogin. php) 475
Display list of All Discussion Groups (forumlist. php) 477
Message List of a Discussion Group (forumread. php) . 478
Representation of Message Text (forummessage. php) 484
Displaying a Thread (forumthread. php) . . . . . . . 486
Contributing to a Discussion (forumwri te • php) . 487
Leaving the Forum (forumlogout. php) 492
Possibilities for Extension . . . . . . . . . . . . 492
14 Perl 495
Programming Techniques . . . . . . . . . . . . . . . 496
Example: Deleting Invalid Data Records (mylibrary) . 512
CGI Example: Library Management (mylibrary) 514
15 Java, JSP' JDBC 523
Introduction . . . . . . . . 524
Programming Techniques . 528
Java Server Pages OSP) .. 548
16 C and C++ 555
The C Application Programming Interface 556
Connector/C++ (MySQL++) . . .... 567
17 Visual Basic, C#, ODBC 577
Fundamentals . . . . . . 578
Access .......... 584
Data Analysis with Excel. 590
ADO Programming and Visual Basic/VBA 592
MyVbQL (Visual Basic Programming Without ADO/ODBC) . 612
ADO.NET (Visual Basic .NET, C#) . . . . . . . . . 616
Converter: Microsoft SQL Server MySQL . . . . . . . . . 635
-t
IV Reference 639
18 SQL Reference 641
Syntax ......... . 642
Operators ....... . 647
Variables and Constants . 651
MySQL Data Types ... 653
Command Overview (Thematic) . 655
Command Reference (Alphabetical) 657
Function Reference . . . . . . . . . . 692
viii
Contents
19 MySQL Tools 703
Overview ................................... . 704
Common Options and Configuration Files . . . . . . . . . . . . . . . . . . 705
mysqld(Server) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 711
mysqld_safe(Server Start) .......................... . 718
mysql_ install_d b(New Installation of mysql Database) . . . . . . . . . . . 719
mysql_f ix_ privilegesCUpdating the mysql Database) . . . . . . . . . . . . 719
mysql_f ix_ extension 5 (Renaming MylSAM Files) ............. . 720
mysql(SQL Command Interpreter) ..................... . 720
mysqladmin(Administration) . . . . . . . . . . . . . . . . . . . . . . . . . . 723
mysqldump(Backup/Export) . . . . . . . . . . . . . . . . . . . . . . . . . . 724
mysqlimport(Text Import, Bulk Import) . . . . . . . . . . . . . . . . . . . . 727
mysqlshow(Displaying Information) . . . . . . . . . . . . . . . . . . . . . . 728
mysqlbug(Sending Error Notification) . . . . . . . . . . . . . . . . . . . . . 728
myisarrichk(Repairing MylSAM Files) .................... . 729
myisampack(Compressing MyISAM Files) . . . . . . . . . . . . . . . . . . . 731
20 API Reference 733
PHPAPI ......... . 734
PerlDBI ......... . 738
JDBC (Connector/J) . . . . . . 745
CAPI ............... . 752
V Appendices 759
A Glossary 761
B MySQL4.1 767
C Example Files 781
D Bibliography 783
Index 785
ix