创建一个四个表的商品销售管理系统的数据库，写出创建表的代码，并画出er 图，写出需求分析，写出主键外键约束，每个表插入五组数据，并进行增删改查处理，并创建三个视图，创建三个触发器

好的，我将会按照您的要求逐一回答您的问题。

首先，根据您所描述的需求，我们需要创建四个表来管理商品销售。这四个表可以分别是：商品表、客户表、订单表和订单详情表。

1. 商品表
CREATE TABLE `goods` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`price` decimal(10,2) NOT NULL,
`inventory` int(11) NOT NULL,
PRIMARY KEY (`id`)
);

2. 客户表
CREATE TABLE `customers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`phone` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
);

3. 订单表
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`order_date` datetime NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

4. 订单详情表
CREATE TABLE `order_details` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`goods_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`price` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_goods_id` FOREIGN KEY (`goods_id`) REFERENCES `goods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

接下来，我们画出ER图：


需求分析：

商品表：用于存储所有商品的信息，包括商品名称、价格和库存数量等。

客户表：用于存储所有客户的信息，包括客户姓名和电话号码等。

订单表：用于存储所有订单的信息，包括订单日期和客户ID等。

订单详情表：用于存储订单的详细信息，包括订单ID、商品ID、数量和价格等。

主键、外键约束：

商品表：主键为id，没有外键约束。

客户表：主键为id，没有外键约束。

订单表：主键为id，外键约束为customer_id，参考客户表的id字段。

订单详情表：主键为id，外键约束为order_id和goods_id，分别参考订单表的id字段和商品表的id字段。

每个表插入五组数据，并进行增删改查处理：

商品表：

INSERT INTO `goods` (`name`, `price`, `inventory`) VALUES
('iPhone 12', 7999.00, 100),
('iPad Pro', 8999.00, 50),
('MacBook Pro', 13999.00, 30),
('AirPods Pro', 1999.00, 200),
('Apple Watch', 3499.00, 80);

SELECT * FROM `goods`;

UPDATE `goods` SET `inventory`= `inventory` - 1 WHERE `name`='iPhone 12';

DELETE FROM `goods` WHERE `name`='Apple Watch';

客户表：

INSERT INTO `customers` (`name`, `phone`) VALUES
('张三', '13888888888'),
('李四', '13999999999'),
('王五', '13666666666'),
('赵六', '13555555555'),
('钱七', '13777777777');

SELECT * FROM `customers`;

UPDATE `customers` SET `phone`='13111111111' WHERE `name`='张三';

DELETE FROM `customers` WHERE `name`='赵六';

订单表：

INSERT INTO `orders` (`customer_id`, `order_date`) VALUES
(1, '2021-10-01 09:00:00'),
(2, '2021-10-02 10:00:00'),
(3, '2021-10-03 11:00:00'),
(4, '2021-10-04 12:00:00'),
(5, '2021-10-05 13:00:00');

SELECT * FROM `orders`;

UPDATE `orders` SET `customer_id`=3 WHERE `id`=1;

DELETE FROM `orders` WHERE `id`=5;

订单详情表：

INSERT INTO `order_details` (`order_id`, `goods_id`, `quantity`, `price`) VALUES
(1, 1, 2, 15998.00),
(2, 2, 1, 8999.00),
(3, 3, 3, 41997.00),
(4, 4, 5, 9995.00),
(5, 5, 2, 6998.00);

SELECT * FROM `order_details`;

UPDATE `order_details` SET `quantity`= `quantity` + 1 WHERE `id`=1;

DELETE FROM `order_details` WHERE `id`=5;

创建三个视图：

1. 展示每个订单的总金额

CREATE VIEW `order_total_view` AS
SELECT `order_id`, SUM(`quantity` * `price`) AS `total`
FROM `order_details`
GROUP BY `order_id`;

2. 展示每个客户的总消费额

CREATE VIEW `customer_total_view` AS
SELECT `c`.`id`, `c`.`name`, SUM(`od`.`quantity` * `od`.`price`) AS `total`
FROM `customers` `c`
LEFT JOIN `orders` `o` ON `c`.`id` = `o`.`customer_id`
LEFT JOIN `order_details` `od` ON `o`.`id` = `od`.`order_id`
GROUP BY `c`.`id`;

3. 展示每个商品的销售情况

CREATE VIEW `goods_sale_view` AS
SELECT `g`.`name`, SUM(`od`.`quantity`) AS `sales`
FROM `goods` `g`
LEFT JOIN `order_details` `od` ON `g`.`id` = `od`.`goods_id`
GROUP BY `g`.`id`;

创建三个触发器：

1. 插入订单时，更新商品库存数量

CREATE TRIGGER `update_inventory` AFTER INSERT ON `order_details`
FOR EACH ROW
BEGIN
UPDATE `goods` SET `inventory` = `inventory` - NEW.`quantity` WHERE `id` = NEW.`goods_id`;
END;

2. 删除订单时，还原商品库存数量

CREATE TRIGGER `restore_inventory` AFTER DELETE ON `order_details`
FOR EACH ROW
BEGIN
UPDATE `goods` SET `inventory` = `inventory` + OLD.`quantity` WHERE `id` = OLD.`goods_id`;
END;

3. 更新订单详情时，更新订单总金额

CREATE TRIGGER `update_order_total` AFTER INSERT, DELETE, UPDATE ON `order_details`
FOR EACH ROW
BEGIN
UPDATE `orders` SET `total` = (SELECT SUM(`quantity` * `price`) FROM `order_details` WHERE `order_id` = NEW.`order_id`) WHERE `id` = NEW.`order_id`;
END;

以上就是根据您的要求创建商品销售管理系统的数据库所需的代码和相关操作，希望能够帮助您更好地理解和应用数据库设计和管理方面的知识。