mysql<六>
时间：2021-07-01 10:21:17
帮助过：9人阅读
< 60
INNER JOIN studentinfo AS s ON sc.studentid = s.studentid;
-- 8、查询所有学生的选课信息（显示为：学生编号、学生姓名、课程名称，并单行显示）
SELECT temp.学生编号, temp.学生姓名, GROUP_CONCAT(temp.课程名称) AS 课程名称
FROM
(
SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, c.`coursename` AS 课程名称
FROM studentinfo AS s
LEFT JOIN scoreinfo AS sc ON s.studentid = sc.studentid
LEFT JOIN courseinfo AS c ON sc.courseid = c.courseid
) AS temp
GROUP BY temp.学生编号;
-- 9、查询任何一门课程成绩在60分以上的学生姓名、课程名称及成绩
SELECT s.`studentname` AS 学生姓名, c.`coursename` AS 课程名称, sc.`score` AS 分数
FROM scoreinfo AS sc
INNER JOIN courseinfo AS c ON sc.courseid = c.courseid
INNER JOIN studentinfo AS s ON sc.studentid = s.studentid
WHERE sc.score > 60;
-- 10、查询至少选修了两门课程的学生信息
-- 写法1、使用独立子查询
SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄
FROM studentinfo AS s
WHERE s.studentid IN
(
SELECT studentid
FROM scoreinfo
GROUP BY studentid
HAVING COUNT(courseid) >= 2
);
-- 写法2、使用内连接
SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄
FROM studentinfo AS s
INNER JOIN
(
SELECT studentid
FROM scoreinfo
GROUP BY studentid
HAVING COUNT(courseid) >= 2
) AS temp
ON s.studentid = temp.studentid;
-- 11、查询全部学生都选修了的课程编号以及课程名称（基于无脏数据）
SELECT courseid AS 课程编号, coursename AS 课程名称
FROM courseinfo
WHERE courseid IN
(
-- 在成绩信息表中，按课程编号分组，统计每组的学生编号数量，看看哪组的数量和学生信息表中学生数量一致，一致就说明是全部学生都选修的课程
SELECT courseid
FROM scoreinfo
GROUP BY courseid
HAVING COUNT(studentid) = (SELECT COUNT(studentid) FROM studentinfo)
);
-- 12、查询个人的英语成绩比数学成绩高的学生信息
-- 思路：在成绩信息表中对行数据进行获取比较，操作起来比较麻烦
-- 考虑进行【行转列】的操作，这样就可以在一行中对不同的列的内容进行比较
-- 【行转列】技巧：从成绩信息表中通过课程名称对应的课程编号形成两个独立的集合，再把这两个集合根据学生编号进行内连接，
-- 这样就得到同一个学生的不同课程的新集合，即得到同一行中有不同课程成绩的新集合
SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄
FROM studentinfo AS s
WHERE s.`studentid` IN
(
SELECT temp1.studentid
FROM
(
-- 从成绩信息表中获取的个人英语成绩集合
SELECT studentid, score
FROM scoreinfo
WHERE courseid = (SELECT courseid FROM courseinfo WHERE coursename = ‘英语‘)
) AS temp1
INNER JOIN
(
-- 从成绩信息表中获取的个人数学成绩集合
SELECT studentid, score
FROM scoreinfo
WHERE courseid = (SELECT courseid FROM courseinfo WHERE coursename = ‘数学‘)
) AS temp2
ON temp1.studentid = temp2.studentid AND temp1.score > temp2.score
);
-- 13、查询所有学生的编号、姓名、选课数量、总成绩
SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, COUNT(sc.`courseid`) AS 选课数量, SUM(sc.`score`) AS 总成绩
FROM studentinfo AS s
LEFT JOIN scoreinfo AS sc ON s.studentid = sc.studentid
-- 按照学生编号进行分组，语法OK
GROUP BY s.studentid;
-- 按照学生编号 和 学生姓名进行分组，语法也OK，因为studentid是主键，久可以唯一标识记录了，加上studentname属于锦上添花
-- GROUP BY s.studentid, s.`studentname`;
-- 按照学生姓名进行分组，语法就不OK了，因为学生姓名有重名时，就会分到一组中了
-- group by s.studentname;
-- 14、查询没有选修过张老师课程的学生信息
-- 思路：没有选修过张老师课程的学生有两种：选修了课程但是选的不是张老师的课程的学生 和 没有选修课程的学生
-- 这里正向思考比较麻烦，所以考虑逆向思考
-- 从学生集合中剔除那些选修了张老师课程的学生，剩下的就是没有选修张老师课程的学生
SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄
FROM studentinfo AS s
WHERE s.studentid NOT IN
(
-- 选修了张老师课程的学生
SELECT sc.studentid
FROM scoreinfo AS sc
INNER JOIN courseinfo AS c ON sc.courseid = c.courseid
INNER JOIN teacherinfo AS t ON c.teacherid = t.teacherid AND t.teachername = ‘张老师‘
);
-- 15、查询学过语文也学过数学的学生信息
SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄
FROM studentinfo AS s
INNER JOIN scoreinfo AS sc1
ON s.studentid = sc1.studentid AND sc1.courseid = (SELECT courseid FROM courseinfo WHERE coursename = ‘语文‘)
INNER JOIN scoreinfo AS sc2
ON s.studentid = sc2.studentid AND sc2.courseid = (SELECT courseid FROM courseinfo WHERE coursename = ‘数学‘);
-- 16、查询个人成绩中每门功课都不及格的学生信息
-- 可能性1、无成绩的也算满足条件
SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄
FROM studentinfo AS s
WHERE s.studentid NOT IN
(
SELECT studentid
FROM scoreinfo
WHERE score >= 60
);
-- 可能性2、无成绩的不算满足条件
SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄
FROM studentinfo AS s
WHERE s.studentid IN
(
-- 如下写法不正确：因为这样会把部分课程不及格部分课程及格的同学也筛选出来
-- SELECT studentid
-- FROM scoreinfo
-- WHERE score < 60
-- 正确写法：按照学生编号分组，分组后组里最高的课程分数还小于60分，意味着所有课程都不及格
SELECT studentid
FROM scoreinfo
GROUP BY studentid
HAVING MAX(score) < 60
);
-- 17、查询每门功课的分数段人数，显示为：课程编号、课程名称、选课人数、[优秀90~100]、[良好80~90]、[一般70~80]、[及格60~70]、[不及格0~60]
SELECT
sc.`courseid` AS 课程编号,
c.`coursename` AS 课程名称,
COUNT(sc.`studentid`) AS 选课人数,
SUM(CASE WHEN sc.score >= 90 AND sc.score
<= 100 THEN 1 ELSE 0 END) AS `[优秀90~100]`,
SUM(CASE WHEN sc.score >= 80 AND sc.score
< 90 THEN 1 ELSE 0 END) AS `[良好80~90]`,
SUM(CASE WHEN sc.score >= 70 AND sc.score
< 80 THEN 1 ELSE 0 END) AS `[一般70~80]`,
SUM(CASE WHEN sc.score >= 60 AND sc.score
< 70 THEN 1 ELSE 0 END) AS `[及格60~70]`,
SUM(CASE WHEN sc.score >= 0 AND sc.score
< 60 THEN 1 ELSE 0 END) AS `[不及格0~60]`
FROM scoreinfo AS sc
INNER JOIN courseinfo AS c ON sc.`courseid` = c.`courseid`
GROUP BY sc.`courseid`;
-- 18、查询没有选修全部课程的学生信息
SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄
FROM studentinfo AS s
WHERE s.studentid IN
(
-- 按照学生编号分组，每组中的课程统计数量小于课程信息表中课程数量的就是没有选修全部课程的学生
SELECT studentid
FROM scoreinfo
GROUP BY studentid
HAVING COUNT(courseid) < (SELECT COUNT(courseid) FROM courseinfo)
);
-- 19、查询和刘备（学生编号1）至少一起选修了一门课程的学生编号和学生姓名
SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄
FROM studentinfo AS s
WHERE s.studentid IN
(
-- 至少和刘备一起选修了一门课程
SELECT studentid
FROM scoreinfo
WHERE courseid IN
(
-- 查询出刘备选的课程
SELECT courseid
FROM scoreinfo AS sc
INNER JOIN studentinfo AS s
ON s.`studentid` = sc.`studentid` AND s.`studentid` = 1 AND s.`studentname` = ‘刘备‘
)
);
-- 20、查询和张飞（学生编号3）选修的课程完全相同的学生编号和学生姓名
-- 思路：
-- 1）首先制作两个成绩信息表的连接，右表为张飞（学生编号3）的课程及成绩
-- 此时会得到 完全和张飞课程相同的同学 和 部分和张飞课程相同的同学
SELECT *
FROM scoreinfo AS sc1
INNER JOIN scoreinfo AS sc2 ON sc1.`courseid` = sc2.`courseid` AND sc2.`studentid` = 3 AND sc1.`studentid` <> sc2.`studentid`
-- 2）在此基础上，在新生成的集合中按照学生编号进行分组，如果有学生的课程数量和张飞的课程数量一致的，那就张飞（学生编号3）选修的课程完全相同的学生
SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄
FROM studentinfo AS s
WHERE s.studentid IN
(
SELECT sc1.`studentid`
FROM scoreinfo AS sc1
INNER JOIN scoreinfo AS sc2 ON sc1.`courseid` = sc2.`courseid` AND sc2.`studentid` = 3 AND sc1.`studentid` <> sc2.`studentid`
GROUP BY sc1.`studentid`, sc2.`studentid`
HAVING COUNT(sc1.`courseid`) = (SELECT COUNT(courseid) FROM scoreinfo WHERE studentid = 3)
);
-- 21、按个人平均成绩降序排列显示学生的语文、数学、英语三门功课的成绩（选修了几门计算几门的平均分，未选修的课程显示未选）
-- 显示为：学生编号、学生姓名、平均成绩、语文成绩、数学成绩、英语成绩
SELECT
sc.`studentid` AS 学生编号,
s.`studentname` AS 学生姓名,
AVG(sc.`score`) AS 平均成绩,
IFNULL((
SELECT sc1.score
FROM scoreinfo AS sc1
WHERE sc1.studentid = sc.`studentid`
AND sc1.courseid = (SELECT courseid FROM courseinfo WHERE coursename = ‘语文‘))
, ‘未选‘) AS 语文成绩,
IFNULL((
SELECT sc2.score
FROM scoreinfo AS sc2
WHERE sc2.studentid = sc.`studentid`
AND sc2.courseid = (SELECT courseid FROM courseinfo WHERE coursename = ‘数学‘))
, ‘未选‘) AS 数学成绩,
IFNULL((
SELECT sc3.score
FROM scoreinfo AS sc3
WHERE sc3.studentid = sc.`studentid`
AND sc3.courseid = (SELECT courseid FROM courseinfo WHERE coursename = ‘英语‘))
, ‘未选‘) AS 英语成绩
FROM scoreinfo AS sc
INNER JOIN studentinfo AS s
ON sc.`studentid` = s.`studentid`
GROUP BY sc.`studentid`
-- order by AVG(sc.`score`) desc;
-- 上面写法和下面写法均可，因为ORDER BY子句在SELECT子句之后执行的
ORDER BY 平均成绩 DESC;
-- 22、查询每门功课的最高分和最低分，显示为：课程编号、课程名称、最高分、最低分
SELECT
sc.courseid AS 课程编号,
c.`coursename` AS 课程名称,
MAX(sc.`score`) AS 最高分,
MIN(sc.`score`) AS 最低分
FROM scoreinfo AS sc
INNER JOIN courseinfo AS c
ON sc.courseid = c.courseid
GROUP BY sc.courseid;
-- 23、查询只选修了一门课程的学生的学生编号和学生姓名
SELECT
sc.`studentid` AS 学生编号,
s.`studentname` AS 学生姓名
FROM scoreinfo AS sc
INNER JOIN studentinfo AS s
ON sc.studentid = s.studentid
GROUP BY sc.studentid
HAVING COUNT(sc.courseid) = 1;
-- 24、查询学过张老师教的全部课程的学生的学生编号和学生姓名
SELECT
sc.`studentid` AS 学生编号,
s.`studentname` AS 学生姓名
FROM scoreinfo AS sc
INNER JOIN courseinfo AS c ON sc.courseid = c.courseid
INNER JOIN teacherinfo AS t ON c.teacherid = t.teacherid AND t.teachername = ‘张老师‘
INNER JOIN studentinfo AS s ON sc.studentid = s.studentid
GROUP BY sc.studentid
HAVING COUNT(sc.courseid) = (
-- 张老师教的课程数量
SELECT COUNT(courseinfo.`courseid`)
FROM courseinfo
INNER JOIN teacherinfo AS t ON courseinfo.teacherid = t.teacherid AND t.teachername = ‘张老师‘
);
-- 25、学生信息表中被人删除了若干条记录，现在需要查询出第4行至第6行的记录来使用（考虑使用多种实现方式，提示：使用LIMIT 和 不使用LIMIT）
-- delete from studentinfo where studentid = 3 or studentid = 7;
-- 写法1、直接使用LIMIT关键字
SELECT * FROM studentinfo LIMIT 3, 3;
-- 写法2、考虑取出前6行，进行倒序排列，再取出前3行，再倒序
SELECT *
FROM
(
SELECT *
FROM
(
SELECT *
FROM (SELECT * FROM studentinfo LIMIT 0, 6) AS temp1
ORDER BY temp1.studentid DESC
) AS temp2 LIMIT 0, 3
) AS temp3
ORDER BY temp3.studentid ASC;
-- 写法3、不使用LIMIT关键字
SELECT temp.`studentid` AS 学生编号, temp.`studentname` AS 学生姓名, temp.`studentgender` AS 学生性别, temp.`studentage` AS 学生年龄
FROM
(
SELECT
*,
(SELECT COUNT(*) FROM studentinfo AS s2 WHERE s2.studentid <= s1.`studentid`) AS rownum
FROM studentinfo AS s1
) AS temp
WHERE temp.rownum BETWEEN 4 AND 6;
mysql<六>
标签：mit else 制作 没有 eid 个学生 排列 标识 between
