语句 查询每个人每个科目的最高分_Mysql中常用的查询实例详解

我们先创建一个数据库stu_info_db,并创建student表和score表进行实例练习查询。

执行以下操作：

(1)按照以上表内容，进行表的创建并增加记录；(创建表的过程略)

(2)查询student表的所有记录；

select * from student；

(3)查询student表的第2条到第4条记录；

select * from student limit 1,3;

(4)从student表查询所有学生的学号(id)、姓名(name)、院系(department)的信息；

select id，name，department from student;

(5)从student表中查询计算机系和英语系的学生的信息；

select * from student where department in (‘计算机系’,’中文系’);

或者

select * from student where department like '计算机系' or department like '中文系';

(6) 从student表中查询年龄为22~30岁的学生人数；

select * from student where 2019-birth>=22 and 2019-birth<=30;

或者

select id,name,sex,2019-birth as age,department,address from student where 2019-birth between 22 and 30;

(7)从student表中查询每个院系有多少人；

select department,count(id) from student group by department;

或者

select department,count(id) as sum_of_department from student group by department;

(8)从score表中查询每个科目的最高分；

select c_name,max(grade) from score group by c_name;

(9) 查询李四的考试科目(c_name)和考试成绩(grade)；

select c_name,grade from score where stu_id=(select id from student where name=’李四’);

(10)用连接查询的方式查询所有学生的信息和考试信息；

select student.id,name,sex,birth,department,address,c_name,grade from student,score where student.id=score.stu_id;

(11) 计算每个学生的总成绩；

select stu_id,sum(grade) from score group by stu_id;

(12)计算每个考试科目的平均成绩；

select c_name,avg(grade) from score group by c_name;

(13)查询计算机成绩低于95的学生信息；

select * from student where id in(select stu_id from score where c_name='计算机' and grade<95);

(14)查询同时参加计算机和英语考试的学生信息；

select * from student where id = any(select stu_id from score where stu_id in(select stu_id from score where c_name='计算机') and c_name='英语');

(15)将计算机考试成绩按从高到底进行排序；

select stu_id,grade from score where c_name=’计算机’ order by grade desc;

(16)从student表和score表中查询出学生的学号，然后合并查询结果；

select id from student union select stu_id from score;

(17)查询姓张或者姓王的同学的姓名、院系和考试科目及成绩；

select student.id name,sex,birth,department,address,c_name,grade from student,score where (name like '张%' or name like '王%') and student.id=score.stu_id;

(18)查询都是湖南的同学的姓名、年龄、院系和考试科目及成绩；

select student.id,name,sex,birth,department,address,c_name,grade from student,score where address like '湖南%' and student.id=score.stu_id;