SQL第5章高级查询

第5章高级查询15.1连接查询

5.2集合查询5.3子查询5.1连接查询5.1.1

基本连接5.1.2JOIN关键字5.1.3

内连接5.1.4

外连接5.1.5

交叉连接5.1.6自连接25.1.1基本连接1、基本连接基本连接指在FROM子句中使用逗号进行两个表或多个表之间的连接,该结果集是两个连接表的行数的乘积.35.1.1基本连接4结果buyer_nameAdamBarrAdamBarrAdamBarrAdamBarrqty1553711

AdamBarr1003

SeanChai15

SeanChai5

SeanChai37

SeanChai11

SeanChai1003

EvaCorets15

......salesbuyer_idprod_idqty114323151553711421003buyersbuyer_id1234buyer_nameAdamBarrSeanChaiEvaCoretsErinO’MeliaSELECTbuyer_name,qtyFROMbuyers,sales示例5.1.1基本连接例1:显示所有产品的产品编号ProductID,产品名称ProductName,类别名称CateGoryName,使用的表为产品表Products和类别表CateGories.其代码为:SELECTProductID,ProductName,CateGoryNameFROMProducts,CateGories5注：当在SELECT后显示的列名在两个表中都存在时，需要对列名进行限定，否则将返回错误信息5.1.1基本连接2、同等连接同等连接指在基本连接的基础上使用WHERE子句中提供连接条件.65.1.1基本连接7salesbuyer_idprod_idqty114323151553711421003buyersbuyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyer_id1234结果buyer_nameAdamBarrAdamBarrErinO’MeliaEvaCoretsbuyer_idqty11431553711ErinO’Melia41003SELECTbuyer_name,sales.buyer_id,qtyFROM

buyers,salesWHERE

buyers.buyer_id=sales.buyer_id示例5.1.1基本连接例2:显示所有产品的产品编号ProductID,产品名称ProductName,类别名称CateGoryName,使用的表为产品表Products和类别表CateGories.其代码为:SELECTProductID,ProductName,CateGoryNameFROMProducts,CateGoriesWHEREProducts.CateGoryID=CateGories.CateGoryID85.1.1基本连接3、使用别名例3:显示所有产品的产品编号ProductID,产品名称ProductName,类别编号CateGoryID，类别名称CateGoryName,使用的表为产品表Products和类别表CateGories，并分别为其命名别名.其代码为:SELECTProductID,ProductName,C.CateGoryID,CateGoryNameFROMProductsasP,CateGoriesasCWHEREP.CateGoryID=C.CateGoryID9注：如果为表指定了别名，则在语句中的所有子句都必须使用别名，而不允许使用实际的表名5.1.1基本连接4、多表连接例4:显示所有订单的订单编号OrderID、客户编号CustomerID,产品名称ProductName,订购日期OrderDate，单价UnitPrice，数量Quantity，使用的表为产品表Products和订单详情表OrderDetails，订购表Orders.其代码为:SELECTOD.OrderID,CustomerID,ProductName,

OrderDate,OD.UnitPrice,QuantityFROMOrderDetails

OD,ProductsP,Orders

OWHEREOd.OrderID=O.OrderIDandOD.productid=P.productid10练习查询产品编号（ProductID）在20-40之间的所有产品的产品编号（ProductID）、产品名称(ProductName)、类别名称(CategoryName)，类别描述(Description)、供应商的公司(CompanyName)、联系人名称(ContactName)。使用的基本表为Products、Categories、Suppliers表。115.1连接查询5.1.1

基本连接5.1.2JOIN关键字

5.1.3

内连接5.1.4

外连接5.1.5

交叉连接5.1.6自连接125.1.2JOIN关键字使用JOIN关键字可以引导出多种连接方式，如内连接、交叉连接、外连接、自然连接等。其连接条件主要通过以下方法定义两个表在查询中的关系方式：指定每个表中要用于连接的目标列，即在一个基表中指定外键，在另一个基表中指定与其关联的键。指定比较各目标列的值时要使用比较运算符。135.1.2JOIN关键字使用JOIN关键字连接查询的语法格式为：SELECTselect_listFROMtable_name1join_type

JOINtable_name2[ONjoin_condition][WHEREsearh_conditions][….]145.1.2JOIN关键字语法注释：table_name1，table_name2为连接的基表join_type指连接类型。JOIN分为[INNER]JOIN、LEFT[OUTER]JOIN、RIGHT[OUTER]JOIN、FULL[OUTER]JOIN、CROSSJOIN几种类型，使用不同的类型可以得到不同的查询结果join_condition：两个表之间的连接条件searh_conditions：查询条件155.1连接查询5.1.1

基本连接5.1.2JOIN关键字5.1.3

内连接5.1.4

外连接5.1.5

交叉连接5.1.6自连接165.1.3内连接INNERJOIN…ON内连接使用比较运算符进行多个基表间数据的比较操作，并列出这些基表中与连接条件相匹配的所有的数据行。格式

SELECT列名[,列名]

FROM

表1[别名1][INNER]JOIN

表2[别名2]

ON

连接条件

WHERE查询条件

ORDERBY

排序条件175.1.3内连接INNERJOIN…ON1、等值连接等值连接就是在连接条件中使用等号=比较运算符来比较连接列的列值，其查询结果中列出被连接表中的所有列，并且包括重复列。185.1.3内连接INNERJOIN…ON19salesbuyer_idprod_idqty114323151553711421003buyersbuyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyer_id1234结果buyer_nameAdamBarrAdamBarrErinO’MeliaEvaCoretsbuyer_idqty11431553711ErinO’Melia41003SELECTbuyer_name,sales.buyer_id,qtyFROM

buyersINNER

JOINsalesON

buyers.buyer_id=sales.buyer_id示例5.1.3内连接INNERJOIN…ON20produceprod_idprod_name1234ApplesPearsOrangesBananas5Peachesbuyer_id1234buyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyersbuyer_id1143prod_id231542qtales结果buyer_nameErinO’MeliaAdamBarrErinO’MeliaAdamBarrEvaCoretsprod_nameApplesPearsPearsOrangesPeachesqty37151003511SELECTbuyer_name,prod_name,qtyFROMbuyers

JOINsalesON

buyers.buyer_id=sales.buyer_idJOINproduceON

d_id=d_id示例5.1.3内连接INNERJOIN…ON例5:显示所有订单的订单编号OrderID、客户编号CustomerID,产品名称ProductName,订购日期OrderDate，单价UnitPrice，数量Quantity，使用的表为产品表Products和订单详情表Order

Details，订购表Orders.其代码为:SELECTOD.OrderID,CustomerID,ProductName,

OrderDate,UnitPrice,QuantityFROM[Order

Details]

OD

JOINProductsPONOD.productid=P.productidJOIN

Orders

OON

Od.OrderID=O.OrderID215.1.3内连接INNERJOIN…ON2、不等值连接即在连接条件中使用除等号外的运算符.3、自然连接与等值连接相同，即在连接条件中使用等于运算符比较被连接的列,其使用选择列表指出查询结果所包含的列,并删除连接表中的重复列。22注：由于内连接严格执行连接条件，因此在执行连接查询后，要从结果集中删除在其他表中没有匹配行的所有数据行，所以使用内连接可能会丢失信息5.1.3内连接INNERJOIN…ON例6:显示单价小于等于20的产品的产品名称ProductName、单价UnitPrice、供应商公司名称CompanyName、联系人ContactName信息，使用的表为产品表Products和供应商表Suppliers.SELECTproductname,

unitprice,

companyname,

contactnameFROMproductspJOINsupplierssONp.supplierid=s.supplieridWHEREunitprice<=20235.1连接查询5.1.1

基本连接5.1.2JOIN关键字5.1.3

内连接5.1.4

外连接5.1.5

交叉连接5.1.6自连接245.1.4外连接OUTERJOIN…ON1、外连接外连接是对内连接结果的扩展，即除返回所有匹配行外，还将返回不匹配的部分或全部行.2、外连接类型左外连接LEFT[OUTER]JOIN…ON

显示左表未匹配行右外连接RIGHT[OUTER]JOIN…ON

显示右表未匹配行全外连接FULL[OUTER]JOIN…ON

显示全部匹配及未匹配行25左外连接26buyersbuyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyer_id1234结果buyer_nameAdamBarrAdamBarrErinO’MeliaEvaCoretsbuyer_idqty11431553711ErinO’Melia41003SeanChaiNULLNULLSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersLEFTOUTERJOINsalesON

buyers.buyer_id=sales.buyer_id;示例salesbuyer_idprod_idqty114323151553711421003531000右外连接27buyersbuyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyer_id1234SELECTbuyer_name,sales.buyer_id,qtyFROMbuyersRIGHTOUTERJOINsalesON

buyers.buyer_id=sales.buyer_id;示例salesbuyer_idprod_idqty114323151553711421003531000结果buyer_nameAdamBarrAdamBarrErinO’MeliaEvaCoretsbuyer_idqty11431553711ErinO’Melia41003NULL51000全外连接28buyersbuyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyer_id1234SELECTbuyer_name,sales.buyer_id,qtyFROMbuyersFULLOUTERJOINsalesON

buyers.buyer_id=sales.buyer_id;示例salesbuyer_idprod_idqty114323151553711421003531000结果buyer_nameAdamBarrAdamBarrErinO’MeliaEvaCoretsbuyer_idqty11431553711ErinO’Melia41003NULL51000SeanChaiNULLNULL295.1.4外连接OUTERJOIN…ON左（右）外联接可以从两个表中返回符合联接条件的记录，同时也将返回左（右）边不符合联接条件的记录使用左（右）外联接时，请注意不满足联接条件的记录将显示空值左联接可以显示第一个表中所有记录右联接可以显示第二个表中所有记录可以把LEFTOUTERJOIN或RIGHTOUTERJOIN简写为LEFTJOIN或RIGHTJOIN5.1.4外连接OUTERJOIN…ON例8:1）使用T-SQL语句在PUBS数据库中的jobs表中添加一条记录：’manager’,10,250;2）然后使用左外连接查询所有工种的员工信息。305.1.4外连接OUTERJOIN…ON实现的T-SQL语句:USEpubsGOINSERTjobsVALUES(‘manager’,10,250)GOSELECTjob_desc,fname+lnameasau_nameFROMjobsLEFTOUTERJOINemployeeONjobs.job_id=employee.job_idGO315.1.4外连接OUTERJOIN…ON例9:1）向Region表中插入一条记录,值为(5,’center’)2）查询Territories表的TerritoryID,TerritoryDescription,Region表的RegionDescription

信息3）分别用左外连接、右外连接、全外连接实现，并比较分析查询结果325.1连接查询5.1.1

基本连接5.1.2JOIN关键字5.1.3

内连接5.1.4

外连接5.1.5

交叉连接5.1.6自连接335.1.5交叉连接CROSSJOIN交叉连接在不带WHERE子句时，返回的是被连接的两个表所有数据行的笛卡尔乘积，其使用CROSSJOIN关键字连接两个表格式：

SELECT

列名[,列名]

FROM

表1CROSSJOIN

表2

…345.1.5交叉连接CROSSJOIN35结果buyer_nameAdamBarrAdamBarrAdamBarrAdamBarrqty1553711

AdamBarr1003

SeanChai15

SeanChai5

SeanChai37

SeanChai11

SeanChai1003

EvaCorets15

......salesbuyer_idprod_idqty114323151553711421003buyersbuyer_id1234buyer_nameAdamBarrSeanChaiEvaCoretsErinO’MeliaSELECTbuyer_name,qtyFROMbuyersCROSSJOINsales;示例5.1.5交叉连接CROSSJOIN例10：查询产品表products的产品名称productname，供应商表suppliers的供应商名称companyname及供应商所在区域region，要求供应商区域不为NULL，使用products和suppliers表（用交叉查询）。代码如下：SELECTductname,s.region,panynameFROMproductspCROSSJOINsupplierssWHEREs.regionISNOTNULL365.1连接查询5.1.1

基本连接5.1.2JOIN关键字5.1.3

内连接5.1.4

外连接5.1.5

交叉连接5.1.6自连接375.1.6自连接38结果SELECTa.buyer_idASbuyer1,d_id,b.buyer_idASbuyer2

FROMsalesASa

JOINsalesASbONd_id=d_idGOsalesbbuyer_idprod_idqty114323151553711421003salesabuyer_idprod_idqty114323151553711421003示例121124131414353421424buyer1prod_idbuyer25.1.6自连接使用自联接，可以查询一个表中各记录之间的关系使用自联接时，应注意

引用表的两份副本时，必须使用表的别名生成自联接时，表中每一行都和自身比较一下，并生成重复的记录，使用WHERE子句来消除这些重复记录395.1.6自连接40buyer14prod_idbuyer221SELECTa.buyer_idASbuyer1,d_id,b.buyer_idASbuyer2

FROMsalesASa

JOINsalesASbONd_id=d_idWHEREa.buyer_id>b.buyer_idGOsalesbbuyer_idprod_idqty114323151553711421003salesabuyer_idprod_idqty114323151553711421003结果示例5.1.6自连接例11:查询产品表中同一供应商供应的产品信息SELECTp1.ProductID,p1.supplierID,p2.ProductIDFROMProductsp1INNERJOINProductsp2ONp1.supplierID=p2.supplierIDWHEREductid>ductid41课堂练习以下操作请使用PUBS数据库中的数据表进行操作：1、查询出版社编号（pub_id）是‘0877’的书籍的名称（title）、价格（price）、出版日期（pubdate）、作者姓名（au_fname+au_lname）、作者次序（au_ord）。使用的基本表为titleauthor、titles、authors表.2、使用左连接查询出所有书籍的书名（title）,类型（type）,书籍的作者编号（au_id）,作者次序（au_ord）。42小结[掌握]1、掌握内连接查询；2、掌握外连接查询[了解]1、了解交叉查询和自连接。

43445.2集合操作5.2.1UNION运算符5.2.2INTERSECT运算符5.2.3EXCEPT运算符455.2.1UNION运算符功能:UNION运算符可以将多个结果集求并集并形成一个结果集.要求源表的列类型及个数必须一致语法格式:select_statement1UNION[ALL]select_statement2说明:1、ALL选项表示将所有行合并到结果集中,即保留重复行,当不选择此项时,则结果集中的重复行只保留一行2、查询结果集中的列标题为第一个查询语句的列标题。3、SELECT列表中要有相同的列12465.2.1UNION运算符例1：查询来源于美国的供应商表Suppliers和经销商表Customers的公司名称CompanyName、联系人ContactName

、联系人职务ContactTitle,地址Address、城市City

。SELECTCompanyName，ContactName，ContactTitle，Address，CityFROMSuppliersWHERECountry=‘USA’UNIONSELECTCompanyName，ContactName，ContactTitle，Address，CityFROMCustomersWHERECountry=‘USA’475.2.1UNION运算符例2：查询员工表employees来源于美国的员工的的姓名，职务TitleOfCourtesy

，城市City和经销商表Customers中来源于美国的联系人ContactName、联系人职务ContactTitle、城市City。SELECTfirstname+‘‘+lastnameasname,TitleOfCourtesy,CityFROMemployeesWHERECountry=‘USA’UNIONSELECTContactName,ContactTitle,CityFROMCustomersWHERECountry=‘USA’485.2.1UNION运算符例3：查询员工表employees来源于美国或者雇佣日期在1994年以前的员工的员工编号及姓名SELECTEmployeeID,LastName,FirstNameFROMemployeesWHERECountry=‘USA’UNIONSELECTEmployeeID,LastName,FirstName

FROMemployeesWHEREhiredate<‘1994-1-1‘495.2集合操作5.2.1UNION运算符5.2.2INTERSECT运算符5.2.3EXCEPT运算符5012功能:INTERSECT运算符可以将形成的两个结果集求交集.要求源表的列类型及个数必须一致语法格式:select_statement1INTERSECTselect_statement2说明:1、查询结果集中的列标题为第一个查询语句的列标题。2、INTERSECT运算返回两个结果集中相同的行5.2.2INTERSECT运算符515.2.2INTERSECT运算符例4：查询员工表employees来源于美国并且雇佣日期在1994年以前的员工的员工编号及姓名SELECTEmployeeID,LastName,FirstNameFROMemployeesWHERECountry='USA'INTERSECTSELECTEmployeeID,LastName,FirstNameFROMemployeesWHEREhiredate<'1994-1-1'525.2.2INTERSECT运算符例5：查询ORDERDETAILS表中被订购的产品的产品编号都有哪些？SELECTproductidFROMProductsINTERSECTSELECTproductidFROM[orderdetails]535.2集合操作5.2.1UNION运算符5.2.2INTERSECT运算符5.2.3EXCEPT运算符545.2.3EXCEPT运算符功能:EXCEPT运算符可以找到两个给定的集合之间的差异，即从其左边查询结果中去除右边查询结果中存在的行。语法格式:select_statement1EXCEPTselect_statement212555.2.3EXCEPT运算符例6：查询PRODUCTS表中未被订购过的产品的产品编号。SELECTproductidFROMProductsEXCEPTSELECTproductidFROM[orderdetails]565.2.3EXCEPT运算符例7：查询PRODUCTS表单价小于等于20并且不是未打折商品的产品编号。SELECTproductidFROMProductsWHEREunitprice<=20EXCEPTSELECTproductidFROMproductsWHEREDiscontinued='0'57第5章高级查询5.1连接查询

5.2集合查询5.3子查询585.3子查询子查询:指嵌入在其他SQL语句中的SELECT语句,也称嵌套查询.根据子查询返回的结果不同,子查询分为:单行子查询:返回单列单行数据的子查询语句多行子查询:返回单列多行数据的子查询语句多列子查询:返回多列数据的子查询语句595.3子查询5.3.1单行子查询

5.3.2多行子查询5.3.3其他子查询605.3.1单行子查询当在WHERE中引用单行子查询时,可以使用单行比较符(=,>,<,>=,<=,<>)等.例1:查询出EMPLOYEES表中与员工编号为5号员工是同一个城市的员工的编号、姓名、城市.分析查询条件:CITY=(5号员工所在城市)显示的结果:employeeid,ename,city数据来源:EMPLOYEES表语句SELECTemployeeid,firstname+‘‘+lastnameename,cityFROMEMPLOYEESWHERECITY=(SELECTCITYFROMEMPLOYEESWHEREemployeeid=5)子查询显示结果:city子查询数据来源:EMPLOYEES表子查询条件:员工编号为5615.3.1单行子查询例2:查询出EMPLOYEES表中与员工编号为9号的员工雇佣时间是同一年的所有员工的编号、姓名、雇佣日期.分析查询条件:year(雇佣时间)=(子查询)显示的结果:employeeid,ename,hiredate数据来源:EMPLOYEES表语句SELECTemployeeid,firstname+‘‘+lastnameename,hiredateFROMEMPLOYEESWHEREyear(HireDate)=(SELECTyear(HireDate)FROMEMPLOYEESWHEREemployeeid=9)子查询显示结果:year(雇佣时间)子查询数据来源:EMPLOYEES表子查询条件:EMPLOYEEID=9625.3.1单行子查询例3:查询出供应商表SUPPLIERS中与经销商表CUSTOMERS中客户编号为’AROUT’同在一个城市的所有供应商的信息.分析查询条件:CITY=(子查询)显示的结果:供应商的所有信息*数据来源:SUPPLIERS表语句SELECT*FROMSUPPLIERSWHERECITY=(SELECTCITYFROMCUSTOMERSWHEREcustomerid=‘AROUT’)子查询显示的列:CITY子查询的数据来源:CUSTOMERS子查询条件:customerid=‘AROUT’63练习请查询出PRODUCTS表中高于所有产品平均价格的产品信息.645.3子查询5.3.1单行子查询5.3.2多行子查询5.3.3其他子查询655.3.2多行子查询多行子查询指返回单列多行数据的子查询.当在WHERE中使用多行子查询时,必须使用多行比较符(IN、ALL、ANY)1、使用IN操作符例4:查询出产品表PRODUCTS中其供应商所在城市为‘London’的所有产品的信息.分析查询条件:SupplierIdIN(子查询)显示的结果:产品的所有信息*数据来源:PRODUCTS表语句SELECT*FROMPRODUCTSWHERESupplierIDIN(SELECTsupplieridFROMsuppliersWHEREcity=‘London’)子查询显示的列:SupplierId子查询的数据来源:SUPPLIERS子查询条件:CITY=‘London’665.3.2多行子查询例5:查询经销商表CUSTOMERS中其订购数量在50个以上的所有经销商的信息.分析查询条件:CustomerIdIN(子查询)显示的结果:经销商的所有信息*数据来源:CUSTOMERS表语句SELECT*FROMCUSTOMERSWHERECustomerIDIN(SELECTCustomeridFROMOrdersOjoin[OrderDetails]OD

onO.orderID=OD.orderIDWHEREOD.quantity>=50)子查询显示的列:CustomerId子查询的数据来源:ORDERS，ORDERDETAILS子查询条件:quantity>=5067练习1、查询PUBS数据库中的employee表中其出版社所在国家为’USA’的所有出版社员工的信息。2、查询PUBS数据库中的SALES表中书籍出版时间pubdate在‘1991-10-1’以后的书籍的销售信息。685.3.2多行子查询2、ANY|SOME运算符ANY|SOME操作符必须与单行操作符结合使用，并且返回行只要匹配于子查询的任一结果即可。例6:查询产品表products中其单价大于任何一个类别编号为3的产品的产品编号、名称、单价.分析查询条件:UNITPRICE>ANY(子查询)显示的结果:产品编号,名称,单价数据来源:PRODUCTS表selectproductid,productname,unitpricefromproductswhereunitprice>

any(selectunitpricefromproductswhereCateGoryID=3)语句子查询显示的列:UNITPRICE子查询的数据来源:PRODUCTS子查询条件:类别编号为3等价于查询单价大于类别编号为3的产品中最低价格的产品信息695.3.2多行子查询例7:查询产品表products中其单价小于任何一个在订单详情表中订购数量小于5的产品的单价的产品编号,名称,单价.分析查询条件:UNITPRICE<ANY(子查询)显示的结果:产品编号,名称,单价数据来源:PRODUCTS表selectproductid,productname,unitpricefromproductswhereunitprice<

any(selectunitpricefrom[orderdetails]wherequantity<5)语句子查询显示的列:UNITPRICE子查询的数据来源:orderdetails子查询条件:订购数量<5等价于查询单价小于订购数量小于5的产品的最高价格的产品信息注意:ANY和SOME关键字前不能用NOT运算符705.3.2多行子查询3、ALL运算符ALL操作符必须与单行操作符结合使用，并且返回行必须要匹配于子查询的所有结果。例8:查询产品表products中其单价大于类别编号为3的产品的所有单价的产品编号、名称、单价.分析查询条件:UNITPRICE>ALL(子查询)显示的结果:产品编号,名称,单价数据来源:PRODUCTS表selectproductid,productname,unitpricefromproductswhereunitprice>

ALL(selectunitpricefromproductswhereCateGoryID=3)语句子查询显示的列:UNITPRICE子查询的数据来源:PRODUCTS子查询条件:类别编号为3等价于查询单价大于类别编号为3的产品中最高价格的产品信息715.3.2多行子查询例9:查询产品表products中其单价小于在订单详情表中订购数量小于5的所有产品的单价的产品编号,名称,单价.分析查询条件:UNITPRICE<ALL(子查询)显示的结果:产品编号,名称,单价数据来源:PRODUCTS表selectproductid,productname,unitpricefromproductswhereunitprice<

ALL(selectunitpricefrom[orderdetails]wherequantity<5)语句子查询显示的列:UNITPRICE子查询的数据来源:orderdetails子查询条件:订购数量<5等价于查询单价小于订购数量小于5的产品的最低价格的产品信息注意:ALL关键字前不能用NOT运算符725.3.2多行子查询ANY运算符与ALL运算符的比较:ANY运算符把列值与子查询的结果值进行比较,只要任意列值的比较结果为真即可返回对应的行ALL运算符把列值与子查询的结果值进行比较,要求所有的列值的比较结果为真才可返回对应的行73练习1、查询PUBS数据库中的titles表中书籍价格高于类别为‘business’的任一书籍价格的所有书籍的信息。2、查询PUBS数据库中的titles表中书籍价格高于