数据库第二次作业---表的约束

创建教师表

输入 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"岗位津贴"
    -> );

 

 创建学生表 student

 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"计算机系"
    -> );

查看

 创建课程表

 create table course(
    -> no char(4) comment"课程号" primary key,
    -> name varchar(20) comment"课程名" not null unique,
    -> class_hours int(2) comment"课时数" default"45"
    -> );
Query OK, 0 rows affected, 1 warning (0.03 sec)
 

 

查看

 创建授课表

输入

 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)
    -> );

查看

 创建成绩表

输入 create table chengweiqiang_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)
    -> );