把课件第 23 页数据字典中的数据结构逐个建成表，装入课件第 13 页的订单，发现订单的“销售商品”一项放不下多件商品，再拆出订单明细表，把整张订单查回来。
MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。
Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。
输入法切到英文。表名、字段名不加引号，字符串值才用单引号。
重建 shop 库，装入实验一拆好的供应商表 supplier 和商品表 goods。数据字典中的“商品库存”就是这张 goods 表。
-- 第 11 章实验二数据：实验一拆好的供应商表 supplier 和商品表 goods -- 整段执行一次即可，想从头再做时重新执行。shop 库会被重建，实验一的台账表不再保留。 DROP DATABASE IF EXISTS shop; CREATE DATABASE shop DEFAULT CHARACTER SET utf8mb4; USE shop; -- 供应商表 CREATE TABLE supplier ( sid INT AUTO_INCREMENT, sname VARCHAR(30) NOT NULL, sphone CHAR(11), PRIMARY KEY (sid), UNIQUE (sname) ); -- 商品库存表，sid 指向供应商 CREATE TABLE goods ( gno CHAR(10) NOT NULL, gname VARCHAR(40), gtype VARCHAR(10), price DECIMAL(8,2), maker VARCHAR(30), intime DATETIME, qty INT, sid INT, PRIMARY KEY (gno), FOREIGN KEY (sid) REFERENCES supplier (sid) ); INSERT INTO supplier (sid, sname, sphone) VALUES (1, '华信图书公司', '18600009999'), (2, '东方数码公司', '13900001234'), (3, '远达文具公司', '13600007788'); INSERT INTO goods VALUES ('1100000112', '数据库原理及应用教程', '图书', 45.50, '人民邮电出版社', '2020-02-11 11:15:25', 100, 1), ('1100000118', 'MySQL 数据库实用教程', '图书', 39.80, '人民邮电出版社', '2020-02-11 11:20:10', 80, 1), ('2120000109', '联想笔记本电脑 E14', '电脑', 5588.60, '联想集团', '2020-03-02 09:30:00', 20, 2), ('2120000110', '联想无线鼠标', '配件', 89.00, '联想集团', '2020-03-02 09:35:00', 50, 2), ('3300000201', '得力中性笔', '文具', 2.50, '得力集团', '2020-03-05 14:00:00', 500, 3); SHOW TABLES;
| 字段 | 含义 | 数据类型 | 要求 |
|---|---|---|---|
| uname | 登录用户名 | VARCHAR(20) | 主码 |
| upwd | 登录密码 | VARCHAR(64) | 不能为空 |
| 找回密码的邮箱 | VARCHAR(50) | ||
| login_time | 登录时间 | DATETIME | 在 login_record 中 |
CREATE TABLE users ( uname VARCHAR(20) NOT NULL, upwd VARCHAR(64) NOT NULL, email VARCHAR(50), PRIMARY KEY (uname) ); -- 登录凭证：谁在什么时间登录成功 CREATE TABLE login_record ( lid INT AUTO_INCREMENT, uname VARCHAR(20) NOT NULL, login_time DATETIME, PRIMARY KEY (lid), FOREIGN KEY (uname) REFERENCES users (uname) ); INSERT INTO users VALUES ('lina', '123456', 'lina@163.com'); INSERT INTO login_record (uname, login_time) VALUES ('lina', '2020-04-10 16:58:02'); SELECT * FROM users; SELECT * FROM login_record;
| users | 数据字典中的“用户信息”：用户名、密码、忘记密码邮箱 |
| login_record | 数据字典中的“登录凭证”：用户名、登录时间。一个用户可以登录多次，所以单独一张表 |
| lid | 登录记录的编号，自动增长 |
| upwd | 这里为了看得清楚存的是明文，实际系统中密码要加密后再存 |
CREATE TABLE address ( aid INT AUTO_INCREMENT, uname VARCHAR(20) NOT NULL, contact VARCHAR(20), sex CHAR(1), addr VARCHAR(60), phone CHAR(11), email VARCHAR(50), PRIMARY KEY (aid), FOREIGN KEY (uname) REFERENCES users (uname) ); INSERT INTO address (uname, contact, sex, addr, phone, email) VALUES ('lina', '李娜', '女', '北京市海淀区成府路 100 号', '13800002876', 'lina@163.com'); SELECT * FROM address;
| contact、sex、addr、phone、email | 数据字典中的联系人、性别、收货地址、手机、电子邮箱 |
| uname | 外码，这个地址属于哪个用户 |
| aid | 地址编号。同一个用户的几个地址靠它区分，订单也用它指明寄到哪里 |
CREATE TABLE orders ( ono CHAR(17) NOT NULL, uname VARCHAR(20), aid INT, submit_time DATETIME, total DECIMAL(10,2), status VARCHAR(10), logistics VARCHAR(40), goods_list VARCHAR(100), PRIMARY KEY (ono), FOREIGN KEY (uname) REFERENCES users (uname), FOREIGN KEY (aid) REFERENCES address (aid) ); -- 一张订单买了两种商品，都写在 goods_list 中 INSERT INTO orders VALUES ('20200410140000001', 'lina', 1, '2020-04-10 17:03:28', 11404.70, '已完成', '中国邮政快递 5650000001', '1100000112 共 5 件，2120000109 共 2 件'); SELECT * FROM orders;
| ono | 订单编号，17 位数字，用 CHAR(17) |
| aid | 外码，订单寄到哪个收货地址 |
| total | 订单总计 11404.70，即 5 × 45.50 + 2 × 5588.60 |
| goods_list | 数据字典中的“销售商品”，这里先照原样写成一段文字 |
-- 只能用 LIKE 在文字中找，找到了也算不出件数 SELECT ono, goods_list FROM orders WHERE goods_list LIKE '%1100000112%';
| LIKE | 按文字查找，能找到订单，但件数写在文字中间，SUM 算不出来 |
| 一个字段放了几件商品 | 字段的值还能再分，下一章会讲，这样的表连第一范式都不满足 |
| 解决办法 | 一张订单有几种商品，就在另一张表中存几行 |
CREATE TABLE order_item ( ono CHAR(17) NOT NULL, gno CHAR(10) NOT NULL, qty INT, price DECIMAL(8,2), PRIMARY KEY (ono, gno), FOREIGN KEY (ono) REFERENCES orders (ono), FOREIGN KEY (gno) REFERENCES goods (gno) ); INSERT INTO order_item VALUES ('20200410140000001', '1100000112', 5, 45.50), ('20200410140000001', '2120000109', 2, 5588.60); -- 商品已经存到明细表中，这一栏不再需要 ALTER TABLE orders DROP COLUMN goods_list; SELECT * FROM order_item;
| PRIMARY KEY (ono, gno) | 同一张订单中，同一种商品只有一行 |
| FOREIGN KEY (gno) | 明细中的商品必须是商品表中存在的商品 |
| price | 成交时的单价。商品以后调价，已经成交的订单不受影响 |
| ALTER TABLE … DROP COLUMN | 删除订单表中的 goods_list 字段 |
SELECT o.ono, o.submit_time, a.contact, a.addr, g.gname, i.qty, i.price FROM orders o JOIN address a ON o.aid = a.aid JOIN order_item i ON o.ono = i.ono JOIN goods g ON i.gno = g.gno; -- 按明细算出的总计，应与 orders 中的 total 相同 SELECT ono, SUM(qty * price) AS 明细合计 FROM order_item GROUP BY ono; SELECT SUM(qty) AS 卖出件数 FROM order_item WHERE gno = '1100000112';
| 四张表连接 | 订单 → 地址、订单 → 明细 → 商品，每一步用外码对上 |
| SUM(qty * price) | 每行的数量乘单价，再加起来 |
| SUM(qty) | 实验 2-4 算不出来的件数，现在一句就能算出 |