YOUR WEBSITE

To remove records from a table without removing the table itself, use the DELETE or TRUNCATE TABLE  statement. Either of the following statements completely empties the named table:   DELETE FROM t;  TRUNCATE TABLE t;  DELETE takes an optional WHERE clause that identifies which records to remove. This is useful when only  a given subset of records from a table need to be deleted. The following statement removes only those  records from t that have a status column value of 'expired', in order by the id column and output limited to  4 rows:   DELETE FROM t WHERE status = 'expired' ORDER BY id LIMIT 4;  TRUNCATE also will reset the metadata held within the format files of the table being truncated ( .frm )  such as the AUTO_INCREMENT starting value. 

Renaming a table changes neither a table's structure nor its contents. The following statement renames table t1 to t2: ALTER TABLE t1 RENAME TO t2; Another way to rename a table is by using the RENAME TABLE statement: RENAME TABLE t1 TO t2; RENAME TABLE has an advantage over ALTER TABLE in that it can perform multiple table renames in a  single operation. One use for this feature is to swap the names of two tables: RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2; For TEMPORARY tables, RENAME TABLE does not work. The ALTER TABLE command must be used  instead.

The second way to alter a column definition is to use a CHANGE clause. The CHANGE clause enables  the modification of both the column's definition and its name. To use this clause, specify the CHANGE  keyword, followed by the column's existing name, its new name, and its new definition, in that order. Note  that this means the existing name must be specified twice to change only the column definition (and not the  name). For example, change the last_name column from CHAR(30) to CHAR(40) without renaming  the column as follows: ALTER TABLE head_of_state CHANGE last_name last_name CHAR(40) NOT  NULL; To change the name as well (for example, to Surname), provide the new name following the existing  name: ALTER TABLE head_of_state CHANGE last_name Surname CHAR(40) NOT NULL; To change a column at a specific position within a table row, use FIRST or AFTER col_name. The  default is to change the last column.

After creating a table, there might be a change of requirements that forces changes to the structure of the  table.  If that happens, the table's structure can be modified without having to re-create the table.  This  section addresses some of the methods available for modifying the table structure and data.  Add Columns  For example, to add a column named Id to the eu_countries table, the following statement can be issued:  ALTER TABLE eu_countries ADD Id INT(6) NOT NULL;  That ALTER TABLE/ADD statement changes the table structure as follows:  mysql> DESCRIBE eu_countries;  +----------------------+-------------------+------+-----+-----------+--------+  | Field                    | Type                | Null | Key | Default  | Extra |  +----------------------+-------------------+------+-----+-----------+--------+  | name                   | char(52)          | NO   |       | NULL    |           |  | new_population | decimal(12,1) | NO   |       | 0.0        |  

To drop a column, use a DROP clause that names the column to be removed: ALTER TABLE eu_countries DROP id; The above ALTER TABLE/DROP statement changes the table structure as follows: +----------------+---------------+------+-----+---------+-------+ | Field                 | Type                | Null   | Key | Default    | Extra   | +----------------+---------------+------+-----+---------+-------+ | name                 | char(52)          | NO   |          | NULL    |           | | new_population | decimal(12,1)  | NO   |          | 0.0         |           | +----------------+---------------+-----+------+---------+-------+