MySQL alter命令修改表详解
作者:mmseoamin日期:2023-12-18

目录

ALTER TABLE 语法

ALTER TABLE 实例

添加一列

添加多列

重命名

修改列定义

修改列名和定义

添加主键

删除列

重命名表

修改表的存储引擎

结论


在使用表的过程中,如果您需要对表进行修改,您可以使用 ALTER TABLE 语句。通过 ALTER TABLE 语句,您可以重命名表、重命名列、添加列、删除列、修改列的属性等。

ALTER TABLE 语法

ALTER TABLE table_name
  [alter_action options], ...

其中 alter_action 是一个修改动作,包括

ADD 关键字可用来添加列、索引、约束等,包括:

  • ADD [COLUMN]: 添加列
  • ADD INDEX: 添加索引
  • ADD PRIMARY KEY: 添加主键
  • ADD FOREIGN KEY: 添加外键
  • ADD UNIQUE INDEX: 添加唯一索引
  • ADD CHECK: 添加检查约束

    DROP 关键字可用来删除列、索引、约束等,包括:

    • DROP [COLUMN] col_name: 删除列
    • ADD INDEX index_name: 删除索引
    • DROP PRIMARY KEY: 删除主键
    • DROP FOREIGN KEY fk_symbol: 删除外键
    • DROP CHECK symbol: 删除检查约束

      MODIFY 关键字用来修改列的定义。与 CHANGE 关键字不同,它不能重命名列。例如: MODIFY [COLUMN] col_name column_definition。

      CHANGE 关键字用来修改列的定义。与 MODIFY 关键字不同,它可以重命名列。例如: CHANGE [COLUMN] old_col_name new_col_name column_definition。

      RENAME 关键字可以重命名列、索引和表。包括:

      • RENAME COLUMN old_col_name TO new_col_name: 重命名列。
      • RENAME INDEX old_index_name TO new_index_name: 重命名索引。
      • RENAME new_tbl_name: 重命名表。

        ALTER TABLE 实例

        从 ALTER TABLE 语法看出, ALTER TABLE 用法很多。为了演示 ALTER TABLE 用法,我们在 testdb 数据库中创建一个表 user。

        请执行以下语句:

        CREATE TABLE user (id INT);

        通过以下语句查看 user 表的定义。

        DESC user;
        +-------+------+------+-----+---------+-------+
        | Field | Type | Null | Key | Default | Extra |
        +-------+------+------+-----+---------+-------+
        | id    | int  | YES  |     | NULL    |       |
        +-------+------+------+-----+---------+-------+
        1 row in set (0.00 sec)

        下面让我们看一些实际的例子。

        添加一列

        以下语句使用 ADD 关键字在 user 表中添加了 name 列。

        ALTER TABLE user
        ADD name VARCHAR(20);
        Query OK, 0 rows affected (0.02 sec)
        Records: 0  Duplicates: 0  Warnings: 0

        注意,当向包含数据的表中添加列时,新添加的列不满足表的约束定义,则会返回错误。

        添加多列

        以下语句使用 ADD 关键字在 user 表中添加了 age 和 email 列。

        ALTER TABLE user
        ADD age INT,
        ADD email VARCHAR(50);
        Query OK, 0 rows affected (0.02 sec)
        Records: 0  Duplicates: 0  Warnings: 0

        重命名列

        以下语句使用 RENAME COLUMN 关键字,将 user 表的 name 列重命名为 username。

        ALTER TABLE user
        RENAME COLUMN name TO username;
        Query OK, 0 rows affected (0.02 sec)
        Records: 0  Duplicates: 0  Warnings: 0

        以下是修改后的表定义:

        +----------+-------------+------+-----+---------+-------+
        | Field    | Type        | Null | Key | Default | Extra |
        +----------+-------------+------+-----+---------+-------+
        | id       | int         | YES  |     | NULL    |       |
        | username | varchar(20) | YES  |     | NULL    |       |
        | age      | int         | YES  |     | NULL    |       |
        | email    | varchar(50) | YES  |     | NULL    |       |
        +----------+-------------+------+-----+---------+-------+
        4 rows in set (0.00 sec)

        修改列定义

        以下语句使用 MODIFY 关键字,将 user 表的 username 列由 varchar(20) 修改为 VARCHAR(45)。

        ALTER TABLE user
        MODIFY username VARCHAR(45);
        Query OK, 0 rows affected (0.02 sec)
        Records: 0  Duplicates: 0  Warnings: 0

        以下是修改后的表定义:

        +----------+-------------+------+-----+---------+-------+
        | Field    | Type        | Null | Key | Default | Extra |
        +----------+-------------+------+-----+---------+-------+
        | id       | int         | YES  |     | NULL    |       |
        | username | varchar(45) | YES  |     | NULL    |       |
        | age      | int         | YES  |     | NULL    |       |
        | email    | varchar(50) | YES  |     | NULL    |       |
        +----------+-------------+------+-----+---------+-------+
        4 rows in set (0.00 sec)

        修改列名和定义

        以下语句使用 CHANGE 关键字,将 user 表的 username 列修改为 name VARCHAR(30)。

        ALTER TABLE user
        CHANGE username name VARCHAR(30);
        Query OK, 0 rows affected (0.02 sec)
        Records: 0  Duplicates: 0  Warnings: 0

        以下是修改后的表定义:

        +-------+-------------+------+-----+---------+-------+
        | Field | Type        | Null | Key | Default | Extra |
        +-------+-------------+------+-----+---------+-------+
        | id    | int         | YES  |     | NULL    |       |
        | name  | varchar(30) | YES  |     | NULL    |       |
        | age   | int         | YES  |     | NULL    |       |
        | email | varchar(50) | YES  |     | NULL    |       |
        +-------+-------------+------+-----+---------+-------+
        4 rows in set (0.00 sec)

        添加主键

        以下语句使用 ADD 关键字,将 user 表的 id 列设置为主键。

        ALTER TABLE user
        ADD PRIMARY KEY(id);
        Query OK, 0 rows affected (0.02 sec)
        Records: 0  Duplicates: 0  Warnings: 0

        注意,由于主键要求列的值是唯一的并且不能为 NULL,如果该列中有重复的值或者 NULL 值,则会返回错误。同样,如果你添加唯一索引,也可能会出现相同的错误。

        以下是修改后的表定义:

        +-------+-------------+------+-----+---------+-------+
        | Field | Type        | Null | Key | Default | Extra |
        +-------+-------------+------+-----+---------+-------+
        | id    | int         | NO   | PRI | NULL    |       |
        | name  | varchar(30) | YES  |     | NULL    |       |
        | age   | int         | YES  |     | NULL    |       |
        | email | varchar(50) | YES  |     | NULL    |       |
        +-------+-------------+------+-----+---------+-------+
        4 rows in set (0.00 sec)

        删除列

        下面语句使用 DROP 关键字删除了 email 列。

        ALTER TABLE user
        DROP COLUMN email;
        Query OK, 0 rows affected (0.02 sec)
        Records: 0  Duplicates: 0  Warnings: 0

        注意:删除列是很危险的操作,因为它会永久删除列中的数据。这是不可撤销的操作,请谨慎。

        以下是删除后的表定义:

        +-------+-------------+------+-----+---------+-------+
        | Field | Type        | Null | Key | Default | Extra |
        +-------+-------------+------+-----+---------+-------+
        | id    | int         | NO   | PRI | NULL    |       |
        | name  | varchar(30) | YES  |     | NULL    |       |
        | age   | int         | YES  |     | NULL    |       |
        +-------+-------------+------+-----+---------+-------+
        3 rows in set (0.00 sec)

        重命名表

        下面语句使用 RENAME 关键字将 user 表重命名为 users。

        ALTER TABLE user
        RENAME users;

        我们还可以是用 RENAME TABLE 语句重命名表,它的用法如下:

        RENAME TABLE table_name TO new_table_name;

        下面的语句同样实现将 user 表重命名为 users:

        RENAME TABLE user TO users;

        修改表的存储引擎

        ALTER TABLE 命令也可以用于修改一张表的存储引擎。可以使用以下语法:

        ALTER TABLE table_name ENGINE=engine_name;

        其中,table_name是要修改的表的名称,engine_name是新的存储引擎。

        示例:

        -- 将customer表的存储引擎改为innodb
        ALTER TABLE customer ENGINE=InnoDB; 

        结论

        在本文中,我们介绍了在 MySQL 服务器上如何使用 ALTER TABLE 语句修改表,包括:添加列、删除列、修改列、重命名列、重命名表和设置主键等。本文要点包括:

        • ALTER TABLE 关键字后面跟要修改的表名。
        • ADD 关键字可用来添加列、索引、约束等。
        • DROP 关键字可用来删除列、索引、约束等。
        • RENAME 关键字可以重命名列、索引和表。
        • MODIFY 关键字用来修改列的定义。
        • CHANGE 关键字用来修改列的定义和列名。
        • RENAME TABLE ... TO ... 用来重命名表。
          -- alter table 修改表结构(表名,列名,列的数据类型,相关约束)
          use d3;
          show tables;
          -- 查看表结构
          desc tuser;
          describe tuser;
          show columns from tuser;
          describe table tuser;
          -- 修改表名
          alter table tuser rename userinfo;
          rename table userinfo to tuser;
          -- 移动表表tt2从db2库移动当前数据库为tt3表
          rename table db2.tt2 to tt3;
          /*
          RENAME TABLE
          tbl_name TO new_tbl_name
          [, tbl_name2 TO new_tbl_*/
          drop table if exists
          t,t1,t3,s,stu,user,tuser,tteacher,dept,employees,student;
          create table t1(t int);
          create table t2(t int);
          create table t3(t int);
          -- 修改多个表的名称
          rename table
          t1 to tt1,
          t2 to tt2,
          t3 to tt3;
          -- 查看表结构
          desc tt1;
          -- 查看建立数据库的语句
          show create database mysql;
          -- 查看建立表的语句
          show create table tt1;
          rename table tt1 to student;
          -- 增加字段类型
          alter table student add column id int unsigned auto_increment primary key;
          alter table student add sname varchar(15) not null;
          alter table student add age tinyint unsigned default 18;
          alter table student add gender enum('男','女') default '男' after sname;
          alter table student add address varchar(255) first;
          -- 修改列的类型 及 位置
          alter table student modify address varchar(255) after age;
          -- 修改列名
          alter table student rename column address to saddr;
          -- 修改列名 类型 及 位置
          alter table student change saddr address varchar(100) after age;
          -- 复制表结构或建立一个空表
          create table stu like student;
          -- 根据查询的内容建立一个表,此表没有相关的约束,不推荐使用
          create table t1 as select 20,'jack';
          -- 删除字段
          alter table student drop column t;
          -- 修改表名 student cf_student cf_user cf_admin cf_car
          rename table w_student to cf_student;
          alter table cf_student rename st;
          -- 查看表结构
          describe st;
          desc st;
          -- 查看建立表语句
          show create table st;
          CREATE TABLE `st` (
          `id` int unsigned NOT NULL AUTO_INCREMENT,
          `name` varchar(30) NOT NULL,
          `gender` enum('男','女') DEFAULT NULL,
          `course` set('计算机英语','高数','离散数学','线性代数') DEFAULT NULL,
          PRIMARY KEY (`id`)
          ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3
          -- 增加一列(字段)
          -- 删除一列(字段)
          -- 修改列名
          -- 修改列的数据类型 int bigint varchar int varchar(30) varchar(5)
          select * from st;
          -- 增加一列
          alter table st add address varchar(255) not null default '郑州市';
          alter table st add age tinyint unsigned not null default 18 first;
          alter table st add age tinyint unsigned default 0 after name;
          -- 删除一列
          alter table st drop address;
          alter table st drop column age;
          -- 修改列名 name StudentName sname
          alter table st rename column name to sname;
          alter table st change sname name varchar(30) first;
          alter table st change name name varchar(30) after id;
          -- 修改列的数据类型及精度
          alter table st modify name varchar(100) after address;
          alter table st modify name varchar(50) after id;
          -- 复制表的结构
          -- create table t6(t int);
          create table st2 like st;
          show create table st2;
          select * from st2;
          -- 插入数据
          insert into st2 select * from st;
          show tables;
          -- 根据查询的结构,建立一个没有相关约束的表,可以直接插入数据
          create table st3 as select id,name from st;
          create table st4 as select id,name,address from st where name like '李%';
          select * from st3;
          show create table st3;
          drop table st3;
          select * from st4;