Altering and Dropping Tables in MySQL
Implementing Database in MySQL – Part 7
Division 4
Forward: In this part of the series, we see how to alter and drop tables in the MySQL database.
By: Chrysanthus Date Published: 6 Aug 2012
Introduction
We are still dealing with data definition. Remember data definition deals with creating of tables, altering tables, dropping tables, creating of indexes and giving different users, privileges to different tables. We have seen how to create tables and give them indexes. In this part of the series we shall see how to alter and drop tables. We shall see how to create user accounts (user name and password) and grant them certain permissions in the next part of the series.
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Altering a Table
To create a table means to create the table structure (automatically saving the table in the database file). To alter a table, means to alter the table structure. It is not anybody who has the right to alter a table. You will be able to do that now because you are the database administrator (with userID that is root). A database administrator has the rights to do anything in the database.
We shall start by looking at some examples, which alter tables. After that I will give you the complete syntax and explain how to read the complete syntax.
Example
We continue with the wholesale example.
Type the following commands in the command prompt to start the server, connect to it and start the database; enter the password, sql:
cd c:
"C:Program FilesMySQLMySQL Server 5.1binmysql" -u root -p
USE wholesale;
Let us add a Total column to the OrderDetails table. Execute the following SQL statement:
ALTER TABLE Orders ADD Total DECIMAL(19,2);
A Total column is a computed value column. You normally do not have to include it; I have done so just for illustration.
In simple terms the SQL statement syntax to drop a table is:
DROP TABLE table-name;
You will now create a table in the database and then drop it. Dropping a table means erasing the table from the database disk.
- Type and execute the following:
CREATE TABLE Purchase (
purchase_num INTEGER NOT NULL PRIMARY KEY,
date_purchased DATE,
name CHAR(80)
);
You should now have a new table called Purchase.
- Now you will delete the table you have just created. Type the following and execute:
DROP TABLE Purchase;
The new table purchase should have been erased.
Let us now drop the connection, stop the database and stop the server. Type the following and press Enter:
QUIT
The complete syntax of the SQL Statement to alter a table is given below; glance through it and read the explanation below it:
ALTER TABLE Syntax
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD FULLTEXT [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD SPATIAL [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name [, col_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| partition_options
| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| COALESCE PARTITION number
| REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
| ANALYZE PARTITION {partition_names | ALL }
| CHECK PARTITION {partition_names | ALL }
| OPTIMIZE PARTITION {partition_names | ALL }
| REBUILD PARTITION {partition_names | ALL }
| REPAIR PARTITION {partition_names | ALL }
| PARTITION BY partitioning_expression
| REMOVE PARTITIONING
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
table_options:
table_option [[,] table_option] ... (see CREATE TABLE options)
I will just explain how I came about the following statement:
ALTER TABLE Orders ADD Total DECIMAL(19,2);
I used this statement to add a new column.
The complete syntax is in sections. The lower you go down the syntax the more details you find about a word or phrase above in the syntax. In the syntax, | means and/or at that position; anything in square brackets is optional.
The first line in the syntax is:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
From this first line, I got
ALTER TABLE Orders
for my own statement and I then proceeded to the details of, alter_specification, in the syntax. From the first line of the details of, alter_specification, I got
ADD Total DECIMAL(19,2);
for my statement.
That is the kind of procedure you should follow to determine your own Altering SQL statements. You should apply the same reasoning to understand other syntaxes in MySQL, such as the CREATE TABLE syntax we saw in one of the previous parts of the series.
Time to take a break. Let us stop here and continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT