mysql建表语句

最新推荐文章于 2025-09-02 10:54:38 发布

原创最新推荐文章于 2025-09-02 10:54:38 发布 · 289 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #p2p #sql

本文介绍了如何在数据库中创建grade、class、student和score表，并通过外键约束实现表之间的关联。重点在于数据模型设计和SQL操作，适合数据库管理和开发人员参考。

drop table if exists score;
drop table if exists grade;
drop table if exists class;
drop table if exists student;

create table grade
(
id bigint not null primary key auto_increment,
grade_no varchar(16) default null
) engine = InnoDB default charset = utf8;

create table class
(
id bigint not null primary key auto_increment,
class_no varchar(16) default null comment '班级号'
) engine = InnoDB default charset = utf8;

create table student
(
student_id bigint not null primary key auto_increment,
student_name varchar(100) null ,
id_card varchar(32) null,
sex varchar(2) null,
grade_id bigint not null comment '年级id',
class_id bigint not null comment '班级id',
createtime timestamp null,
modifytime timestamp null,
version varchar(1) default 1 not null comment '版本号',
constraint fk_student_class foreign key (class_id) references class (id),
constraint fk_student_grade foreign key (grade_id) references grade (id)
) ENGINE = InnoDB default charset = utf8;

create table score
(
exam_id bigint not null primary key auto_increment,
exam_information varchar(100) null ,
student_id bigint not null,
student_name varchar(32) null,
chinese_score int null comment '语文成绩',
math_score int null comment '数学成绩',
english_score int null comment '英语成绩',
composite_score int null comment '综合成绩',
total int null comment '总成绩',
createtime timestamp null,
modifytime timestamp null,
version varchar(1) default 1 not null comment '版本号',
constraint fk_score_student foreign key (student_id) references student (student_id)
) ENGINE = InnoDB default charset = utf8;