我们先创建一个数据库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 -birth>=22 and -birth<=30;
或者
select id,name,sex,-birth as age,department,address from student where -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;
