MySQL alter table
Table of contents :
What does MySQL alter table mean?
The management of MySQL databases
is one of the basic tasks that the owner of a content-loaded website
has to deal with. This includes many key operations such as create user
, create database
and create table
. The possibility to manage MySQL tables
' settings is the key to keeping the information on your website always up to date and secure. This is done through the MySQL-alter-table option in the command line. Using the ALTER TABLE statement you are able to rename an existing table. Also, it can be used to add, modify or drop a column
from an existing table. Also, such options are available via the graphic interface of popular MySQL administration tools such as phpMyAdmin
, or by using a MySQL query in a PHP script
How to Alter a table via the SQL command line?
The most commonly used method for managing a database table is the MySQL Server-inbuilt SQL command line. To enter the command line you need to establish a connection to the server
via a terminal or an SSH
client, and then log in to the server using the root or other account with the appropriate rights.
How to log in to a specific database on a MySQL server:
mysql -u myuser -p'mypass' db_phonebook
Once logged on the server you can manage your database tables using the ALTER TABLE statement. To add a column called 'email
' in the 'phonebook' MySQL table you can use a query similar to the one below:
An example of how to add a column using the Alter Table in MySQL
ALTER TABLE phonebook ADD COLUMN email VARCHAR(128); adds a new column 'email' into 'phonebook' table
Also, you can rename a table or a column in a table. To rename the table ‘phonebook’ to 'contacts' you need to use the query below:
An example of how to rename a table using the Alter Table in MySQL
ALTER TABLE phonebook RENAME TO contacts
Also, if you want to rename a column from 'last_name' to 'first_name' in the 'contacts' table, use this query:
An example of how to rename a column in a table using the Alter Table in MySQL
ALTER TABLE contacts RENAME COLUMN last_name to first_name;
If you want to modify a column in an existing table you need to use the ALTER TABLE and MODIFY statements together:
How to modify an existing column in a table using the Alter Table in MySQL
ALTER TABLE contacts MODIFY first_name varchar2(100) not null;
Also, using the SQL command line you are allowed to delete a column from a table. To drop a column called 'first_name' from the table called 'contacts' we will use the query below:
How to delete a column in a table using the Alter Table in MySQL
ALTER TABLE contacts DROP COLUMN first_name;
How to Alter a table using PHPMyAdmin?
With all the web hosting
plans offered by NTC Hosting you get the phpMyAdmin interface readily integrated into the Control Panel, which gives you full control over your MySQL databases and their tables. You can apply any necessary changes to the settings of an existing table as soon as you go to the MySQL Databases section and log into the phpMyAdmin panel.
To ALTER a table using the PHPMyAdmin tool, please select the table from the list, and then click on the table's 'Operations' tab. There are a variety of alter-table options within the phpMyAdmin interface. The most important options include the possibility to change or delete the existing values for any of the table creation entries such as the name of a field, its type and the length of the allowed characters, the collation method, the field's attributes, etc. Also the Operations section allows you to alter the table order, and to rename, move or copy the table.