先选择一个数据库
use jobtest
go
引入:该数据库jobtest里面有两张表,Student学生表和Grade年级表,表中的数据如下所示:
学生表Student:
年级Grade表:
接下来我们来看看sql server中的子查询:
子查询
1.查询编号比李太白大的学生信息
--先获取李太白的编号
declare @sid int ;
select @sid = sid from student where sname = '李太白'
--再查询编号比@sid大的信息
select * from student where sid>@sid
当然我们也可以改成子查询:
—可以改成子查询(将@sid替换成获取李太白编号的sql语句)
select * from student where sid>(select sid from student where sname = '李太白')
其运行结果都是:
以上就是一个最简单的子查询。
习惯上,外面的查询称为父查询,括号里面的则是子查询,返回结果集。因为子查询作为where条件的一部分,所以还可以和update、insert以及delete一起使用,语法和select的类似。
注意:将子查询和比较运算符联合使用,必须保证子查询返回的值不多于一个。
还有一个就是:
select * from student不如select sid,sname,sphone,spass,sgid from student的效率高,可维护性也比不上,所以一般使用后者。
下面我们再来一个子查询的案例
需求:查询青鸟一班的学生信息
我们可以看到思路是这样的:
(1)先根据年级名称查询出年级编号
(2)学生表里面的年级编号让和查询出来的年级编号对应起来就可以了
select * from student where sgid = (select gid from grade where gname = '青鸟一班')
运行结果:
in和not in查询
前面我们说过,将子查询和比较运算符联合使用,必须保证子查询返回的值不多于一个。
如果返回多个值的话会怎么样呢?我们不妨来试试、
查询学生表中,年级编号是1的年级名称
select gname from grade where gid =(select sgid from student where sgid = 1)
运行结果:
可见,程序给我报异常了,表示子查询返回了多个结果。
正确写法应该是这样的:
select gname from grade where gid in(select sgid from student where sgid = 1)
in子查询适合用在子查询结果集返回多条记录的情况下。
反过来not in就是正好与in取反,还是同样上面给出的例子,我们换成not in看看结果是什么样的?
select gname from grade where gid not in(select sgid from student where sgid = 1)
结果:
很明显,除了青鸟一班,其余年级名称均已输出。
exists和no exists
exists,只注重子查询是否有返回行,如查有返回行结果为真,否则为假,并不适用子查询的结果,仅使用测试子查询是否有返回结果
下面来举例说明一下:
查询是否有编号为6的学生信息,如果有,则输出‘有编号是6的学生’
if exists(select * from student where sid = 6)
print '有编号是6的学生'
运行结果为:
因为表中并没有编号是6的学生信息。
而not exists正好与exists相反,还是利用上面的案例
if not exists(select * from student where sid = 6)
print '有编号是6的学生'
运行结果为: