ALTER TABLE: This command is used to modify an existing table. You can use it to add, delete, or modify columns, as well as to change the type of existing columns, add and drop constraints, etc.
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
DESCRIBE (or DESC): This command provides information about a table’s columns, including their data type, whether they can be NULL, and their default values.
DESCRIBE table_name;
SHOW INDEXES: This command displays information about the indexes on a table.
SHOW INDEXES FROM table_name;
SHOW TABLE STATUS: While MySQL doesn’t have a direct command to show fragmentation, the SHOW TABLE STATUS command can provide information about the table, including data length, index length, and data free (which can give an idea about fragmentation).
SHOW TABLE STATUS LIKE 'table_name';
OPTIMIZE TABLE: This command is used to reclaim the unused space and to defragment the data file. It’s a way to reduce fragmentation in tables.
OPTIMIZE TABLE table_name;
SHOW CREATE TABLE: This command displays the SQL statement that created the table. It’s useful to see the table’s structure, including its indexes and constraints.
SHOW CREATE TABLE table_name;
SHOW TABLES: This command lists all the tables in the current database.
SHOW TABLES;
CHECK TABLE: This command checks a table or tables for errors.
CHECK TABLE table_name;
REPAIR TABLE: If a table has been corrupted, this command attempts to repair it.
