Creating MYSQL Database Tables

Creating a table must begin with a plan. What do you want to store in your table? How many columns does your table need? What should each column be labeled? What kind of data do you plan to store in each column?

The syntax to create a table is: CREATE TABLE tablename (columnnames);
For our example, let's return to our pets table and explain how it was created. The examples below are identical, except the second example contains extra line breaks and whitespace that make it more readable. Either example would create a table when entered "as is" into a mysql command line.
CREATE TABLE pets (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(75), breed VARCHAR(75), gender VARCHAR(7), dateofbirth VARCHAR(25), description TEXT, pedigree TEXT, medicalrecord TEXT, PRIMARY KEY(id));

CREATE TABLE pets (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(75),
    breed VARCHAR(75),
    gender VARCHAR(7),
    dateofbirth VARCHAR(25),
    description TEXT,
    pedigree TEXT,
    medicalrecord TEXT,
    PRIMARY KEY(id)
);
Let's break down our example. After naming the table, the columns are all listed between parentheses in the order that they will appear in the table (left-to-right). Each column name, comma separated, is assigned a data type that limits (or not) the type and amount of data that can be stored.
First, we assign a column name of "id" and give it a data type of "INT", which stands for integer, meaning that only integers (numbers between -2147483648 and 2147483647) can be stored in this column. Also, "NOT NULL" prevents this column from allowing null (empty) fields, and "AUTO INCREMENT" automatically assigns a number to each new row. The first row will be 1, and each new row will be incremented by 1 (2, 3, 4, 5, etc.)
The second, third, fourth and fifth columns each have a column name, followed by a data type of varchar(). This data types allows "various characters" (letters, numbers, spaces, punctuation, etc.) but limits the number of characters to the number specified between parenthesis. The pet's name, for example, can be up to 75 characters long, but if the name exceeds this limit, it will not be stored properly.
The sixth, seventh and eighth columns each have a column name, followed by a data type of "text". This data type allows any string as long as it does not exceed 65535 characters.
The final detail is not a column. A primary key is a unique index used to identify the rows in a table. Every table should have one primary key, and only one primary key. A primary key column cannot contain NULL (empty) values, and must contain unique values. In our example, this makes the "id" column an ideal candidate for the primary key column. In fact, the "id" column in our example serves little purpose other than being the primary key.
There are several basic data type options available that you can learn more about before creating your own table.
So now you know how to create a table. Next we can learn how to look at the table we just created.