-- 创建数据库
CREATE DATABASE IF NOT EXISTS wkbishe;
USE wkbishe;
-- 学生信息表
CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
grade ENUM('primary', 'middle', 'high') NOT NULL,
learning_style VARCHAR(20) COMMENT '视觉/听觉/动手',
interest_tags JSON COMMENT '存储兴趣标签数组 ["数学","编程"]',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 学科知识体系表
CREATE TABLE subjects (
subject_id INT AUTO_INCREMENT PRIMARY KEY,
subject_name VARCHAR(20) NOT NULL COMMENT '数学/物理/编程',
grade_level ENUM('p1-p6','m1-m3','h1-h3') NOT NULL,
difficulty TINYINT DEFAULT 1 COMMENT '1-5级难度'
);
-- 知识点表
CREATE TABLE knowledge_points (
point_id INT AUTO_INCREMENT PRIMARY KEY,
subject_id INT NOT NULL,
point_name VARCHAR(100) NOT NULL COMMENT '如：分数运算',
pre_requisite_id INT COMMENT '前置知识点',
core_concept TEXT,
FOREIGN KEY (subject_id) REFERENCES subjects(subject_id),
FOREIGN KEY (pre_requisite_id) REFERENCES knowledge_points(point_id)
);
-- 学生能力评估表
CREATE TABLE ability_assessments (
assessment_id INT AUTO_LOGIC_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
subject_id INT NOT NULL,
logical_thinking TINYINT COMMENT '1-5分',
memory_ability TINYINT,
spatial_imagination TINYINT,
assessment_date DATE,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
);
-- 学习路径表
CREATE TABLE learning_paths (
path_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
subject_id INT NOT NULL,
current_point_id INT COMMENT '当前学习节点',
progress FLOAT DEFAULT 0.0 COMMENT '0-1进度值',
generated_time DATETIME,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (subject_id) REFERENCES subjects(subject_id),
FOREIGN KEY (current_point_id) REFERENCES knowledge_points(point_id)
);
-- 学习路径详情表
CREATE TABLE learning_path_details (
detail_id INT AUTO_INCREMENT PRIMARY KEY,
path_id INT NOT NULL,
point_id INT NOT NULL,
order_num INT NOT NULL COMMENT '路径顺序',
estimated_time INT COMMENT '分钟',
completed BOOLEAN DEFAULT false,
FOREIGN KEY (path_id) REFERENCES learning_paths(path_id),
FOREIGN KEY (point_id) REFERENCES knowledge_points(point_id)
);
-- 知识点关系表（图谱结构）
CREATE TABLE knowledge_point_relations (
relation_id INT AUTO_INCREMENT PRIMARY KEY,
from_point INT NOT NULL,
to_point INT NOT NULL,
relation_type ENUM('prerequisite','correlated'),
weight FLOAT DEFAULT 1.0,
FOREIGN KEY (from_point) REFERENCES knowledge_points(point_id),
FOREIGN KEY (to_point) REFERENCES knowledge_points(point_id)
);
-- 交叉学科表
CREATE TABLE cross_subject (
cross_id INT AUTO_INCREMENT PRIMARY KEY,
main_subject INT NOT NULL,
related_subject INT NOT NULL,
relation_description TEXT,
FOREIGN KEY (main_subject) REFERENCES subjects(subject_id),
FOREIGN KEY (related_subject) REFERENCES subjects(subject_id)
);
-- 创建索引
CREATE INDEX idx_student_grade ON students(grade);
CREATE INDEX idx_knowledge_subject ON knowledge_points(subject_id);
CREATE INDEX idx_path_student ON learning_paths(student_id);报错1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTO_LOGIC_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
subject_id ' at line 3
本文介绍如何使用SQL语句创建包含自增主键的表格，并详细解释了各个字段的作用及注释说明。
扫一扫
2234
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
