多表查询实验报告

PAGEPAGE8《数据库系统概论》实验报告实验名称：多表查询实验人：实验地点：实验楼317实验日期：2009-10-12

实验准备1．硬件及软件环境要求为了使该实验顺利进行，需要有一台计算机，计算机必须安装Windows2000、WindowsXP或WindowsNT操作系统，还必须安装MicrosoftSQLServer2000任意一版本（个人版、标准版、企业版）。实验开始之前，必须将实验1.2节中创建好的SCDB数据库和ShiYan数据库附加到当前SQL数据库服务器中。2．本实验涉及的主要SQL语句的语法格式及各种操作说明数据库各数据表中存放着不同的数据，用户经常需要用多个表中的数据来组合提炼出所需的数据信息，如果一个查询需要对多个表进行操作，这一查询就称为多表查询（或关联查询）。多表查询是通过各个表之间共同的关联属性列来实现数据查询的，它是关系数据库查询操作的一种最常见的方法。多表查询主要表现为连接查询、嵌套查询、集合查询等几种形式。(1)连接查询连接查询是关系数据库中最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询。1)等值、非等值和自然连接查询连接查询中用来连接两张表的条件称为连接条件或连接谓词，其语法为：SELECTcolumn_name[,...n]

[FROMtablename1,tablename2]

WHERE[tablename1].<column_name><operator>[tablename2].<column_name>【说明】operator：连接运算符。当为“=”时，称为等值连接；否则称为非等值连接；在等值连接中，如果将目标列中重复的属性列去掉则称为自然连接。2)外连接查询在通常的连接操作中，只有满足条件的元组才能作为结果输出，但如果想以某张表作为主体列出所有的情况，在没有信息的情况下，只能输出其基本情况信息，这时就需要使用外连接查询。其语法为：SELECTcolumn_name[,...n]

[FROMtablename1,tablename2]

WHERE[tablename1].<column_name>[(*)]<operator>[(*)][tablename2].<column_name>【说明】外连接的表示方法为在连接谓词的某一边加符号“*”（外连接符）。外连接符出现在<operator>的左边称为左外连接；外连接符出现在<operator>的右边称为右外连接。3)复合条件连接查询如果在查询语句的WHERE子句中有多个连接条件，则称为复合条件连接查询。其语法为：SELECTcolumn_name[,...n]

[FROMtablename1,tablename2]

WHERE[tablename1].<column_name><operator>[tablename2].<column_name><link>[tablename1].<column_name><operator>[tablename2].<column_name>[,...n]【说明】link：逻辑连接词，可以是AND、OR、NOT或其他限定条件。(2)嵌套查询一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。在嵌套查询中，子查询的结果往往是一个集合，因此IN、EXISTS等谓词经常使用在嵌套查询中。(3)集合查询如果有多个不同的查询结果数据集，但又希望它们连接在一起，组成一组数据，这组数据就是这多个结果集的逻辑联合。在这种情况下，通常可以使用UNION子句。使用UNION子句时的两条基本原则：·每一个查询结果集的数据类型必须相同；·每一个查询结果集中的列数必须相同，列的排列顺序必须相互对应。实验目的1．了解查询的概念和方法；2．掌握查询分析器的使用方法；3．掌握复杂查询的实现方法；4．掌握多表连接的方法；5．掌握嵌套查询与集合查询的基本方法；6．掌握SELECT语句在多表查询中的应用。实验内容针对实验数据库ShiYan，完成以下多表查询操作：1．查询为工程J1供应红色零件的供应商号码SNO。SELECTSPJ.SNOFROMSPJWHERESPJ.JNO='j1'ANDSPJ.PNOin(selectP.PNOfromPwhereP.COLOR='红')2．查询没有使用天津供应商生产的零件并且当前工程所使用零件的颜色全部为红色的工程号JNO。selectSPJ.JNOfromSPJwhereSPJ.PNOin(selectP.PNOfromPwhereP.COLOR='红')ANDSPJ.SNOin(selectS.SNOfromSwhereS.CITY!='天津')3．查询至少选用了供应商S1所供应的全部零件的工程号JNO。selectSPJ.JNOfromSPJwhereSPJ.SNO='S1'4．找出工程项目J2使用的各种零件的名称及其重量。selectP.PNAME,P.WEIGHTfromPwhereP.PNOin(selectSPJ.PNOfromSPJwhereSPJ.JNO='J2')5．找出上海厂商供应的所有零件号码。selectDISTINCTSPJ.PNOfromSPJwhereSPJ.SNOin(selectS.SNOfromSwhereS.CITY='上海')6．找出使用上海产的零件的工程名称。selectDISTINCTSPJ.JNOfromSPJwhereSPJ.SNOin(selectS.SNOfromSwhereS.CITY='上海')7．找出没有使用天津产的零件的工程号码。selectDISTINCTSPJ.JNOfromSPJwhereSPJ.JNOnotin(selectDISTINCTSPJ.JNOfromSPJwhereSPJ.SNOin(selectS.SNOfromSwhereS.CITY='天津'))8．找出重量最轻的红色零件的零件编号PNO。selectP.PNOfromPwhereP.WEIGHT<=all(selectP.WEIGHTfromPwhereP.COLOR='红')9．找出供应商与工程所在城市相同的供应商提供的零件号码。SELECT

DISTINCT

P.PNO

FROM

S,J,SPJ,P

WHERE

S.CITY

=

J.CITY

AND

J.JNO

=

SPJ.JNO

AND

S.SNO

=

SPJ.SNO

AND

SPJ.PNO

=

P.PNO10．找出所有这样的一些＜CITY，CITY，PNAME＞三元组，使得第一个城市的供应商为第二个城市的工程供应零件的名称为PNAME。SELECTS.CITY,J.CITY,P.PNAMEFROMS,J,SPJ,PWHEREJ.JNO=SPJ.JNOANDS.SNO=SPJ.SNOANDSPJ.PNO=P.PNO11．重复第15题，但不检索两个CITY值相同的三元组。SELECTS.CITY,J.CITY,P.PNAMEFROMS,J,SPJ,PWHERES.CITY!=J.CITYANDJ.JNO=SPJ.JNOANDS.SNO=SPJ.SNOANDSPJ.PNO=P.PNO12．找出供应商S1为工程名中含有“厂”字的工程供应的零件数量总和。selectSUM(SPJ.QTY)as'零件数量总和'fromSP