10、ALTER TABLE 修改表结构

1、ALTER TABLE

是 SQL 中用于修改现有表结构的数据定义语言（DDL）语句。在已有的表中添加、修改或删除列字段，创建或者取消索引，更改字段类型，重命名字段或者表名等。

 语法：

alter table 表名 [修改选项]

修改选项语法如下：

1）添加一个新字段

可以指定添加到哪个字段之后。

add column 字段名 类型(长度) [COMMENT 注释] [约束] [after 已经存在的字段名]；

例如：

MariaDB [test]> show columns from w1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| addr  | char(4) | YES  |     | NULL    |       |
| name  | char(4) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [test]> alter table w1 add num int(4) unique after name;
Query OK, 3 rows affected (0.04 sec)               
Records: 3  Duplicates: 0  Warnings: 0

2）删除字段

drop column 字段1，drop column 字段2，...；

删除多个字段得写多个drop，每个字段之间用逗号（，）隔开

例如：

MariaDB [test]> alter table w1 drop name,drop num;
Query OK, 3 rows affected (0.07 sec)               
Records: 3  Duplicates: 0  Warnings: 0

3）只修改字段的数据类型和属性

不能修改字段名。

modify column 字段 新数据类型 约束条件；

例如：

MariaDB [test]> alter table w1 add name int(4) not null ;
Query OK, 3 rows affected (0.06 sec)               
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> desc w1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| addr  | char(4) | YES  |     | NULL    |       |
| num   | char(4) | YES  |     | NULL    |       |
| name  | int(4)  | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

MariaDB [test]> alter table w1 modify name char(4) ;
Query OK, 3 rows affected (0.08 sec)               
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> desc w1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| addr  | char(4) | YES  |     | NULL    |       |
| num   | char(4) | YES  |     | NULL    |       |
| name  | char(4) | YES  |     | NULL    |       |                -- null在定义数据时为默认设置
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4）修改字段名和数据类型

可以修改字段名和数据类型

CHANGE column 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];

注意： 修改字段名必须在新字段名后面添加数据类型，就算数据类型还是原先的类型，也得再写一遍，无数据类型则修改不成功,报错。

MariaDB [test]> alter table w1 change name Name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

MariaDB [test]> alter table w1 change name Name char(4);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

5）重命名列

RENAME COLUMN old_column_name TO new_column_name;

例如：

-- 将 employees 表中的 name 列重命名为 full_name
ALTER TABLE employees RENAME COLUMN name TO full_name;

注： to 为可选参数，使用与否不影响结果

6）添加约束（ADD CONSTRAINT）

添加主键

ALTER TABLE logs
ADD CONSTRAINT pk_log_id PRIMARY KEY (log_id);

1. ALTER TABLE logs：表示要修改名为 logs 的表结构

2. ADD CONSTRAINT pk_log_id

   • ADD CONSTRAINT: 添加约束

   • pk_log_id: 为这个约束指定的名称（主键约束名）

3. PRIMARY KEY (log_id)

• PRIMARY KEY: 主键约束

• (log_id): 指定 log_id 列作为主键

添加外键

 

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE;

 

1. ALTER TABLE orders

   • 表示要修改名为 orders 的表结构

2. ADD CONSTRAINT fk_customer

   • ADD CONSTRAINT: 添加约束

   • fk_customer: 为这个外键约束指定的名称

3. FOREIGN KEY (customer_id)

   • FOREIGN KEY: 外键约束声明

   • (customer_id): 指定当前表（orders）中的外键列

4. REFERENCES customers(id)

   • REFERENCES: 引用声明

   • customers(id): 指定被引用的主表（customers）和主键列（id）

5. ON DELETE CASCADE

   • 指定删除操作时的级联行为

 

2、另一种修改表名的语句

rename table 原表名1 to 新表名1，原表名1 to 新表名1，原表名2 to 新表名2，...；

例如：

MariaDB [test]> rename table w1 to test1;
Query OK, 0 rows affected (0.02 sec)

3、删除表

drop table 表名；