mysql经典查询语句

student表：student_id,student_name,sex,age,department,address

score表：id(数据的序号),student_id,course_name,grade

 

1.查询student表的第2条到4条记录

select * from student limit 1,3;

2.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

select id,name,department from student;

3.从student表中查询计算机系和英语系的学生的信息

select * from student where department in('计算机系' ,‘英语系’)；

4.从student表中查询年龄18~22岁的学生信息

select * from student where age between 18 and 22;

5.从student表中查询每个院系有多少人 

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

6.从score表中查询每个科目的最高分

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

7.查询李四的考试科目（c_name）和考试成绩（grade）

select course_name,grade from score where student_id = (select student_id from student where student_name = '李四')；

8.用连接的方式查询所有学生的信息和考试信息----

select student.*,score .* from student,score where student.student_id = score.student_id;

9.计算每个学生的总成绩

select student.student_id,student.name,sum(grade) from student,score

where student.student_id = score.student_id group by student.student_id;

10.计算每个考试科目的平均成绩

select course_id,course_name ,avg(grade) from score group by course_name ;

11.查询计算机成绩低于95的学生信息

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

12.将计算机考试成绩按从高到低进行排序

select student_id , grade from score where course_name ='计算机'  order by grade desc;

13.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

select student_name,department, course_name,course_id ,grade from student,course where

student.student_id =  course_student_id

and student_name like ‘张%' or student_name like '王%';

14.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

select student_name,age,department,course_name,grade from student student , score 

where address like '湖南%' and student.student_id = score.student_id;