411 REFERENCE · Wikibooks
PHP and MySQL Programming/Creating a Table
Reference material by Wikibooks contributors. This retains its source identity and is not 411 news reporting.
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 |
+--------+--------------+------+-----+---------+-------+
| | varchar(35) | | | | |
| Author | varchar(50) | | | | |
| Title | varchar(255) | | | | |
| Year | int(11) | | | 2000 | |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.18 sec)