-- 创建学生管理信息系统数据库(stuMIS)
create database stuMIS default character set utf8;
-- 打开stuMIS数据库
use stuMIS;
*****************************************************
-- 创建部门表(department)
create table department
( departid int primary key,
departname varchar(20),
office varchar(40),
tel varchar(20),
chairman varchar(20)
);
-- 创建班级表
create table class
( departid int not null ,
classid varchar(10) not null,
classname varchar(40),
monitor varchar(20) ,
primary key (classid),
foreign key (departid) references department(departid) on delete cascade on update cascade
);
-- 创建课程表(course)
create table course
( cid varchar(10) not null,
cname varchar(40) not null,
ctype varchar(20) not null,
ctime varchar(30) not null,
teacher varchar(10),
smallnum int not null ,
registernum int not null,
primary key(cid)
);
-- 创建学生表(student)
create table student
( stuid varchar(10) not null,
stuname varchar(10) not null,
stusex varchar(2) not null,
stupwd varchar(7) not null,
stuage int ,
classid varchar(10) not null,
address varchar(100) null,
primary key (stuid),
foreign key(classid) references class(classid)
);
-- 创建成绩表(score)
create table score
( stuid varchar(10) not null,
cid varchar(10) not null,
score int,
primary key(stuid,cid),
foreign key(stuid) references student(stuid),
foreign key(cid) references course(cid)
);
-- 向部门表插入数据
insert into department( departid,departname,office,tel,chairman) values
(1,'计算机系','a502','4040','孙丰伟'),
(2,'经济管理系','a305','4024','李红艳'),
(3,'水晶石','a218','4012','吴可鹏&#