46 lines
3.2 KiB
SQL
46 lines
3.2 KiB
SQL
-- 创建词汇书表
|
|
CREATE TABLE IF NOT EXISTS `ai_vocabulary_books` (
|
|
`id` VARCHAR(36) NOT NULL COMMENT '词汇书ID',
|
|
`name` VARCHAR(200) NOT NULL COMMENT '词汇书名称',
|
|
`description` TEXT COMMENT '词汇书描述',
|
|
`category` VARCHAR(100) NOT NULL COMMENT '分类',
|
|
`level` ENUM('beginner','elementary','intermediate','advanced','expert') NOT NULL COMMENT '难度级别',
|
|
`total_words` INT DEFAULT 0 COMMENT '总单词数',
|
|
`cover_image` VARCHAR(500) COMMENT '封面图片URL',
|
|
`icon` VARCHAR(255) COMMENT '图标',
|
|
`color` VARCHAR(7) COMMENT '主题色',
|
|
`is_system` BOOLEAN DEFAULT TRUE COMMENT '是否系统词汇书',
|
|
`is_active` BOOLEAN DEFAULT TRUE COMMENT '是否启用',
|
|
`sort_order` INT DEFAULT 0 COMMENT '排序',
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_category` (`category`),
|
|
KEY `idx_level` (`level`),
|
|
KEY `idx_is_system` (`is_system`, `is_active`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='词汇书表';
|
|
|
|
-- 创建词汇书单词关联表
|
|
CREATE TABLE IF NOT EXISTS `ai_vocabulary_book_words` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '关联ID',
|
|
`book_id` VARCHAR(36) NOT NULL COMMENT '词汇书ID',
|
|
`vocabulary_id` VARCHAR(36) NOT NULL COMMENT '词汇ID',
|
|
`sort_order` INT DEFAULT 0 COMMENT '排序',
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_book_vocabulary` (`book_id`, `vocabulary_id`),
|
|
KEY `idx_book_id` (`book_id`),
|
|
KEY `idx_vocabulary_id` (`vocabulary_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='词汇书单词关联表';
|
|
|
|
-- 插入系统词汇书数据
|
|
INSERT INTO `ai_vocabulary_books` (`id`, `name`, `description`, `category`, `level`, `total_words`, `icon`, `color`, `is_system`, `is_active`, `sort_order`) VALUES
|
|
('cet4_core_2500', '大学英语四级核心词汇', '涵盖CET-4考试核心词汇2500个', 'CET-4核心词汇', 'intermediate', 2500, '📚', '#4CAF50', TRUE, TRUE, 1),
|
|
('cet6_core_3000', '大学英语六级核心词汇', '涵盖CET-6考试核心词汇3000个', 'CET-6核心词汇', 'advanced', 3000, '📖', '#2196F3', TRUE, TRUE, 2),
|
|
('toefl_high_3500', '托福高频词汇', '托福考试高频词汇3500个', 'TOEFL高频词汇', 'advanced', 3500, '🎓', '#FF9800', TRUE, TRUE, 3),
|
|
('ielts_high_3500', '雅思高频词汇', '雅思考试高频词汇3500个', 'IELTS高频词汇', 'advanced', 3500, '🌟', '#9C27B0', TRUE, TRUE, 4),
|
|
('primary_core_1000', '小学英语核心词汇', '小学阶段必备核心词汇1000个', '小学核心词汇', 'beginner', 1000, '🎈', '#E91E63', TRUE, TRUE, 5),
|
|
('junior_core_1500', '初中英语核心词汇', '初中阶段必备核心词汇1500个', '初中核心词汇', 'elementary', 1500, '📝', '#00BCD4', TRUE, TRUE, 6),
|
|
('senior_core_3500', '高中英语核心词汇', '高中阶段必备核心词汇3500个', '高中核心词汇', 'intermediate', 3500, '📕', '#FF5722', TRUE, TRUE, 7),
|
|
('business_core_1000', '商务英语核心词汇', '商务场景常用核心词汇1000个', '商务英语', 'intermediate', 1000, '💼', '#607D8B', TRUE, TRUE, 8);
|