SQL ALTER COMMAND | add, drop, modify, change, rename column/table in MySQL database | Leela Soft
#leelasoft #SQL
Syntax:
ALTER {DATABASE | SCHEMA} [db_name]
alter_option ...
alter_option: {
READ ONLY [=] {DEFAULT | 0 | 1}
Ex:
ALTER DATABASE leela_soft READ ONLY 1;
Syntax to add a column:
ALTER TABLE tbl_name
ADD [COLUMN] (col_name column_definition,...)
Ex:
ALTER TABLE customer ADD (cust_name VARCHAR(20), cust_addr VARCHAR(200) );
ALTER TABLE customer ADD cust_phone VARCHAR(20);
Syntax to add a column in a specific position:
ALTER TABLE tbl_name
ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
Ex:
ALTER TABLE customer ADD sno INT(5) first;
ALTER TABLE customer ADD cust_email VARCHAR(30) after cust_addr;
Dropping Column:
ALTER TABLE tbl_name
[alter_option [, alter_option] ...]
alter_option: {
DROP [COLUMN] col_name
}
Ex:
ALTER TABLE customer DROP sno;
Change:
ALTER TABLE tbl_name
[alter_option [, alter_option] ...]
alter_option: {
CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST | AFTER col_name]
}
Ex:
ALTER TABLE customer change cust_id cid VARCHAR(10);
Modify:
ALTER TABLE tbl_name
[alter_option [, alter_option] ...]
alter_option: {
MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
}
Ex:
ALTER TABLE customer modify sno INT(5) fisrt;
Rename:
ALTER TABLE tbl_name
[alter_option [, alter_option] ...]
alter_option: {
RENAME COLUMN old_col_name TO new_col_name
}
Ex:
ALTER TABLE customer rename COLUMN cust_name TO cname;