mysql学习之修改数据表
1. 修改表的名字
语法: alter table <旧表名> rename [to] <新表名>;
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept |
| tb_dept1 |
| tb_emp1 |
| tb_emp2 |
| tb_emp3 |
| tb_emp4 |
| tb_emp5 |
+-------------------+
7 rows in set (0.00 sec)
把表tb_dept名字改为tb_deptment
alter table tb_dept rename to tb_deptment;
mysql> alter table tb_dept rename to tb_deptment;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1 |
| tb_deptment | -----修改成功
| tb_emp1 |
| tb_emp2 |
| tb_emp3 |
| tb_emp4 |
| tb_emp5 |
+-------------------+
7 rows in set (0.00 sec)
2.修改字段的数据类型
语法: alter table <表名> modify <字段名> <数据类型>
mysql> desc tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
把tb_emp1中name字段由varchar(25)改为varchar(80)
mysql> alter table tb_emp1 modify name varchar(80);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(80) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
把tb_emp1中name字段由varchar(25)改为int(80)
mysql> alter table tb_emp1 modify name int(80);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_emp1;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | int(80) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3.修改字段名
语法: alter table <表名> change <旧字段名> <新字段名> <新数据类型>
mysql> desc tb_emp1;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | int(80) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
把tb_emp1表中的salary 改为sal 数据类型改int
mysql> alter table tb_emp1 change salary sal int(20);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_emp1;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | int(80) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| sal | int(20) | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
说明:
由此可见change也可修改字段的数据类型和modify效果一样..
4.向表中添加字段
语法:alter table <表名> add <字段名> <数据类型> [约束条件] [first | after 已存在字段名];
mysql> desc tb_emp1;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | int(80) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| sal | int(20) | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
向表中添加phone字段约束不为null,默认添加表的最后
mysql> alter table tb_emp1 add phone int(20) not null;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_emp1;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | int(80) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| sal | int(20) | YES | | NULL | |
| phone | int(20) | NO | | NULL | |
+--------+---------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
在表的第一列添加一个字段col1
mysql> alter table tb_emp1 add col1 varchar(2) first;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_emp1;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| col1 | varchar(2) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | int(80) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| sal | int(20) | YES | | NULL | |
| phone | int(20) | NO | | NULL | |
+--------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
在表的指定列之后添加一个字段,在id添加一个col2 字段
mysql> alter table tb_emp1 add col2 varchar(20) after id;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col1 | varchar(2) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| col2 | varchar(20) | YES | | NULL | |
| name | int(80) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| sal | int(20) | YES | | NULL | |
| phone | int(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql>
5.删除字段
语法:alter table <表名> drop <字段名>
删除col1字段
mysql> alter table tb_emp1 drop col1;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| col2 | varchar(20) | YES | | NULL | |
| name | int(80) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| sal | int(20) | YES | | NULL | |
| phone | int(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
6.修改字段的排列位置
语法:alter table <表名> modify <字段1> <数据类型> first | after <字段类型2>
mysql> desc tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| col2 | varchar(20) | YES | | NULL | |
| name | int(80) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| sal | int(20) | YES | | NULL | |
| phone | int(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
修改deptid为表的第一个字段(还可以同时修改字段的类型)
mysql> alter table tb_emp1 modify deptid int(12) first;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptid | int(12) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| col2 | varchar(20) | YES | | NULL | |
| name | int(80) | YES | | NULL | |
| sal | int(20) | YES | | NULL | |
| phone | int(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
修改字段在表的指定列之后修改name列在sal之后
mysql> alter table tb_emp1 modify name varchar(25) after sal;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptid | int(12) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| col2 | varchar(20) | YES | | NULL | |
| sal | int(20) | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| phone | int(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
7.更改表的存储引擎
语法: alter table <表名> engine=<更改后的存储引擎名>
mysql> show create table tb_emp1 \G;
*************************** 1. row ***************************
Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
`deptid` int(12) DEFAULT NULL,
`id` int(11) DEFAULT NULL,
`col2` varchar(20) DEFAULT NULL,
`sal` int(20) DEFAULT NULL,
`name` varchar(25) DEFAULT NULL,
`phone` int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>
mysql> alter table tb_emp1 engine=myisam;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
把表的存储引擎修改为myisam
mysql> show create table tb_emp1 \G;
*************************** 1. row ***************************
Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
`deptid` int(12) DEFAULT NULL,
`id` int(11) DEFAULT NULL,
`col2` varchar(20) DEFAULT NULL,
`sal` int(20) DEFAULT NULL,
`name` varchar(25) DEFAULT NULL,
`phone` int(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>