在mysql 的表中增加或删除列

最新推荐文章于 2024-03-20 00:30:00 发布

stuShan

最新推荐文章于 2024-03-20 00:30:00 发布

阅读量761

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/stuShan/article/details/50611554

版权

mysql 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

演示的表

>SHOW CREATE TABLE ex_book;

TABLE    CREATE TABLE                                                                                                                                            
-------  -------------------------------
ex_book  CREATE TABLE `ex_book` (                                                                                                                                       
           `id` INT(11) NOT NULL AUTO_INCREMENT,                                                                                                                        
           `name` VARCHAR(25) DEFAULT NULL,                                                                                                                             
           PRIMARY KEY (`id`)                                                                                                                                           
         ) ENGINE=INNODB DEFAULT CHARSET=utf8

添加单列

>ALTER TABLE ex_book ADD `author` VARCHAR(25) DEFAULT NULL;
>DESC ex_book;

FIELD   TYPE         NULL    KEY     DEFAULT  Extra           
------  -----------  ------  ------  -------  ----------------
id      INT(11)      NO      PRI     (NULL)   AUTO_INCREMENT  
NAME    VARCHAR(25)  YES             (NULL)                   
author  VARCHAR(25)  YES             (NULL)

添加多列

>ALTER TABLE ex_book ADD `isbn` VARCHAR(20) DEFAULT NULL,ADD `publish` VARCHAR(25) DEFAULT NULL;
>ALTER TABLE ex_book ADD (`key_word` VARCHAR(20) DEFAULT NULL,`desc` VARCHAR(25) DEFAULT NULL);
>SHOW COLUMNS FROM ex_book;
FIELD     TYPE         NULL    KEY     DEFAULT  Extra           
--------  -----------  ------  ------  -------  ----------------
id        INT(11)      NO      PRI     (NULL)   AUTO_INCREMENT  
NAME      VARCHAR(25)  YES             (NULL)                   
author    VARCHAR(25)  YES             (NULL)                   
isbn      VARCHAR(20)  YES             (NULL)                   
publish   VARCHAR(25)  YES             (NULL)                   
key_word  VARCHAR(20)  YES             (NULL)                   
DESC      VARCHAR(25)  YES             (NULL)

删除单列

>ALTER TABLE ex_book DROP COLUMN `desc`; 
>SHOW COLUMNS FROM ex_book;
FIELD     TYPE         NULL    KEY     DEFAULT  Extra           
--------  -----------  ------  ------  -------  ----------------
id        INT(11)      NO      PRI     (NULL)   AUTO_INCREMENT  
NAME      VARCHAR(25)  YES             (NULL)                   
author    VARCHAR(25)  YES             (NULL)                   
isbn      VARCHAR(20)  YES             (NULL)                   
publish   VARCHAR(25)  YES             (NULL)                   
key_word  VARCHAR(20)  YES             (NULL)

删除多列

>ALTER TABLE ex_book DROP COLUMN `publish`, DROP COLUMN `key_word`;
>SHOW COLUMNS FROM ex_book;
FIELD   TYPE         NULL    KEY     DEFAULT  Extra           
------  -----------  ------  ------  -------  ----------------
id      INT(11)      NO      PRI     (NULL)   AUTO_INCREMENT  
NAME    VARCHAR(25)  YES             (NULL)                   
author  VARCHAR(25)  YES             (NULL)                   
isbn    VARCHAR(20)  YES             (NULL)