MySQL数据表操作 | 您所在的位置:网站首页 › mysql修改列名字 › MySQL数据表操作 |
修改数据表的前提是数据库中已经存在该表。修改表指的是修改数据库中已经存在的数据表的结构。例如增加或删减列、更改原有列类型、重新命名列或表等。 语法 ALTER TABLE [修改选项][修改选项]的语法格式如下: { ADD COLUMN | CHANGE COLUMN | ALTER COLUMN { SET DEFAULT | DROP DEFAULT } | MODIFY COLUMN | DROP COLUMN | RENAME TO | CHARACTER SET | COLLATE } 实例 修改表名MySQL 通过 ALTER TABLE语句来实现表名的修改,语法规则如下: ALTER TABLE RENAME [TO|AS] ;其中,TO 和 AS 为可选参数,使用与否均不影响结果。 修改表名并不修改表的结构,因此修改名称后的表和修改名称前的表的结构是相同的。可以使用 DESC 命令查看修改后的表结构 mysql> show tables; +-------------------+ | Tables_in_test_db | +-------------------+ | e_diff | | es_diff | | s_diff | | t_create_table | | vc_diff | +-------------------+ 5 rows in set (0.00 sec) mysql> alter table s_diff rename as temp_as; Query OK, 0 rows affected (0.02 sec) mysql> show tables; +-------------------+ | Tables_in_test_db | +-------------------+ | e_diff | | es_diff | | t_create_table | | temp_as | | vc_diff | +-------------------+ 5 rows in set (0.00 sec) mysql> alter table e_diff rename to temp_to; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-------------------+ | Tables_in_test_db | +-------------------+ | es_diff | | t_create_table | | temp_as | | temp_to | | vc_diff | +-------------------+ 5 rows in set (0.00 sec) 修改表字符集MySQL 通过 ALTER TABLE 语句来实现表字符集的修改,语法规则如下: ALTER TABLE 表名 [DEFAULT] CHARACTER SET [DEFAULT] COLLATE ;其中,DEFAULT 为可选参数,使用与否均不影响结果。 mysql> show create table t_create_table \G *************************** 1. row *************************** Table: t_create_table Create Table: CREATE TABLE `t_create_table` ( `r_name` varchar(100) DEFAULT NULL, `r_sex` varchar(5) DEFAULT NULL, `r_type` varchar(5) DEFAULT NULL, `r_no` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) mysql> alter table t_create_table character set gb2312 collate gb2312_chinese_ci; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t_create_table \G *************************** 1. row *************************** Table: t_create_table Create Table: CREATE TABLE `t_create_table` ( `r_name` varchar(100) CHARACTER SET utf8mb4 DEFAULT NULL, `r_sex` varchar(5) CHARACTER SET utf8mb4 DEFAULT NULL, `r_type` varchar(5) CHARACTER SET utf8mb4 DEFAULT NULL, `r_no` varchar(100) CHARACTER SET utf8mb4 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 1 row in set (0.01 sec) 修改字段名称MySQL 中修改表字段名的语法规则如下: ALTER TABLE CHANGE ;其中: 旧字段名:指修改前的字段名;新字段名:指修改后的字段名;新数据类型:指修改后的数据类型,如果不需要修改字段的数据类型,可以将新数据类型设置成与原来一样,但数据类型不能为空。 mysql> alter table t_create_table change r_type n_type varchar(100); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | varchar(100) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)CHANGE 也可以只修改数据类型,实现和 MODIFY 同样的效果,方法是将 SQL 语句中的“新字段名”和“旧字段名”设置为相同的名称,只改变“数据类型”。 修改字段数据类型修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 MySQL 中修改字段数据类型的语法规则如下: ALTER TABLE MODIFY其中: 表名:指要修改数据类型的字段所在表的名称;字段名:指需要修改的字段;数据类型:指修改后字段的新数据类型。实例 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | varchar(100) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table t_create_table modify n_type int(20); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | int(20) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) 删除字段删除字段是将数据表中的某个字段从表中移除,语法格式如下: ALTER TABLE DROP ;其中,“字段名”指需要从表中删除的字段的名称。 实例 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_age | int(3) | YES | | NULL | | | r_id | bigint(100) | YES | | NULL | | | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | int(20) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | | r_num | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> alter table t_create_table drop r_num; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_age | int(3) | YES | | NULL | | | r_id | bigint(100) | YES | | NULL | | | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | int(20) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) 在末尾添加字段(列)一个完整的字段包括字段名、数据类型和约束条件。MySQL 添加字段的语法格式如下: ALTER TABLE ADD [约束条件];对语法格式的说明如下: 为数据表的名字; 为所要添加的字段的名字; 为所要添加的字段能存储数据的数据类型;[约束条件] 是可选的,用来对添加的字段进行约束。这种语法格式默认在表的最后位置(最后一列的后面)添加新字段。 实例 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_age | int(3) | YES | | NULL | | | r_id | bigint(100) | YES | | NULL | | | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | int(20) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table t_create_table add r_num varchar(100); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_age | int(3) | YES | | NULL | | | r_id | bigint(100) | YES | | NULL | | | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | int(20) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | | r_num | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) 在开头添加字段(列)MySQL 默认在表的最后位置添加新字段,如果希望在开头位置(第一列的前面)添加新字段,那么可以使用 FIRST 关键字,语法格式如下: ALTER TABLE ADD [约束条件] FIRST;FIRST 关键字一般放在语句的末尾。 实例 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | int(20) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table t_create_table add r_age int(3) first; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_age | int(3) | YES | | NULL | | | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | int(20) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) 在中间位置添加字段(列)MySQL 除了允许在表的开头位置和末尾位置添加字段外,还允许在中间位置(指定的字段之后)添加字段,此时需要使用 AFTER 关键字,语法格式如下: ALTER TABLE ADD [约束条件] AFTER ;AFTER 的作用是将新字段添加到某个已有字段后面。 注意,只能在某个已有字段的后面添加新字段,不能在它的前面添加新字段。 实例 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_age | int(3) | YES | | NULL | | | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | int(20) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table t_create_table add r_id bigint(100) after r_age; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t_create_table add r_id bigint(100) after r_age; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_age | int(3) | YES | | NULL | | | r_id | bigint(100) | YES | | NULL | | | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | int(20) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) 修改字段名称和类型 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_age | int(3) | YES | | NULL | | | r_id | bigint(100) | YES | | NULL | | | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | varchar(200) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table t_create_table change n_type r_type varchar(200); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_age | int(3) | YES | | NULL | | | r_id | bigint(100) | YES | | NULL | | | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | r_type | varchar(200) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 6 rows in set (0.01 sec) 修改字段类型和长度 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_age | int(3) | YES | | NULL | | | r_id | bigint(100) | YES | | NULL | | | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | int(20) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table t_create_table modify column n_type varchar(200); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t_create_table; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | r_age | int(3) | YES | | NULL | | | r_id | bigint(100) | YES | | NULL | | | r_name | varchar(100) | YES | | NULL | | | r_sex | varchar(5) | YES | | NULL | | | n_type | varchar(200) | YES | | NULL | | | r_no | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) |
CopyRight 2018-2019 实验室设备网 版权所有 |