SELECT number,NAME,subject,score FROM student_info e RIGHT JOIN student_score d ON e.student_id = d.student_id

这是一个 SQL 查询语句，目的是从两个表中获取学生的信息和成绩。使用了 RIGHT JOIN，表示以 student_score 表为主表，以 student_info 表为从表，关联条件是两个表的 student_id 相等。

具体解释如下：

- SELECT number, NAME, subject, score：选取需要查询的列，分别是学号、姓名、科目和成绩。
- FROM student_info e RIGHT JOIN student_score d ON e.student_id = d.student_id：指定查询的数据来源，使用 RIGHT JOIN 进行关联，主表是 student_score，从表是 student_info，关联条件是两个表的 student_id 相等。

简单来说，这个查询语句的目的是查询每个学生在每门科目上的成绩，同时显示他们的学号和姓名。如果学生信息表中没有对应的记录，则该学生的信息仍然会被查询出来，但是学号和姓名会显示为 NULL。

相关问题

用mariadb实现 1. 教语文的老师所带班级各多少学生? 2. 每门课的老师所带班级各多少学生? 3. 来自上海的学生的班主任都有谁? 4. 来自南京的学生共多少人 5. 来自武汉的男同学和女同学分别多少人? 6. 每个城市的学生各多少人 7. 高于平均工资的老师所带的班级分别多少学生? 8. 每个老师班里各多少来自郑州的学生?1. 查看1年级1班所有同学的语文成绩是多少? 2. 统计1年级1班数学成绩的平均值? 3. 统计6年级的英语成绩的平均值? 4. 查看"刘苍松"所带班级的英语平均分? 5. 查看工资最高的老师所带班级的各科成绩的平均分，最高分和最低分分别是多少? 6. 查看每位大队长的5门成绩平均分是多少？

抱歉，我无法直接连接到你的数据库，但是我可以告诉你如何实现这些问题的查询语句，你可以将其应用于你的数据库中。

1. 教语文的老师所带班级各多少学生?

SELECT t.`name` AS teacher_name, c.`name` AS class_name, COUNT(s.`name`) AS student_count
FROM teacher t
INNER JOIN class c ON t.`id` = c.`teacher_id`
INNER JOIN student s ON c.`id` = s.`class_id`
INNER JOIN course co ON s.`id` = co.`student_id`
WHERE co.`subject` = '语文'
GROUP BY t.`name`, c.`name`

2. 每门课的老师所带班级各多少学生?

SELECT t.`name` AS teacher_name, co.`subject`, c.`name` AS class_name, COUNT(s.`name`) AS student_count
FROM teacher t
INNER JOIN class c ON t.`id` = c.`teacher_id`
INNER JOIN student s ON c.`id` = s.`class_id`
INNER JOIN course co ON s.`id` = co.`student_id`
GROUP BY t.`name`, co.`subject`, c.`name`

3. 来自上海的学生的班主任都有谁?

SELECT t.`name` AS teacher_name, c.`name` AS class_name
FROM teacher t
INNER JOIN class c ON t.`id` = c.`teacher_id`
INNER JOIN student s ON c.`id` = s.`class_id`
INNER JOIN city ci ON s.`city_id` = ci.`id`
WHERE ci.`name` = '上海'
GROUP BY t.`name`, c.`name`

4. 来自南京的学生共多少人?

SELECT COUNT(*) AS student_count
FROM student s
INNER JOIN city ci ON s.`city_id` = ci.`id`
WHERE ci.`name` = '南京'

5. 来自武汉的男同学和女同学分别多少人?

SELECT s.`gender`, COUNT(*) AS student_count
FROM student s
INNER JOIN city ci ON s.`city_id` = ci.`id`
WHERE ci.`name` = '武汉'
GROUP BY s.`gender`

6. 每个城市的学生各多少人?

SELECT ci.`name` AS city_name, COUNT(*) AS student_count
FROM student s
INNER JOIN city ci ON s.`city_id` = ci.`id`
GROUP BY ci.`name`

7. 高于平均工资的老师所带的班级分别多少学生?

SELECT t.`name` AS teacher_name, c.`name` AS class_name, COUNT(s.`name`) AS student_count
FROM teacher t
INNER JOIN class c ON t.`id` = c.`teacher_id`
INNER JOIN student s ON c.`id` = s.`class_id`
WHERE t.`salary` > (SELECT AVG(`salary`) FROM teacher)
GROUP BY t.`name`, c.`name`

8. 每个老师班里各多少来自郑州的学生?

SELECT t.`name` AS teacher_name, c.`name` AS class_name, COUNT(*) AS student_count
FROM teacher t
INNER JOIN class c ON t.`id` = c.`teacher_id`
INNER JOIN student s ON c.`id` = s.`class_id`
INNER JOIN city ci ON s.`city_id` = ci.`id`
WHERE ci.`name` = '郑州'
GROUP BY t.`name`, c.`name`

1. 查看1年级1班所有同学的语文成绩是多少?

SELECT s.`name` AS student_name, co.`score` AS chinese_score
FROM student s
INNER JOIN class c ON s.`class_id` = c.`id`
INNER JOIN course co ON s.`id` = co.`student_id`
WHERE c.`grade` = 1 AND c.`number` = 1 AND co.`subject` = '语文'

2. 统计1年级1班数学成绩的平均值?

SELECT AVG(co.`score`) AS math_average_score
FROM student s
INNER JOIN class c ON s.`class_id` = c.`id`
INNER JOIN course co ON s.`id` = co.`student_id`
WHERE c.`grade` = 1 AND c.`number` = 1 AND co.`subject` = '数学'

3. 统计6年级的英语成绩的平均值?

SELECT AVG(co.`score`) AS english_average_score
FROM student s
INNER JOIN class c ON s.`class_id` = c.`id`
INNER JOIN course co ON s.`id` = co.`student_id`
WHERE c.`grade` = 6 AND co.`subject` = '英语'

4. 查看"刘苍松"所带班级的英语平均分?

SELECT AVG(co.`score`) AS english_average_score
FROM teacher t
INNER JOIN class c ON t.`id` = c.`teacher_id`
INNER JOIN student s ON c.`id` = s.`class_id`
INNER JOIN course co ON s.`id` = co.`student_id`
WHERE t.`name` = '刘苍松' AND co.`subject` = '英语'

5. 查看工资最高的老师所带班级的各科成绩的平均分，最高分和最低分分别是多少?

SELECT co.`subject`, AVG(co.`score`) AS average_score, MAX(co.`score`) AS max_score, MIN(co.`score`) AS min_score
FROM teacher t
INNER JOIN class c ON t.`id` = c.`teacher_id`
INNER JOIN student s ON c.`id` = s.`class_id`
INNER JOIN course co ON s.`id` = co.`student_id`
WHERE t.`salary` = (SELECT MAX(`salary`) FROM teacher)
GROUP BY co.`subject`

6. 查看每位大队长的5门成绩平均分是多少？

SELECT t.`name` AS teacher_name, AVG(co.`score`) AS average_score
FROM teacher t
INNER JOIN class c ON t.`id` = c.`teacher_id`
INNER JOIN student s ON c.`id` = s.`class_id`
INNER JOIN course co ON s.`id` = co.`student_id`
WHERE s.`is_leader` = 1
GROUP BY t.`name`