It’s very likely when you use this book that you’ll have an application in mind you’re trying to develop but are not sure how to implement certain pieces of it. In this case, you’ll already know what type of problem you want to solve, so you should search the table of contents or the index looking for a recipe that shows how to do what you want. Ideally, the recipe will be just what you had in mind. Failing that, you should be able to find a recipe for a similar problem that you can adapt to suit the issue at hand. I try to explain the principles involved in developing each technique so that you’ll be able to modify it to fit the particular requirements of your own applications.
Another way to approach this book is to just read through it with no specific problem in mind. This can help you because it will give you a broader understanding of the things MySQL can do, so I recommend that you page through the book occasionally. It’s a more effective tool if you have a general familiarity with it and know the kinds of problems it addresses.
As you get into later chapters, you’ll sometimes find recipes that
assume a knowledge of topics covered in earlier chapters. This also
applies within a chapter, where later sections often use techniques
discussed earlier in the chapter. If you jump into a chapter and find a
recipe that uses a technique with which you’re not familiar, check the
table of contents or the index to find where the technique is covered.
You should find that it’s been explained earlier. For example, if you
find that a recipe sorts a query result using an ORDER
BY
clause that you don’t understand, turn to Chapter 7,
which discusses various sorting methods and explains how they
work.
The following paragraphs summarize each chapter to give you an overview of the book’s contents.
Chapter 1, Using the mysql Client Program, describes how to use the standard MySQL command-line client. mysql is often the first or primary interface to MySQL that people use, and it’s important to know how to exploit its capabilities. This program enables you to issue queries and see their results interactively, so it’s good for quick experimentation. You can also use it in batch mode to execute canned SQL scripts or send its output into other programs. In addition, the chapter discusses other ways to use mysql, such as how to number output lines or make long lines more readable, how to generate various output formats, and how to log mysql sessions.
Chapter 2, Writing MySQL-Based
Programs, demonstrates the basic elements of MySQL
programming: how to connect to the server, issue queries, retrieve the
results, and handle errors. It also discusses how to handle special
characters and NULL
values in
queries, how to write library files to encapsulate code for commonly
used operations, and describes various ways to gather the parameters
needed for making connections to the server.
Chapter 3, Selecting Data from
Tables, covers several aspects of the SELECT
statement, which is the primary vehicle
for retrieving data from the MySQL server: specifying which columns and
rows you want to retrieve, performing comparisons, dealing with NULL
values, and selecting one section of a
query result. Later chapters cover some of these topics in more detail,
but this chapter provides an overview of the concepts on which they
depend. You should read it if you need some introductory background on
row selection or you don’t yet know a lot about SQL.
Chapter 4, Table Management, covers table cloning, copying results into other tables, using temporary tables, and checking or changing a table’s storage engine.
Chapter 5, Working with
Strings, describes how to deal with string data. It covers
character sets and collations, string comparisons, dealing with
case-sensitivity issues, pattern matching, breaking apart and combining
strings, and performing FULLTEXT
searches.
Chapter 6, Working with Dates and
Times, shows how to work with temporal data. It describes
MySQL’s date format and how to display date values in other formats. It
also covers how to use MySQL’s special TIMESTAMP
data type, how to set the time zone,
conversion between different temporal units, how to perform date
arithmetic to compute intervals or generate one date from another, and
leap-year calculations.
Chapter 7, Sorting Query
Results, describes how to put the rows of a query result in
the order you want. This includes specifying the sort direction, dealing
with NULL
values, accounting for
string case sensitivity, and sorting by dates or partial column values.
It also provides examples that show how to sort special kinds of values,
such as domain names, IP numbers, and ENUM
values.
Chapter 8, Generating Summaries, shows techniques that are useful for assessing the general characteristics of a set of data, such as how many values it contains or what its minimum, maximum, or average values are.
Chapter 9, Obtaining and Using Metadata, discusses how to get information about the data that a query returns, such as the number of rows or columns in the result, or the name and type of each column. It also shows how to ask MySQL what databases and tables are available or find out about the structure of a table and its columns.
Chapter 10, Importing and Exporting
Data, describes how to transfer information between MySQL and
other programs. This includes how to convert files from one format to
another, extract or rearrange columns in datafiles, check and validate
data, rewrite values such as dates that often come in a variety of
formats, and how to figure out which data values cause problems when you
load them into MySQL with LOAD
DATA
.
Chapter 11, Generating and Using
Sequences, discusses AUTO_INCREMENT
columns, MySQL’s mechanism for
producing sequence numbers. It shows how to generate new sequence values
or determine the most recent value, how to resequence a column, how to
begin a sequence at a given value, and how to set up a table so that it
can maintain multiple sequences at once. It also shows how to use
AUTO_INCREMENT
values to maintain a
master-detail relationship between tables, including some of the
pitfalls to avoid.
Chapter 12, Using Multiple Tables, shows how to perform joins, which are operations that combine rows in one table with those from another. It demonstrates how to compare tables to find matches or mismatches, produce master-detail lists and summaries, enumerate many-to-many relationships, and update or delete rows in one table based on the contents of another.
Chapter 13, Statistical Techniques, illustrates how to produce descriptive statistics, frequency distributions, regressions, and correlations. It also covers how to randomize a set of rows or pick a row at random from the set.
Chapter 14, Handling Duplicates, discusses how to identify, count, and remove duplicate rows—and how to prevent them from occurring in the first place.
Chapter 15, Performing Transactions, shows how to handle multiple SQL statements that must execute together as a unit. It discusses how to control MySQL’s auto-commit mode, and how to commit or roll back transactions, and demonstrates some workarounds you can use for non-transactional storage engines.
Chapter 16, Using Stored Routines, Triggers, and Events, describes how to write stored functions and procedures that are stored on the server side, triggers that activate when tables are modified, and events that execute on a scheduled basis.
Chapter 17, Introduction to MySQL on the Web, gets you set up to write web-based MySQL scripts. Web programming enables you to generate dynamic pages from database content or collect information for storage in your database. The chapter discusses how to configure Apache to run Perl, Ruby, PHP, and Python scripts, and how to configure Tomcat to run Java scripts written using JSP notation. It also provides an overview of the Java Standard Tag Library (JSTL) that is used heavily for JSP pages in the following chapters.
Chapter 18, Incorporating Query Results in Web Pages, shows how to use the results of queries to produce various types of HTML structures, such as paragraphs, lists, tables, hyperlinks, and navigation indexes. It also describes how to store images into MySQL, and retrieve and display them later, and how to send a downloadable result set to a browser. The chapter also includes a section that demonstrates how to use a template package to generate web pages.
Chapter 19, Processing Web Input with MySQL, discusses how to obtain input from users over the Web and use it to create new database rows or as the basis for performing searches. It deals heavily with form processing, including how to construct form elements, such as radio buttons, pop-up menus, or checkboxes, based on information contained in your database.
Chapter 20, Using MySQL-Based Web Session Management, describes how to write web applications that remember information across multiple requests, using MySQL for backing store. This is useful when you want to collect information in stages, or when you need to make decisions based on what the user has done earlier.
Appendix A, Obtaining MySQL Software, indicates where to get the source code for the examples shown in this book, and where to get the software you need to use MySQL and write your own database programs.
Appendix B, Executing Commands from
the Command Line, provides background on executing commands
at the command prompt and how to set environment variables such as
PATH
.
Appendix C, JSP and Tomcat Primer, provides a general overview of JSP and installation instructions for the Tomcat web server. Read this if you need to install Tomcat or are not familiar with it, or if you’re never written pages using JSP notation.
Appendix D, References, lists sources of information that provide additional information about topics covered in this book. It also lists some books that provide introductory background for the programming languages used here.
3.14.131.47