SQL 练习指南

作者: 博弈史密斯 | 来源:发表于2018-07-13 11:34 被阅读0次

在线SQL 测试网站：http://www.sqlfiddle.com/，这个网站有时访问不太稳定。

网站使用方法

下面的表和题出自：https://blog.csdn.net/mrbcy/article/details/68965271

先创建表：

create table students(
  sno varchar(3) not null, 
  sname varchar(4) not null,
  ssex varchar(2) not null, 
  sbirthday datetime,
  class varchar(5)
);

create table courses(
  cno varchar(5) not null, 
  cname varchar(10) not null, 
  tno varchar(10) not null
);

create table scores(
  sno varchar(3) not null, 
  cno varchar(5) not null, 
  degree numeric(10, 1) not null
);

create table teachers(
  tno varchar(3) not null, 
  tname varchar(4) not null, 
  tsex varchar(2) not null, 
  tbirthday datetime not null, 
  prof varchar(6), 
  depart varchar(10) not null
);

insert into students (sno,sname,ssex,sbirthday,class) values (108 ,'曾华' ,'男' ,'1977-09-01',95033);
insert into students (sno,sname,ssex,sbirthday,class) values (105 ,'匡明' ,'男' ,'1975-10-02',95031);
insert into students (sno,sname,ssex,sbirthday,class) values (107 ,'王丽' ,'女' ,'1976-01-23',95033);
insert into students (sno,sname,ssex,sbirthday,class) values (101 ,'李军' ,'男' ,'1976-02-20',95033);
insert into students (sno,sname,ssex,sbirthday,class) values (109 ,'王芳' ,'女' ,'1975-02-10',95031);
insert into students (sno,sname,ssex,sbirthday,class) values (103 ,'陆君' ,'男' ,'1974-06-03',95031);

insert into courses(cno,cname,tno) values ('3-105' ,'计算机导论',825);
insert into courses(cno,cname,tno) values ('3-245' ,'操作系统' ,804);
insert into courses(cno,cname,tno) values ('6-166' ,'数据电路' ,856);
insert into courses(cno,cname,tno) values ('9-888' ,'高等数学' ,100);

insert into scores(sno,cno,degree) values (103,'3-245',86);
insert into scores(sno,cno,degree) values (105,'3-245',75);
insert into scores(sno,cno,degree) values (109,'3-245',68);
insert into scores(sno,cno,degree) values (103,'3-105',92);
insert into scores(sno,cno,degree) values (105,'3-105',88);
insert into scores(sno,cno,degree) values (109,'3-105',76);
insert into scores(sno,cno,degree) values (101,'3-105',64);
insert into scores(sno,cno,degree) values (107,'3-105',91);
insert into scores(sno,cno,degree) values (108,'3-105',78);
insert into scores(sno,cno,degree) values (101,'6-166',85);
insert into scores(sno,cno,degree) values (107,'6-106',79);
insert into scores(sno,cno,degree) values (108,'6-166',81);

insert into teachers(tno,tname,tsex,tbirthday,prof,depart) values (804,'李诚','男','1958-12-02','副教授','计算机系');
insert into teachers(tno,tname,tsex,tbirthday,prof,depart) values (856,'张旭','男','1969-03-12','讲师','电子工程系');
insert into teachers(tno,tname,tsex,tbirthday,prof,depart) values (825,'王萍','女','1972-05-05','助教','计算机系');
insert into teachers(tno,tname,tsex,tbirthday,prof,depart) values (831,'刘冰','女','1977-08-14','助教','电子工程系');

查询Score表中成绩在60到80之间的所有记录

select * from scores where degree > 60 and degree < 80
或者：
select * from scores where degree between 60 and 80

查询Score表中成绩为85，86或88的记录

select * from scores where degree=85 or degree=86 or degree=88
或者：
select * from scores where degree in (85,86,88)

查询Student表中“95031”班或性别为“女”的同学记录

select * from students where class='95033' or ssex='女'

以Class降序查询Student表的所有记录。

select * from students order by class desc

以Cno升序、Degree降序查询Score表的所有记录

select * from scores order by cno asc, degree desc 
或者：
select * from scores order by cno, degree desc

查询“95031”班的学生人数

select count(1) as sum from students where class='95031'

查询Score表中的最高分的学生学号和课程号

原来想的是：select max(degree) from scores ，但这样没有显示出学号和课程号，
后来想出：select sno,cno from scores where max(degree)，没这样用的。。。还是不对
正确的应该是：

select sno,cno from scores group by degree desc limit 1
或者
select sno,cno from scores order by degree desc limit 1

order by 是排序， group by 是分组，两者在这里都可以实现此功能

又想到几种实现方式：

select  sno,cno,max(degree) from scores

或者：

select  sno,cno from scores having degree=max(degree)

查询‘3-105’号课程的平均分

select avg(degree) from scores where cno='3-105'

查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

like 操作符用于在 where 子句中搜索列中的指定模式。
like 经常和通配符一起使用，比如上面的例子中，以3开头的课程就可以这样实现：where cno like '3%'

而至少5名学生选修，该怎么实现？
我第一次想到的是：where count(sno) >= 5，但是：
在 SQL 中，where 关键字无法与 count、sum、max、min、avg 等合计函数一起使用，所以只能用 having ： having count(sno) >= 5。

而且 having 要放在 where 的后面：

select avg(degree) from scores where cno like '3%' having count(sno) >= 5

上面的sql语句是不对的，我们翻译一下： 所有符合以3开头的课程，这些课程个数相加大于等于5？是的，那我们对这些课程求平均值
看着是否有些别扭，是的，我们应该是求满足 至少5名学生选修且 以3开头的 每一个课程求平均值，所以我们要对课程通过 group by 进行分组。

要记得：当我们使用 avg、sum 这类统计相关的函数时，看是否需要使用 group by 来对某一列进行分组，看是否是对某一列下的同一属性进行统计，譬如：对分数表中，每个班的分数求平均值，便是这样的例子。

那么上面的题目要这样实现（group by 放在 where 后面，having 放在 group by 后面）：

select cno, avg(degree) from scores
where cno like '3%' group by cno
having count(cno) >= 5

我们是求符合要求的课程，所以列最好添加课程：cno

查询最低分大于70，最高分小于90的Sno列

原来是这样求的：

select sno, degree from scores where degree>70 and degree<90 group by sno

上面对题目理解错了，上面是求分数在 70 和 90 之间的学生，而题目是求最低分大于70 且最高分小于90，要这样写：

select sno from scores group by sno having max(degree)<90 and min(degree)>70

查询所有学生的Sname、Cno和Degree列

select sname, cno, degree
from students join scores on students.sno = scores.sno

或者（inner join 等同于 join）：
select sname, cno, degree
from students inner join scores on students.sno = scores.sno

或者（给表名 起别名）：
select sname, cno, degree
from students st join scores sc on st .sno = sc .sno

查询所有学生的Sno、Cname和Degree列

select sno, cname, degree
from courses join scores on courses.cno = scores.cno

查询所有学生的Sname、Cname和Degree列

需要三个表关联，考察了多个join的使用

select sname, cname, degree
from students join scores on students.sno = scores.sno
join courses on courses.cno = scores.cno

查询“95033”班所选课程的平均分

原来是这样写的：

select class, avg(degree)
from students join scores on students.sno = scores.sno
where class='95033' group by class

应该是按课程进行分组，上面 group by class 加上没啥鸟用。。。，正确写法：

select class, cname, avg(degree)
from students join scores on students.sno = scores.sno
join courses on scores.cno=courses.cno
where class='95033' group by courses.cno

现查询所有同学的Sno、Cno和rank列。

先创建表：grade，并插入数据：

create table grade(low numeric(10, 1), upp numeric(10, 1), rank char(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');

考察 join 的另一种用法

select sno, cno, rank
from scores join grade
on scores.degree between grade.low and grade.upp

或者：
select sno, cno, rank
from scores join grade
on scores.degree>=grade.low and scores.degree<=grade.upp

查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录

select sno, degree
from scores where cno='3-105' having degree>(select degree from scores
where cno='3-105' and sno='109')

或者通过自己关联自己，来实现：

select s1.sno, s1.degree
from scores s1 join Scores s2
on(s1.cno=s2.cno and s1.Degree>s2.Degree)
WHERE s1.cno='3-105' and s2.sno='109'

查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录

这一题和上面一题的实现方式一样，刚开始写时忘记在 on 后添加： s1.cno=s2.cno，其实题目没有说清，不添加就是表示所有高于 109、 3-105 的成绩，但想想添加更符合逻辑，因为比较同一课程的成绩才有意义。

select s1.* from scores s1
join scores s2 on s1.cno=s2.cno and s1.degree>s2.degree
where s2.sno='109' and s2.cno='3-105'

查询score中选学一门以上课程的同学中分数为非最高分成绩的记录

原来想的是：

select *
from scores group by sno having count(sno)>1

然后就不知道 非最高分成绩 这个怎么实现了。。。，还以为有什么函数可以实现此功能，想多了……，下面是正确实现方式：

select *
from scores group by sno having count(sno)>1 and degree!=max(degree)

查询和学号为105的同学同年出生的所有学生的Sno、Sname和Sbirthday列

datetime 格式的数据，有几个函数：

正确使用姿势：

select s1.* from students s1
join students s2 on year(s1.sbirthday)=year(s2.sbirthday)
where s2.sno='105'

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

要关联3个表：

select sno, degree
from teachers
join courses on teachers.tno=courses.tno
join scores on courses.cno=scores.cno
where teachers.tname='张旭'

查询选修某课程的同学人数多于5人的教师姓名

select tname
from teachers join courses on teachers.tno=courses.tno
join scores on courses.cno=scores.cno
group by scores.cno having count(scores.cno)>5

查询95033班和95031班全体学生的记录

select * from Students
where class='95033' or class='95031'

或者：
select * from Students
where class in ('95033', '95031')

查询存在有85分以上成绩的课程Cno

原来想的是：

select cno, degree from scores
where degree>85

结果是：

有重复的 cno，题目应该是列出一个即可，我们用 distinct 去重：

select distinct cno from scores
where degree>85

查询选修编号为“3-105“课程且成绩至少高于任意选修编号为“3-245”的同学的成绩的Cno、Sno和Degree,并按Degree从高到低次序排序

至少高于任意 这句话的意思是：选3-105课程同学的成绩，只要高出一个选3-245同学的成绩，就满足条件。要用 any 来表示：

select * from scores
where cno='3-105'
and degree > any(select degree from scores where cno='3-245')
order by degree desc

3-245同学成绩最高为：86分，最低为75分：

结果图

查询选修编号为“3-105”且成绩高于所有选修编号为“3-245”课程的同学的Cno、Sno和Degree.

这一题和上一题的区别是不是至少了，而是高于所有，意思是：选3-105同学的成绩，要高出所有选3-245同学的成绩，才满足条件。用 all 来实现：

select * from scores
where cno='3-105'
and degree > all(select degree from scores where cno=3-245'')
order by degree desc

只有高于3-245最高分：86分的选3-105的同学才满足条件：

结果

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

合并相同的列，要通过 union 来实现：union 内部的 select 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 select 语句中的列的顺序必须相同。

select tname,tsex,tbirthday from teachers
union
select sname,ssex,sbirthday from students

查询所有“女”教师和“女”同学的name、sex和birthday

和上题类似，只是添加了条件过滤：

select tname,tsex,tbirthday from teachers where tsex='女'
union
select sname,ssex,sbirthday from students where ssex='女'

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

select * from scores
where degree<(select avg(degree) from scores group by cno)

或者：

select s1.* from students s1 join (
  select sno, avg(degree) degree
  from scores
  group by cno) s2
on s1.cno=s2.cno and s1.degree<s2.degree

上面这种实现方式 join 的表是在括号内实时生成的，要学会这种关联方式。

查询所有任课教师的Tname和Depart

原来以为下面的方式即可：

select tname,depart
from teachers

其实不然，这些教师不一定都任课，所以要在courses 中匹配：

select tname,depart
from teachers
where tno in (
  select tno from courses
)

查询所有未讲课的教师的Tname和Depart

select tname,depart
from teachers
where tno not in (
  select tno from courses
)

查询至少有2名男生的班号

select class, count(1) boyCount from students
where ssex='男'
group by class
having count(class)>=2

查询Student表中不姓“王”的同学记录

select * from students
where sname not like '王%'

查询Student表中每个学生的姓名和年龄

计算年应，要算出今年的年份减去出生的年份，通过 now() 函数得到现在的时间，通过 year() 得到年份：

select sname, year(now()) - year(sbirthday) age from students

以班号和年龄从大到小的顺序查询Student表中的全部记录

select * from students
group by class
order by class desc, sbirthday asc

原来以为要通过 group by 对 class 进行分组，其实不用，直接对 class 和 asc 进行 order by 即可，正确使用姿势：

select * from students
order by class desc, sbirthday asc

查询“男”教师及其所上的课程

select t.tname, c.cname
from teachers t join courses c
on t.cno=c.cno and t.tsex='男'

注意：关联的时候，on 后面跟的条件，必须是两个表都包含的属性，像 and 后面的 t.tsex='男' 则不应该放在这里，正确语句：

select t.tname, c.cname
from teachers t join courses c
on t.cno=c.cno
where t.tsex='男'

查询每科最高分同学的Sno、Cno和Degree列

select * from scores
group by cno
having degree=max(degree)

查询和“李军”同性别的所有同学的Sname

select s1.sname from students s1
join students s2
on s1.ssex=s2.ssex
where s2.sname='李军'

查询所有选修“计算机导论”课程的“男”同学的成绩表

两种方式，第一种：

select *
from scores sc
join students s on s.sno=sc.sno
join courses c on c.cno=sc.cno
join teachers t on t.tno=c.tno
where c.cname='计算机导论' and s.ssex='男'

第二种：

select *
from scores
where Sno in (
    select Sno
    from students
    where Ssex='男') and
    Cno in (
    select Cno
    from Courses
    where Cname='计算机导论');

这种方式比较独特，多体会一下