MySQL Terminal: Alter table, charset and delete

ALTER TABLE MODIFY

The ALTER TABLE command with the MODIFY clause in MySQL is used to change the definition of an existing column in a table. Here’s the command structure:

ALTER TABLE table_name MODIFY column_name data_type [options];

• table_name: the name of the table where the column is being modified.
• column_name: the name of the column you want to modify.
• data_type: the new data type for the column.
• [options]: any other optional specifications, such as NOT NULL, DEFAULT value, etc.

Example

Suppose you have an age column in the users table of type INT, and you want to change it to INT(3) with a default value of 18. The command would be:

ALTER TABLE users MODIFY age INT(3) DEFAULT 18;

This command changes the data type of the age column to INT(3) and sets the default value to 18.

ALTER TABLE CHANGE

The ALTER TABLE command with the CHANGE clause in MySQL is used to rename a column and change its data type or attributes. Here’s the structure of the command:

ALTER TABLE table_name CHANGE old_column_name new_column_name data_type [options];

• table_name: the name of the table where the column exists.
• old_column_name: the current name of the column you want to modify.
• new_column_name: the new name for the column.
• data_type: the new data type for the column.
• [options]: any additional specifications, such as NOT NULL, DEFAULT value, etc.

Example

Suppose you have a column age in the users table with type INT, and you want to rename it to current_age and change it to INT(3) with a default value of 18. The command would look like this:

ALTER TABLE users CHANGE age current_age INT(3) DEFAULT 18;

This command renames the age column to current_age, changes its data type to INT(3), and sets the default value to 18.

ALTER TABLE RENAME TO

The ALTER TABLE command with the RENAME TO clause in MySQL is used to rename a table. Here’s the syntax:

ALTER TABLE old_table_name RENAME TO new_table_name;

• old_table_name: the current name of the table you want to rename.
• new_table_name: the new name for the table.

Example

Suppose you have a table named users and you want to rename it to customers. The command would be:

ALTER TABLE users RENAME TO customers;

This command renames the table users to customers.

ALTER TABLE CONVERT TO CHARACTER SET

The ALTER TABLE ... CONVERT TO CHARACTER SET command in MySQL is used to change the character set and collation of an entire table. Here’s the syntax:

ALTER TABLE table_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

• table_name: the name of the table whose character set and collation you want to change.
• charset_name: the new character set for the table, such as utf8mb4.
• [COLLATE collation_name]: an optional parameter to specify the collation (e.g., utf8mb4_unicode_ci). If omitted, the default collation for the specified character set will be used.

Example

If you want to change the character set of the customers table to utf8mb4 with its default collation, you would use:

ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4;

Or, if you want to specify a specific collation, like utf8mb4_unicode_ci, you would use:

ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This command updates all text columns in the table to the specified character set and collation.

DROP TABLE

The DROP TABLE command in SQL is used to permanently delete a table and all of its data from the database. Here’s the syntax:

DROP TABLE table_name;

• table_name: the name of the table you want to delete.

Example

If you want to delete a table named customers, the command would be:

DROP TABLE customers;

Warning: Using DROP TABLE will permanently remove the table and all of its data. This action cannot be undone, so make sure to back up any important data before executing this command.