Understanding Basic Commands in Software

Drawer Quick Response Code in Software Understanding Basic Commands

modify keys; alter the table type; and change the table name (among other things) The following sections discuss these capabilities in greater detail

To alter a table name, use an ALTER TABLE statement with a supplementary RENAME clause The following example demonstrates by renaming table bills to invoices:

mysql> ALTER TABLE airport RENAME TO cities; Query OK, 0 rows affected (028 sec)

An alternative is to use the RENAME TABLE statement, which does the same thing Here s an example, which reverses the previous operation:

mysql> RENAME TABLE cities TO airport; Query OK, 0 rows affected (006 sec)

A CHANGE clause can be used to alter a field s name, type, and properties, simply by using a new field definition instead of the original one Here s an example, which changes the field named Runways defined as INT(11) to a field named NumRunways with definition TINYINT(1):

mysql> ALTER TABLE airport CHANGE Runways NumRunways TINYINT(1); Query OK, 0 rows affected (023 sec) Records: 0 Duplicates: 0 Warnings: 0

When a field is changed from one type to another, MySQL will automatically attempt to convert the data in that field to the new type If the data in the field is inconsistent with the new field definition for example, a field defined as NOT NULL contains NULL values, or a field marked as UNIQUE contains duplicate values MySQL will generate an error To alter this default behavior, add an IGNORE clause to the ALTER TABLE statement that tells MySQL to ignore such inconsistencies

To add a new field to a table, place an ADD clause in your ALTER TABLE statement The following example demonstrates by adding a field named StartYear to the airports table:

mysql> ALTER TABLE airport ADD StartYear YEAR NOT NULL; Query OK, 0 rows affected (026 sec) Records: 0 Duplicates: 0 Warnings: 0

To do the reverse delete an existing field from a table use a DROP clause instead of an ADD clause The following example removes the field added in the previous operation (along with any data it might have contained):

mysql> ALTER TABLE airport DROP StartYear; Query OK, 0 rows affected (018 sec) Records: 0 Duplicates: 0 Warnings: 0

Part I:

To delete a table s primary key, use the DROP PRIMARY KEY clause, as illustrated here:

mysql> ALTER TABLE airport DROP PRIMARY KEY; Query OK, 0 rows affected (006 sec)

To add a new primary key, use the ADD PRIMARY KEY clause, as illustrated here:

mysql> ALTER TABLE airport ADD PRIMARY KEY (AirportID); Query OK, 0 rows affected (005 sec)

To alter the table s storage engine, add an ENGINE clause to the ALTER TABLE statement with the name of the new storage engine, as in the following example:

mysql> ALTER TABLE airport ENGINE = INNODB; Query OK, 6 rows affected (011 sec)

table, changes it, and then deletes the original table and replaces it with the changed copy For this reason, ALTER TABLE operations on large tables may take a fair amount of time

To remove a database, use the DROP DATABASE statement, which deletes the named database and all its tables permanently Similarly, to delete a table, use the DROP TABLE statement Try this out by creating and dropping a database and a table:

mysql> CREATE DATABASE music; Query OK, 1 row affected (005 sec) mysql> CREATE TABLE member ( MemberID INT NOT NULL ); Query OK, 0 rows affected (000 sec) mysql> DROP TABLE member; Query OK, 0 rows affected (000 sec) mysql> DROP DATABASE music; Query OK, 0 rows affected (049 sec)

These DROP statements will immediately wipe out the target, along with all the data it contains so use them with care!