MYSQL数据库（九）

2024-07-09 15:51| 来源: 网络整理| 查看: 265

目录一、修改列定义（modify col_name）二、修改列类型（modify col_name）三、修改列名称（change col_name）三、修改表名称(rename table a to b）用到的语句 a、修改列定义和名称：

语法一：Alter table tbl_name modify[column] col_name [first|after col_name]

讲解：更改数据表某某表修改【列】列名称列定义【位置|位置】

语法二：alter table tab_name change[column] old_name new_name [first|after col_name]

讲解：更改数据表某某表修改【列】列名称列定义【位置|位置】

注意：语法二的功能要比语法一大很多，因为语法二既可以更改列定义，由可以更改列名称

b、修改表名称：

语法一： alter table tbl_name rename[to|as] new_tbl_name

讲解：可以更改一张数据表名称

语法二：rename table tbl_name to new_tbl_name [,tbl_name2 TO new_tbl_namew]……

讲解：可以多表更改名称

一、修改列定义(改变age列的位置，使用modify) 先确定我们数据库存在我们操作的数据表city2 mysql> show columns from city2;//查询数据表 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | | 0 | | | username | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +----------+----------------------+------+-----+---------+-------+ 案例 //具体操作语句：更改数据表city2,修改age列位于username之后 mysql> alter table city2 modify age tinyint unsigned NOT NULL after username; Query OK, 0 rows affected (0.21 sec)//修改成功 //修改前age列位于pid之下 mysql> show columns from city2; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | | 0 | | | username | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +----------+----------------------+------+-----+---------+-------+ //修改后，age位于username之下，pid之上 +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | | 0 | | | username | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) 二、修改列类型（修改age列的储存类型，使用modify）注意：由大类型改变成小类型的时候有可能造成数据的丢失

语句： alter table city2 modify age smallint unsigned NOT NULL; 解析：更改数据表city2 修改age列，类型：smallint,无符号，非空

//具体操作 mysql> alter table city2 modify age smallint unsigned NOT NULL; Query OK, 0 rows affected (0.18 sec)//修改成功 //查询数据表 mysql> show columns from city2; //修改前，age的类型为tinyint +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | | 0 | | | username | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ //修改后，age的类型改变为smallint +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | | 0 | | | username | varchar(20) | NO | | NULL | | | age | smallint(5) unsigned | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ 三、修改列名称（使用change改变列的名称和类型，使用change）

注意：change col_name 不仅仅可以修改列定义，还可以修改列的名称。

语法：alter table tab_name change[column] old_name new_name [first|after col_name]

解析：更改数据表某某，改变列老列名新列明列定于【位置first|某列之后】

语法：alter table city2 change pid p_id tinyint unsigned not null 解析：更改数据表city2 改变pid列老名称：pid,新名称：p_id,类型：tinyint ，无符号，非空。 mysql> alter table city2 change pid p_id tinyint unsigned not null; Query OK, 0 rows affected (0.20 sec)//修改成功过 mysql> show columns from city2; //修改前，数据表的名称pid 类型为smallint +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | | 0 | | | username | varchar(20) | NO | | NULL | | | age | smallint(5) unsigned | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ //修改后，pid列名称改变为p_id，类型由smallint改变为tinyint +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | | 0 | | | username | varchar(20) | NO | | NULL | | | age | smallint(5) unsigned | NO | | NULL | | | p_id | tinyint(3) unsigned | NO | | NULL | | +----------+----------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) 三、修改表名称注意：修改数据表的名称有两种方式。

//可以更改一张数据表 1、alter table tbl_name rename[to|as] new_tbl_name

//可以为多张数据表修改名称 2、rename table tbl_name to new_tbl_name [,tbl_name2 TO new_tbl_namew]……

一、方法一单列改名语句：show tables; 解析：查看数据库里有那些数据表语句：alter table city2 rename city222; 解析：更改数据表名称tity2 更改为名称city222 mysql> show tables; +--------------+ | Tables_in_t1 | +--------------+ | city | | city1 | | city2 | | mm1 | | mm2 | | mm3 | | sheng | +--------------+ //我们修改city2的表名称为city222 mysql> alter table city2 rename city222; Query OK, 0 rows affected (0.07 sec)//修改成功 mysql> show tables; //修改前,数据表city2存在 +--------------+ | Tables_in_t1 | +--------------+ | city | | city1 | | city2 | | mm1 | | mm2 | | mm3 | | sheng | +--------------+ //修改后，city2数据表变成了city222 +--------------+ | Tables_in_t1 | +--------------+ | city | | city1 | | city222 | | mm1 | | mm2 | | mm3 | | sheng | +--------------+

方法二，多列改名

【本文地址】

公司简介

联系我们