1 Increase of two fields:
[SQL]View Plaincopy
- mysql> Create table id_name (id int,name varchar (20));
- Query OK, 0 rows affected (0.13 sec)
- mysql> alter table Id_name add age int,add address varchar (11);
- Query OK, 0 rows affected (0.13 sec)
- records:0 duplicates:0 warnings:0
- mysql> desc id_name;
- +---------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+-------------+------+-----+---------+-------+
- | ID | Int (11) | YES | | NULL | |
- | name | varchar (20) | YES | | NULL | |
- | Age | Int (11) | YES | | NULL | |
- | Address | varchar (11) | YES | | NULL | |
- +---------+-------------+------+-----+---------+-------+
- 4 rows in Set (0.00 sec)
- 2. Delete two fields
- mysql> alter table id_name drop column age,drop column address;
- Query OK, 0 rows affected (0.14 sec)
- records:0 duplicates:0 warnings:0
- mysql> desc id_name;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | ID | Int (11) | YES | | NULL | |
- | name | varchar (20) | YES | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- 2 rows in Set (0.00 sec)
- 3. Insert
- mysql> INSERT INTO id_name values (1,' QUSTDJX ');
- Query OK, 1 row Affected (0.00 sec)
- 4. See for yourself
- mysql> alter table Id_name add age int,add address varchar (11);
- Query OK, 1 row affected (0.07 sec)
- Records:1 duplicates:0 warnings:0
- Mysql> SELECT * from id_name;
- +------+---------+------+---------+
- | ID | Name | age | address |
- +------+---------+------+---------+
- | 1 | QUSTDJX | NULL | NULL |
- +------+---------+------+---------+
- 1 row in Set (0.00 sec)
- 5. Add fields and insert
- mysql> INSERT INTO id_name values (2,' qust ', +,' Shandong ');
- Query OK, 1 row Affected (0.00 sec)
- Mysql> SELECT * from id_name;
- +------+---------+------+---------+
- | ID | Name | age | address |
- +------+---------+------+---------+
- | 1 | QUSTDJX | NULL | NULL |
- | 2 | Qust | 14 | Shandong |
- +------+---------+------+---------+
- 2 rows in Set (0.00 sec)
1. Add a field
ALTER TABLE user add COLUMN new1 VARCHAR () DEFAULT NULL; Add a field, default is empty
ALTER TABLE user add COLUMN new2 VARCHAR (a) not NULL; Add a field that cannot be empty by default www.2cto.com
2. Delete a field
ALTER TABLE user DROP COLUMN new2; Delete a field
3. Modify a Field
ALTER TABLE user MODIFY New1 VARCHAR (10); Modify the type of a field
ALTER TABLE user change new1 new4 int; To modify the name of a field, be sure to re-specify the field's type
Add a Delete field to a table in MySQL