MySQL 数据库

-- 学生表 CREATE TABLE students ( student_id INT PRIMARY KEY AUTO_INCREMENT, student_no VARCHAR(20) UNIQUE NOT NULL, name VARCHAR(50) NOT NULL, gender ENUM('男', '女'), birth_date DATE, enrollment_date DATE ); -- 课程表 CREATE TABLE courses ( course_id INT PRIMARY KEY AUTO_INCREMENT, course_code VARCHAR(20) UNIQUE NOT NULL, course_name VARCHAR(100) NOT NULL, credit INT NOT NULL, teacher_id INT ); -- 教师表 CREATE TABLE teachers ( teacher_id INT PRIMARY KEY AUTO_INCREMENT, teacher_no VARCHAR(20) UNIQUE NOT NULL, name VARCHAR(50) NOT NULL, title VARCHAR(50), department VARCHAR(100) ); -- 选课关系表（多对多关系） CREATE TABLE enrollments ( enrollment_id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL, course_id INT NOT NULL, enrollment_date DATE DEFAULT (CURRENT_DATE), score DECIMAL(5,2) CHECK (score >= 0 AND score <= 100), FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id), UNIQUE KEY uk_student_course (student_id, course_id) ); -- 添加外键到课程表 ALTER TABLE courses ADD CONSTRAINT fk_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id);

-- 1. 使用有意义的表名和列名 -- 2. 使用适当的整数类型做主键 -- 3. 为常用查询字段创建索引 -- 4. 避免NULL值，使用DEFAULT -- 5. 使用ENUM或SET替代字符串常量 -- 6. 大文本字段单独存放 -- 7. 考虑未来扩展性 -- 8. 记录创建和更新时间 -- 示例：完整的学生信息表 CREATE TABLE student_info ( id INT PRIMARY KEY AUTO_INCREMENT, student_code VARCHAR(20) UNIQUE NOT NULL, name VARCHAR(50) NOT NULL, id_card VARCHAR(18) UNIQUE, gender TINYINT DEFAULT 0 COMMENT '0:未知 1:男 2:女', birth_date DATE, phone VARCHAR(20), email VARCHAR(100), address VARCHAR(200), status TINYINT DEFAULT 1 COMMENT '1:在读 2:毕业 3:休学', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_name (name), INDEX idx_status (status), INDEX idx_phone (phone) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;