Burger mobile menu

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:

  1. Create new table in MySQL
  2. Add new column in MySQL table
  3. Change existing column in MySQL
  4. Delete existing table in MySQL

1. Creating new table in MySQL

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

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.

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:

  1. “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].
  2. “name” – column can contain strings values with max 30 length [VARCHAR(30)]  and it can’t be empty [NOT NULL].
  3. “age” – only integer numbers max length 3 [int(3)], this field can’t be empty [NOT NULL].
  4. “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

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:

In our example it will looks like below:

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 add new column

 

MySQL new column is added

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:

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

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

MySQL changing existing table

MySQL changing existing table

 

MySQL changed table

MySQL changed table

 

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

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 delete existing table

 

MySQL existing table is deleted

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


Scroll top
Loading
Zdjęcie w lewo Zdjęcie w prawo