Search⌘ K
Home
Courses
An Introductory Guide to SQL

Alterations

Explore how to modify existing MySQL tables using the ALTER TABLE statement. Learn to rename columns, change data types, add or remove columns, and manage column positions. Understand when altering tables can be costly and how to combine multiple changes into one statement for efficiency.

We'll cover the following...

Alterations

MySQL allows us to change our mind about the entities we create and alter them. We can rename tables, add, remove, or rename columns, change type of an existing column, etc.

Example Syntax

ALTER TABLE table

CHANGE oldColumnName newColumnName <datatype> <restrictions>;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/17lesson.sh and wait for the MySQL prompt to start-up.

MySQL
-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 



-- Query 1

ALTER TABLE Actors CHANGE FirstName First_Name varchar(120);



-- Query 2

ALTER TABLE Actors MODIFY First_Name varchar(20) DEFAULT "Anonymous";



-- Query 3

ALTER TABLE Actors CHANGE First_Name First_Name varchar(20) DEFAULT "Anonymous";



-- Query 4

ALTER TABLE Actors MODIFY First_Name INT;



-- Query 5

ALTER TABLE Actors MODIFY First_Name varchar(300);



-- Query 6

ALTER TABLE Actors ADD MiddleName varchar(100);



-- Query 7

ALTER TABLE Actors DROP MiddleName;



-- Query 8

ALTER TABLE Actors ADD MiddleName varchar(100) FIRST;



-- Query 9

ALTER TABLE Actors DROP MiddleName;

ALTER TABLE Actors ADD MiddleName varchar(100) AFTER DoB;



--Query 10

ALTER TABLE Actors DROP MiddleName, ADD Middle_Name varchar(100);
Terminal 1
Terminal
Loading...

  1. Let’s say we want to rename the column FirstName to First_Name for the Actors table. We can do so as follows:

    ALTER TABLE Actors CHANGE FirstName First_Name varchar(120);
...