jupyter中写SQL “最佳商品”小实战

在这记录课设任务的过程，代码已完结。

题目要求

一家名为“最佳商品”的批发公司，在我国各地都有一些商店，决定建立一个信息系统，以提高会员的和股票的效率。 系统需要维护自己的数据库，应用程序应该在数据库上开发。 为此，我们收集系统的需求并对它们进行分析。 系统由以下子系统组成：

• 成员管理
• 库存管理
• 销售管理

成员管理

这个子系统是为了维护公司客户（成员）的信息而开发的，例如，他/她是谁。每个客户都应该有会员资格，只有会员才能从商店购买商品。要成为会员，客户应登记其个人信息，如其会员号码、姓氏、姓名、电话号码和购买物品的地址。

库存管理

此系统维护公司销售的项目的所有信息，例如项目id、名称和描述以及存储id。 每个商品都应该有商品编号、名称、描述和商店目前库存的数量。商店必须记录所提供的价格（可以根据供应时间变化）和物品交付商店的时间。

销售管理

此系统维护来自客户（成员)的销售订单的所有信息）。 销售订单清单可以由多个销售项目组成，其数量和销售价格。销售订单清单由几个销售项目组成，有销售订单号，客户，订单和交货日期。销售订单列表中的已订购（或已出售）项目有其项目id、名称、数量和销售价格的信息。

要求：

1. 请根据您在课程中学到的E/R模型为公司设计一个E/R模型。
2. 在SQL中实现E/R图，包括将图转换为几个表，在每个表上绘制FD，将每个表标准化为3NF，并在SQL中创建所有表。
3. 将一些值内插到表中。

请根据您的数据库，在SQL中编写一个应用程序，提供以下功能，包括但不限于：

查询所有会员的数量
查询所有会员的平均费用
查询会员购买的商品数量
查询从商店出售的商品数量。
使用事务来修改项目的详细信息，例如价格和描述
使用事务来修改成员的信息，例如名称和地址

编辑于 2020.12.30.

---

课设结束都都忘了这事了，这里讲一下思路并对代码进行完善。首先，大体上我建立了6个实体：Custmers、Items、Store、List，因为Items和Stores之间是M：N的关系，所以中间加一个Weak（这里只是名字叫这样，本身并不是弱实体），因为题中说时间可以影响价格，我又加了一个Time_Price实体。

逻辑图如下：

三范式图：

下面是代码实现，编写环境Win10+jupyter

%load_ext sql   #导入sql模块
%sql sqlite:///dataset_1.db   #导入我使用的.db数据库文件

%%sql CREATE TABLE customer  #实体
(
    member_id int NOT NULL,
    tel int,
    FirstName varchar(255),
    LastName varchar(255),
    address varchar(255),
    PRIMARY KEY (member_id)
);
#插入数据
%%sql
INSERT INTO customer VALUES (1,123,'Taufik','Hidyat','HeNan');
INSERT INTO customer VALUES (2,234,'gg','hh','beijing');
INSERT INTO customer VALUES (3,345,'aa','bb','sichuan');
INSERT INTO customer VALUES (4,456,'cc','dd','tianjin');
INSERT INTO customer VALUES (5,567,'ee','ff','hebei');

#显示customer实体的数据
%sql SELECT * FROM customer;

图面表示插入数据正常插入。然后就是对其他实体的实现和数据插入代码，这里不再显示插入后的情况。

%%sql CREATE TABLE list #实体
(
    list_id int NOT NULL,
    member_id int,
    item_id int,
    store_id int,
    list_price int,
    buy_quantity int,
    deliver_time varchar(255),
    PRIMARY KEY(list_id),
    FOREIGN KEY(member_id) REFERENCES customer ON UPDATE CASCADE
);

%%sql
INSERT INTO list VALUES (101,1,1,1,100,10,'1.1');
INSERT INTO list VALUES (102,2,2,1,200,20,'1.2');
INSERT INTO list VALUES (103,3,3,3,300,10,'1.3');
INSERT INTO list VALUES (104,4,4,4,400,10,'1.4');
INSERT INTO list VALUES (105,5,5,5,500,10,'1.5');

%%sql CREATE TABLE item
(
    item_id int NOT NULL,
    list_id int,
    item_name varchar(255),
    Desprition varchar(255),
    PRIMARY KEY(item_id),
    FOREIGN KEY(list_id) REFERENCES store ON UPDATE CASCADE
);

%%sql
INSERT INTO item VALUES (1,101,'banana','haha');
INSERT INTO item VALUES (2,102,'apple','hei');
INSERT INTO item VALUES (3,103,'juzi','xixi');
INSERT INTO item VALUES (4,104,'xiangjiao','ji');
INSERT INTO item VALUES (5,105,'pingguo','uy');

%%sql CREATE TABLE _store
(
    store_id int NOT NULL,
    item_name varchar(255),
    PRIMARY KEY(store_id)
);

%%sql
INSERT INTO _store VALUES (1,'banana');
INSERT INTO _store VALUES (2,'apple');
INSERT INTO _store VALUES (3,'juzi');
INSERT INTO _store VALUES (4,'xiangjiao');
INSERT INTO _store VALUES (5,'pingguo');

%%sql CREATE TABLE weak
(
    item_id int NOT NULL,
    store_id int NOT NULL,
    time_in_store varchar(255),
    time_out_store varchar(255),
    store_quantity int,
    PRIMARY KEY(item_id, store_id),
    FOREIGN KEY(item_id) REFERENCES item ON UPDATE CASCADE
    FOREIGN KEY(store_id) REFERENCES store ON UPDATE CASCADE
);

%%sql
INSERT INTO weak VALUES (1,1,'2.1','1.1',100);
INSERT INTO weak VALUES (2,2,'2.2','1.2',100);
INSERT INTO weak VALUES (3,3,'2.3','1.3',100);
INSERT INTO weak VALUES (4,4,'2.4','1.4',100);
INSERT INTO weak VALUES (5,5,'2.5','1.5',100);

%%sql CREATE TABLE time_price
(
    time_out_store varchar(255) NOT NULL,
    item_id int,
    item_price int,
    PRIMARY KEY(time_out_store,item_id)
);

%%sql
INSERT INTO time_price VALUES ('1.1',1,10);
INSERT INTO time_price VALUES ('1.2',2,10);
INSERT INTO time_price VALUES ('1.3',3,10);
INSERT INTO time_price VALUES ('1.4',4,10);
INSERT INTO time_price VALUES ('1.5',5,10);

好的，现在全部实体创建成功，数据也输入到数据库中了，这里我做了一个触发器，用于更改库存变化，理由：某商品余额 = 库存 - 订单中商品量。人为的每次更改太麻烦，所以需要一个触发器，每当订单中有某一商品时，经过商品id比对、商店id比对，对此商店中这一商品的库存更改。代码实现如下：

%%sql
CREATE TRIGGER update_quantity  #trigger名字
AFTER INSERT ON list #每当我插入一个订单商品在list实体时
BEGIN #开始执行
    UPDATE weak #更新weak实体中的
        SET store_quantity = store_quantity - NEW.buy_quantity
            WHERE weak.item_id = NEW.item_id AND weak.store_id = NEW.store_id; #只要两个id匹配，更改库存量
END; #结束

然后我对trigger功能进行检验，我在list中插入了一个需求数量是20的商品。

%sql INSERT INTO list VALUES (107,1,1,1,100,20,'1.6');
%sql SELECT * FROM weak;

发现第一行store_quantity由100变为80，表明代码无误。

下面对课设问题进行解答：

1. Query the number of all memberships

%sql SELECT COUNT(*) AS nums FROM customer;

2. Query average costs of all memberships

%sql SELECT AVG(list_price) FROM list

3. Query the number of items a membership purchased

%sql SELECT SUM(buy_quantity) AS nums FROM list WHERE member_id=1

4. Query the number of items which are sold from a store.

%sql INSERT INTO list VALUES (106,1,6,6,500,50,'1.6');
%sql SELECT SUM(buy_quantity) AS nums FROM list WHERE store_id=1

5. Use transactions to modify details of items, for example prices and descriptions

%sql UPDATE item SET Desprition='good' WHERE item_id=1;
%sql UPDATE time_price SET item_price='50' WHERE item_id=1;
%sql SELECT * FROM item;

%sql SELECT * FROM time_price;

6. Use transactions to modify information of memberships, for example, name and address

%sql UPDATE customer SET FirstName='Liu', LastName='Nan',address='ChongQing' WHERE member_id=1;
%sql SELECT * FROM customer

编辑于2021.1.18 17:08