1、修改数据列在数据表中的位置
mysql> ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看修改的情况:
mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | | NULL | |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2、修改数据列的数据类型
要注意:由打类型改为小类型的时候,有可能出现数据的丢失。
mysql> ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看修改的情况:
mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned | NO | | NULL | |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
3、change修改数据列名称与数据类型 (尽量不用,会影响已经引用的原数据表列的数据)
mysql> ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users2;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned | NO | | NULL | |
| username | varchar(10) | NO | | NULL | |
| p_id | tinyint(3) unsigned | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
4、数据表更名 (尽量不用,会影响已经引用的原数据表名的数据)
方法一:
mysql> ALTER TABLE users2 RENAME users3;
Query OK, 0 rows affected (0.01 sec)
查看当前数据库中的数据表:
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| provinces |
| tb1 |
| tb2 |
| users |
| users1 |
| users3 |
+----------------+
6 rows in set (0.00 sec)
方法二:
mysql> RENAME TABLE users3 TO users2;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| provinces |
| tb1 |
| tb2 |
| users |
| users1 |
| users2 |
+----------------+
6 rows in set (0.00 sec)
小结:
修改列定义:
修改列所在的位置:alter table user2 modify id smallint unsigned not null first;(此命令意思就是将id所在字段改变到首列)
修改列的属性时只需将命令行的id属性上修改即可 但是要注意大类型改小类型要防止数据丢失
修改列名称:alter table user2 change pid p_id tinyint unsigned not null;(此命令行表达的是将pid的名称改为p_id 关键词 change)
数据表更名:
方法1:alter table user2 rename user3;(命令行的意思就是将user2改为user3)
方法2:rename table user3 to user4 可为多张数据表更名
尽量不要随意更改数据列和数据表的名字
约束:
按功能划为 not null,primary key,unique key,default,foreign。
按数据列的数目划为:表级 列级约束
修改数据表:
针对字段的操作:添加或删除字段 修改列定义 修改列名称等
针对约束的操作:添加或删除各种约束
针对数据表的操作:数据表更名(两种方式)