用MYSQL学生课程与成绩管理系统

用MYSQL学生课程与成绩管理系统

模式要求：一个老师可以任教多门课程，一门课程可以由多个老师教导。一个学生可以选修多门课程，一门课程也可以被多个同学选。一i个老师可以教导多个学生，一个学生有多个老师。

画ER图

将ER图转化为关系模式图

建表

teacher table

CREATE TABLE teacher (
  teacherId int NOT NULL AUTO_INCREMENT,
  teacherName varchar(20),
  college varchar(20),
  PRIMARY KEY (teacherId)
) 

advisor table

CREATE TABLE advisor (
  teacherId int NOT NULL,
  studentId int NOT NULL,
  PRIMARY KEY (teacherId,studentId),
  KEY studentId studentId),
  FOREIGN KEY (teacherId) REFERENCES teacher (teacherId),
  FOREIGN KEY (studentId) REFERENCES student(studentId)
) 

student table

CREATE TABLE  student  (
   studentId  int NOT NULL AUTO_INCREMENT,
   studentName  varchar(45) ,
   sex  varchar(45) ,
   class  varchar(45),
   major  varchar(45) ,
  PRIMARY KEY ( studentId ),
 
) 

take table

CREATE TABLE  take  (
   courseId  int NOT NULL,
   studentId  int NOT NULL,
    teacherId int NOT NULL,
  PRIMARY KEY ( courseId , studentId ,teacherId),
 FOREIGN KEY ( courseId ) REFERENCES  course  ( courseId ),
 FOREIGN KEY ( studentId ) REFERENCES  student  ( studentId )
) 

course table

CREATE TABLE  course  (
   courseId  int NOT NULL AUTO_INCREMENT,
   courseName  varchar(20) ,
   courseScore  int DEFAULT NULL,
   teacherId  int NOT NULL,
  PRIMARY KEY ( courseId , teacherId ),
  FOREIGN KEY ( teacherId ) REFERENCES  teacher  ( teacherId )
) 

student table

CREATE TABLE  student  (
   studentId  int NOT NULL AUTO_INCREMENT,
   studentName  varchar(45) ,
   sex  varchar(45),
   class  varchar(45) L,
   major  varchar(45) ,
  PRIMARY KEY ( studentId ),
) 

score table

CREATE TABLE  score  (
   courseId  int NOT NULL,
   studentId  int NOT NULL,
   score  int DEFAULT NULL,
  PRIMARY KEY ( courseId , studentId ),
  FOREIGN KEY ( courseId ) REFERENCES  course  ( courseId ),
FOREIGN KEY ( studentId ) REFERENCES  student  ( studentId )
) 

teach table

CREATE TABLE  course  (
   courseId  int NOT NULL AUTO_INCREMENT,
   teacherId  int NOT NULL,
   courseName  varchar(20) ,
   courseScore  int DEFAULT ,
  PRIMARY KEY ( courseId , teacherId ),
    FOREIGN KEY ( teacherId ) REFERENCES  teacher  ( teacherId )
)

注意：如果要删除表中数据要先 SET SQL_SAFE_UPDATES = 0

插入数据

teacher table

student table

advisor table

teach table
take table

score table

创建视图

显示学生全部信息

drop view  if exists studentInFo;
create view studentInfo as
select studentId,studentName,sex,class,major,courseId,courseName
from student natural join course;

显示老师全部信息

drop view  if exists teacherInFo;
create view teacherInfo as
select teacherId,teacherName,college,studentId,studentName,courseId,courseName
from student natural join course natural join teacher;

函数

选数据库这门课程得分最高的学生

drop function if exists MaxScoreOfDataBase;
DELIMITER //
create function MaxScoreOfDataBase(CourseId integer)
returns integer
DETERMINISTIC
begin
declare MaxScore integer;
	select max(score) into MaxScore
	from student natural join take natural join score
	where courseId = CourseId;
return Maxscore;
END//
DELIMITER ;

过程

选数据库这门课程得分最低的学生

drop procedure if exists MinScoreOfDataBase;
DELIMITER //
create definer = current_user procedure MinScoreOfDataBase(
	IN CourseId INT, 
	OUT MinScore INT
)
DETERMINISTIC
begin
	select min(score) into MinScore
	from student natural join take natural join score
	where courseId = CourseId;
END//
DELIMITER ;