MySQL字段操作二

添加/删除默认约束

ALTER TABLE 表格 ALTER 哪列 SET/DROP 约束 默认值;

• 首先创建一个age列，然后添加一个默认值

• 然后修改他的默认值

mysql> ALTER TABLE tb9 ADD age TINYINT NOT NULL DEFAULT 0;
Query OK, 0 rows affected (0.02 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       |       |
| cid      | tinyint     | YES  |     | NULL    |       |
| age      | tinyint     | NO   |     | 0       |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)


mysql> ALTER TABLE tb9 ALTER age SET DEFAULT 20;
Query OK, 0 rows affected (0.02 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       |       |
| cid      | tinyint     | YES  |     | NULL    |       |
| age      | tinyint     | NO   |     | 20      |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

• 接下来在删除默认值

mysql> ALTER TABLE tb9 ALTER age DROP DEFAULT;
Query OK, 0 rows affected (0.02 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       |       |
| cid      | tinyint     | YES  |     | NULL    |       |
| age      | tinyint     | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除主键约束

ALTER TABLE 表名称 DROP PRIMARY KEY

• 因为主键在每张表中只存在一个，所以不用给列名

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

删除唯一约束

ALTER TABLE 表名称 DROP 列名；

修改列定义

• ALTER TABLE 表名 MODIFY 列名 属性；

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


mysql> ALTER TABLE tb9 MODIFY cid TINYINT UNSIGNED NOT NULL DEFAULT 10;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> DESC tb9;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int              | NO   |     | NULL    |       |
| sex   | tinyint          | NO   |     | 0       |       |
| cid   | tinyint unsigned | NO   |     | 10      |       |
| age   | tinyint          | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改列名称

• ALTER TABLE 表名 CHANGE 旧列名 新列名 属性 位置;

mysql> ALTER TABLE tb9 CHANGE cid pid TINYINT NOT NULL;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改数据表

• ALTER TABLE 旧数据表 RENAME 新数据表;

mysql> ALTER TABLE tb9 RENAME tb99;
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW TABLES;
+------------------+
| Tables_in_xiaoer |
+------------------+
| num              |
| tb1              |
| tb2              |
| tb3              |
| tb4              |
| tb5              |
| tb6              |
| tb7              |
| tb8              |
| tb99             |
| user             |
+------------------+
11 rows in set (0.00 sec)