ALTER statement in MySQL
Hello and
welcome everyone. In this article, we will learn about the ALTER table statement
in MySQL. Let’s go.
MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/
C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/
Web API 2- .Net Core In depth In 5 Hours
click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/
MySQL
ALTER command is used to modify the table, view an existing database. When you
have completed your database design and after that, you realize some of the
information was left out when creating a database. You want to change without
losing existing data, and then you can change data using ALTER command.
MySQL
ALTER command is used to add a column with an existing table, add multiple
columns, drop a column, modify a column, rename a column, and rename the table.
ALTER command also used to add and drop constraints on the existing table.
Now we
see in details with an example following ALTER command uses:
1.
Add Column
2.
Add Multiple Columns
3.
Drop Column
4.
Modify Column
5.
Rename Column
6.
Rename Table
Here is
the, we will create a table for an employee in the database.
CREATE
TABLE IF NOT EXISTS employee (
e_id
int (10) PRIMARY KEY,
e_firstname
varchar (50) NOT NULL,
e_lastname
varchar (50) NOT NULL,
e_age
int (10),
e_city
varchar (250)
e_sex
char (1)
e_phnumber
int (15) NOT NULL
)
Here we
insert record in the employee table.
INSERT INTO employee (
‘e_id’, ’e_firstname’, ’e_lastname’,
’e_age’, ’e_city’, ’e_sex’,
’e_phnumber’
)
VALUES
(
1, ‘Hone’, ‘Reus’, 25, ‘San Jose’, ‘M’, ‘ +
1 555 555 1111’
),
(
2, ‘Cries’, ’Fence’, 23, ’Fresno’, ’F’, ’
+1 555 555 1111’
);
Final
Result
‘e_id’ ’e_firstname’ ’e_lastname’ ’e_age’ ’e_city’ ’e_sex’ ’e_phnumber’
1 Hone Reus 25 San Jose
M +1 555 555 1111
2 Cries Fence 23 Fresno F +1 555 555 1111
1.
Add Column
In that, you can add column in an existing table. Above employee
table, I want to add e_email address column.
Syntax
ALTER TABLE table_name
ADD column_name datatype (size);
Example
ALTER TABLE employee
ADD e_email varchar (150);
2.
Add Multiple Columns
In that, you can add multiple columns in the existing table. Above
employee table, I want to add two new columns, e_salary, and e_designation
Syntax
ALTER TABLE
table_name
ADD column_name_1
datatype (size)
ADD column_name_2
datatype (size)
Example
ALTER TABLE
employee
ADD e_salary int
(50)
ADD e_designation
varchar (255);
3.
Drop Columns
If you want to drop some columns in the existing table, then you
can use drop column command with an ALTER table. I want to drop an e_email
column from above employee table.
Syntax
ALTER TABLE
table_name
DROP COLUMN
column_name;
Example
ALTER TABLE
employee
DROP COLUMN
e_email;
4.
Modify Column
Change data type or data type size with modifying column command. I
want to modify e_age column datatype size.
Syntax
ALTER TABLE
table_name
MODIFY COLUMN
column_name datatype (size);
Example
ALTER TABLE
employee
MODIFY COLUMN e_age
INT (2);
5.
Rename Column
When you want to change the column name, then you can use rename
column. I want to rename “e_phnumber” column name to “e_mobno”.
Syntax
ALTER TABLE
table_name
CHANGE COLUMN old_column_name
new_column_name;
Example
ALTER TABLE
employee
CHANGE COLUMN e_phnumber
e_mobno;
6.
Rename Table
Change table name using the rename table command. I want to rename
“employee” table name to “employee_info”.
Syntax
ALTER TABLE
table_name
RENAME to
new_table_name;
Example
ALTER TABLE
employee
RENAME to
employee_info;
In this article, we learned
about ALTER TABLE command. What is the use of ALTER TABLE command and how to
use it.
MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/
C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/
Web API 2- .Net Core In depth In 5 Hours
click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/
Comments
Post a Comment