首页 > 安全资讯 >

SQL嵌套查询与子查询实例解析

18-05-22

SQL嵌套查询与子查询实例解析。SQL嵌套查询:嵌套查询指的是一个查询语块可以嵌套在另外一个查询语句块的WHERE子句或者HAVING子句中,前者为子查询或内查询,后者为父查询或外查询。

SQL嵌套查询

嵌套查询指的是一个查询语块可以嵌套在另外一个查询语句块的WHERE子句或者HAVING子句中,前者为子查询或内查询,后者为父查询或外查询。

下面总结一下学习到的几种子查询。

例子使用的表的定义为:

student
(sno char(12) primary key,
sname char(20),
sex char(2),
age smallint,
dept char(20)
);

course
(
cno char(4) primary key,
cname char(40),
ceredit smallint, --学分
);

sc
(
sno char(12),
cno char(4),
score smallint,
);
在sc中 sno 和 cno 为外码

带IN的子查询

IN关键字主要用于判断表达式是否在多值列表中。返回在多值列表中的记录。

--列出选修了C01课程的学生的学号、姓名
select sno, sname
from student
where sno in (select sno from sc where cno='C01');

在这里子查询里面没有依赖父查询,此种查询也叫做不相关子查询

若子查询条件依赖于父查询,则为相关子查询

带比较运算符的子查询

带比较运算符的子查询指父查询与子查询之间通过比较运算符连接。并且子查询返回的是单值,才可以用 = 、<、 >、 != 、>=、 <=等比较运算符连接。

select cno from sc as x
where score > 
(
    select avg(score) from sc as y where x.sno=y.sno and x.sno = '2016110129'
)
and sno = '2016110129';

这个子查询依赖于父查询,属于相关子查询

因为这里将同一张表既作为父查询的表又作为子查询的表,所以将这张表取了两个别名,以便区分。

查询的过程为:将父查询中的sno代入子查询中sno进行匹配然后判断该记录中的课程成绩是否大于该学生的平均成绩,符合条件则返回该记录,否则继续匹配该学生的下一条记录。

带ANY(SOME)或ALL子查询

子查询返回单值时的比较,可以用上面的比较运算符,当返回多值时需要比较,就要使用ANY(SOME)或者ALL。

若是在与多值序列的比较中,只需要满足与多值序列中的一个值满足比较关系就返回True,则用ANY(SOME)。

若是在与多值序列的比较中,需要满足与多值序列中的全部值满足比较关系才返回True,则用ALL。

--查询选课人数最多得课程号
select cno from sc
group by cno
having COUNT(*) >= all(select COUNT(*) from sc group by cno);
带EXISTS的子查询

EXISTS代表存在量词存,带有EXISTS的子查询不返回任何数据,只产生逻辑真值“True”或者逻辑假值“false”。

使用EXISTS后,若内查询结果不为空,则外查询结果返回真值,否则返回假值。

使用EXISTS引出的子查询,其目标表达式列都使用*,因为带EXISTS的子查询只返回真值或假值,给出列名无实际含义。

--列出选修了C01课程的学生的学号、姓名
select sno,sname from student
where exists(
select * from sc where sc.sno=student.sno and cno='C01'
);

这个查询的一个大概过程:从student的第一条记录开始查询,将第一条记录代入子查询中,在sc表中匹配该学生选课记录,若匹配到则立刻返回真,父查询中输出该记录;若匹配完后结果仍为空,否则返回假,继续父查询继续代入下一条记录到子查询中查询。

在带IN的子查询中,会遍历sc表中所有记录进行筛选,带EXISTS的查询找到一条记录就返回,不会遍历整个表,所以由此可见带EXISTS的查询是一个优质查询

附加一题作为EXISTS的练习

“查询选了所有课程的学生”

这里需要使用双层带not exists(即不存在)关键词的查询。具体查询语句如下:

--查询选了所有课程的学生
select sno,sname from student where not exists(
    select * from course where not exists (
        select * from sc where sc.sno=student.sno and sc.cno=course.cno
    )
);

这个相当于一个进行一个双重循环,因为是选出学生的信息,所以student表作为“外层循环”,course表作为“内循环”,在sc表中查询学生的选课记录是否存在。

把student表中第一个学生代入“内循环”,然后开始“内循环”,在sc表中查询该学生是否选了course表中所有课程。

如果遍历了course表后,不存在没有被选的课程(课程在sc表中没有记录),则说明该学生选了所有课程,内部not exists就会返回假,外部not exists返回为真,说明该学生不存在没有选的课程,外部查询输出该学生的信息开始下一个学生的查询。

在遍历course表时,若有一个课程没有被选,则内部就会立刻返回真,外部查询返回为假,说明该学生没有选完所有课程,外部查询就会开始下一个学生的查询。

基于派生表的查询

SELECT查询的结果也是一张表,可以作为出现在FROM子句后面作为派生表进行查询。“`

--求学分获得8分以上学生的学号 平均分  以及总学分  
--需要注意此处的作用域不同,只有该课程的成绩大于60才会获得该课程的学分,平均分包括了所有课程(不及格和及格)
--思路:先将查询到的总学分结果看做是一张表 再与sc表连接进行查询平均分
select sc.sno,total_cre,avg(score) from
    (select sno,SUM(ceredit) as total_cre from sc,course where sc.cno=course.cno 
    and score >= 60
    group by sno 
    having SUM(ceredit) >= 8) as temptable, sc
where temptable.sno=sc.sno 
group by sc.sno,sum_cre;

这个零散地总结记录了学习SQL中嵌套查询中的要点,后续有了新的理解再更。

相关文章
最新文章
热点推荐