Creating and Modifying Tables - MySQL Series Part 3

Today we’ll continue our MySQL series and learn how to create and modify tables. MySQL tables are the containers that actually hold data. A table with multiple columns and records can be seen as an excel sheet.

In this article, we will try to create some tables that can hold our data and also modify them. So fire up your mysql command line clients and Let’s learn.  

Creating Tables

The first thing we will do is create a table. To do this we need to select the database we want to work with. This can easily be done by

use theitstuff;

Replace theistuff by the database of your choice. If you haven’t created the database you can do that by create database theitstuff;.

The command is fairly simple. Just type in the following command.

create table employee;

This should give you the following output.

We got an error saying that a table must have at least 1 column. This is because we tried creating a table without describing a single column. A table must have at least 1 column.

​Now we need to add some columns to this table. Have a look at the following code.

create table employee( name varchar(50), email varchar(50), salary int);

We have added 3 columns in the above table name, email and salary. For now, just run the code above and then type describe employee;. This should give you the following output.

You can see that we have described the structure of the employee table that we created and it has 3 fields or columns. And they have the characteristics that we defined while creating the table.

• name– Varchar with maximum length of 50.
• email– Varchar with maximum length of 50.
• salary– Integer with maximum length of 4 bytes.

You can see some other columns as well, like Null, Key, Default and Extra. These are all attributes of each column. We will discuss them in detail later.

​Now we have successfully created a table with 3 columns. Let’s say in future we wanted to add a new column called city.

We could do that with the following command.

alter table employee add column city varchar(50);

We could also drop the column from the table by using it.

alter table employee drop column city;

You can see that the city column has been dropped.

We can also give multiple commands such as adding multiple columns or deleting columns. This can be achieved by simply putting the commands separated by a comma sign.

alter table employee add column city varchar(50), add column age int;

Now that you know how to add or delete columns from a database, we can finally learn how to alter a column. This means we will be modifying the attributes of a column.
​
We will be using the following command to change the size of the city column to 20 characters. This means we will have to use varchar(20).

alter table employee add column city;

You can see in the example above that we have successfully changed the city column to a varchar(20) from varchar(50).
​
Finally, if you ever wanted to rename a column you could do that as well. But doing this will require you to enter all the attributes of the new column including the new name and datatype. To change the name of the city column to country. We could do this.

alter table employee change city country varchar(20);

Conclusion

We saw various table operations in MySQL. We created and modified various columns in our tables. We also deleted and renamed existing columns in our tables. We will learn more about column attributes some other day. If you get stuck somewhere or have a doubt, drop it in the comment sections and I’ll be there for you.