SET character_set_client = gbk ;
SET character_set_connection = gbk ;
SET character_set_database = gbk ;
SET character_set_results = gbk ;
SET character_set_server = gbk ;
SET collation_connection = gbk_chinese_ci ;
SET collation_database = gbk_chinese_ci ;
SET collation_server = gbk_chinese_ci ;

create database student;

use student;

create table classes(
class_id int auto_increment primary key,
class_no char(10) not NULL unique,
class_name char(20) not NULL
);

create table course(
course_id int auto_increment primary key,
course_no char(10) not NULL unique,
course_name char(20) not NULL
);

create table student(
student_id int auto_increment primary key,
student_no char(10) not NULL unique,
student_name char(10) not NULL,
class_id int,
constraint FK_student_class foreign key (class_id) references classes(class_id) 
);

create table teacher(
teacher_id int auto_increment primary key,
teacher_no char(10) not NULL unique,
teacher_name char(10) not NULL,
class_id int unique,
constraint FK_teacher_class foreign key (class_id) references classes(class_id) 
);

create table score(
score_id int auto_increment primary key,
student_id int not NULL,
course_id int not NULL,
grade int, 
constraint FK_sore_student foreign key (student_id) references student(student_id),
constraint FK_score_course foreign key (course_id) references course(course_id) 
);

insert into classes values(NULL,'10maths','10数学');
insert into classes values(NULL,'10english','10英语');
insert into classes values(NULL,'10chinese','10语文');
insert into student values(NULL,'2010010101','张三',1);
insert into student values(NULL,'2010010102','李四',1);
