数据库的基本操作之单列添加

来源：互联网发布：剑网南风捏脸米苏数据编辑：程序博客网时间：2024/05/16 10:22
添加单列ALTER TABLE tbl_name ADD [COLUMNS] col_name column_definition [FIRST | AFTER col_name]开始时的表结构mysql> show columns from users1;+----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(30) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | |+----------+----------------------+------+-----+---------+----------------+3 rows in set (0.04 sec)默认添加在结尾mysql> alter table users1 add age smallint unsigned default 10;Query OK, 0 rows affected (1.04 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show columns from users1;+----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(30) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || age | smallint(5) unsigned | YES | | 10 | |+----------+----------------------+------+-----+---------+----------------+4 rows in set (0.00 sec)在username列之后添加mysql> alter table users1 add password varchar(30) not null after username;Query OK, 0 rows affected (1.42 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show columns from users1;+----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(30) | NO | | NULL | || password | varchar(30) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || age | smallint(5) unsigned | YES | | 10 | |+----------+----------------------+------+-----+---------+----------------+5 rows in set (0.00 sec)在所有列之前添加mysql> alter table users1 add sex enum('1', '2', '3') default '3' first;Query OK, 0 rows affected (1.20 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show columns from users1;+----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| sex | enum('1','2','3') | YES | | 3 | || id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(30) | NO | | NULL | || password | varchar(30) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || age | smallint(5) unsigned | YES | | 10 | |+----------+----------------------+------+-----+---------+----------------+6 rows in set (0.13 sec)
0 0