SQL | 多表连接 | 找出最小的N个数

student表： 姓名name,学号stu_id,班级class,入学时间date1,年龄age,专业major
score1表：学号stu_id,课程course_id,分数score
筛选出2017年入学的“计算机”专业年龄最小的3位同学名单（姓名、年龄）
统计每个班同学各科成绩平均分大于80分的人数和人数占比
1、创建表

CREATE TABLE IF NOT EXISTS student(
name VARCHAR(10),
stu_id VARCHAR(10),
class VARCHAR(10),
date1 DATE,
age INT,
major VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS score1(
stu_id VARCHAR(10),
course_id VARCHAR(10),
score INT
);

2、插入数据

INSERT INTO student VALUES('小红','00001','01班','2020-9-1',20,'计算机'),
						  ('小兰','00002','01班','2019-9-1',18,'计算机'),
						  ('小明','00003','01班','2019-9-1',19,'金融'),
						  ('小芳','00004','02班','2018-9-1',18,'金融'),
						  ('小花','00005','02班','2020-9-1',21,'计算机'),
						  ('小果','00006','02班','2019-9-1',17,'计算机'),
						  ('小天','00007','03班','2018-9-1',18,'计算机'),
						  ('小云','00008','03班','2019-9-1',19,'计算机'),
						  ('小笑','00009','03班','2020-9-1',20,'计算机');

INSERT INTO score1 VALUES('00001','001',97),
						  ('00002','001',80),
						  ('00002','002',81),
						  ('00003','001',83),
						  ('00003','002',76),
						  ('00003','003',58),
						  ('00004','003',91),
						  ('00005','002',76),
						  ('00005','003',78),
						  ('00006','001',89),
						  ('00006','002',63),
						  ('00006','003',74),
						  ('00007','002',83),
						  ('00007','003',59),
						  ('00008','001',69),
						  ('00009','003',95);

3、逻辑SQL
（1）筛选出2019年入学的“计算机”专业年龄最小的2位同学名单（姓名、年龄）
取date中的年份
year(date)
date_format(date,’%Y’)

mysql> SELECT name,age
    -> FROM student
    -> WHERE YEAR(date1)=2019 AND major='计算机'
    -> ORDER BY age
    -> limit 3;
+--------+------+
| name   | age  |
+--------+------+
| 小果   |   17 |
| 小兰   |   18 |
| 小云   |   19 |
+--------+------+
3 rows in set (0.00 sec)

（2）统计每个班同学各科成绩平均分大于80分的人数和人数占比
思路：
a)每个班同学各科成绩平均分大于80分的人

SELECT stu_id,AVG(score) AS avg
FROM score1
GROUP BY stu_id
HAVING AVG(score)>80;

b)利用左连接
student表与大于80分的临时表合成一张表，没有匹配上的会留空，正好count()空值不计数，从而求出大于80分的人数
round(数，N）保留N位小数
concat()连接%转为为百分数

mysql> SELECT s1.class,count(s2.stu_id) AS '大于80分人数',CONCAT(ROUND(count(s2.stu_id)/count(s1.stu_id),2)*100,'%') AS '人数占比'
    -> FROM student s1 LEFT JOIN
    -> (SELECT stu_id,AVG(score) AS avg
    -> FROM score1
    -> GROUP BY stu_id
    -> HAVING AVG(score)>80) s2
    -> ON  s1.stu_id=s2.stu_id
    -> GROUP BY s1.class;
+-------+-------------------+--------------+
| class | 大于80分人数      | 人数占比     |
+-------+-------------------+--------------+
| 01班  |                 2 | 67.00%       |
| 02班  |                 1 | 33.00%       |
| 03班  |                 1 | 33.00%       |
+-------+-------------------+--------------+
3 rows in set (0.00 sec)