Data Definition

Alter Table

Column - Add

ALTER TABLE Syntax

ALTER TABLE thread ADD archived BOOL NOT NULL;
ALTER TABLE thread ADD COLUMN status CHAR(5) DEFAULT NULL AFTER archived;
ALTER TABLE thread ADD COLUMN pk INT NOT NULL AUTO_INCREMENT UNIQUE FIRST;

Note: If you need to specify the column name in quotes, then enclose in the back-tick (not the normal single quote) e.g:

ALTER TABLE order ADD COLUMN `currency_id` INTEGER NOT NULL;

Column - Change

To rename a column (this example also adds a couple of columns):

ALTER TABLE village
  ADD COLUMN
    area DOUBLE DEFAULT NULL,
  ADD COLUMN
    volume DOUBLE DEFAULT NULL,
  CHANGE COLUMN
    width
    old_width DOUBLE NOT NULL

Note: Don’t forget the , comma between each ADD, DROP and CHANGE.

Column - Drop

ALTER TABLE address DROP COLUMN mobile;

Index

ALTER TABLE village DROP PRIMARY KEY;
CREATE UNIQUE INDEX idx_name_age ON village (name, age);

View

Columns

SHOW COLUMNS FROM address;

Create Table

CREATE TABLE fruit (
  pk INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(45) NOT NULL,
  age VARCHAR(45) DEFAULT NULL,
  description VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY (pk),
  UNIQUE KEY idx_name_age (name, age),
  UNIQUE KEY idx_name (name),
  KEY idx_age_description (age, description)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Copy the structure of an existing table:

CREATE TABLE backup_recipes LIKE recipes

Fields

isBlue BOOLEAN DEFAULT False