A2-03-10.DDL-Using MySQL ALTER TABLE To Change Table Structure
转载自：http://www.mysqltutorial.org/mysql-alter-table.aspx
Using MySQL ALTER TABLE To Change Table Structure
Summary: in this tutorial, you will learn about the MySQL ALTER TABLE statement that changes existing table structure such as adding or removing columns, changing column attributes, etc.
Introduction to MySQL ALTER TABLE statement
You use the ALTER TABLE statement to change the structure of existing tables. The ALTER TABLE statement allows you to add a column, drop a column, change the data type of column, add primary key, rename table, and many more. The following illustrates the ALTER TABLE statement syntax:
|
1
|
ALTER TABLE table_name action1[,action2,…]
|
To change the structure an existing table:
- First, you specify the table name, which you want to change, after the
ALTER TABLEclause. - Second, you list a set of actions that you want to apply to the table. An action can be anything such as adding a new column, adding primary key, renaming table, etc. The
ALTER TABLEstatement allows you to apply multiple actions in a singleALTER TABLEstatement, each action is separated by a comma (,).
Let’s create a new table for practicing the ALTER TABLE statement.
We’re going to create a new table named tasks in the sample database. The following is the script for creating the tasks table.
|
1
2
3
4
5
6
7
8
9
|
CREATE TABLE tasks (
task_id INT NOT NULL,
subject VARCHAR(45) NULL,
start_date DATE NULL,
end_date DATE NULL,
description VARCHAR(200) NULL,
PRIMARY KEY (task_id),
UNIQUE INDEX task_id_unique (task_id ASC)
);
|
Changing columns using MySQL ALTER TABLE statement
Using MySQL ALTER TABLE statement to set auto-increment attribute for a column
Suppose you want the value of the task_id column to be increased automatically by one whenever you insert a new row into the tasks table. To do this, you use the ALTER TABLE statement to set the attribute of the task_id column to AUTO_INCREMENT as follows:
|
1
2
|
ALTER TABLE tasks
CHANGE COLUMN task_id task_id INT(11) NOT NULL AUTO_INCREMENT;
|
You can verify the change by inserting some rows to the tasks table.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
INSERT INTO tasks(subject,
start_date,
end_date,
description)
VALUES('Learn MySQL ALTER TABLE',
Now(),
Now(),
'Practicing MySQL ALTER TABLE statement');
INSERT INTO tasks(subject,
start_date,
end_date,
description)
VALUES('Learn MySQL CREATE TABLE',
Now(),
Now(),
'Practicing MySQL CREATE TABLE statement');
|
And you can query data to see if the value of the task_id column is increased by 1 each time you insert a new row:
|
1
2
3
4
|
SELECT
task_id, description
FROM
tasks;
|
Using MySQL ALTER TABLE statement to add a new column into a table
Because of the new business requirements, you need to add a new column called complete to store the percentage of completion for each task in the tasks table. In this case, you can use the ALTER TABLE to add a new column to the tasks table as follows:
|
1
2
3
|
ALTER TABLE tasks
ADD COLUMN complete DECIMAL(2,1) NULL
AFTER description;
|
Using MySQL ALTER TABLE to drop a column from a table
Suppose you don’t want to store the description of tasks in the tasks table and you have to remove it. The following statement allows you to remove the description column of the tasks table:
|
1
2
|
ALTER TABLE tasks
DROP COLUMN description;
|
Renaming table using MySQL ALTER TABLE statement
You can use the ALTER TABLE statement to rename a table. Notice that before renaming a table, you should take a serious consideration to understand if the change impacts both database and application layers.
The following statement renames the tasks table to the work_items table:
|
1
2
|
ALTER TABLE tasks
RENAME TO work_items;
|
In this tutorial, you’ve learned how to use the MySQL ALTER TABLE statement to change existing table structure and to rename the table.
