MySQL字段操作1

添加单列

ALTER TABLE 表名 ADD 列名 列的属性 位置；

• 简单添加一行命名为sex

mysql> CREATE TABLE tb9(
    -> id INT,
    -> username VARCHAR(10) NOT NULL);
Query OK, 0 rows affected (0.05 sec)


mysql> ALTER TABLE tb9 ADD sex TINYINT NOT NULL DEFAULT 0;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> DESC tb9;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(10) | NO   |     | NULL    |       |
| sex      | tinyint     | NO   |     | 0       |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


复制

• 在id列前面添加Cid与末尾添加pid

mysql> ALTER TABLE tb9 ADD pid INT NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> ALTER TABLE tb9 ADD Cid INT NOT NULL FIRST;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> DESC tb9;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Cid      | int         | NO   |     | NULL    |       |
| id       | int         | YES  |     | NULL    |       |
| username | varchar(10) | NO   |     | NULL    |       |
| sex      | tinyint     | NO   |     | 0       |       |
| pid      | int         | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)


复制

添加多列

ALTER TABLE 表名 ADD (列名1，列名2...); 注意：添加多列不可以选择位置，只能往后排。

• 添加aa、bb两行

mysql> ALTER TABLE tb9 ADD (aa TINYINT NOT NULL,bb CHAR(10) NOT NULL);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> DESC tb9;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Cid      | int         | NO   |     | NULL    |       |
| id       | int         | YES  |     | NULL    |       |
| username | varchar(10) | NO   |     | NULL    |       |
| sex      | tinyint     | NO   |     | 0       |       |
| pid      | int         | NO   |     | NULL    |       |
| aa       | tinyint     | NO   |     | NULL    |       |
| bb       | char(10)    | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)


复制

删除单列

ALTER TABLE 表名 DROP 被删除的列名;

• 删除Cid列

mysql> ALTER TABLE tb9 DROP Cid;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> DESC tb9;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(10) | NO   |     | NULL    |       |
| sex      | tinyint     | NO   |     | 0       |       |
| pid      | int         | NO   |     | NULL    |       |
| aa       | tinyint     | NO   |     | NULL    |       |
| bb       | char(10)    | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)


复制

删除多列

ALTER TABLE 表明 DROP 被删除列1,DROP 被删除列2,DROP 被删除列3;

• 删除aa、bb、pid三个列

mysql> ALTER TABLE tb9 DROP aa,DROP bb,DROP pid;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> DESC tb9;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(10) | NO   |     | NULL    |       |
| sex      | tinyint     | NO   |     | 0       |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


复制

添加主键约束

LTER TABLE 表名 ADD PRIMARY KEY (被添加的列名)

• 给id列添加

mysql> ALTER TABLE tb9 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> DESC tb9;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| username | varchar(10) | NO   |     | NULL    |       |
| sex      | tinyint     | NO   |     | 0       |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


复制

添加唯一约束

ALTER TABLE 表名 ADD UNIQUE (列1，列2，。。。);

• 给username添加唯一约束

mysql> ALTER TABLE tb9 ADD UNIQUE (username);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> DESC tb9;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| username | varchar(10) | NO   | UNI | NULL    |       |
| sex      | tinyint     | NO   |     | 0       |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


复制