MySQL: The Complete Reference / Edition 1

by Vikram Vaswani, Pamela Smith

Overview

Publisher's Note: Products purchased from Third Party sellers are not guaranteed by the publisher for quality, authenticity, or access to any online entitlements included with the product.


Get comprehensive coverage of all the powerful new features of MySQL, one of the fastest—and free—relational databases in use today

Written in conjunction with the MySQL development team, this expert resource covers transactional integrity, disaster recovery, scalability, support for mobile users, Web-based and client/server programming, and much more.

Product Details

ISBN-13:	9780072224771
Publisher:	McGraw Hill LLC
Publication date:	01/28/2004
Series:	Osborne Complete Reference Series
Pages:	544
Product dimensions:	7.30(w) x 9.10(h) x 1.45(d)

About the Author

Vikram Vaswani is the founder and CEO of Melonfire (http://www.melonfire.com/), a company specializing in software consultancy and content creation and syndication services. He is also the author of PHP Programming Solutions, How to do Everything with PHP and MySQL, and MySQL: The Complete Reference (all McGraw-Hill/Osborne titles) and XML and PHP (New Riders Publishing). He is also the author of numerous well-received articles on open-source technologies (including Perl, Python, XML and the very popular PHP 101 series), all written with the goal of making complex technologies accessible and understandable to novice users. He has been developing software since 1995, was first introduced to PHP in 1998, and hasn't looked back since. He is regular columnist with Zend Technologies (creators of PHP), as well as with IBM DeveloperWorks, CNET Builder.com, DevX.com and other OSS sites.

Table of Contents

	Foreword	xi
	Acknowledgments	xiii
	Introduction	xv
Part I	Installation
1	Introduction to Databases	3
	The Big Picture	3
	...And the Little Database that Could	6
	History and Evolution	6
	Features	7
	Applications	11
	Summary	12
2	A Technical Tour of MySQL	13
	An Overview of MySQL Architecture	13
	Primary Subsystems	13
	Support Components	16
	Subsystem/Component Interaction and Control Flow	16
	The MySQL Engine	17
	Connectivity	17
	SQL	17
	Data Integrity	19
	Transactions	19
	Extensibility	22
	Symmetric Multiprocessing with MySQL	23
	Security	24
	Replication	25
	Application Programming Interfaces	25
	Add-On Tools	26
	Summary	26
3	MySQL Installation and Configuration	27
	Obtaining MySQL	27
	Choosing Which Version to Install	28
	Choosing Between Binary and Source Distributions	28
	Installing and Configuring MySQL	30
	Installing and Configuring MySQL on UNIX	30
	Installing and Configuring MySQL on Windows	40
	Testing MySQL	49
	Post-Installation Steps	51
	Summary	52
Part II	Usage
4	SQL Basics	55
	A Brief History of SQL	55
	An Overview of SQL	56
	A (My)SQL Tutorial	57
	Understanding an RDBMS	57
	Creating a Database	59
	Adding Tables	60
	Adding Records	63
	Removing and Modifying Records	64
	Executing Queries	65
	Summary	74
5	MySQL Data Types	75
	The Need and Rationale for Data Types	75
	MySQL Data Types	76
	Numeric Types	77
	String Types	82
	Date and Time Types	85
	Complex Types	93
	Data Type Selection	97
	Summary	99
6	MySQL Operators	101
	Arithmetic Operators	102
	Comparison Operators	106
	Logical Operators	119
	Bit Operators	122
	Summary	125
7	MySQL Functions	127
	Math Functions	128
	Aggregate Functions	133
	String Functions	137
	Date and Time Functions	144
	Encryption Functions	153
	Control Flow Functions	157
	Formatting Functions	162
	Type Conversion Functions	164
	System Information Functions	166
	Summary	171
8	Working with Databases and Tables	173
	Creating Databases	173
	Selecting Databases for Use	175
	Deleting Databases	176
	Creating Tables	176
	Field Types	177
	Field Constraints	177
	Indexes	180
	Primary Keys	186
	Foreign Keys	187
	Table Types	196
	Other Table Modifiers	199
	Copying Tables	200
	Modifying Tables	204
	Deleting Tables	209
	Obtaining Information About Databases, Tables, Fields, and Indexes	209
	Summary	210
9	Working with Data	213
	Inserting, Updating, and Deleting Records	213
	Inserting Records	213
	Updating Records	220
	Deleting Records	223
	Retrieving Records	226
	Retrieving Specific Rows and Columns	227
	Using Built-In Functions	228
	Aliasing Table and Column Names	230
	Limiting Query Results	230
	Sorting Query Results	231
	Grouping Query Results	233
	Using Variables	235
	Using Subqueries	236
	Controlling SELECT Behavior	237
	Copying, Importing, and Exporting Records	239
	Copying Records	239
	Importing Records	241
	Exporting Records	244
	Summary	247
10	Joins	249
	What Is a Join?	249
	Types of Joins	252
	Cross Joins	253
	Inner Joins	254
	Outer Joins	256
	Self Joins	262
	Unions	265
	Summary	267
11	Subqueries	269
	What Is a Subquery?	270
	Types of Subqueries	274
	Subqueries and the Where/Having Clause	274
	Subqueries and the From Clause	282
	Subqueries and Joins	284
	Subqueries and Other DML Statements	286
	Summary	290
12	Transactions	291
	What Is a Transaction?	292
	Transactions and the ACID Properties	294
	Life Cycle of a Transaction	296
	Controlling Transactional Behavior	300
	Automatic Commits	300
	Transaction Isolation Levels	302
	Transactions and Performance	307
	Pseudotransactions with Nontransactional Tables	310
	Table Locks as a Substitute for Transactions	311
	Implementing a Pseudotransaction with Table Locks	315
	Summary	316
Part III	Administration
13	Administration and Configuration	319
	Database Administration and MySQL	319
	Uptime	320
	Data Backup	320
	Security and Access Control	321
	Performance Optimization	321
	Basic Server Administration and Configuration Tasks	322
	Starting and Stopping the Server	323
	Checking MySQL Server Status	326
	Managing MySQL Client Processes	327
	Altering the Server Configuration	328
	Troubleshooting with the Error Log	333
	Summary	334
14	Security, Access Control, and Privileges	335
	The MySQL Grant Tables	335
	The user Table	336
	The db and host Tables	340
	The tables_priv and columns_priv Tables	342
	Granting, Revoking, and Viewing User Privileges	345
	Using the Grant and Revoke Commands	346
	Using the Insert, Update, and Delete Commands	351
	Viewing Privileges	352
	Reloading the Grant Tables	352
	Resetting the Grant Tables	353
	Changing User Passwords	353
	Setting the root Password	355
	Resetting the root Password	355
	Summary	356
15	Maintenance, Backup, and Recovery	357
	Maintenance	357
	Logging	357
	Checking and Repairing Tables	361
	Backup and Restore	365
	Backing Up Databases and Tables	365
	Restoring Databases and Tables from Backup	367
	Summary	369
16	Performance Optimization	371
	Indexing	371
	Query Caching	374
	Query Analysis	376
	Optimizing Multi-Table Queries	378
	Using Temporary Tables	380
	Optimizing Table Design	381
	Adjusting Server Settings	382
	Benchmarking	383
	Summary	388
17	MySQL Replication	389
	Replication Basics	389
	The Master-Slave Relationship	390
	Replication Threads	390
	Configuring MySQL for Replication	391
	Managing the Replication Process	393
	Summary	398
Part IV	Development
18	The MySQL APIs	401
	The MySQL APIs In Context	401
	Components of the MySQL API	402
	Language Support	403
	Selecting an API	405
	Summary	409
19	MySQL and C	411
	C	411
	History and Evolution	411
	Installation	412
	MySQL and C	412
	Connection Management	417
	Query Execution	419
	Result Set Processing	421
	Error Handling	429
	Ancillary Functions	431
	Real-World Usage	433
	The Interactive SQL Client	433
	The Expense Tracker	437
	Summary	443
20	MySQL and Perl	445
	Perl	445
	History and Evolution	445
	Installation	446
	MySQL and the Perl DBI	447
	Connection Management	451
	Query Execution	454
	Result Set Processing	457
	Error Handling	461
	Ancillary Functions	465
	A Real-World Example	465
	Designing the Database	466
	Building an Article Index	468
	Retrieving Article Contents	470
	Adding Comments	473
	Building a Threaded Comment Index	477
	Viewing Comments	481
	Summary	484
21	MySQL and PHP	485
	PHP: History and Evolution	485
	Installation	486
	MySQL and PHP	488
	Connection Management	493
	Query Execution	494
	Result Set Processing	498
	Error Handling	505
	Ancillary Functions	505
	A Real-World Example	508
	Designing the Database	509
	Retrieving Data	510
	Adding Data	512
	Removing Data	516
	Summary	520
	Index	521

Editorial Reviews

From the Computer Media column by Rachel Singer. Gordon. .

...perennially popular open-source companions for . creating database-driven web sites. These guides cover MySQL, as well as its use with PHP and other languages..."Complete Reference" tries to be all things to all people, covering topics from an introduction to relational databases and installing and configuring MySQL to reference material for administrators, but is most appropriate for new and intermediate users. Sidebars and notes add info and link to additional resources, while a welcome thoroughness and clarity make this a good, basic purchase for all libraries.

From the Computer Media column by Rachel Singer Gordon

...perennially popular open-source companions for creating database-driven web sites. These guides cover MySQL, as well as its use with PHP and other languages..."Complete Reference" tries to be all things to all people, covering topics from an introduction to relational databases and installing and configuring MySQL to reference material for administrators, but is most appropriate for new and intermediate users. Sidebars and notes add info and link to additional resources, while a welcome thoroughness and clarity make this a good, basic purchase for all libraries.

From the Publisher