AHU-数据库原理--第4次实验

【实验名称】实验4  嵌套查询

【实验目的】

1.熟悉基本的嵌套查询与集合查询的概念和作用；

2. 了解数据库管理系统DBMS 实现嵌套查询的基本方法；

3. 掌握SQL语言嵌套查询与集合查询语句的语法和功能，掌握并熟练运用嵌查询与集合查询语句实现数据库的查询应用。

4. 了解SQL查询结果的处理方式

【实验内容】

针对供应管理数据库SPJ，进行各种嵌套与集合查询：

1. 查询使用了“S001”供应商供应的零件的工程项目信息；

图片：

代码：

select *

from j082

where jno in

  (select jno

    from spj082

      where sno='S001')

1. 查询供应零件“螺丝刀”的供应商信息；

图片：

代码：

select *

from s082

where sno in

  (select sno

    from spj082

      where pno in

        ( SELECT pno

            from p082

              where pname='螺丝刀'))

1. 查询供应商“北京新天地”的所有客户（工程项目）的信息；

图片：

代码：

select *

from j082

where jno in

  (select jno

    from spj082

      where sno in

        ( SELECT sno

            from s082

              where sname='北京新天地'))

(4)查询供应了“J003”工程“P001”零件的供应商信息；

图片：

代码：

select *

from s082

where sno in

  (select sno

    from spj082

      where jno='J003' and pno='P001')

(5)查询使用了“天津”供应商供应的“红色”零件的工程项目信息；

图片：

代码：

select *

from j082

where jno in

  (select jno

    from spj082

      where sno IN

      (SELECT sno

          from s082

            where city like '天津')and pno IN

                                    (SELECT pno

                                        from  p082

                                          where color like '%红%'))

  

(6)查询没有使用“天津”供应商供应的“红色”零件的工程项目信息；

图片：

代码：

select *

from j082

where jno not in

  (select jno

    from spj082

      where sno IN

      (SELECT sno

          from s082

            where city like '天津')and pno IN

                                    (SELECT pno

                                        from  p082

                                          where color like '%红%'))

  

7.查询使用了“S001”供应的“螺母”零件的工程信息；

图片：

代码：

select *

from j082

where jno in

  (select jno

    from spj082

      where sno like 'S001' and pno IN

                                    (SELECT pno

                                        from  p082

                                          where pname like '%螺  母%'))

  

8.查询使用了 “螺母”零件的工程信息及相应供应商信息；

图片：

代码：

select *

from j082,s082

where jno in

  (select jno

    from spj082

      where pno IN

             (SELECT pno

                  from  p082

                     where pname like '%螺  母%'))

  

9.查询给“长春一汽”项目供应零件的供应商信息和供应情况；

图片：

代码：

SELECT

S082.SNO,

SNAME,

S082.CITY,

P082.PNO,

PNAME,

COLOR,

WT

FROM

S082, SPJ082, J082, P082

WHERE

  SPJ082.SNO = S082.SNO

AND SPJ082.JNO = J082.JNO

AND SPJ082.PNO = P082.PNO

AND JNAME like '%长春一汽%'

10.查询给“北京三建”项目供应“螺丝刀”零件最多的供应商信息和供应情况;

图片：

代码：

SELECT

S082.SNO,

SNAME,

S082.CITY,

P082.PNO,

PNAME,

COLOR,

WT

FROM

S082, SPJ082, J082, P082

WHERE

  SPJ082.SNO = S082.SNO

AND SPJ082.JNO = J082.JNO

AND SPJ082.PNO = P082.PNO

AND JNAME like '%北京三建%'

AND WT IN ( SELECT MAX ( WT ) FROM P082 WHERE PNAME like '%螺丝刀%' )

11.查询使用了“螺母”或“螺丝刀”零件的工程项目信息；

图片：

代码：

SELECT * 

FROM j082

WHERE jno IN 

    ( SELECT jno

        FROM SPJ082

          WHERE pno IN 

          ( SELECT pno

              FROM p082

                 WHERE PNAME like '%螺  母%'))

union

SELECT * 

FROM j082

WHERE jno IN 

    ( SELECT jno

        FROM SPJ082

          WHERE pno IN 

          ( SELECT pno

              FROM p082

                    WHERE pname like '%螺丝刀%' ) )

order by jno

  

12.查询既使用了“螺母”又使用了“螺丝刀”零件的工程项目信息；

图片：

代码：

SELECT * 

FROM j082

WHERE jno IN 

    ( SELECT jno

        FROM SPJ082

          WHERE pno IN 

          ( SELECT pno

              FROM p082

                 WHERE PNAME like '%螺  母%'))

INTERSECT

SELECT * 

FROM j082

WHERE jno IN 

    ( SELECT jno

        FROM SPJ082

          WHERE pno IN 

          ( SELECT pno

              FROM p082

                    WHERE pname like '%螺丝刀%' ) )

order by jno

13.查询使用了“螺母”零件但没有使用“螺丝刀”零件的工程项目信息；

图片：

代码：

SELECT * 

FROM j082

WHERE jno IN 

    ( SELECT jno

        FROM SPJ082

          WHERE pno IN 

          ( SELECT pno

              FROM p082

                 WHERE PNAME like '%螺  母%'))

EXCEPT

SELECT * 

FROM j082

WHERE jno IN 

    ( SELECT jno

        FROM SPJ082

          WHERE pno IN 

          ( SELECT pno

              FROM p082

                    WHERE pname like '%螺丝刀%' ) )

order by jno

14.查询使用过所有供应了零件的供应商都供应了零件的工程项目信息。

不存在没有供应零件的供应商

图片：

代码：

SELECT * 

FROM

j082

WHERE NOT EXISTS (

SELECT * 

FROM p082

WHERE NOT EXISTS 

( SELECT * 

    FROM spj082

      WHERE spj082.PNO = p082.PNO AND spj082.JNO = j082.JNO )

)

【小结或讨论】

  本次试验对我来说难度明显提升

首先是不断的嵌套让我有点晕头转向的

其次是对于各种语句的使用不熟练

本次我首次认识了union是两个集合取并集，intersect是两个集合取交集，except是两个集合求差集，这三个运算在要求同一列中的元素或与非上具有不可替代的作用

其次我觉得很难得是两次，里面有一题“使用过所有供应了零件的供应商都供应了零件”这句话我来回读了很多遍不得其意，但看了ppt之后发现可以将语句进行转换”不存在没有供应零件的供应商”, NOT EXISTS两个的使用使这个得以完成

第三是“供应商信息和供应情况”如何将之进行连接，后来采用连接后特意提取特征完成，

最后难住我的是“供应“螺丝刀”零件最多”，最后还是采取了嵌套，AND WT IN ( SELECT MAX ( WT ) FROM P082 WHERE PNAME like '%螺丝刀%' )，可能当时脑子已经一片浆糊了，感觉对于题目的感知力都下降了。

总之这次实验完成其实我还是有很多不明白的，下去之后我要对着课外练习再次精进！