PHP and MySQL Programming/Creating a Table

Before creating a table, please read the previous section on ../Creating a Database/.

A Table resides inside a database. Tables contain rows, which are made up of a collection of common fields or columns. Here is a sample output for a SELECT * query:

mysql> SELECT * FROM `books`; ++--+--+--+ | ISBN | Author       | Title                        | Year | ++--+--+--+ | 1234567890 | Poisson, R.W | Programming PHP and MySQL   | 2006 | | 5946253158 | Wilson, M   | Java Secrets                 | 2005 | | 8529637410 | Moritz, R   | C from Beginners to Advanced | 2001 | ++--+--+--+

As you can see, We have rows (horizontal collection of fields), as well as columns (the vertical attributes and values).

Creating a Table
The SQL code for creating a table is as follows:

mysql> CREATE TABLE `table_name` (       `field1` type NOT NULL|NULL default 'default_value',        `field2` type NOT NULL|NULL default 'default_value',        ...        );

Example
Here is an example of creating a table called `books`:

mysql> CREATE TABLE `books` (       `ISBN` varchar(35) NOT NULL default ,        `Author` varchar(50) NOT NULL default  ,        `Title` varchar(255) NOT NULL default '' ,        `Year` int(11) NOT NULL default '2000'        );

Getting Information about Tables
To get a list of tables:

mysql> SHOW TABLES;

Which produces the following output:

+---+ | Tables_in_library | +---+ | books            | +---+ 1 row in set (0.19 sec)

To show the CREATE query used to create the table:

mysql> SHOW CREATE TABLE `books`;

Which produces the following output:

+---++ | Table | Create Table +---++ | books | CREATE TABLE `books` (  `ISBN` varchar(35) NOT NULL default ,   `Author` varchar(50) NOT NULL default  ,   `Title` varchar(255) NOT NULL default '' ,   `Year` int(11) NOT NULL default '2000' ) TYPE=MyISAM | +---++ 1 row in set (0.05 sec)

And then to show the same information, in a tabulated format:

mysql> DESCRIBE `books`;

Which produces the following output:

++--+--+-+-+---+ | Field | Type         | Null | Key | Default | Extra | ++--+--+-+-+---+ | ISBN | varchar(35)  |      |     |         |       | | Author | varchar(50) |      |     |         |       | | Title | varchar(255) |      |     |         |       | | Year  | int(11)      |      |     | 2000    |       | ++--+--+-+-+---+ 4 rows in set (0.18 sec)