数据表操作实训

-- 创建学生管理信息系统数据库(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','吴可鹏&#