必威体育Betway必威体育官网
当前位置:首页 > IT技术

MySQL(学生表、教师表、课程表、成绩表)多表查询

时间:2019-09-26 17:14:34来源:IT技术作者:seo实验室小编阅读:59次「手机版」
 

成绩表

                                              多表查询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

相关阅读

excel表格输入身份证号乱码怎么解决

我们平时在编辑excel表格的时候,难免要输入身份证号,很多朋友应该都会有一个疑问,输入身份证号之后会显示乱码出来,然后就去重新输入

学生做微商卖什么产品好,学生做好微商利弊分析

现在刚毕业的大学生都比较倾向于做微商,毕竟在家就能轻轻松松赚钱。但是由于学生对于做这些都是没有什么经验,经常会做亏本生意。学

create table ,create table as 与create like三种建

Hive的hql是基于sql而来,而sql中关于表的创建有几种方式。同样,hive也支持这些表的创建方式。 官网文档关于建表的地址:hive常见建表

《给教师的一百条建议》读后感3500字

《给教师的一百条建议》读后感3500字:初读苏霍姆林斯基的《给教师的一百条建议》这本书,是因为招聘考试中经常会涉及到苏联著名教育

商品展示列表——大图、多图、图文列表该如何选择?

这篇文章在我所写文章里面,耗时最长,不是因为它很复杂,而是要写清楚它的可操作性确实是一件不易的事情,这里要感谢为我指出文章问题的

分享到:

栏目导航

推荐阅读

热门阅读