数据库第二次作业

创建教师表

1.教师表

 create table teacher(
    no char(4) comment"教师号" primary key,
    name varchar(10) comment"教师姓名" not null,
    prof varchar(20) comment"职称" not null default"助教",
    sal int(2) comment"工资"not null,
    comm smallint(2) comment"岗位津贴"
     );

 

 2.学生表

create table student(
    no char(4) comment"学生号" primary key,
    name varchar(10) comment"学生姓名" not null,
    age tinyint(1) comment"年龄" not null,
    dept varchar(20) comment"系名" not null default"计算机系"
    );

3.课程表

 create table course(

 no char(4) comment"课程号" primary key,

 name varchar(20) comment"课程名" not null unique,

 class_hours int(2) comment"课时数" default"45"

）；

4. 授课表

create table school_teaching(
    id int(4) comment"序号" primary key auto_increment,
    course_no char(4) comment"课程号" not null,
    teacher_no char(4) comment"教师号" not null,
    week int(2) comment "周数" default"15",
    class_num varchar(10) comment"教师号",
    constraint fk_course_no foreign key (course_no) references course(no),
    constraint fk_teacher_no foreign key (teacher_no) references teacher(no)
    )；

 5.成绩表

 create table grade(
    course_no char(4) comment"课程号" not null,
    student_no char(4) comment"学生号" not null,
    score float comment"成绩" not null default"60",
    primary key(course_no,student_no)
    );