MySQL modify columns in the table

MySQL modify columns in the table

To modify the syntax of a column in a table:

A table, created, with n columns. You can then add or remove or modify columns

ALTER TABLE table name add column Name column type column parameter; [This adds the column at the end of the table]
Example: ALTER TABLE M1 add username char (a) not null default ';

ALTER TABLE table name add column Name column type column parameter after a column; [After a new column is added to a column]
Example: ALTER TABLE M1 add username char (a) not null default ';


If you want to add a column that is at the front of the table, use first
ALTER TABLE table name add column Name column type column parameter first;
Example: ALTER TABLE M1 add PID int not null first;


--------------------------------------------------------------------------------------------------------------- ----
To delete the syntax for a column in a table:

ALTER TABLE name drop column name;
Example: ALTER TABLE M1 drop PID;


--------------------------------------------------------------------------------------------------------------- ---
To modify the syntax of a column type in a table:

ALTER TABLE name modify column name new type parameter;
Example: ALTER TABLE M1 Modify gender char (4) NOT null default ';


--------------------------------------------------------------------------------------------------------------- ----
To modify the syntax for column names and types in a table:

ALTER TABLE name change old column name new type new parameter of new column name;
Example: ALTER TABLE M1 change ID uid int unsigned;

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

Here's how it's done 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 (Ten) unsigned | NO | PRI | NULL | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in Set (0.14 sec)

Mysql> ALTER TABLE M1 add username char (a) 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 (Ten) 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 ' 0000-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 (Ten) 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 (Ten) 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> #如果想增加一列, and at the front of the table, with 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 (Ten) 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> #删除列
Mysql> DESC M1;
+----------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+----------------+
| PID | Int (11) |     NO | |                NULL | |
| ID | Int (Ten) 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 (Ten) 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 (Ten) 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 (Ten) 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 ...

MySQL modify columns in the table