How to Create a Table in MySQL

Creating the Table

1. 
   Open your database. In order to create a table, you must have a database to house it in. You can open your database by typing USE database at the MySQL command prompt.

   1. If you don't remember your database's name, type SHOW DATABASES; to list the databases on the MySQL server.
   2. If you don't have a database yet, you can create one by typing CREATE DATABASE database;. The database name cannot contain spaces.
2. 
   Learn the basic data types. Every entry in the table is stored as a certain type of data. This allows MySQL to interact with them in different ways. The data types you will use will depend on the needs of your table. There are many more types than these, but you can use these to make a basic, useful table:

   1. INT - This data type is for whole numbers, and is often used for ID fields.
   2. DECIMAL - This data type stores decimal values, and is defined by the total number of digits and after the number after the decimal. For example: DECIMAL(6,2) would store numbers as "0000.00".
   3. CHAR - This is the basic data type for text and strings. You would normally define a limit for the number of characters stored, such as CHAR(30). You can also use VARCHAR to vary the size based on input. Phone numbers should also be stored using this data type, as they often contain symbols and do not interact with numbers (they are not added, subtracted, etc.)^{[1] X Research source} .
   4. DATE - This data type stores dates in the format YYYY-MM-DD. Use this if you need to store someone's age as opposed to their actual age, otherwise you will need to update the entry every year.
3. 
   Create your table. To create your table in the command line, you will be creating all of your fields in one command. You create tables using the CREATE TABLE command, followed by your table's information. To create a basic employee record, you would enter the following command:

   CREATE TABLE employees (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, lastname VARCHAR(20), firstname VARCHAR(20), phone VARCHAR(20), dateofbirth DATE) 

   1. INT NOT NULL PRIMARY KEY AUTO_INCREMENT creates an ID number for each employee that is added to the record. The number increases each time automatically. This allows you to easily reference employees with other functions.
   2. Although VARCHAR allows you to trim the size based on input, you can set a limit for it so that the user can't input strings that are too large. In the above example, both first name and last name are limited to 20 characters each.
   3. Note that the phone number entry is stored as VARCHAR so that symbols are handled correctly.
4. 
   Verify that your table was created properly. Once you create your table, you will receive a message that it was successfully made. You can now use the DESCRIBE command to ensure that you included all the fields you wanted to and that they have the right data types. Type DESCRIBE database; and refer to the chart that appears to check your table's structure.
5. 
   Create a table using PHP. If you are using PHP to administer your MySQL database through a webserver, you can create a table using a simple PHP file. This assumes that the database already exists on your MySQL server. Enter the following code to create the same table as Step 3, replacing the connection information with your own: