简易教务管理系统

E-R图：

创建数据库

create database EducationalMS character set utf8;
这是创建的所有表：

班级表

create table class_table(
class_no int primary key auto_increment,
class_name varchar(10),
speciality varchar(10),
count int
)engine = InnoDB character set gbk;

插入数据并查询

学生表

drop table if exists stu_table;
create table stu_table(
-- stu_no char(10) primary key auto_increment, #自动增长的主键应该使用整型
stu_no char(10) primary key,
stu_name varchar(10),
stu_gender varchar(10),
stu_age tinyint,
class_no int,
foreign key stu_table(class_no) references class_table(class_no) on update cascade
)engine = InnoDB character set gbk;

插入数据并查询

当向学生表中添加数据时，同时班级表中的总人数也会随之改变。总人数计算出来后的传递？用触发器。
drop trigger if exists count_total;
create trigger count_total after insert on stu_table #添加学生时，更新班级表中的总人数
for each row
update class_table set count = (select count(class_no) from stu_table where class_table.class_no = class_no);

drop trigger if exists count_total1;
create trigger count_total1 after delete on stu_table #删除学生时，更新班级表中的总人数
for each row
update class_table set count = (select count(class_no) from stu_table where class_table.class_no = class_no);

例如：
insert into stu_table values('1604122005', '李明', '男', 23, 2);

课程表

create table course_table(
course_no int primary key auto_increment,
course_name varchar(20),
course_period tinyint,
course_credit tinyint
)engine = InnoDB character set gbk;

插入数据并查询

选课表

drop table if exists select_table;
create table select_table(
stu_no char(10),
course_no int,
grade tinyint,
primary key(stu_no, course_no),
foreign key(stu_no) references stu_table(stu_no),
foreign key(course_no) references course_table(course_no)
)engine = InnoDB character set gbk;

插入数据并查询

创建视图学生-课程

create view stu_course
as select stu_no, stu_name, course_name, course_period, course_credit, grade
from (stu_table left join select_table using(stu_no)) left join course_table using(course_no);

教师表

drop table if exists teacher_table;
create table teacher_table(
teacher_no int primary key auto_increment,
teacher_name varchar(10),
teacher_gender varchar(10),
teacher_age tinyint,
title varchar(10)
)engine = InnoDB character set gbk;

插入数据并查询

任课表

create table teaching(
teacher_no int,
course_no int,
timeint,
primary key(teacher_no, course_no),
foreign key(teacher_no) references teacher_table(teacher_no),
foreign key(course_no) references course_table(course_no)
)engine = InnoDB character set gbk;

插入数据并查询

创建视图教师-课程

create view teacher_course
as select teacher_no, teacher_name, course_name, course_period, course_credit,time
from (teacher_table left join teaching using(teacher_no)) left join course_table using(course_no);

小结：

通过实现这个简易的教务管理系统，使自己加深了对外键的理解，
，设置级联操作后，默认为restrict，如果是restrict，则不能对主表进行删除操作；从表也不能插入与主表数据不相符的字段。能够灵活运用触发器和子查询的相关操作。能熟练使用对mysql的“增删改查”的操作。加深了对view的理解。