Mysql基礎教程 mysql進階

修改字段名稱或屬性



修改字段


ALTER TABLE tbl_name MODIFY 字段名稱 字段類型 [完整性約束條件] [FIRST|AFTER 字段名稱]


將email VARCHAR(200), 先選中字段在進行修改


mysql> ALTER TABLE user10 MODIFY email VARCHAR(200);
Query OK, 0 rows affected (0.75 sec)
Records: 0  Duplicates: 0  Warnings: 0


查看表結構


mysql> DESC user10;
+----------+----------------------------+------+-----+-------------+----------------+
| Field    | Type                   | Null | Key | Default     | Extra          |
+----------+----------------------------+------+-----+-------------+----------------+
| id       | smallint(5) unsigned      | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)              | NO   | UNI | NULL        |                |
| password | char(32)                | NO   |     | NULL        |                |
| email    | varchar(200)             | YES  |     | NULL        |                |
| age      | tinyint(3) unsigned        | YES  |     | 18          |                |
| sex      | enum('男','女','保密')     | YES  |     | 保密        |                |
| test     | int(10) unsigned          | NO   |     | 10          |                |
| salary   | float(6,2)                 | YES  |     | NULL        |                |
| regTime  | int(10) unsigned          | YES  |     | NULL        |                |
| face     | char(100)                | NO   |     | default.jpg |                |
| card     | char(18)                 | YES  |     | NULL        |                |
+----------+----------------------------+------+-----+-------------+----------------+
11 rows in set (0.04 sec)


改回原來的記錄


mysql> ALTER TABLE user10 MODIFY email VARCHAR(50) NOT NULL DEFAULT '[email protected]';
Query OK, 0 rows affected (0.92 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field    | Type                | Null | Key | Default          | Extra          |
+----------+----------------------------+------+-----+------------------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)        | NO   | UNI | NULL             |                |
| password | char(32)           | NO   |     | NULL             |                |
| email    | varchar(50)         | NO   |     | [email protected] |                |
| age      | tinyint(3) unsigned   | YES  |     | 18               |                |
| sex      | enum('男','女','保密') | YES  |     | 保密             |                |
| test     | int(10) unsigned     | NO   |     | 10               |                |
| salary   | float(6,2)           | YES  |     | NULL             |                |
| regTime  | int(10) unsigned    | YES  |     | NULL             |                |
| face     | char(100)          | NO   |     | default.jpg      |                |
| card     | char(18)           | YES  |     | NULL             |                |
+----------+----------------------------+------+-----+------------------+----------------+
11 rows in set (0.00 sec)


將card字段移動到text字段之后


mysql> ALTER TABLE user10 MODIFY card CHAR(18) AFTER test;
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field    | Type             | Null | Key | Default          | Extra          |
+----------+----------------------------+------+-----+------------------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)        | NO   | UNI | NULL             |                |
| password | char(32)           | NO   |     | NULL             |                |
| email    | varchar(50)        | NO   |     | [email protected] |                |
| age      | tinyint(3) unsigned  | YES  |     | 18               |                |
| sex      | enum('男','女','保密') | YES  |     | 保密             |                |
| test     | int(10) unsigned     | NO   |     | 10               |                |
| card     | char(18)           | YES  |     | NULL             |                |
| salary   | float(6,2)           | YES  |     | NULL             |                |
| regTime  | int(10) unsigned    | YES  |     | NULL             |                |
| face     | char(100)         | NO   |     | default.jpg      |                |
+----------+----------------------------+------+-----+------------------+----------------+
11 rows in set (0.01 sec)


將text字段修改為CHAR(32) NOT NULL DEFAUL‘123’移動到第一個位置


mysql> ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST;
Query OK, 0 rows affected (0.60 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field    | Type              | Null | Key | Default          | Extra          |
+----------+----------------------------+------+-----+------------------+----------------+
| test     | char(32)           | NO   |     | 123              |                |
| id       | smallint(5) unsigned   | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL             |                |
| password | char(32)           | NO   |     | NULL             |                |
| email    | varchar(50)        | NO   |     | [email protected] |                |
| age      | tinyint(3) unsigned  | YES  |     | 18               |                |
| sex      | enum('男','女','保密') | YES  |     | 保密             |                |
| card     | char(18)            | YES  |     | NULL             |                |
| salary   | float(6,2)           | YES  |     | NULL             |                |
| regTime  | int(10) unsigned     | YES  |     | NULL             |                |
| face     | char(100)           | NO   |     | default.jpg      |                |
+----------+----------------------------+------+-----+------------------+----------------+
11 rows in set (0.01 sec)

 


修改字段名稱


ALTER TABLE tbl_name CHANGE 舊字段名稱 新字段名稱 字段類型 [完整性約束條件] [FIRST|AFTER 字段名稱]


將test字段改為text1


mysql> ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field    | Type              | Null | Key | Default          | Extra          |
+----------+----------------------------+------+-----+------------------+----------------+
| test1    | char(32)            | NO   |     | 123              |                |
| id       | smallint(5) unsigned  | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL             |                |
| password | char(32)           | NO   |     | NULL             |                |
| email    | varchar(50)        | NO   |     | [email protected] |                |
| age      | tinyint(3) unsigned  | YES  |     | 18               |                |
| sex      | enum('男','女','保密') | YES  |     | 保密             |                |
| card     | char(18)           | YES  |     | NULL             |                |
| salary   | float(6,2)            | YES  |     | NULL             |                |
| regTime  | int(10) unsigned    | YES  |     | NULL             |                |
| face     | char(100)           | NO   |     | default.jpg      |                |
+----------+----------------------------+------+-----+------------------+----------------+
11 rows in set (0.01 sec)


改變text1的字段的類型


mysql> ALTER TABLE user10 CHANGE test1 test VARCHAR(200) NOT NULL AFTER username;
Query OK, 0 rows affected (0.62 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field    | Type               | Null | Key | Default          | Extra          |
+----------+----------------------------+------+-----+------------------+----------------+
| id       | smallint(5) unsigned     | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL             |                |
| test     | varchar(200)        | NO   |     | NULL             |                |
| password | char(32)           | NO   |     | NULL             |                |
| email    | varchar(50)         | NO   |     | [email protected] |                |
| age      | tinyint(3) unsigned   | YES  |     | 18               |                |
| sex      | enum('男','女','保密  | YES  |     | 保密             |                |
| card     | char(18)            | YES  |     | NULL             |                |
| salary   | float(6,2)            | YES  |     | NULL             |                |
| regTime  | int(10) unsigned     | YES  |     | NULL             |                |
| face     | char(100)           | NO   |     | default.jpg      |                |
+----------+----------------------------+------+-----+------------------+----------------+
11 rows in set (0.01 sec)


修改字段類型和字段屬性,也能完成modify


mysql> ALTER TABLE user10 CHANGE test test INT;
Query OK, 0 rows affected (0.76 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field    | Type                  | Null | Key | Default          | Extra          |
+----------+----------------------------+------+-----+------------------+----------------+
| id       | smallint(5) unsigned     | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL             |                |
| test     | int(11)              | YES  |     | NULL             |                |
| password | char(32)           | NO   |     | NULL             |                |
| email    | varchar(50)        | NO   |     | [email protected] |                |
| age      | tinyint(3) unsigned   | YES  |     | 18               |                |
| sex      | enum('男','女','保密') | YES  |     | 保密             |                |
| card     | char(18)            | YES  |     | NULL             |                |
| salary   | float(6,2)           | YES  |     | NULL             |                |
| regTime  | int(10) unsigned     | YES  |     | NULL             |                |
| face     | char(100)            | NO   |     | default.jpg      |                |
+----------+----------------------------+------+-----+------------------+----------------+
11 rows in set (0.04 sec)



【本文由麥子學院獨家原創,轉載請注明出處并保留原文鏈接】

logo
? 2012-2016 www.jvbprd.live
蜀ICP備13014270號-4 Version 5.0.0 release20160127

免費領取價值1888元求職寶典!

客服熱線 400-862-8862

回到頂部

彩票安徽25选5