Menu
Dev Bay – front-end tips
  • Front-end tips
  • Back-end tips
  • Converters
Dev Bay – front-end tips

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

Posted on October 31, 2018July 28, 2022

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
  • Add new column in MySQL existing table
  • Change existing column in MySQL
  • Delete existing table in MySQL
    • Related posts:

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:

  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:

MySQL command
MySQL command
New customers table
New customers table

 

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:

MySQL add new column
MySQL add new column to existing table

 

MySQL new column is added
MySQL new column is added

 

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

 

MySQL changed table
MySQL changed table

 

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

MySQL delete existing table
MySQL delete existing table

 

MySQL existing table is deleted
MySQL existing table is deleted

 


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

Related posts:

Simple cookie banner – pure JavaScript + HTML Webpack configuration with TypeScript Insert JavaScript vuejs/react/angular apps into SalesForce page
©2025 Dev Bay – front-end tips | Powered by SuperbThemes & WordPress