赞
踩
把Mysql实现和华为openGauss实现都尝试了一遍,如下所示:
设计一个学籍管理小系统。系统包含以下信息:学号、学生姓名、性别、出生日、学生所在系名、学生所在系号、课程名、课程号、课程类型(必修、选修、任选)、学分、任课教师姓名、教师编号、教师职称、教师所属系名、系号、学生所选课程号、学生所选上课教师姓名、选课成绩。作业所需资料和数据已经放在课程网站上。
用E-R图表示概念模型设计出合理的关系模式:给出表名、列名、数据类型指出每个关系的键和外键(如果有的话)
完成在表上的操作 (用SQL的SELECT语句): 1.给出学生名,查询学生所选的所有课程名、成绩,累计学分。 2.给出课程名,查询学生的平均成绩 3.各门课按平均成绩排序 4.显示所有的课程名和讲课教师名
内容中的ER图在软件上实现,关系模式用具体数据库软件实现,并拷屏。要求2内容在具体数据库软件上实现,并将实验结果拷贝到作业当中。实验数据在课程网站的excel表格中。
主键与外键
数据库的具体实现(包括数据类型)
代码:
SELECT * FROM ( SELECT course.`课程名` ,selectcourse.`选课成绩`,student.`学生姓名` ,course.`学分` FROM course,student, selectcourse WHERE student.`学生姓名` = '张三' AND course.`课程号` = selectcourse.`课程号` AND selectcourse.`学号` = student.`学号` ) AS tmp1, ( SELECT SUM(course.`学分`) AS '该生累计学分' FROM course,student, selectcourse WHERE student.`学生姓名` = '张三' AND course.`课程号` = selectcourse.`课程号` AND selectcourse.`学号` = student.`学号` ) AS tmp2
运行结果:
代码:
SELECT course.`课程号`,course.`课程名`,AVG(selectcourse.`选课成绩`) AS 平均成绩
FROM course,selectcourse
WHERE
course.`课程名` = '模式识别' AND
course.`课程号` = selectcourse.`课程号`;
运行结果:
代码:
SELECT course.`课程名`,selectcourse.`课程号`,AVG(selectcourse.`选课成绩`) AS 平均成绩
FROM selectcourse,course
WHERE course.`课程号` = selectcourse.`课程号`
GROUP BY selectcourse.`课程号`
ORDER BY 平均成绩
运行结果:
代码:
SELECT course.`课程名` ,teacher.`教师姓名`
FROM course,teach,teacher
WHERE
course.`课程号` = teach.`课程号` AND
teach.`教师编号` = teacher.`教师编号`
ORDER BY teacher.`教师姓名`
运行结果:
主键与外键
数据库的具体实现(包括数据类型)
course
courseid即课程号
department
departmentid即系号
student
studentid即学号
selectcourse
teacher
teacherId即教师编号
teach
TotalCredits即累计学分
SELECT * FROM ( SELECT course.courseName ,selectcourse.grade,student.studentName ,course.credit FROM course,student, selectcourse WHERE student.studentName = '张三' AND course.courseId = selectcourse.courseId AND selectcourse.studentId = student.studentId ) AS tmp1, ( SELECT SUM(course.credit) AS TotalCredits FROM course,student, selectcourse WHERE student.studentName = '张三' AND course.courseId = selectcourse.courseId AND selectcourse.studentId = student.studentId ) AS tmp2;
代码:
SELECT course.courseName,AVG(grade) AS avgScore
FROM course,selectcourse
WHERE
course.courseName = '模式识别' AND
course.courseId = selectcourse.courseId
GROUP BY course.courseId;
查询结果
代码
SELECT course.courseName,course.courseId,AVG(selectcourse.grade) AS avgScore
FROM selectcourse,course
WHERE course.courseId = selectcourse.courseId
GROUP BY course.courseId
ORDER BY avgScore;
查询结果
代码
SELECT course.courseName ,teacher.teacherName
FROM course,teach,teacher
WHERE
course.courseId = teach.courseId AND
teach.teacherId = teacher.teacherId
ORDER BY teacher.teacherName
查询结果
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。