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 asNOT 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 asNOT 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 asutf8mb4
. -
[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.
Top comments (0)