sql题目

表结构

# Student 学生表
CREATE TABLE Student
(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL,
s_birth VARCHAR(20) NOT NULL, 
s_sex VARCHAR(10) NOT NULL,
PRIMARY KEY(s_id)
);

INSERT INTO Student VALUES('01', '赵雷', '1990-01-01', '男');
INSERT INTO Student VALUES('02', '钱电', '1990-12-21', '男');
INSERT INTO Student VALUES('03', '孙风', '1990-05-20', '男');
INSERT INTO Student VALUES('04', '李云', '1990-08-06', '男');
INSERT INTO Student VALUES('05', '周梅', '1991-12-01', '女');
INSERT INTO Student VALUES('06', '吴兰', '1992-03-01', '女');
INSERT INTO Student VALUES('07', '郑竹', '1989-07-01', '女');
INSERT INTO Student VALUES('08', '王菊', '1990-01-20', '女');


# Course 课程表
CREATE TABLE Course
(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL,
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);

INSERT INTO Course VALUES('01', '语文', '02');
INSERT INTO Course VALUES('02', '数学', '01');
INSERT INTO Course VALUES('03', '英语', '03');


# Teacher 教师表
CREATE TABLE Teacher
(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);

INSERT INTO Teacher VALUES('01', '张三');
INSERT INTO Teacher VALUES('02', '李四');
INSERT INTO Teacher VALUES('03', '王五');


# Score 分数表
CREATE TABLE Score
(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id, c_id)  # 注意这里是联合主键
);

INSERT INTO Score VALUES('01', '01', 80);
INSERT INTO Score VALUES('01', '02', 90);
INSERT INTO Score VALUES('01', '03', 99);
INSERT INTO Score VALUES('02', '01', 70);
INSERT INTO Score VALUES('02', '02', 60);
INSERT INTO Score VALUES('02', '03', 80);
INSERT INTO Score VALUES('03', '01', 80);
INSERT INTO Score VALUES('03', '02', 80);
INSERT INTO Score VALUES('03', '03', 80);
INSERT INTO Score VALUES('04', '01', 50);
INSERT INTO Score VALUES('04', '02', 30);
INSERT INTO Score VALUES('04', '03', 20);
INSERT INTO Score VALUES('05', '01', 76);
INSERT INTO Score VALUES('05', '02', 87);
INSERT INTO Score VALUES('06', '01', 31);
INSERT INTO Score VALUES('06', '03', 34);
INSERT INTO Score VALUES('07', '02', 89);
INSERT INTO Score VALUES('07', '03', 98);

# 四张表
SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM Teacher;
SELECT * FROM Score;

题目

查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号和成绩

SELECT
    *
FROM
    student
JOIN(
    SELECT
        s1.s_id,
        s1.c_id course1,
        s1.s_score score1,
        s2.c_id course2,
        s2.s_score score2
    FROM
        score s1
    JOIN score s2 ON
        s1.s_id = s2.s_id AND s1.c_id = "01" AND s2.c_id = "02" AND s1.s_score > s2.s_score
) score_temp
ON
    student.s_id = score_temp.s_id    

题解说明

因为需要获得01课题比02课题的成绩高，说明需要成绩表关联成绩表才能对比出来 (from score join score)

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

SELECT
    s_id,
    AVG(s_score) avg_score
FROM
    score
GROUP BY
    s_id
HAVING
    avg_score > 60

题解说明

从题解可以看出sql解析是先group by 后触发函数AVG()再执行HAVING

所有成绩小于60分的学生信息

s_id not in 的方法

SELECT
    *
FROM
    student
WHERE
    s_id NOT IN(
    SELECT
        s_id
    FROM
        score
    WHERE
        s_score >= 60
)

max group by方法 【这个方法如果有未考试的人会筛选不出来】

SELECT
    *
FROM
    student
JOIN(
    SELECT
        s_id,
        MAX(s_score) max_score
    FROM
        score
    GROUP BY
        s_id
    HAVING
        max_score < 60
) score_temp
ON
    score_temp.s_id = student.s_id

查询平均成绩小于60分的学生的学号和平均成绩，考虑没参加考试的情况

不考虑未参加考试的人

SELECT
    *
FROM
    `student`
JOIN(
    SELECT
        s_id,
        AVG(s_score) avg_score
    FROM
        score
    GROUP BY
        s_id
    HAVING
        avg_score < 60
) score_temp
ON
    student.s_id = score_temp.s_id

考虑未参加考试的人，所以需要student left join score，将8号学生添加一行0分的数据

SELECT
    s_id,
    s_name,
    AVG(s_score) avg_score
FROM
    (
    SELECT
        student.*,
        score.c_id,
        
        IF (score.s_score is null, 0, score.s_score) s_score
    FROM
        student
    LEFT JOIN score ON student.s_id = score.s_id
) s
GROUP BY
    s_id
HAVING
    avg_score < 60

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

需要考虑未参加考试的人 left join

SELECT
    student.s_id,
    student.s_name,
    COUNT(score.c_id) score_count,
    SUM(score.s_score) score_sum
FROM
    `student`
LEFT JOIN score ON student.s_id = score.s_id
GROUP BY
    s_id

查询姓“猴”的老师的个数

SELECT
    COUNT(1)
FROM
    teacher
WHERE
    t_name LIKE "猴%"

查询没学过“张三”老师课的学生的学号、姓名

多表join

# 未学过张三老师课的学生信息
SELECT
    *
FROM
    `student`
WHERE
    s_id NOT IN(
    SELECT
        score.s_id
    FROM
        score
    JOIN course ON score.c_id = course.c_id
    JOIN teacher ON teacher.t_id = course.t_id
    WHERE
        teacher.t_name = "张三"
)

题解说明

当多个表是"=“关联时，时可以使用join多表关联
当是”!="关联时，不得使用join多表，而是改成not in
当题目改成”学过张三老师的课的学生信息“，可以直接使用4表关联，上面题目因为是没学过的学生是 "!="关系，所以只有3表关联，而student表只能使用not in关键字

# 学生张三老师的课的学生信息
SELECT
    *
FROM
    student
    join score on score.s_id=student.s_id
JOIN course ON score.c_id = course.c_id
JOIN teacher ON teacher.t_id = course.t_id
WHERE
    teacher.t_name = "张三"

使用in的方法替代join

SELECT
    *
FROM
    `student`
WHERE
    s_id NOT IN(
    SELECT
        score.s_id
    FROM
        score
    WHERE
        score.c_id IN(
        SELECT
            c_id
        FROM
            course se
        JOIN teacher tr ON
            tr.t_name = "张三" AND se.t_id = tr.t_id
    )
)

查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（！）

SELECT
    *
FROM
    `student`
JOIN(
    SELECT
        s1.s_id
    FROM
        score s1
    JOIN score s2 ON
        s1.s_id = s2.s_id
    WHERE
        s1.c_id = "01" AND s2.c_id = "02"
) tmp
ON
    student.s_id = tmp.s_id

题解说明
一个表内满足2个相同字段的不同条件，需要 自己 join 自己