进销存系统

1. **建立mypos数据库和五个表**
CREATE DATABASE mypos;
USE mypos;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    contact_info VARCHAR(255)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    unit_of_measure VARCHAR(50),
    retail_price DECIMAL(10, 2),
    promotion_price DECIMAL(10, 2)
);

CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    quantity INT DEFAULT 0
);

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    customer_id INT,
    sale_time DATETIME,
    cashier VARCHAR(255),
    due_amount DECIMAL(10, 2),
    received_amount DECIMAL(10, 2),
    discount_amount DECIMAL(10, 2),
    notes TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE sales_details (
    sale_detail_id INT PRIMARY KEY,
    sale_id INT,
    product_id INT,
    quantity INT,
    retail_price DECIMAL(10, 2),
    promotion_price DECIMAL(10, 2),
    FOREIGN KEY (sale_id) REFERENCES sales(sale_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);
```

2. **编写触发器**

```sql
DELIMITER $$

CREATE TRIGGER insert_product_trigger AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO inventory (product_id, quantity) VALUES (NEW.product_id, 0);
END$$

DELIMITER ;
```

3. **编写存储过程**

```sql
DELIMITER $$

CREATE PROCEDURE insert_product(
    IN pname VARCHAR(255),
    IN uom VARCHAR(50),
    IN rprice DECIMAL(10, 2),
    IN pprice DECIMAL(10, 2)
)
BEGIN
    INSERT INTO products (product_name, unit_of_measure, retail_price, promotion_price)
    VALUES (pname, uom, rprice, pprice);
END$$

DELIMITER ;
```

4. **利用存储过程插入商品记录**

```sql
CALL insert_product('可口可乐', '瓶', 2.50, 2.30);
CALL insert_product('百事可乐', '瓶', 2.50, 2.30);
CALL insert_product('伊利牛奶', '盒', 5.20, 4.80);
CALL insert_product('农夫山泉', '瓶', 1.50, 1.30);
CALL insert_product('涪陵榨菜', '袋', 1.00, 0.80);
```

5. **在商品表中建立索引**

```sql
CREATE INDEX index_product_name ON products(product_name);
```

6. **建立商品库存视图**

```sql
CREATE VIEW v_inventory AS
SELECT p.product_name, i.quantity
FROM products p
JOIN inventory i ON p.product_id = i.product_id;
```

7. **利用触发器实现删除销售记录时修改库存**

```sql
DELIMITER $$

CREATE TRIGGER delete_sale_trigger BEFORE DELETE ON sales
FOR EACH ROW
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT product_id, quantity FROM sales_details WHERE sale_id = OLD.sale_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO @product_id, @quantity;
        IF done THEN
            LEAVE read_loop;
        END IF;
        UPDATE inventory SET quantity = quantity + @quantity WHERE product_id = @product_id;
        DELETE FROM sales_details WHERE product_id = @product_id AND sale_id = OLD.sale_id;
    END LOOP;
    CLOSE cur;
    SET @product_id = NULL;
    SET @quantity = NULL;
END$$

DELIMITER ;
```

请注意，这些SQL语句是基于通用的SQL语法编写的，可能需要根据您的具体数据库系统进行调整。在实际应用中，您可能还需要考虑更多的错误处理和完整性约束。