ALTER command to add and drop column in MySQL
An column in MySQL can be added using ALTER statement in multiple ways as shown:
ALTER TABLE table_name ADD column_name column_type;
Drop:
ALTER TABLE table_name DROP column_name;
ALTER command to add and drop INDEX in MySQL
Index in a database is used to improve the speed of operations in a table. Index can be done using single or multiple columns.
There are 4 types of index commands to adding indexes to a table:
1. ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) : Adds a primary key which means adds indexes and does not allow null values.
2. ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) : Creates an index whose values must be null with the exception of NULL values.
3. ALTER TABLE tbl_name ADD INDEX index_name (column_list) : Adds indexes in which one value can appear more than once
4. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) : Adds an index for efficient text-searching purpose.
Dropping indexes:
To drop a primary key:
ALTER TABLE testalter_tbl DROP PRIMARY KEY;
To drop an index:
DROP INDEX index_name ON tbl_name.