只有在刷题的时候才是最平静的。

## 1.数据库练习题

基本表结构:

  • student(sno,sname,sage,ssex)学生表
  • course(cno,cname,tno) 课程表
  • sc(sno,cno,score) 成绩表
  • teacher(tno,tname) 教师表

建表数据:(Win10 MySQL 5.7.23版本数据库 navicate操作)具体数据自行添加

CREATE TABLE student(
sno int NOT NULL PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
sage int NOT NULL,
ssex char(2) check(ssex='男'or ssex='女')
)character set = utf8;

CREATE TABLE teacher(
tno int NOT NULL PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(20) NOT NULL
)character set = utf8;

CREATE TABLE course(
cno int NOT NULL PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20) NOT NULL,
tno int,
CONSTRAINT fk_couTotea FOREIGN KEY (tno) REFERENCES teacher(tno)
)character set = utf8;

CREATE TABLE sc(
sno int,
cno int,
score numeric(5,2),
CONSTRAINT fk_scTostu FOREIGN KEY (sno) REFERENCES student(sno),
CONSTRAINT fk_scTocou FOREIGN KEY (cno) REFERENCES course(cno)
)character set = utf8;

以下sql语句均在本地验证成功

1.查询课程1的成绩比课程2的成绩高的所有学生的学号

select a.sno from
(select sno,score from sc where cno=1) a,
(select sno,score from sc where cno=2) b
where a.score>b.score and a.sno=b.sno

2.查询平均成绩大于60分的同学的学号和平均成绩

提示:AVG 函数返回数值列的平均值。NULL 值不包括在计算中。

SQL AVG() 语法
SELECT AVG(column_name) FROM table_name

select a.sno as "学号",avg(a.score) as "平均成绩"
from
(select sno,score from sc) a
group by sno having avg(a.score)>60

3.查询所有同学的学号、姓名、选课数、总成绩

_GROUP BY必须得配合聚合函数来用,分组之后你可以计数(COUNT),求和(SUM),求平均数(AVG)等。分组的依照字段必须是select 后没有被计算过的原始字段。

select a.sno as "学号",b.sname as "姓名",
count(a.sno) as "选课数",sum(a.score) as "总成绩"
from sc a,student b
where a.sno = b.sno
group by a.sno,b.sname

或者

select student.sno as "学号",student.sname as "姓名",
count(sc.sno) as "选课数",sum(sc.score) as "总成绩"
from student left join sc on student.sno = sc.sno
group by student.sno,sname

4.查询姓“菜”的老师的个数

select count(tname) as "人数" from teacher where tname like '菜%'

或者

select tname as "姓名",count(tname) as "人数"
from teacher
where tname like '菜%'
group by tname

5.查询没学过“张三”老师课的同学的学号、姓名

select student.sno,sname from student
where sno not in (select sc.sno from sc,course,teacher
where sc.cno = course.cno
and teacher.tno = course.tno
and teacher.tname='张三')

或者

select student.sno,sname from student
where sno not in (select sc.sno from sc where cno in (
select cno from course where tno = (
select tno from teacher where tname='张三')
)
)

6.查询同时学过课程1和课程2的同学的学号、姓名

select sno,sname from student 
where sno in (select sc.sno from sc where sc.cno = 1)
and sno in (select sc.sno from sc where sc.cno = 2)

或者

select c.sno,sname from 
(select sno from sc where cno = 1) a,
(select sno from sc where cno = 2) b,
student c
where a.sno = b.sno and a.sno = c.sno

或者

select student.sno,sname from student,sc where student.sno = sc.sno and sc.cno = 1 
and exists (select * from sc as sc2 where sc2.sno = sc.sno and sc2.cno = 2)

注意,MySQL不支持INTERSECT(交集)操作符。

7.查询学过“李四”老师所教课程的同学的学号、姓名

select distinct student.sno,sname from student,sc 
where student.sno = sc.sno and sc.cno in (
select course.cno from course,teacher where course.tno = teacher.tno and tname='李四')

或者

select distinct a.sno,sname from student a, sc b,
(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '菜徐坤') e
where a.sno = b.sno and b.cno = e.cno

8.查询课程编号1的成绩比课程编号2的成绩高的所有同学的学号、姓名

select a.sno,sname from student a,
(select sno,score from sc where cno = 1)b,
(select sno,score from sc where cno = 2)c
where a.sno = b.sno and b.sno = c.sno and b.score > c.score

9.查询所有课程成绩小于60分的同学的学号、姓名

select sno,sname from student
where sno not in (select distinct sno from sc where score > 60)

10,查询至少有一门课程与学号为1的同学所学课程相同的同学的学号和姓名

select distinct a.sno, a.sname
from student a, sc b
where a.sno <> 1 and a.sno=b.sno and
b.cno in (select cno from sc where sno = 1)

或者

select s.sno,s.sname 
from student s,
(select sc.sno
from sc
where sc.cno in (select sc1.cno from sc sc1 where sc1.sno=1)and sc.sno<>1
group by sc.sno)r1
where r1.sno=s.sno

11.把“sc”表中“王五”所教课的成绩都更改为此课程的平均成绩

思考思考中。。。

12.查询和编号为2的同学学习的课程完全相同的其他同学学号和姓名

select b.sno, b.sname
from sc a, student b
where b.sno <> 2 and a.sno = b.sno
and a.sno not in (
select distinct sno from sc where cno not in (select cno from sc where sno =2 ) )
group by b.sno, b.sname
having sum(cno) = (select sum(cno) from sc where sno = 2)

注:
这个sql语句是通过将sc中学号2所学课程的课程号(cno)进行求和作为判断的标志,课程号求和相等即为选课相同。后来发现了个问题,如果学生2选的课程为1,2,4,存在其他学生选课为2,5或者3,4时也会匹配成功被选中。后来增加限制条件a.sno not in (select distinct sno from sc where cno not in (select cno from sc where sno =2 )
其中select distinct sno from sc where cno not in (select cno from sc where sno =2 )这句的意思是筛选出有选择除了1,2,4课程之外课程的学生学号(这些学生自然也就必定不符合要求)。

13.删除学习“王五”老师课的sc表记录

delete from sc where sc.cno in (
select course.cno from course,teacher
where course.tno = teacher.tno and teacher.tname='王五')

14.向sc表中插入记录:将没有课程3成绩同学的该成绩补齐, 其成绩取所有学生的课程2的平均成绩

INSERT INTO SELECT 语句从一个表复制数据,然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

insert into sc select sno, 3, (select avg(score) from sc where cno = 2)
from student
where sno not in (select sno from sc where cno = 3)

15、按平平均分从高到低显示所有学生的如下统计报表:

– 学号,企业管理,马克思,UML,数据库,物理,课程数,平均分

SQL之case when then用法

select sno as 学号
,max(case when cno =1 then score end)as 企业管理
,max(case when cno =2 then score end)as 马克思
,max(case when cno =3 then score end)as UML
,max(case when cno =4 then score end)as 数据库
,max(case when cno =5 then score end)as 物理
,count(cno)as 课程数
,avg(score)
from sc
group by sno
order by avg(score) desc

16.查询各科成绩最高分和最低分:

以如下形式显示:课程号,课程名,最高分,最低分

select  course.cno as 课程号
,course.cname as 课程名
,MAX(score) as 最高分
,MIN(score) as 最低分
from sc,course
where sc.cno=course.cno
group by course.cno

17.按各科平均成绩从低到高和及格率的百分数从高到低顺序

select cno,avg(score),
100*sum(case when score>=60 then 1 else 0 end)/count(*)
as 及格率
from sc
group by cno
order by avg(score) ,及格率 desc;

18.查询如下课程平均成绩和及格率的百分数(用”1行”显示):

*企业管理(001),马克思(002),UML (003),数据库(004) *

select 
avg(case when cno = 1 then score end)as 平均分1,
avg(case when cno = 2 then score end)as 平均分2,
avg(case when cno = 3 then score end)as 平均分3,
avg(case when cno = 4 then score end)as 平均分4,
100*sum(case when cno=1 and score >=60 then 1 else 0 end)/count(*) as 及格率1,
100*sum(case when cno=2 and score >=60 then 1 else 0 end)/count(*) as 及格率2,
100*sum(case when cno=3 and score >=60 then 1 else 0 end)/count(*) as 及格率3,
100*sum(case when cno=4 and score >=60 then 1 else 0 end)/count(*) as 及格率4
from sc

19.查询不同老师所教不同课程平均分, 从高到低显示

select t.tname as 教师,c.cname as 课程,avg(s.score)
from teacher t,course c,sc s
where t.tno = c.tno and c.cno = s.cno
group by c.cno
order by avg(s.score) desc

20.查询如下课程成绩均在第3名到第6名之间的学生的成绩:

– [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

用top

select top 6 max(a.sno) 学号, max(b.sname) 姓名,
max(case when cno = 1 then score end) as 企业管理,
max(case when cno = 2 then score end) as 马克思,
max(case when cno = 3 then score end) as UML,
max(case when cno = 4 then score end) as 数据库,
avg(score) as 平均分
from sc a, student b
where a.sno not in
(select top 2 sno from sc where cno = 1 order by score desc)
and a.sno not in (select top 2 sno from sc where cno = 2 order by scoredesc)
and a.sno not in (select top 2 sno from sc where cno = 3 order by scoredesc)
and a.sno not in (select top 2 sno from sc where cno = 4 order by scoredesc)
and a.sno = b.sno
group by a.sno

不过MySQL不支持Top,用的Limit代替

select max(a.sno)as 学号,max(b.sname)as 姓名,
max(case when cno = 1 then score end) as 企业管理,
max(case when cno = 2 then score end) as 马克思,
max(case when cno = 3 then score end) as UML,
max(case when cno = 4 then score end) as 数据库,
avg(score) as 平均分
from sc a, student b
where a.sno not in
(select sc1.sno from (select sno from sc where cno = 1 order by score desc limit 2)as sc1)
and a.sno not in (select sc2.sno from (select sno from sc where cno = 2 order by score desc limit 2)as sc2)
and a.sno not in (select sc3.sno from (select sno from sc where cno = 3 order by score desc limit 2)as sc3)
and a.sno not in (select sc4.sno from (select sno from sc where cno = 4 order by score desc limit 2)as sc4)
and a.sno = b.sno
group by a.sno limit 6

坑:
MySQL子查询中直接写limit会报错This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'意思是,这版本的 MySQL 不支持使用 LIMIT 子句的 IN/ALL/ANY/SOME 子查询,即是支持非 IN/ALL/ANY/SOME 子查询的 LIMIT 子查询。

例如:
select * from table where id in (select id from table limit 10)
是错误的写法

但是只要我们再裹一层就可以了(我真是无语啊)
select * from table where id in (select t.id from (select * from table limit 10)as t)

扩充:

浅谈sql中的in与not in,exists与not exists的区别以及性能分析