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.
RENAME TABLE movies TO movie_table;
You can also rename multiple tables in one go
RENAME TABLE movies TO movie_table, actors TO actor_table;
DROP TABLE reviews;
You can also use the
IF EXISTS keyword (preferred)
DROP 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.
Truncate deletes a table and then creates a new empty table of the same name.
TRUNCATE TABLE movie_table;
TRUNCATE keyword doesn’t need to take the
TABLE keyword after it. The following will work just fine.
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
DELETE followed by column name and values.
ALTER TABLE movies ADD COLUMN genre VARCHAR(100);
COLUMN is an optional keyword, only
ADD will also work. Here’s another example
ALTER TABLE actors ADD (pob VARCHAR(100), dob DATE);
ALTER TABLE table_name CHANGE column_current_name column_new_name data_type;
ALTER TABLE actors CHANGE pob place_of_birth VARCHAR(100);
CHANGE can not take multiple columns. For every column name you want to change, you’ll have to create a new statement.
ALTER TABLE actors CHANGE pob place_of_birth VARCHAR(100); ALTER TABLE actors CHANGE dob date_of_birth DATE;
Change Data Type
ALTER TABLE movies CHANGE year year_released YEAR;
ALTER TABLE actors DROP date_of_birth;
Determing a Column’s place
You can determine where to add a column using the keywords
ALTER 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
ALTER TABLE movies ADD COLUMN name VARCHAR(150) AFTER id;
Here is another example
ALTER TABLE movies ADD COLUMN id INTEGER PRIMARY KEY AUTO_INCREMENT FIRST;
DROP (DATABASE | SCHEMA) [IF EXISTS] db_name;
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.