PHP/MySQL ->
MySQL - change table structure using ALTER TABLE

We can use ALTER TABLE statement to Add/Remove column, change column definition, rename table or change index.

/* Add column "a" before all existing columns, column "b" after it, and then drop "a" */

ALTER TABLE `Employees` ADD a INT FIRST;
ALTER TABLE `Employees` ADD b Char(10) AFTER a;

ALTER TABLE `Employees` DROP a;

SHOW COLUMNS FROM `Employees`;


Change column definitions using CHANGE/MODIFY:
/* rename column "b" to "c" and change datatype to FLOAT */
ALTER TABLE `Employees` CHANGE b c FLOAT;

/* change column c datatype to INT, define it as NOT NULL with default value 100 */
ALTER TABLE `Employees` MODIFY c INT NOT NULL DEFAULT 100;

SHOW COLUMNS FROM `Employees` LIKE 'c';


Rename table:
RENAME TABLE `log_journal` TO `log_journal_June`;


Add indexes:
/* Add primary Key */
ALTER TABLE `Customers` MODIFY CustomerID INT NOT NULL;
ALTER TABLE `Customers` ADD PRIMARY KEY (CustomerID);

/* Add index on 2 columns */
ALTER TABLE `Customers` ADD INDEX (city, street);

SHOW INDEX FROM `Customers`;




sqlexamples.info