15.5实战演练——数据表的基本操作

9/17/2020 9:40:31 PM 人评论次浏览

15.5　实战演练——数据表的基本操作

下面通过一个案例的介绍来了解数据表的基本操作。

创建数据库company，按照下面给出的表结构在company数据库中创建如表15-2和表15-3所示两个数据表offices和employees，按照操作过程完成对数据表的基本操作。

表15-2　offices表结构

表15-3　employees表结构

（续表）

案例操作过程如下。

01　登录MySQL数据库。打开Windows命令行，输入登录用户名和密码：

　　C:\>mysql –h localhost -u root -p
　　Enter password: **

或者打开MySQL 5.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语句进行操作。

02　创建数据库company。创建数据库company的语句如下：

　　mysql> CREATE DATABASE company;
　　Query OK, 1 row affected (0.00 sec)

结果显示创建成功，在company数据库中创建表，必须先选择该数据库，输入语句如下：

　　mysql> USE company;
　　Database changed

结果显示选择数据库成功。

03　创建表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，创建成功。

04　创建表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)

可以看到，两个表中字段分别满足表15-2和表15-3中要求的数据类型和约束类型。

05　将表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字段的后面。

06　将表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的字段了，修改名称成功。

07　修改sex字段，数据类型为CHAR(1)，非空约束。修改字段数据类型，需要用到ALTER TABLE语句，输入语句如下：

　　mysql>ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;
　　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
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	char(1)	NO		NULL
+-------------------	+---------------	+--------	+-------	+---------	+-------------------+

9 rows in set (0.00 sec)

执行结果可以看到，sex字段的数据类型由前面的VARCHAR(5)修改为CHAR(1)，且其Null列显示为NO，表示该列不允许空值，修改成功。

08　删除字段note。删除字段，需要用到ALTER TABLE语句，输入语句如下：

　　mysql> ALTER TABLE employees DROP note;
　　Query OK, 0 rows affected (0.01 sec)
　　Records: 0  Duplicates: 0  Warnings: 0

结果显示执行语句成功，使用DESC employees查看语句执行后的结果：

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
sex	char(1)	NO		NULL
+-------------------	+---------------	+--------	+-------	+---------	+-------------------+

8 rows in set (0.00 sec)

可以看到，DESC语句返回了8个列字段，note字段已经不在表结构中，删除字段成功。

09　增加字段名favoriate_activity，数据类型为VARCHAR(100)。增加字段，需要用到ALTER TABLE语句，输入语句如下：

　　mysql> ALTER TABLE employees ADD favoriate_activity VARCHAR(100);
　　Query OK, 0 rows affected (0.01 sec)
　　Records: 0  Duplicates: 0  Warnings: 0

结果显示执行语句成功，使用DESC employees查看语句执行后的结果：

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
sex	char(1)	NO		NULL
favoriate_activity	varchar(100)	YES		NULL
+---------------------	+----------------	+--------	+-------	+--------	+-------------------+

9 rows in set (0.00 sec)

可以看到，数据表employees中增加了一个新的列favoriate_activity，数据类型为VARCHAR(100)，允许空值，添加新字段成功。

10　删除表offices。在创建表employees的时候，设置了表的外键，该表关联了其父表的officeCode主键，如前面所述，删除关联表时，要先删除子表employees的外键约束，才能删除父表，因此，必须先删除employees表的外键约束。

（1）删除employees表的外键约束，输入如下语句：

　　mysql>ALTER TABLE employees DROP FOREIGN KEY office_fk;
　　Query OK, 0 rows affected (0.01 sec)
　　Records: 0  Duplicates: 0  Warnings: 0

其中，office_fk为表employees的外键约束的名称，即创建外键约束时约束关键字后面的参数，结果显示语句执行成功，现在可以删除offices父表。

（2）删除表offices，输入如下语句：

　　mysql>DROP TABLE offices;
　　Query OK, 0 rows affected (0.00 sec)

结果显示执行删除操作成功，使用SHOW TABLES语句查看数据库中的表，结果如下：

　　mysql> show tables;
　　+-----------------------+
　　| Tables_in_company     |
　　+-----------------------+
　　| employees             |
　　+-----------------------+
　　1 row in set (0.00 sec)

可以看到，数据库中已经没有名称为offices的表了，删除表成功。

11　修改表employees的存储引擎为MyISAM。修改表的存储引擎时，需要用到ALTER TABLE语句，输入语句如下：

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

结果显示执行修改存储引擎操作成功，使用SHOW CREATE TABLE语句查看表结构，结果如下：

　　mysql> show CREATE TABLE employees\G;
　　*************************** 1. row ***************************
　　       Table: employees
　　Create Table: CREATE TABLE 'employees' (
　　  'employeeNumber' int(11) NOT NULL AUTO_INCREMENT,
　　  'lastName' varchar(50) NOT NULL,
　　  'firstName' varchar(50) NOT NULL,
　　  'officeCode' int(10) NOT NULL,
　　  'mobile' varchar(25) DEFAULT NULL,
　　  'jobTitle' varchar(50) NOT NULL,
　　  'employee_birth' datetime DEFAULT NULL,
　　  'sex' char(1) NOT NULL,
　　  'favoriate_activity' varchar(100) DEFAULT NULL,
　　  PRIMARY KEY ('employeeNumber'),
　　  KEY 'office_fk' ('officeCode')
　　) ENGINE=MyISAM DEFAULT CHARSET=utf8
　　1 row in set (0.00 sec)

可以看到，倒数第二行中ENGINE后面的参数已经修改为MyISAM，修改成功。

12　将表employees名称修改为employees_info。修改数据表名，需要用到ALTER TABLE语句，输入语句如下：

　　mysql>ALTER TABLE employees RENAME employees_info;
　　Query OK, 0 rows affected (0.00 sec)

结果显示执行语句成功，使用SHOW TABLES语句查看执行结果：

　　mysql> show tables;
　　+-----------------------+
　　| Tables_in_company      |
　　+-----------------------+
　　| employees_info        |
　　+----------------------+
　　1 rows in set (0.00 sec)

可以看到数据库中已经没有名称为employees的数据表。

文章教程

15.5实战演练——数据表的基本操作