6、修改sex字段的数据类型为CHAR(1)，非空约束
mysql> ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC employees; +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | employeeNumber | int | NO | PRI | NULL | auto_increment | | lastName | varchar(50) | NO | | NULL | | | firstName | varchar(50) | NO | | NULL | | | officeCode | int | NO | MUL | NULL | | | mobile | varchar(25) | YES | UNI | NULL | | | jobTile | varchar(50) | NO | | NULL | | | emoloyee_birth | datetime | YES | | NULL | | | note | varchar(255) | YES | | NULL | | | sex | char(1) | NO | | NULL | | +----------------+--------------+------+-----+---------+----------------+ 9 rows in set (0.00 sec)
可以看到，数据表employees的sex字段的数据类型已经修改为了CHAR(1)。
7、删除字段note
mysql> ALTER TABLE employees DROP note; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC employees; +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+----------------+ | employeeNumber | int | NO | PRI | NULL | auto_increment | | lastName | varchar(50) | NO | | NULL | | | firstName | varchar(50) | NO | | NULL | | | officeCode | int | NO | MUL | NULL | | | mobile | varchar(25) | YES | UNI | NULL | | | jobTile | varchar(50) | NO | | NULL | | | emoloyee_birth | datetime | YES | | NULL | | | sex | char(1) | NO | | NULL | | +----------------+-------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec)
可以看到，数据表employees的note字段的已经删除了。
8、增加字段名称为favorite_activity，数据类型为VARCHAR(100)
mysql> ALTER TABLE employees ADD favorite_activity VARCHAR(100); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC employees; +-------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+----------------+ | employeeNumber | int | NO | PRI | NULL | auto_increment | | lastName | varchar(50) | NO | | NULL | | | firstName | varchar(50) | NO | | NULL | | | officeCode | int | NO | MUL | NULL | | | mobile | varchar(25) | YES | UNI | NULL | | | jobTile | varchar(50) | NO | | NULL | | | emoloyee_birth | datetime | YES | | NULL | | | sex | char(1) | NO | | NULL | | | favorite_activity | varchar(100) | YES | | NULL | | +-------------------+--------------+------+-----+---------+----------------+ 9 rows in set (0.00 sec)
可以看到，数据表employees已经增加字段名称为favorite_activity，数据类型为VARCHAR(100)。
9、修改数据表employees存储引擎为MyISAM
1、删除外键约束
mysql> ALTER TABLE employees DROP FOREIGN KEY office_fk; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
2、修改数据表employees存储引擎为MyISAM
mysql> ALTER TABLE employees ENGINE=MyISAM; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE employees\G; *************************** 1. row *************************** Table: employees Create Table: CREATE TABLE `employees` ( `employeeNumber` int NOT NULL AUTO_INCREMENT, `lastName` varchar(50) NOT NULL, `firstName` varchar(50) NOT NULL, `officeCode` int NOT NULL, `mobile` varchar(25) DEFAULT NULL, `jobTile` varchar(50) NOT NULL, `emoloyee_birth` datetime DEFAULT NULL, `sex` char(1) NOT NULL, `favorite_activity` varchar(100) DEFAULT NULL, PRIMARY KEY (`employeeNumber`), UNIQUE KEY `mobile` (`mobile`), KEY `office_fk` (`officeCode`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
可以看到存储引擎已经变为了
ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
可以看到，已经将数据表employees的存储引擎修改为了MyISAM。
10、将数据表employees名称修改为employees_info
mysql> ALTER TABLE employees RENAME employees_info; Query OK, 0 rows affected (0.01 sec) mysql> SHOW TABLES; +-------------------+ | Tables_in_company | +-------------------+ | employees_info | | offices | +-------------------+ 2 rows in set (0.00 sec)
可以看到，已经将数据表employees的名称修改为employees_info。
11、删除数据表employees_info
mysql> DROP TABLE employees_info; Query OK, 0 rows affected (0.00 sec) mysql> SHOW TABLES; +-------------------+ | Tables_in_company | +-------------------+ | offices | +-------------------+ 1 row in set (0.00 sec)
可以看到，已经将数据表employees删除了。
二、用Navicat实现案例过程
1、新建查询
进入Navicat软件好，新建一个创建数据库的查询。
在查询中编写如下的SQL语句，如下所示。
-- 1、创建数据库company CREATE DATABASE company; -- 2、创建数据表offices USE company; CREATE TABLE offices ( officeCode INT(10) NOT NULL UNIQUE, city VARCHAR(50) NOT NULL, address VARCHAR(50), country VARCHAR(50) NOT NULL, postalCode VARCHAR(15), PRIMARY KEY(officeCode) ); -- 3、创建数据表employees CREATE TABLE employees ( employeeNumber INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, lastName VARCHAR(50) NOT NULL, firstName VARCHAR(50) NOT NULL, mobile VARCHAR(25) UNIQUE, officeCode INT(10) NOT NULL, jobTile VARCHAR(50) NOT NULL, birth DATETIME NOT NULL, note VARCHAR(255), sex VARCHAR(5), CONSTRAINT office_fk FOREIGN KEY(officeCode) REFERENCES offices(officeCode) ); -- 4、将数据表employees的mobile字段修改到officeCode字段后面 ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode; DESC employees; -- 5、将数据表employees的birth字段修改名称为employee_birth ALTER TABLE employees CHANGE birth employee_birth DATETIME; DESC employees; -- 6、修改sex字段的数据类型为CHAR(1)，非空约束 ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL; DESC employees; -- 7、删除字段note ALTER TABLE employees DROP note; DESC employees; -- 8、增加字段名称为favorite_activity，数据类型为VARCHAR(100) ALTER TABLE employees ADD favorite_activity VARCHAR(100); DESC employees; -- 9、修改数据表employees存储引擎为MyISAM ALTER TABLE employees DROP FOREIGN KEY office_fk; ALTER TABLE employees ENGINE=MyISAM; SHOW CREATE TABLE employees; -- 10、将数据表employees名称修改为employees_info ALTER TABLE employees RENAME employees_info; SHOW TABLES; -- 11、删除数据表employees_info; DROP TABLE employees_info; SHOW TABLES;
2、运行过程
依次选择要运行的SQL语句。运行过程如下所示：
1、创建数据库company
2、创建数据表offices
3、创建数据表employees
4、将数据表employees的mobile字段修改到officeCode字段后面
5、将数据表employees的birth字段修改名称为employee_birth
6、修改sex字段的数据类型为CHAR(1)，非空约束
7、删除字段note
8、增加字段名称为favorite_activity，数据类型为VARCHAR(100)
9、修改数据表employees存储引擎为MyISAM
10、将数据表employees名称修改为employees_info
11、删除数据表offices
总结
本文主要介绍了MySQL中数据表的各种操作：
创建数据表、添加各类约束、查看数据表表结构，修改数据表和删除数据 表。使读者可以很快掌握MySQL数据库中关于创建数据表、添加各类约束、查看数据表表结构，修改数据表和删除数据表的基本使用方法。另外还介绍了分别使用cmd环境和Navicat软件环境中创建数据表表、添加各类约束、查看数据表表结构，修改数据表和删除数据表的使用方法。
