2、网上图书订购

已于 2024-06-28 09:28:07 修改
阅读量281 收藏
点赞数 3
文章标签： 数据库 mysql 学习 sql
于 2024-06-26 17:36:46 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/wangzihuan_5/article/details/139994845
版权

完整DDL+DML


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for administarators
-- ----------------------------
DROP TABLE IF EXISTS `administarators`;
CREATE TABLE `administarators`  (
  `admin_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '管理员编号',
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '管理员',
  PRIMARY KEY (`admin_id`) USING BTREE,
  INDEX `username`(`username`) USING BTREE,
  CONSTRAINT `administarators_ibfk_1` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '管理员表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of administarators
-- ----------------------------
INSERT INTO `administarators` VALUES (1, '小喜', '5468742514', '包包大人');
INSERT INTO `administarators` VALUES (2, '小美', '5468742514', '包包大人');
INSERT INTO `administarators` VALUES (3, '小沸', '5468742514', '包包大人');
INSERT INTO `administarators` VALUES (4, '懒懒', '5468742514', '包包大人');
INSERT INTO `administarators` VALUES (5, '暖暖', '5468742514', '包包大人');
INSERT INTO `administarators` VALUES (6, '一棵草', '5468742514', '包包大人');
INSERT INTO `administarators` VALUES (7, '小喜', '5468742514', '包包大人');
INSERT INTO `administarators` VALUES (8, '小美', '5468742514', '包包大人');
INSERT INTO `administarators` VALUES (9, '小沸', '5468742514', '包包大人');
INSERT INTO `administarators` VALUES (10, '懒懒', '5468742514', '包包大人');
INSERT INTO `administarators` VALUES (11, '暖暖', '5468742514', '包包大人');
INSERT INTO `administarators` VALUES (12, '一棵草', '5468742514', '包包大人');

-- ----------------------------
-- Table structure for books
-- ----------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books`  (
  `book_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '书籍编号',
  `isbn` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '国际标准书号',
  `bookname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '书名',
  `bpress` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '出版社',
  `bprice` decimal(4, 2) NULL DEFAULT NULL COMMENT '单价',
  `bauthor` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '作者',
  PRIMARY KEY (`book_id`) USING BTREE,
  UNIQUE INDEX `isbn`(`isbn`) USING BTREE,
  INDEX `bookname`(`bookname`) USING BTREE,
  CONSTRAINT `books_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `order` (`book_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '书籍表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of books
-- ----------------------------
INSERT INTO `books` VALUES (1, '978-1', '遨游神秘洋', '新华出版社', 50.00, '黄伟明');
INSERT INTO `books` VALUES (2, '978-2', '勇闯四季城', '新华出版社', 60.00, '黄伟明');
INSERT INTO `books` VALUES (3, '978-3', '心世界奇遇', '新华出版社', 23.00, '黄伟明');
INSERT INTO `books` VALUES (4, '978-4', '决战次时代', '新华出版社', 55.00, '黄伟明');
INSERT INTO `books` VALUES (5, '978-5', '奇妙大营救', '新华出版社', 40.00, '黄伟明');
INSERT INTO `books` VALUES (6, '978-6', '异国大冒险', '新华出版社', 40.00, '黄伟明');

-- ----------------------------
-- Table structure for leavewords
-- ----------------------------
DROP TABLE IF EXISTS `leavewords`;
CREATE TABLE `leavewords`  (
  `leavewords_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(20) NOT NULL COMMENT '用户名',
  `remarks` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `re_status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '1' COMMENT '(0:已支付 1:待支付 )',
  PRIMARY KEY (`leavewords_id`) USING BTREE,
  INDEX `userName`(`user_id`) USING BTREE,
  CONSTRAINT `leavewords_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `order` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of leavewords
-- ----------------------------
INSERT INTO `leavewords` VALUES (1, 1, '尽快发货', '0');
INSERT INTO `leavewords` VALUES (2, 2, '', '1');
INSERT INTO `leavewords` VALUES (3, 3, '未支付期待发货', '1');
INSERT INTO `leavewords` VALUES (4, 4, '', '0');
INSERT INTO `leavewords` VALUES (5, 5, '', '1');
INSERT INTO `leavewords` VALUES (6, 6, '尽快发货', '0');

-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`  (
  `order_id` int(19) NOT NULL AUTO_INCREMENT COMMENT '订购表编号',
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `quantity` int(11) NOT NULL COMMENT '订购数量',
  `book_id` int(11) NOT NULL COMMENT '书籍编号',
  `user_id` int(11) NOT NULL COMMENT '用户编号',
  `ship_id` int(11) NOT NULL COMMENT '发货编号',
  PRIMARY KEY (`order_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `username`(`username`) USING BTREE,
  INDEX `book_id`(`book_id`) USING BTREE,
  INDEX `ship_id`(`ship_id`) USING BTREE,
  CONSTRAINT `order_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 32 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '订购表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES (1, '小喜', 20, 1, 1, 1);
INSERT INTO `order` VALUES (2, '小美', 30, 2, 2, 2);
INSERT INTO `order` VALUES (3, '小沸', 90, 3, 3, 3);
INSERT INTO `order` VALUES (4, '懒懒', 111, 4, 4, 4);
INSERT INTO `order` VALUES (5, '暖暖', 21, 5, 5, 5);
INSERT INTO `order` VALUES (6, '一棵草', 29, 6, 6, 6);

-- ----------------------------
-- Table structure for shipped
-- ----------------------------
DROP TABLE IF EXISTS `shipped`;
CREATE TABLE `shipped`  (
  `ship_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '发货单编号',
  `datetime` datetime(0) NULL DEFAULT NULL COMMENT '发货日期',
  `ship_man` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '发货人',
  PRIMARY KEY (`ship_id`) USING BTREE,
  CONSTRAINT `shipped_ibfk_1` FOREIGN KEY (`ship_id`) REFERENCES `order` (`ship_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '发货单表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of shipped
-- ----------------------------
INSERT INTO `shipped` VALUES (1, '2024-06-09 22:19:14', '乌龟速递');
INSERT INTO `shipped` VALUES (2, '2024-10-09 22:19:14', '乌龟速递');
INSERT INTO `shipped` VALUES (3, '2023-06-09 22:19:14', '乌龟速递');
INSERT INTO `shipped` VALUES (4, '2014-06-09 22:19:14', '乌龟速递');
INSERT INTO `shipped` VALUES (5, '2024-06-09 21:14:14', '乌龟速递');
INSERT INTO `shipped` VALUES (6, '2024-07-09 22:54:14', '乌龟速递');

-- ----------------------------
-- Table structure for stock
-- ----------------------------
DROP TABLE IF EXISTS `stock`;
CREATE TABLE `stock`  (
  `stock_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `isbn` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '国际标准书号',
  `stock_qty` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '库存数量',
  `loglate` datetime(0) NULL DEFAULT NULL COMMENT '登记日期',
  PRIMARY KEY (`stock_id`) USING BTREE,
  UNIQUE INDEX `isbn`(`isbn`) USING BTREE,
  CONSTRAINT `stock_ibfk_1` FOREIGN KEY (`isbn`) REFERENCES `books` (`isbn`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 22 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '库存表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of stock
-- ----------------------------
INSERT INTO `stock` VALUES (1, '978-1', '55', '2021-01-02 12:01:02');
INSERT INTO `stock` VALUES (2, '978-2', '5525', '2022-01-02 12:01:02');
INSERT INTO `stock` VALUES (3, '978-3', '444', '2023-06-02 12:01:02');
INSERT INTO `stock` VALUES (4, '978-4', '551', '2021-12-02 12:01:02');
INSERT INTO `stock` VALUES (5, '978-5', '577', '2014-03-02 12:01:02');
INSERT INTO `stock` VALUES (6, '978-6', '594', '2012-01-09 12:01:02');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '用户名称',
  `address` char(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '地址',
  `True_name` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '真实姓名',
  `age` tinyint(3) UNSIGNED NOT NULL COMMENT '年龄',
  `sex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'M' COMMENT '(M:男 F:女)',
  `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电话',
  `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `created_at` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  PRIMARY KEY (`user_id`) USING BTREE,
  INDEX `username`(`username`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '小喜', '青青草原101', '喜羊羊', 19, 'M', '123456789', 'user1@example.com', '2024-06-26 15:37:46');
INSERT INTO `user` VALUES (2, '小美', '青青草原102', '美羊羊', 18, 'F', '852074195', 'user2@example.com', '2024-06-26 15:39:21');
INSERT INTO `user` VALUES (3, '小沸', '青青草原103', '沸羊羊', 20, 'M', '123445678', 'user3@example.com', '2024-06-26 15:38:44');
INSERT INTO `user` VALUES (4, '懒懒', '青青草原104', '懒羊羊', 16, 'M', '126345679', 'user4@example.com', '2024-06-26 15:38:51');
INSERT INTO `user` VALUES (5, '暖暖', '青青草原105', '暖羊羊', 25, 'F', '123945789', 'user5@example.com', '2024-06-26 15:39:28');
INSERT INTO `user` VALUES (6, '一棵草', '青青草原201', '慢羊羊', 50, 'M', '74108520', 'user6@example.com', '2024-06-26 15:39:14');

SET FOREIGN_KEY_CHECKS = 1;

完整注释DDL


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 首先创建user表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
	-- 	用户ID、主键自增
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
	-- 用户名称、不能为空
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '用户名称',
	-- 	地址、不能为空
  `address` char(100)  CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '地址',
	-- 真实姓名、不能为空
  `True_name` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '真实姓名',
	-- 电话、不能为空
  `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电话',
	-- 邮箱、可以为空
  `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
	-- 创建时间、可以为空
  `created_at` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建时间',
	-- 主键
  PRIMARY KEY (`user_id`) USING BTREE,
  -- 索引
  INDEX `username`(`username`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- 接着创建订购表（order），依赖于user表
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`  (
	-- 订购表编号、主键自增
  `order_id` int(19) NOT NULL AUTO_INCREMENT COMMENT '订购表编号',
	-- 用户名、不能为空
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
	-- 订购数量、可以为空
  `quantity` int(11) NULL DEFAULT NULL COMMENT '订购数量',
	-- 书籍编号、不能为空
  `book_id` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '书籍编号',
	-- 用户编号、不能为空
  `user_id` int(11) NOT NULL COMMENT '用户编号',
  	-- 发货编号、不能为空
  `ship_id` int(11) NOT NULL COMMENT '发货编号',
	-- 主键
  PRIMARY KEY (`order_id`) USING BTREE,
  -- 索引
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `username`(`username`) USING BTREE,
  INDEX `book_id`(`book_id`) USING BTREE,
  INDEX `ship_id`(`ship_id`) USING BTREE,
	-- 外键约束
  CONSTRAINT `order_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '订购表' ROW_FORMAT = Dynamic;

-- 然后创建书籍表（books），依赖于order表
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books`  (
	-- 书籍编号、主键自增
  `book_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '书籍编号',
	-- 国际标准书号、唯一且不能为空
  `isbn` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '国际标准书号',
	-- 书名、不能为空
  `bookname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '书名',
	-- 出版社、不能为空
  `bpress` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '出版社',
	-- 单价、可以为空
  `bprice` decimal(4, 2) NULL DEFAULT NULL COMMENT '单价',
	-- 作者、不能为空
  `bauthor` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '作者',
	-- 主键
  PRIMARY KEY (`book_id`) USING BTREE,
  INDEX `bookname`(`bookname`) USING BTREE,
  UNIQUE KEY `isbn` (`isbn`) USING BTREE,
	-- 外键约束
  CONSTRAINT `books_ibfk_1` FOREIGN KEY (`bookname`) REFERENCES `order` (`book_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '书籍表' ROW_FORMAT = Dynamic;

-- 接下来创建库存表（stock），依赖于books表
DROP TABLE IF EXISTS `stock`;
CREATE TABLE `stock`  (
	-- 编号、主键自增
  `stock_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
	-- ISBN、唯一索引不能为空
  `isbn` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '国际标准书号',
	-- 库存数量、可以为空
  `stock_qty` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '库存数量',
	-- 登记日期、可以为空
  `loglate` datetime(0) NULL DEFAULT NULL COMMENT '登记日期',
	-- 主键
  PRIMARY KEY (`stock_id`) USING BTREE,
	UNIQUE KEY `isbn` (`isbn`) USING BTREE,
	-- 外键约束
  CONSTRAINT `stock_ibfk_1` FOREIGN KEY (`isbn`) REFERENCES `books` (`isbn`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '库存表' ROW_FORMAT = Dynamic;


-- 创建 发货单表（shipped），依赖于order表
DROP TABLE IF EXISTS `shipped`;
CREATE TABLE `shipped`  (
	-- 发货单编号、主键自增
  `ship_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '发货单编号',
	-- 发货日期、可以为空
  `datetime` datetime(0) NULL DEFAULT NULL COMMENT '发货日期',
	-- 发货人、不能为空
  `ship_man` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '发货人',
	-- 主键
  PRIMARY KEY (`ship_id`) USING BTREE,
	-- 外键约束、关联订购表（order）表的order_id字段
  CONSTRAINT `shipped_ibfk_1` FOREIGN KEY (`ship_id`) REFERENCES `order` (`order_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '发货单表' ROW_FORMAT = Dynamic;


-- 创建用户留言表（leavewords），依赖于order表
CREATE TABLE `leavewords` (
	-- 用户留言表ID、主键自增
  `leavewords_id` int(11) NOT NULL AUTO_INCREMENT,
	-- 用户名、非空
  `user_id` int(20) NOT NULL COMMENT '用户名',
	-- 类别、可以为空
  `stype` varchar(10) DEFAULT NULL COMMENT '类别',
	-- 状态、可以为空
  `re_status` varchar(10) DEFAULT NULL COMMENT '状态',
	-- 主键
  PRIMARY KEY (`leavewords_id`),
  KEY `userName` (`user_id`),
  CONSTRAINT `leavewords_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `order` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建管理员表（administarators），依赖于user表
DROP TABLE IF EXISTS `administarators`;
CREATE TABLE `administarators` (
	-- 管理员ID、主键自增
  `admin_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '管理员编号',
	-- 用户名、非空
  `username` varchar(20) NOT NULL COMMENT '用户名',
	-- 密码、非空
  `password` varchar(255) NOT NULL COMMENT '密码',
	-- 主键
  PRIMARY KEY (`admin_id`) USING BTREE,
  KEY `username` (`username`) USING BTREE,
	-- 外键约束
  CONSTRAINT `administarators_ibfk_1` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='管理员表';

管理员表

书籍表

 

用户留言表

 

发货单表

库存表

 

用户表

 

 订购表

 

仙女欢
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值