MySQL: ALTER TABLE Statement - TechOnTheNet

1. Home
2. MySQL
3. Tables

MySQL: ALTER TABLE Statement

This MySQL tutorial explains how to use the MySQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with syntax and examples).

Description

The MySQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The MySQL ALTER TABLE statement is also used to rename a table.

Add column in table

Syntax

The syntax to add a column in a table in MySQL (using the ALTER TABLE statement) is:

ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ]; table_name The name of the table to modify. new_column_name The name of the new column to add to the table. column_definition The datatype and definition of the column (NULL or NOT NULL, etc). FIRST | AFTER column_name Optional. It tells MySQL where in the table to create the column. If this parameter is not specified, the new column will be added to the end of the table.

Example

Let's look at an example that shows how to add a column in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id;

This MySQL ALTER TABLE example will add a column called last_name to the contacts table. It will be created as a NOT NULL column and will appear after the contact_id field in the table.

Add multiple columns in table

Syntax

The syntax to add multiple columns in a table in MySQL (using the ALTER TABLE statement) is:

ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ], ADD new_column_name column_definition [ FIRST | AFTER column_name ], ... ; table_name The name of the table to modify. new_column_name The name of the new column to add to the table. column_definition The datatype and definition of the column (NULL or NOT NULL, etc). FIRST | AFTER column_name Optional. It tells MySQL where in the table to create the column. If this parameter is not specified, the new column will be added to the end of the table.

Example

Let's look at an example that shows how to add multiple columns in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id, ADD first_name varchar(35) NULL AFTER last_name;

This ALTER TABLE example will add two columns to the contacts table - last_name and first_name.

The last_name field will be created as a varchar(40) NOT NULL column and will appear after the contact_id column in the table. The first_name column will be created as a varchar(35) NULL column and will appear after the last_name column in the table.

Modify column in table

Syntax

The syntax to modify a column in a table in MySQL (using the ALTER TABLE statement) is:

ALTER TABLE table_name MODIFY column_name column_definition [ FIRST | AFTER column_name ]; table_name The name of the table to modify. column_name The name of the column to modify in the table. column_definition The modified datatype and definition of the column (NULL or NOT NULL, etc). FIRST | AFTER column_name Optional. It tells MySQL where in the table to position the column, if you wish to change its position.

Example

Let's look at an example that shows how to modify a column in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE contacts MODIFY last_name varchar(50) NULL;

This ALTER TABLE example will modify the column called last_name to be a data type of varchar(50) and force the column to allow NULL values.

Modify Multiple columns in table

Syntax

The syntax to modify multiple columns in a table in MySQL (using the ALTER TABLE statement) is:

ALTER TABLE table_name MODIFY column_name column_definition [ FIRST | AFTER column_name ], MODIFY column_name column_definition [ FIRST | AFTER column_name ], ... ; table_name The name of the table to modify. column_name The name of the column to modify in the table. column_definition The modified datatype and definition of the column (NULL or NOT NULL, etc). FIRST | AFTER column_name Optional. It tells MySQL where in the table to position the column, if you wish to change its position.

Example

Let's look at an example that shows how to modify multiple columns in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE contacts MODIFY last_name varchar(55) NULL AFTER contact_type, MODIFY first_name varchar(30) NOT NULL;

This ALTER TABLE example will modify two columns to the contacts table - last_name and first_name.

The last_name field will be changed to a varchar(55) NULL column and will appear after the contact_type column in the table. The first_name column will be modified to a varchar(30) NOT NULL column (and will not change position in the contacts table definition, as there is no FIRST | AFTER specified).

Drop column in table

Syntax

The syntax to drop a column in a table in MySQL (using the ALTER TABLE statement) is:

ALTER TABLE table_name DROP COLUMN column_name; table_name The name of the table to modify. column_name The name of the column to delete from the table.

Example

Let's look at an example that shows how to drop a column in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE contacts DROP COLUMN contact_type;

This ALTER TABLE example will drop the column called contact_type from the table called contacts.

Rename column in table

Syntax

The syntax to rename a column in a table in MySQL (using the ALTER TABLE statement) is:

ALTER TABLE table_name CHANGE COLUMN old_name new_name column_definition [ FIRST | AFTER column_name ] table_name The name of the table to modify. old_name The column to rename. new_name The new name for the column. column_definition The datatype and definition of the column (NULL or NOT NULL, etc). You must specify the column definition when renaming the column, even if it does not change. FIRST | AFTER column_name Optional. It tells MySQL where in the table to position the column, if you wish to change its position.

Example

Let's look at an example that shows how to rename a column in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE contacts CHANGE COLUMN contact_type ctype varchar(20) NOT NULL;

This MySQL ALTER TABLE example will rename the column called contact_type to ctype. The column will be defined as a varchar(20) NOT NULL column.

Rename table

Syntax

The syntax to rename a table in MySQL is:

ALTER TABLE table_name RENAME TO new_table_name; table_name The table to rename. new_table_name The new table name to use.

Example

Let's look at an example that shows how to rename a table in MySQL using the ALTER TABLE statement.

For example:

ALTER TABLE contacts RENAME TO people;

This ALTER TABLE example will rename the contacts table to people.

NEXT: Drop Table Share on:

Databases

• SQL
• Oracle / PLSQL
• SQL Server
• MySQL
• MariaDB
• PostgreSQL
• SQLite

MS Office

• Excel
• Access
• Word

Web Development

• HTML
• CSS
• JavaScript
• Color Picker

Programming

• C Language

More

• ASCII
• Unicode
• Linux
• UNIX
• Techie Humor

MySQL Basics

• ALIASES
• AND
• AND & OR
• BETWEEN
• COMPARISON OPERATORS
• DELETE
• DELETE LIMIT
• DISTINCT
• EXISTS
• FROM
• GROUP BY
• HAVING
• IN
• INSERT
• INTERSECT
• IS NOT NULL
• IS NULL
• JOIN
• LIKE
• NOT
• OR
• ORDER BY
• SELECT
• SELECT LIMIT
• SUBQUERY
• TRUNCATE
• UNION
• UNION ALL
• UPDATE
• WHERE

MySQL Advanced

• Alter Table
• AUTO_INCREMENT
• Change Password
• Comments in SQL
• Create Table
• Create Table As
• Create User
• Data Types
• Declare Variables
• Drop Table
• Drop User
• Find Users
• Find Users Logged In
• Functions
• Grant/Revoke Privileges
• Indexes
• Literals
• Primary Key
• Procedures
• Rename User
• Show Grants
• Unique Constraints
• Views

MySQL Cursors

• Close Cursor
• Cursor NOT FOUND
• Declare Cursor
• Fetch Cursor
• Open Cursor

MySQL Loops/Conditionals

• CASE
• IF-THEN-ELSE
• ITERATE
• LEAVE
• LOOP
• REPEAT Loop
• RETURN
• WHILE Loop

MySQL Triggers

• After Delete Trigger
• After Insert Trigger
• After Update Trigger
• Before Delete Trigger
• Before Insert Trigger
• Before Update Trigger
• Drop Trigger

String Functions

• ASCII
• CHAR_LENGTH
• CHARACTER_LENGTH
• CONCAT
• CONCAT_WS
• FIELD
• FIND_IN_SET
• FORMAT
• INSERT
• INSTR
• LCASE
• LEFT
• LENGTH
• LOCATE
• LOWER
• LPAD
• LTRIM
• MID
• POSITION
• REPEAT
• REPLACE
• REVERSE
• RIGHT
• RPAD
• RTRIM
• SPACE
• STRCMP
• SUBSTR
• SUBSTRING
• SUBSTRING_INDEX
• TRIM
• UCASE
• UPPER

Numeric/Math Functions

• ABS
• ACOS
• ASIN
• ATAN
• ATAN2
• AVG
• CEIL
• CEILING
• COS
• COT
• COUNT
• DEGREES
• DIV
• EXP
• FLOOR
• GREATEST
• LEAST
• LN
• LOG
• LOG10
• LOG2
• MAX
• MIN
• MOD
• PI
• POW
• POWER
• RADIANS
• RAND
• ROUND
• SIGN
• SIN
• SQRT
• SUM
• TAN
• TRUNCATE

Date/Time Functions

• ADDDATE
• ADDTIME
• CURDATE
• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• CURTIME
• DATE
• DATE_ADD
• DATE_FORMAT
• DATE_SUB
• DATEDIFF
• DAY
• DAYNAME
• DAYOFMONTH
• DAYOFWEEK
• DAYOFYEAR
• EXTRACT
• FROM_DAYS
• HOUR
• LAST_DAY
• LOCALTIME
• LOCALTIMESTAMP
• MAKEDATE
• MAKETIME
• MICROSECOND
• MINUTE
• MONTH
• MONTHNAME
• NOW
• PERIOD_ADD
• PERIOD_DIFF
• QUARTER
• SEC_TO_TIME
• SECOND
• STR_TO_DATE
• SUBDATE
• SUBTIME
• SYSDATE
• TIME
• TIME_FORMAT
• TIME_TO_SEC
• TIMEDIFF
• TIMESTAMP
• TO_DAYS
• WEEK
• WEEKDAY
• WEEKOFYEAR
• YEAR
• YEARWEEK

Advanced Functions

• BIN
• BINARY
• CASE
• CAST
• COALESCE
• CONNECTION_ID
• CONV
• CONVERT
• CURRENT_USER
• DATABASE
• IF
• IFNULL
• ISNULL
• LAST_INSERT_ID
• NULLIF
• SESSION_USER
• SYSTEM_USER
• USER
• VERSION

Encryption Functions

• ENCRYPT
• MD5
• OLD_PASSWORD
• PASSWORD

Home | About Us | Contact Us | Testimonials | Donate

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy.

Copyright © 2003-2026 TechOnTheNet.com. All rights reserved.