数据库实验----数据查询报告

实验 2 数据查询

 

一、实验目的

１．理解数据库中数据的其他查询方法和应用；

２．学会各种查询要求的实现；

３．学会各种查询的异同及相互之间的转换方法。

 

二、实验内容

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

1. 查询供应商号码为 S1 的供应商的名称 SNAME，所在城市 CITY
2. 查询颜色为红色的零件号码
3. 查询工程所在地为天津的工程名称 JNAME
4. 查询供应商号和名称，分别用小写字母和大写字母表示供应商代码
5. 查询零件的详细信息，用结果属性名用中文显示。
6. 查询供应工程 J1 零件 P1 的供应商号 SNO
7. 查询供应商的名称和所在城市，并按照所在城市升序排序，同一个城市的按照供应  商的名称降序排序
8. 查询使用供应商 S1 所供应零件的工程号码
9. 查询各种零件的平均重量
10. 查询零件的总个数

 

1. 查询所有以“螺”字开头的零件的零件号、零件名和颜色
2. 查询各个供应商供应的零件 P3 总数量
3. 供应工程 J1 红色零件的供应商号 SNO
4. 工程项目 J2 使用的各种零件的名称及数量
5. 使用上海产的零件的工程的名称
6. 所有项目的使用零件的情况（无论有没有使用零件），包括项目代码、项目名称、  零件代码和零件数量
7. 供应商 S1 和 S3 供应的相同的零件编号
8. 没有使用天津产的零件的工程号码
9. 没有使用天津供应商生产的红色零件的工程号
10. 至少用了供应商 S1 所供应的全部零件的工程号 Jno

三、实验代码及方法

/*查询供应商号码为 S1 的供应商的名称 SNAME，所在城市 CITY*/ Select Sname,City

From S

Where SNO='S1';

方法：用 where 条件语句按行从 s 表中进行查询，符合就 Select，不符合就跳过查询结果如下

 

 

 

 

 

 

 

/*查询颜色为红色的零件号码*/ Select PNO

From P

Where Color='红';

 

/*查询工程所在地为天津的工程名称 JNAME*/ Select Jname

From J

Where City='天津';

 

/*查询供应商号和名称，分别用小写字母和大写字母表示供应商代码*/ Select SNO,lower(SNO) as sno,SNAME/*划重点*/

From S

方法：在 sql 语言中，不区分大小写，在输出时 Select 用的什么名就输出什么名，Sno 输出 Sno，SNo 输出 SNo，或者特别标明 lower（Sno）自动转化成小写。

 

 

查询结果如下

 

 

 

 

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

Select Pno as 零件号码,Pname as 零件名,Color as 颜色,Weight as 重量From P

//as 可以省略，直接跟别名

/*查询供应工程 J1 零件 P1 的供应商号SNO*/ Select SNO

From SPJ

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

//双重 and 条件并列成立

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

Select Sname,City From S

Order by City,Sname desc;/*划重点*/

 

//若要按某一列排序，需用 order by 在其列表名加上 desc 标识符，默认升序。有多个参照时，按顺序优先排序。

 

 

/*查询使用供应商 S1 所供应零件的工程号码*/ Select Distinct JNO

From SPJ Where PNO IN

(Select Pno From  SPJ Where SNO='S1'

);

//使用嵌套查询，In 的不相关子查询

 

 

 

/*查询各种零件的平均重量*/

Select Pno,Avg(Weight) as Avg_Weight From P

Group by PNO;

//使用了 Avg 的聚集函数，并且是在 Group by 分组的基础上，Avg 会单独操作分组后的每一个组， 并输出结果。所有聚集函数都是这样

 

/*查询零件的总个数*/ Select Sum(QTY) as Sum_QTY From SPJ;

 

/*查询所有以“螺”字开头的零件的零件号、零件名和颜色*/ Select PNO,PNAME,COLOR

From P

Where Pname like '螺%';/*划重点*/

//‘a%’表示以‘a’开头的无长度限制的字符串，‘a%d’表示以‘a’开头，‘b’为结尾的任意长

//度字符，要用 like 谓词。

/*查询各个供应商供应的零件 P3 总数量*/ Select DISTINCT SNO,Sum(QTY) as Sum_P3 From SPJ

Where PNO='P3' Group by SNO;

//在 SPJ 表里用 SNO 分组（去掉重复列），再用 PNO=’P3’的限定条件，就可以用聚集函数 SUM 将

//QYT 求和了。

/*供应工程 J1 红色零件的供应商号 SNO*/ Select Distinct SNO

From SPJ

Where Jno='J1'and PNO In

(

Select PNO From P

Where Color='红'

);/*划重点*/

//首先确定供应工程为 J1，然后再找出红色零件对应的 Pno，用 In 来查出供应商 Sno

 

 

 

查询结果

 

 

 

 

/*工程项目 J2 使用的各种零件的名称及数量*/ Select Pname,Qty

From P,SPJ

Where SPJ.PNO=P.PNO AND SPJ.JNO='J2';

//使用了连接查询，先把 Spj 表中的属于 J2 的挑出来，然后用这个表与P 表进行自然连接

/*使用上海产的零件的工程的名称*/ Select JNAME

From J,SPJ

Where J.JNO=SPJ.JNO AND PNO in(

Select PNO From SPJ Where SNO in( Select SNO From S

Where City='上海'

)

) AND SNO IN(Select SNO

From S

Where City='上海');

//用了三次嵌套查询

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

Select J.JNO,JNAME,P.PNO,QTY From J,P,SPJ

Where J.JNO=SPJ.JNO AND P.PNO=SPJ.PNO/*执行过程是什么？*/ Order by JNO,PNO

//首先找到 J 表出一个元组，跟spj 按行进行对比，把所有 Jno 相同的连接在一起，直到 J 表检索完

//毕，并且以 JNO 和 PNO 升序排序

 

/*供应商 S1 和 S3 供应的相同的零件编号*/

Select PNO

From SPJ

Where SNO='S1'AND PNO IN(

Select PNO

From SPJWhere SNO='S3'

);

//要找到 S1 和 S3 供应相同的零件标号，首先先找到 S1 的所有元组，然后 AND 嵌套 S3 对应元组，找

//到 S1 的 PNO 与其相同，之后直接 Select 出 PNO 即可。

/*没有使用天津产的零件的工程号码*/

Select JNO

From SPJ

Where JNO Not in

(Select Jno

From SPJ

Where PNO in

(Select Pno

From SPJ,S

Where SPJ.SNO=S.SNO AND S.City='天津'));

//三层嵌套查询，先找到天津产的零件对应的 JNO，再用 NOt In 即可查出

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

Select JNO

From SPJ

Where Jno not in

(Select Jno

From SPJ

Where PNO in(

Select SPJ.PNO

From SPJ,S,P

Where SPJ.SNO=S.SNO AND P.PNO=SPJ.PNO AND S.CITY='天津' AND P.COLOR='红'

)

);

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

Select Distinct Jno

From Spj Spjx

Where Not Exists

(

Select *

From Spj Spjy

Where Sno='s1' And Not Exists

(

Select *

From Spj Spjz

Where Spjx.Jno= Spjz.Jno And Spjy.PNO=Spjz.Pno

)

);/* 查百度 *///问题可以等价为不存在一个 Jno，没有使用 s1 供应的全部零件，还是绕不过弯，老是不明白为什

//么会因为这句话就能写出这串代码

四、实验所遇问题及其解决方法

1. 在查询各个供应商供应的零件 P3 总数量的问题中，查出以下结果，与题不符，

对照代码，发现了问题，题意没读清楚

Select Sum(QTY) as Sum_P3

From SPJ

Where PNO='P3'

改：Select DISTINCT SNO,Sum(QTY) as Sum_P3

From SPJ

Where PNO='P3'

Group by SNO;

解决完毕

2.在至少用了供应商 S1 所供应的全部零件的工程号 Jno 中，读了好几遍也不会，没有思路，

然后询问了老师，老师讲了一遍，把大体框架讲了出来，又听老师的看了书上的

题目，有了那么一点明白，多写了几遍，加深了一下印象。多做这种类型的题，

吃的透透的。

五、实验心得