How to create a TABLE

Back to: Structured Query Language (SQL)

The analogy of a database might be to that of a library card catalog (for those that can remember them). Library’s used to have drawers full of card, with each card containing the information about a single book.

The wooden drawers below are effectively the database and tables:

The drawers (database or tables) are full of cards called records (a record is like a row in a table), and each record has the same field names on it (field names are like the table column headings), like Author: Title: and so on. Each book in the library uses one single card (record) and is placed into the catalog in alphabetical order of the author’s surname. The number of fields is only limited by the size of the card but in a database, there is really no limit.

A table in a relational database like MySQL consists of rows and columns, similar to an Excel spreadsheet. In this module we are going to create the TABLE and the COLUMNS. We’ll be populating the table in the next module.

Create a Table and Columns

Open MySQL Workbench, we’ll continue working on the database created in the last module called myDB.

CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hourly_pay DECIMAL(5,2),
    hire_date DATE
);

You can type the SQL statement in a single line, but it is more readable if you use a separate line for each heading. Each field name (column) has to have a data type specified. Common data types are INT (for an integer), VARCHAR(30) (a string with a maximum of 30 characters), DECIMAL(5,2) (for 5 digits, 2 decimal places – so the maximum would be $999.99).

The data types can be:

Execute the script and refresh the Schema to see the newly created table is now in the list.

To view (SELECT) the table:

SELECT * FROM employees;

There are no rows yet, but we can see the columns. This table is ready to be populated with employee data.

Rename a Table

We can change our mind about the name of the table with the following SQL statement:

RENAME TABLE employees TO workers;

If you execute this SQL statement and refresh your Schema, the table name will now read “workers”.

Workers is a silly name, so change the name of the table back to “employees”.

Delete a Table

Similar to deleting the entire database, we use DROP to delete a table:

DROP TABLE employees;

We don’t want to execute this statement, if you do, you’ll need to re make the table and its columns.

Alter a Table

We can make changes to a table using ALTER. We can add a “phone_number” column to the table:

ALTER TABLE employees
ADD phone_number VARCHAR(15);

We can check that the new column has been added by running the SQL statement:

SELECT * FROM employees;

The * here means ALL or EVERYTHING. Executing this statement will show an updated list of columns now including phone_number.

Rename a Column (or fieldname)

Let’s assume we made a mistake and prefer to call the “phone_number” column to “mobile”:

ALTER TABLE employees
RENAME COLUMN phone_number TO mobile;

If this doesn’t work (and it may not if using XAMPP)

ALTER TABLE employees
CHANGE COLUMN phone_number mobile VARCHAR(15);

Then check that the change has been successful:

SELECT * FROM employees;

If we click on the “employees” table in MySQL Workbench, in the bottom left you can see a list of the fields (Columns) that we have in this table.

For “mobile” the data type is still varchar(15). Since the typical Australian mobile has 10 characters we can change this:

ALTER TABLE employees
MODIFY COLUMN mobile VARCHAR(10);

Refresh the Schema and the mobile column will have updated.

The order of the columns can be important so if we prefer the “mobile” column to be after “last_name” we can move it:

ALTER TABLE employees
MODIFY mobile VARCHAR(10)
AFTER last_name;

SELECT * FROM employees;

This will move the “mobile” column and display the resultant list – you can run multiple SQL statements at the same time.

If you wanted the “mobile” column to be the first:

ALTER TABLE employees
MODIFY mobile VARCHAR(10)
FIRST;

Finally, we can delete a column using the DROP command:

ALTER TABLE employees
DROP mobile;

How to create a TABLE

Key Knowledge