用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 ;