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语法编写的,可能需要根据您的具体数据库系统进行调整。在实际应用中,您可能还需要考虑更多的错误处理和完整性约束。