SQL中的JOIN、GROUP BY、HAVING、DISTIN

首先，我们先来构造数据：

# 创建数据库
CREATE DATABASE Test;
USE TEMP;

# 创建表
CREATE TABLE student(id int (11) primary key auto_increment,name char(255),sex char(255),age int(11));
CREATE TABLE student_score(id int (11) primary key auto_increment,class char(255),score char(255),student_id int(11));

# 插入学生信息
INSERT INTO student(name,sex,age) VALUES('学生1','男','12');
INSERT INTO student(name,sex,age) VALUES('学生2','女','13');
INSERT INTO student(name,sex,age) VALUES('学生3','男','15');
INSERT INTO student(name,sex,age) VALUES('学生4','女','14');
INSERT INTO student(name,sex,age) VALUES('学生5','男','11');
INSERT INTO student(name,sex,age) VALUES('学生6','女','12');
INSERT INTO student(name,sex,age) VALUES('学生7','男','11');
INSERT INTO student(name,sex,age) VALUES('学生8','女','15');
INSERT INTO student(name,sex,age) VALUES('学生9','男','16');
INSERT INTO student(name,sex,age) VALUES('学生10','女','12');

# 插入学科及分数信息
INSERT INTO student_score(class,score,student_id) VALUES('语文','100',1);
INSERT INTO student_score(class,score,student_id) VALUES('数学','100',1);
INSERT INTO student_score(class,score,student_id) VALUES('英语','100',1);
INSERT INTO student_score(class,score,student_id) VALUES('语文','90',2);
INSERT INTO student_score(class,score,student_id) VALUES('数学','70',2);
INSERT INTO student_score(class,score,student_id) VALUES('英语','60',2);
INSERT INTO student_score(class,score,student_id) VALUES('语文','89',3);
INSERT INTO student_score(class,score,student_id) VALUES('数学','65',3);
INSERT INTO student_score(class,score,student_id) VALUES('英语','32',3);
INSERT INTO student_score(class,score,student_id) VALUES('语文','100',4);
INSERT INTO student_score(class,score,student_id) VALUES('数学','89',4);
INSERT INTO student_score(class,score,student_id) VALUES('英语','98',4);
INSERT INTO student_score(class,score,student_id) VALUES('语文','50',5);
INSERT INTO student_score(class,score,student_id) VALUES('数学','34',5);
INSERT INTO student_score(class,score,student_id) VALUES('英语','56',5);
INSERT INTO student_score(class,score,student_id) VALUES('语文','100',6);
INSERT INTO student_score(class,score,student_id) VALUES('数学','89',6);
INSERT INTO student_score(class,score,student_id) VALUES('英语','98',6);
INSERT INTO student_score(class,score,student_id) VALUES('语文','80',7);
INSERT INTO student_score(class,score,student_id) VALUES('数学','89',7);
INSERT INTO student_score(class,score,student_id) VALUES('英语','78',7);
INSERT INTO student_score(class,score,student_id) VALUES('语文','90',8);
INSERT INTO student_score(class,score,student_id) VALUES('数学','89',8);
INSERT INTO student_score(class,score,student_id) VALUES('英语','78',8);
INSERT INTO student_score(class,score,student_id) VALUES('语文','100',9);
INSERT INTO student_score(class,score,student_id) VALUES('数学','99',9);
INSERT INTO student_score(class,score,student_id) VALUES('英语','98',9);
INSERT INTO student_score(class,score,student_id) VALUES('语文','0',100);
INSERT INTO student_score(class,score,student_id) VALUES('数学','0',100);
INSERT INTO student_score(class,score,student_id) VALUES('英语','0',100);

构造完数据，结果如下：

student表

student表

student_score表

student_score表

可以看到两个表里各有和对方匹配不上的数据，student表中的学生10和student_score表中的student_id 100，这些是为了之后讲JOIN时用的。

下面我们就来看具体的实验事件吧：

1. 获取各科目分数平均值（GROUP BY使用）

# 获取各科目分数平均值
SELECT class AS '课程', AVG(score) AS '平均数' FROM student_score GROUP BY class;

结果：

获取各科目分数平均值

2. 获取语文分数大于60分的学生分数的数量分布（GROUP BY + HAVING使用）

# 获取语文分数大于60分的学生分数的数量分布
SELECT
    class AS '课程',
    score AS '分数',
    count(*) AS '数量'
FROM
    student_score
WHERE
    class = '语文'
GROUP BY
    score
HAVING
    score >= 60;

结果：

获取语文分数大于60分的学生分数的数量分布

3. 获取两个表中共有学生的全部信息（INNER JOIN使用）

# 获取两个表中共有学生的全部信息（INNER JOIN使用）
SELECT * FROM student A INNER JOIN student_score B ON A.id = B.student_id;

结果：

获取两个表中共有学生的全部信息（INNER JOIN使用）

4. 获取存在于student表中学生的全部信息（LEFT JOIN使用）

# 获取存在于student表中学生的全部信息（LEFT JOIN使用）
SELECT * FROM student A LEFT JOIN student_score B ON A.id = B.student_id;

结果：

获取存在于student表中学生的全部信息（LEFT JOIN使用）

5. 获取只存在于student表中学生的全部信息（LEFT JOIN使用）

# 获取只存在于student表中学生的全部信息（LEFT JOIN使用）
SELECT * FROM student A LEFT JOIN student_score B ON A.id = B.student_id WHERE B.student_id IS NULL;

结果：

获取只存在于student表中学生的全部信息（LEFT JOIN使用）

6. 获取存在于student_score表中学生的全部信息（RIGHT JOIN使用）

# 获取存在于student_score表中学生的全部信息（RIGHT JOIN使用）
SELECT * FROM student A RIGHT JOIN student_score B ON A.id = B.student_id;

结果：

获取存在于student_score表中学生的全部信息（RIGHT JOIN使用）

7. 获取只存在于student_score表中学生的全部信息（RIGHT JOIN使用）

# 获取只存在于student_score表中学生的全部信息（RIGHT JOIN使用）
SELECT * FROM student A RIGHT JOIN student_score B ON A.id = B.student_id WHERE A.id IS NULL;

结果：

获取只存在于student_score表中学生的全部信息（RIGHT JOIN使用）

8. 获取两个表中出现的所有学生的全部信息（FULL JOIN使用，Mysql中没有FULL JOIN，只能用union来实现）

# 获取两个表中出现的所有学生的全部信息（FULL JOIN使用，Mysql中没有FULL JOIN，只能用union来实现）
SELECT
    *
FROM
    student A
LEFT JOIN student_score B ON A.id = B.student_id
UNION
    SELECT
        *
    FROM
        student A
    RIGHT JOIN student_score B ON A.id = B.student_id;

结果：

获取两个表中出现的所有学生的全部信息（FULL JOIN使用，Mysql中没有FULL JOIN，只能用union来实现）

9. 获取数学分数为第3～5名的学生名单（DISTINCT、嵌套SQL使用）

# 获取数学分数为第3～5名的学生名单
SELECT
    A.student_id,
    B. NAME,
    A.class,
    A.score
FROM
    student_score A
RIGHT JOIN student B ON A.student_id = B.id
WHERE
    score IN (
        SELECT
            score
        FROM
            (
                SELECT DISTINCT
                    score
                FROM
                    student_score
                WHERE
                    class = '数学'
                ORDER BY
                    score DESC
                LIMIT 2,
                3
            ) AS T
        WHERE
            class = '数学'
    )
ORDER BY
    A.score DESC;

结果：

获取数学分数为第3～5名的学生名单