本文已参与「新人创作礼」活动，一起开启掘金创作之路。

【MySQL】E-R图及关系模型应用

一、目的与要求：

设计并绘出此数据库的E-R图
将E-R图转换成关系模式
针对已给定的数据库及应用需求，提出优化或改进措施并实现

二、内容：

设计并绘出此数据库的E-R图

在这里插入图片描述

将E-R图转换成关系模式

客户（客户编号#，客户名，客户地址，城市，邮编，联系人，电子邮箱）
订单（订单号#，订单项#，数量，单价）
水果（水果编号#，水果名，价格）
供应商（供应商编号#，供应商名，邮编，电话，城市）
下（客户编号#，订单号#，订购日期）
订购（订单号#，订单项#，水果编号#）
提供（供应商编号#，水果编号#）

3.针对已给定的数据库及应用需求，提出优化或改进措施并实现

①.在customers表中插入price字段，实现自动计算用户的订单总价格，不需要跳转到其他表在通过订单号查看价格。节省时间成本，方便查看总价格。

源码

ALTER TABLE customers
ADD price decimal(10,2)

CREATE PROCEDURE updateCus_P1()
BEGIN
DECLARE number INT;
DECLARE price decimal(10,2); 
DECLARE updateOrdCUS CURSOR FOR 
SELECT sum(pay) AS SumSI
from orders;
DECLARE CONTINUE HANDLER FOR 
NOT FOUND SET number=1;
SET number=0;
OPEN updateOrdCUS;
FETCH next FROM updateOrdCUS INTO price;
WHILE (number=0) DO

UPDATE customers
SET price =(SELECT sum(pay)
AS SumSI
FROM orders
WHERE orders.c_id = customers.c_id);

FETCH next FROM updateOrdCUS INTO price ;
END WHILE;
CLOSE updateOrdCUS;
END

在这里插入图片描述

②.在原先的基础上创建触发器，使得能够自动保存对所有表的插入、更新、删除操作到opertaion表中，而不仅限于fruits表与customers表

CREATE TRIGGER insertORS AFTER INSERT ON orderitems FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('orderitems','INSERT',now());
CREATE TRIGGER updateORS AFTER UPDATE ON orderitems FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('orderitems','UPDATE',now());
CREATE TRIGGER deleteORS AFTER DELETE ON orderitems FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('orderitems','DELETE',now());

CREATE TRIGGER insertORD AFTER INSERT ON orders FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('orders','INSERT',now());
CREATE TRIGGER updateORD AFTER UPDATE ON orders FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('orders','UPDATE',now());
CREATE TRIGGER deleteORD AFTER DELETE ON orders FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('orders','DELETE',now());

CREATE TRIGGER insertSUP AFTER INSERT ON suppliers FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('suppliers','INSERT',now());
CREATE TRIGGER updateSUP AFTER UPDATE ON suppliers FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('suppliers','UPDATE',now());
CREATE TRIGGER deleteSUP AFTER DELETE ON suppliers FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('suppliers','DELETE',now());

在这里插入图片描述

③.在suppliers表中添加fruit字段，使得可以直观的看到供应商可以提供哪些编号的水果，方便商店对供应商的选择

ALTER TABLE suppliers
ADD fruit char(10)

CREATE PROCEDURE updateSUP_Fru1()
BEGIN
DECLARE number INT;
DECLARE fruit char(10); 
DECLARE updateSUPFru CURSOR FOR 
SELECT GROUP_CONCAT(f_id) AS SumSI
from fruits;
DECLARE CONTINUE HANDLER FOR 
NOT FOUND SET number=1;
SET number=0;
OPEN updateSUPFru;
FETCH next FROM updateSUPFru INTO fruit;
WHILE (number=0) DO

UPDATE suppliers SET fruit = (
SELECT GROUP_CONCAT(f_id)
AS SumSI
FROM fruits
WHERE suppliers.s_id = fruits.s_id
GROUP BY s_id
);

FETCH next FROM updateSUPFru INTO fruit ;
END WHILE;
CLOSE updateSUPFru;
END

在这里插入图片描述

小结

1．遇到的问题及解决过程

在E-B图的绘制过程中，由于对该数据库的部分属性不够了解，在各属性关系中不清楚是一对多还是多对多的关系。通过仔细观察各个属性表的主外键等关系解决该问题。

2．实验中产生的错误及原因分析

① [Err] 1364 - Field 's_name' doesn't have a default value

系统设置问题，没有设置插入空值自动填充，在系统中输入语句

SET@@GLOBAL.sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";在重启系统即可解决。

② [Err] 1062 - Duplicate entry '100' for key 'PRIMARY'

对主键的输入重复了，将该值替换为其他值在输入

3．体会和收获

在Navicat Premium可视化系统中对数据库进行一些列操作，实现综合运用SQL语言相关知识如变量、游标、函数、触发器等解决实际问题。通过解决出现的问题与错误，在解决问题的过程中能够学到更多的MYSQL数据库相关的其他内容。通过数据库后台系统的操控了解更多可视化数据库管理系统的功能与MYSQL数据库的用处之广。通过多种操作的综合运用，能够更加熟练的更加便捷的使用数据库系统。同时也能够了解更多数据库在实际生活中的应用。