Mysql修改数据表实战

一 将数据表tb_dept3改名为tb_deptment3

1 使用ALTER TABLE将表tb_dept3改名为tb_deptment3，SQL语句如下：

mysql> ALTER TABLE tb_dept3 RENAME tb_deptment3;
Query OK, 0 rows affected (0.10 sec)

二 将数据表tb_dept1中name字段的数据类型由VARCHAR(22)修改成VARCHAR(30)

1 执行修改表名操作之前，使用DESC查看tb_dept表结构，结果如下：

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.00 sec)

可以看到现在name字段的数据类型为VARCHAR(22)，下面修改其类型。输入如下SQL语句并执行：

mysql> ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

三 将数据表tb_dept1中的location字段名称改为loc，数据类型保持不变，SQL语句如下

mysql> ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

四 将数据表tb_dept1中的loc字段名称改为location，同时将数据类型变为VARCHAR(60)，SQL语句如下

mysql> ALTER TABLE tb_dept1 CHANGE loc location VARCHAR(60);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

五 在数据表tb_dept1中添加一个没有完整性约束的INT类型的字段managerId（部门经理编号），SQL语句如下

mysql> ALTER TABLE tb_dept1 ADD managerId INT(10);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

六 在数据表tb_dept1中添加一个不能为空的VARCHAR(12)类型的字段column1，SQL语句如下

mysql> ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) not null;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

七 在数据表tb_dept1中添加一个INT类型的字段column2，SQL语句如下

mysql> ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

八 在数据表tb_dept1中name列后添加一个INT类型的字段column3，SQL语句如下

mysql> ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

九 删除数据表tb_dept1表中的column2字段

删除column2字段，SQL语句如下：

mysql> ALTER TABLE tb_dept1 DROP column2;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

十 将数据表tb_dept中的column1字段修改为表的第一个字段，SQL语句如下

mysql> ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

十一 将数据表tb_dept1中的column1字段插入到location字段后面，SQL语句如下

mysql> ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER location;
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

十二 将数据表tb_deptment3的存储引擎修改为MyISAM

1 在修改存储引擎之前，先使用SHOW CREATE TABLE查看表tb_deptment3当前的存储引擎，结果如下。

mysql> SHOW CREATE TABLE tb_deptment3 \G
*************************** 1. row ***************************
       Table: tb_deptment3
Create Table: CREATE TABLE `tb_deptment3` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `STH` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

可以看到，表tb_deptment3当前的存储引擎为ENGINE=InnoDB，接下来修改存储引擎类型，输入如下SQL语句并执行：

mysql> ALTER TABLE tb_deptment3 ENGINE=MyISAM;
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用SHOW CREATE TABLE再次查看表tb_deptment3的存储引擎，发现表tb_dept的存储引擎变成了“MyISAM”，结果如下：

mysql> SHOW CREATE TABLE tb_deptment3 \G
*************************** 1. row ***************************
       Table: tb_deptment3
Create Table: CREATE TABLE `tb_deptment3` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `STH` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

十三 删除数据表tb_emp9中的外键约束

首先创建表tb_emp9，创建外键deptId关联tb_dept1表的主键id，SQL语句如下：

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)
);

使用SHOW CREATE TABLE查看表tb_emp9的结构，结果如下：

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)

可以看到，已经成功添加了表的外键，下面删除外键约束，SQL语句如下：

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

执行完毕之后，将删除表tb_emp的外键约束，使用SHOW CREATE TABLE再次查看表tb_emp9结构，结果如下：

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)