4.5 综合案例——数据表的基本操作
本章全面介绍了MySQL中数据表的各种操作，如创建表、添加各类约束、查看表结构，以及修改和删除表。读者应该掌握这些基本的操作，为以后的学习打下坚实的基础。本章给出一个综合案例，让读者全面回顾一下本章的知识要点，并通过这些操作来检验自己是否已经了掌握数据表的常用操作。
1. 案例目的：创建、修改和删除表，掌握数据表的基本操作。
创建数据库company，按照下面给出的表结构在company数据库中创建两个数据表offices和employees，按照操作过程完成对数据表的基本操作。
表4.2 offices表结构
|
字段名 |
数据类型 |
主键 |
外键 |
非空 |
唯一 |
自增 |
|
officeCode |
INT(10) |
是 |
否 |
是 |
是 |
否 |
|
city |
INT(11) |
否 |
否 |
是 |
否 |
否 |
|
address |
VARCHAR(50) |
否 |
否 |
否 |
否 |
否 |
|
country |
VARCHAR(50) |
否 |
否 |
是 |
否 |
否 |
|
postalCode |
VARCHAR(25) |
否 |
否 |
否 |
是 |
否 |
表4.3 employees表结构
|
字段名 |
数据类型 |
主键 |
外键 |
非空 |
唯一 |
自增 |
|
employeeNumber |
INT(11) |
是 |
否 |
是 |
是 |
是 |
|
lastName |
VARCHAR(50) |
否 |
否 |
是 |
否 |
否 |
|
firstName |
VARCHAR(50) |
否 |
否 |
是 |
否 |
否 |
|
mobile |
VARCHAR(25) |
否 |
否 |
否 |
是 |
否 |
|
officeCode |
VARCHAR(10) |
否 |
是 |
是 |
否 |
否 |
|
jobTitle |
VARCHAR(50) |
否 |
否 |
是 |
否 |
否 |
|
birth |
DATETIME |
否 |
否 |
是 |
否 |
否 |
|
note |
VARCHAR(255) |
否 |
否 |
否 |
否 |
否 |
|
sex |
VARCHAR(5) |
否 |
否 |
否 |
否 |
否 |
2. 案例操作过程
步骤1：登陆MySQL数据库。
打开windows命令行，输入登陆用户名和密码：
C:>mysql –h localhost -u root -p
Enter password: **
或者打开MySQL5.5 Command Line Client，只用输入用户密码也可以登陆。登陆成功后显示如下信息：
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.5.13 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
登陆成功，可以输入SQL语句进行操作。
步骤2：创建数据库company。
创建数据库company的语句如下：
mysql> CREATE DATABASE company;
Query OK, 1 row affected (0.00 sec)
结果显示创建成功，在company数据库中创建表，必须先选择该数据库，输入语句如下：
mysql> USE company;
Database changed
结果显示选择数据库成功。
步骤3：创建表offices。
创建表offices的语句如下：
CREATE TABLE offices
(
officeCode INT(10) NOT NULL UNIQUE,
city VARCHAR(50) NOT NULL,
address VARCHAR(50) NOT NULL,
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15) NOT NULL,
PRIMARY KEY (officeCode)
);
执行成功之后，使用SHOW TABLES；语句查看数据库中的表，语句如下：
mysql> show tables;
+-----------------------+
| Tables_in_company |
+-----------------------+
| offices |
+-----------------------+
1 row in set (0.00 sec)
可以看到，数据库中已经有了数据表offices，创建成功。
步骤4 创建表employees。
创建表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) NOT NULL,
officeCode INT(10) NOT NULL,
jobTitle VARCHAR(50) NOT NULL,
birth DATETIME,
note VARCHAR(255),
sex VARCHAR(5),
CONSTRAINT office_fk FOREIGN KEY(officeCode) REFERENCES offices(officeCode)
);
执行成功之后，使用SHOW TABLES；语句查看数据库中的表，语句如下：
mysql> show tables;
+------------------------+
| Tables_in_company |
+------------------------+
| employees |
| offices |
+------------------------+
2 rows in set (0.00 sec)
可以看到，现在数据库中已经创建好了employees和offices两个数据表。要检查表的结构是否按照要求创建，使用DESC分别查看两个表的结构，如果语句正确，则显示结果如下：
mysql>DESC offices;
+--------------+-------------+-------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+-------+------+---------+-------+
| officeCode | int(10) | NO | PRI | NULL | |
| city | varchar(50) | NO | | NULL | |
| address | varchar(50) | NO | | NULL | |
| country | varchar(50) | NO | | NULL | |
| postalCode | varchar(15) | NO | | NULL | |
+------------+---------------+--------+-----+---------+-------+
5 rows in set (0.02 sec)
mysql>DESC employees;
+----------------------+--------------+--------+-------+---------+--------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+--------+-------+---------+--------------------+
| employeeNumber | int(11) | NO | PRI | NULL | auto_increment |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| mobile | varchar(25) | NO | | NULL | |
| officeCode | int(10) | NO | MUL | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| birth | datetime | YES | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+--------------------+----------------+--------+-------+---------+--------------------+
9 rows in set (0.00 sec)
可以看到，两个表中字段分别满足【表4.2】和【表4.3】中要求的数据类型和约束类型。
步骤5：将表employees的mobile字段修改到officeCode字段后面。
修改字段位置，需要用到ALTER TABLE语句，输入语句如下：
mysql> ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行成功，使用DESC查看修改后的结果如下：
mysql>DESC employees;
+----------------------+--------------+--------+-------+---------+--------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+--------+-------+---------+--------------------+
| employeeNumber | int(11) | NO | PRI | NULL | auto_increment |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| officeCode | int(10) | NO | MUL | NULL | |
| mobile | varchar(25) | NO | | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employee _birth | datetime | YES | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+--------------------+----------------+--------+-------+---------+--------------------+
9 rows in set (0.00 sec)
可以看到，mobile字段已经插入到officeCode字段的后面。
步骤6：将表employees的birth字段改名为employee_birth。
修改字段名，需要用到ALTER TABLE语句，输入语句如下：
ALTER TABLE employees CHANGE birth employee_birth DATETIME;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行成功，使用DESC查看修改后的结果如下：
mysql>DESC employees;
+----------------------+--------------+--------+-------+---------+--------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+--------+-------+---------+--------------------+
| employeeNumber | int(11) | NO | PRI | NULL | auto_increment |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| mobile | varchar(25) | NO | | NULL | |
| officeCode | int(10) | NO | MUL | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employee _birth | datetime | YES | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+--------------------+----------------+--------+-------+---------+--------------------+
9 rows in set (0.00 sec)
可以看到，表中只有employee_birth字段，已经没有名称为birth的字段了，修改名称成功。