MySQL: modifying columns in a table

Source: Internet
Author: User

MySQL: modifying columns in a table

Modifying columns in a MySQL table


Modify the column syntax in the table:

Create a table with N columns. You can also add, delete, or modify columns.

Alter table name add column name column type column parameter; [add the column at the end of the table]
Example: alter table m1 add username char (20) not null default '';

Alter table name add column name column type column parameter after a column; [add a new column after a column]
Example: alter table m1 add username char (20) not null default '';


If you want to add a column at the beginning of the table, use first
Alter table name add column name column type column parameter first;
For example, alter table m1 add pid int not null first;


Bytes -------------------------------------------------------------------------------------------------------------------
Syntax for deleting columns in a table:

Alter table Name drop column name;
For example, alter table m1 drop pid;


Bytes ------------------------------------------------------------------------------------------------------------------
Modify the syntax of the column type in the table:

Alter table name, modify column name, new type, new parameter;
Example: alter table m1 modify gender char (4) not null default '';


Bytes -------------------------------------------------------------------------------------------------------------------
Syntax for modifying column names and types in a table:

Alter table name change old column name new type new parameter;
Example: alter table m1 change id uid int unsigned;





Bytes ---------------------------------------------------------------------------------------------------------------

The following are the operations in mysql:

Mysql> create table m1 (
-> Id int unsigned auto_increment primary key
-> );
Query OK, 0 rows affected (0.62 sec)

Mysql> desc m1;
+ ------- + ------------------ + ------ + ----- + --------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------------ + ------ + ----- + --------- + ---------------- +
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
+ ------- + ------------------ + ------ + ----- + --------- + ---------------- +
1 row in set (0.14 sec)

Mysql> alter table m1 add username char (20) not null default '';
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0

Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + --------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + --------- + ---------------- +
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
+ ---------- + ------------------ + ------ + ----- + --------- + ---------------- +
2 rows in set (0.01 sec)

Mysql> alter table m1 add birth date not null default '2017-00-00 ';
Query OK, 0 rows affected (0.46 sec)
Records: 0 Duplicates: 0 Warnings: 0

Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
3 rows in set (0.01 sec)

Mysql> alter table m1 add gender char (1) not null default ''after username;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0

Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
| Gender | char (1) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
4 rows in set (0.01 sec)

Mysql> # If you want to add a column at the beginning of the table, use first
Mysql> alter table m1 add pid int not null default ''first;
ERROR 1067 (42000): Invalid default value for 'pid'
Mysql> alter table m1 add pid int not null first;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0

Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Pid | int (11) | NO | NULL |
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
| Gender | char (1) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
5 rows in set (0.01 sec)

Mysql> # delete a column
Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Pid | int (11) | NO | NULL |
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
| Gender | char (1) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
5 rows in set (0.01 sec)

Mysql> alter table m1 drop pid;
Query OK, 0 rows affected (0.37 sec)
Records: 0 Duplicates: 0 Warnings: 0

Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
| Gender | char (1) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
4 rows in set (0.01 sec)

Mysql> alter table m1 modify gender char (4) not null default '';
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0

Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Username | char (20) | NO |
| Gender | char (4) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ---------------- +
4 rows in set (0.05 sec)

Mysql> alter table m1 change id uid int unsigned;
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0

Mysql> desc m1;
+ ---------- + ------------------ + ------ + ----- + ------------ + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------ + ------ + ----- + ------------ + ------- +
| Uid | int (10) unsigned | NO | PRI | 0 |
| Username | char (20) | NO |
| Gender | char (4) | NO |
| Birth | date | NO | 0000-00-00 |
+ ---------- + ------------------ + ------ + ----- + ------------ + ------- +
4 rows in set (0.01 sec)

Mysql> exit;



----------------------------------------------------------------------------------

Welcome to explore and learn ......



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.