sql单表和多表查询练习

1.分别查询student表和score表的所有记录

select * from student;

select * from score;

 

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

select * from student limit 1,4;

 

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

 select * from student where department like '计算机%' or department like '英语%';

 

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

select * from student where year(now())-birth < 22;

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

select department '院系',count(id) '院校人数' from student group by department;

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

select cname '科目',max(grade) '最高成绩' from score group by cname;

7. 查询李广昌的考试科目(c_name)和考试成绩(grade)

select cname,grade from score where stu_id=(select id from student where name='李广昌');

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

select student.*,cname,grade from student join score on student.id=stu_id;

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

select name '姓名',sum(grade) '总成绩' from student join score on student.id=stu_id group by name;

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

select cname '科目', round(avg(grade),2) '平均成绩' from student join score on student.id=stu_id group by cname;

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

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

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

select name '姓名' , grade '成绩' from student join score on student.id=stu_id where cname='计算机' order by grade desc;

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

select id from student union select stu_id from score;

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

select name,department,cname,grade from student join score on student.id=stu_id where name like '王%' or name like '张%';

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

select name '姓名',year(now())-birth '年龄',department '院系',cname '考试科目',grade '成绩' from student join score on student.id=stu_id where address like '湖南%';