MySQL – create new table, add new column, change and delete existing column

Piotr Elmanowski | Published: 31 Oct, 2018

As front-end developers we can’t stay only in HTML/CSS/JS area. We need to do from time to time operation in other IT areas like in back-end, server side or in databases. Probably the most popular database used in web development is MySQL. I will use in this examples PhpMyAdmin, which allows to maintain database using graphical interface or SQL queries. I will explain here shortly a few basic operations on MySQL database, this is:

Create new table in MySQL
Add new column in MySQL table
Change existing column in MySQL
Delete existing table in MySQL

1. Creating new table in MySQL

To create new table in MySQL we need to follow code like below:

CREATE TABLE new_table_name (
    column_name TYPE(LENGTH[OPTIONAL]) ADDITIONAL_ATTRIBUTES,
    column_name TYPE(LENGTH[OPTIONAL]) ADDITIONAL_ATTRIBUTES,
    column_name TYPE(LENGTH[OPTIONAL]) ADDITIONAL_ATTRIBUTES,
    ...
)

CREATE TABLE new_table_name (

column_name TYPE(LENGTH[OPTIONAL]) ADDITIONAL_ATTRIBUTES,

...

)

But it could look quite mysterious, so take a look below on real example. We are creating new table which will contain customers data. We need as columns: unique ID for each customer, her/his name, age and some description. Code looks like below, I will explain it further.

CREATE TABLE customers (
	id INT(6) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(30) NOT NULL,
	age INT(3) NOT NULL,
	description VARCHAR(255)
)

CREATE TABLE customers (

id INT(6) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(30) NOT NULL,

age INT(3) NOT NULL,

description VARCHAR(255)

)

First we declare column name (like above: id, name, age, description) and its length in bracket eg. (6). This what I called “ADDITION_ATTRIBUTES” is some kind of specification and it describes eg. what is allowed in column or if column has an important task to do. I explained below used attributes in my example:

UNSIGNED – column can contain only numbers equal or greater than zero,
NOT NULL – data is required for that field. You can’t leave this field empty (null = empty),
PRIMARY KEY – Make unique order for rows in a table,
AUTO_INCREMENT – data base engine will increase value of that field by 1 for each new added row.

Using simple words, we create new table “customers”, with following 4 fields:

“id” – only integer numbers max length 6 [int(6)], values have to be equal or greater than 0 [UNSIGNED], this field can’t be empty [NOT NULL], is a key field, which identify rows in table [PRIMARY KEY] and increase by 1 when new row is added [AUTO INCREMENT].
“name” – column can contain strings values with max 30 length [VARCHAR(30)] and it can’t be empty [NOT NULL].
“age” – only integer numbers max length 3 [int(3)], this field can’t be empty [NOT NULL].
“description” – column can contain strings values with max 30 length [VARCHAR(255)]

Check how creating new table in MySQL looks in PhpMyAdmin on images:

New customers table

OK so now creating new table should be easy 🙂

2. Add new column in MySQL table

We have our “customers” table, but we realized, that we need one more column there. This column should contain currency (GBP, EUR, USD…) assigned to each user. This is quite simple operation. We must use following syntax:

ALTER TABLE table_name
ADD COLUMN new_column_name TYPE(LENGTH[OPTIONAL]) ADDITIONAL_ATTRIBUTES [FIRST|LAST|AFTER `other_coumn_name`];;

1 2	ALTER TABLE table_name ADD COLUMN new_column_name TYPE(LENGTH[OPTIONAL]) ADDITIONAL_ATTRIBUTES [FIRST\|LAST\|AFTER `other_coumn_name`];;

In our example it will looks like below:

ALTER TABLE customers
ADD COLUMN currency VARCHAR(3) NOT NULL AFTER `name`;

1 2	ALTER TABLE customers ADD COLUMN currency VARCHAR(3) NOT NULL AFTER `name`;

We user ALTER TABLE command to modify an exiting table. Then we write ADD COLUMN in the same syntax as we used for table creation (above – step 1). The only one new thing is that on the end of query we have to write where new column should be placed. We can write:

FIRST – then it will be first column
AFTER existing_column_name

In my example, I want to place new column just after “name” column. Please check it on images which shows how to add new column to table in PhpMyAdmin using queries:

MySQL add new column

MySQL new column is added

OK – I hope it was easy 🙂 Let’s go to last

3. Change existing column in MySQL

So now we know how to create new table in MySQL, how to add new column and now I will show how to change existing column. It is quite ease. For example, we decided that last column in “customers” table must be longer. It means we want to change type of column from VARCHAR into “TEXT”. The syntax of this query is:

ALTER TABLE `table_name`
CHANGE `column_name` `column_name` TYPE(LENGTH) ADDITIONAL_ATTRIBUTES;

1 2	ALTER TABLE `table_name` CHANGE `column_name` `column_name` TYPE(LENGTH) ADDITIONAL_ATTRIBUTES;

In our case we must change “description” column, so our query will look like:

ALTER TABLE `customers`
CHANGE `description` `description` TEXT;

1 2	ALTER TABLE `customers` CHANGE `description` `description` TEXT;

Check on images how to change existing table in PhpMyAdmin using query:

MySQL changing existing table

MySQL changed table

4. Delete existing table in MySQL

I will show now how to delete existing table in MySQL. For example, we decided that we don’t need any more this table and we want to remove it from our data base. This is the easier query in this article. Please check code below:

DROP TABLE `customers`;

1	DROP TABLE `customers`;

and data base is clean. Check it on images below, how I did deleted existing table in PhpMyAdmin by a query:

MySQL delete existing table

MySQL existing table is deleted

If anything is not clear or you know better method to do same things – just write in comments below 🙂

Tags: back-end, data base, mysql, sql