Manipulating Schema with SQL

If your database is connected to an app, you’d probably want to take that app offline or put into some maintenance mode so that any database changes you make don’t effect the application.

Also, BACKUP before making any changes.

Altering Tables

Rename

1RENAME TABLE movies TO movie_table;

You can also rename multiple tables in one go

1RENAME TABLE movies TO movie_table, actors TO actor_table;

Drop (Delete)

1DROP TABLE reviews;

You can also use the IF EXISTS keyword (preferred)

1DROP TABLE IF EXISTS reviews;

The benefit of IF EXISTS is that it’ll result in a warning instead of an error. The rest of the code will still run after a warning but not after an error.

Empty (Truncate)

Truncate deletes a table and then creates a new empty table of the same name.

1TRUNCATE TABLE movie_table;

The TRUNCATE keyword doesn’t need to take the TABLE keyword after it. The following will work just fine.

1TRUNCATE movie_table;

Altering Columns

To make changes to a column, first you’ll specify the table which contains the column with ALTER TABLE table_name and then you’ll use the keyword ADD, CHANGE, or DELETE followed by column name and values.

Add Columns ADD COLUMN

1ALTER TABLE movies ADD COLUMN genre VARCHAR(100);

COLUMN is an optional keyword, only ADD will also work. Here’s another example

1ALTER TABLE actors ADD (pob VARCHAR(100), dob DATE);

Rename Columns CHANGE

1ALTER TABLE table_name CHANGE column_current_name column_new_name data_type;

1ALTER TABLE actors CHANGE pob place_of_birth VARCHAR(100);

Unlike ADD, CHANGE can not take multiple columns. For every column name you want to change, you’ll have to create a new statement.

1ALTER TABLE actors CHANGE pob place_of_birth VARCHAR(100);
2ALTER TABLE actors CHANGE dob date_of_birth DATE;

Change Data Type

1ALTER TABLE movies CHANGE year year_released YEAR;

Delete Column DROP

1ALTER TABLE actors DROP date_of_birth;

Determing a Column’s place

You can determine where to add a column using the keywords FIRST and AFTER.

1ALTER TABLE movies ADD COLUMN name VARCHAR(150) FIRST;

will add a column name and add it as the FIRST column of the table. Similarly, the code below will add the column name AFTER the column id

1ALTER TABLE movies ADD COLUMN name VARCHAR(150) AFTER id;

Here is another example

1ALTER TABLE movies ADD COLUMN id INTEGER PRIMARY KEY AUTO_INCREMENT FIRST;

Removing Databases

1DROP (DATABASE | SCHEMA) [IF EXISTS] db_name;

Using IF EXISTS when dropping a database which doesn’t exist will return in a warning instead of an error, meaning the subsequent SQL commands will still run. Using IF EXISTS will keep your script running and avoid errors.