6.3 數據字段操作 · 3天入門MySQL

假設我們存在user表，user結構如下： > mysql> desc user; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | username | varchar(10) | YES | | NULL | | | password | varchar(32) | YES | | NULL | | | createtime | int(10) | YES | | NULL | | | createip | int(10) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) ## 修改表字段類型 modify | 類別 | 詳細解示 | | -- | -- | | 基本語法 | alter table 表名 modify 字段名 varchar(20); | | 示例 |alter table user modify username varchar(20); | | 示例說明 | 將user表的username的類型改為varchar(20) | 我們執行一下，看看結果： > mysql> alter table user modify username varchar(20); Query OK, 0 rows affected (0.48 sec) Records: 0 Duplicates: 0 Warnings: 0 > mysql> desc user; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | username | varchar(20) | YES | | NULL | | | password | varchar(32) | YES | | NULL | | | createtime | int(10) | YES | | NULL | | | createip | int(10) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) ##增加表字段 | 類別 | 詳細解示 | | -- | -- | | 基本語法 | alter table 表名 add column 字段名類型; | | 示例 |alter table user add column age int(3); | | 示例說明 | 添加一個字段為age，類型為整型長度為3 | > mysql> alter table emp add column age int(3); Query OK, 0 rows affected (0.40 sec) Records: 0 Duplicates: 0 Warnings: 0 > mysql> desc user; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | username | varchar(20) | YES | | NULL | | | password | varchar(32) | YES | | NULL | | | createtime | int(10) | YES | | NULL | | | createip | int(10) | YES | | NULL | | | age | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) ##增加字段時控制字段順序我們剛剛學了增加字段。如果你仔細實驗發現每次都是增加在最后面，如何在第一個增加或者在指字字段之后增加呢？ | 類別 | 詳細解釋 | | -- | -- | | 基本語法 | ALTER TABLE 表名 ADD 字段名字段類型 AFTER 字段名; | | 示例 |ALTER TABLE user ADD email VARCHAR(60) AFTER createip; | | 示例說明 | user表中，在createip后增加一個字段為email，類型為varchar，長度為60 | | 類別 | 詳細解示 | | -- | -- | | 基本語法 | ALTER TABLE 表名 ADD 字段名字段類型;| | 示例 |ALTER TABLE user ADD id INT(10) FIRST; | | 示例說明 | user表中在最開始的位置增加一個字段為id,類型為int，長度為10 | > ALTER TABLE user ADD email VARCHAR(60) AFTER createip; Query OK, 0 rows affected (0.40 sec) Records: 0 Duplicates: 0 Warnings: 0 > mysql> desc user; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | username | varchar(20) | YES | | NULL | | | password | varchar(32) | YES | | NULL | | | createtime | int(10) | YES | | NULL | | | createip | int(10) | YES | | NULL | | | email | varchar(60) | YES | | NULL | | | age | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) ##刪除表字段 | 類別 | 詳細解示 | | -- | -- | | 基本語法 | alter table 表名 drop column 字段名; | | 示例 | alter table user drop column age; | | 示例說明 | 在user表中刪除字段age | > mysql> alter table user drop column age; Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0 > mysql> desc user; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | username | varchar(20) | YES | | NULL | | | password | varchar(32) | YES | | NULL | | | createtime | int(10) | YES | | NULL | | | createip | int(10) | YES | | NULL | | | email | varchar(60) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) ##表字段改名 | 類別 | 詳細解示 | | -- | -- | | 基本語法 | alter table 表名 change 字段原名字段新名字段類型; | | 示例 | alter table user change email em varchar(60); | | 示例說明 | 在user表中將字段中的email字段名字為em | 詳細示例： mysql> alter table user change email em varchar(60); Query OK, 0 rows affected (0.38 sec) Records: 0 Duplicates: 0 Warnings: 0 > mysql> desc user; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | username | varchar(20) | YES | | NULL | | | password | varchar(32) | YES | | NULL | | | createtime | int(10) | YES | | NULL | | | createip | int(10) | YES | | NULL | | | em | varchar(60) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) ##修改表字段排列順序在前的字段增加和修改語句（add/change/modify）中，最后都可以加一個可選項 first|after。增加表字段時我們已經學過了如何調整順序。我們現在在來看看另外的change或modify如何來調整順序。我們用first做個小實驗。 ###使用modify調整順序 > mysql> alter table user modify em varchar(60) first; Query OK, 0 rows affected (0.41 sec) Records: 0 Duplicates: 0 Warnings: 0 > mysql> desc user; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | em | varchar(60) | YES | | NULL | | | username | varchar(20) | YES | | NULL | | | password | varchar(32) | YES | | NULL | | | createtime | int(10) | YES | | NULL | | | createip | int(10) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) ##修改表名 | 類別 | 詳細解示 | | -- | -- | | 基本語法 | alter table 舊表名 rename 新的表名; | | 示例 | alter table user rename new_user; | | 示例說明 | 將user表名改為new_user | > mysql> alter table user rename new_user; Query OK, 0 rows affected (0.35 sec) > mysql> desc new_user; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | em | varchar(60) | YES | | NULL | | | username | varchar(20) | YES | | NULL | | | password | varchar(32) | YES | | NULL | | | createtime | int(10) | YES | | NULL | | | createip | int(10) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)