學(xué)習(xí)-數(shù)據(jù)庫sql多表查詢

SQL多表查1、集合理1.1集 1rows1 0rowsn也可以有多個列，這些列可以是從多個表中取出的多行信息。mrowsn1)ABe∈Ae∈B｝ABe|e∈Ae!∈B}ABe∈A交FROMproductspFROMmore_productsm差FROMproductspFROMmore_productsm并--使用unionall--unionall操作符返回查詢所檢索出的所有行，包括重復(fù)行。SELECTduct_id,duct_type_id,FROMproductspunionFROMmore_productsmselect*fromproductsselect*frommore_productsFROMproducts--ORDERBY1UNIONFROMmore_productsORDERBY1--使用unionFROMproductsFROMmore_productsORDERBY1SELECTproduct_id,product_type_id,NAMEFROMproductsSELECTprd_id,prd_type_id,NAMEFROMmore_productsSELECTproduct_id,product_type_id,NAMEFROMSELECTproduct_id,product_type_id,NAMEFROMproducts(SELECTprd_id,prd_type_id,NAMEFROMmore_productsSELECTproduct_id,product_type_id,NAMEFROM內(nèi)連2.1連連接在SQL92join關(guān)鍵字來實現(xiàn)，JOIN關(guān)鍵字操作本質(zhì)上來說在一個SQL語句中，JOINFROMFROMjoin_tablejoin_typejoin_table[ON(join_condition)]----左外連接(LEFTOUTERJOIN或LEFTJOIN)、右外連接(RIGHTOUTERJOINRIGHTJOIN)和全外連接(FULLOUTERJOINFULLJOIN)SQL92標(biāo)準(zhǔn)來說，交叉連接(CROSSJOIN)ONUSING關(guān)鍵字；對于SQL89標(biāo)準(zhǔn)來說，就是不帶where搜索條件的連接。SSELECT*FROMproducts,--連接操作中的ON(join_condition)子句連接條件，查詢會將A表的每一行和B表的每一行進(jìn)行比較，并找出滿足連接謂詞的組合。當(dāng)連接謂詞被滿足，A和B中匹配的行會按列組合(并排組結(jié)果集中的一行。AB中的每一行組合，然后返回滿足連接謂詞的記錄。FROMtable_nameINNERJOIN ONsearch_condition;FROMtable_nameINNERJOIN SELECTvalue_expressionWHEREsearch_condition;WHEREONSELECT*FROMproducts;SELECT*FROMproduct_types;FROMduct_type_id=SELECT,product_FROMduct_type_id=ON=比較操作符SELECT*FROMproducts;SELECT*FROMpurchases;SELECTFROMproductsINNERJOINpurchasesONduct_id=ONSELECT*FROMproducts;SELECT*FROMproduct_types;FROMproductsinnerjoinonnvl(duct_type_id,2)<>value_expression中不能使用表別名。SELECT*FROMproducts;SELECT*FROMpurchases;SELECTcustomer_id,product_id,price,tyFROMpurchasesnaturalinnerjoinproducts;duct_id=SELECT,FROMproductsPINNERJOINproduct_typesONduct_type_id=SELECTFROM(select_statement)ONsearch_condition;FROM(select_statement)SELECT*FROMproducts;SELECT*FROMSELECTproduct_id,name,priceFROMproductsWHEREproduct_type_idISNOTNULLANDprice>15SELECTproduct_id,customer_id,tyFROMpurchasesWHEREty>SELECTduct_id,,pc.customer_id,pc.tyFROM(SELECTproduct_id,name,priceFROMWHEREproduct_type_idISNOTNULLANDprice>15)pdINNERJOIN(SELECTproduct_id,customer_id,tyFROMpurchasesWHEREty>1)pc .product_id=duct_id;JOIN和JOINSELECTFROM(table_nameINNERJOINtable_nameONsearch_condition)INNERJOINONFROMtable_nameINNER(table_nameINNERJOINtable_nameONsearch_condition)ONsearcha_condition;SELECT*FROMcustomers;SELECT*FROMproducts;SELECT*FROMSELECTc.customer_id,c. _name,c.last_nameFROM(productspdINNERJOINpurchasespcONduct_id=duct_idANDpc.ty>1)INNERJOINcustomerscONc.customer_id=SELECTc.customer_id,c. _name,c.last_nameFROMcustomerscINNERJOIN(productspdINNERJOINpurchasesONduct_id=ANDpc.ty>1)ONc.customer_id=外連3.1外連出現(xiàn)在JOIN前的第一個表為左表，JOIN后的第二個表為右表左外連接：先對AB表做內(nèi)連接，再將A表沒有匹配的行加入到結(jié)果集中得到最終的FROMtable_nameLEFTOUTERONFROMtable_nameRIGHTOUTERONSELECTvalue_expressionSELECTvalue_expression1SELECT*FROMproducts;SELECT*FROMproduct_types;SELECTduct_id duct_type_idlpti, rnameFROMproductsleftouterJOINproduct_typesONduct_type_id=duct_type_idORDERBY1;2SELECT*FROMproducts;SELECT*FROMSELECTduct_idlpi,lname,pc.tyrtyFROMproductspleftOUTERJOINpurchasesONduct_id=duct_idORDERBY1;3SELECT*FROMproducts;SELECT*FROMSELECTduct_idlpi,lname,pc. tyr FROMproductspleftOUTERJOINpurchasesONduct_id=duct_idANDduct_id<>1ORDERBY1;4SELECTduct_idlpi,lname,pc.tyrtyFROMproductspLEFTOUTERJOINpurchasesONduct_id=duct_idWHEREduct_id<>1ORDERBY1SELECT*FROMproducts;SELECT*FROMproduct_types;SELECTduct_idlpi,lname,rnameFROMproductspRIGHTOUTERJOINproduct_typesONduct_type_id=duct_type_idORDERBY1;2SELECTduct_idlpi,lname,FROMproductsRIGHTOUTERJOINproduct_typesONduct_type_id=duct_type_idAND<>' ORDERBYSELECTFROM(select_statement)1SELECT*FROMproducts;SELECT*FROMSELECTduct_id,,,pc.customer_id,pc.FROM(SELECTproduct_id,name,priceFROMproductsWHEREproduct_type_idISNOTNULLANDprice>15)pdLEFTOUTERJOIN(SELECTproduct_id,customer_id,tyFROMpurchasesWHEREty>1)pc .product_id=duct_id;2SELECT*FROMproducts;SELECT*FROMproduct_types;SELECTduct_id,duct_type_id,,,duct_type_id,FROM(SELECTproduct_id,product_type_id,name,priceFROMproductsWHEREprice>15)RIGHTOUTERJOIN(SELECTproduct_type_id,FROMWHEREproduct_type_id<>1)ptONduct_type_id=--哪些用戶沒有過Supernova產(chǎn)品(select嵌套SELECT*FROMcustomers;SELECT*FROMproducts;SELECT*FROMSELECTc.customer_id,c. _name,c.last_name,duct_idFROMcustomerscLEFTOUTERJOIN(SELECTFROMproductsinnerJOINpurchasesONduct_id=duct_idWHERE='Supernova')ipONc.customer_id=ip.customer_idWHEREduct_idISNULL;JOIN和JOINSELECTONFROMtable_name(table_nameLEFT/RIGHTOUTERJOINtable_nameONsearch_condition)ONsearch_condition;--哪些用戶沒有過Supernova產(chǎn)品(join嵌套SELECT*FROMcustomers;SELECT*FROMproducts;SELECT*FROMSELECTc.customer_id,c. _name,c.last_name,duct_idFROMcustomerscLEFTOUTERJOIN(productspinnerJOINpurchasespcONduct_id=AND='Supernova')ONc.customer_id=pc.customer_idWHEREduct_idISNULL;SELECTSELECT*FROMproducts;SELECT*FROMproduct_types;SELECTduct_idlpi,lname,rnameFROMproductspFULLOUTERJOINproduct_typesONduct_type_id=duct_type_idORDERBY1;交叉連接返回符合邏輯表達(dá)式的行(內(nèi)連接加入左表不符合邏輯表達(dá)式的行(左外連接)交叉連接返回符合邏輯表達(dá)式的行(內(nèi)連接加入右表不符合邏輯表達(dá)式的行(右外連接)交叉連接->((右外連子查4.1子查select1。ncols0/1rows(2)表子查詢(TABLEsubquery)一個嵌入的select表達(dá)式，返回單個或多個列，行數(shù)任意。 ncolsmrows(3)標(biāo)量子查詢(scalarsubquery) 1cols1SQLwhere、havingon子句中查詢條件謂詞的SELECT*FROMproducts;SELECT*FROMWHERE(product_id,price)>(1,selectwhere子句需要的比較值列表中使用通過標(biāo)量子查詢得到的單個SELECTvalue_expressionFROMtable_name;+-*/SELECT*FROMcustomers;SELECT*FROMproducts;SELECT*FROM--在select子句SELECTc. =c.customer_id) FROMcustomersc;SELECTc. (SELECTCOUNT(*)FROMpurchasespWHEREp.customer_id= FROMcustomers--在where搜索條件中SELECT*FROMproducts;SELECT*FROMpurchases;duct_idfrompurchasesSELECTduct_id,,p.priceFROMproductspWHEREduct_id(duct_idfrompurchasesselectc. duct_id=innerjoincustomersconc.customer_id=SELECTvalue_expressionFROMtable_referenceWHEREsearch_conditionGROUPBYvalue_expressionHAVINGsearch_conditiontable_referenceONWHERE集員IN/NOTFROMtable_referenceSELECT*FROMproducts;SELECT*FROMSELECTFROMWHEREproduct_idIN(SELECTproduct_idFROMSELECTFROMWHEREproduct_idNOTIN(SELECTproduct_idFROMSELECTFROMWHERE(product_id,product_type_id)(SELECTproduct_id,tyFROM定量比較謂詞 ANY=FROMtable_referenceSELECTFROMWHEREproduct_type_id=(SELECTproduct_idFROMSELECTFROMWHEREproduct_id>ANY(SELECTproduct_idFROMSELECTFROMWHEREproduct_id>ALL(SELECTproduct_idFROMall，小于最小的，大于allEXISTSNOTEXISTS否則返回falseNOTEXISTSEXISTSSELECT*FROMproducts;SELECT*FROMSELECTFROMproductspWHEREEXISTS(SELECT*FROMpurchasespc,productspWHEREduct_id=SELECTFROMproductspWHERE