mysql hs courseid_mysql<六>

<60INNER 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.studentidLEFT 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.courseidINNER JOIN studentinfo AS s ON sc.studentid= s.studentidWHERE 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 <= 100THEN 1 ELSE 0 END) AS `[优秀90~100]`,

SUM(CASE WHEN sc.score>= 80 AND sc.score <90THEN 1 ELSE 0 END) AS `[良好80~90]`,

SUM(CASE WHEN sc.score>= 70 AND sc.score <80THEN 1 ELSE 0 END) AS `[一般70~80]`,

SUM(CASE WHEN sc.score>= 60 AND sc.score <70THEN 1 ELSE 0 END) AS `[及格60~70]`,

SUM(CASE WHEN sc.score>= 0 AND sc.score <60THEN 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`= 1AND s.`studentname`= ‘刘备‘)

);

-- 20、查询和张飞(学生编号3)选修的课程完全相同的学生编号和学生姓名

-- 思路：

-- 1)首先制作两个成绩信息表的连接，右表为张飞(学生编号3)的课程及成绩

-- 此时会得到 完全和张飞课程相同的同学 和 部分和张飞课程相同的同学

SELECT *

FROM scoreinfo AS sc1

INNER JOIN scoreinfo AS sc2 ON sc1.`courseid`= sc2.`courseid`AND sc2.`studentid`= 3AND 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

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://www.cnblogs.com/java-le/p/6443412.html