mysql 修改表結構操作

使用 【desc 表名】查看表結構

1、mysql > alter table passwd add id int(3) not null auto_increment primary? key not null first ;

在字段的上面添加一個新的字段。需要關鍵字 first

mysql> desc passwd ;

+----------+-------------+------+-----+---------+-------+

| Field??? | Type??????? | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| username | char(30)??? | NO?? | MUL | NULL??? |?????? |

| pass???? | char(1)???? | NO?? |???? | NULL??? |?????? |

| uid????? | int(5)????? | NO?? |???? | NULL??? |?????? |

| gid????? | int(5)????? | NO?? |???? | NULL??? |?????? |

| common?? | varchar(50) | YES? |???? | NULL??? |?????? |

| homedir? | char(50)??? | YES? |???? | NULL??? |?????? |

| shell??? | char(50)??? | NO?? |???? | NULL??? |?????? |

mysql> alter table passwd add id int(3) not null auto_increment primary? key not null first ;

Query OK, 37 rows affected (0.04 sec)

Records: 37? Duplicates: 0? Warnings: 0

mysql> desc passwd ;

+----------+-------------+------+-----+---------+----------------+

| Field??? | Type??????? | Null | Key | Default | Extra????????? |

+----------+-------------+------+-----+---------+----------------+

| id?????? | int(3)????? | NO?? | PRI | NULL??? | auto_increment |

| username | char(30)??? | NO?? | MUL | NULL??? |??????????????? |

| pass???? | char(1)???? | NO?? |???? | NULL??? |??????????????? |

| uid????? | int(5)????? | NO?? |???? | NULL??? |??????????????? |

| gid????? | int(5)????? | NO?? |???? | NULL??? |??????????????? |

| common?? | varchar(50) | YES? |???? | NULL??? |??????????????? |

| homedir? | char(50)??? | YES? |???? | NULL??? |??????????????? |

| shell??? | char(50)??? | NO?? |???? | NULL??? |??????????????? |

2、在指定的位置添加一個新的字段

在id字段后添加一個新的字段date

mysql> alter table passwd add date year after id ;?? 需要用到after關鍵字

mysql> alter table passwd add date year after id ;

Query OK, 37 rows affected (0.01 sec)

Records: 37? Duplicates: 0? Warnings: 0

mysql> desc passwd

-> ;

+----------+-------------+------+-----+---------+----------------+

| Field??? | Type??????? | Null | Key | Default | Extra????????? |

+----------+-------------+------+-----+---------+----------------+

| id?????? | int(3)????? | NO?? | PRI | NULL??? | auto_increment |

| date???? | year(4)???? | YES? |???? | NULL??? |??????????????? || username | char(30)??? | NO?? | MUL | NULL??? |??????????????? |

| pass???? | char(1)???? | NO?? |???? | NULL??? |??????????????? |

3、默認添加在最后面.

mysql> alter table passwd add QQ int(16) ;

mysql> alter table passwd add QQ int(16) ;

Query OK, 37 rows affected (0.01 sec)

Records: 37? Duplicates: 0? Warnings: 0

mysql> desc passwd ;

+----------+-------------+------+-----+---------+----------------+

| Field??? | Type??????? | Null | Key | Default | Extra????????? |

+----------+-------------+------+-----+---------+----------------+

| id?????? | int(3)????? | NO?? | PRI | NULL??? | auto_increment |

| date???? | year(4)???? | YES? |???? | NULL??? |??????????????? |

| username | char(30)??? | NO?? | MUL | NULL??? |??????????????? |

| pass???? | char(1)???? | NO?? |???? | NULL??? |??????????????? |

| uid????? | int(5)????? | NO?? |???? | NULL??? |??????????????? |

| gid????? | int(5)????? | NO?? |???? | NULL??? |??????????????? |

| common?? | varchar(50) | YES? |???? | NULL??? |??????????????? |

| homedir? | char(50)??? | YES? |???? | NULL??? |??????????????? |

| shell??? | char(50)??? | NO?? |???? | NULL??? |??????????????? |

| QQ?????? | int(16)???? | YES? |???? | NULL??? |??????????????? |

+----------+-------------+------+-----+---------+----------------+

10 rows in set (0.00 sec)

4、刪除表結構

mysql> alter table passwd drop date ;

mysql> desc passwd ;

+----------+-------------+------+-----+---------+----------------+

| Field??? | Type??????? | Null | Key | Default | Extra????????? |

+----------+-------------+------+-----+---------+----------------+

| id?????? | int(3)????? | NO?? | PRI | NULL??? | auto_increment |

| date???? | year(4)???? | YES? |???? | NULL??? |??????????????? |

| username | char(30)??? | NO?? | MUL | NULL??? |??????????????? |

| shell??? | char(50)??? | NO?? |???? | NULL??? |??????????????? |

| QQ?????? | int(16)???? | YES? |???? | NULL??? |??????????????? |

+----------+-------------+------+-----+---------+----------------+

mysql> alter table passwd drop date ;

Query OK, 37 rows affected (0.02 sec)

Records: 37? Duplicates: 0? Warnings: 0

mysql> desc passwd ;

+----------+-------------+------+-----+---------+----------------+

| Field??? | Type??????? | Null | Key | Default | Extra????????? |

+----------+-------------+------+-----+---------+----------------+

| id?????? | int(3)????? | NO?? | PRI | NULL??? | auto_increment |

| username | char(30)??? | NO?? | MUL | NULL??? |??????????????? |

| shell??? | char(50)??? | NO?? |???? | NULL??? |??????????????? |

| QQ?????? | int(16)???? | YES? |???? | NULL??? |??????????????? |

+----------+-------------+------+-----+---------+----------------+

5、修改字段類型

mysql> alter table passwd modify QQ int(11) not null ;?? 修改passwd表的QQ字段。

mysql> desc passwd ;

+----------+-------------+------+-----+---------+----------------+

| Field??? | Type??????? | Null | Key | Default | Extra????????? |

+----------+-------------+------+-----+---------+----------------+

| id?????? | int(3)????? | NO?? | PRI | NULL??? | auto_increment |

| username | char(30)??? | NO?? | MUL | NULL??? |??????????????? |

| shell??? | char(50)??? | NO?? |???? | NULL??? |??????????????? |

| QQ?????? | int(16)???? | YES? |???? | NULL??? |??????????????? |

+----------+-------------+------+-----+---------+----------------+

mysql> alter table passwd modify QQ int(11) not null ;

Query OK, 37 rows affected, 37 warnings (0.00 sec)

Records: 37? Duplicates: 0? Warnings: 37

mysql> desc passwd ;

+----------+-------------+------+-----+---------+----------------+

| Field??? | Type??????? | Null | Key | Default | Extra????????? |

+----------+-------------+------+-----+---------+----------------+

| id?????? | int(3)????? | NO?? | PRI | NULL??? | auto_increment |

| username | char(30)??? | NO?? | MUL | NULL??? |??????????????? |

| QQ?????? | int(11)???? | NO?? |???? | NULL??? |??????????????? |

+----------+-------------+------+-----+---------+----------------+

6、修改字段名

mysql> alter table passwd change QQ qq int(11) not null ;? 修改QQ字段為qq，用到change關鍵字。 需要將權限類型寫上，也可以更改類型。

mysql> alter table passwd change QQ qq int(11) not null ;

Query OK, 37 rows affected (0.00 sec)

Records: 37? Duplicates: 0? Warnings: 0

mysql> desc passwd ;

+----------+-------------+------+-----+---------+----------------+

| Field??? | Type??????? | Null | Key | Default | Extra????????? |

+----------+-------------+------+-----+---------+----------------+

| id?????? | int(3)????? | NO?? | PRI | NULL??? | auto_increment |

| username | char(30)??? | NO?? | MUL | NULL??? |??????????????? |

| pass???? | char(1)???? | NO?? |???? | NULL??? |??????????????? |

| uid????? | int(5)????? | NO?? |???? | NULL??? |??????????????? |

| gid????? | int(5)????? | NO?? |???? | NULL??? |??????????????? |

| common?? | varchar(50) | YES? |???? | NULL??? |??????????????? |

| homedir? | char(50)??? | YES? |???? | NULL??? |??????????????? |

| shell??? | char(50)??? | NO?? |???? | NULL??? |??????????????? |

| qq?????? | int(11)???? | NO?? |???? | NULL??? |??????????????? |

+----------+-------------+------+-----+---------+----------------+

9 rows in set (0.00 sec)

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的mysql修改表结构权限_mysql 修改表结构操作的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。