mysql 查询

Student(Sid,Sname,Sage,Ssex) 学生表 Sid：学号；Sname：学生姓名；Sage：学生年龄；Ssex：学生性别
Course(Cid,Cname,Tid) 课程表 Cid,课程编号；Cname：课程名字；Tid：教师编号
SC(Sid,Cid,score) 成绩表 Sid：学号；Cid,课程编号；score：成绩
Teacher(Tid,Tname) 教师表 Tid：教师编号； Tname：教师名字

1、查询1课程比2课程成绩高的所有学生的学号；

SELECT
    a.Sid
FROM
    (SELECT Sid,score FROM sc WHERE Cid = 1) a,
    (SELECT Sid,score FROM sc WHERE Cid = 2) b
WHERE
    a.score > b.score
AND a.Sid = b.SId;

需求：查询学号

条件：比较成绩（同一学生）

对同一张表进行内连接查询

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

SELECT
    Sid,
    AVG(score)
FROM
    sc
GROUP BY
    Sid
    HAVING AVG(score) > 60;

需求：查询学号和平均成绩（定位主查成绩表）

条件：比较平均成绩

按学号分组查询平均成绩，注意涉及到分组字段条件关键字用HAVING，其他字段条件在分组前用where

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

SELECT
    student.Sid,
    student.Sname,
    COUNT(sc.Cid),
    SUM(sc.score)
FROM
    student
LEFT JOIN sc ON student.Sid = sc.Sid
GROUP BY student.Sid,student.Sname

需求：查询学生信息，成绩信息（定位学生表、成绩表）

条件：无

count函数，sum函数，学生id，学生name，通过对学生id，学生name分组，学生表左链接成绩表查出相关信息

4、查询姓“语”的老师的个数；

SELECT
    COUNT(teacher.Tid)
FROM
    teacher
GROUP BY
    teacher.Tid,teacher.Tname
HAVING
    teacher.Tname LIKE '语%';


SELECT
    COUNT(DISTINCT(Tname))
FROM
    teacher
WHERE
    Tname LIKE '语%';

需求：求教师的个数（教师表，聚合函数count）

条件：姓‘语’（like ‘语%’）

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

SELECT
    COUNT(teacher.Tid)
FROM
    teacher
WHERE teacher.Tname="语文老师"
GROUP BY
    teacher.Tid
HAVING
    teacher.Tid = 1

5、查询没学过“语文老师”课的同学的学号、姓名

SELECT
    student.Sid,
    student.Sname
FROM
    student
WHERE
    student.Sid NOT IN (
        SELECT
            DISTINCT(sc.Sid)
        FROM
            sc,
            teacher,
            course
        WHERE
            sc.Cid = course.Cid
        AND teacher.Tid = course.Tid
        AND teacher.Tid = "语文老师"
    );

需求：查询学生信息（学生表，教师表，课程表，成绩表-关联学生）

条件：没学过语文老师的课程（没... not in）学过语文老师的课程

6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；

SELECT
    student.Sid,
    student.Sname
FROM
    student,
    sc
WHERE
    student.Sid = sc.Sid
AND sc.Cid = 1
AND EXISTS (
    SELECT
        *
    FROM
        sc AS sc2
    WHERE
        sc2.Sid = sc.Sid
    AND sc2.Cid = 2
)

需求：查询学生信息（学生表，成绩表）

条件：学过“1”并且也学过编号“2”课程（不用查主表，应为条件为id所以关联表成绩表，同一字段两个条件：条件1 and exists（条件2 and 表1条件字段=表2条件字段））

7、查询学过“语文老师”所教的所有课的同学的学号、姓名；

SELECT
    student.Sid,
    student.Sname
FROM
    student
WHERE
    student.Sid IN (
        SELECT
            sc.Sid
        FROM
            sc,
            course,
            teacher
        WHERE
            sc.Cid = course.Cid
        AND course.Tid = teacher.Tid
        AND teacher.Tname = "语文老师"
        GROUP BY
            sc.Sid
        HAVING
            COUNT(sc.Cid) = (
                SELECT
                    COUNT(course.Cid)
                FROM
                    course,
                    teacher
                WHERE
                    course.Tid = teacher.Tid
                AND teacher.Tname = "语文老师"
            )
);

需求：查询学生信息（学生表，教师表，成绩表，课程表）

条件：学过“语文老师”所教的所有课，学过语文老师的课+学过语文老师的所有课（数据表id in 内连接条件1 分组条件2 count 内连接条件1）

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


SELECT
    student.Sid,
    student.Sname
FROM
    student,
    (
        SELECT
            sc.Sid,
            sc.score
        FROM
            sc
        WHERE
            sc.Cid = 1
    ) a1,
    (
        SELECT
            sc.Sid,
            sc.score
        FROM
            sc
        WHERE
            sc.Cid = 2
    ) a2
WHERE
    student.Sid = a1.Sid
AND a1.Sid = a2.Sid
AND a1.score > a2.score;



SELECT
    ret.Sid,
    ret.Sname
FROM
    (
        SELECT
            student.Sid,
            student.Sname,
            sc.score,
            (
                SELECT
                    sc2.score
                FROM
                    sc sc2
                WHERE
                    sc2.Sid = student.Sid
                AND sc2.Cid = 2
            ) score2
        FROM
            student,
            sc
        WHERE
            student.Sid = sc.Sid
        AND sc.Cid = 1
    ) ret
WHERE
    ret.score2 < ret.score

需求：查询学生信息（学生表，成绩表）

条件：课程1成绩比课程2成绩高

课程1的成绩比课程2的成绩作比较，因为中间表成绩表中有课程id和学生id所以不会用到课程表，主查询表为学生表，同一学生不同课程成绩的比较。

不同课程的成绩可从中间表查询学生id和比较的分数其结果作为中间表a1，同样中间表a2，两表和主表内联查询，最后where作比较

一个中间表既包括学生信息，又包括两个分数信息，但是第二个分数需要做子查询和主表关联。最后条件比较两个分数

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

SELECT
    student.Sid,
    student.Sname
FROM
    student
WHERE
    student.Sid NOT IN (
        SELECT DISTINCT
            (student1.Sid)
        FROM
            sc,
            student student1
        WHERE
            sc.Sid = student1.Sid
        AND sc.score > 60
    )

需求：查询学生信息（学生表，成绩表）

条件：所有课程成绩小于60

说明：所有小于 = 不大于，所以要用 not in 大于一个数的查询用来简化条件

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

SELECT
    student.Sid,
    student.Sname
FROM
    student,
    sc
WHERE
    student.Sid = sc.Sid
GROUP BY
    student.Sid,
    student.Sname
HAVING
    COUNT(sc.Cid) < (
        SELECT
            COUNT(course.Cid)
        FROM
            course
    )

需求：查询学生信息（学生表，课程表，成绩表）

条件：没有学全所有课

说明：所有课和学完了的课做比较，连个数量比较，属于一个人的两个聚合关系的比较，所以要按人的其他非聚合信息分组，在having中比较聚合关系

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

SELECT DISTINCT
    student.Sid,
    student.Sname
FROM
    student,
    (SELECT sc.Sid, sc.Cid FROM sc) sc1,
    (
        SELECT
            sc2.Sid,
            sc2.Cid
        FROM
            sc sc2
        WHERE
            sc2.Sid = 1
    ) sc3
WHERE
    student.Sid = sc1.Sid
AND sc1.Cid = sc3.Cid;

SELECT
    DISTINCT
    student.Sid,
    student.Sname
FROM
    student,
    sc
WHERE
    student.Sid = sc.Sid
AND sc.Cid IN (
    SELECT
        sc1.Cid
    FROM
        sc sc1
    WHERE
        sc1.Sid = 1
);

需求：查询学生信息（学生表，成绩表）

条件：至少有一门课与学号为“1”的同学所学课程相同

说明：主表为学生表，主表id作为关联，单条数据中的课程id in 学号为1所学课程的id，或者子查询所有课程和学号1所学课程内连接，需要注意的是结果笛卡尔积，需要使用distinct去重

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

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

UPDATE sc
INNER JOIN (
    SELECT
        a.*
    FROM
        (
            SELECT
                sc2.Cid,
                AVG(sc2.score) average
            FROM
                sc sc2
            GROUP BY
                sc2.Cid
        ) a,
        teacher,
        course
    WHERE
        a.Cid = course.Cid
    AND teacher.Tid = course.Tid
    AND teacher.Tname = "语文老师"
) AS b ON sc.Cid = b.Cid
SET sc.score = b.average

UPDATE sc
SET sc.score = (
	SELECT
		AVG(sc1.score)
	FROM
		sc AS sc1,
		course AS course1,
		teacher AS teacher1
	WHERE
		sc1.Cid = course1.Cid
	AND course1.Tid = teacher1.Tid
	AND teacher1.Tname = "语文老师"
)
WHERE
	sc.Cid IN (
		SELECT
			course.Cid
		FROM
			course,
			teacher
		WHERE
			course.Cid = teacher.Tid
		AND teacher.Tname = "语文老师"
	);
[Err] 1093 - You can't specify target table 'sc' for update in FROM clause

需求：批量修改某老师课程的成绩表中成绩为此课程的平均成绩

条件：语文老师所教的课

说明：批量修改一般使用set 属性=值 where id in 数组的操作，但是这样做得在编程中将条件查询成常量，在进行二次查询，否则出现上述错误，在子查询中不能用被更新的表。mysql 中可以使用update a join b on a.id=b.id set a.name=b.name where a.m='...' and b.n='...'

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

SELECT
    student.Sid,
    student.Sname,
    COUNT(sc.Cid)
FROM
    student,
    sc
WHERE
    student.Sid = sc.Sid AND sc.Cid IN (SELECT Cid FROM sc WHERE Sid = 1)
GROUP BY
    Sid,Sname
HAVING
    COUNT(1) = (
        SELECT
            COUNT(1)
        FROM
            sc
        WHERE
            Sid = 1
    )

需求：查询学生信息（学生表，成绩表）

条件：和同学1的课程完全相同的其他学生的信息

说明：学生1的课程信息 = 其他学生的信息。这个条件进行拆分：其他同学的课程id in 学生1的课程id && 其他同学的课程count = 学生1的课程数量

15、删除学习“语文老师”课的SC表记录；

DELETE
FROM
    sc
WHERE
    sc.Cid IN (
        SELECT
            sc1.Cid
        FROM
            sc sc1,
            course,
            teacher
        WHERE
            sc1.Cid = course.Cid
        AND course.Tid = teacher.Tid
        AND teacher.Tname = "语文老师"
    );

[Err] 1093 - You can't specify target table 'sc' for update in FROM claus

DELETE sc
FROM
    sc,
    course,
    teacher
WHERE
    sc.Cid = course.Cid
AND course.Tid = teacher.Tid
AND teacher.tName = "语文老师"
AND sc.score = 33;

需求：删除成绩表记录（成绩表，老师表）

条件：学习过语文老师课的成绩

说明：修改操作不能再子查询中使用被修改的表，所以第一种写法错误，第二种写法中delete a from a,b,c .... 这里被删除的a必须在from后边有出现过，否则语法错误

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

INSERT Sid,1,(SELECT AVG( score) FROM sc WHERE Cid=1) FROM student WHERE Sid NOT IN(SELECT Sid FROM sc WHERE Cid=1)

需求：插入成绩信息（学生表，成绩表）

条件：没上过课程3的学生，平均成绩。先查出课程，按照课程id程批量插入。主查询,子查询 + not in

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

SELECT
    t.Sid AS '学生id',(
        SELECT
            score
        FROM
            sc
        WHERE
            Sid = t.Sid
        AND Cid = 1
    ) AS '语文成绩',
    (
        SELECT
            score
        FROM
            sc
        WHERE
            Sid = t.Sid
        AND Cid = 2
    ) AS '数学成绩',
    (
        SELECT
            score
        FROM
            sc
        WHERE
            Sid = t.Sid
        AND Cid = 3
    ) AS '英语成绩',
    COUNT(1) AS '有效课程数',
    AVG( t.score) AS '平均成绩'
FROM
    sc AS t
GROUP BY
    t.Sid
ORDER BY
    AVG(t.score)

需求：学生ID,语文,数学,英语,有效课程数,有效平均分（sc）

条件：按平均成绩从高到低显示，主查询中课程id作为子查询的条件来查各课程的成绩，最后主查询按照学生id分组，按照平均成绩排序

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

SELECT
    Cid AS '课程id',
    MAX(score) AS '最高成绩',
    MIN(score) AS '最低成绩'
FROM
    sc
GROUP BY
    Cid;

SELECT
    Cid AS '课程id',
    MAX(score) AS '最高成绩',
    MIN(score) AS '最低成绩'
FROM
    sc,student WHERE sc.Sid = student.Sid
GROUP BY
    Cid;



SELECT
    DISTINCT
    l.Cid,
    l.score,
    r.score
FROM
    sc l,
    sc r
WHERE
    l.Cid = r.Cid
AND l.score = (
    SELECT
        MAX(ll.score)
    FROM
        sc ll,
        student lm
    WHERE
        l.Cid = ll.Cid
    AND lm.Sid = ll.Sid
    GROUP BY
        ll.Cid
)
AND r.score = (
    SELECT
        MIN(lr.score)
    FROM
        sc AS lr
    WHERE
        r.Cid = lr.Cid
    GROUP BY
        lr.Cid
);

需求：查询各科成绩最高和最低的分（sc,student）

条件：

说明：各科说明按课程id分组，最低分，最高分都在成绩表中，所以使用max，min可以直接查出，同时也可以自关联通过条件子查询max，min进行等于比较进行条件判断来查询相关信息。

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

SELECT
    sc.Cid,
    course.Cname,
    AVG(sc.score) AS '平均成绩',
    100 * SUM(
        CASE
        WHEN IFNULL(sc.score, 0) >= 60 THEN
            1
        ELSE
            0
        END
    ) / COUNT(1) AS '及格百分比'
FROM
    sc,
    course
WHERE
    sc.Cid = course.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(1)
 DESC

需求：查询课程id，课程名称，平均成绩，及格百分数（成绩表，课程表）

条件：各科平均成绩正序，及格率倒序

说明：成绩表关联课程表，按照课程id，课程名称分组，及格百分比通过及格课程数/课程总数获得 case when else end, ifnull

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

需求：查询各课程平均成绩，及格率

条件：一条记录查询各课程平均成绩，及格率

说明：查询两个字段，两个函数计算结果得出一条记录，所以都用子查询，以主表某字段作为子查询条件，%可以说使用concat ( left (数值1 / 数值2 *100,5),'%')先截取字符串，在链接字符串，同时截取字符串可以替换为四舍五入等操作

SELECT
    SUM(
        CASE
        WHEN Cid = 1 THEN
            score
        ELSE
            0
        END
    ) / SUM(CASE WHEN Cid = 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) '语文及格率',
    SUM(
        CASE
        WHEN Cid = 2 THEN
            score
        ELSE
            0
        END
    ) / SUM(CASE WHEN Cid = 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) '数学及格率',
    SUM(
        CASE
        WHEN Cid = 3 THEN
            score
        ELSE
            0
        END
    ) / SUM(CASE WHEN Cid = 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) '英语及格率'
FROM sc

SELECT
    SUM(
        CASE
        WHEN Cid = 1 THEN
            score
        ELSE
            0
        END
    ) / SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0 END) AS '语文平均分',
    CONCAT(
        LEFT (
            100 * SUM(
                CASE
                WHEN Cid = 1
                AND score >= 60 THEN
                    1
                ELSE
                    0
                END
            ) / SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0 END),
            5
        ),
        '%'
    ) '语文及格率',
    SUM(
        CASE
        WHEN Cid = 2 THEN
            score
        ELSE
            0
        END
    ) / SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0 END) AS '数学平均分',
    CONCAT(
        LEFT (
            100 * SUM(
                CASE
                WHEN Cid = 2
                AND score >= 60 THEN
                    1
                ELSE
                    0
                END
            ) / SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0 END),
            5
        ),
        '%'
    ) '数学及格率',
    SUM(
        CASE
        WHEN Cid = 3 THEN
            score
        ELSE
            0
        END
    ) / SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0 END) AS '英语平均分',
    CONCAT(
        LEFT (
            100 * SUM(
                CASE
                WHEN Cid = 3
                AND score >= 60 THEN
                    1
                ELSE
                    0
                END
            ) / SUM(CASE WHEN Cid = 1 THEN 1 ELSE 0 END),
            5
        ),
        '%'
    ) '英语及格率'
FROM
    sc

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

需求：查询老师，课程，分数(teacher,sc,course)

条件：不同老师，不同课程，平均分，倒序

说明：不同老师，按老师分组，不同课程，按课程分组，平均分 avg，倒序desc 老师为条件，主要结果为平均分，所以主表为sc

SELECT
    teacher.Tname,
    course.Cname,
    AVG(sc.score)
FROM
    sc,
    course,
    teacher
WHERE
    sc.Cid = course.Cid
AND course.Tid = teacher.Tid
GROUP BY
    course.Tid,
    sc.Cid
ORDER BY
    AVG(sc.score) DESC;

SELECT
    MAX(z.Tname),
    MAX(c.Cname),
    AVG(t.score)
FROM
    sc t,
    course c,
    teacher z
WHERE
    t.Cid = c.Cid
AND c.Tid = z.Tid
GROUP BY
    c.Cid
ORDER BY
    AVG(t.score) DESC

22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：语文（1），数学（2），英语（3）[学生ID],[学生姓名],语文，数学，英语，平均成绩

需求：（课程表，成绩表，学生表）

条件：课程成绩第 3 名到第 4名的学生成绩单

说明：课程成绩从第x名到第y名 order by 总成绩，同一个人多列成绩转变为一个人一列多课程成绩，这样的查询可以自关联已课程iD为条件，这里avgScore 不能复用totalScore/4进行计算

SELECT DISTINCT
    sc.Sid,
    student.Sname,
    sc1.score AS '语文',
    sc2.score AS '数学',
    sc3.score AS '英语',
    sc4.score AS '政治',
    (
        IFNULL(sc1.score, 0) + IFNULL(sc2.score, 0) + IFNULL(sc3.score, 0) + IFNULL(sc4.score, 0)
    ) AS totalScore,
    (
        IFNULL(sc1.score, 0) + IFNULL(sc2.score, 0) + IFNULL(sc3.score, 0) + IFNULL(sc4.score, 0)
    )/4 AS avgScore
FROM
    student,
    sc
LEFT JOIN sc AS sc1 ON sc.Sid = sc1.Sid
AND sc1.Cid = 1
LEFT JOIN sc AS sc2 ON sc.Sid = sc2.Sid
AND sc2.Cid = 2
LEFT JOIN sc AS sc3 ON sc.Sid = sc3.Sid
AND sc3.Cid = 3
LEFT JOIN sc AS sc4 ON sc.Sid = sc4.Sid
AND sc4.Cid = 4
WHERE
    student.Sid = sc.Sid
ORDER BY
    totalScore
LIMIT 2,
 2;

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

需求：课程信息，分数信息（课程表，分数表）

条件：各科成绩

说明：统计if（条件）个数等信息：case when else end

SELECT
    course.Cid,
    course.Cname,
    (
        SELECT
            SUM(
                CASE
                WHEN sc.score BETWEEN 85
                AND 100 THEN
                    1
                ELSE
                    0
                END
            )
        FROM
            sc
        WHERE
            sc.Cid = course.Cid
        AND sc.score
    ) AS '85-100',
    (
        SELECT
            SUM(
                CASE
                WHEN sc.score BETWEEN 70
                AND 85 THEN
                    1
                ELSE
                    0
                END
            )
        FROM
            sc
        WHERE
            sc.Cid = course.Cid
        AND sc.score
    ) AS '70-85',
    (
        SELECT
            SUM(
                CASE
                WHEN sc.score BETWEEN 60
                AND 70 THEN
                    1
                ELSE
                    0
                END
            )
        FROM
            sc
        WHERE
            sc.Cid = course.Cid
        AND sc.score
    ) AS '60-70',
    (
        SELECT
            SUM(
                CASE
                WHEN sc.score < 60 THEN
                    1
                ELSE
                    0
                END
            )
        FROM
            sc
        WHERE
            sc.Cid = course.Cid
        AND sc.score
    ) AS '<60'
FROM
    course;

SELECT
    course.Cid,
    course.Cname,
    SUM(
        CASE
        WHEN sc.score BETWEEN 85
        AND 100 THEN
            1
        ELSE
            0
        END
    ) AS '85-100',
    SUM(
        CASE
        WHEN sc.score BETWEEN 70
        AND 85 THEN
            1
        ELSE
            0
        END
    ) AS '70-85',
    SUM(
        CASE
        WHEN sc.score BETWEEN 60
        AND 70 THEN
            1
        ELSE
            0
        END
    ) AS '60-70',
    SUM(
        CASE
        WHEN sc.score < 60 THEN
            1
        ELSE
            0
        END
    ) AS '<60'
FROM
    course,
    sc
WHERE
    course.Cid = sc.Cid
GROUP BY
    course.Cid,
    course.Cname;

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

需求：查询平均成绩和名次

条件：学生的平均成绩和名次

说明：平均成绩需要按学生分组，学生名次：子查询中的分数和主查询中的分数作比较统计个数 + 1 可以作为名次

SELECT
    Sid,
    avgScore,
    (
        SELECT
            1 + COUNT(avgScore)
        FROM
            (
                SELECT
                    Sid,
                    AVG(score) AS avgScore
                FROM
                    sc
                GROUP BY
                    Sid
            ) AS t1
        WHERE
            t1.avgScore > t2.avgScore
    ) AS ranking
FROM
    (
        SELECT
            Sid,
            AVG(score) AS avgScore
        FROM
            sc
        GROUP BY
            Sid
    ) AS t2
ORDER BY
    avgScore DESC

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

需求：查询课程表（sc）

条件：各科成绩前三，不考虑并列

分析：并列是否考虑先不说，各科成绩前三，说明两个条件，各科，成绩前三，需要先查询前三的成绩，参数为课程id，因为mysql 不支持top，所以只能使用limit，又因为limit不能用在in里边，同时临时表中不能使用主表字段做比较条件，这样就给该查询造成了困难。

这里要用到一个函数EXISTS():exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当 exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录，反之如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为 false

查询sc表，已分数作为条件在查询sc表，如果子表分数大于主表分数的数量 > 1就是前一名，如果 > 2就是前2名，如果有并列都会查询出来

SELECT
    *
FROM
    sc AS t
WHERE
    EXISTS (
        SELECT
            COUNT(1)
        FROM
            sc
        WHERE
            t.Cid = sc.Cid
        AND t.score < sc.score
        HAVING
            COUNT(1) < 1
    )
ORDER BY Cid

26. 查询每门课程被选修的学生数

需求：求学生数（sc表）

条件：每门课程被选修的学生数

说明：聚合函数，学生信息，按学生分组

SELECT
    sc.Cid,
    course.Cname,
    COUNT(sc.Sid)
FROM
    sc
JOIN course ON sc.Cid = course.Cid
GROUP BY
    sc.Cid;

27、查询出只选修了一门课程的全部学生的学号和姓名

需求：学生的学号和姓名（sc,student）

条件：只选修了一门课程

说明：sc,student两个表关联，最后有个条件一门课程，就是要对sc表中的Cid使用聚合函数，但是就不能再查下学生信息，这是个误区。所以不考虑链接查询，替换情况就是查询学生表通过in函数来查询。通过查询成绩表中的学生id，按照学生id分组，一个学生id对应多个课程id，然后对课程id聚合数量作为条件查出符合条件的学生id

SELECT
    student.Sid,
    student.Sname
FROM
    student
WHERE
    student.Sid IN (
        SELECT
            sc1.Sid
        FROM
            sc AS sc1
        GROUP BY
            sc1.Sid
        HAVING
            COUNT(Cid) = 1
    );

28、查询男生、女生人数

需求：查询不同种类的学生人数（student）

条件：不同种类的学生人数

说明：同一表，同一查询（不同条件）多次查询

SELECT
    COUNT(student.Ssex)
FROM
    student
GROUP BY
    student.Ssex
HAVING
    student.Ssex = 1;

SELECT
    COUNT(student.Ssex)
FROM
    student
GROUP BY
    student.Ssex
HAVING
    student.Ssex = 0;

29、查询姓“张”的学生名单

SELECT
    Sname
FROM
    student
WHERE
    Sname LIKE '%张';

30、查询同名同性学生名单，并统计同名人数

说明：相同姓名，即要按照姓名分组。同名人数，即人数大于1

SELECT
    Sname,
    COUNT(Sname)
FROM
    student
GROUP BY
    Sname
HAVING
    COUNT(*) > 1;

31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

说明：日期转换，date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') 20080808222301

SELECT
    DATE_FORMAT(NOW(), '%Y') AS age,
    Sname
FROM
    student
WHERE
    DATE_FORMAT(NOW(), '%Y') = 2018;

32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

说明：升序ASC，降序DESC

SELECT
	Cid,AVG(score)
FROM
	sc
GROUP BY
	Cid
ORDER BY
	AVG(score),Cid DESC

33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

说明：分组查询，条件使用having

SELECT
    student.Sid,
    student.Sname,
    AVG(sc.score)
FROM
    student
INNER JOIN sc ON student.Sid = sc.Sid
GROUP BY student.Sid,student.Sname
HAVING
    AVG(sc.score) > 85

SELECT
    Sname,
    sc.Sid,
    AVG(score)
FROM
    student,
    sc
WHERE
    student.Sid = sc.Sid
GROUP BY
    sc.Sid,
    Sname
HAVING
    AVG(score) > 85;

34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数

SELECT
    student.Sname,
    IFNULL(sc.score,0)

FROM
    student,
    sc,
    course
WHERE
    student.Sid = sc.Sid
AND sc.Cid = course.Cid
AND sc.score < 60
AND course.Cname = "政治";
//这里由于sc表中score不能为null，并且是全连接所以ifnull无意义


SELECT
    student.Sname,
    IFNULL(sc.score, 0)
FROM
    student
LEFT JOIN sc ON student.Sid = sc.Sid
LEFT JOIN course ON sc.Cid = course.Cid
WHERE
    sc.score < 60
AND course.Cname = "政治";

//这里由于student左链接其他两个表，所以ifnull给null值赋默认值

35、查询所有学生的选课情况；

说明：如果为null给默认值ifnull

SELECT
    sc.Sid,
    student.Sname,
    sc.Cid,
    course.Cname
FROM
    sc,
    student,
    course
WHERE
    sc.Sid = student.Sid
AND sc.Cid = course.Cid;

SELECT
    student.Sid,
    student.Sname,
    IFNULL(course.Cid,-1),
    IFNULL(course.Cname,"未选课程")

FROM student LEFT JOIN sc ON student.Sid = sc.Sid LEFT JOIN course ON sc.Cid = course.Cid

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

说明：只要有一门课程在70分以上，没有任何一门课程在70分以上，所有课程在70分以上

//只要有一门课程在70分以上-满足条件即可
SELECT DISTINCT
    student.Sid,
    student.Sname,
    sc.Cid,
    sc.score
FROM
    student,
    sc
WHERE
    student.Sid = sc.Sid
AND sc.score >= 70;

//所有课程在70分以下，多转一，取反
SELECT DISTINCT
    student.Sid,
    student.Sname
FROM
    student
LEFT JOIN sc ON student.Sid = sc.Sid
WHERE
    student.Sid NOT IN (
        SELECT
            student.Sid
        FROM
            student
        LEFT JOIN sc ON student.Sid = sc.Sid
        WHERE
            sc.score >= 70
    )

//所有课程在70分以上，多转一，取反
SELECT DISTINCT
    student.Sid,
    student.Sname
FROM
    student
LEFT JOIN sc ON student.Sid = sc.Sid
WHERE
    student.Sid NOT IN (
        SELECT
            student.Sid
        FROM
            student
        LEFT JOIN sc ON student.Sid = sc.Sid
        WHERE
            sc.score < 70
    )

A表左链接B表，以A表为主，如果查询字段有B表字段那么A表中没有关联B表的关联关系，那么结果中就会出现一行A字段为没有，B字段为null的列

37、查询不及格的课程，并按课程号从大到小排列

说明：倒序desc

SELECT sc.Cid FROM sc WHERE sc.score < 60 ORDER BY sc.Cid DESC

38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；

SELECT
    sc.Sid,
    student.Sname
FROM
    student,
    sc
WHERE
    student.Sid = sc.Sid
AND sc.Cid = '1'
AND sc.score > 80

39、求选了课程的学生人数

说明：多级查询

SELECT COUNT(1) FROM (SELECT COUNT(sc.Sid) FROM sc GROUP BY sc.Sid) AS scount

40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩

说明：成绩最高，通过子查询查询出最高成绩在和成绩形成等于关系

SELECT
    sc.Sid,
    student.Sname,
    sc.score
FROM
    sc,
    student,
    course,
    teacher
WHERE
    sc.Sid = student.Sid
AND sc.Cid = course.Cid
AND course.Tid = teacher.Tid
AND teacher.Tname = "语文老师"
AND sc.score = (
    SELECT
        MAX(sc.score)
    FROM
        sc,
        teacher,
        course
    WHERE
        sc.Cid = course.Cid
    AND course.Tid = teacher.Tid
    AND teacher.Tname = "语文老师"
);

子查询中可以使用父查询表中的字段，子查询中查询一个字段，不用给结果as别名

SELECT
    sc.Sid,
    student.Sname,
    sc.score
FROM
    sc,
    student,
    course,
    teacher
WHERE
    sc.Sid = student.Sid
AND sc.Cid = course.Cid
AND course.Tid = teacher.Tid
AND teacher.Tname = "语文老师"
AND sc.score = (
    SELECT
        MAX(sc1.score)
    FROM
        sc sc1
    WHERE
 sc1.Cid = sc.Cid
);

41、查询各个课程及相应的选修人数

说明：按照课程id，课程名称分组查询选同一门课程的人数

SELECT
    course.Cid,
    course.Cname,
    COUNT(sc.Sid)
FROM
    sc
LEFT JOIN course ON sc.Cid = course.Cid
LEFT JOIN student ON sc.Sid = student.Sid
GROUP BY sc.Cid

SELECT COUNT(1) FROM sc GROUP BY Cid

42、查询不同课程成绩相同的学生的学号、课程号、学生成绩

说明：所有条件和查询结果都在一张表中，可以自关联来满足多个条件

SELECT
    a.Sid,
    b.score
FROM
    sc AS a,
    sc AS b
WHERE
    a.score = b.score
AND a.Cid != b.Cid

43、查询每门课程成绩最好的前两名

说明：条件拆分：每门课程 + 成绩前两名，正常请求下如果说每门课程就要按照课程分组了，但是这里的后续条件前两名，就不能按照课程分组了，结果会是一门课程多条数据的一个集合。所以重点条件是第二个条件：前两名，所以成绩表里的每一条数据和成绩表里的每一条数据作比较来求数量，条件为课程id相同，且条件查询分数大于主查询分数，这个数量<=1就是前二名，因为如果相当于自关联，这个数量的值为0，那么这门课程就一个人，如果这个数量是1，那么说明同一门课比他分数高的有一人，那么他就是第二名。当然，这个查询是包含并列的。比我成绩高有一个人，我俩就是前二名。

SELECT
    sc1.Sid,
    sc1.Cid,
    sc1.score
FROM
    sc AS sc1
WHERE
    score IN (
        SELECT
            sc2.score
        FROM
            sc AS sc2
        WHERE sc1.score = sc2.score
        ORDER BY sc2.score DESC
        LIMIT 2
        
    )

[Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

包含并列成绩
SELECT
    sc1.Sid,
    sc1.Cid,
    sc1.score
FROM
    sc AS sc1
WHERE
    (
        SELECT
            COUNT(1)
        FROM
            sc AS sc2
        WHERE
            sc2.Cid = sc1.Cid
        AND sc2.score > sc1.score
    ) <= 1
ORDER BY sc1.Cid

44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

说明：在成绩表中查询每门课程输出一行，典型的分组问题。

SELECT
    sc.Cid,
    COUNT(sc.Sid)
FROM
    sc
GROUP BY
    sc.Cid
HAVING
    COUNT(sc.Sid) > 2
ORDER BY
    COUNT(sc.Sid) DESC,
    sc.Cid ASC

SELECT
    sc.Cid,
    COUNT(1)
FROM
    sc
GROUP BY
    sc.Cid
HAVING
    COUNT(1) > 2
ORDER BY
    COUNT(1) DESC,
    sc.Cid ASC

45、检索至少选修两门课程的学生学号

说明：至少选两门课程，所选课程数大于1，查询学号，按照学号分组，课程数大于1为条件

SELECT
    sc.Sid
FROM
    sc
GROUP BY
    sc.Sid
HAVING
    COUNT(1) > 3

46、查询全部学生选修的课程的课程号和课程名

说明：全部学生id集合作为条件查询成绩表

SELECT
    Cid,
    Cname
FROM
    course
WHERE
    Cid IN (SELECT Cid FROM sc GROUP BY Cid)

47、查询没学过“叶平”老师讲授的任一门课程的学生姓名

说明：没有。。。 = not in 。。。

SELECT
    Sid,
    Sname
FROM
    student
WHERE
    Sid NOT IN (
        SELECT
            Sid
        FROM
            sc,
            course,
            teacher
        WHERE
            sc.Cid = course.Cid
        AND course.Tid = teacher.Tid
        AND teacher.Tname = "语文老师"
    )

48、查询两门以上不及格课程的同学的学号及其平均成绩

说明：不及格成绩>2的人

SELECT
    sc1.Sid,
    AVG(sc1.score)
FROM
    sc sc1
GROUP BY
    sc1.Sid
HAVING
    (
        SELECT
            count(1)
        FROM
            sc AS sc2
        WHERE
            sc1.Sid = sc2.Sid
        AND sc2.score < 60
    ) > 2;

SELECT
    Sid,
    AVG(score)
FROM
    sc
WHERE
    Sid IN (
        SELECT
            Sid
        FROM
            sc
        WHERE
            score < 60
        GROUP BY
            Sid
        HAVING
            COUNT(*) > 2
    )
GROUP BY Sid

49、检索“004”课程分数小于60，按分数降序排列的同学学号

SELECT
    Sid
FROM
    sc
WHERE
    Cid = 2
AND score < 60
ORDER BY
    score DESC;

50、删除“002”同学的“001”课程的成绩

DELETE
FROM
    sc
WHERE
    Sid = 1
AND Cid = 1

posted @ 2018-08-15 20:11 yangfei969 阅读(634) 评论(0) 编辑收藏举报

刷新页面返回顶部

yangfei969

mysql 查询

公告