Module 4: Basic Reporting


This module deals with retrieving records from your database tables and presenting the data in them in a concise and informative display. You have already used the SELECT WHERE command to give you a basic view of your table s data. Here, you will learn how to refine your output by using more selective specifications. With a narrowed focus, you can present only the data you need in an easily accessible format.

Critical Skill 4.1 Shape the Data from Your Tables to Fit Your Needs

While it can be true that the quality of an answer depends on the degree of detail in the question, there are things you, as the database administrator, can do to improve the effectiveness of the data you supply. When any request is made for specific data, you should give some thought to exactly what you are being asked to provide, and if the data you provide is going to lead to other, easily forecasted, requests .

For instance, if the sales department puts in a request for how many current customers live in a particular state or region, you may assume from past experience that the next request will involve a breakdown of those customers by city, and therefore decide to supply that information the first time around. You can also decide that ordering the output by the city name , which groups the customers in that city together, will make it easier for the sales department to draw conclusions from the data.

Always take a few moments to clarify what is being asked of you, and once you have that information, proceed to choose which fields from which tables will provide you with the appropriate collection of data.

As you ve learned in previous modules, you can choose data from your database with the SELECT command. The SELECT command is used for two basic reasons. One is the reason you have been using it so far: testing. SELECT allows you to look at your tables quickly and easily in order to verify their content and the format of that content. The second reason is to shape the data from your tables into formats that are easier to read and use, defining the output to match the needs of your circumstances. This section will briefly review the SELECT command as you already know and use it, and then expand on its usefulness by adding some options to your repertoire that allow you to shape the data to fit your needs more closely.

Testing with the SELECT Command

Your use of SELECT so far has been to verify that the commands you used in your projects gave the desired results. You ve used the wildcard form of SELECT ( SELECT * ) as a verifying test. Although this form is useful, it can present some problems. So, first we will look at when using SELECT * isn t appropriate. Then you will learn about another useful testing form of SELECT that uses the LIMIT option, which allows you to control how much data is returned.

Understanding the Problems with SELECT *

The basic SELECT command you have seen in previous modules has the following syntax:

 SELECT [*] FROM <  table_name  >; 

The asterisk (or wildcard) indicates choosing all of a table s columns .

The problem with using the wildcard is that you may be pulling up columns of information that you have no use for at the moment. In a small database, such as duckwear , this isn t much of a problem. However, if the duck_cust table held hundreds or even thousands of records, the importance of looking at only the fields you really need to examine becomes clear. Not only does using the wildcard complicate your output, but it also begins to affect your response time when your database has become large.

Another consideration relates to using SELECT * in code. When you write a piece of code using SELECT * , you are relying on the response to that code showing up in the order in which the table you are accessing is designed. If at some later point, you change the table in question ”by adding, deleting, rearranging, or modifying a column s definition ”then you will have unwittingly broken the code with the SELECT * in it. It is easy enough to do this with your own code, and even easier if someone else has taken over the administration of the database.

Because of these potential problems, you should use SELECT * only in testing. Even if you need all the fields in a table, when writing actual code that will be in use over time, you should call out the column names individually to avoid needless difficulties later in the life of your database.

Limiting Selections

The LIMIT option of the SELECT command allows you to specify a numeric quantity to control the amount of data you get from the command. You can also use it as a kind of scrolling stop.

The syntax for using the basic LIMIT option is as follows :

 SELECT <  column_name  > FROM <  table_name  > LIMIT <  numeric_value  >; 

Even if you use the SELECT * command, the output will produce only the number of lines of data specified in the LIMIT clause. For instance, you could issue the following command:

 SELECT * FROM duck_cust LIMIT 9; 

Figure 4-1 shows the results of using this command with the duck_cust table as it stands right now. As you can see, only the first 9 entries in the table appear, instead of the entire 14 entries it contains. This demonstrates how the LIMIT option can be used to scroll through a table a few entries at a time.


Figure 4-1: Using the SELECT command with the LIMIT option

You can also specify which record to begin with (the offset ) and the total number of records you want to see ( count ), by using the following syntax:

 SELECT * FROM <  table_name  > LIMIT <  offset  >, <  count  >; 

For instance, you could use the following command:

 SELECT * FROM duck_cust LIMIT 3, 4; 

Figure 4-2 shows the results: the records starting with the record after 3 (customer number 4) and ending when the total number of records equals four (customer number 7).


Figure 4-2: Using the LIMIT option with offset and count specifications

Using the LIMIT option of the SELECT command is handy for testing the results of commands, as well as scrolling through the table looking for results or content.

Shaping Data with the SELECT Command

After you have worked out what you need in a given piece of code and how you will go about displaying the data required, you will use forms of SELECT that shape the format of the data, rather than display it unfiltered . The DISTINCT option of the SELECT command is useful for removing duplicates from the results. The AS option lets you display columns with different names than they have in the table.

Removing Duplicates

The DISTINCT option allows you to cull out any duplicates in your reporting data. If you are looking for how many states you have customers in, for example, the list will be easier to compile if only one incidence of each state is reported .

The syntax for using the DISTINCT option is as follows:

 SELECT [DISTINCT] <  column_name  > FROM <  table_name  >; 

The DISTINCT option allows you to specify when you need to see only one incidence of a return value, which can make it much easier to draw conclusions from your output.

 SELECT DISTINCT species FROM monsters; 

For example, if the species column of the monsters table listed five vampires, three werewolves, two zombies , and one banshee, then this command would return the list vampire, werewolf, zombie, and banshee , editing out the repeating entries occurring in the species fields.

Renaming Columns for Display Purposes

The AS option allows you to give a column a new heading in the output of the SELECT command. It does not change the name of the column in the table, just defines an alias for the heading of that column s information in the output. This can be useful for clarity and security. Changing the display heading clearly labels the data in that column, and using an alias makes it harder for someone to access areas of the database where they do not have SHOW privileges in order to view table names and structures.

If, for instance, the name of a column is cust_duckname , you can change the header to DuckName for the output by using the following command:

 SELECT cust_first, cust_last, cust_duckname AS DuckName FROM duck_cust; 

If the new header contains spaces or special characters , then it must be enclosed within double quotation marks to indicate it is a string that should be handled as one unit of text. If you want to use Duck s Name as the header, which contains both a space and a special character (apostrophe), you would then use the following command:

 SELECT cust_first, cust_last, cust_duckname AS "Ducks Name" FROM duck_cust; 

Figure 4-3 shows the resulting output from the preceding command. The first two columns have their duck_cust field names as headers. However, the third column uses the name supplied with the AS option, rather than the duck_cust field name.


Figure 4-3: Using the AS option of the SELECT command

Progress Check

1.  

What are two reasons the SELECT * command should not be used except for testing purposes?

2.  

What does the LIMIT option of the SELECT command do?

3.  

What does the DISTINCT option of the SELECT command do?

4.  

What does the AS option of the SELECT command do?

Answers

1.  

One reason that the SELECT * command should not be used except for testing purposes is that you select more fields than your query actually requires, which clutters your output and may slow your response time. Another reason is that changing the order of the table columns can break the code, putting information in the wrong column headers .

2.  

The LIMIT option restricts the output of the SELECT command to a specific number of lines, or it can be used to scroll through a table by a specific number of lines at a time.

3.  

The DISTINCT option removes duplicated data from the output of the SELECT command.

4.  

The AS option creates an alias for the output headers of a column or columns from a table specified in a SELECT command.




MySQL(c) Essential Skills
MySQL: Essential Skills
ISBN: 0072255137
EAN: 2147483647
Year: 2006
Pages: 109

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