Databases Reference
In-Depth Information
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 drop-
ped. 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 in set (0.00 sec)
The warning is also generated with the error if you leave out the
IF EXISTS
clause.
