学习-数据库sql多表查询

SQL多表查1、集合理1.1集 1rows1 0rowsn也可以有多个列，这些列可以是从多个表中取出的多行信息。mrowsn1)ABe∈Ae∈B｝ABe|e∈Ae!∈B}ABe∈A交FROMproductspFROMmore_productsm差FROMproductspFROMmore_productsm并--使用unionall--unionall操作符返回查询所检索出的所有行，包括重复行。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内连2.1连连接在SQL92join关键字来实现，JOIN关键字操作本质上来说在一个SQL语句中，JOINFROMFROMjoin_tablejoin_typejoin_table[ON(join_condition)]----左外连接(LEFTOUTERJOIN或LEFTJOIN)、右外连接(RIGHTOUTERJOINRIGHTJOIN)和全外连接(FULLOUTERJOINFULLJOIN)SQL92标准来说，交叉连接(CROSSJOIN)ONUSING关键字；对于SQL89标准来说，就是不带where搜索条件的连接。SSELECT*FROMproducts,--连接操作中的ON(join_condition)子句连接条件，查询会将A表的每一行和B表的每一行进行比较，并找出满足连接谓词的组合。当连接谓词被满足，A和B中匹配的行会按列组合(并排组结果集中的一行。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外连出现在JOIN前的第一个表为左表，JOIN后的第二个表为右表左外连接：先对AB表做内连接，再将A表没有匹配的行加入到结果集中得到最终的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产品(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产品(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;交叉连接返回符合逻辑表达式的行(内连接加入左表不符合逻辑表达式的行(左外连接)交叉连接返回符合逻辑表达式的行(内连接加入右表不符合逻辑表达式的行(右外连接)交叉连接->((右外连子查4.1子查select1。ncols0/1rows(2)表子查询(TABLEsubquery)一个嵌入的select表达式，返回单个或多个列，行数任意。 ncolsmrows(3)标量子查询(scalarsubquery) 1cols1SQLwhere、havingon子句中查询条件谓词的SELECT*FROMproducts;SELECT*FROMWHERE(product_id,price)>(1,selectwhere子句需要的比较值列表中使用通过标量子查询得到的单个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