语句:alter table city1 add age tinyint UNSIGNED NOT NULL DEFAULT 10; 解释:更改 表格 city1, 添加参数age ,类型:tinyint ,无符号,非空,默认值10
//更改表格city1,添加age列,类型:tinyint ,无符号类,非空,默认值:10 mysql> alter table city1 add age tinyint UNSIGNED NOT NULL DEFAULT 10; Query OK, 2 rows affected (0.53 sec)//插入成功 //查看插入结果,会发现我们数据表中增加了一列,默认值为10. mysql> SHOW COLUMNS FROM CITY1;//不指定位置默认放在列末尾 //插入前不存在age列 +---------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | usename | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | +---------+----------------------+------+-----+---------+----------------+ //插入后存在age列,插入成功 +---------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | usename | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | +---------+----------------------+------+-----+---------+----------------+语句:alter table city1 add password varchar(10) NOT NULL AFTER usename; 解释:更改 表格 city1 添加参数password 字符类型 位置位于usename后
注意:这里我们指定了某列之后:AFTER usename,就是位置位于usename下方
mysql> alter table city1 add password varchar(10) NOT NULL AFTER usename; Query OK, 2 rows affected (0.32 sec) //插入成功 mysql> show columns from city1 //插入前,查看city1表的结构不存在password列 +---------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | usename | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | +---------+----------------------+------+-----+---------+----------------+ //插入后,查看city1表的结构,我们插入的password列位于usename 之后 +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | usename | varchar(20) | NO | | NULL | | | password | varchar(10) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | +----------+----------------------+------+-----+---------+----------------+语句:alter table city1 add tname varchar(22) NOT NULL FIRST; 解释:更改 表格 city1 添加参数tname 字符类型 不能为空 位于顶部
mysql> alter table city1 add tname varchar(22) NOT NULL FIRST; Query OK, 2 rows affected (0.25 sec) //插入成功过 mysql> SHOW COLUMNS FROM CITY1;//查看city1表的结构,我们插入的tname列位于列的顶端 //插入前,查询列表,暂无tname列 +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | usename | varchar(20) | NO | | NULL | | | password | varchar(10) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | +----------+----------------------+------+-----+---------+----------------+ //插入后,查询列表,tname列位于列表的顶端 +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | tname | varchar(22) | NO | | NULL | | | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | usename | varchar(20) | NO | | NULL | | | password | varchar(10) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | +----------+----------------------+------+-----+---------+----------------+ 6 rows in set (0.01 sec)语句:alter table t_name add[column](col_name column_definition,……) 解释:更改 表格 某某 添加 【列】 (某列,某列,……)
mysql> alter table city1 add ( -> ttt varchar(20) NOT NULL, -> uuu varchar(20) NOT NULL); Query OK, 2 rows affected (0.33 sec) //添加成功 mysql> show columns from city1;//添加的两列ttt和uuu位于列的最下方 //插入前 +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | tname | varchar(22) | NO | | NULL | | | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | usename | varchar(20) | NO | | NULL | | | password | varchar(10) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | +----------+----------------------+------+-----+---------+----------------+ //插入后,ttt和uuu两列插入成功 +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | tname | varchar(22) | NO | | NULL | | | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | usename | varchar(20) | NO | | NULL | | | password | varchar(10) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | | ttt | varchar(20) | NO | | NULL | | | uuu | varchar(20) | NO | | NULL | | +----------+----------------------+------+-----+---------+----------------+ 8 rows in set (0.01 sec)语句:alter table tal_name drop [column] col_name 解释:更改 表格 表名册 删除 【列】 列名册