MySQL | 常见面试题练手及总结

参考源：上海-悠悠的博客
非常重要的是：仅作为自己的笔记，笔者小白，思路蛮混乱的，轻易勿参考

创建表

create table if not exists student(
id int unsigned primary key auto_increment,
name char(32) not null,
age int(32) not null 
)engine = innodb auto_increment = 1001 default charset = utf8;

create table if not exists grade(
no int unsigned primary key auto_increment,
id int not null,
kemu char(32) not null,
score int not null
)engine = innodb default charset = utf8;

写入数据

insert student(name, age) values ("zhang3", 18), ("zhang4", 19), ("zhang5", 20), ("zhang6", 19);

insert grade(id, kemu, score) values (1001, "语文", 85), (1001, "数学", 86), (1002, "英语", 98), (1002, "语文", 94), (1002, "数学", 98), (1003, "数学", 56), (1003, "语文", 69), (1003, "英语", 68), (1004, "英语", 99), (1004, "数学", 97);

表格效果：

学生表，表名：student

成绩表，表名：grade

下边开始都是题～～～～～～～都是哦～～～～～～～～～～

1. 查询所有学生的数学成绩，显示学生姓名name, 分数， 由高到低

/* 思路1：成绩表中选择数据，条件是科目等于数学，按照字段成绩进行排序desc，输出选择结果;学生表作为A表，和刚才输出结果连接 */
select s.name, temp.score from student s
left join 
(select g.id, g.score from grade g where g.kemu = "数学") temp
on s.id = temp.id
order by temp.score desc;

/* 思路2:从两个表里选择，条件是id相等，and科目等于数学，然后进行排序，再输出选择结果； */
select a.name, b.score 
from student a, grade b
where a.id = b.id
and b.kemu = "数学"
order by b.score desc;

2. 统计每个学生的总成绩,显示字段：姓名，总成绩

/* 思路：成绩表中根据id字段分组求和，选择数据id以及求和后的值作为temp表；学生表和此表左连接 */
select a.name, temp.sum_score as 总分 from student a
left join
(select b.id, sum(b.score) as sum_score from grade b
group by b.id) temp
on a.id = temp.id

<