How to Create a MySQL Table

Creating a MySQL table utilizes the CREATE command and the "Data Definition Language," or DDL. DDL is used to define data structures. MySQL provides a number of column types. Each column holds a specific class of information. To create a MySQL table, you will need to create column types and then put them all together to form a database (your table).

Instructions

1

Write the skeleton of the CREATE command. In this example, the table is named "customers." Replace "customers" with the name of the table you want to create. Example:
CREATE TABLE customers (
);

2

Create an id column. All tables should have an id column to uniquely identify that row, since two rows can exist with identical data. Without an id column, it would be impossible to tell the two rows apart.

3

Define the id column as an integer. It should also be an AUTO_INCREMENT column. This means MySQL keeps track of the id numbers for you in sequential order. Example:
id INTEGER AUTO_INCREMENT,

4

Remember that an AUTO_INCREMENT column is also required to be a primary key and add this line. Example:
PRIMARY KEY (id)

5

Create a string column using the VARCHAR column type. A VARCHAR(n) will hold up to n characters but no more, so be sure to choose an n that's large enough to hold all the data you'll need, yet short enough so it won't waste space. Example:
name VARCHAR(32),

6

Create a real number column. A "real" number is anything but an integer. If, for example, you want to store a customer's balance, you'll need to store cents as well as dollars, so a FLOAT column type is also necessary. Example:
balance FLOAT,

7

Create an integer column. Here an INTEGER is used to track the number of purchases a customer has made. Example:
purchases INTEGER,


8

Create a date column. This example uses a DATE column to store the date of the first purchase. Example:
customer_since DATE,


9

Put the column types together. The command can be on multiple lines to make it easier to read and to store in files. The MySQL command-line client also allows for commands to be entered this way. Example:
CREATE TABLE customers (
id INTEGER AUTO_INCREMENT, name VARCHAR(128),
address VARCHAR(512),
ph_number VARCHAR(20),
email VARCHAR(64), balance FLOAT, purchases INTEGER, customer_since DATE, PRIMARY KEY (id)
);