Specifying Which Columns to Display

3.2.1 Problem

You want to display some or all of the columns from a table.

3.2.2 Solution

Use * as a shortcut that selects all columns. Or name the columns you want to see explicitly.

3.2.3 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:

mysql> SELECT * FROM mail;
+---------------------+---------+---------+---------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+---------+
| 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2001-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2001-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |
...

Alternatively, you can list the columns explicitly:

mysql> SELECT t, srcuser, srchost, dstuser, dsthost, size FROM mail;
+---------------------+---------+---------+---------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+---------+
| 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2001-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2001-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |
...

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. To accomplish this, name the columns as follows:

mysql> SELECT t, srchost, srcuser, dsthost, dstuser, size FROM mail;
+---------------------+---------+---------+---------+---------+---------+
| t | srchost | srcuser | dsthost | dstuser | size |
+---------------------+---------+---------+---------+---------+---------+
| 2001-05-11 10:15:08 | saturn | barb | mars | tricia | 58274 |
| 2001-05-12 12:48:13 | mars | tricia | venus | gene | 194925 |
| 2001-05-12 15:02:49 | mars | phil | saturn | phil | 1048 |
| 2001-05-13 13:59:18 | saturn | barb | venus | tricia | 271 |
...

Another advantage of naming the columns compared to using * is that you can name just those columns you want to see and omit those in which you have no interest:

mysql> SELECT size FROM mail;
+---------+
| size |
+---------+
| 58274 |
| 194925 |
| 1048 |
| 271 |
...
mysql> SELECT t, srcuser, srchost, size FROM mail;
+---------------------+---------+---------+---------+
| t | srcuser | srchost | size |
+---------------------+---------+---------+---------+
| 2001-05-11 10:15:08 | barb | saturn | 58274 |
| 2001-05-12 12:48:13 | tricia | mars | 194925 |
| 2001-05-12 15:02:49 | phil | mars | 1048 |
| 2001-05-13 13:59:18 | barb | saturn | 271 |
...

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net