Writer: Paul DuBois
Published Year: November 01,2006
Publisher: O'Reilly Media, Inc.
Size: 1.4 MB (rar)
Chapter 1: Using the mysql Client Program, describes how to use thestandard 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 rowsand 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.