修改表的结构（ALTER）_修改表结构

修改表的结构操作，有下面几种。

1、修改列名   
2、修改表的名字
3、修改列的类型
4、增加某列
5、删除某列

相似的语法：alter table TableName xxx(操作)

一、修改列名

①、语法：

         ALTER TABLE TABLENAME CHANGE 原列名  想改成的列名 列类型;

②、示例：

1、查看原表的结构

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> desc MyTable;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stuname | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2、修改某一列想改的列名
        

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> alter table MyTable change stuname sname varchar(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

3、查看结果

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> desc MyTable;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sname | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

二、修改表的名字

①、基本语法：

    修改表的名字的方法有两种：
    1、ALTER TABLE TABLENAME RENAME NEWTABLENAME;
    2、RENAME TABLE TABLENAME TO NEWTABLENAME;

②、示例

     1、查看表的原名字,假如我们要更改FirTable_COPY 为 FCS

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| FirTable       |
| FirTable_COPY  |
| FirTable_COPY2 |
| FirstTable     |
| MT             |
+----------------+
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      2、运用第一种方法修改

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> alter table FirTable_COPY rename FC;
Query OK, 0 rows affected (2.22 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3、查看结果

       
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| FC             |
| FirTable       |
| FirTable_COPY2 |
| FirstTable     |
| MT             |
+----------------+
5 rows in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

4、发现手残打错了，运用第二种方法修改(当然也可以进行用第一种办法)

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> rename table FC to FCS;
Query OK, 0 rows affected (0.04 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

5、查看结果

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| FCS            |
| FirTable       |
| FirTable_COPY2 |
| FirstTable     |
| MT             |
+----------------+
5 rows in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

三、修改列的类型

①、基本语法：
        

         ALTER TABLE TABLENAME MODIFY 列名 想改的类型

②、示例：

        1、查看原表格的结构，假如要改变sno的int变成varchar(13)

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> desc FCS;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | int(11)     | YES  |     | NULL    |       |
| sname | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2、修改语句

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> ALTER TABLE FCS MODIFY sno varchar(13);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    

3、查看结果

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> desc FCS;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(13) | YES  |     | NULL    |       |
| sname | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

四、增加列

①、语法：

           ALTER TABLE TABLENAME ADD 列名 类型;

②、示例：

 

1、原来表的结构是如下情况：

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> desc MyTable;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stuname | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

2、进行增加操作

       

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> alter table MyTable add stuNum long;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3、完成之后的操作

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> desc MyTable;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stuname | varchar(10) | YES  |     | NULL    |       |
| stuNum  | mediumtext  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

③、常见错误：

       1、添加了相同名字的列

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> alter table MyTable add stuNum long;
ERROR 1060 (42S21): Duplicate column name 'stuNum'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        2、同时添加了很多个列

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> alter table MyTable add stuName  varchar(10), stuCount varchar(16);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'stuCount varchar(16)' at line 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      3、修改没有权限操作的库的表
      这里以information_schema为例

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> alter table INNODB_FT_CONFIG add count int;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

五、删除某列

①、语法：ALTER TABLE TABLENAME   DROP 列名

②、示例：

1、MyTable的原始状态

 

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> desc MyTable;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stuname | varchar(10) | YES  |     | NULL    |       |
| stuNum  | mediumtext  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2、删除的语句

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> ALTER TABLE MyTable DROP stuNum;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

3、操作后MyTable的结构

        
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> desc MyTable;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stuname | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~