Scribd
Upload
4 views3 pages

MySQL ALTER and TRUNCATE Examples

This document provides an overview of using ALTER and TRUNCATE statements in MySQL, highlighting their syntax and applications. It explains how to add or modify columns in a table using ALTER TABLE and how to delete all rows in a table using TRUNCATE TABLE. By the end of the reading, users should be able to execute these statements effectively.

Uploaded by

dowanan742
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views3 pages

MySQL ALTER and TRUNCATE Examples

This document provides an overview of using ALTER and TRUNCATE statements in MySQL, highlighting their syntax and applications. It explains how to add or modify columns in a table using ALTER TABLE and how to delete all rows in a table using TRUNCATE TABLE. By the end of the reading, users should be able to execute these statements effectively.

Uploaded by

dowanan742
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

4/10/25, 4:40 PM about:blank

Reading: Examples to ALTER and TRUNCATE tables using MySQL


Estimated time to complete: 5 minutes

In the previous video, the ALTER and TRUNCATE syntax applies to DB2. There are variations in syntax between different databases. This reading will
explore some examples of ALTER and TRUNCATE statements using MySQL.

Objective(s)

At the end of this reading, you will be able to:

Use the ALTER TABLE statement in the correct syntax.


Use TRUNCATE statements in syntax.
Execute examples of ALTER and TRUNCATE statements.

ALTER TABLE
ALTER TABLE statements can be used to add or remove columns from a table, to modify the data type of columns, to add or remove keys, and to add or remove
constraints. The syntax of the ALTER TABLE statement is:

ADD COLUMN syntax


ALTER TABLE table_name
ADD column_name data_type;

A variation of the syntax for adding column is:


ALTER TABLE table_name
ADD COLUMN column_name data_type;

By default, all the entries are initially assigned the value NULL. You can then use UPDATE statements to add the necessary column values.

For example, to add a telephone_number column to the author table in the library database, the statement will be written as:
ALTER TABLE author
ADD telephone_number BIGINT;

Here, BIGINT is a data type for Big Integer.


After adding the entries to the new column, a sample output is shown below.

Modify column data type

about:blank 1/3
4/10/25, 4:40 PM about:blank
ALTER TABLE table_name
MODIFY column_name data_type;

Sometimes, the data presented may be in a different format than required. In such a case, we need to modify the data_type of the column. For example, using a numeric
data type for telephone_number means you cannot include parentheses, plus signs, or dashes as part of the number. For such entries, the appropriate choice of
data_type is CHAR.

To modify the data type, the statement will be written as:


ALTER TABLE author
MODIFY telephone_number CHAR(20);

The entries can then be updated using UPDATE statements. An updated version of the "author" table is shown below.

TRUNCATE Table
TRUNCATE TABLE statements are used to delete all of the rows in a table. The syntax of the statement is:

TRUNCATE TABLE table_name;

So, to truncate the "author" table, the statement will be written as:
TRUNCATE TABLE author;

The output would be as shown in the image below.

about:blank 2/3
4/10/25, 4:40 PM about:blank

Note: The TRUNCATE statement will delete the rows and not the table.

Author
[Link]

Additional Contributor(s)
Abhishek Gagneja

about:blank 3/3

You might also like