sql实训

瑤間.

已于 2024-06-28 09:13:49 修改

阅读量142

点赞数 1

文章标签：数据库

于 2024-06-27 17:42:15 首次发布

本文链接：https://blog.csdn.net/2301_80422037/article/details/140019756

版权

1.ER实体关系图与数据库模型图绘制

2.DDL与DML语句

CREATE TABLE `administrators` (
  `admin_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '管理员ID，主键，自增',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '员密码',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '管理员创建时间',
  PRIMARY KEY (`admin_id`),
  UNIQUE KEY `username` (`username`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `administrators_ibfk_1` FOREIGN KEY (`created_at`) REFERENCES `users` (`created_at`) ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
CREATE TABLE `courier_companies` (
  `company_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '快递公司ID，主键，自增',
  `company_name` varchar(100) NOT NULL COMMENT '快递公司名称，唯一标识',
  `contact_info` varchar(255) DEFAULT NULL COMMENT '快递公司联系方式或地址',
  PRIMARY KEY (`company_id`),
  UNIQUE KEY `company_name` (`company_name`),
  CONSTRAINT `courier_companies_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `express_status_history` (`history_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
CREATE TABLE `express_orders` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '快递单ID，主键，自增',
  `user_id` int(11) NOT NULL COMMENT '用户ID，外键，关联users表的user_id',
  `company_id` int(11) NOT NULL COMMENT '快递公司ID，外键，关联courier_companies表的company_id',
  `tracking_number` varchar(50) NOT NULL COMMENT '快递单号',
  `sender_name` varchar(100) DEFAULT NULL COMMENT '寄件人姓名',
  `receiver_name` varchar(100) DEFAULT NULL COMMENT '收件人姓名',
  `send_address` varchar(255) DEFAULT NULL COMMENT '寄件地址',
  `receive_address` varchar(255) DEFAULT NULL COMMENT '收件地址',
  `status` varchar(50) NOT NULL COMMENT '快递状态',
  PRIMARY KEY (`order_id`),
  UNIQUE KEY `tracking_number` (`tracking_number`),
  KEY `user_id` (`user_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `express_orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
CREATE TABLE `express_status_history` (
  `history_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '历史记录ID，主键，自增',
  `order_id` int(11) NOT NULL COMMENT '快递单ID，外键，关联express_orders表的order_id',
  `status_id` int(11) NOT NULL COMMENT '快递状态ID，外键，关联express_statuses表的status_id',
  `status_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '状态变更时间',
  PRIMARY KEY (`history_id`),
  KEY `status_id` (`status_id`),
  KEY `order_id` (`order_id`),
  CONSTRAINT `express_status_history_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `express_statuses` (`status_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
CREATE TABLE `express_statuses` (
  `status_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '快递状态ID，主键，自增',
  `status_name` varchar(50) NOT NULL COMMENT '快递状态名称，唯一标识',
  PRIMARY KEY (`status_id`),
  UNIQUE KEY `status_name` (`status_name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
CREATE TABLE `system_logs` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '日志ID，主键，自增',
  `admin_id` int(11) NOT NULL COMMENT '管理员ID，外键，关联administrators表的admin_id',
  `operation` varchar(100) NOT NULL COMMENT '操作名称',
  `target` varchar(100) DEFAULT NULL COMMENT '操作目标',
  `details` text COMMENT '操作详情',
  `log_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '日志记录时间',
  PRIMARY KEY (`log_id`),
  KEY `admin_id` (`admin_id`),
  KEY `log_time` (`log_time`),
  KEY `operation` (`operation`),
  KEY `target` (`target`),
  CONSTRAINT `system_logs_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `administrators` (`admin_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `system_logs_ibfk_2` FOREIGN KEY (`target`) REFERENCES `courier_companies` (`company_name`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID，主键，自增',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `gender` enum('男','女') NOT NULL COMMENT '性别',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户创建时间',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `administrators` VALUES (1, 'admin', '123', 'admin@example.com', '1234567890', '2024-6-26 23:52:02');
INSERT INTO `administrators` VALUES (2, 'operator', '123', 'operator@example.com', '0987654321', '2024-6-26 23:52:02');
INSERT INTO `courier_companies` VALUES (1, '顺丰速运', '4008111111');
INSERT INTO `courier_companies` VALUES (2, '中通快递', '4008270270');
INSERT INTO `courier_companies` VALUES (3, '圆通速递', '95554');
INSERT INTO `express_orders` VALUES (1, 1, 1, '123456789012', '诸葛亮', '张三', '蜀国 成都', '魏国 许昌', '待揽收');
INSERT INTO `express_orders` VALUES (2, 2, 2, '234567890123', '关羽', '李四', '蜀国 荆州', '吴国 建业', '运输中');
INSERT INTO `express_orders` VALUES (3, 3, 3, '345678901234', '曹操', '王五', '魏国 洛阳', '蜀国 成都', '已签收');
INSERT INTO `express_orders` VALUES (4, 1, 2, '456789012345', '诸葛亮', '赵六', '蜀国 成都', '吴国 柴桑', '已退回');
INSERT INTO `express_orders` VALUES (5, 2, 1, '567890123456', '刘备', '孙七', '蜀国 汉中', '魏国 邺城', '待揽收');
INSERT INTO `express_orders` VALUES (6, 3, 3, '678901234567', '曹操', '周八', '魏国 许昌', '蜀国 绵竹', '运输中');
INSERT INTO `express_status_history` VALUES (1, 1, 2, '2024-6-26 23:52:02');
INSERT INTO `express_status_history` VALUES (2, 2, 3, '2024-6-26 23:52:02');
INSERT INTO `express_status_history` VALUES (3, 3, 4, '2024-6-26 23:52:02');
INSERT INTO `express_status_history` VALUES (4, 4, 2, '2024-6-26 23:52:02');
INSERT INTO `express_statuses` VALUES (3, '已签收');
INSERT INTO `express_statuses` VALUES (4, '已退回');
INSERT INTO `express_statuses` VALUES (1, '待揽收');
INSERT INTO `express_statuses` VALUES (2, '运输中');
INSERT INTO `users` VALUES (1, '诸葛亮', '123', 'zhugeliang@example.com', '13800138000', '男', '2024-6-26 23:52:02');
INSERT INTO `users` VALUES (2, '刘备', '123', 'liubei@example.com', '13900139000', '男', '2024-6-26 23:52:02');
INSERT INTO `users` VALUES (3, '曹操', '123', 'caocao@example.com', '13700137000', '男', '2024-6-26 23:52:02');
INSERT INTO `users` VALUES (4, '孙权', '123', 'sunquan@example.com', '13600136000', '男', '2024-6-26 23:52:02');
INSERT INTO `system_logs` VALUES (1, 1, '运送', '顺丰速运', NULL, '2024-6-27 17:26:27');
INSERT INTO `system_logs` VALUES (2, 2, '运送', '中通快递', NULL, '2024-6-27 17:31:07');
INSERT INTO `system_logs` VALUES (3, 2, '运送', '圆通速递', NULL, '2024-6-27 17:32:05');