把课件中的采购台账原样建成一张表，让修改异常、插入异常、删除异常逐一出现，再把台账拆成供应商表和商品表，重做一遍。
MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。
Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。
输入法切到英文。表名、字段名不加引号，字符串值才用单引号。
新建电子商务系统的数据库 shop 和采购台账表 purchase_ledger，装入 5 条进货记录，其中华信图书公司、东方数码公司各供了两种商品。
-- 第 11 章实验一数据：电子商务系统的采购台账 -- 整段执行一次即可，想从头再做时重新执行。 DROP DATABASE IF EXISTS shop; CREATE DATABASE shop DEFAULT CHARACTER SET utf8mb4; USE shop; -- 采购台账：一行记一次进货，商品和供应商的信息写在同一行 CREATE TABLE purchase_ledger ( gno CHAR(10) NOT NULL, -- 商品编号 gname VARCHAR(40), -- 商品名称 gtype VARCHAR(10), -- 商品类别 price DECIMAL(8,2), -- 单价 maker VARCHAR(30), -- 生产厂家 intime DATETIME, -- 入库时间 qty INT, -- 供货数量 sname VARCHAR(30), -- 供应商 sphone CHAR(11), -- 供应商电话 PRIMARY KEY (gno) ); INSERT INTO purchase_ledger VALUES ('1100000112', '数据库原理及应用教程', '图书', 45.50, '人民邮电出版社', '2020-02-11 11:15:25', 100, '华信图书公司', '18600008965'), ('1100000118', 'MySQL 数据库实用教程', '图书', 39.80, '人民邮电出版社', '2020-02-11 11:20:10', 80, '华信图书公司', '18600008965'), ('2120000109', '联想笔记本电脑 E14', '电脑', 5588.60, '联想集团', '2020-03-02 09:30:00', 20, '东方数码公司', '13900001234'), ('2120000110', '联想无线鼠标', '配件', 89.00, '联想集团', '2020-03-02 09:35:00', 50, '东方数码公司', '13900001234'), ('3300000201', '得力中性笔', '文具', 2.50, '得力集团', '2020-03-05 14:00:00', 500, '远达文具公司', '13600007788'); SELECT * FROM purchase_ledger;
SELECT * FROM purchase_ledger; -- 每个供应商的名称和电话在台账中出现了几次 SELECT sname, sphone, COUNT(*) AS 出现次数 FROM purchase_ledger GROUP BY sname, sphone;
| GROUP BY sname, sphone | 名称和电话都相同的行分成一组 |
| COUNT(*) | 每一组有几行，也就是这个供应商的信息存了几次 |
-- 只改了一行 UPDATE purchase_ledger SET sphone = '18600009999' WHERE gno = '1100000112'; SELECT sname, sphone, COUNT(*) AS 出现次数 FROM purchase_ledger GROUP BY sname, sphone; -- 这家供应商的两行都要改 UPDATE purchase_ledger SET sphone = '18600009999' WHERE gno IN ('1100000112', '1100000118');
| WHERE gno = '1100000112' | 只改了一行，另一行还是旧电话 |
| 修改异常 | 同一个事实存了多份，修改时要一处不漏，漏改就前后矛盾 |
| gno IN (…) | 按商品编号列出要改的行。Workbench 的安全更新模式要求 WHERE 中用主码 |
-- 新供应商还没有商品，商品编号只能是空值 INSERT INTO purchase_ledger (gno, sname, sphone) VALUES (NULL, '星河电器公司', '13700005678');
| gno | 台账的主码是商品编号，主码不能为空 |
| 报错 1048 | Column gno cannot be null，没有商品就记不下供应商 |
| 插入异常 | 现实中存在的信息，因为缺少主码的值，表中放不进去 |
-- 远达文具公司只供过这一种商品 DELETE FROM purchase_ledger WHERE gno = '3300000201'; SELECT * FROM purchase_ledger WHERE sname = '远达文具公司';
| DELETE | 本意只是删除一条进货记录 |
| 删除异常 | 删掉这一行，供应商的名称和电话也一起丢了 |
| 字段 | 含义 | 数据类型 | 要求 |
|---|---|---|---|
| sid | 供应商编号 | INT | 主码，自动增长 |
| sname | 供应商名称 | VARCHAR(30) | 不能为空，不能重复 |
| sphone | 电话 | CHAR(11) | |
| sid（goods 中） | 商品的供应商 | INT | 外码，参照 supplier |
-- 供应商表：一个供应商只存一行 CREATE TABLE supplier ( sid INT AUTO_INCREMENT, sname VARCHAR(30) NOT NULL, sphone CHAR(11), PRIMARY KEY (sid), UNIQUE (sname) ); INSERT INTO supplier (sname, sphone) SELECT DISTINCT sname, sphone FROM purchase_ledger; -- 商品表：不再存供应商的名称和电话，只存 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 goods SELECT l.gno, l.gname, l.gtype, l.price, l.maker, l.intime, l.qty, s.sid FROM purchase_ledger l JOIN supplier s ON l.sname = s.sname; SELECT * FROM supplier; SELECT * FROM goods;
| SELECT DISTINCT | 去掉重复的行，每个供应商只取一次 |
| INSERT INTO … SELECT | 把查询结果直接插入另一张表 |
| JOIN supplier ON … | 按名称找到每件商品的供应商编号 sid |
| FOREIGN KEY (sid) | 商品表中的 sid 必须是供应商表中存在的编号 |
-- 换电话只改一行 UPDATE supplier SET sphone = '13900005555' WHERE sname = '东方数码公司'; -- 没有商品的供应商也能记下来 INSERT INTO supplier (sname, sphone) VALUES ('星河电器公司', '13700005678'); -- 删除商品，供应商的信息还在 DELETE FROM goods WHERE gno IN ('2120000109', '2120000110'); SELECT g.gno, g.gname, s.sname, s.sphone FROM goods g JOIN supplier s ON g.sid = s.sid; SELECT * FROM supplier;
| WHERE sname = '东方数码公司' | sname 上建了唯一约束，安全更新模式下也可以用它 |
| INSERT INTO supplier | 供应商有了自己的表，不需要商品编号 |
| JOIN | 需要看台账的样子时，把两张表连接起来 |