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

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:

Table of Contents

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,
    ...
)

But it could look quite mysterious, so take a look below on real example. We are creating new table in MySQL 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)
)

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 MySQL 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:

OK so now creating new table should be easy 🙂

Add new column in MySQL existing 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. To add a new column to existing MySQL table 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`];;

In our example it will looks like below:

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

We user ALTER TABLE command to modify an exiting MySQL 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 MySQL table’s column just after “name” column. Please check it on images which shows how to add new column to table in PhpMyAdmin using queries:

OK – I hope it was easy 🙂 Let’s go to next point.

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 in MySQL table. 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;

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

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

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

MySQL changing existing table — MySQL changing existing column in table

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`;

and table customers in now removed from the data base. Check it on images below, how I did deleted existing table in PhpMyAdmin by a query:

I hope that my article with description this 4 basic operations in MySQL data base was helpful for you 🙂

Creating new table in MySQL

Add new column in MySQL existing table

Change existing column in MySQL

Delete existing table in MySQL

Related posts: