MySQL(学生表、教师表、课程表、成绩表)多表查询 |
您所在的位置:网站首页 › 用mysql创建一个学生信息表包含姓名性别成绩年龄 › MySQL(学生表、教师表、课程表、成绩表)多表查询 |
多表查询SQL语句
1、表架构
student(sid,sname,sage,ssex) 学生表 course(cid,cname,tid) 课程表 sC(sid,cid,score) 成绩表 teacher(tid,tname) 教师表 2、建表sql语句 CREATE TABLE student ( sid INT PRIMARY KEY NOT NULL, sname VARCHAR(30), sage INT, ssex VARCHAR(8) ) CREATE TABLE course ( cid INT PRIMARY KEY NOT NULL, cname VARCHAR(30), tid INT ) CREATE TABLE sc ( sid INT NOT NULL, cid INT NOT NULL, score INT ) CREATE TABLE teacher ( tid INT PRIMARY KEY NOT NULL, tname VARCHAR(30) ) 3、问题:(1)查询“30001”课程的所有学生的学号与分数; SELECT sid,score FROM sc WHERE cid="30001"
(2)查询“001”课程比“002”课程成绩高的所有学生的学号与分数; SELECT a.sid,a.score FROM (SELECT sid,score FROM sc WHERE cid="30001") a, (SELECT sid,score FROM sc WHERE cid="30002") b WHERE a.score>b.score AND a.sid=b.sid(3)查询平均成绩大于60分的同学的学号和平均成绩; SELECT sid,AVG(score) FROM sc GROUP BY sid HAVING AVG(score)>60(4)查询所有同学的学号、姓名、选课数、总成绩 SELECT s.sid AS "学号", s.sname AS "姓名", COUNT(sc.cid) AS "课程数目", SUM(sc.score) AS "总分数" FROM student s, sc sc WHERE s.sid=sc.sid GROUP BY s.sid
(5)查询姓“李”的老师的个数; select count(distinct(Tname)) from teacher where tname like '李%';(6)查询学过“张三”老师课的同学的学号、姓名 SELECT s.sid AS "学号", s.sname AS "姓名" FROM student s, sc sc, course c, teacher t WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="张三"(7)查询没有学过“张三”老师课的同学的学号、姓名 SELECT s.sid, s.sname FROM student s WHERE s.sid NOT IN ( SELECT s.sid FROM student s, sc sc, course c, teacher t WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="张三" )(8)查询学过“30001”并且也学过编号“30002”课程的同学的学号、姓名 SELECT s.sid, s.sname FROM student s, sc sc WHERE s.sid=sc.sid AND sc.cid="30001" AND EXISTS( SELECT * FROM sc AS sc2 WHERE sc2.sid=sc.sid AND sc2.cid="30002" )(9)查询学过“叶平”老师所教的所有课的同学的学号、姓名; SELECT sid, sname FROM student WHERE sid IN ( SELECT sc.sid FROM sc sc, course c, teacher t WHERE sc.cid=c.cid AND c.tid=t.tid AND t.tname="张二" )(10)查询所有课程成绩小于60分的同学的学号、姓名 SELECT sid, sname FROM student WHERE sid NOT IN ( SELECT DISTINCT(sc.sid) FROM student s, sc sc WHERE sc.sid=s.sid AND sc.score>60) (11)查询没有学全所有课的同学的学号、姓名; SELECT sid, sname FROM student WHERE sid NOT IN( SELECT s.sid FROM student s, sc sc WHERE sc.sid=s.sid GROUP BY s.sid HAVING COUNT(sc.cid)=( SELECT COUNT(cid) FROM course))(12)查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分 SELECT cid AS "课程ID", MAX(score) AS "最高分", MIN(score) AS "最低分" FROM sc GROUP BY cid(13)按各科平均成绩从低到高和及格率的百分数从高到低顺序 (方式一) SELECT sc.cid AS "课程ID",c.cname AS "课程名", AVG(sc.score) AS "平均成绩", SUM(CASE WHEN sc.score > 60 THEN 1 ELSE 0 END)/COUNT(1)*100 AS "及格百分数" FROM sc sc, course c WHERE sc.cid=c.cid GROUP BY sc.cid ORDER BY AVG(sc.score) ASC, SUM(CASE WHEN sc.score > 60 THEN 1 ELSE 0 END)/COUNT(1)*100 DESC (方式二) SELECT sc.cid AS "课程ID",c.cname AS "课程名", IFNULL(AVG(sc.score),0) AS "平均成绩", 100*SUM(CASE WHEN IFNULL(sc.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS "及格百分数" FROM sc sc, course c WHERE sc.cid = c.cid GROUP BY sc.cid ORDER BY AVG(sc.score) ASC, 100*SUM(CASE WHEN IFNULL(sc.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC(14)查询所有学生的所有科目的成绩单(学号、姓名、语文、数学、英语、物理、平均分、总分(按照总分由高到低排序)) SELECT s.sid AS "学号", s.sname AS "姓名", SUM(CASE c.cname WHEN "语文" THEN sc.score ELSE 0 END) AS "语文", SUM(CASE c.cname WHEN "数学" THEN sc.score ELSE 0 END) AS "数学", SUM(CASE c.cname WHEN "英语" THEN sc.score ELSE 0 END) AS "英语", SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理", IFNULL(AVG(sc.score),0) AS "平均分", IFNULL(SUM(sc.score),0) AS "总分" FROM student s LEFT OUTER JOIN sc sc ON s.sid=sc.sid LEFT OUTER JOIN course c ON sc.cid=c.cid GROUP BY s.sid, s.sname ORDER BY IFNULL(SUM(sc.score),0) DESC(15)查询总分排名在200-300(包含200和第300)之间的学生所有成绩单信息 SELECT s.sid AS "学号", s.sname AS "姓名", SUM(CASE c.cname WHEN "语文" THEN sc.score ELSE 0 END) AS "语文", SUM(CASE c.cname WHEN "数学" THEN sc.score ELSE 0 END) AS "数学", SUM(CASE c.cname WHEN "英语" THEN sc.score ELSE 0 END) AS "英语", SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理", IFNULL(AVG(sc.score),0) AS "平均分", IFNULL(SUM(sc.score),0) AS "总分" FROM student s LEFT OUTER JOIN sc sc ON s.sid=sc.sid LEFT OUTER JOIN course c ON sc.cid=c.cid GROUP BY s.sid, s.sname HAVING IFNULL(SUM(sc.score),0) BETWEEN 200 AND 300 ORDER BY IFNULL(SUM(sc.score),0) DESC(16)查询总分排名在前四名的学生所有成绩单信息 SELECT s.sid AS "学号", s.sname AS "姓名", SUM(CASE c.cname WHEN "语文" THEN sc.score ELSE 0 END) AS "语文", SUM(CASE c.cname WHEN "数学" THEN sc.score ELSE 0 END) AS "数学", SUM(CASE c.cname WHEN "英语" THEN sc.score ELSE 0 END) AS "英语", SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理", IFNULL(AVG(sc.score),0) AS "平均分", IFNULL(SUM(sc.score),0) AS "总分" FROM student s LEFT OUTER JOIN sc sc ON s.sid=sc.sid LEFT OUTER JOIN course c ON sc.cid=c.cid GROUP BY s.sid, s.sname ORDER BY IFNULL(SUM(sc.score),0) DESC LIMIT 0,4(17)查询总分排名在前二名到四名的学生所有成绩单信息(limit 1,3表示从第二条数据开始,连续三条数据) SELECT s.sid AS "学号", s.sname AS "姓名", SUM(CASE c.cname WHEN "语文" THEN sc.score ELSE 0 END) AS "语文", SUM(CASE c.cname WHEN "数学" THEN sc.score ELSE 0 END) AS "数学", SUM(CASE c.cname WHEN "英语" THEN sc.score ELSE 0 END) AS "英语", SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理", IFNULL(AVG(sc.score),0) AS "平均分", IFNULL(SUM(sc.score),0) AS "总分" FROM student s LEFT OUTER JOIN sc sc ON s.sid=sc.sid LEFT OUTER JOIN course c ON sc.cid=c.cid GROUP BY s.sid, s.sname ORDER BY IFNULL(SUM(sc.score),0) DESC LIMIT 1,3(18)查询学生平均成绩及其名次 (非本人) SELECT 1+(SELECT COUNT( distinct 平均成绩) FROM ( SELECT sid,AVG(score) AS 平均成绩 FROM sc GROUP BY sid ) AS T1 WHERE 平均成绩 > T2.平均成绩) as 名次, S# as 学生学号,平均成绩 FROM (SELECT sid,AVG(score) 平均成绩 FROM sc GROUP BY sid ) AS T2 ORDER BY 平均成绩 desc
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |