MySQL数据库操作与查询示例：学生课程成绩管理系统
题目描述：
Mysql中现有四张表分别为学生表、课程表、成绩表、教师表。表结构如下
学生表student：
| 字段 | 类型 | 说明 |
| sid | Varchar(10) | 学号 |
| sName | Varchar(20) | 姓名 |
| sAge | datetime | 年龄 |
| sGender | Varchar(10) | 性别 |
课程表course：
| 字段 | 类型 | 说明 |
| cid | Varchar(10) | 课程编号 |
| cName | Varchar(20) | 课程名称 |
| tid | Int(20) | 授课老师id |
成绩表sc：
| 字段 | 类型 | 说明 |
| sid | Varchar(10) | 学生学号 |
| cid | Varchar(20) | 课程编号 |
| score | Int(10) | 分数 |
教师表Teacher：
| 字段 | 类型 | 说明 |
| tid | Int(10) | 教师id |
| tName | Varchar(20) | 教师姓名 |
请按照以下要求完成需求：
在mysql数据库中分别创建四张表，并添加数据
insert into course(cid,cName,tid) values ('001','企业管理',3);
insert into course(cid,cName,tid) values ('002','马克思',3);
insert into course(cid,cName,tid) values ('004','数据库',1);
insert into course(cid,cName,tid) values ('005','英语',1);
insert into sc(sid,cid,score) values ('1001','001',80);
insert into sc(sid,cid,score) values ('1001','002',60);
insert into sc(sid,cid,score) values ('1001','003',70);
insert into sc(sid,cid,score) values ('1002','001',85);
insert into sc(sid,cid,score) values ('1002','002',70);
insert into sc(sid,cid,score) values ('1003','004',90);
insert into sc(sid,cid,score) values ('1003','001',90);
insert into sc(sid,cid,score) values ('1003','002',99);
insert into sc(sid,cid,score) values ('1004','002',65);
insert into sc(sid,cid,score) values ('1004','003',50);
insert into sc(sid,cid,score) values ('1005','005',80);
insert into sc(sid,cid,score) values ('1005','004',70);
insert into sc(sid,cid,score) values ('1003','003',10);
insert into sc(sid,cid,score) values ('1003','005',10);
insert into student(sid,sName,sAge,sGender) values ('1001','张三丰','1980-10-12 23:12:36','男');
insert into student(sid,sName,sAge,sGender) values ('1002','张无极','1995-10-12 23:12:36','男');
insert into student(sid,sName,sAge,sGender) values ('1003','李奎','1992-10-12 23:12:36','女');
insert into student(sid,sName,sAge,sGender) values ('1004','李元宝','1980-10-12 23:12:36','女');
insert into student(sid,sName,sAge,sGender) values ('1005','李世明','1981-10-12 23:12:36','男');
insert into student(sid,sName,sAge,sGender) values ('1006','赵六','1986-10-12 23:12:36','男');
insert into student(sid,sName,sAge,sGender) values ('1007','田七','1981-10-12 23:12:36','女');
insert into teacher(tid,tName) values (1,'李老师');
insert into teacher(tid,tName) values (2,'何以琛');
insert into teacher(tid,tName) values (3,'叶平');查询“001”课程比“002”课程成绩高的所有学生的学号;
SELECT s.sid
FROM student s
JOIN sc sc1 ON s.sid = sc1.sid AND sc1.cid = '001'
JOIN sc sc2 ON s.sid = sc2.sid AND sc2.cid = '002'
WHERE sc1.score > sc2.score查询平均成绩大于60分的同学的学号和平均成绩;
SELECT s.sid,AVG(sc.score) '平均成绩'
FROM student s
JOIN sc ON s.sid = sc.sid
GROUP BY s.sid
HAVING AVG(sc.score) >60查询所有同学的学号、姓名、选课数、总成绩;
SELECT s.sid,s.sName,COUNT(sc.sid) AS '选课数',SUM(score) AS '总成绩'
FROM student s
JOIN sc ON s.sid = sc.sid
GROUP BY s.sid,s.sName查询姓“李”的老师的个数;
SELECT COUNT(*)
FROM Teacher
WHERE tName LIKE '李%';查询没学过“叶平”老师课的同学的学号、姓名;
SELECT s.sid, s.sName
FROM student s
WHERE s.sid NOT IN (
SELECT DISTINCT sc.sid
FROM sc
JOIN course c ON sc.cid = c.cid
JOIN Teacher t ON c.tid = t.tid
WHERE t.tName = '叶平'
);查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
SELECT s.sid,s.sName
FROM student s
JOIN sc sc1 ON s.sid = sc1.sid AND sc1.cid = '001'
JOIN sc sc2 ON s.sid = sc2.sid AND sc2.cid = '002'
WHERE sc1.score > sc2.score;查询没有学全所有课的同学的学号、姓名;
SELECT s.sid, s.sName
FROM student s
WHERE EXISTS (
SELECT *
FROM course
WHERE cid NOT IN (SELECT cid FROM sc WHERE sc.sid = s.sid)
);按各科平均成绩从低到高和及格率的百分数从高到低排序
SELECT c.cid, c.cName, AVG(sc.score) AS avg_score, COUNT(sc.score >= 60)*1.0 / COUNT(sc.score) AS pass_rate
FROM course c
JOIN sc ON c.cid = sc.cid
GROUP BY c.cid, c.cName
ORDER BY avg_score ASC, pass_rate DESC;查询同名同性学生名单，并统计同名人数
SELECT sName, sGender, COUNT(*) AS count
FROM student
GROUP BY sName, sGender
HAVING COUNT(*) > 1;查询每门功课成绩最好的前两名
SELECT SC.sid,S.sName,SC.cid,SC.score
FROM SC
JOIN student S ON SC.sid = S.sid
WHERE
2 > (
SELECT COUNT(*)
FROM SC AS InnerSC
WHERE InnerSC.cid = SC.cid AND InnerSC.score > SC.score
)
ORDER BY SC.cid, SC.score DESC;检索至少选修两门课程的学生学号
SELECT sid
FROM sc
GROUP BY sid
HAVING COUNT(cid) >= 2;原文地址：https://blog.csdn.net/b123321888/article/details/142522046
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！