识岗实践作业

xyh200412

已于 2024-06-24 10:17:53 修改

阅读量80

点赞数 2

文章标签： java 前端服务器

于 2024-06-18 15:50:59 首次发布

本文链接：https://blog.csdn.net/xyh200412/article/details/139774753

版权

ddl

CREATE TABLE `order` (
  `user_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `order_ID` int(11) NOT NULL COMMENT '订单ID',
  `order_status` varchar(255) DEFAULT NULL COMMENT '订单状态',
  `order_time` datetime NOT NULL COMMENT '下单时间',
  `total_price` decimal(10,2) NOT NULL COMMENT '总价',
  PRIMARY KEY (`user_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `order_into` (
  `product_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  `product_quantity` varchar(255) NOT NULL COMMENT '商品数量',
  `unit_price` decimal(10,2) NOT NULL COMMENT '单位价格',
  `total_prices` varchar(255) NOT NULL COMMENT '总价',
  `order_id` int(11) NOT NULL COMMENT '订单ID',
  `detal_id` int(11) NOT NULL COMMENT '订单详情ID',
  PRIMARY KEY (`product_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  `product_description` varchar(255) NOT NULL COMMENT '商品描述',
  `price` decimal(10,2) NOT NULL COMMENT '价格',
  `stock_quantity` varchar(255) NOT NULL COMMENT '库存数量',
  `category_id` int(11) NOT NULL COMMENT '分类ID',
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `product_type` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `category_name` varchar(255) NOT NULL COMMENT '类别名称',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `userID` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户名ID',
  `username` varchar(255) NOT NULL COMMENT '用户名',
  `password` varchar(50) NOT NULL COMMENT '密码',
  `phoneNumber` int(11) DEFAULT NULL COMMENT '电话号码',
  PRIMARY KEY (`userID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


CREATE TABLE `user_wallet` (
  `userID` int(11) NOT NULL COMMENT '用户名ID',
  `wallet_ID` int(11) NOT NULL COMMENT '钱包ID',
  `balance` decimal(10,2) NOT NULL COMMENT '当前余额',
  `integral` varchar(255) DEFAULT NULL COMMENT '积分',
  `currency` varchar(255) DEFAULT NULL COMMENT '货币类型',
  `lacking_state` varchar(255) DEFAULT NULL COMMENT '锁定状态',
  PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_wallet_log` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `wallet_id` int(11) NOT NULL COMMENT '钱包ID',
  `transaction_type` varchar(50) NOT NULL COMMENT '交易类型',
  `user_ID` int(11) NOT NULL COMMENT '用户ID',
  `transaction_amount` varchar(255) NOT NULL COMMENT '交易金额',
  `transaction_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '交易时间',
  PRIMARY KEY (`log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

dml

 
-- 插入8条用户数据
INSERT INTO `user` VALUES ('1', '王语嫣', 'password1','12345678910');
INSERT INTO `user` VALUES ('2', '小龙女', 'password2','12345678478');
INSERT INTO `user` VALUES ('3', '赵灵儿', 'password3','12345671475');
INSERT INTO `user` VALUES ('4', '杨过', 'password4','12345678975');
INSERT INTO `user` VALUES ('5', '向问天', 'password5','12345647852');
INSERT INTO `user` VALUES ('6', '杨潇', 'password6','12345678666');
INSERT INTO `user` VALUES ('7', '郭靖', 'password7','12345671472');
INSERT INTO `user` VALUES ('8', '黄蓉', 'password8','12345678968');
 
 
-- 插入8条用户钱包数据，与user表对应

-- 插入第一条数据
INSERT INTO `user_wallet` (`userID`, `wallet_ID`, `balance`, `integral`, `currency`, `lacking_state`) VALUES
(1, 1001, 1000.00, '100', 'USD', 'unlocked');


-- 插入第二条数据
INSERT INTO `user_wallet` (`userID`, `wallet_ID`, `balance`, `integral`, `currency`, `lacking_state`) VALUES
(2, 1002, 1500.50, '150', 'EUR', 'locked');


-- 插入第三条数据
INSERT INTO `user_wallet` (`userID`, `wallet_ID`, `balance`, `integral`, `currency`, `lacking_state`) VALUES
(3, 1003, 500.25, '50', 'CNY', 'unlocked');


-- 插入第四条数据
INSERT INTO `user_wallet` (`userID`, `wallet_ID`, `balance`, `integral`, `currency`, `lacking_state`) VALUES
(4, 1004, 2000.75, '200', 'GBP', 'locked');


-- 插入第五条数据
INSERT INTO `user_wallet` (`userID`, `wallet_ID`, `balance`, `integral`, `currency`, `lacking_state`) VALUES
(5, 1005, 800.00, '80', 'JPY', 'unlocked');


-- 插入第六条数据
INSERT INTO `user_wallet` (`userID`, `wallet_ID`, `balance`, `integral`, `currency`, `lacking_state`) VALUES
(6, 1006, 1200.30, '120', 'AUD', 'locked');


-- 插入第七条数据
INSERT INTO `user_wallet` (`userID`, `wallet_ID`, `balance`, `integral`, `currency`, `lacking_state`) VALUES
(7, 1007, 999.99, '99', 'BTC', 'unlocked');


-- 插入第八条数据
INSERT INTO `user_wallet` (`userID`, `wallet_ID`, `balance`, `integral`, `currency`, `lacking_state`) VALUES
(8, 1008, 1800.10, '180', 'ETH', 'locked');
 
 
-- 插入8条用户钱包交易日志数据
INSERT INTO `user_wallet_log` VALUES ('1', '1', '充值','1', '100.00', '2024-06-01 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('2', '2', '消费','2', '50.00', '2024-06-08 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('3', '3', '提现','3', '200.00', '2024-06-09 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('4', '4', '充值','4', '150.00', '2024-05-31 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('5', '5', '消费','5', '75.00', '2024-06-04 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('6', '6', '提现','6', '300.00', '2024-06-02 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('7', '7', '充值','7', '250.00', '2024-06-09 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('8', '8', '消费','8', '120.00', '2024-06-07 22:07:48');

INSERT INTO `product_type` VALUES ('1', '智能手机');
INSERT INTO `product_type` VALUES ('2', '笔记本电脑');
INSERT INTO `product_type` VALUES ('3', '智能家居');
INSERT INTO `product_type` VALUES ('4', '电视与显示器');
INSERT INTO `product_type` VALUES ('5', '耳机与音响');
INSERT INTO `product` VALUES ('1', 'iPhone 13', '6999.00', '100', '1');
INSERT INTO `product` VALUES ('2', 'MacBook Pro', '14999.00', '50', '2');
INSERT INTO `product` VALUES ('3', '小米智能音箱', '199.00', '200', '3');
INSERT INTO `product` VALUES ('4', '三星65寸4K电视', '7999.00', '80', '4');
INSERT INTO `product` VALUES ('5', '索尼降噪耳机', '1299.00', '150', '5');

INSERT INTO `order` VALUES ('1', '2', '待支付', '2024-06-09 22:19:14', '120.00');
INSERT INTO `order` VALUES ('2', '5', '已支付', '2024-06-09 22:19:14',  '230.50');
INSERT INTO `order` VALUES ('3', '7', '已完成', '2024-06-09 22:19:14', '345.75');
INSERT INTO `order` VALUES ('4', '9', '已取消', '2024-06-09 22:19:14', '10.00');
INSERT INTO `order` VALUES ('5', '2', '待支付', '2024-06-09 22:19:14', '45.00');
INSERT INTO `order` VALUES ('6', '5', '已支付', '2024-06-09 22:19:14', '78.00');
INSERT INTO `order` VALUES ('7', '7', '待评价', '2024-06-09 22:19:14', '90.20');
INSERT INTO `order` VALUES ('8', '9', '待发货', '2024-06-09 22:19:14','150.00');

INSERT INTO `order_into` VALUES ('1', '100', '50.00', '5000', '5','6');
INSERT INTO `order_into` VALUES ('2', '100', '60.00', '6000', '6','7');
INSERT INTO `order_into` VALUES ('3', '200', '20.00', '4000', '7','8');
INSERT INTO `order_into` VALUES ('4', '300', '30.00', '9000', '6','9');
INSERT INTO `order_into` VALUES ('5', '300', '30.00', '1500', '8','10');
INSERT INTO `order_into` VALUES ('6', '500', '50.00', '2500', '5','11');
INSERT INTO `order_into` VALUES ('7', '600', '60.00', '3600', '8','12');
INSERT INTO `order_into` VALUES ('8', '700', '70.00', '4900', '7','13');