首先闷煤，我們先來(lái)構(gòu)造數(shù)據(jù)：
# 創(chuàng)建數(shù)據(jù)庫(kù)
CREATE DATABASE Test;
USE TEMP;
# 創(chuàng)建表
CREATE TABLE student(id int (11) primary key auto_increment,name char(255),sex char(255),age int(11))charset utf8;
CREATE TABLE student_score(id int (11) primary key auto_increment,class char(255),score char(255),student_id int(11))charset utf8;
# 插入學(xué)生信息
INSERT INTO student(name,sex,age) VALUES('學(xué)生1','男','12');
INSERT INTO student(name,sex,age) VALUES('學(xué)生2','女','13');
INSERT INTO student(name,sex,age) VALUES('學(xué)生3','男','15');
INSERT INTO student(name,sex,age) VALUES('學(xué)生4','女','14');
INSERT INTO student(name,sex,age) VALUES('學(xué)生5','男','11');
INSERT INTO student(name,sex,age) VALUES('學(xué)生6','女','12');
INSERT INTO student(name,sex,age) VALUES('學(xué)生7','男','11');
INSERT INTO student(name,sex,age) VALUES('學(xué)生8','女','15');
INSERT INTO student(name,sex,age) VALUES('學(xué)生9','男','16');
INSERT INTO student(name,sex,age) VALUES('學(xué)生10','女','12');
# 插入學(xué)科及分?jǐn)?shù)信息
INSERT INTO student_score(class,score,student_id) VALUES('語(yǔ)文','100',1);
INSERT INTO student_score(class,score,student_id) VALUES('數(shù)學(xué)','100',1);
INSERT INTO student_score(class,score,student_id) VALUES('英語(yǔ)','100',1);
INSERT INTO student_score(class,score,student_id) VALUES('語(yǔ)文','90',2);
INSERT INTO student_score(class,score,student_id) VALUES('數(shù)學(xué)','70',2);
INSERT INTO student_score(class,score,student_id) VALUES('英語(yǔ)','60',2);
INSERT INTO student_score(class,score,student_id) VALUES('語(yǔ)文','89',3);
INSERT INTO student_score(class,score,student_id) VALUES('數(shù)學(xué)','65',3);
INSERT INTO student_score(class,score,student_id) VALUES('英語(yǔ)','32',3);
INSERT INTO student_score(class,score,student_id) VALUES('語(yǔ)文','100',4);
INSERT INTO student_score(class,score,student_id) VALUES('數(shù)學(xué)','89',4);
INSERT INTO student_score(class,score,student_id) VALUES('英語(yǔ)','98',4);
INSERT INTO student_score(class,score,student_id) VALUES('語(yǔ)文','50',5);
INSERT INTO student_score(class,score,student_id) VALUES('數(shù)學(xué)','34',5);
INSERT INTO student_score(class,score,student_id) VALUES('英語(yǔ)','56',5);
INSERT INTO student_score(class,score,student_id) VALUES('語(yǔ)文','100',6);
INSERT INTO student_score(class,score,student_id) VALUES('數(shù)學(xué)','89',6);
INSERT INTO student_score(class,score,student_id) VALUES('英語(yǔ)','98',6);
INSERT INTO student_score(class,score,student_id) VALUES('語(yǔ)文','80',7);
INSERT INTO student_score(class,score,student_id) VALUES('數(shù)學(xué)','89',7);
INSERT INTO student_score(class,score,student_id) VALUES('英語(yǔ)','78',7);
INSERT INTO student_score(class,score,student_id) VALUES('語(yǔ)文','90',8);
INSERT INTO student_score(class,score,student_id) VALUES('數(shù)學(xué)','89',8);
INSERT INTO student_score(class,score,student_id) VALUES('英語(yǔ)','78',8);
INSERT INTO student_score(class,score,student_id) VALUES('語(yǔ)文','100',9);
INSERT INTO student_score(class,score,student_id) VALUES('數(shù)學(xué)','99',9);
INSERT INTO student_score(class,score,student_id) VALUES('英語(yǔ)','98',9);
INSERT INTO student_score(class,score,student_id) VALUES('語(yǔ)文','0',100);
INSERT INTO student_score(class,score,student_id) VALUES('數(shù)學(xué)','0',100);
INSERT INTO student_score(class,score,student_id) VALUES('英語(yǔ)','0',100);
構(gòu)造完數(shù)據(jù)，結(jié)果如下：
student表
student表
student_score表
student_score表
可以看到兩個(gè)表里各有和對(duì)方匹配不上的數(shù)據(jù)涮瞻，student表中的學(xué)生10和student_score表中的student_id 100鲤拿，這些是為了之后講JOIN時(shí)用的。
下面我們就來(lái)看具體的實(shí)驗(yàn)事件吧：
1. 獲取各科目分?jǐn)?shù)平均值（GROUP BY使用）
# 獲取各科目分?jǐn)?shù)平均值
SELECT class AS '課程', AVG(score) AS '平均數(shù)' FROM student_score GROUP BY class;
結(jié)果：
獲取各科目分?jǐn)?shù)平均值
2. 獲取語(yǔ)文分?jǐn)?shù)大于60分的學(xué)生分?jǐn)?shù)的數(shù)量分布（GROUP BY + HAVING使用）
# 獲取語(yǔ)文分?jǐn)?shù)大于60分的學(xué)生分?jǐn)?shù)的數(shù)量分布
SELECT
class AS '課程',
score AS '分?jǐn)?shù)',
count(*) AS '數(shù)量'
FROM
student_score
WHERE
class = '語(yǔ)文'
GROUP BY
score
HAVING
score >= 60;
結(jié)果：
獲取語(yǔ)文分?jǐn)?shù)大于60分的學(xué)生分?jǐn)?shù)的數(shù)量分布
3. 獲取兩個(gè)表中共有學(xué)生的全部信息（INNER JOIN使用）
# 獲取兩個(gè)表中共有學(xué)生的全部信息（INNER JOIN使用）
SELECT * FROM student A INNER JOIN student_score B ON A.id = B.student_id;
結(jié)果：
獲取兩個(gè)表中共有學(xué)生的全部信息（INNER JOIN使用）
4. 獲取存在于student表中學(xué)生的全部信息（LEFT JOIN使用）
# 獲取存在于student表中學(xué)生的全部信息（LEFT JOIN使用）
SELECT * FROM student A LEFT JOIN student_score B ON A.id = B.student_id;
結(jié)果：
獲取存在于student表中學(xué)生的全部信息（LEFT JOIN使用）
5. 獲取只存在于student表中學(xué)生的全部信息（LEFT JOIN使用）
# 獲取只存在于student表中學(xué)生的全部信息（LEFT JOIN使用）
SELECT * FROM student A LEFT JOIN student_score B ON A.id = B.student_id WHERE B.student_id IS NULL;
結(jié)果：
獲取只存在于student表中學(xué)生的全部信息（LEFT JOIN使用）
6. 獲取存在于student_score表中學(xué)生的全部信息（RIGHT JOIN使用）
# 獲取存在于student_score表中學(xué)生的全部信息（RIGHT JOIN使用）
SELECT * FROM student A RIGHT JOIN student_score B ON A.id = B.student_id;
結(jié)果：
獲取存在于student_score表中學(xué)生的全部信息（RIGHT JOIN使用）
7. 獲取只存在于student_score表中學(xué)生的全部信息（RIGHT JOIN使用）
# 獲取只存在于student_score表中學(xué)生的全部信息（RIGHT JOIN使用）
SELECT * FROM student A RIGHT JOIN student_score B ON A.id = B.student_id WHERE A.id IS NULL;
結(jié)果：
獲取只存在于student_score表中學(xué)生的全部信息（RIGHT JOIN使用）
8. 獲取兩個(gè)表中出現(xiàn)的所有學(xué)生的全部信息（FULL JOIN使用署咽，Mysql中沒(méi)有FULL JOIN近顷，只能用union來(lái)實(shí)現(xiàn)）
# 獲取兩個(gè)表中出現(xiàn)的所有學(xué)生的全部信息（FULL JOIN使用，Mysql中沒(méi)有FULL JOIN宁否，只能用union來(lái)實(shí)現(xiàn)）
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;
結(jié)果：
獲取兩個(gè)表中出現(xiàn)的所有學(xué)生的全部信息（FULL JOIN使用窒升，Mysql中沒(méi)有FULL JOIN，只能用union來(lái)實(shí)現(xiàn)）
9. 獲取數(shù)學(xué)分?jǐn)?shù)為第3～5名的學(xué)生名單（DISTINCT慕匠、嵌套SQL使用）
# 獲取數(shù)學(xué)分?jǐn)?shù)為第3～5名的學(xué)生名單
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 = '數(shù)學(xué)'
ORDER BY
score DESC
LIMIT 2,
3
) AS T
WHERE
class = '數(shù)學(xué)'
)
ORDER BY
A.score DESC;
結(jié)果：
獲取數(shù)學(xué)分?jǐn)?shù)為第3～5名的學(xué)生名單
