. 13
( 132 .)


3 rows in set (0.14 sec)

The MySQL installation includes the other two databases (mysql and test) auto-
matically. The mysql database is covered in great detail in Appendix D.
If you want to work with any of these databases in the command-line client,
issue the use command:

mysql> use store;
Database changed

show tables
After you are connected to a specific database, you can view the tables that make
up the database by running the show tables command:

mysql> show tables;
| Tables_in_store |
| addresses |
| formats |
| items_for_sale |
| order_items |
| orders |
| places |
| products |
| users |
8 rows in set (0.01 sec)
46 Part I: Working with MySQL

show columns
You can get specific information about the columns within a table. The syntax of
the command is show columns from table_name. Note that there are two syn-
onyms to show columns: show fields (show fields from table_name) and
describe (describe table_name).

mysql> show columns from users;
| Field | Type | Null | Key | Default | Extra |
| user_id | int(11) | | PRI | NULL | auto_increment |
| fname | varchar(25) | | | | |
| lname | varchar(40) | | | | |
| email | varchar(60) | YES | | NULL | |
| home_phone | varchar(14) | YES | | NULL | |
| work_phone | varchar(14) | YES | | NULL | |
| fax | varchar(14) | YES | | NULL | |
7 rows in set (0.12 sec)

The preceding query lists most of what you need to know about this table. The
first column, Field, shows the column name; Type (logically enough) shows the
column type; Null indicates whether or not null values are permitted in the col-
umn; Key shows if an index was created for the column, and if so what kind;
Default shows the default value (if one was indicated in the create statement);
and Extra gives some added information (in the preceding table, you can see that
user_id is an auto_increment column).

show index
There will come times when you will need to examine the indexes on your tables.
You can get a lot of information from the show index command. The following
command lists all indexes on the addresses table:

mysql> SHOW INDEX from addresses \G
*************************** 1. row ***************************
Table: addresses
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: address_id
Collation: A
Cardinality: 7
Sub_part: NULL
Chapter 2: The Structured Query Language for Creating and Altering Tables 47

Packed: NULL
1 row in set (0.13 sec)

Notice that in the preceding command we used \G to terminate the command.
This lets the MySQL command-line client know that the data are listed in the pre-
ceding format, rather than in the tabular format you™ve seen so far. This kind of
layout, showing the column name, a colon, and then the value, is convenient when
a query result contains more rows than can comfortably fit in a table.

show table status
If you want to get more detailed information on each table, you can run the show
table status command. This command will show you the number of rows in each
table, the time the table was created, and quite a few other interesting tidbits. You
can get the information on all tables in a database at once by simply running show
table status, or you can get the information on a specific table by using a com-
mand like the following (wildcards % and ˜™ are legal):

mysql> show table status like ˜addresses™ \G
*************************** 1. row ***************************
Name: addresses
Type: MyISAM
Row_format: Dynamic
Rows: 7
Avg_row_length: 58
Data_length: 412
Max_data_length: 4294967295
Index_length: 2048
Data_free: 0
Auto_increment: 8
Create_time: 2001-10-25 15:32:08
Update_time: 2001-10-27 08:51:44
Check_time: 2001-11-27 09:45:46
1 row in set (0.01 sec)

show create table
Before running an alter command, you may want to know exactly what statement
was used to create the table in the first place. You can get this information using
the show create table command:

mysql> SHOW CREATE TABLE addresses \G
48 Part I: Working with MySQL

*************************** 1. row ***************************
Table: addresses
Create Table: CREATE TABLE `addresses` (
`address_id` int(11) NOT NULL auto_increment,
`user_id` int(11) default NULL,
`place` varchar(25) NOT NULL default ˜™,
`addr_1` varchar(255) NOT NULL default ˜™,
`addr_2` varchar(255) default NULL,
`city` varchar(50) NOT NULL default ˜™,
`state` char(2) NOT NULL default ˜™,
`ZIP` varchar(5) NOT NULL default ˜™,
`country` varchar(5) default NULL,
PRIMARY KEY (`address_id`)
1 row in set (0.00 sec)

GUI Tools for Manipulating MySQL
Tables and Data
So far in this book we™ve shown you how to work with MySQL tables and data
using standard SQL statements. However, the process of creating tables and view-
ing table data can a bit of a drag when you™re using the command-line client.
Happily, a variety of programs are available that will help you create and alter
tables and view table data.

Using phpMyAdmin
phpMyAdmin is probably the most widely used MySQL-administration tool. It™s
written in PHP and can therefore run on any platform on which PHP can run. (And
given the subject of this book, we feel safe in assuming that you™re running a PHP-
capable platform.) Be aware, though, that you have to carefully follow the installa-
tion instructions to prevent security problems.
The first step in working with phpMyAdmin is to grab a copy of the source files.
A version is on the book accompanying this CD, but we recommend getting the lat-
est possible source files. You can get the most recent release from http://www.
phpmyadmin.net/. If you™re working off of a Unix or Mac OS X machine, you™ll
want to get the copy of the source that has a .tar.gz extension; for example,
phpMyAdmin-2.5.1-rc3-php.tar.gz. For Windows, get a copy of the source with
the .zip extension (for example, phpMyAdmin-2.5.1-rc3-php.zip).
You™ll want to copy the folder to your Web server™s root directory. On Apache
installations, this directory is usually called /htdocs. You can then uncompress the
file using the following command:

shell> tar xvzf phpMyAdmin-2.5.1-rc3-php.tar.gz
Chapter 2: The Structured Query Language for Creating and Altering Tables 49

phpMyAdmin will then be available through your Web server via a URL like the fol-
lowing: http://localhost/phpMyAdmin-2.5.1-rc3/
On Windows, you™ll use a zip utility like WinZip or pkzip to unzip the files.
Before you can access the application, you™ll need to make changes to the
config.inc.php file. In most cases, all you™ll need to do is put the appropriate user-
name and password on the following lines:

$cfg[˜Servers™][$i][˜user™] = ˜root™; // MySQL user
$cfg[˜Servers™][$i][˜password™] = ˜mypass™; // MySQL

If you™re finding an error that states you don™t have iconv support compiled in,
simply change the following entry in the config.inc.php file to FALSE.

$cfg[˜AllowAnywhereRecoding™] = TRUE

Once you are done with the configuration you should be able to go to the
/index.php page and start using phpMyAdmin.
Using phpMyAdmin is fairly straightforward, and we won™t explain it here. Just
spend some time clicking around and you™ll get a good idea of how it works.
Figures 2-1 and 2-2 show what you can expect from a couple of phpMyAdmin™s

Figure 2-1: View of a table in phpMyAdmin
50 Part I: Working with MySQL

Figure 2-2: Creating a table in phpMyAdmin

MySQL Control Center
This program is an offering from MySQL AB, the company that does most of the
work on the MySQL server daemon and that maintains mysql.com. The graphical
client, called MySQL Control Center (MySQLCC), has the advantage of working on
a wide variety of systems, including FreeBSD, OpenBSD, Solaris, and Linux. If you
want a graphic administrative client that doesn™t use HTTP, as phpMyAdmin does,
this will be one of your better choices.
To give MySQLCC a spin, download it from www.mysql.com/downloads/ and
follow the installation instructions. Figure 2-3 shows what you can expect
MySQLCC to look like. It includes tools for creating tables, viewing table contents,
and running queries (manually and automatically).

Using MacSQL
The people at Runtime Labs have created a very nice, sophisticated GUI front for
Mac OS X that connects to a variety of SQL Servers, MySQL included. You can get
a copy of this software, called MacSQL, from the rtlabs.com Web site. Runtime
provides a free demo that you can take for a test run.
After you download and install MacSQL, you can start the application by double-
clicking the MacSQL icon. At that point MacSQL will detect that you have MySQL
installed and will offer you a screen like the one shown in Figure 2-4.
Chapter 2: The Structured Query Language for Creating and Altering Tables 51

Figure 2-3: The MySQL Control Center interface

Figure 2-4: The Connections screen for MacSQL

To make a connection to MySQL on the local machine, make sure that the Port
item is blank and that the username, host (localhost), and password are appropriate.
At this point you™ll be presented with a screen, like the one shown in Figure 2-5,
that offers several options.
If you™re using OS X, we recommend that you download the free demo and work
through each of the options on this screen. You™ll find that most anything you want
to do with MySQL you can accomplish with this software. At that point you may
decide that it™s worth the $99 for a version of MacSQL Lite.
52 Part I: Working with MySQL

Figure 2-5: Options for MacSQL

This chapter discussed what you need to know in order to create and maintain
databases and database tables when working with MySQL. It is possible that you
will never need to commit the details of the create statement to memory, as graph-
ical tools like phpMyAdmin can help you create and alter tables. Still, it is impor-
tant to understand the column types and the purposes of indexes, as a quick and
efficient database will always use the correct data type and will only include
indexes when necessary.
This chapter also introduced you to some of the GUI tools that can be used to
administer a MySQL installation. In the end, most find that using some type of GUI
tool is easier than manually inputting SQL commands for creating and altering
databases and tables. With these highly useful tools, you™ll likely come to the same


. 13
( 132 .)