第 14 章 · 实验二 · 在第三节之后

建立案例的十张表

按表 14-1 到表 14-10 建立电子商务系统的十张表，按外码的依赖顺序逐批建表，最后核对表和外码。

环　境

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

执　行

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

注　意

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

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

实验 2-1供应商表和商品表
实验 2-2分类表和用户表
实验 2-3地址表、订单表、物流表
实验 2-4三张联系表
实验 2-5核对表和外码

第一步

数据准备（必做）

重建一个空的 ecommerce 库，实验一建的练习表被删除。

整段复制，全部执行下载 .sql

-- 第 14 章实验二数据：新建一个空的 ecommerce 库
-- 整段执行一次即可，想从头再做时重新执行。
DROP DATABASE IF EXISTS ecommerce;
CREATE DATABASE ecommerce DEFAULT CHARACTER SET utf8mb4;
USE ecommerce;

2-1

供应商表和商品表

讲义 · 供应商表与商品表

目的: 按表 14-1、表 14-2 建立供应商表 Supplier 和商品表 Commodity，每个字段写上说明。
前提: 已执行数据准备，当前库是 ecommerce。

建立 Supplier 和 Commodity
用 SHOW CREATE TABLE 查看 Commodity

参考答案

可以直接复制到 Workbench 执行

-- 表 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)
);
SHOW CREATE TABLE Commodity;

语句解释

commodityid CHAR(11)	商品编号 11 位，定长
storagetime DATETIME	入库时间要精确到秒
summary VARCHAR(500)	概述比名称长得多，用变长字符串
thumbnail VARCHAR(100)	缩略图只存图片文件的路径，图片本身放在磁盘上

执行后看：两张表建成功。SHOW CREATE TABLE 的结果中能看到每个字段的 COMMENT。

2-2

分类表和用户表

讲义 · 供应表、分类表与订单表讲义 · 用户表、属于表与包含表

目的: 按表 14-4、表 14-8 建立分类表 Type 和用户表 Customer，用户编号由 MySQL 自动增长。
前提: 已完成实验 2-1。

建立 Type
建立 Customer，customerid 自动增长，cuspassword 用 VARCHAR(64)
插入一个用户，查看它的编号

参考答案

可以直接复制到 Workbench 执行

-- 表 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)
);
INSERT INTO Customer (cusname, cuspassword, cusemail, cusauthority)
VALUES ('lina', 'e10adc3949ba59abbe56e057f20f883e', 'lina@163.com', 2);
SELECT customerid, cusname, cusauthority FROM Customer;

语句解释

AUTO_INCREMENT	插入时不写编号，MySQL 自动取下一个值
cusauthority TINYINT	只存 1 或 2，1 表示采购用户，2 表示销售用户
INT	整数，int(11) 中的 11 只是显示宽度，MySQL 8.0 已不推荐这样写
cuspassword	存的是加密后的 32 位或 64 位字符串，不存明文

执行后看：两张表建成功。lina 的 customerid 为 1。

2-3

地址表、订单表、物流表

讲义 · 物流表与地址表讲义 · 供应表、分类表与订单表

目的: 按表 14-7、表 14-5、表 14-6 建立地址表、订单表和物流表，用外码把它们串起来。
前提: 已完成实验 2-2。

建立 Address，customerid 参照 Customer
建立 `Order`，addressid 参照 Address
建立 Logistics，orderid 参照 `Order`

参考答案

可以直接复制到 Workbench 执行

-- 表 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)
);

语句解释

建表的顺序	被参照的表要先建：Customer → Address → Order → Logistics
`Order`	保留字作表名，每次用到都要加反引号
来自合并	地址表的 customerid、订单表的 addressid、物流表的 orderid，都是上一章合并联系时加进来的

执行后看：三张表建成功。

2-4

三张联系表

讲义 · 供应表、分类表与订单表讲义 · 用户表、属于表与包含表

目的: 按表 14-3、表 14-9、表 14-10 建立供应表、属于表和包含表，主码由两个编号组成，两个编号又分别是外码。
前提: 已完成实验 2-3。

建立 Supply、Classification、Contain
每张表的两个编号都加外码

参考答案

可以直接复制到 Workbench 执行

-- 表 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)
);

语句解释

PRIMARY KEY (supid, commodityid)	两个编号合起来作主码，是 m:n 联系转换出的关系
FOREIGN KEY	两个编号分别参照另外两张表，都要加外码
supplyprice DECIMAL(9,2)	单价是金额，用 DECIMAL

执行后看：三张表建成功。

2-5

核对表和外码

讲义 · 用户表、属于表与包含表

目的: 查看建好的表，并从 information_schema 中列出全部外码，核对是否一个不少。
前提: 已完成实验 2-4。

列出 ecommerce 中的表
列出每个外码所在的表、字段和参照的表

参考答案

可以直接复制到 Workbench 执行

SHOW TABLES;
SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'ecommerce'
  AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME;

语句解释

information_schema	MySQL 存放数据库、表、字段等信息的系统库
KEY_COLUMN_USAGE	记录主码、外码等约束用到的字段
REFERENCED_TABLE_NAME	外码参照的表，不为空的行就是外码

执行后看：共 10 张表，9 个外码：地址、订单、物流各 1 个，供应、属于、包含各 2 个。在 Windows 上表名会显示为小写。

上一节第三节　案例：十张表的物理结构下一节第四节　数据库实施和运行维护

保山学院·人工智能教研室·曹鼎鼎

建立案例的十张表

环 境

执 行

注 意

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

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

环　境

执　行

注　意

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