Page 393 - Beginning PHP 5.3

Chapter 12: Introducing Databases and SQL
You ’ ve now created your table. To see a list of tables in your database, use the SHOW TABLES command:

mysql > SHOW TABLES;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| fruit |
+----------------------+
1 row in set (0.00 sec)

You can even see the structure of your newly created table by using the EXPLAIN command, as follows:

mysql > EXPLAIN fruit;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| color | varchar(30) | NO | | NULL | |
+-------+----------------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)
You ’ ve created a table with the following three fields:

❑ id is the primary key. It uniquely identifies each row of the table. You created the id field as
SMALLINT UNSIGNED , which means it can hold integer values up to 65,535 (which should be
enough for even the most ardent fruit fan). You used the keywords NOT NULL , which means that
NULL values aren ’ t allowed in the field. You also specified the keyword AUTO_INCREMENT . This
ensures that, whenever a new row is added to the table, the id field automatically gets a new,
unique value (starting with 1). This means you don ’ t have to specify this field ’ s value when
inserting data
❑ name will store the name of each fruit. It ’ s created as VARCHAR(30) , which means it can hold
strings of up to 30 characters in length. Again, the NOT NULL keywords specify that NULL values
aren ’ t allowed for this field
❑ color was created in the same way as name , and will be used to store the color of each fruit

By the way, if you ever want to create a regular key (as opposed to a primary key) for a field in a table,
use the keyword KEY or INDEX instead of PRIMARY KEY . So if you wanted to add an index for the name
field (because your table contained a large number of fruit records and you frequently wanted to look up
fruit by name), you could use (again, don ’ t type the arrows):

mysql > CREATE TABLE fruit (
- > id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
- > name VARCHAR(30) NOT NULL,
- > color VARCHAR(30) NOT NULL,
- > PRIMARY KEY (id),
- > KEY (name)

- > );

355

9/21/09 9:11:12 AM
c12.indd 355
c12.indd 355 9/21/09 9:11:12 AM