MYSQL设计表格，插入语数据

zzt0121

已于 2024-06-18 17:01:43 修改

阅读量177

点赞数 3

文章标签： mysql android 数据库

于 2024-06-17 11:44:48 首次发布

本文链接：https://blog.csdn.net/zzt0121/article/details/139739573

版权

注释DDL

CREATE TABLE `order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `order_status` varchar(50) NOT NULL DEFAULT '待支付' COMMENT '订单状态（例如：待支付、已支付、已发货、已完成等）',
  `order_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
  `payment_status` varchar(50) NOT NULL DEFAULT '未支付' COMMENT '支付状态（例如：未支付、已支付）',
  `payment_time` timestamp NULL DEFAULT '0000-00-00 00:00:00' COMMENT '支付时间',
  `total_price` decimal(10,2) NOT NULL COMMENT '订单总价',
  PRIMARY KEY (`order_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `order_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

CREATE TABLE `order_info` (
  `order_info_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单详情ID',
  `order_id` int(11) NOT NULL COMMENT '订单ID',
  `product_id` int(11) NOT NULL COMMENT '商品ID',
  `quantity` int(11) NOT NULL COMMENT '购买数量',
  `unit_price` decimal(10,2) NOT NULL COMMENT '单价',
  PRIMARY KEY (`order_info_id`),
  KEY `order_id` (`order_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `order_info_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `order` (`order_id`) ON DELETE CASCADE,
  CONSTRAINT `order_info_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

CREATE TABLE `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  `product_name` varchar(255) NOT NULL COMMENT '商品名称',
  `price` decimal(10,2) NOT NULL COMMENT '商品价格',
  `stock` int(11) NOT NULL DEFAULT '0' COMMENT '商品库存',
  `type_id` int(11) NOT NULL COMMENT '所属类别ID',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`product_id`),
  KEY `type_id` (`type_id`),
  CONSTRAINT `product_ibfk_1` FOREIGN KEY (`type_id`) REFERENCES `product_type` (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

CREATE TABLE `product_type` (
  `type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '类别ID',
  `type_name` varchar(255) DEFAULT NULL COMMENT '类别名称',
  `description` varchar(255) DEFAULT NULL COMMENT '类别描述',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(255) DEFAULT NULL COMMENT '用户名',
  `password` varchar(255) DEFAULT NULL COMMENT '密码',
  `email` varchar(255) DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(255) DEFAULT NULL COMMENT '手机号',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

CREATE TABLE `user_wallet` (
  `wallet_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '钱包ID',
  `user_id` int(11) DEFAULT NULL COMMENT '所属用户ID',
  `balance` decimal(10,2) DEFAULT NULL COMMENT '钱包余额',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`wallet_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `user_wallet_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;


CREATE TABLE `user_wallet_log` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `user_id` int(11) DEFAULT NULL COMMENT '所属用户ID',
  `transaction_type` enum('充值','消费') DEFAULT NULL COMMENT '交易类型（例如：''充值'', ''消费''等）',
  `amount` decimal(10,2) DEFAULT NULL COMMENT '交易金额（可正可负，表示充值或消费）',
  `transaction_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '交易时间',
  PRIMARY KEY (`log_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `user_wallet_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

生成DDL对应的DML

order对应DML语句


INSERT INTO `order` (order_id, user_id, order_status, order_time, payment_status, payment_time, total_price)
VALUES
    (1, 1, '待支付', '2024-06-18 12:00:00', '未支付', '0000-00-00 00:00:00', 150.00),
    (2, 2, '已发货', '2024-06-17 13:30:00', '已支付', '2024-06-17 13:35:00', 220.50),
    (3, 3, '已完成', '2024-06-16 10:10:00', '已支付', '2024-06-16 10:15:00', 180.75),
    (4, 4, '待支付', '2024-06-15 14:40:00', '未支付', '0000-00-00 00:00:00', 95.30),
    (5, 5, '已支付', '2024-06-14 16:20:00', '已支付', '2024-06-14 16:25:00', 120.60),
    (6, 6, '已发货', '2024-06-13 11:50:00', '已支付', '2024-06-13 11:55:00', 260.90),
    (7, 7, '已完成', '2024-06-12 15:20:00', '已支付', '2024-06-12 15:25:00', 310.50),
    (8, 8, '待支付', '2024-06-11 17:10:00', '未支付', '0000-00-00 00:00:00', 80.80),
    (9, 9, '已支付', '2024-06-10 13:40:00', '已支付', '2024-06-10 13:45:00', 110.10),
    (10, 10, '已发货', '2024-06-09 12:30:00', '已支付', '2024-06-09 12:35:00', 135.00);
		
		INSERT INTO order_info (order_id, product_id, quantity, unit_price)
VALUES
    (1, 1, 2, 199.99),
    (1, 2, 1, 99.99),
    (2, 3, 3, 59.99),
    (3, 4, 2, 79.99),
    (3, 5, 1, 129.99),
    (4, 6, 4, 39.99),
    (5, 7, 2, 89.99),
    (6, 8, 3, 49.99),
    (7, 9, 1, 149.99),
    (8, 10, 2, 79.99);

product对应的DML语句

INSERT INTO product (product_name, price, stock, type_id)
VALUES
    ('智能手表', 999.99, 50, 1),
    ('无线耳机', 499.99, 30, 1),
    ('平板电脑', 1999.99, 20, 2),
    ('电子书阅读器', 799.99, 15, 2),
    ('智能摄像头', 399.99, 25, 3),
    ('蓝牙音箱', 299.99, 30, 3),
    ('智能门铃', 149.99, 40, 4),
    ('智能插座', 99.99, 50, 4),
    ('运动手环', 199.99, 60, 5),
    ('智能体重秤', 129.99, 70, 5);

	INSERT INTO `product_type` (`type_name`, `description`)
VALUES
    ('电子产品', '各类电子设备'),
    ('家电', '家庭使用的电器'),
    ('通讯设备', '手机等通讯相关'),
    ('电脑周边', '与电脑配套的产品'),
    ('存储设备', '如 U 盘、硬盘等');

user对应的DML语句

INSERT INTO `user` VALUES ('1', '宋亚轩', 'password1', 'user1@example.com', '13100012341', '2024-06-01 21:05:31');
INSERT INTO `user` VALUES ('2', '贺峻霖', 'password2', 'user2@example.com', '13200043212', '2024-06-02 21:05:32');
INSERT INTO `user` VALUES ('3', '张真源', 'password3', 'user3@example.com', '13300056783', '2024-06-03 21:05:33');
INSERT INTO `user` VALUES ('4', '马嘉祺', 'password4', 'user4@example.com', '13400099994', '2024-05-30 21:05:34');
INSERT INTO `user` VALUES ('5', '丁程鑫', 'password5', 'user5@example.com', '13500055555', '2024-06-04 22:05:35');
INSERT INTO `user` VALUES ('6', '刘耀文', 'password6', 'user6@example.com', '13600066666', '2024-06-01 22:07:36');
INSERT INTO `user` VALUES ('7', '严浩翔', 'password7', 'user7@example.com', '13700099997', '2024-06-01 22:07:37');
INSERT INTO `user` VALUES ('8', '王俊凯', 'password8', 'user8@example.com', '13800033338', '2024-06-04 22:07:38');
INSERT INTO `user` VALUES ('9', '王源', 'password9', 'user9@example.com', '13900000009', '2024-06-08 22:07:39');
INSERT INTO `user` VALUES ('10', '易烊千玺', 'password10', 'user10@example.com', '13800000010', '2024-06-08 22:07:39');

INSERT INTO `user_wallet` (`user_id`, `balance`)
VALUES
    (1, 100.50),
    (2, 200.75),
    (3, 150.20),
    (4, 80.00),
    (5, 120.30),
    (6, 90.15),
    (7, 180.40),
    (8, 70.60),
    (9, 110.80),
    (10, 130.70);
		
		
		
		INSERT INTO `user_wallet_log` (`user_id`, `transaction_type`, `amount`, `transaction_time`)
VALUES
    (1, '充值', 50.00, '2024-06-18 10:00:00'),
    (2, '消费', 20.00, '2024-06-18 11:00:00'),
    (3, '充值', 80.00, '2024-06-18 12:00:00'),
    (4, '消费', 15.00, '2024-06-18 13:00:00'),
    (5, '充值', 30.00, '2024-06-18 14:00:00'),
    (6, '消费', 10.00, '2024-06-18 15:00:00'),
    (7, '充值', 45.00, '2024-06-18 16:00:00'),
    (8, '消费', 25.00, '2024-06-18 17:00:00'),
    (9, '充值', 60.00, '2024-06-18 18:00:00'),
    (10, '消费', 18.00, '2024-06-18 19:00:00');