Brad Crockett, Big Blue and Cousins, Feb 2003
Review
Book Description
Product Description
While MySQL has turned up among high profile users such as Yahoo!, NASA and the U.S. Census Bureau, the rising popularity of this open source database is especially keen among users with little database experience. These days, even a small organization or web site has uses for a database, and MySQL is an obvious choice. Affordable and easy to use, MySQL packs the power, speed and efficiency that enable it to rival expensive, proprietary database solutions. Yet, even if you know the basics, anyone without practical MySQL experience--novices and skilled DBAs alike--might stumble over common database-related tasks. Fortunately, there's a sensible shortcut.
MySQL Cookbook provides a unique problem-and-solution format that offers practical examples for everyday programming dilemmas. For every problem addressed in the book, there's a worked-out solution or "recipe"--short, focused pieces of code that you can insert directly into your applications. But MySQL Cookbook is more than a collection of cut-and-paste code. You also get explanations of how and why the code works, so you can learn to adapt the techniques to similar situations.
The book covers a lot of ground. Solutions for typical MySQL dilemmas range from simple ways to find all records that contain a given string, to more difficult problems, such as finding matching/non-matching records in two tables. Whether you use MySQL on Unix, Linux, Windows or the Mac OS X platform, the book will show you how to:
- Import data from external sources
- Export data for use by external programs
- Access MySQL from your web server
- Use scripts with MySQL to read queries from a file
- Access MySQL from within client programs that use Perl, PHP, Java, Python and other languages
- Construct queries that solve commonly-occurring questions
- Interact with the server
From the Publisher
About the Author
is one of the primary contributors to the MySQL Reference Manual, a renowned online manual that has supported MySQL administrators and database developers for years, now available in an attractive paper format from the O'Reilly Community Press. He is also the author of Using csh & tcsh and Software Portability with imake by O'Reilly, as well as MySQL and MySQL and Perl for the Web by New Riders.
Excerpted from My SQL Cookbook by Paul DuBois. Copyright © 2002. Reprinted by permission. All rights reserved.
3.0 Introduction
This chapter focuses on the SELECT statement that is used for retrieving information from a database.It provides some essential background that shows various ways you can use SELECT to tell MySQL what you want to see.You should find the chapter helpful if your SQL background is limited or if you want to find out about the MySQL-specific extensions to SELECT syntax.However, there are so many ways to write SELECT queries that we’ll necessarily touch on just a few.You may wish to consult the MySQL Reference Manual or a MySQL text for more information about the syntax of SELECT, as well as the functions and operators that you can use for extracting and manipulating data.
SELECT gives you control over several aspects of record retrieval:
•Which table to use
•Which columns to display from the table
•What names to give the columns
•Which rows to retrieve from the table
•How to sort the rows
Many useful queries are quite simple and don’t specify all those things.For example, some forms of SELECT don’t even name a table —a fact used in Recipe 1.31, which discusses how to use mysql as a calculator. Other non –table-based queries are useful for purposes such as checking what version of the server you’re running or the name of the current database:
However, to answer more involved questions, normally you’ll need to pull information from one or more tables.Many of the examples in this chapter use a table named mail, which contains columns used to maintain a log of mail message traffic between users on a set of hosts.Its definition looks like this:
CREATE TABLE mail
(
t DATETIME, #when message was sent
srcuser CHAR(8), #sender (source user and host)
srchost CHAR(20),
dstuser CHAR(8), #recipient (destination user and host)
dsthost CHAR(20),
size BIGINT, #message size in bytes
INDEX (t)
);
To create the mail table and load its contents, change location into the tables directory of the recipes distribution and run this command:
%mysql cookbook Many of the queries shown here can be tried out with mysql, which you can read about in Chapter 1. Some of the examples issue queries from within the context of a programming language. See Chapter 2 for background on programming techniques. 3.1 Specifying Which Columns to Display Problem It’s certainly easier to use *than to write out a list of column names. However, with*, there is no guarantee about the order in which columns will be returned.(The server returns them in the order they are listed in the table definition, but this may change if you change the definition.See Chapter 8.)Thus, one advantage of naming the columns explicitly is that you can place them in whatever order you want.Suppose you want hostnames to appear before usernames, rather than after.
You want to display some or all of the columns from a table.
Solution
Use *as a shortcut that selects all columns.Or name the columns you want to see explicitly.
Discussion
To indicate what kind of information you want to see from a table, name a column or a list of columns and the table to use.The easiest way to select output columns is to use the *specifier, which is a shortcut for naming all the columns in a table:
