MySQL in a Nutshell (2008)
Preface
MySQL is the most popular open source database system available. Although it’s free, it’s still very dependable and fast, and is being employed increasingly in areas that used to be the province of Oracle or MS SQL Server. Thanks to a variety of utilities packaged with MySQL, administration is fairly effortless. With its several application programming interfaces (APIs), it’s easy to develop your own software to interface with MySQL.
This book provides a quick reference to MySQL statements and functions, the administrative utilities, and the most popular APIs. The first few chapters are designed to help you to get started with MySQL. Each chapter on an API also starts with a tutorial.
When this book was written, version 5.0 of MySQL was generally available, and early releases of the development versions of 5.1 and 6.0 were available but not yet stable. As a result, you will find mostly features from version 5.0.x in this book. Features that appear only in newer versions are noted as such.
The Purpose of This Book
The purpose of this book is to provide a quick reference to:
§ MySQL statements and functions
§ Command-line options and configuration information for the MySQL server and utilities
§ The most popular APIs used to access MySQL databases
Several chapters start with tutorials, but the central purpose of the book is to fill in the gaps for people who are already comfortable with relational databases.
The format that I’ve followed for a description of each statement or function is to move from curt memory-joggers to more leisurely explanations. If you know the statement or function that you’re looking up, but can’t quite remember the syntax, you’ll find that first. If you need a bit more information to jog your memory or to clarify the possibilities available, you can find this in the first sentence or so of the explanation. If you require more clarification, you can continue with the slower-paced material that will follow a statement or function. Examples of usage are provided for almost all statements and functions.
In summary, the goal is to be brief but fairly complete, and to increase the level of detail as you read on.
How This Book Is Organized
This book is broken up into 14 chapters and 3 appendixes, as follows.
Part I, Introduction and Tutorials
Chapter 1, Introduction to MySQL, explains the major components of MySQL and useful guidelines for other information on MySQL.
Chapter 2, Installing MySQL, describes how to get MySQL running on common operating systems supported by MySQL AB. It is necessary to read this chapter only if your system does not already have MySQL installed.
Chapter 3, MySQL Basics, introduces SQL (Structured Query Language) and use of the mysql command-line interface. It’s not a replacement for learning SQL and relational database design, but it can be useful to orient you to MySQL.
Part II, SQL Statements and Functions
Chapter 4, Security and User Statements and Functions, covers SQL statements and functions related to the management of user accounts and security.
Chapter 5, Database and Table Schema Statements, lists, explains, and provides examples of SQL statements and functions related to the creating, altering, and dropping of databases, tables, indexes, and views.
Chapter 6, Data Manipulation Statements and Functions, covers any SQL statements and functions that involve the manipulation of data—inserting, updating, replacing, or deleting.
Chapter 7, Table and Server Administration Statements and Functions, includes details and examples related to SQL statements and functions that might be used in the administration of databases, tables, or the server.
Chapter 8, Replication Statements and Functions, includes SQL statements that strictly relate to replication. This chapter also includes a tutorial and an explanation of the replication process. It also explains the replication states to help in solving problems.
Chapter 9, Stored Routines Statements, covers statements specifically related to events, stored procedures, triggers, and user-defined functions.
Chapter 10, Aggregate Clauses, Aggregate Functions, and Subqueries, combines aggregate clauses (i.e., GROUP BY) and functions that basically are only used with an aggregate clause. It also includes a tutorial on subqueries as they can be used to aggregate data.
Chapter 11, String Functions, covers any functions that are related to the manipulation of strings of data.
Chapter 12, Date and Time Functions, covers date and time related functions.
Chapter 13, Mathematical Functions, explains and gives examples of strictly mathematical related functions.
Chapter 14, Flow Control Functions, covers flow control functions such as CASE and IF.
Part III, MySQL Server and Client Tools
Chapter 15, MySQL Server and Client, covers the mysqld daemon and the mysql client and their options. It also explains scripts used to start the server (e.g., mysqld_safe).
Chapter 16, Command-Line Utilities, describes the utilities that can be used to administer the MySQL server and data. It also includes utilities such as mysqldump used for data backups.
Part IV, MySQL API
Chapter 17, C API, covers the functions provided by MySQL’s basic C library.
Chapter 18, Perl API, presents the Perl DBI module, used to access MySQL databases from the programming language Perl.
Chapter 19, PHP API, presents the PHP functions used to query and manipulate MySQL databases.
Appendixes
Appendix A lists all the data types supported by MySQL.
Appendix B lists all MySQL operators, such as arithmetic signs and the LIKE and IS NULL comparison operators.
Appendix C lists the operating system’s environment variables consulted by the MySQL server, client, and other utilities.
Conventions Used in This Book
The following typographical conventions are used in this book:
Plain text
Indicates menu titles, menu options, menu buttons, and keyboard accelerators (such as Alt and Ctrl).
Italic
Indicates new terms, URLs, email addresses, usernames, hostnames, filenames, file extensions, pathnames, and directories.
Constant width
Indicates elements of code, configuration options, variables, functions, modules, databases, tables, columns, command-line utilities, the contents of files, or the output from commands.
Constant width bold
Shows commands or other text that should be typed literally by the user.
Constant width italic
Shows text that should be replaced with user-supplied values.