1 修改数据表表名
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1 |
| tb_dept2 |
| tb_emp2 |
| tb_emp3 |
| tb_emp4 |
| tb_emp5 |
| tb_emp6 |
| tb_emp8 |
| tb_empl |
+-------------------+
9 rows in set (0.00 sec)
mysql> ALTER TABLE tb_emp2 RENAME tb_employ2;
Query OK, 0 rows affected (0.13 sec)
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1 |
| tb_dept2 |
| tb_emp3 |
| tb_emp4 |
| tb_emp5 |
| tb_emp6 |
| tb_emp8 |
| tb_empl |
| tb_employ2 |
+-------------------+
9 rows in set (0.00 sec)
mysql>
2 修改数据表字段类型
mysql> desc tb_emp5;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| deptId | int(11) | YES | MUL | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE tb_emp5 MODIFY name VARCHAR(30);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_emp5;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| deptId | int(11) | YES | MUL | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
3 修改数据表字段名
mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| location | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tb_dept1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| loc | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
4.1 添加字段(无约束条件)
mysql> DESC tb_dept1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| loc | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE tb_dept1 ADD managerId INT(10);
Query OK, 0 rows affected (0.56 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.18 sec)
mysql>
4.2 添加字段(有约束条件)
mysql> DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.18 sec)
mysql> ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) NOT NULL;
Query OK, 0 rows affected (0.60 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
4.3 添加字段(指定位置1)
mysql> DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column2 | int(11) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
4.4 添加字段(指定位置2)
mysql> ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column2 | int(11) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name;
Query OK, 0 rows affected (0.54 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column2 | int(11) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql>
5 删除字段
mysql> desc tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column2 | int(11) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> ALTER TABLE tb_dept1 DROP column2;
Query OK, 0 rows affected (0.76 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
6 修改字段排列位置
方式1
mysql> desc tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
mysql> ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;
Query OK, 0 rows affected (0.54 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column1 | varchar(12) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
方式2
mysql> desc tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column1 | varchar(12) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER loc;
Query OK, 0 rows affected (0.56 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| column1 | varchar(12) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
7 更改表的存储引擎
mysql> show create table tb_dept2\G;
*************************** 1. row ***************************
Table: tb_dept2
Create Table: CREATE TABLE `tb_dept2` (
`id` int(11) NOT NULL,
`name` varchar(22) DEFAULT NULL,
`location` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> ALTER TABLE tb_dept2 ENGINE=MyISAM;
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tb_dept2\G;
*************************** 1. row ***************************
Table: tb_dept2
Create Table: CREATE TABLE `tb_dept2` (
`id` int(11) NOT NULL,
`name` varchar(22) DEFAULT NULL,
`location` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
8 删除表的外键约束
mysql> CREATE TABLE tb_emp9
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT,
-> CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
-> );
Query OK, 0 rows affected (1.36 sec)
mysql>
mysql> SHOW CREATE TABLE tb_emp9\G
*************************** 1. row ***************************
Table: tb_emp9
Create Table: CREATE TABLE `tb_emp9` (
`id` int(11) NOT NULL,
`name` varchar(25) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_emp_dept` (`deptId`),
CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>
mysql> ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE tb_emp9\G
*************************** 1. row ***************************
Table: tb_emp9
Create Table: CREATE TABLE `tb_emp9` (
`id` int(11) NOT NULL,
`name` varchar(25) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_emp_dept` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>