How to create a table in database?


Creating the database is the easy part, but at this point it is empty, as SHOW TABLES tells you:


Empty set (0.00 sec)

The harder part is deciding what the structure of your database should be: what tables you need and what columns should be in each of them.

You want a table that contains a record for each of your PRODUCTS. This can be called the PRODUCTS table, and it should contain, as a bare minimum, each product’s name. Because the name by itself is not very interesting, the table should contain other information. For example, if more than one CUSTOMER in your business bought products, you might want to list each buyers name. You might also want to record some basic descriptive information such as product’s description and price.

Use a CREATE TABLE statement to specify the layout of your table:

mysql> CREATE TABLE products (name VARCHAR(20), description VARCHAR(20),
-> price CHAR(20), customername VARCHAR(30));

VARCHAR is a good choice for the name, owner, and species columns because the column values vary in length. The lengths in those column definitions need not all be the same, and need not be 20. You can normally pick any length from 1 to 65535, whatever seems most reasonable to you.

Once you have created a table, SHOW TABLES should produce some output:

| Tables in sales    |
| products             |

To verify that your table was created the way you expected, use a DESCRIBE statement:

mysql> DESCRIBE products;
| Field            | Type                    | Null  | Key | Default | Extra |
| name          | varchar(20)          | YES  |        | NULL   |          |
|description   | varchar(20)          | YES  |       | NULL    |         |
| price           | varchar(20)           | YES  |       | NULL    |         |
| customername | varchar(30)     | YES  |       | NULL    |         |
4 rows in set (0.03 sec)