student.sql

drop database if exists student;
create database student;
use student;


create table tb_class(
id int primary key auto_increment,
class_name varchar(18)
);
#一对多用主外键关联
#证明多对多关系不能再tb_student表用主外键关联
create table tb_student(
id int primary key auto_increment,
stu_name varchar(18),
sex varchar(6),
age int,
class_id int,
#course_id int,
constraint tb_student_class_java foreign key (class_id) references tb_class(id),
#constraint tb_course_class_java foreign key (course_id) references tb_course(id)
);


#学生和课程是多对多关系
create table tb_course(
id int primary key auto_increment,
course_name varchar(18),
score int
);
#多对多关系,建立一张中间表处理关联关系
create table tb_student_course(
id int primary key auto_increment,
student_id int,
course_id int,
gride int,
unique (student_id,course_id),
constraint tb_student_java foreign key (student_id) references tb_student(id),
constraint tb_course_java foreign key (course_id) references tb_course(id)
);