Altering Structures | 219

You can find more about these operations in the MySQL manual under the “ALTER
DATABASE” and ALTER TABLE headings.
Beginning with MySQL 5.1, you can also change the name of a database using the new
RENAME DATABASE
command:
mysql> RENAME DATABASE old_database_name new_database_name;
Query OK, 0 rows affected (0.01 sec)
Deleting Structures
In the previous section, we showed how you can delete columns and rows from a database now we’ll describe how to remove databases and tables.
Dropping Databases
Removing, or dropping, a database is straightforward. Here’s how you drop the music database:
mysql> DROP DATABASE music;
Query OK, 4 rows affected (0.01 sec)
The number of rows returned in the response is the number of tables removed. You should take care when dropping a database, since all its tables, indexes, and columns are deleted, as are all the associated disk-based files and directories that MySQL uses to maintain them.
If a database doesn’t exist, trying to drop it causes MySQL to report an error. Let’s try dropping the music database again:
mysql> DROP DATABASE music;
ERROR 1008 (HY000): Can't drop database 'music database doesn't exist
You can avoid the error, which is useful when including the statement in a script, by using the IF EXISTS
phrase:
mysql> DROP DATABASE IF EXISTS music;
Query OK, 0 rows affected, 1 warning (0.00 sec)
You can see that a warning is reported, since the music database has already been dropped. You can always check what the warning was with the SHOW WARNINGS
statement,
which has been available since MySQL 4.1.0:
mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------+
| Note | 1008 | Can't drop database 'music database doesn't exist |
+-------+------+-----------------------------------------------------+
1 row inset sec)
The warning is also generated with the error if you leave out the IF EXISTS
clause.

Download 4.24 Mb.

Share with your friends: