MySQL数据库创建班级表代码 MySQL如何创建数据库班级表代码总汇

-- 创建数据库（如果数据库不存在）
CREATE DATABASE IF NOT EXISTS `school`;

-- 使用数据库
USE `school`;

-- 创建班级表
CREATE TABLE IF NOT EXISTS `classes` (
  `class_id` INT UNSIGNED AUTO_INCREMENT COMMENT '班级ID，自增',
  `class_name` VARCHAR(50) NOT NULL COMMENT '班级名称',
  `grade` INT UNSIGNED NOT NULL COMMENT '年级',
  `major` VARCHAR(50) COMMENT '专业',
  `teacher_id` INT UNSIGNED COMMENT '班主任ID，关联教师表',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`class_id`),
  UNIQUE KEY `class_name` (`class_name`) -- 班级名称唯一
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='班级表';

-- 添加外键约束（如果需要关联教师表）
ALTER TABLE `classes`
ADD CONSTRAINT `fk_classes_teacher`
FOREIGN KEY (`teacher_id`)
REFERENCES `teachers` (`teacher_id`);

class_id

INT UNSIGNED AUTO_INCREMENT

class_name

VARCHAR(50)

NOT NULL

grade

INT UNSIGNED

TIMESTAMP

class_id

class_name

grade

major

-- 为年级添加索引
CREATE INDEX idx_grade ON classes (grade);

-- 为专业添加索引
CREATE INDEX idx_major ON classes (major);

WHERE

teacher_id

teachers

teacher_id

-- 创建教师表（示例）
CREATE TABLE IF NOT EXISTS `teachers` (
  `teacher_id` INT UNSIGNED AUTO_INCREMENT COMMENT '教师ID，自增',
  `teacher_name` VARCHAR(50) NOT NULL COMMENT '教师姓名',
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='教师表';

JOIN

SELECT c.class_name, t.teacher_name
FROM classes c
JOIN teachers t ON c.teacher_id = t.teacher_id
WHERE c.class_name = '三年级一班';

VARCHAR

ENUM

SET

TEXT

LONGTEXT