/*
Navicat Premium Dump SQL
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 80036 (8.0.36)
Source Host : localhost:3306
Source Schema : waimai
Target Server Type : MySQL
Target Server Version : 80036 (8.0.36)
File Encoding : 65001
Date: 24/12/2024 12:39:54
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- Table structure for 商品
DROP TABLE IF EXISTS 商品;
CREATE TABLE 商品 (
id int NOT NULL AUTO_INCREMENT,
name varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
business_id int NOT NULL,
price double(16, 2) NOT NULL,
limit_num int NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX business_id(business_id) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- Records of 商品
INSERT INTO 商品 VALUES (1, '鸡公煲', 1, 11.90, 1);
INSERT INTO 商品 VALUES (2, '麻辣香锅', 4, 12.50, 2);
INSERT INTO 商品 VALUES (3, '牛腩', 2, 13.90, NULL);
INSERT INTO 商品 VALUES (4, '韭菜猪肉', 3, 14.90, 3);
-- Table structure for 商家
DROP TABLE IF EXISTS 商家;
CREATE TABLE 商家 (
id int NOT NULL AUTO_INCREMENT,
name varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
turnover double(16, 2) NOT NULL DEFAULT 0.00,
phone varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
address varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- Records of 商家
INSERT INTO 商家 VALUES (1, '我要吃饭', 0.00, '18430983251', '三水区乐平大道');
INSERT INTO 商家 VALUES (2, '饺子', 0.00, '17430893654', '三水区桥头东道');
INSERT INTO 商家 VALUES (3, '港式', 0.00, '16554324238', '三水区桥头西道');
-- Table structure for 客户
DROP TABLE IF EXISTS 客户;
CREATE TABLE 客户 (
id int NOT NULL AUTO_INCREMENT,
name varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
consumption double(16, 2) NOT NULL DEFAULT 0.00,
phone varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
address varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- Records of 客户
INSERT INTO 客户 VALUES (1, '张三', 11.90, '15877062980', '佛山市三水区大鹏公寓');
INSERT INTO 客户 VALUES (2, '李四', 44.70, '15257885321', '佛山市三水区大鹏村青年公寓');
INSERT INTO 客户 VALUES (3, '王五', 55.60, '19054322637', '佛山市三水区五间寮');
INSERT INTO 客户 VALUES (4, '赵六', 0.00, '13579431270', '佛山市三水区塘边村');
-- Table structure for 订单
DROP TABLE IF EXISTS 订单;
CREATE TABLE 订单 (
id int NOT NULL AUTO_INCREMENT,
order_time datetime NOT NULL,
customer_id int NOT NULL,
商品_id int NOT NULL,
number int NOT NULL,
total_cost double(16, 2) NOT NULL,
state int NOT NULL DEFAULT 0,
PRIMARY KEY (id) USING BTREE,
INDEX customer_id(customer_id) USING BTREE,
INDEX 商品_id(商品_id) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Fixed;
-- Records of 订单
INSERT INTO 订单 VALUES (1, '2024-12-12 14:14:35', 1, 1, 1, 11.90, 0);
INSERT INTO 订单 VALUES (2, '2024-12-12 13:45:12', 2, 4, 3, 44.70, 0);
INSERT INTO 订单 VALUES (3, '2024-12-11 01:14:35', 3, 3, 4, 55.60, 0);
-- View structure for order_details
DROP VIEW IF EXISTS order_details;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW order_details AS select 订单.id AS 订单编号,商家.name AS 商家名称,商品.name AS 商品名称,订单.number AS 数量,订单.total_cost AS 总额,订单.state AS 订单状态,客户.name AS 客户姓名,客户.phone AS 电话,客户.address AS 地址,订单.order_time AS 时间 from (((商家 join 商品 on((商家.id = 商品.business_id))) join 订单 on((商品.id = 订单.商品_id))) join 客户 on((订单.customer_id = 客户.id)));
-- Triggers structure for table 订单
DROP TRIGGER IF EXISTS limit_buy;
delimiter ;;
CREATE TRIGGER limit_buy BEFORE INSERT ON 订单 FOR EACH ROW BEGIN
DECLARE p DOUBLE(16,2); /商品单价/
DECLARE lim INT; /限购数量/
SET p=(SELECT price FROM 商品 WHERE 商品.id=NEW.商品_id);
SET lim=(SELECT limit_num FROM 商品 WHERE 商品.id=NEW.商品_id);
IF (lim IS NOT NULL AND NEW.number>lim) THEN
SET NEW.number=lim;
END IF ;
SET NEW.total_cost=NEW.number*p;
END
;;
delimiter ;
-- Triggers structure for table 订单
DROP TRIGGER IF EXISTS calculate_consumption;
delimiter ;;
CREATE TRIGGER calculate_consumption AFTER INSERT ON 订单 FOR EACH ROW BEGIN
UPDATE 客户
SET consumption=consumption+NEW.total_cost
WHERE 客户.id=NEW.customer_id;
END
;;
delimiter ;
-- Triggers structure for table 订单
DROP TRIGGER IF EXISTS calculate_turnover;
delimiter ;;
CREATE TRIGGER calculate_turnover BEFORE UPDATE ON 订单 FOR EACH ROW BEGIN
IF (NEW.state=1 AND OLD.state=0) THEN
UPDATE 商家
SET turnover=turnover+OLD.total_cost
WHERE id=(SELECT business_id FROM 商品 WHERE 商品.id=NEW.商品_id);
ELSEIF (NEW.state=3 AND OLD.state=0) THEN
UPDATE 客户
SET consumption=consumption-OLD.total_cost
WHERE id=OLD.customer_id;
ELSEIF (NEW.state<OLD.state) THEN
SET NEW.state=OLD.state;
END IF ;
END
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;
根据这个数据库写出单表查询、多表查询、排序、分组、分组后的帅选、统计函数的应用、自身连接等内容