一、概要
作为《网上商店》项目开发组的程序员,按要求完成:
数据库的创建;
数据表的创建;
数据的操作。
二、实施步骤及内容
《网上商店》的E-R图如图2.28 所示,逻辑数据模型如图2.29所示,物理数据模型如图2.30所示,数据表字段名定义见表2.12。按以下设计完成数据库创建、数据表创建和数据操作任务:
三、代码
-- 创建数据库表
CREATE TABLE IF NOT EXISTS T_goods(
Goods_id BIGINT NOT NULL,
Supp_code VARCHAR(20) NOT NULL,
Goods_name VARCHAR(50) NOT NULL,
Goods_price DECIMAL(10,2) NOT NULL,
Goods_address VARCHAR(50) NOT NULL,
PRIMARY KEY(Goods_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS T_orders(
Order_id BIGINT NOT NULL,
Goods_id BIGINT NOT NULL,
Quantity INT NOT NULL,
Total_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY(Order_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS T_suppliers(
Supp_code VARCHAR(20) NOT NULL,
Supp_name VARCHAR(50) NOT NULL,
PRIMARY KEY(Supp_code)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 为表T_goods添加外键FK_goods_supp(Supp_code)
ALTER TABLE T_goods ADD CONSTRAINT FK_goods_supp FOREIGN KEY(Supp_code)
REFERENCES T_suppliers(Supp_code);
-- 为表T_orders添加外键FK_goods_ord(goods_id)
ALTER TABLE T_orders ADD CONSTRAINT FK_goods_ord FOREIGN KEY(Goods_id)
REFERENCES T_goods(Goods_id);
-- 插入数据
INSERT INTO T_goods VALUES(1000,'430102','盛唐笔记本',5600,'广东');
INSERT INTO T_goods VALUES(1001,'540199','博士笔记本',6700,'台湾');
INSERT INTO T_goods VALUES(1002,'440708','惠普笔记本',7800,'广东');
INSERT INTO T_suppliers VALUES('430102','盛唐科技');
INSERT INTO T_suppliers VALUES('540199','博士科技');
INSERT INTO T_suppliers VALUES('440708','惠普科技');
INSERT INTO T_orders VALUES(11070232,1000,3,16800);
INSERT INTO T_orders VALUES(11060343,1002,1,7800);
INSERT INTO T_orders VALUES(11050322,1001,2,13400);
-- 将商品名为“惠普笔记本”的价格下调10%
UPDATE T_goods SET Goods_price = Goods_price - Goods_price * 0.1 WHERE Goods_name='惠普笔记本';
-- 查询出商品编号为“1002”的总的订购数量
SELECT Goods_id,Quantity FROM T_orders WHERE Goods_id=1002;
-- 查询出商品名称为“惠普笔记本”的商品的订购数量、总价
SELECT Goods_name,Quantity,Total_price FROM T_orders INNER JOIN T_goods
ON T_orders.`Goods_id` = T_goods.`Goods_id`
AND T_goods.`Goods_name`='惠普笔记本';
-- 创建存储过程P_stored_proc,指定供应商代码,查询该供应商的订单信息
DELIMITER $$
CREATE PROCEDURE P_stored_proc(IN suppCode VARCHAR(20))
BEGIN
SELECT T_orders.*,Supp_code FROM T_orders INNER JOIN T_goods
ON T_orders.`Goods_id` = T_goods.`Goods_id`
AND T_goods.`Supp_code` = suppCode;
END $$
DELIMITER ;
CALL p_stored_proc(440708)
-- 查询所有名称包含“科技”的供应商编号、供应商名称
SELECT * FROM T_suppliers WHERE Supp_name LIKE '%科技%';