下载

3下载券

加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 《跟我学Java 26日通》第 2天 多表查询与数据更新

《跟我学Java 26日通》第 2天 多表查询与数据更新.pdf

《跟我学Java 26日通》第 2天 多表查询与数据更新

bjchengzhimin
2009-10-21 0人阅读 举报 0 0 0 暂无简介

简介:本文档为《《跟我学Java 26日通》第 2天 多表查询与数据更新pdf》,可适用于IT/计算机领域

OracleOraclesyschangoninstallsystemmanagerscotttigerMLDNMLDNSQLPLUSWSETLINESIZESETPAGESIZEedSELECT{DISTINCT}*|,,…FROM{WHERE(s)}{ORDERBYASC|DESC,ACS|DESC,…}TOCHAR()NVL()DECODE()SQL:SELECT{DISTINCT}*|,,…FROM,,…{WHERE(s)}{ORDERBYASC|DESC,ACS|DESC,…}empdeptSELECT*FROMemp,deptempdeptempSELECTCOUNT(*)FROMempdeptSELECTCOUNT(*)FROMdept=emp*deptMLDNempdeptnodeptdeptnoempdeptnodeptdeptnoempdeptno=deptdeptnoWHERESELECT*FROMemp,deptWHEREempdeptno=deptdeptnoMLDNSELECT*FROMempe,deptdWHEREedeptno=ddeptnoSELECTeempno,eename,ddeptno,ddname,dlocFROMempe,deptdWHEREedeptno=ddeptnoMLDNempmgrempempSELECTeename,ejob,menameFROMempe,empmWHEREemgr=mempnodeptMLDNSELECTeename,ejob,mename,ddnameFROMempe,empm,deptdWHEREemgr=mempnoANDedeptno=ddeptnosalgradesalgradeSELECT*FROMsalgradesalgrade,sgradeWHEREedeptno=ddeptnoANDesalBETWEENslosalANDshisalSELECTeename,esal,ddnameFROMempe,deptd,salgradesMLDNSELECTeename,esal,ddname,sgrade,mename,msal,msgradeFROMempe,deptd,salgrades,empm,salgrademsWHEREedeptno=ddeptnoANDesalBETWEENslosalANDshisalANDemgr=mempnoANDmsalBETWEENmslosalANDmshisalMLDNDECODE()SELECTeename,esal,ddname,DECODE(sgrade,,'',,'',,'',,'',,''),mename,msal,DECODE(msgrade,,'',,'',,'',,'',,'')FROMempe,deptd,salgrades,empm,salgrademsWHEREedeptno=ddeptnoANDesalBETWEENslosalANDshisalANDemgr=mempnoANDmsalBETWEENmslosalANDmshisalMLDNdeptSELECT*FROMdeptempdeptSELECTeempno,eename,ddeptno,ddname,dlocFROMempe,deptdWHEREedeptno=ddeptnoSELECTeempno,eename,ddeptno,ddname,dlocFROMempe,deptdWHEREedeptno()=ddeptnoMLDN()=()=SELECTeempno,eename,ddeptno,ddname,dlocFROMempe,deptdWHEREedeptno=ddeptno()MLDNSELECTeempno,eename,mempno,menameFROMempe,empmWHEREemgr=mempnoABKINGPRESIDENTmgrSELECTeempno,eename,mempno,menameFROMempe,empmWHEREemgr=mempno()KINGSQL:SQLSQL:SELECTtablecolumn,tablecolumnFROMtableCROSSJOINtable|NATURALJOINtable|JOINtableUSING(columnname)|JOINtableON(tablecolumnname=tablecolumnname)|LEFT|RIGHT|FULLOUTERJOINtableON(tablecolumnname=tablecolumnname)CROSSJOINSELECT*FROMempCROSSJOINdeptMLDNNATURALJOINSELECT*FROMempNATURALJOINdeptUSINGSELECT*FROMempeJOINdeptdUSING(deptno)WHEREdeptno=MLDNONSELECT*FROMempeJOINdeptdON(edeptno=ddeptno)WHEREdeptno=LEFTJOINRIGHTJOINSELECTeename,ddeptno,ddname,dlocFROMempeRIGHTOUTERJOINdeptdON(edeptno=ddeptno)MLDNSQLCOUNT()MAX()MIN()AVG()SUM()COUNT()SELECTCOUNT(empno)FROMempACOUNT()(B)MIN()MAX()MIN()SELECTMIN(sal)FROMempSELECTMAX(sal)FROMempAMAX()BSUM()MLDNSELECTSUM(sal)FROMempWHEREdeptno=SELECTAVG(sal)FROMempGROUPBYSQLSELECT{DISTINCT}*|,,…FROM,,…{WHERE(s)}{GROUPBY}{ORDERBYASC|DESC,ACS|DESC,…}deptnoSELECTdeptno,COUNT(empno)FROMempGROUPBYdeptnoSELECTdeptno,AVG(sal)FROMempGROUPBYdeptnoMLDNSELECTdeptno,COUNT(empno)FROMempGROUPBYSELECTdeptno,empno,COUNT(empno)FROMempGROUPBYdeptnoempnoGROUPBYMLDNSELECTddname,COUNT(eempno)FROMdeptd,empeWHEREddeptno=edeptnoGROUPBYddnameAVG(sal)>SELECTdeptno,AVG(sal)FROMempWHEREAVG(sal)>GROUPBYdeptnoWHEREHAVINGSQLSELECT{DISTINCT}*|,,…FROM,,…{WHERE(s)}{GROUPBY{HAVING}}{ORDERBYASC|DESC,ACS|DESC,…}MLDNHAVINGSELECTdeptno,AVG(sal)FROMempGROUPBYdeptnoHAVINGAVG(sal)>$job<>'SALESMAN'SELECT*FROMempWHEREjob<>'SALESMAN'SELECTjob,SUM(sal)FROMempWHEREjob<>'SALESMAN'GROUPBYjobMLDNSELECTjob,SUM(sal)FROMempWHEREjob<>'SALESMAN'GROUPBYjobHAVINGSUM(sal)>SELECTjob,SUM(sal)suFROMempWHEREjob<>'SALESMAN'GROUPBYjobHAVINGSUM(sal)>ORDERBYsuMLDNSELECTdeptno,MAX(AVG(sal))FROMempGROUPBYdeptnoSELECTMAX(AVG(sal))FROMempGROUPBYdeptnoSELECT{DISTINCT}*|,,FROM,,(SELECT{DISTINCT}*|,,FROM,,{WHERE(s)}{GROUPBY{HAVING}}{ORDERBYASC|DESC,ACS|DESC,}),{WHERE(s)MLDN(SELECT{DISTINCT}*|,,FROM,,{WHERE(s)}{GROUPBY{HAVING}}{ORDERBYASC|DESC,ACS|DESC,})}{GROUPBY{HAVING}}{ORDERBYASC|DESC,ACS|DESC,}SELECTsalFROMempWHEREempno=salSELECT*FROMempWHEREsal>(SELECTsalFROMempWHEREempno=)()MLDNSELECTsalFROMempWHEREempno=ABSELECTjobFROMempWHEREempno=SELECT*FROMempWHEREsal>(SELECTsalFROMempWHEREempno=)ANDjob=(SELECTjobFROMempWHEREempno=)SELECTMIN(sal)FROMempSELECT*FROMempWHEREsal=(SELECTMIN(sal)FROMemp)MLDNdeptempdeptnoSELECTdeptno,COUNT(empno),AVG(sal)FROMempGROUPBYdeptnodeptSELECTddname,edc,edaFROMdeptd,(SELECTdeptno,COUNT(empno)c,AVG(sal)aFROMempGROUPBYdeptno)edWHEREddeptno=eddeptnoMLDNSELECTddname,edc,eda,eenameFROMdeptd,(SELECTdeptno,COUNT(empno)c,AVG(sal)a,MIN(sal)minFROMempGROUPBYdeptno)ed,empeWHEREddeptno=eddeptnoANDesal=edminINANYALLININSELECT*FROMempWHEREsalIN(SELECTMIN(sal)FROMempGROUPBYdeptno)MLDNANY=ANYINSELECT*FROMempWHEREsal=ANY(SELECTMIN(sal)FROMempGROUPBYdeptno)>ANYSELECT*FROMempWHEREsal>ANY(SELECTMIN(sal)FROMempGROUPBYdeptno)<ANYSELECT*FROMempWHEREsal<ANY(SELECTMIN(sal)FROMempGROUPBYdeptno)MLDNALL>ALLSELECT*FROMempWHEREsal>ALL(SELECTMIN(sal)FROMempGROUPBYdeptno)<ALLSELECT*FROMempWHEREsal<ALL(SELECTMIN(sal)FROMempGROUPBYdeptno)SELECT*FROMempWHERE(sal,NVL(comm,))IN(SELECTsal,NVL(comm,)FROMempWHEREdeptno=)MLDNSELECTINSERTUPDATEDELETEempCREATETABLEmyempASSELECT*FROMempSELECT*FROMmyempINSERTINTO(,)VALUES(,,)myempINSERTINTOmyemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(,'','',,'',,,)MLDNmyempSELECT*FROMmyemp''INSERTINTOmyempVALUES(,'','',,'',,,)INSERTINTOmyemp(empno,ename,job,hiredate,sal,deptno)VALUES(,'','','',,)MLDNINSERTINTOmyempVALUES(,'','',,'',,,)TODATE()DATEINSERTINTOmyemp(empno,ename,job,hiredate,sal,deptno)VALUES(,'','',TODATE('','yyyymmdd'),,)MLDNSQLUPDATEUPDATESET=,=,UPDATESET=,=,WHEREmyempUPDATEmyempSETcomm=myempMLDNUPDATEmyempSETsal=WHEREempno=MLDNUPDATEmyempSETmgr=WHEREempno=UPDATEmyempSETmgr=,comm=WHEREempnoIN(,,)MLDNSQLDELETEDELETEFROMDELETEFROMWHEREDELETEFROMmyempWHEREempno=DELETEFROMmyempWHEREempnoIN(,,,)MLDNDELETEFROMmyempWHEREcommISNOTDELETEFROMmyempCREATETABLEempASSELECT*FROMempWHEREdeptno=empDELETEFROMempWHEREempno=MLDNempOracleOraclesqlplussqlpluswsessionsessionsessionsessioncommitrollback|OraclesessionsessionSELECTdeptno,COUNT(empno)FROMempGROUPBYdeptnoMLDNSELECTdeptno,COUNT(empno)FROMempGROUPBYdeptnoHAVINGCOUNT(empno)>SELECTd*,edcouFROMdeptd,(SELECTdeptno,COUNT(empno)couFROMempGROUPBYdeptnoHAVINGCOUNT(empno)>)edWHEREddeptno=eddeptnoSMITHSMITHSELECTsalFROMempWHEREename='SMITH'SELECT*FROMempWHEREsal>(SELECTsalFROMempWHEREename='SMITH')MLDNSELECTeename,menameFROMempe,empmWHEREemgr=mempnoMLDNmgr=empnohiredateSELECTeempno,eenameFROMempe,empmWHEREemgr=mempnoANDehiredate<mhiredatedeptSELECTeempno,eename,ddnameFROMempe,empm,deptdWHEREemgr=mempnoANDehiredate<mhiredateANDedeptno=ddeptnoMLDNSELECTddeptno,ddname,eempno,eenameFROMdeptd,empeWHEREddeptno=edeptno()CLERKSELECTename,deptnoFROMempWHEREjob='CLERK'MLDNSELECTeename,ddnameFROMempe,deptdWHEREjob='CLERK'ANDedeptno=ddeptnoGROUPBYSELECTeename,ddname,edcouFROMempe,deptd,(SELECTdeptno,COUNT(empno)couFROMempGROUPBYdeptno)edWHEREjob='CLERK'ANDedeptno=ddeptnoANDeddeptno=edeptnocommitrollbackMLDNMLDN

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

评分:

/42

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利