任务5.3 嵌套查询

把内部查询语句的结果作为比较值放在外部查询语句的WHERE或HAVING子句中，这种查询方式叫嵌套查询。内部的查询语句称为子查询。

例：对教务数据库，查询[教师表]中，职称与“刘军芳”相同的教师信息。

USE  教务数据库

GO 

SELECT  *  FROM 教师表   WHERE 职称=(SELECT 职称 FROM 教师表 WHERE 姓名='刘军芳') AND 姓名<>'刘军芳 '

思考：执行   SELECT  *  FROM 教师表   WHERE 职称=(SELECT 职称 FROM 教师表 WHERE 姓名='刘军芳') ，输出的结果会是怎样的？

在子查询中常用的逻辑运算符如表5-5所示。

表5-5逻辑运算符             

嵌套查询是指在一个外层查询中包含有另一个内层查询，其中外层查询称为父查询、主查询。内层查询称为子查询、从查询。

  子查询比较测试的运算符是：＝、<>、<.、>、<＝、>＝。子查询比较测试把一个表达式的值和一个有子查询生成的值进行比较，只是只查询只能返回一个值，否则错误。

现有： [学生图书借阅管理]数据库，内有三个表：借阅信息、图书信息、学生信息）

例1：  查询王大力同学借书的图书编号

USE  [学生图书借阅管理]

GO

Select 图书编号 from 借阅信息

Where 借书证号=(select 借书证号 from 学生信息

                Where 姓名=’王大力’)

 或解：

Select A.借书证号, B.姓名,图书编号 from借阅信息 A ,学生信息  B

Where B.姓名='王大力'  AND  B.借书证号=A.借书证号

例2.  查询图书定价大于平均定价的图书信息

Select * from 图书信息

Where 定价>(select avg(定价) from 图书信息)

例3. 查询计算机类图书的名称、图书类别、定价和平均定价

Select 图书名称,图书类别,定价,(selectavg(定价) from 图书信息

Where 图书类别=’计算机’)

   from 图书信息

      Where  图书类别=’计算机’

例4.   查询借过图书的学生借书证号和姓名

Select 借书证号,姓名

From 学生信息

Where 借书证号 in(select 借书证号 from 借阅信息)

例5. 查询被王大力同学借过的图书名称

Select 图书名称 from 图书信息

Where 图书编号 in(select 图书编号 from借阅信息

                  Where 借书证号=(select 借书证号

from 学生信息

                  where 姓名=’王大力’)

例6.查询计算机类图书定价比电子类图书最高定价还高的图书信息

Select * from 图书信息

Where 图书类别=’计算机’ and 定价>all(select定价 from 图书信息

                                   Where 图书类别=’电子’)

例7.查询计算机类图书定价比电子类图书最低定价高的图书信息

Select * from 图书信息

Where 图书类别=’计算机’ and 定价>any(select定价 from 图书信息

                                   Where 图书类别=’电子’)

例8.利用exists查询所有被借过的图书信息

Select * from 图书信息

Whereexists(select * from 借阅信息

            Where 借阅信息.图书编号=图书信息.图书编号)

例9.查询图书信息表中大于同类图书平均定价的图书名称和定价

Select 图书名称，定价 from 图书信息 as a

Where 定价>(select avg(定价) from 图书信息 as b

            Where b.图书类别=a.图书类别)

现有：系统示例数据库 PUBS

【实例】查询Titles表中价格大于平均价格的书籍信息。

use pubs

go

select * from titles where price>(selectavg(price)from titles)

         利用in关键字实现嵌套查询

  一些嵌套内层的子查询会产生一个值，也有一些子查询会返回一列值。即子查询不能返回带几行和几列数据的表。原因在于子查询的结果必须适合外层查询语句。当子查询产生一系列值时，适合用带in的嵌套查询。

  把查询表达式单个数据由子查询产生一系列的数值相比较，如果数值匹配一系列值中的一个，则返回该记录。

【实例】查询authors表和Titleauthor表中来自Ca州的作家的所有作品和作家编号。

use pubs

go

select title_id,au_id from titleauthor where au_id

in(select au_id from authors where state='ca')

【实例】查询authors表和Titleauthor表中不是来自Ca州的作家的所有作品和作家编号。

select title_id,au_id from titleauthor where au_id not

in(select au_id from authors where state='ca')

         利用Exists嵌套查询

  Exists关键字只是注重查询是否有返回的行。如果子查询返回一个或多个行，则主查询执行查询，否则主查询不执行查询。Exists注重的不是使用子查询的查询结果而是注重子查询是否有结果。

【实例】查询titleauthor表中的信息，条件是有作者来自于“MI”州。

select title_id,au_id from titleauthor where 

exists(select au_id from authors where state='mi')

【实例】

select title_id,au_id from titleauthor where 

notexists(select au_id from authors where state='mi')

         在列的清单中使用嵌套查询

   子查询不仅仅可以放在where语句中也可以放在列中或放在From后面。

【实例】查询来自ca这个州作家的名字，书的代号以及书的销量。

selecta.au_lname,t.title_id,(select sum(qty) from sales where title_id=t.title_id) as'salesqty'

fromtitles t inner join titleauthor ta on t.title_id=ta.title_id inner join authorsa

ona.au_id=ta.au_id where a.state='ca'

  先求出每本书的销量，然后在求出作家的名字和书籍的代号。

         在From后使用嵌套查询

  子查询可以作为一个查询的结果集存放在From的后面，作为一个表来处理，因此必须给结果集起一个别名。

【实例】查询Northwind库订单中产品数量大于10的产品名称、订单编号、数量。

select  * from (select o.orderid,P.productName,o.quantity from [order details] o joinproducts p on o.productid=p.productid)

as twhere quantity>20

         嵌套查询练习

【实例】查询northwind库中orders 表中的6－10条数据。

select  *  from orders where orderid in (select top 10 orderid from orders order byorderid) and

orderid  not in(select top 5 orderid from orders order by orderid)

或

select  *  from (select top 5 * from (select top 10 * from orders order by orderid) as torder by orderid desc) as t

orderby orderid

【实例】根据下表查询所有购买商品为两种或两种以上的购物人记录。

表  商品购物表                                   

select * from shop where shoper in(select shoper fromshop group by shoper having count(shoper)>1)

【实例】 根据下表用一条查询语句查询出每门课都大于80的学生姓名。

select distinct namefrom score where name not in(select name from score where fenshu <80)