MYSQL库表操作的基础操作3

题目要求：

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

mysql> select * from student;

mysql> select * from score;

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

mysql> select * from student  limit 1,4;
(3).从student表中查询计算机系和英语系的学生的信息

以下三种方法都可以，但是用 union all 的话 可以让相同系的记录行挨在一起利于查看，并且用union all 的话 比用 in 或 or 要快

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

mysql> select * from student where department in ('计算机系','英语系');

mysql> select * from student where department = '计算机系' or department = '英语系';
(4).从student表中查询年龄小于22岁的学生信息

mysql> select * from student where year(now()) - birth < 22;
(5).从student表中查询每个院系有多少人

mysql> select department, count(id) from student group by department;
(6).从score表中查询每个科目的最高分

mysql> select c_name, max(grade) from score group by c_name;
(7).查询李广昌的考试科目(cname)和考试成绩(grade)

mysql> 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;
(8).用连接的方式查询所有学生的信息和考试信息

mysql> select * from student inner join score on student.id = stu_id;
(9).计算每个学生的总成绩

mysql> 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;
(10).计算每个考试科目的平均成绩

mysql> select c_name as '科目', avg(grade) as '平均成绩' from score group by c_name;
(11).查询计算机成绩低于95的学生信息

mysql> select student.* from student right join(select stu_id from score where c_name = '计算机' and grade < 95) as sc on id = sc.stu_id;
(12).将计算机考试成绩按从高到低进行排序

mysql> 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;
(13).从student表和score表中查询出学生的学号，然后合并查询结果

mysql> select id from student union select stu_id from score;
(14).查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

mysql> 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;
(15).查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

mysql> 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;