In this second part of the MySQL Beginner Series Basics, I will explore various uses of the ALTER TABLE
syntax. Part 1 covered the CREATE
command, establishing a database, table, and user. This blog post will build from there, so be sure to visit that post to get up to speed.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and MySQL 5.7.20 for these exercises.

Photo by Luca Bravo on Unsplash
Objectives.
- Use
ALTER TABLE ADD COLUMN
syntax to add in a new column to an existing table. - Use the
CHANGE
clause to change the name of existing columns in the target table. - Use the
MODIFY
clause to change a columns’ definition, but not its name.
Adding in a new column.
Below is a description of the target ‘friends’ table I will be working with.
1 2 3 4 5 6 7 8 9 10 11 | mysql> DESC friends; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | friend_id | int(11) | NO | PRI | NULL | auto_increment | | first_name | varchar(30) | NO | | NULL | | | last_name | varchar(30) | NO | | NULL | | | cell_phone | varchar(15) | YES | | NULL | | | age | int(11) | YES | | NULL | | +------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.05 sec) |
This friends
table would not serve us well at all if we did not remember a friend’s birthday. How about we add that column to the table?
Having limited privileges, this_user
(created in Part 1), needs the ALTER
command privilege in order to change or modify the friends
table.
That privilege is granted with the below command:
Query OK, 0 rows affected (0.07 sec)
Now this_user
canALTER
the friends
table and add in that birthday column.
Query OK, 0 rows affected (0.64 sec)
Records: 0 Duplicates: 0 Warnings: 0
With the above command, I have added the f_bday
column and defined it to have the DATE
data type.
Let’s confirm by re-checking the table description:
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| friend_id | int(11) | NO | PRI | NULL | auto_increment |
| first_name | varchar(30) | NO | | NULL | |
| last_name | varchar(30) | NO | | NULL | |
| cell_phone | varchar(15) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| f_bday | date | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
Sweet. Now we will not forget a single birthday!
That column name is… meh
Suppose you want to change the first_name
, last_name
, and cell_phone
column names prefixing them with an f to denote a ‘friend’ column.
All 4 column names are changed and prefixed with f by using the CHANGE
clause within the ALTER TABLE
command:
Query OK, 0 rows affected (0.74 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
Shown above, the basic syntax for renaming a column with the CHANGE
clause is:
Noteworthy: Notice the column definition is still required for changing only the column name, when using the CHANGE
clause. To keep the original definition, just specify it in the command syntax while renaming.
And checking out the column names.
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| friend_id | int(11) | NO | PRI | NULL | auto_increment |
| f_first_name | varchar(30) | YES | | NULL | |
| f_last_name | varchar(30) | YES | | NULL | |
| f_cell_phone | varchar(15) | YES | | NULL | |
| f_age | int(11) | YES | | NULL | |
| f_bday | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
Boom! Now I know for certain what those column names represent.
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
This data type needs to be reconfigured…
Finally for demonstration purposes, suppose you want to change the data type of the f_cell_phone
column from VARCHAR(15)
to CHAR(12)
?
Since most phone numbers, to my knowledge (In the U.S. at least), have 12 total values, including two dashes (–), the CHAR
data type would be a good fit for this column, as it will be fixed length.
Storing the area code, 7-digit number, and both dashes, it likely will maintain this consistency
In this instance, the ALTER TABLE
command along with the MODIFY
clause will accomplish this.
Query OK, 1 row affected (1.76 sec)
Records: 1 Duplicates: 0 Warnings: 0
This syntax is faily simple,
TIP:
You can use the CHANGE
clause in lieu of the MODIFY
clause for this purpose as well.
A final description of the friends
table, showing all the changes made this session.
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| friend_id | int(11) | NO | PRI | NULL | auto_increment |
| f_first_name | varchar(30) | YES | | NULL | |
| f_last_name | varchar(30) | YES | | NULL | |
| f_cell_phone | char(12) | YES | | NULL | |
| f_age | int(11) | YES | | NULL | |
| f_bday | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
Key Takeaways.
CHANGE
clause:- Can rename or re-define a column or both.
- Using
FIRST
orAFTER
can reorder columns. (Not shown in this post)
MODIFY
clause:- Cannot change a column’s name, but can change its definition.
- Can also reorder columns using the
FIRST
andAFTER
commands. (Not shown in this post)
Closing.
Pairing the ALTER TABLE
command, with the CHANGE
and MODIFY
clauses, provides the ability to change table column names and definitions as your programming or database specifications change and dictate.
Join me in the next part of the series where I will INSERT
data into our table.
Feel free to visit the official MySQL 5.7 Online Manual for further study or questions.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Have I mentioned how much I love a cup of coffee?!?!
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
5 thoughts on “ALTER TABLE: Examples with MySQL – Beginner Series.”