SQL篇--查询练习II(查询语句)

根据上一篇查询练习I中给出的数据表和数据，进行查询练习。其中涉及简单查询，聚合，排序，多表查询等等。下面直接开始。

1.查询所有记录

该语句较简单

select * from student;
select * from score;

2.查询固定的列

如查询student中的姓名，

select s_name from student;

3.查询某列且要求不重复

使用函数distinct，如查询score中的学号，且要求不重复

select distinct s_no from score;

4.查询范围

使用between and，如查询score表中成绩在60-80之间所有的记录

select * from score where sc_degree BETWEEN 61 AND 79;
或
select* from score where sc_degree < 80 AND sc_degree > 60 ; 

需要注意的是between and包含边界。

5.查询score表中成绩为85, 86, 或者88的记录

或的关系，应使用in

select * from score where sc_degree in (85,86,88);

6.查询student表中’95031’班或性别为’女’的同学记录

题目要求中有两个条件，且是或的关系，用or连接

select * from student
where  s_class='95031' or s_sex='女';

7.以class降序查询student表

考察降序。排序使用order by，asc为升序，desc为降序

select * from student
order by s_class desc;

8.以c_no升序.sc_degree降序查询score表

该方式类似于排序中的稳定性问题，先按照c_no升序排，如果c_no相同则根据sc_degree降序排

selec* from score order by c_no asc,sc_degree desc;

9.查询’95031’班的学生人数

查询人数，则需要我们对人数进行统计，使用count()函数

select count(s_no) from student
where s_class='95031';

10.查询score表中的最高分数的学生号和课程号

该题目比较重点，且能引申出更多类型题目，一些练习网站都对该类题有考察。首先我们可以用子查询。

select max(sc_degree) from score; -- 先寻找最大成绩

select s_no,c_no from score       -- 进行子查询
where sc_degree in (select max(sc_degree) from score); -- 此处in和=都可以，效果一样，即使是有多个最大值也一样

需要注意的问题是，该题使用排序方式求解

select s_no,c_no from score
order by score desc
limit 0,1;

该方法存在缺陷，即最大值可能不止一个的时候，该方法只能返回一个值。

11.查询每门课的平均成绩

每门课 的 平均成绩，需要聚合group by和聚合函数avg()配合使用

select avg(sc_degree) from score
group by c_no;

12.查询score表中至少有2名学生选修的,并且以3开头的课程的平均分

题目考察的点涉及group by，having过滤和模糊匹配

select avg(sc_degree) from score
where c_no like '3%'
group by c_no having count(*)>1;
或
selec avg(sc_degree),c_no  from score 
group by c_no having count(c_no)>=2 and c_no like '3%';

• where 条件不能放在 group by后面
• group by后面只能使用having来做查询

13.查询所有的学生 s_name , c_no, sc_degree列

s_name，c_no和sc_degree分别在student表和score表中，说明我们需要进行多表查询

-- 多表连接
select s_name,c_no,sc_degree from student,score
where student.s_no=score.s_no;
或
-- inner join方式
select s_name,c_no,sc_degree from 
student inner join score
on student.s_no=score.s_no;

14.查询班级是’95031’班学生每门课的平均分

该题考察的是多表查询和聚合

select avg(sc_degree) from student,score
where student.s_no=score.s_no
and s_class='95031'
group by c_no;
或
select avg(sc_degree) from
student join score on student.s_no=score.s_no
where(and也可以) s_class='95031'
group by c_no;
或
--子查询
select avg(sc_degree) from score 
where s_no in (select s_no from student where s_class='95031')
group by c_no;

前两种方法实际都是表连接为一个新表后，根据新表查询；而子查询是找到95031班级的学生，进而计算平均分。

15.查询选修"3-105"课程的成绩高于’109’号同学’3-105’成绩 的所有同学的记录

-- 子查询
select * from score,student
where score.s_no=student.s_no
and c_no='3-105' 
and sc_degree> 
(select sc_degree from score
where s_no='109' and c_no='3-105');
-- join
select * from
student join score on score.s_no=student.s_no
where c_no='3-105'
and sc_degree>
(select sc_degree from score
where s_no='109' and c_no='3-105');

16.查询所有学号为108、101的同学同年出生的所有学生的s_no,s_name和s_birthday

该题考查year函数

select s_no,s_name,s_birthday from student
where year(s_birthday) in
(select year(s_birthday) from student
where s_no='108' or s_no='101');
-- where s_no in ('108','101');

17.查询 张旭 教师任课的学生的成绩

该题又是一个多表查询问题，涉及张旭教师的教师号，所上课程号，学生所选课程，要充分分析表结构

select t_no from teacher  -- 第一步，选出张旭所教课程的教师号
where t_name='张旭';

select c_no from course   -- 第二步，根据教师号选出课程号
where t_no=(select t_no from teacher where t_name='张旭');

select * from score       -- 第三步，根据课程号从score中选出学生信息
where score.c_no in
(select c_no from course
where t_no=(select t_no from teacher where t_name='张旭'));

我们使用多表连接也是可以的

select * from
teacher join course on teacher.t_no=course.t_no
join score on score.c_no=course.c_no
where t_name='张旭';

18.查询选修课程的同学人数多余 5 人的教师姓名

该题考数group by、having和多表查询

select t_name from
teacher join course on teacher.t_no=course.t_no
join score on score.c_no=course.c_no
group by score.c_no having count(s_no)>5;

多表连接后看成一个新表再做筛选比较简介直观，我们也可以使用子查询来做，逻辑思维如下

-- 第一步，找出多于5人的课程号
-- 第二步，根据课程号去寻找教师号
-- 第三步，最终找到教师姓名
select t_name from teacher
where t_no in
(select t_no from course
where c_no in
(select c_no from score group by c_no having count(s_no)>5));

19.查询所有教师和同学的 name ,sex, birthday

该题考察union

select s_name as name,s_sex as sex,s_birthday as birthday from student
union
select t_name,t_sex,t_birthday from teacher;

• union 结果集中的列名总是等于第一个select语句中的列名
• union all与union的区别在于union all列出所有的值，不会去重复

20.查询成绩比该课程平均成绩低的同学的成绩表

该题涉及自连接

select * from score s1
where sc_degree<
(select avg(sc_degree) from score s2 where s1.c_no=s2.c_no);

自连接就是两个表并列，然后根据条件限定操作；where s1.c_no=s2.c_no的目的是限定课程去对比。
下面我们对自连接进行探索，

select count(*) from score;      
输出13

select count(*) from score s1 join score s2;
输出169，没有限定，相当于笛卡尔积

select count(*) from score s1 join score s2 
where s1.s_no=s2.s_no and s1.c_no=s2.c_no;
加了限定，输出13，这里s_no和c_no一同构成主键

select * from score s1 join score s2 
where s1.s_no=s2.s_no and s1.c_no=s2.c_no;
输出是两个score表并列

21.查询student 中每个学生的姓名和年龄

该题考察now()函数

select s_name,year(now())-year(s_birthday) as age
from student;