mysql sql 多表查询小练习

1.查询student表的所有记录

select * from student;

 

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

select * from student  limit 1,3;

 

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

select id ,name,department from student;

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

select * from student where department = '计算机系'
    -> union all
    -> select * from student where department = '英语系';

# 这里也可以用department in ('计算机系','英语系')
# 或 department = '计算机系' or department = '英语系'
# 但是用 union all 的话 可以让相同系的记录行挨在一起利于查看
# 并且用union all 的话 比用 in 或 or 要快

5.从student表中查询年龄33~37岁的学生信息

select * from student where year(now())-birth between 33 and 37;

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

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

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

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

8.查询李四的考试科目(cname)和考试成绩(grade)

select  stu.name,score.c_name,score.grade   from score inner join 
(select id,name from student where name ='李四')as stu 
on score.stu_id =stu.id;

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

select * from student inner join score on student.id=stu_id;

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

 select  name,sc.grade  from student inner join 
(select stu_id,sum(grade)as grade from score group by stu_id)as sc 
on id=sc.stu_id;

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

select c_name as `科目` , avg(grade) as `平均成绩`  from score group by c_name;

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

select student.*  from student right join 
(select stu_id  from score where c_name ='计算机' and grade <95 )as sc 
on id = sc.stu_id;

13.查询同时参加计算机和英语考试的学生的信息

select stu.*   from student as stu inner join 
(select  stu_id   from  
((select * from score where c_name ='计算机')
union all 
(select * from score where c_name='英语'))as s 
group by stu_id having count(c_name)>=2)as sc 
on stu.id=sc.stu_id;

 

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

select name,c_name,grade from student inner join 
(select stu_id,c_name , grade from score where c_name='计算机')as sc 
on student.id = sc.stu_id order by sc.grade desc;

15.从student表和score表中查询出学生的学号，然后合并查询结果

select id from student
    -> union
    -> select stu_id from score;

 

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

 select  stu.name,stu.department,score.c_name,score.grade   from 
(select id,name,department from student where name like '张%' 
union all 
select id,name,department from student where name like '王%')as stu 
inner join score on stu.id=score.stu_id;

 

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

select name,year(now())-birth as '年龄',department,c_name,grade from 
(select id,name,birth,department from student where address like '湖南%')as stu 
inner join score on stu.id=score.stu_id;