课程 › 第 14 章 › 实验三
第 14 章 · 实验三 · 在第四节之后

装数据、试运行与维护

按实施的步骤装入一张订单并校验，做一次功能测试和性能测试，再完成维护中的完整性、安全性、重组和重构。

环　境

MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。

执　行

Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。

注　意

输入法切到英文。表名、字段名不加引号，字符串值才用单引号。

本次实验的顺序　后一个实验用到前一个的结果

  1. 实验 3-1按依赖顺序装入订单
  2. 实验 3-2装入后校验
  3. 实验 3-3功能测试：查一张订单
  4. 实验 3-4性能测试：看执行计划
  5. 实验 3-5维护完整性和安全性
  6. 实验 3-6重组与重构
第一步

数据准备（必做）

用 root 连接执行。重建 ecommerce 库和实验二的十张表，装入 3 个供应商、4 种商品、3 个分类和 2 个用户。

整段复制，全部执行下载 .sql
-- 第 14 章实验三数据：实验二建好的十张表，装入供应商、商品、分类和用户的数据
-- 整段执行一次即可，想从头再做时重新执行。
DROP USER IF EXISTS 'sale1'@'localhost';
DROP DATABASE IF EXISTS ecommerce;
CREATE DATABASE ecommerce DEFAULT CHARACTER SET utf8mb4;
USE ecommerce;

-- 表 14-1 供应商表
CREATE TABLE Supplier (
  supid CHAR(5) NOT NULL COMMENT '供应商编号',
  supname VARCHAR(40) NOT NULL COMMENT '供应商名称',
  email VARCHAR(40) COMMENT '电子邮箱',
  telephone CHAR(11) COMMENT '联系方式',
  PRIMARY KEY (supid)
);
-- 表 14-2 商品表
CREATE TABLE Commodity (
  commodityid CHAR(11) NOT NULL COMMENT '商品编号',
  commodityname VARCHAR(40) NOT NULL COMMENT '商品名',
  manufacturer VARCHAR(40) COMMENT '生产厂家',
  storagetime DATETIME COMMENT '入库时间',
  summary VARCHAR(500) COMMENT '概述',
  thumbnail VARCHAR(100) COMMENT '缩略图的路径',
  PRIMARY KEY (commodityid)
);
-- 表 14-4 分类表
CREATE TABLE Type (
  typeid CHAR(2) NOT NULL COMMENT '分类编号',
  typename CHAR(10) NOT NULL COMMENT '分类名称',
  typedetail VARCHAR(50) COMMENT '分类概述',
  PRIMARY KEY (typeid)
);
-- 表 14-8 用户表
CREATE TABLE Customer (
  customerid INT NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  cusname CHAR(20) NOT NULL COMMENT '登录名',
  cuspassword VARCHAR(64) NOT NULL COMMENT '登录密码',
  cusemail VARCHAR(40) COMMENT '登录邮箱',
  cusauthority TINYINT COMMENT '用户权限，1 采购用户，2 销售用户',
  registime DATETIME COMMENT '注册时间',
  logtime DATETIME COMMENT '上次登录时间',
  PRIMARY KEY (customerid)
);
-- 表 14-7 地址表
CREATE TABLE Address (
  addressid CHAR(10) NOT NULL COMMENT '地址编号',
  person CHAR(4) NOT NULL COMMENT '联系人',
  sex CHAR(2) COMMENT '性别',
  telephone CHAR(11) NOT NULL COMMENT '手机',
  email VARCHAR(40) COMMENT '电子邮箱',
  country CHAR(10) COMMENT '国家',
  province CHAR(10) COMMENT '省',
  city CHAR(10) COMMENT '市',
  street CHAR(20) COMMENT '街道',
  customerid INT COMMENT '用户编号',
  PRIMARY KEY (addressid),
  FOREIGN KEY (customerid) REFERENCES Customer (customerid)
);
-- 表 14-5 订单表，Order 是保留字，要加反引号
CREATE TABLE `Order` (
  orderid CHAR(17) NOT NULL COMMENT '订单编号',
  committime DATETIME NOT NULL COMMENT '提交时间',
  orderstate CHAR(6) NOT NULL COMMENT '订单状态',
  addressid CHAR(10) COMMENT '地址编号',
  PRIMARY KEY (orderid),
  FOREIGN KEY (addressid) REFERENCES Address (addressid)
);
-- 表 14-6 物流表
CREATE TABLE Logistics (
  logisticsid CHAR(3) NOT NULL COMMENT '物流编号',
  logisticsname CHAR(10) NOT NULL COMMENT '物流公司',
  logisticstel CHAR(11) COMMENT '公司电话',
  expressid CHAR(14) NOT NULL COMMENT '快递单号',
  orderid CHAR(17) COMMENT '订单编号',
  PRIMARY KEY (logisticsid),
  FOREIGN KEY (orderid) REFERENCES `Order` (orderid)
);
-- 表 14-3 供应表
CREATE TABLE Supply (
  supid CHAR(5) NOT NULL COMMENT '供应商编号',
  commodityid CHAR(11) NOT NULL COMMENT '商品编号',
  supplytime DATE NOT NULL COMMENT '供应时间',
  supplyprice DECIMAL(9,2) NOT NULL COMMENT '供应单价',
  supplynumber INT NOT NULL COMMENT '供应数量',
  PRIMARY KEY (supid, commodityid),
  FOREIGN KEY (supid) REFERENCES Supplier (supid),
  FOREIGN KEY (commodityid) REFERENCES Commodity (commodityid)
);
-- 表 14-9 属于表
CREATE TABLE Classification (
  typeid CHAR(2) NOT NULL COMMENT '分类编号',
  commodityid CHAR(11) NOT NULL COMMENT '商品编号',
  PRIMARY KEY (typeid, commodityid),
  FOREIGN KEY (typeid) REFERENCES Type (typeid),
  FOREIGN KEY (commodityid) REFERENCES Commodity (commodityid)
);
-- 表 14-10 包含表
CREATE TABLE Contain (
  orderid CHAR(17) NOT NULL COMMENT '订单编号',
  commodityid CHAR(11) NOT NULL COMMENT '商品编号',
  saleprice DECIMAL(9,2) COMMENT '销售单价',
  salenumber INT COMMENT '销售数量',
  PRIMARY KEY (orderid, commodityid),
  FOREIGN KEY (orderid) REFERENCES `Order` (orderid),
  FOREIGN KEY (commodityid) REFERENCES Commodity (commodityid)
);

-- 基础数据
INSERT INTO Supplier VALUES
('01001', '华信图书公司', 'hx@163.com', '18600008965'),
('01002', '东方数码公司', 'df@163.com', '13900001234'),
('02001', '远达文具公司', 'yd@163.com', '13600007788');
INSERT INTO Commodity VALUES
('11000000112', '数据库原理及应用教程', '人民邮电出版社', '2020-02-11 11:15:25', '全面讲解数据库的基本原理和应用', 'img/11000000112.jpg'),
('11000000118', 'MySQL 数据库实用教程', '人民邮电出版社', '2020-02-11 11:20:10', 'MySQL 的安装、使用和管理', 'img/11000000118.jpg'),
('21200000109', '联想笔记本电脑 E14', '联想集团', '2020-03-02 09:30:00', '14 英寸轻薄笔记本', 'img/21200000109.jpg'),
('33000000201', '得力中性笔', '得力集团', '2020-03-05 14:00:00', '0.5 毫米黑色', 'img/33000000201.jpg');
INSERT INTO Type VALUES
('01', '图书', '各类图书和教材'), ('02', '电脑', '笔记本和台式电脑'), ('03', '文具', '办公和学习用品');
INSERT INTO Classification VALUES
('01', '11000000112'), ('01', '11000000118'), ('02', '21200000109'), ('03', '33000000201');
INSERT INTO Supply VALUES
('01001', '11000000112', '2020-02-11', 40.00, 100),
('01001', '11000000118', '2020-02-11', 32.00, 80),
('01002', '21200000109', '2020-03-02', 5000.00, 20),
('02001', '33000000201', '2020-03-05', 1.50, 500);
INSERT INTO Customer (cusname, cuspassword, cusemail, cusauthority, registime, logtime) VALUES
('lina', 'e10adc3949ba59abbe56e057f20f883e', 'lina@163.com', 2, '2020-04-01 10:00:00', '2020-04-10 16:58:02'),
('caigou1', 'fcea920f7412b5da7be0cf42b8c93759', 'cg1@163.com', 1, '2020-01-05 09:00:00', '2020-03-05 13:50:00');

SHOW TABLES;
3-1

按依赖顺序装入订单

讲义 · 装入数据、试运行与整理文档
目的
装入李娜的那张订单：地址、订单、订单明细、物流，再插入一条订单不存在的明细。
前提
已执行数据准备，当前库是 ecommerce。
  1. 按地址、订单、订单明细、物流的顺序插入
  2. 插入一条订单编号不存在的订单明细

参考答案

可以直接复制到 Workbench 执行
-- 按外码的依赖顺序：先地址，再订单，最后订单明细和物流
INSERT INTO Address VALUES
('A000000001', '李娜', '女', '13800002876', 'lina@163.com', '中国', '北京', '北京',
 '海淀区成府路 100 号', 1);
INSERT INTO `Order` VALUES ('20200410140000001', '2020-04-10 17:03:28', '已完成', 'A000000001');
INSERT INTO Contain VALUES
('20200410140000001', '11000000112', 45.50, 5),
('20200410140000001', '21200000109', 5588.60, 2);
INSERT INTO Logistics VALUES ('001', '中国邮政', '11185', '5650000001', '20200410140000001');
-- 订单 20200410140000002 不存在，报错 1452
INSERT INTO Contain VALUES ('20200410140000002', '11000000118', 39.80, 1);

语句解释

装入的顺序被参照的行要先存在，所以地址在订单之前，订单在明细和物流之前
报错 1452外码的值在被参照的表中找不到
装入数据是实施阶段工作量最大的一步
执行后看：前面的插入都成功。最后一句报错 1452。
3-2

装入后校验

讲义 · 装入数据、试运行与整理文档
目的
装完数据后检查行数、找出没有供应商的商品、检查不合理的数值。
前提
已完成实验 3-1。
  1. 统计商品表和包含表的行数
  2. 找出没有任何供应记录的商品
  3. 找出单价或数量不大于 0 的订单明细

参考答案

可以直接复制到 Workbench 执行
SELECT COUNT(*) FROM Commodity;
SELECT COUNT(*) FROM Contain;
-- 没有供应记录的商品
SELECT c.commodityid, c.commodityname
FROM Commodity c LEFT JOIN Supply s ON c.commodityid = s.commodityid
WHERE s.supid IS NULL;
-- 不合理的数值
SELECT * FROM Contain WHERE saleprice <= 0 OR salenumber <= 0;

语句解释

核对行数与原始资料的记录数比较，看有没有漏装
LEFT JOIN … IS NULL找出在另一张表中没有对应行的记录
数值检查外码和主码管不到的错误，要靠这样的查询找出来
执行后看：商品 4 行，订单明细 2 行。后两个查询都没有结果，说明没有发现问题。
3-3

功能测试：查一张订单

讲义 · 装入数据、试运行与整理文档
目的
按用户的实际需要，查出李娜这张订单的收货人、地址、商品和金额，检查结果是否正确。
前提
已完成实验 3-2。
  1. 连接订单、地址、用户、包含、商品五张表查询
  2. 按明细计算订单金额

参考答案

可以直接复制到 Workbench 执行
SELECT o.orderid, o.committime, c.cusname, a.person, a.street,
       m.commodityname, t.saleprice, t.salenumber
FROM `Order` o
JOIN Address a ON o.addressid = a.addressid
JOIN Customer c ON a.customerid = c.customerid
JOIN Contain t ON o.orderid = t.orderid
JOIN Commodity m ON t.commodityid = m.commodityid;
SELECT orderid, SUM(saleprice * salenumber) AS 订单金额
FROM Contain GROUP BY orderid;

语句解释

订单找用户订单通过地址找到用户，这是上一章全局 E-R 图的设计
功能测试运行实际的查询和修改，检查能否完成预定的功能
订单金额5 × 45.50 + 2 × 5588.60 = 11404.70
执行后看：第一个查询 2 行，用户是 lina，收货人是李娜。订单金额 11404.70。
3-4

性能测试：看执行计划

讲义 · 装入数据、试运行与整理文档
目的
常按提交时间查询订单，用 EXPLAIN 查看执行方式，在 committime 上建索引后再看一次。
前提
已完成实验 3-3。
  1. EXPLAIN 按时间范围查询订单
  2. 在 committime 上建索引
  3. 再 EXPLAIN 一次

参考答案

可以直接复制到 Workbench 执行
EXPLAIN SELECT * FROM `Order`
WHERE committime >= '2020-04-01' AND committime < '2020-05-01';
CREATE INDEX idx_committime ON `Order` (committime);
EXPLAIN SELECT * FROM `Order`
WHERE committime >= '2020-04-01' AND committime < '2020-05-01';

语句解释

性能测试测试响应时间等性能指标，达不到设计目标就回头修改设计
type 为 range按索引扫描一个范围
索引的代价查询变快，但插入订单时要多维护一个索引
执行后看：建索引前 type 为 ALL，建索引后 possible_keys 中出现 idx_committime。数据很少时，MySQL 可能仍然选择全表扫描。
3-5

维护完整性和安全性

讲义 · 数据库运行维护
目的
业务要求供应单价必须大于 0，给供应表加上检查约束。再为销售人员建一个只能查看商品表的用户。
前提
已完成实验 3-4。建用户和授权要用 root 连接。
  1. 给 Supply 加检查约束 supplyprice > 0
  2. 建用户 sale1，只授予商品表的查询权限
  3. 插入一条单价为 0 的供应记录

参考答案

可以直接复制到 Workbench 执行
ALTER TABLE Supply ADD CHECK (supplyprice > 0);
CREATE USER 'sale1'@'localhost' IDENTIFIED BY 'Sale@2024';
GRANT SELECT ON ecommerce.Commodity TO 'sale1'@'localhost';
SHOW GRANTS FOR 'sale1'@'localhost';
-- 单价为 0，报错 3819
INSERT INTO Supply VALUES ('01002', '11000000118', '2020-04-01', 0, 10);

语句解释

ADD CHECKMySQL 8.0.16 起支持检查约束，不满足条件的数据不能写入
报错 3819Check constraint is violated，违反了检查约束
GRANT按需授权，销售人员只能查看商品，不能修改，这是第 8 章的内容
执行后看：SHOW GRANTS 显示 sale1 只有 Commodity 的 SELECT 权限。最后一句报错 3819。
3-6

重组与重构

讲义 · 数据库运行维护
目的
用 OPTIMIZE TABLE 重新组织包含表，再给商品表增加库存量字段，比较重组和重构的区别。
前提
已完成实验 3-5。
  1. 重新组织 Contain
  2. 给 Commodity 增加 stock 字段，默认值为 0
  3. 查看 Commodity 的结构

参考答案

可以直接复制到 Workbench 执行
-- 重组：整理存储，不改结构
OPTIMIZE TABLE Contain;
-- 重构：修改表的结构
ALTER TABLE Commodity ADD stock INT DEFAULT 0 COMMENT '库存量';
DESC Commodity;

语句解释

OPTIMIZE TABLE重新整理表的存储，回收删除数据后留下的空间
提示 doing recreateInnoDB 的做法是重建表再更新统计信息，结果是 OK
ALTER TABLE … ADD改变了表的结构，属于重构
备份维护的第一件事是定期备份，做法见第 10 章
执行后看：OPTIMIZE 显示 Table does not support optimize, doing recreate + analyze instead 和 OK。Commodity 多了 stock 字段。
保山学院·人工智能教研室·曹鼎鼎