Home arrow MySQL arrow Page 6 - Data Definition Language, Part 1

4.7 DROP TABLE - MySQL

Studying for the MySQL Certification exam? This article, the first of two parts, covers roughly 10 percent of the material that will appear on the exam, including general database and table properties, storage engines and table types, and more. It is excerpted from chapter four of the book MySQL Certification Guide written by Paul Dubois et. al. (Sams, 2004, ISBN: 0672326329).

TABLE OF CONTENTS:
  1. Data Definition Language, Part 1
  2. 4.1 General Database and Table Properties
  3. 4.3 Limits on Number and Size of Database Components
  4. 4.4 Identifier Syntax
  5. 4.5 CREATE DATABASE and DROP DATABASE
  6. 4.7 DROP TABLE
  7. 4.9 Creating and Dropping Indexes
  8. 4.10 Column Types
  9. 4.10.2 String Column Types
  10. 4.10.3 Date and Time Column Types
  11. 4.10.4 Column Options
  12. 4.10.5 Using the AUTO_INCREMENT Column Option
  13. 4.10.6 Automatic Type Conversion and Value Clipping
By: Sams Publishing
Rating: starstarstarstarstar / 41
January 19, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

When you no longer need a table, you can destroy it with the DROP TABLE statement:

DROP TABLE t;

In MySQL, a single DROP TABLE statement can name several tables to be dropped simultaneously:

DROP TABLE t1, t2, t3;

Normally, an error occurs if you attempt to drop a table that does not exist:

mysql>
DROP TABLE no_such_table;ERROR 1051: Unknown table 'no_such_table'

To prevent an error from occurring if a table does not exist when you attempt to drop it, add an IF EXISTS clause to the statement:

mysql>
DROP TABLE IF EXISTS no_such_table;

If you drop a table by mistake, you must recover it from backups, so be careful. (This is the same principle as the one mentioned earlier for databases: If you drop a database, you cannot undo the action. A dropped database can only be recovered from your backups.)

4.8 ALTER TABLE

After creating a table, you might discover that its structure is not quite right for its intended use. If that happens, you can change the table's structure. One way to do this is to remove the table with DROP TABLE and then issue another CREATE TABLE statement that defines the table correctly. This can be a drastic method: If the table already contains data, dropping and re-creating the table destroys its contents unless you first make a backup. To change a table "in place," use the ALTER TABLE statement. With ALTER TABLE, you can modify a table's structure in the following ways:

  • Add or drop columns

  • Change the name or definition of a column

  • Add or drop indexes

  • Sort the table's rows in a particular order

  • Rename the table

This section describes how to perform all the possible changes except for adding and dropping indexes. Adding and dropping of indexes is covered in a later section that focuses specifically on indexing issues. (See section 4.9, "Creating and Dropping Indexes.")

Most of the examples shown in this section use a table named HeadOfState, designed to keep track of world leaders. Assume that the table initially has the following structure:

CREATE TABLE HeadOfState
(
ID     INT NOT NULL,
LastName  CHAR(30) NOT NULL,
FirstName  CHAR(30) NOT NULL,
CountryCode CHAR(3) NOT NULL,
);

The corresponding DESCRIBE output for the table is as follows:

mysql>
DESCRIBE HeadOfState;+-------------+----------+-----+---
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| ID          | int(11)  |      |     | 0       |       |
| LastName    | char(30) |      |     |         |       |
| FirstName   | char(30) |      |     |         |       |
| CountryCode | char(3)  |      |     |         |       |
+-------------+----------+------+-----+---------+-------+
4.8.1 Adding and Dropping Columns

To add a new column to a table, use ALTER TABLE with an ADD clause that specifies the column's definition. A column definition uses the same syntax for ALTER TABLE as for CREATE TABLE. For example, to add a DATE column named Inauguration to record the date the leaders listed in the table assumed office, you can issue this statement:

ALTER TABLE HeadOfState ADD Inauguration DATE NOT NULL;

This ALTER TABLE changes the table structure as follows:

mysql>
DESCRIBE HeadOfState;+--------------+----------+------+-----+
| Field        | Type     | Null | Key | Default    | Extra |
+--------------+----------+------+-----+------------+-------+
| ID           | int(11)  |      |     | 0          |       |
| LastName     | char(30) |      |     |            |       |
| FirstName    | char(30) |      |     |            |       |
| CountryCode  | char(3)  |      |     |            |       |
| Inauguration | date     |      |     | 0000-00-00 |       |
+--------------+----------+------+-----+------------+-------+

As shown in the DESCRIBE output, when you add a new column to a table, MySQL places it after all existing columns. This is the default placement unless you specify otherwise. To indicate that MySQL should place the new column in a specific position within the table, append either the keyword FIRST or the keyword-identifier combination AFTER column_name to the column definition. For example, assume that you had executed this ALTER TABLE statement instead of the previous one:

ALTER TABLE HeadOfState ADD Inauguration DATE NOT NULL FIRST;

The FIRST keyword tells ALTER TABLE to place the new column before all existing columns (in the "first" position), resulting in the following table structure:

mysql>
DESCRIBE HeadOfState;+--------------+----------+------+-----+
| Field        | Type     | Null | Key | Default    | Extra |
+--------------+----------+------+-----+------------+-------+
| Inauguration | date     |      |     | 0000-00-00 |       |
| ID           | int(11)  |      |     | 0          |       |
| LastName     | char(30) |      |     |            |       |
| FirstName    | char(30) |      |     |            |       |
| CountryCode  | char(3)  |      |     |            |       |
+--------------+----------+------+-----+------------+-------+

Using AFTER column_name tells ALTER TABLE to place the new column after a specific existing column. For example, to place the new Inauguration column after the existing FirstName column, you would issue this statement:

ALTER TABLE HeadOfState ADD Inauguration DATE NOT NULL AFTER FirstName;

This ALTER TABLE statement would result in a table structure that looks like this:

mysql>
DESCRIBE HeadOfState;+--------------+----------+------+-----+
| Field        | Type     | Null | Key | Default    | Extra |
+--------------+----------+------+-----+------------+-------+
| ID           | int(11)  |      |     | 0          |       |
| LastName     | char(30) |      |     |            |       |
| FirstName    | char(30) |      |     |            |       |
| Inauguration | date     |      |     | 0000-00-00 |       |
| CountryCode  | char(3)  |      |     |            |       |
+--------------+----------+------+-----+------------+-------+

You cannot add a column with the same name as one that already exists in the table; column names within a table must be unique. Column names are not case sensitive, so if the table already contains a column named ID, you cannot add a new column using any of these names: ID, id, Id, or iD. They all are considered to be the same name.

To drop a column, use a DROP clause. In this case, it's necessary only to name the column you want to drop:

ALTER TABLE table_name DROP column_name;
4.8.2 Modifying Existing Columns

There are two ways to change the definition of an existing column within a table. One of these also enables you to rename the column.

The first way to alter a column definition is to use a MODIFY clause. You must specify the name of the column that you want to change, followed by its new definition. Assume that you want to change the ID column's datatype from INT to BIGINT, to allow the table to accommodate larger identification numbers. You also want to make the column UNSIGNED to disallow negative values. The following statement accomplishes this task:

ALTER TABLE HeadOfState MODIFY ID BIGINT UNSIGNED NOT NULL;

DESCRIBE now shows the table structure to be as follows:

mysql>
DESCRIBE HeadOfState;+--------------+---------------------+------+-----+
| Field        | Type                | Null | Key | Default    | Extra |
+--------------+---------------------+------+-----+------------+-------+
| ID           | bigint(20) unsigned |      |     | 0          |       |
| LastName     | char(30)            |      |     |            |       |
| FirstName    | char(30)            |      |     |            |       |
| Inauguration | date                |      |     | 0000-00-00 |       |
| CountryCode  | char(3)             |      |     |            |       |
+--------------+---------------------+------+-----+------------+-------+

Note that if you want to disallow NULL in the column, the column definition provided for MODIFY must include the NOT NULL option, even if the column was originally defined with NOT NULL. This is true for other column options as well; if you don't specify them explicitly, the new definition won't carry them over from the old definition.

The second way to alter a column definition is to use a CHANGE clause. CHANGE enables you to modify 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 you must specify the existing name twice if you want to change only the column definition (and not the name). For example, to change the LastName column from CHAR(30) to CHAR(40) without renaming the column, you'd do this:

ALTER TABLE HeadOfState CHANGE LastName LastName CHAR(40) NOT NULL;

To change the name as well (for example, to Surname), provide the new name following the existing name:

ALTER TABLE HeadOfState CHANGE LastName Surname CHAR(40) NOT NULL;
4.8.3 Renaming a Table

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 renaming operations in a single statement. One use for this feature is to swap the names of two tables:

RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2;
4.8.4 Specifying Multiple Alterations

You can specify multiple alterations for a table with a single ALTER TABLE statement. Just separate the actions by commas:

ALTER TABLE HeadOfState RENAME TO CountryLeader,
MODIFY ID BIGINT UNSIGNED NOT NULL,
ORDER BY LastName, FirstName;

This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: