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
Keyword | Meaning |
---|---|
RENAME |
Rename |
DROP |
Delete |
TRUNCATE |
Empty |
Rename
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 (Delete)
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.
Empty (Truncate)
Truncate deletes a table and then creates a new empty table of the same name.
TRUNCATE TABLE movie_table;
The TRUNCATE
keyword doesn’t need to take the TABLE
keyword after it. The following will work just fine.
TRUNCATE movie_table;
Altering Columns
Keyword | Meaning |
---|---|
ADD |
Add Column |
CHANGE |
Rename Column |
DROP |
Delete Column |
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
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);
Rename Columns CHANGE
ALTER TABLE table_name CHANGE column_current_name column_new_name data_type;
ALTER 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.
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;
Delete Column DROP
ALTER 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
.
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;
Removing Databases
DROP (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.