MySQL — 练习题

 

一. 表关系

1. 创建如下表，并创建相关约束.

 

# 创建班级表
create table class (
    cid int primary key not null auto_increment,
    caption varchar(20) not null
);

# 创建老师表
create table teacher (
    tid int primary key not null auto_increment,
    tname varchar(20) not null
);

# 创建学生表
create table student (
    sid int primary key not null auto_increment,
    sname varchar(20) not null,
    gender enum("男", "女"),
    class_id int not null,
    constraint fk_student_class foreign key(class_id) references class(cid)
    on delete cascade
    on update cascade
);

# 创建课程表
create table course (
    cid int primary key not null auto_increment,
    cname varchar(20) not null,
    teach_id int not null,
    constraint fk_course_teacher foreign key(teach_id) references teacher(tid)
    on delete cascade
    on update cascade
);

# 创建成绩表
create table score(
    sid int primary key not null auto_increment,
    student_id int not null,
    course_id int not null,
    number int not null,
    constraint fk_score_student foreign key(student_id) references student(sid)
    on delete cascade
    on update cascade,
    constraint fk_score_course foreign key(course_id) references course(cid)
    on delete cascade
    on update cascade
);

创建表 -- 参考