CREATE DATABASE zaixiangouwu;
USE zaixiangouwu;
CREATE TABLE Users (
UserID INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户唯一标识',
Username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
Email VARCHAR(100) NOT NULL UNIQUE COMMENT '用户邮箱',
Password VARCHAR(255) NOT NULL COMMENT '加密后的密码',
Address VARCHAR(255) COMMENT '用户地址',
Phone VARCHAR(20) COMMENT '联系电话',
RegistrationTime DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
INDEX idx_Username (Username),
INDEX idx_Email (Email)
)
CREATE TABLE Products (
ProductID INT AUTO_INCREMENT PRIMARY KEY COMMENT '商品唯一标识',
ProductName VARCHAR(100) NOT NULL COMMENT '商品名称',
Category VARCHAR(50) NOT NULL COMMENT '商品分类',
Price DECIMAL(10,2) NOT NULL COMMENT '单价(单位:元)',
Inventory INT NOT NULL DEFAULT 0 COMMENT '库存量',
Description TEXT COMMENT '商品描述',
ImageURL VARCHAR(255) COMMENT '商品图片URL',
INDEX idx_Category (Category)
)
CREATE TABLE Orders (
OrderID INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单唯一标识',
UserID INT NOT NULL COMMENT '下单用户ID',
ProductID INT NOT NULL COMMENT '购买商品ID',
Quantity INT NOT NULL COMMENT '购买数量',
OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
Status ENUM('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled') DEFAULT 'Pending' COMMENT '订单状态',
TotalAmount DECIMAL(10,2) AS (Quantity * (SELECT Price FROM Products WHERE Products.ProductID = Orders.ProductID)) STORED COMMENT '订单总金额(自动计算:数量×单价)',
PaymentMethod ENUM('Credit Card', 'PayPal', 'Cash on Delivery', 'Bank Transfer') COMMENT '支付方式',
DeliveryAddress VARCHAR(255) NOT NULL COMMENT '配送地址',
FOREIGN KEY (UserID) REFERENCES Users(UserID) ON DELETE CASCADE,
FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE,
INDEX idx_UserID (UserID),
INDEX idx_OrderStatus (Status)
)
INSERT INTO Users (Username, Email, Password, Address, Phone) VALUES
('zhangsan', 'zhangsan@edu.cn', 'bcrypt$hash123', '南校区宿舍楼A栋101', '13800138001'),
('lisi', 'lisi@example.com', 'sha256$abc123', '北校区图书馆B座203', '13900139002'),
('wangwu', 'wangwu@campus.com', 'argon2$passwd', '东校区学生公寓C区305', '13600136003'),
('zhaoliu', 'zhaoliu@mail.edu', 'pbkdf2$qwerty', '西校区研究生楼D栋412', '13500135004'),
('qianqi', 'qianqi@univ.org', 'scrypt$letmein', '中心校区国际学生公寓E座108', '13400134005'),
('sunba', 'sunba@campus.cn', 'bcrypt$hello123', '南校区教师公寓F栋302', '13300133006'),
('zhoujiu', 'zhoujiu@mail.com', 'sha256$sunshine', '北校区体育场旁G区115', '13200132007'),
('wushi', 'wushi@edu.org', 'argon2$password1', '东校区实验楼H座206', '13100131008'),
('zhengshiyi', 'zheng11@example.edu', 'pbkdf2$admin123', '西校区艺术中心I栋503', '13000130009'),
('prof_li', 'li.prof@univ.edu', 'scrypt$welcome', '教师公寓1号楼601', '13700137010'),
('dr_zhang', 'zhang.doc@med.edu', 'bcrypt$starwars', '医学院宿舍J栋401', '13900239011'),
('liuxuesheng', 'liu.student@campus.cn', 'sha256$dragon', '国际交流学院K座307', '13800348012'),
('chenyanjiu', 'chen.research@lab.edu', 'argon2$football', '科研楼L栋209', '13700457013'),
('zhaojiaoshou', 'zhao.prof@academy.edu', 'pbkdf2$baseball', '教授楼M栋105', '13600566014'),
('qianxuesheng', 'qian.student@mail.com', 'scrypt$superman', '南校区宿舍N栋214', '13500675015'),
('sunlaoshi', 'sun.teacher@edu.cn', 'bcrypt$batman', '教育学院O座312', '13400784016'),
('zhoujiaoshou', 'zhou.prof@univ.org', 'sha256$harrypotter', '文科楼P栋408', '13300893017'),
('wuguanli', 'wu.admin@campus.com', 'argon2$guitar', '行政楼Q座501', '13200992018'),
('zhangzhuren', 'zhang.director@edu.org', 'pbkdf2$butterfly', '院系办公室R栋102', '13100091019'),
('prof_wang', 'wang@univ.edu', 'scrypt$mountain', '教师公寓3号楼502', '13700137020');
INSERT INTO Products (ProductName, Category, Price, Inventory, Description, ImageURL) VALUES
('食堂餐食配送', '外卖配送', 5.00, 9999, '食堂/周边商家餐食配送', 'https://example.com/food.png'),
('快递代取服务', '快递代取', 3.00, 9999, '快递站至宿舍配送', 'https://example.com/express.jpg'),
('文件打印服务', '文件打印', 2.00, 9999, '图书馆/打印店代打印服务', 'https://example.com/printing.jpg'),
('超市商品代购', '超市代购', 4.00, 9999, '校内超市商品代买', 'https://example.com/shopping.png'),
('排队代办服务', '排队代办', 6.00, 9999, '讲座/活动/缴费排队', 'https://example.com/queue.jpg'),
('紧急送药服务', '紧急送药', 8.00, 9999, '校内药店紧急配送', 'https://example.com/medicine.png'),
('跨校区文件传递', '资料传递', 3.50, 9999, '跨校区文件资料传递', 'https://example.com/delivery.jpg'),
('临时物品保管', '物品暂存', 2.50, 9999, '临时物品保管服务', 'https://example.com/storage.jpg'),
('文具书籍代购', '学习用品', 1.50, 9999, '文具/书籍代购', 'https://example.com/stationery.jpg'),
('洗衣店取送服务', '衣物送洗', 7.00, 9999, '洗衣店取送服务', 'https://example.com/laundry.png'),
('电子设备维修', '电子产品', 10.00, 9999, '电脑手机维修取送', 'https://example.com/electronics.jpg'),
('活动物资搬运', '活动协助', 15.00, 9999, '社团活动物资搬运', 'https://example.com/event.jpg'),
('紧急代课服务', '代课签到', 20.00, 9999, '紧急情况代课服务', 'https://example.com/attendance.jpg'),
('二手物品代送', '二手交易', 5.00, 9999, '二手物品代送', 'https://example.com/secondhand.jpg'),
('个性化定制服务', '其他服务', 12.00, 9999, '个性化定制服务', 'https://example.com/other.jpg');
INSERT INTO Orders (UserID, ProductID, Quantity, DeliveryAddress, PaymentMethod) VALUES
(1, 1, 1, '南校区宿舍A栋101', '微信支付'),
(3, 2, 2, '北校区3号楼308', '支付宝'),
(5, 1, 1, '东校区学生公寓C区305', '微信支付'),
(2, 3, 3, '西校区图书馆4层', '支付宝'),
(4, 4, 1, '中心校区国际学生公寓E座108', '微信支付'),
(7, 5, 1, '北校区体育场旁G区115', 'Cash on Delivery'),
(6, 1, 2, '南校区教师公寓F栋302', '微信支付'),
(9, 6, 1, '西校区艺术中心I栋503', '支付宝'),
(8, 2, 1, '东校区实验楼H座206', '微信支付'),
(10, 7, 1, '教师公寓1号楼601', '支付宝'),
(12, 1, 1, '国际交流学院K座307', '微信支付'),
(11, 8, 2, '医学院宿舍J栋401', 'Cash on Delivery'),
(13, 9, 3, '科研楼L栋209', '微信支付'),
(15, 10, 1, '南校区宿舍N栋214', '支付宝'),
(14, 11, 1, '教授楼M栋105', '微信支付'),
(16, 1, 1, '教育学院O座312', '微信支付'),
(18, 12, 1, '研究生公寓D座1204', '支付宝'),
(17, 13, 1, '文科楼P栋408', '微信支付'),
(19, 14, 2, '行政楼Q座501', '支付宝'),
(20, 15, 1, '教师公寓3号楼502', 'Cash on Delivery'),
(1, 3, 1, '南校区宿舍A栋101', '微信支付'),
(3, 4, 1, '北校区3号楼308', '支付宝'),
(5, 5, 1, '东校区学生公寓C区305', '微信支付'),
(7, 6, 1, '北校区体育场旁G区115', '支付宝'),
(2, 1, 1, '西校区图书馆4层', '微信支付'),
(4, 2, 3, '中心校区国际学生公寓E座108', '支付宝'),
(6, 7, 1, '南校区教师公寓F栋302', 'Cash on Delivery'),
(8, 8, 2, '东校区实验楼H座206', '微信支付'),
(10, 9, 1, '教师公寓1号楼601', '支付宝'),
(12, 10, 1, '国际交流学院K座307', '微信支付'),
(11, 1, 2, '医学院宿舍J栋401', '微信支付'),
(13, 2, 1, '科研楼L栋209', '支付宝'),
(15, 3, 4, '南校区宿舍N栋214', 'Cash on Delivery'),
(14, 4, 1, '教授楼M栋105', '微信支付'),
(16, 5, 1, '教育学院O座312', '支付宝'),
(18, 6, 1, '研究生公寓D座1204', '微信支付'),
(17, 7, 1, '文科楼P栋408', '支付宝'),
(19, 8, 3, '行政楼Q座501', '微信支付'),
(20, 9, 2, '教师公寓3号楼502', 'Cash on Delivery'),
(1, 10, 1, '南校区宿舍A栋101', '微信支付'),
(3, 11, 1, '北校区3号楼308', '支付宝'),
(5, 12, 1, '东校区学生公寓C区305', '微信支付'),
(7, 13, 1, '北校区体育场旁G区115', 'Cash on Delivery'),
(2, 14, 1, '西校区图书馆4层', '支付宝'),
(4, 15, 1, '中心校区国际学生公寓E座108', '微信支付'),
(6, 1, 1, '南校区教师公寓F栋302', '微信支付'),
(8, 2, 2, '东校区实验楼H座206', '支付宝'),
(10, 3, 1, '教师公寓1号楼601', '微信支付'),
(12, 4, 1, '国际交流学院K座307', '支付宝');