【题】MySQL数据库的一个例题（上） [ 云息未来 ]

创建数据库及其对应的表

表架构
学生表：（sid,sname,sage,ssex）
课程表：（cid,cname,tid）
分数表：（sid,cid,score）
教师表：（tid,tname）

学生表：

CREATE TABLE Student(
    sid int,
    sname varcahr(10),
    sage int,
    ssex varcahr(10)
);

课程表：

CREATE TABLE Course(
    cid int,
    cname varcahr(10),
    tid int
);

分数表：

CREATE TABLE SC(
    sid int,
    cid int,
    score int
);

教师表：

CREATE TABLE Teacher(
    tid int,
    tname varcahr(10)
);

插入测试语句

insert into Student select 1,N'刘一',18,N'男' union all
 select 2,N'钱二',19,N'女' union all
 select 3,N'张三',17,N'男' union all
 select 4,N'李四',18,N'女' union all
 select 5,N'王五',17,N'男' union all
 select 6,N'赵六',19,N'女' 
 
 insert into Teacher select 1,N'叶平' union all
 select 2,N'贺高' union all
 select 3,N'杨艳' union all
 select 4,N'周磊'
 
 insert into Course select 1,N'语文',1 union all
 select 2,N'数学',2 union all
 select 3,N'英语',3 union all
 select 4,N'物理',4
 
 insert into SC 
 select 1,1,56 union all 
 select 1,2,78 union all 
 select 1,3,67 union all 
 select 1,4,58 union all 
 select 2,1,79 union all 
 select 2,2,81 union all 
 select 2,3,92 union all 
 select 2,4,68 union all 
 select 3,1,91 union all 
 select 3,2,47 union all 
 select 3,3,88 union all 
 select 3,4,56 union all 
 select 4,2,88 union all 
 select 4,3,90 union all 
 select 4,4,93 union all 
 select 5,1,46 union all 
 select 5,3,78 union all 
 select 5,4,53 union all 
 select 6,1,35 union all 
 select 6,2,68 union all 
 select 6,4,71

问题

为了方便思考这里我把四张表信息帖在下面：

学生表：

sid	sname	sage	ssex
1	刘一	18	男
2	钱二	19	女
3	张三	17	男
4	李四	18	女
5	王五	17	男
6	赵六	19	女

课程表：

Cid	Cname	Tid
1	语文	1
2	数学	2
3	英语	3
4	物理	4

分数表：

Sid	Cid	score
1	1	56
1	2	78
1	3	67
1	4	58
2	1	79
2	2	81
2	3	92
2	4	68
3	1	91
3	2	47
3	3	88
3	4	56
4	2	88
4	3	90
4	4	93
5	1	46
5	3	78
5	4	53
6	1	35
6	2	68
6	4	71

教师表：

Tid	Tname
1	叶平
2	贺高
3	杨艳
4	周磊

查询“001”课程比“002”课程成绩高的所有学生的学号；

SELECT a.sid '学号' FROM 
    (SELECT sid,score FROM sc WHERE cid = '1') AS a,
    (SELECT sid,score FROM sc WHERE cid = '2') AS b
    WHERE a.score > b.score AND a.sid = b.sid;

查询平均成绩大于60分的同学的学号和平均成绩；

1
2
3

SELECT sid '学号',avg(score) '平均成绩'
FROM sc
GROUP BY sid HAVING avg(score) > 60;

查询所有同学的学号、姓名、选课数、总成绩；

1
2
3

SELECT student.sid '学号',student.sname '姓名',count(sc.cid) '选课数',sum(score) '总成绩'
FROM student LEFT JOIN sc ON student.sid = sc.sid
GROUP BY student.sid,student.sname;

查询姓“李”的老师的个数；

1
2
3

SELECT count(DISTINCT(tname)) '姓“李”的老师的个数'
FROM teacher
WHERE tname like '李%';

查询没学过“叶平”老师课的同学的学号、姓名；

SELECT student.sid '学号',student.sname '姓名'
FROM student
WHERE sid NOT IN (
    SELECT DISTINCT(sc.sid)
    FROM sc,teacher,course
    WHERE sc.Cid = course.Cid AND teacher.Tid = course.Tid AND teacher.Tname = '叶平'
)

查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

SELECT student.sid '学号',student.sname '姓名'
FROM student,sc
WHERE student.sid = sc.sid AND sc.cid = '1' AND EXISTS(
    SELECT * 
    FROM sc AS sc_2
    WHERE sc_2.sid = sc.sid AND sc_2.cid = '2'
);

查询学过“叶平”老师所教的所有课的同学的学号、姓名；

SELECT student.sid '学号', student.sname'姓名'
FROM student
WHERE sid in (
    SELECT sid 
    FROM sc,course,teacher
    WHERE sc.cid = course.cid AND teacher.tid = course.tid AND  teacher.tname = '叶平'
    GROUP BY sid HAVING count(sc.cid) = (
        SELECT count(cid)
        FROM course,teacher
        WHERE course.tid = teacher.tid AND tname = '叶平'
    )
);

查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

SELECT sid '学号',sname '姓名'
FROM (
    SELECT student.sid,student.sname,score,(
        SELECT score 
        FROM sc AS sc_2
        WHERE sc_2.sid = student.sid AND sc_2.cid = '2'
) score2
FROM student,sc
WHERE student.sid = sc.sid AND cid = '1'
) S_2
WHERE score2 < score;

查询所有课程成绩小于60分的同学的学号、姓名；

SELECT sid '学号', sname '姓名'
FROM student
WHERE sid IN (
    SELECT s.sid
    FROM student AS s,sc
    WHERE s.sid = sc.sid AND sc.score < 60
);

查询没有学全所有课的同学的学号、姓名；

SELECT student.sid '学号',student.sname '姓名'
FROM student,sc
WHERE student.sid = sc.sid
GROUP BY student.sid,student.sname
HAVING count(cid) < (
    SELECT count(cid)
    FROM course
);

查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

SELECT DISTINCT student.sid '学号',sname '姓名'
FROM student,sc
WHERE student.sid = sc.sid AND sc.cid IN (
    SELECT cid
    FROM sc
    WHERE sid = '1'
);

查询至少学过学号为“1”同学所有一门课的,其他同学学号和姓名；

SELECT DISTINCT student.sid '学号',sname '姓名'
FROM student,sc
WHERE student.sid = sc.sid AND sc.cid IN (
    SELECT cid 
    FROM sc
    WHERE sc.sid = '1'
);

把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；

UPDATE sc s JOIN (
    SELECT cid,AVG(score) score
    FROM sc 
    WHERE cid IN (
            SELECT c.cid
            FROM course c JOIN teacher t ON c.tid = t.tid
            WHERE t.TName='叶平'
    ) GROUP BY cid
) t ON s.cid = t.cid SET s.score = .score

查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名；

SELECT  sid '学号',sname '姓名'
FROM    student
WHERE   sid IN ( 
    SELECT sc.sid
    FROM   sc
    JOIN ( 
        SELECT  *
        FROM    sc
        WHERE   sid = '2'
    ) t ON t.cid = SC.Cid
        AND t.sid <> sc.sid
        GROUP BY sc.sid
            HAVING COUNT(1) = (
                SELECT COUNT(1)
                FROM   sc
                WHERE  sid = '2'
        ) 
);

删除学习“叶平”老师课的SC表记录；

DELETE FROM sc
    WHERE cid IN (
        SELECT cid
        FROM course c LEFT JOIN teacher t ON c.tid = t.tid 
        WHERE tname = '叶平'
    );

向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“3”课程的同学学号、2号课的平均成绩；

1 2	INSERT sc SELECT sid,'3',(Select avg(score) FROM sc WHERE cid='2') FROM student where sid NOT IN (SELECT sid FROM sc WHERE cid='3');

按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩
按如下形式显示：学生ID,语文,数学,英语,有效课程数,有效平均分。

SELECT sid '学号'
    ,(SELECT score FROM sc WHERE sc.sid = sc_2.sid AND cid = '1') '语文'
    ,(SELECT score FROM sc WHERE sc.sid = sc_2.sid AND cid = '2') '数学'
    ,(SELECT score FROM sc WHERE sc.sid = sc_2.sid AND cid = '3') '英语'
    ,count(1) '课程数' , avg(sc_2.score) '平均分'
FROM sc AS sc_2
GROUP BY sid
ORDER BY avg(sc_2.score) DESC

查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

1
2
3

SELECT cid '课程号', max(score) '最高分', min(score) '最低分'
FROM sc 
GROUP BY cid;

按各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT t1.cid,
       AVG(t1.score) AS avg_score,
       100 * SUM(
                 CASE 
                  WHEN IFNULL(t1.score,0)>=60 THEN 
                    1 
                  ELSE 
                    0 
                  END
                )/COUNT(*) AS pass_pct
    FROM sc t1 
    GROUP BY t1.cid
    ORDER BY avg_score ASC,pass_pct DESC

查询如下课程平均成绩和及格率的百分数(用”1行”显示): 语文（1），数学（2），英语（3），物理（4）

SELECT SUM(CASE WHEN Cid ='1' THEN score ELSE 0 END)/SUM(CASE Cid WHEN '1' THEN 1 ELSE 0 END) AS 语文平均分
        ,100 * SUM(CASE WHEN Cid = '1' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN Cid = '1' THEN 1 ELSE 0 END) AS 语文及格百分数 
        ,SUM(CASE WHEN Cid = '2' THEN score ELSE 0 END)/SUM(CASE Cid WHEN '2' THEN 1 ELSE 0 END) AS 数学平均分 
        ,100 * SUM(CASE WHEN Cid = '2' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN Cid = '2' THEN 1 ELSE 0 END) AS 数学及格百分数 
        ,SUM(CASE WHEN Cid = '3' THEN score ELSE 0 END)/SUM(CASE Cid WHEN '3' THEN 1 ELSE 0 END) AS 英语平均分 
        ,100 * SUM(CASE WHEN Cid = '3' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN Cid = '3' THEN 1 ELSE 0 END) AS 英语及格百分数 
        ,SUM(CASE WHEN Cid = '4' THEN score ELSE 0 END)/SUM(CASE Cid WHEN '4' THEN 1 ELSE 0 END) AS 物理平均分 
        ,100 * SUM(CASE WHEN Cid = '4' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN Cid = '4' THEN 1 ELSE 0 END) AS 物理及格百分数 
  FROM SC

查询不同老师所教不同课程平均分从高到低显示

SELECT t3.tname '教师姓名'
        ,t2.cname AS '课程名称'
        ,AVG(t1.score) AS '平均成绩'
FROM sc t1
INNER JOIN course t2
ON t1.cid = t2.cid
INNER JOIN teacher t3
ON t2.tid = t3.tid
GROUP BY t3.tname,t2.cname
ORDER BY 平均成绩 DESC

查询如下课程成绩第 3 名到第 6 名的学生成绩单：

语文（1），数学（2），英语 （3），物理（4） [学生ID],[学生姓名],语文,数学,英语,物理,平均成绩

SELECT sub_query.cname,
       sub_query.rank   
FROM(
      SELECT t2.cname,
             t1.score,
             rank() over(PARTITION BY t1.cid ORDER BY t1.score DESC) AS rank  
      FROM sc t1
      INNER JOIN course t2
        ON t1.cid = t2.cid
      WHERE t1.cid IN (1,2,3,4)
    ) sub_query
WHERE sub_query.rank BETWEEN 3 AND 6;

统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

SELECT t1.cid AS '课程ID',
         t2.cname AS '课程名称',
         SUM(CASE WHEN t1.score > 85 AND t1.score <= 100 THEN 1 ELSE 0 END) AS '100-85',
         SUM(CASE WHEN t1.score > 70 AND t1.score <= 85 THEN 1 ELSE 0 END)  AS '85-70',
         SUM(CASE WHEN t1.score > 60 AND t1.score <= 70 THEN 1 ELSE 0 END)  AS '70-60',
         SUM(CASE WHEN t1.score <= 60 THEN 1 ELSE 0 END)                    AS ' <60'
FROM sc t1
INNER JOIN course t2
  ON t1.cid = t2.cid
GROUP BY t1.cid,t2.cname

查询学生平均成绩及其名次

SELECT t1.sname,
         t1.sid,
         AVG(t2.score) AS avg_score
        -- dense_rank() over(ORDER BY AVG(t2.score) DESC) AS dense_rank
FROM student t1
INNER JOIN sc t2
  ON t1.sid = t2.sid
GROUP BY t1.sname,t1.sid

查询各科成绩前三名的记录:(不考虑成绩并列情况)

SELECT sub_query.cname,
         sub_query.sid,
         sub_query.sname,
         sub_query.score
         -- sub_query.rank
FROM ( 
       SELECT t3.cname,
              t1.sid,
              t1.sname,
              t2.score,
              -- rank() over(PARTITION BY t2.cid ORDER BY t2.score DESC) AS rank
       FROM student t1
       INNER JOIN sc t2
         ON t1.sid = t2.sid
       INNER JOIN course t3
         ON t2.cid = t3.cid
      ) sub_query
WHERE sub_query.rank < 4

Sid	Cid	score
1	1	56
1	2	78
1	3	67
1	4	58
2	1	79
2	2	81
2	3	92
2	4	68
3	1	91
3	2	47
3	3	88
3	4	56
4	2	88
4	3	90
4	4	93
5	1	46
5	3	78
5	4	53
6	1	35
6	2	68
6	4	71

Sid	Cid	score
1	1	56
1	2	78
1	3	67
1	4	58
2	1	79
2	2	81
2	3	92
2	4	68
3	1	91
3	2	47
3	3	88
3	4	56
4	2	88
4	3	90
4	4	93
5	1	46
5	3	78
5	4	53
6	1	35
6	2	68
6	4	71

Sid	Cid	score
1	1	56
1	2	78
1	3	67
1	4	58
2	1	79
2	2	81
2	3	92
2	4	68
3	1	91
3	2	47
3	3	88
3	4	56
4	2	88
4	3	90
4	4	93
5	1	46
5	3	78
5	4	53
6	1	35
6	2	68
6	4	71