实验二 数据查询

实验二 数据查询

 一．实验目的

1.理解数据库中数据的其他查询方法和应用；
2.学会各种查询要求的实现；
3.学会各种查询的异同及相互之间的转换方法。

二. 实验内容

在实验1的基础上，练习其它查询语句的使用，包括计算列、求和、最大、最小值、各类选择条件、字符匹配、分组和排序，连接查询、嵌套查询及EXISTS查询等，体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为简单综合应用打下良好的基础。

1.    查询供应商号码为S1的供应商的名称SNAME，所在城市CITY

实验过程：

SELECT SNAME,CITY

FROM s

WHERE sno='s1';

实验结果：

结果分析：

单表查询，非常简单，从S表里可直接查到S1对应的SNAME 精益CITY 天津

 2.    查询颜色为红色的零件号码

实验过程：

SELECT PNO 零件号码

FROM p

WHERE color='红';

实验结果：

结果分析：

单表查询，非常简单，可以直接从P表当中查询到颜色为红色的零件代码为P1、P4、P6

3.查询工程所在地为天津的工程名称JNAME

实验过程

SELECT JNAME

FROM j

WHERE city='天津';

实验结果

结果分析

同样是单表查询，直接从J表得知，天津的工程名称为弹簧厂那个、造船厂

5.查询供应商号和名称，分别用小写字母和大写字母表示供应商代码

实验过程

1. 小写字母表示供应商代码：

SELECT LOWER(SNO)小写字母表示供应商代码,SNAME 名称

FROM s

1. 大写字母表示供应商代码：

SELECT UPPER(SNO)大写字母表示供应商代码,SNAME 名称

FROM s

实验结果

1. 小写字母表示供应商代码

1. 大写字母表示供应商代码

结果分析

使用LOWER(SNO)将SNO以小写字母显示，使用UPPER(SNO) 将SNO以大写字母显示。

6.查询零件的详细信息，用结果属性名用中文显示。

实验过程

SELECT PNO 零件代码,PNAME 零件名,COLOR 颜色,WEIGHT 重量

FROM p

实验结果

结果分析

列表项后面＋空格+[表头名]，也可以用as代替空格。

1. 查询供应工程J1零件P1的供应商号SNO

实验过程

SELECT SNO

FROM spj

WHERE JNO='J1'AND PNO='P1';

实验结果

结果分析

这个直接从spj表当中用where添加限制条件单表查询供应工程J1零件P1的供应商号SNO为S3

1. 查询供应商的名称和所在城市，并按照所在城市升序排序，同一个城市的按照供应商的名称降序排序

实验过程

SELECT SNAME,CITY

FROM s

ORDER BY CITY,SNAME DESC;

实验结果

结果一

   结果二

结果分析

这时（结果一）我们会发现城市的升降序排列规则与我们的习惯不同，这是MySQL的编码格式所造成的，我们只需要将utf的编码格式换成gb2312（如下图所示），排序规则换成gb2312_chinese_ci就可以实现看似正常一点的排序结果了（结果二）

1. 查询使用供应商S1所供应零件的工程号码

实验过程

SELECT DISTINCT JNO

FROM spj

WHERE SNO='S1';

实验结果

结果分析

    我们只需从spj表里查询就可以了

1. 查询各种零件的平均重量

实验过程

按名字分类

SELECT PNAME,avg(WEIGHT)平均重量

FROM p

GROUP BY PNAME

    按序号分类（但感觉没有意义）

SELECT PNO,avg(WEIGHT)平均重量

FROM p

GROUP BY PNO

实验结果

按名字分类

按序号分类（但感觉没有意义）

结果分析

查询各种零件的平均重量，这道题我做了两种可能，一是按名字分类，二是按序号，自我感觉求平均重量按PNO没有意义呀

1. 查询零件的总个数

实验过程

SELECT COUNT(*) 零件总数

FROM p

实验结果

结果分析

    用count()函数求总数，P表就是6个零件

1. 查询所有以“螺”字开头的零件的零件号、零件名和颜色

实验过程

SELECT PNO 零件号,PNAME 零件名,COLOR 颜色

FROM p

WHERE PNAME LIKE'螺%';

实验结果

结果分析

%代表任意数量的字符，_(下划线)代表一个字符的位置，另外，这种模糊查询，用LIKE，不小心写成“=”，就错了

1. 查询各个供应商供应的零件P3总数量

实验过程

SELECT  SNO,SUM(QTY)零件P3总数量

FROM spj

WHERE PNO='P3'

GROUP BY SNO

   “=”可以用“in”来代替：

SELECT  SNO,SUM(QTY)零件P3总数量

FROM spj

WHERE PNO in ('P3')

GROUP BY SNO

   “not in”也可以实现：

SELECT  SNO,SUM(QTY)零件P3总数量

FROM spj

WHERE PNO NOT in('P1','P2','P4','P5','P6')

GROUP BY SNO

但是，怎样显示所有供应商呢（没有供应P3的显示为空）?这就需要进行s表与spj表的连接了

SELECT s1.SNO,SUM(QTY)零件P3总数量

FROM s s1 LEFT OUTER JOIN (

    SELECT *

    FROM spj

    WHERE PNO='P3') s2 ON s1.SNO=s2.SNO

GROUP BY SNO

实验结果

结果分析

题目当中说求各个供应商供应P3零件的总数目，如果只从spj表里查询就会出现不全的情况，这时候需要回归到s表，用SUM(QTY)函数和GROUP BY SNO 实现对供应数量求和。

1. 供应工程J1红色零件的供应商号SNO

实验过程

左外连接：

SELECT SNO

FROM p LEFT OUTER JOIN spj ON p.PNO=spj.PNO

WHERE spj.JNO='J1' AND p.color='红'

等值连接：

SELECT sno

FROM p,spj

WHERE p.pno=spj.pno AND jno='j1' AND color='红'

嵌套查询

SELECT sno

FROM spj

WHERE jno='j1' AND pno in(

                       SELECT pno

                       FROM p

                           WHERE color='红')

实验结果

结果分析

等值连接和左外连接都是将两个表全连接起来，再根据条件查询。

嵌套查询是先从p表里查到红色零件对应的零件号P1 P4 P6，再去spj表查询同时满足JNO为J1和PNO为P1 P4 P6的供应商号。

 

8.工程项目J2使用的各种零件的名称及数量

实验过程

左外连接：

SELECT p.PNAME,spj.QTY

FROM spj LEFT OUTER JOIN p ON spj.PNO=P.PNO

WHERE JNO='J2';

等值连接：

SELECT PNAME 零件名,QTY 数量

FROM spj,p 

WHERE  spj.PNO=P.PNO and JNO='J2';

实验结果

结果分析

    将p表与spj表进行连接，查询条件为jno=’J2’

     

9.使用上海产的零件的工程的名称

实验过程

三个表的左外连接：

SELECT  DISTINCT j.JNAME

FROM spj LEFT OUTER JOIN s ON spj.SNO=s.SNO LEFT OUTER JOIN j ON spj.JNO=j.JNO

WHERE s.CITY='上海';

三层嵌套查询：

SELECT DISTINCT JNAME

FROM j

WHERE jno in(SELECT jno

FROM spj

WHERE sno in(SELECT sno

FROM s

WHERE city='上海'));

三个表的等值连接：

SELECT DISTINCT JNAME

FROM j,s,spj

WHERE j.JNO=spj.JNO AND spj.SNO=s.SNO AND s.CITY='上海'

实验结果

结果分析

    本题涉及到三个表的连接查询，将三个表直接连接起来非常简单。

使用嵌套查询的意思是先在s表中找到上海的供应商代码s5，再找到spj表s5对应的工程代码J1 J2 J4，最后在j表找到对应的工程名称

 

 

1. 所有项目的使用零件的情况（无论有没有使用零件），包括项目代码、项目名称、零件代码和零件数量

实验过程

SELECT  J1.JNO 项目代码,J1.JNAME 项目名称,SPJ1.PNO 零件代码,SPJ1.QTY 零件数量

FROM j J1  LEFT OUTER JOIN spj SPJ1 ON SPJ1.JNO=J1.JNO

ORDER BY J1.JNO;

实验结果

结果分析

统计所有项目的零件使用情况，要对J表与spj表进行左外连接，保留没有使用零件的项目（显示所有项目）。

1. 供应商S1和S3供应的相同的零件编号

实验过程

内连接：

SELECT DISTINCT spj1.PNO

FROM spj spj1  INNER JOIN  spj spj2 ON spj1.PNO=spj2.PNO

WHERE spj1.SNO='S1' AND spj2.SNO='S3';

旧式内连接：

SELECT DISTINCT `FIRST`.PNO

FROM spj FIRST,spj SECOND

WHERE `FIRST`.PNO=`SECOND`.PNO AND `FIRST`.SNO='S1'AND `SECOND`.SNO='S3'

实验结果

结果分析

    将spj表进行内连接，需要起表别名s1和s3供应相同的零件编号是P1

1. 没有使用天津产的零件的工程号码

实验过程

SELECT JNO

FROM j 

WHERE NOT EXISTS (

                       SELECT *

                       FROM spj

                       WHERE SNO IN (

                                   SELECT SNO

                                   FROM s

                                   WHERE CITY='天津') AND j.JNO=spj.JNO);

嵌套查询：

SELECT jno

FROM j

WHERE jno not in(

        SELECT  DISTINCT JNO

        FROM spj

        WHERE sno in(SELECT sno

                   FROM s

                       WHERE CITY='天津'));

实验结果

结果分析

嵌套查询原理为，先在s表找到天津生产的零件所对应零件号s1 s4，再在spj表内找到s1 s4对应的工程号j1 j2 j3 j4，最后去j表排除j1 j2 j3 j4后剩下的项目代码就是答案了。如果仅在spj表里查询工程号，结果是不全的，要考虑完整。

 

 

Exist查询的原理可以认为类似一个for循环，从j1到j7依次带入查询，通过exist判断是否符合条件，符合天津产的零件的条件，exist为true，not exist为false，不输出，不符合天津产的零件的条件时，not exist为true，就可以输出。（仅仅是理解方法，表述不一定恰当）。

1. 没有使用天津供应商生产的红色零件的工程号

实验过程

Exist方法：

SELECT JNO

FROM j 

WHERE NOT EXISTS (

                   SELECT *

                   FROM spj

                   WHERE SNO IN (

                               SELECT SNO

                               FROM s

                               WHERE CITY='天津' ) AND PNO IN (

                                               SELECT PNO

                                               FROM p

                                       WHERE COLOR='红') AND

j.JNO=spj.JNO);

   嵌套查询：     

   SELECT jno

FROM j

WHERE jno not in(

                SELECT  DISTINCT JNO

                FROM spj

                WHERE sno in(SELECT sno

                                   FROM s

                                   WHERE CITY='天津')

                 AND pno in(SELECT pno

                           FROM p

                           WHERE color='红'));

实验结果

结果分析

Exist方法的原理与18题相类似，多涉及到一个表，多一个限制条件。需要注意的是，天津产的红色零件不可以，但是不是天津产的红的零件还是满足条件的。即天津产和红色是并且的关系（and），所以是排除J3 J4的其他工程。

 

1. 至少用了供应商S1所供应的全部零件的工程号Jno

实验过程

SELECT DISTINCT JNO

FROM spj spj1

WHERE NOT EXISTS

(SELECT *

 FROM spj spj2

 WHERE spj2.SNO='S1' AND NOT EXISTS

(SELECT *

 FROM spj spj3

 WHERE spj3.JNO=spj1.JNO AND spj3.PNO=spj2.PNO)

);

实验结果

结果分析

    由spj表，s1提供p1 p2零件，再找同时使用p1 p2的工程号，是j4