MySQL alter table (managing MySQL table settings)

Content:

What does MySQL alter table mean?
How to Alter a table via the SQL command line
How to Alter a table using PHPMyAdmin
Resources

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.

Resources:

MySQL alter table