Database Reference
In-Depth Information
Reordering a Table
The
SELECT
statement,which is used toretrieve data from a table, has an
ORDER BY
clause that may be used to sort or order the results of the statement. This is useful when
displaying data, especially when viewing a table with many rows of data. Although it's
not necessary, there may be times in which it would be desirable to resort the data within a
table. You might do this with tables in which the data is rarely changed, such as a referen-
ce table. It can sometimes make a sequential search of the table faster, but a good index
will work fine and is usually better.
As an example of how to reorder a table, if you go to my website, you will find a table
listing country codes. We might use such a table in conjunction with members of the site
or maybe to have a list of birds spotted in each country. The
country_codes
table con-
tains two-character country codes, along with the names of the countries. Rather than type
the name of the country for each record in a related table for members or bird spottings,
we could enter a two-character code for the country (e.g.,
us
for
United States of Amer-
ica
). The table is already in alphabetical order by name, but you might want to reorder
that table to put rows in alphabetical order. Or perhaps you want to add a new country to
the list, perhaps a disputed territory that you want to recognize. You might want to reorder
the list after making the addition.
First, let's see how the data in the table looks now. Let's enter the following
SELECT
statement in
mysql
, limiting the results to the first five rows of data:
SELECT * FROM country_codes
LIMIT 5;
+--------------+----------------+
| country_code | country_name |
+--------------+----------------+
| af | Afghanistan |
| ax | Åland Islands |
| al | Albania |
| dz | Algeria |
| as | American Samoa |
+--------------+----------------+
As you can see, the data is already in alphabetical order based on the values in the
coun-
try_name
column. Let's use the
ALTER TABLE
statement with its
ORDER BY
clause
toreorder the data in the table based on the
country_code
column. We would prob-
ably not want the table in this order, but let's do it just to experiment with this clause of
the
ALTER TABLE
statement. We can change it back afterwards. Enter the following in
mysql
:
