MySQL数据库应用案例课堂
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

4.5 综合实例——数据表的基本操作

本章全面介绍了MySQL中数据表的各种操作，如创建表、添加各类约束、查看表结构，以及修改和删除表。读者应该掌握这些基本的操作，为以后的学习打下坚实的基础。在这里，给出一个综合实例，让读者全面回顾一下本章的知识要点，并通过这些操作来检验自己是否已经掌握了数据表的常用操作。

1．案例目的

创建、修改和删除表，掌握数据表的基本操作。

创建数据库company，按照表4-4和表4-5给出的表结构在company数据库中创建两个数据表offices和employees，按照操作过程完成对数据表的基本操作。

表4-4 offices表的结构

表4-5 employees表的结构

2．案例操作过程

step01 登录MySQL数据库。

打开Windows命令行窗口，输入登录用户名和密码：

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

或者打开MySQL 5.6 Command Line Client，只输入用户密码也可以登录。登录成功后显示如下信息：

  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 2
  Server version: 5.6.10 MySQL Community Server (GPL)
  Copyright (c) 2000, 2013, 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语句进行操作。

step02 创建数据库company。

创建数据库company的语句及其执行结果如下：

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

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

  mysql> USE company;
  Database changed

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

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

step04 创建表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中要求的数据类型和约束类型。

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

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

step07 修改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，表示该列不允许空值，修改成功。

step08 删除字段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字段已经不在表结构中，删除字段成功。

step09 增加字段名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)，允许空值，添加新字段成功。

step10 删除表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表的外键约束的名称，即创建外键约束时CONSTRAINT关键字后面的参数，结果显示语句执行成功，现在可以删除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的表了，删除表成功。

step11 修改表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)

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

step12 将表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的数据表了。

上一章目录下一章