For this example we'll create two tables. The first one describe the species of animals available in a pet store and the second will store the data of a pet in the store. The table names will be species and pet
The species table will consist of the id and the animal species, and the pet table will consist of animal id, species, sex, and price
mysql> CREATE TABLE species (id INT NOT NULL AUTO_INCREMENT,
species varchar(30) NOT NULL, primary key(id));
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE pet(id INT NOT NULL AUTO_INCREMENT, sp_id INT NOT
NULL, sex CHAR(1) NOT NULL, price DECIMAL(4,2) NOT NULL, primary key(id));
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW tables;
+--------------------+
| Tables_in_petstore |
+--------------------+
| pet |
| species |
+--------------------+
2 rows in set (0.00 sec)
mysql> DESCRIBE species;
+---------+-------------+----+-----+---------+---------------+
| Field | Type |Null|
Key | Default | Extra |
+---------+-------------+----+-----+---------+---------------+
| id | int(11) | |
PRI | NULL | auto_increment|
| name | varchar(30) | | |
| |
+---------+-------------+----+-----+---------+---------------+
2 rows in set (0.05 sec)
mysql> DESC pet;
+-------+--------------+----+-----+---------+----------------+
| Field | Type |Null| Key
| Default | Extra |
+-------+--------------+----+-----+---------+----------------+
| id | int(11) | |
PRI | NULL | auto_increment |
| sp_id | int(11) | |
| 0 | |
| sex | char(1) | |
| |
|
| price | decimal(4,2) | | | 0.00
| |
+-------+--------------+----+-----+---------+----------------+
4 rows in set (0.00 sec)
The SQL sytax to create table is : CREATE TABLE <tablename> (<list of fields>)
The DESCRIBE or DESC statement is used to show a description of a table. You can also use EXPLAIN or SHOW COLUMNS
mysql> EXPLAIN pet;
+-------+--------------+----+-----+---------+----------------+
| Field | Type |Null| Key
| Default | Extra |
+-------+--------------+----+-----+---------+----------------+
| id | int(11) | |
PRI | NULL | auto_increment |
| sp_id | int(11) | |
| 0 | |
| sex | char(1) | |
| |
|
| price | decimal(4,2) | | | 0.00
| |
+-------+--------------+----+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM pet;
+-------+--------------+----+-----+---------+----------------+
| Field | Type |Null| Key
| Default | Extra |
+-------+--------------+----+-----+---------+----------------+
| id | int(11) | |
PRI | NULL | auto_increment |
| sp_id | int(11) | |
| 0 | |
| sex | char(1) | |
| |
|
| price | decimal(4,2) | | | 0.00
| |
+-------+--------------+----+-----+---------+----------------+
4 rows in set (0.00 sec)
