mysql查询计算机系和英语系的学生信息,MySQL 数据库操作

stu 表：学生信息表

score 表：分数表

1. 写出一条sql语句，查出所有学生信息

select * from stu;

2. 写出一条sql语句，查出表中从第2条到第4条学生的信息

select * from stu limit 1,3;

select * from stu where id between 902 and 904;

3. 写出一条sql语句，查出年龄最小的那个学生的信息

select * from stu order by birth desc limit 1;

4. 写出一条sql语句，查出“计算机系”和“英语系”的所有学生信息

select * from stu where department=”计算机系” or department=”英语系”;

5. 写出一条sql语句，查出每一个系总共有多少学生

select count(*) from stu group by department;

6. 写出一条sql语句，查出每一个系的最高分是多少，包括对应的系名信息

select department,max(sc.grade) from stu left join score as sc on stu_id = sc.stu_id group by stu.department;

7. 写出一条sql语句，查出每一个系的最高分的那个学生的信息

select * from  (select a.name,a.id,a.department,b.grade from stu as a join score as b on a.id=b.stu_id) a join (select department,max(grade) grade from stu left join score as sc on stu.id = sc.stu_id group by stu.department) b on a.department=b.department and a.grade=b.grade