【MySQL】实战示例建表语句

【MySQL】实战示例建表语句

1. 建表语句1

DROP TABLE IF EXISTS `meituan_source_data`;
CREATE TABLE `meituan_source_data` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表自增主键',
  `data_day` int unsigned NOT NULL DEFAULT '0' COMMENT '数据日期',
  `data_type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '数据类型 10-订单 11-订单详情 20-账单 21-账单关联订单 22-账单关联订单详情',
  `data_id` varchar(64) NOT NULL DEFAULT '' COMMENT '数据唯一标识',
  `data_content` longtext NOT NULL COMMENT '数据内容',
  `created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
  `updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`data_day`,`data_type`,`data_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='美团原始数据表';

DROP TABLE IF EXISTS `meituan_order`;
CREATE TABLE `meituan_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表自增主键',
  `user_id` varchar(24) NOT NULL DEFAULT '' COMMENT '员工id',
  `user_name` varchar(64) NOT NULL DEFAULT '' COMMENT '员工姓名',
  `user_mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '员工手机号',
  `user_employee_no` varchar(255) NOT NULL DEFAULT '' COMMENT '员工工号',
  `user_employee_email` varchar(255) NOT NULL DEFAULT '' COMMENT '员工邮箱',
  `user_dept_path` varchar(255) NOT NULL DEFAULT '' COMMENT '员工部门id path',
  `user_dept_name_path` varchar(255)  NOT NULL DEFAULT '' COMMENT '员工部门name path',
  `user_city` varchar(64) NOT NULL DEFAULT '' COMMENT '员工城市',
  `company_subject` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '公司主体 1-苏州斯科半导体有限公司 2-北京罗克维尔斯科技有限公司',
  `order_time` int unsigned NOT NULL DEFAULT '0' COMMENT '下单时间戳,单位秒',
  `sqt_biz_order_id` varchar(64) NOT NULL DEFAULT '' COMMENT '订单编号',
  `orig_order_id` varchar(64) NOT NULL DEFAULT '' COMMENT '业务订单号',
  `order_type` int unsigned NOT NULL DEFAULT '0' COMMENT '订单类型',
  `order_status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '订单状态',
  `province_name` varchar(32) NOT NULL DEFAULT '' COMMENT '消费省',
  `city_name` varchar(32) NOT NULL DEFAULT '' COMMENT '消费城市',
  `shop_name` varchar(255) NOT NULL DEFAULT '' COMMENT '商户名称',
  `shop_address` varchar(255) NOT NULL DEFAULT '' COMMENT '商户地址',
  `recipient_name` varchar(64) NOT NULL DEFAULT '' COMMENT '收餐人姓名',
  `recipient_phone` varchar(20) NOT NULL DEFAULT '' COMMENT '收餐人手机号',
  `recipient_address` varchar(255) NOT NULL DEFAULT '' COMMENT '配送地址',
  `order_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '消费金额',
  `ent_pay_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '企业支付金额',
  `staff_pay_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '个人支付金额',
  `ent_consumption_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '企业消费结算金额',
  `total_refund_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '退款金额',
  `ent_refund_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '企业退款金额',
  `staff_refund_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '个人退款金额',
  `real_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '实付金额',
  `latest_refund_time` int unsigned NOT NULL DEFAULT '0' COMMENT '最后退款时间戳,单位秒',
  `create_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '同步时间',
  `update_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`sqt_biz_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='美团订单表';

DROP TABLE IF EXISTS `meituan_bill`;
CREATE TABLE `meituan_bill` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表自增主键',
  `bill_period` int unsigned NOT NULL DEFAULT '0' COMMENT '账期',
  `start_day` int unsigned NOT NULL DEFAULT '0' COMMENT '账期开始日期',
  `end_day` int unsigned NOT NULL DEFAULT '0' COMMENT '账期结束日期',
  `pay_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '账单总金额',
  `refund_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '退款总金额',
  `total_real_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '实付总金额',
  `service_fee_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '服务费总金额',
  `total_pay_amount` varchar(24) NOT NULL COMMENT '总计支付金额',
  `company_subject` tinyint NOT NULL DEFAULT '0' COMMENT '公司主体 1-苏州斯科半导体有限公司 2-北京罗克维尔斯科技有限公司',
  `invoice_title` varchar(255) NOT NULL DEFAULT '' COMMENT '发票抬头',
  `settlement_account_id` varchar(24) NOT NULL DEFAULT '' COMMENT '结算账户ID',
  `ent_pay_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '企业支付金额',
  `ent_refund_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '企业退款金额',
  `staff_pay_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '员工支付金额',
  `staff_refund_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '员工退款金额',
  `bill_start_account_balance` varchar(24) NOT NULL DEFAULT '0' COMMENT '期初账户余额',
  `bill_account_recharge_balance` varchar(24) NOT NULL DEFAULT '0' COMMENT '本期账户充值金额',
  `enterprise_settlement_balance` varchar(24) NOT NULL DEFAULT '0' COMMENT '企业消费结算金额',
  `bill_end_account_balance` varchar(24) NOT NULL DEFAULT '0' COMMENT '期末账户余额',
  `confirm_user_id` varchar(24) NOT NULL DEFAULT '' COMMENT '账单确认人id',
  `confirm_user_name` varchar(64) NOT NULL DEFAULT '' COMMENT '账单确认人名称',
  `create_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '同步时间',
  `update_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`bill_period`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='美团账单表';

DROP TABLE IF EXISTS `meituan_bill_order`;
CREATE TABLE `meituan_bill_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表自增主键',
  `user_id` varchar(24) NOT NULL DEFAULT '' COMMENT '员工id',
  `user_name` varchar(64) NOT NULL DEFAULT '' COMMENT '员工姓名',
  `user_mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '员工手机号',
  `user_employee_no` varchar(255) NOT NULL DEFAULT '' COMMENT '员工工号',
  `user_employee_email` varchar(255) NOT NULL DEFAULT '' COMMENT '员工邮箱',
  `user_dept_path` varchar(255) NOT NULL DEFAULT '' COMMENT '员工部门id path',
  `user_dept_name_path` varchar(255)  NOT NULL DEFAULT '' COMMENT '员工部门name path',
  `user_city` varchar(64) NOT NULL DEFAULT '' COMMENT '员工城市',
  `company_subject` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '公司主体 1-苏州斯科半导体有限公司 2-北京罗克维尔斯科技有限公司',
  `order_time` int unsigned NOT NULL DEFAULT '0' COMMENT '下单时间戳,单位秒',
  `sqt_biz_order_id` varchar(64) NOT NULL DEFAULT '' COMMENT '订单编号',
  `orig_order_id` varchar(64) NOT NULL DEFAULT '' COMMENT '业务订单号',
  `order_type` int unsigned NOT NULL DEFAULT '0' COMMENT '订单类型',
  `order_status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '订单状态',
  `province_name` varchar(32) NOT NULL DEFAULT '' COMMENT '消费省',
  `city_name` varchar(32) NOT NULL DEFAULT '' COMMENT '消费城市',
  `shop_name` varchar(255) NOT NULL DEFAULT '' COMMENT '商户名称',
  `shop_address` varchar(255) NOT NULL DEFAULT '' COMMENT '商户地址',
  `recipient_name` varchar(64) NOT NULL DEFAULT '' COMMENT '收餐人姓名',
  `recipient_phone` varchar(20) NOT NULL DEFAULT '' COMMENT '收餐人手机号',
  `recipient_address` varchar(255) NOT NULL DEFAULT '' COMMENT '配送地址',
  `order_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '消费金额',
  `ent_pay_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '企业支付金额',
  `staff_pay_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '个人支付金额',
  `ent_consumption_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '企业消费结算金额',
  `total_refund_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '退款金额',
  `ent_refund_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '企业退款金额',
  `staff_refund_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '个人退款金额',
  `real_amount` varchar(24) NOT NULL DEFAULT '' COMMENT '实付金额',
  `latest_refund_time` int unsigned NOT NULL DEFAULT '0' COMMENT '最后退款时间戳,单位秒',
  `create_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '同步时间',
  `update_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`sqt_biz_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='美团账单关联的订单表';

2. 建表语句2

CREATE TABLE `knowledge_point` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键id',
  `title` varchar(64) NOT NULL DEFAULT '' COMMENT '名称',
  `owner_id` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者id',
  `auth_role_id` int(11) NOT NULL DEFAULT '0' COMMENT '创建者角色id',
  `created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
  `updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`title`)
) ENGINE=InnoDB COMMENT='知识点';

3.建表语句3

-- DROP TABLE IF EXISTS `project`;
CREATE TABLE `project` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键id',
  `dataset_check_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '项目唯一标识',
  `title` varchar(255) NOT NULL DEFAULT '' COMMENT '项目名',
  `training_type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '培训分类',
  `principals_uids` varchar(255) NOT NULL DEFAULT '' COMMENT '英文逗号分隔的负责人飞书用户id',
  `principals_dept_id_paths` varchar(1024) NOT NULL DEFAULT '' COMMENT '英文逗号分隔的负责人部门id path',
  `join_rule` varchar(2048) NOT NULL DEFAULT '' COMMENT '加入规则,json',
  `auto_join_rule` varchar(2048) NOT NULL DEFAULT '' COMMENT '自动加入规则,json',
  `state` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '项目状态',
  `created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间戳,单位秒',
  `updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间戳,单位秒',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`dataset_check_id`)
) ENGINE=InnoDB COMMENT='项目表';

-- DROP TABLE IF EXISTS `project_student`;
CREATE TABLE `project_student` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键id',
  `user_id` varchar(64) NOT NULL DEFAULT '' COMMENT '飞书用户id',
  `dept_id_path` varchar(255) NOT NULL DEFAULT '' COMMENT '用户部门id path',
  `employment_state` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '在职状态',
  `entry_ts` int unsigned NOT NULL DEFAULT 0 COMMENT '入职时间戳,单位秒',
  `mentor_user_id` varchar(64) NOT NULL DEFAULT '' COMMENT '导师飞书用户id',
  `project_dataset_check_id` bigint unsigned NOT NULL DEFAULT 0 COMMENT '项目唯一标识',
  `training_start_ts` int unsigned NOT NULL DEFAULT 0 COMMENT '培训开始时间戳,单位秒',
  `training_end_ts` int unsigned NOT NULL DEFAULT 0 COMMENT '培训结束时间戳,单位秒',
  `training_state` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '培训状态',
  `camp_state` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '出营状态',
  `study_state` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '学习状态',
  `created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间戳,单位秒',
  `updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间戳,单位秒',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`project_dataset_check_id`, `user_id`)
) ENGINE=InnoDB COMMENT='项目学员表';

-- DROP TABLE IF EXISTS `project_student_summary`;
CREATE TABLE `project_student_summary` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键id',
  `user_id` varchar(64) NOT NULL DEFAULT '' COMMENT '飞书用户id',
  `project_dataset_check_id` bigint unsigned NOT NULL DEFAULT 0 COMMENT '项目唯一标识',
  `project_summary` longtext NOT NULL COMMENT '项目json格式的学习汇总数据',
  `training_result` text NOT NULL COMMENT '培训结果,json',
  `created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间戳,单位秒',
  `updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间戳,单位秒',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`project_dataset_check_id`, `user_id`)
) ENGINE=InnoDB COMMENT='项目学员统计表';

-- DROP TABLE IF EXISTS `project_student_task`;
CREATE TABLE `project_student_task` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键id',
  `user_id` varchar(64) NOT NULL DEFAULT '' COMMENT '飞书用户id',
  `dept_id_path` varchar(255) NOT NULL DEFAULT '' COMMENT '用户部门id path',
  `mentor_user_id` varchar(64) NOT NULL DEFAULT '' COMMENT '导师飞书用户id',
  `project_dataset_check_id` bigint unsigned NOT NULL DEFAULT 0 COMMENT '项目唯一标识',
  `project_section_key` varchar(128) NOT NULL DEFAULT '' COMMENT '阶段唯一标识',
  `project_section_task_key` varchar(128) NOT NULL DEFAULT '' COMMENT '任务唯一标识',
  `detail_type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '任务类型',
  `task_name` varchar(64) NOT NULL DEFAULT '' COMMENT '任务名称',
  `detail` longtext NOT NULL COMMENT '任务详情',
  `state` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '任务状态',
  `created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间戳,单位秒',
  `updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间戳,单位秒',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`project_dataset_check_id`, `project_section_key`, `project_section_task_key`, `user_id`)
) ENGINE=InnoDB COMMENT='项目学员任务表';

-- DROP TABLE IF EXISTS `project_student_task_log`;
CREATE TABLE `project_student_task_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键id',
  `user_id` varchar(64) NOT NULL DEFAULT '' COMMENT '飞书用户id',
  `project_dataset_check_id` bigint unsigned NOT NULL DEFAULT 0 COMMENT '项目唯一标识',
  `project_section_key` varchar(128) NOT NULL DEFAULT '' COMMENT '阶段唯一标识',
  `project_section_task_key` varchar(128) NOT NULL DEFAULT '' COMMENT '任务唯一标识',
  `detail_type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '任务类型',
  `detail` longtext NOT NULL COMMENT '任务详情',
  `created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间戳,单位秒',
  `updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间戳,单位秒',
  PRIMARY KEY (`id`),
  KEY `uniq_1` (`project_dataset_check_id`, `project_section_key`, `project_section_task_key`, `user_id`)
) ENGINE=InnoDB COMMENT='项目学员任务学习记录表';
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

boy快快长大

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值