學(xué)習(xí)oracle dba12c18c19c ocp認(rèn)證培訓(xùn)實戰(zhàn)教程課程筆記sir課前軟件certified

OracleOracleCertifiedProfessionalOracleDatabaseCode：1Z0-1章關(guān)系數(shù)據(jù)庫及SQL簡EFCodd19706月的一篇題為《大型共享數(shù)據(jù)庫的數(shù)據(jù)關(guān)系模型》的中概述的。它是關(guān)系數(shù)據(jù)庫管理系統(tǒng)(RDBMS)的基礎(chǔ)。SQL(StructuredQueryLanguage)結(jié)構(gòu)化查詢語言，是一種數(shù)據(jù)庫查詢語言，用于查詢和管理關(guān)系數(shù)據(jù)庫系統(tǒng)?；贏NSI（AmericanNationalStandardsDQL：數(shù)據(jù)查詢語言（SELECT）列入 語言里面。TCL： 控制語句（ALTERSESSION、ALTERSYSTEM、SETROLE）ESS：嵌入式TCL重點COMMIT為Oracle數(shù)據(jù)庫的默認(rèn)事務(wù)級別是提交讀（ReadCommitted。 MIT為ON，則在DML語句中插入、修改、刪除 MITON2章基本SELECT語[中，DEPARTMENTS表包含4個列：conn/asalteruserhridentifiedbyhraccountunlock;connhr/hrselect*fromtab;setlinesizeselect*fromSQL語句對大小寫不敏感SQLDeveloper中，SQL語句可以隨意的通過分號來結(jié)束；在執(zhí)行多條語句時，分SQL*Plus中你需要使用分號來結(jié)束每一條語句。+加、-減、*selectlast_name,salary,12*salary+100fromemployees;selectlast_name,salary,12*(salary+100)fromemployees;NULL是一個未使用、未分配的、未知的、或不適用的值?？梢宰兿嗟恼J(rèn)為NULL就是一個長度為0的字符串。很多數(shù)據(jù)類型的列都可以包含空，可是，某些約束（NOTNULL和PRIMARYKEY）是防止在列中使用NULL的。才有傭金，其他的雇員是沒有傭金的，NULL就表實了這個事實。包含NULL值的算術(shù)表達(dá)式值為NULLselectlast_name,12*salaryfromemployees;NOTNULL，不能使用=NULL或者<>NULL，有關(guān)NULL值注意下面幾點：空值不是空格，也不是0；包含空值的數(shù)學(xué)表達(dá)式的值（即加減乘除操作）都為NULL；SQL>selectavg(manager_id)fromdepartments;SQL>selectcount(manager_id)fromdepartments;SQL>selectcount(*)fromdepartments;NULL在排序中默認(rèn)為最大值，DESC在最前，ASC在最后，可以加上NULLSLAST來限制NULL值的顯示；SQL>select*fromdepartmentswheremanager_idnotin(null,114);SQL>select*fromdepartmentswheremanager_idnotin(114);在顯示查詢結(jié)果時，SQL通常用被選擇的列的名字作為列標(biāo)題，該標(biāo)題的描述可能不selectlast_name mission_pctcommfromemployees;selectlast_name mission_pctCommfromemployees;selectlast_name"Name",commission_pct"Comm"fromemployees;SELECT和ORDERBYWHERE子句SQL>selectdepartment_namename,location_ididfromdepartmentswhereid=1700andrownum<4;selectdepartment_namename,location_ididfromdepartmentswhereid=1700and*ERRORatlineORA-00904:"ID":invalidSQL>select*from (selectdepartment_namename,location_ididfromdepartments)awherea.id=1700;1Z0-071QuestionYouwanttodisythememberIDs,duedate,andlatefeeas$2foralltransactions.WhichSQLstatementmustyouexecute?SELECTmember_idASMEMBER_ID,due_dateASDUE_DATE,$2ASLATE_FEEFROMBOOKS_TRANSACTIONS;SELECTmember_id‘MEMBER_ID’,due_date‘DUEDATE’,‘$2ASLATEFROMSELECTmember_idAS"MEMBERID",due_dateAS"DUEDATE",‘$2’AS"LATEFEE"FROMBOOKS_TRANSACTIONS;SELECTmember_idAS"MEMBERID",due_dateAS"DUEDATE",$2ASFEE"FROM連接運算符（管道符如果字符串連接一個NULLSELECT列表中的列處理。日期和字符文字必須放在單引號中，數(shù)字不需selectlast_name||'isa'||job_idas"EmployeeDetails"fromemployees;selectlast_name||'''sjobidis:'||job_id"EmployeeDetails"fromemployees;selectdepartment_name||q'[Department'sManagerId:]'||manager_idas"DaM"fromdepartmentswheremanager_idisnotnull;selectdepartment_name||q'\Department'sManagerId:\'||manager_idas"DaM"fromdepartmentswheremanager_idisnotnull;selectdepartment_name||q'(Department'sManagerId:)'||manager_idas"DaM"fromdepartmentswheremanager_idisnotnull;selectdepartment_name||q'{Department'sManagerId:}'||manager_idas"DaM"fromdepartmentswheremanager_idisnotnull;selectdepartment_name||q'<Department'sManagerId:>'||manager_idas"DaM"fromdepartmentswheremanager_idisnotnull;selectdepartment_name||q''Department'sManagerId:''||manager_idas"DaM"fromdepartmentswheremanager_idisnotnull;selectdepartment_name||q'"Department'sManagerId:"'||manager_idas"DaM"fromdepartmentswheremanager_idisnotnull;fromdepartmentswheremanager_idisnot注意：&在sqlselectdepartment_name||q'&Department'sManagerId:&'||manager_idas"DaM"fromdepartmentswheremanager_idisnotnull;1Z0-051QuestionViewtheExhibitandexaminethestructureofthePRODUCTSYouneedtogenerateareportinthefollowing5MPDigitalPhotoCamera'scategoryisPhotoYBox'scategoryisElectronicsEnvoyAmbassador'scategoryisWhichtwoquerieswouldgivetherequiredoutput?(ChooseSELECTprod_name||q'''scategoryis'||prod_categoryCATEGORIESFROMSELECTprod_name||q'['s]'categoryis'||prod_categoryCATEGORIESFROMSELECTprod_name||q'\'s\'||'categoryis'||prod_categoryCATEGORIESFROMproducts;SELECTprod_name||q'<'s>'||'categoryis'||prod_categoryCATEGORIESFROMproducts;相（去重DISTINCT修飾多列時，將多列內(nèi)容同時作為一個對象去提交，只要有一方不同，selectdepartment_idfromselectdistinctdepartment_idfromselectdistinctdepartment_id,job_idfromDESCRIBE命令來展現(xiàn)表結(jié)構(gòu)，該命令顯示了列名和數(shù)據(jù)類型，向你展現(xiàn)是否必須包含數(shù)據(jù)（就是列上是否存在NOTNULL非空約束DESC語法中，tablename可以是已存在的一張表、一個視圖、或者一個同義詞。descemployees;3WHERE條件及排偽列：ROWIDROWID:18位識別號，去定位在物理磁盤存在哪個物理塊上。ROWID是確定的。:邏輯操作符來指定一個condition，并且返回值是TRUE、FALSE或者是未知。selectemployee_id,last_name,job_id,department_idfromemployeeswhere默認(rèn)的日期格式顯示為DD-MON-RRSQL>selectlast_name,job_id,department_idfromemployeeswhereselectlast_name,job_id,department_idfromemployeeswherenorowsselectlast_name,job_id,department_idfromemployeeswherehire_date='17-FEB-96';selectlast_name,job_id,department_idfromemployeeswherehire_date='17-FEB-*ERRORatlineORA-01858:anon-numericcharacterwasfoundwhereanumericwasselecthire_datefromselectlast_name,job_id,department_idfromemployeeswherehire_date='2006-07-11日期格式selectsysdatefrom>:<:=:>=:<=:<>:!=:^=:IN在某個集合里L(fēng)IKE:匹配字符ISNULL:為空的ISNOTNULL:BETWEENAND在兩個值之間（包含界限值使用條件查詢EMPLOYEES表中工資小于等于3000的有哪些人。selectlast_name,salaryfromemployeeswheresalary3000;查詢EMPLOYEES表中工資大于等于2500小于等于3500。selectlast_name,salaryfromemployeeswheresalary>=2500andsalary<=selectlast_name,salaryfromemployeeswheresalarybetween2500and使用條件selectemployee_id,last_name,salary,manager_idfromemployeeswheremanager_idin(100,101,201);selectemployee_id,last_name,salary,manager_idfromemployeeswherelast_namein('Hartstein','Vargas);selectemployee_id,last_name,salary,manager_idfromemployeeswheremanager_id=100ormanager_id=101ormanager_id=201;selectemployee_id,last_name,salary,manager_idfromemployeeswherelast_name='Hartstein'orlast_name='Vargas';DROPTABLETBAPURGE;CREATETABLETBA(anumber);SETSERVEROUTONFORIIN1..10000EXECUTEIMMEDIATE'ALTERTABLETBAADD(col'||I||'number)';ENDLOOP;WHENOTHERSTHEN/desctba;spoolselect*fromcolswheretable_name=upper('tba');selectcount(*)fromcolsdwhered.table_name='TBA';selectobject_id,object_name,object_typefromdba_objectswhereobject_idinSQL>@/home/oracle/sy.sql*ERRORatline umnumberofexpressionsinalistisExaminethestructureofthemembersWhichquerycanbeusedtodisythelastnamesandcitynamesonlyformembersfromthestatesMOandMI?SELECTlast_name,cityFROMmembersWHEREstate=’MO’ANDstateSELECTlast_name,cityFROMmembersWHEREstateLIKESELECTlast_name,cityFROMmembersWHEREstateIN(‘MO’,SELECTDISTINCTlast_name,cityFROMmembersWHEREstate=’MO’OR使用條件我們可能不知道要搜索的確切值，但你可以使用LIKE條件來匹配一個字符模板的行。字符模板匹配運算涉及通配符查詢。有兩個符號%和_來構(gòu)造搜索串。%:0_:selectfirst_namefromemployeeswherefirst_namelikeselectlast_namefromemployeeswherelast_namelike比如要搜索包含SA_的內(nèi)容：selectemployee_id,last_name,job_idfromemployeeswherejob_idlike'SA\_%'escape'\';使用條件ISNULLISNOTselectemp mission_pctfromemployeeswherecommission_pctisnullandrownum<10;selectemp mission_pctfromemployeeswherecommission_pctisnotnull;ANDTRUEOR:如果單個條件滿足則返回TRUENOT:如果條件不滿足則返回TRUESQLAND和ORselectemployee_id,last_name,job_id,salaryfromemployeeswheresalary>=10000andjob_idlike'%MAN%';selectemployee_id,last_name,job_id,salaryfromemployeeswheresalary>=10000orjob_idlike'%MAN%';selectemployee_id,last_name,job_id,salaryfromemployeeswheresalary>=10000orjob_idnotin('IT_PROG','ST_CLERK','SA_REP');注意：ANDORSQL語句時，理清邏輯關(guān)系尤為重要，特selectlast_name,job_id,salaryfromemployeeswhere(last_name='Jones'andjob_id='SH_CLERK')or(last_name='Walsh'andJOB_ID='SH_CLERK');selectlast_name,job_id,salaryfromemployeeswherelast_name='Jones'and(job_id='SH_CLERK'orlast_name='Walsh')andJOB_ID='SH_CLERK';selectlast_name,job_id,salaryfromemployeeswherejob_id='SA_REP'orjob_id='AD_PRES'andsalary>15000;selectlast_name,job_id,salaryfromemployeeswhere(job_id='SA_REP'orjob_id='AD_PRES')andsalary>15000;ORDERBYselectlast_name,department_id,hire_datefromemployeesorderbyhire_date;selectlast_name,department_id,hire_datefromemployeesorderbyhire_dateasc;selectlast_name,department_id,hire_datefromemployeesorderbyhire_datedesc;selectlast_name,department_id,salaryfromemployeesorderbydepartment_id,salarydesc;WhichstatementistrueregardingthedefaultbehavioroftheORDERBYInacharactersort,thevaluesarecase-NULLvaluesarenotconsideredatallbythesortOnlythosecolumnsthatarespecifiedintheSELECTlistcanbeusedintheORDERBYclause.Numericvaluesaredisyedfromthe umtotheminimumvalueiftheyhavedecimalpositions.想要一個查詢來列出不同工種的員工，不是那種JOB_IDisSA_REP。這時候你可以編輯where子句，每次查詢的時候提供不同的值進(jìn)行運行。但是這兒有一個更簡單的辦法。where子句中可以通過使用替代變量來替換這個實際值，你可以通過一個查詢來輸可以在ORDERBY子句中；DEFILE命令來預(yù)先定義好變量，通過DEFINE來創(chuàng)建和分配值給這個SQL>selectemployee_id,last_name,salaryfromemployeeswhereEntervalueforemployee_num: 1:selectemployee_id,last_name,salaryfromemployeeswhere 1:selectemployee_id,last_name,salaryfromemployeeswhere SQL>selectlast_name,department_id,salary*12fromemployeeswhereTEntervalueforjob_title: 1:selectlast_name,department_id,salary*12fromemployeeswhere 1:selectlast_name,department_id,salary*12fromemployeeswhere SQL>selectemployee_id,last_name,job_id,&column_namefromemployeeswhere&conditionorderby&order_column;Entervalueforcolumn_name:salaryEntervalueforcondition:salary>15000Entervaluefororder_column:last_name 1:selectemployee_id,last_name,job_id,&column_namefromemployeeswhere&conditionorderby&order_column 1:selectemployee_id,last_name,job_id,salaryfromemployeeswheresalary>15000orderbylast_name 102De100101selectemployee_id,last_name,job_id,&&column_namefromemployeesorderbyDEFINE使用UNDEFINE命令刪除變量SQL>defineemployee_num=200SQL>selectemployee_id,last_name,salary,department_idfromemployeeswhere 1:selectemployee_id,last_name,salary,department_idfromemployeeswhere 1:selectemployee_id,last_name,salary,department_idfromemployeeswhereemployee_id=200 SQL>undefine1Z0-071QuestionYouexecutethefollowingSQL>DEFINEhiredate=‘01-APR-SQL>SELECTemployee_id,first_name,salaryFROMemployeesWHEREhire_date>ANDmanager_id>Forwhichsubstitutionvariablesareyoupromptedforthenone,becausenoinputboththesubstitutionvariables‘hiredate’andonlyonlysetverifyonsetverifyoff是關(guān)閉SQL>setverifySQL>selectemployee_id,last_name,salaryfromemployeeswhereEntervalueforemployee_num: SQL>setverifySQL>selectemployee_id,last_name,salaryfromemployeeswhereEntervalueforemployee_num: 1:selectemployee_id,last_name,salaryfromemployeeswhere 1:selectemployee_id,last_name,salaryfromemployeeswhere 4章函（Lesson3:UsingSingle-RowFunctionstoCustomizeOutput）穿插：DUAL表介紹有用的，例如，常數(shù)值，偽列或者不是來自用戶的數(shù)據(jù)表的表達(dá)式。DUAL表通常用于SELECT子句語法的完整，因為不管是SELECT還是FROM子句都是強制的，并且一些 大小寫處理函數(shù)（UPPER、LOWER、UPPER將指定字符串內(nèi)字符變?yōu)榇髮?，支持CHAR,VARCHAR2,NCHARINITCAP將指定字符串內(nèi)第一個字母轉(zhuǎn)換為大寫，其他則轉(zhuǎn)換為小寫。SQL>selectlower('abCDEfg'),upper('abCDEfg'),initcap('abCDEfg')fromdual; SQL>selectinitcap('oworld')fromdual;o字符處理函數(shù)SQL>selectconcat('aaa','bbb')fromdual;SQL>selectconcat('aaa','bbb','ccc')from字符處理函數(shù)SUBSTR(c1,n1[,n2截取指定長度的字符串。n1=開始長度，n2=截取字符串的長度，n10thenn1n10oracle從左向右確認(rèn)起始位置截?。ㄕ蚪厝10oracle從右向左確認(rèn)起始位置（反向截取n1c1字符處理函數(shù)LENGTHselectlength('employee_id')fromemployees;selectlength('')fromdual;字符處理函數(shù)])3表示左邊第3處開始，如果為0，則返回0.SQL>selectinstr('abcdefg','e',2)fromExaminethedataintheCUST_NAMEcolumnoftheCUSTOMERStable.RenskeLadwigJasonMallinSamuelMcCainAllanMCEwenJuliaNayerYouneedtodisycustomers’secondnameswherethesecondnamestartswithorWhichquerygivestherequiredSELECTSUBSTR(cust_name,INSTR(cust_name,‘FROMWHERESUBSTR(cust_name,INSTR(cust_name,‘‘)+1)LIKEINITCAP(‘MC%’);FROMWHEREINITCAP(SUBSTR(cust_name,INSTR(cust_name,‘‘)+1))=SELECTSUBSTR(cust_name,INSTR(cust_name,‘FROMWHEREINITCAP(SUBSTR(cust_name,INSTR(cust_name,‘‘)+1))LIKE‘Mc%’;SELECTSUBSTR(cust_name,INSTR(cust_name,‘FROM字符處理函數(shù)SQL>selectlpad('abcdefg',10,'#')from字符處理函數(shù)字符處理函數(shù)SQL>selecttrim(' abcdef ')fromdual;abcSQL>select abcdef')fromSQL>selecttrim('H'from oWorld')fromSQL>select ')from 字符處理函數(shù) ROUND:MOD:求余操作（也叫求模 SQL>selectmod(100,12)from1Z0-071考題 EvaluatethefollowingSQL>SELECTTRUNC(ROUND(156.00,-1),-1)FROMDUAL;Whatwouldbe 系統(tǒng)日期Oracle數(shù)據(jù)庫中內(nèi)部數(shù)字格式有：世紀(jì)、年、月、日、時、分、秒數(shù)據(jù)庫默認(rèn)日期格式為DD-MON-RRRR格式：小小當(dāng)前、往前、大小往后、大大當(dāng)前selectsysdatefrom日期計算SQL>selectsysdate+1fromdual;SQL>selectsysdate-1fromMONTHS_BETWEEN8ADD_MONTHS:2018-05-0215:43:05NEXT_DAY2018-04-0815:46:28LAST_DAYSQL>selectlast_day(sysdate)from2018-04-3015:50:41ROUNDTRUNCSQL>selectround(sysdate,'month')rsm,round(sysdate,'year') 2018-04-0100:00:002018-01-0100:00:002018-04-0100:00:002018-01-01注意：如果格式模型為月，日期是1-15，那么結(jié)果是當(dāng)前月的第一天。SELECTename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12)year,FROMemp;（Lesson4:UsingConversionFunctionsandConditionalExpressions）隱式轉(zhuǎn)換#sysdatelength是字符處理函數(shù)，這里sysdate隱式轉(zhuǎn)換成了selectemployee_idfromemployeeswhereemployee_id='199';#employee_id本來是number類型，這里字符'199'199。select'66.6'+22.2fromselect10+('13.5'||11)from顯式轉(zhuǎn)換TO_CHAR、TO_DATE、createtableaaaasselecthire_date,to_char(hire_date,'yyyy-mm-dd')tocharfromcreatetablebbbasselecthire_date,to_char(hire_date,'fmyyyy-mm-dd')tocharfromselectlast_name,to_char(salary,'L999,999.99fromemployees;selectlast_name,to_char(salary,'$999,999.99')fromemployees; 注意如果是X-S createtablecccasselectlast_name,to_char(salary,'$9,999.99tocharfromemployees;2015-05-0500:00:002015-05-0500:00:00SQL>select 2015-05-0500:00:00SQL>selectlast_name,substr(last_name,1,8)fromemployeeswhere - SQL>selectlast_name,concat(substr(last_name,1,8),'_US')qtfromemployeeswhere SQL>selectlast_name,upper(concat(substr(last_name,1,8),'_US'))qtfromemployeeswheredepartment_id=60; NVL(expr1,expr2)將NULLexpr1expr2，如果不為空返回expr1:NULLIF(expr1,expr2)NULL，如果不想等返回 selectnvl2(commission_pct,' M','SAL')fromemployeesselectnvl2(null,2,3)fromdual;考點：NVL2exp2exp3LONG類型之外的任意Examinethestructureof WhichtwoSQLstatementswouldexecute SELECTinv_no,NVL2(inv_amt,inv_amt*.25,‘NotAvailable’)FROM;selectnullif(1,1)fromdual;selectnullif(1,2)fromselectcoalesce(1,2,3,4,5,6)fromCASE是基于（SQL）指定的標(biāo)準(zhǔn)，DECODE是oracle特DECODEselectlast_name,salary,salary)asfromCASE表達(dá)selectLAST_NAMEwhen'Gates'thensalary*1.1when'Bell'thensalary*1.5when'Jones'thensalary*1.8elsesalaryendasfromselectwhenLAST_NAME='Gates'thensalary*1.1whenLAST_NAME='Bell'thensalary*1.5whenLAST_NAME='Jones'thensalary*1.8elsesalaryendasnewsalfromselectwhensalary<=3000then'Low'whensalary>=10000then'High'else'Soso'endaslevfrom

（Lesson5:ReportingAggregatedDataUsingtheGroupselectdepartment_id,avg(salary),sum(salary),max(salary),min(salary)fromemployeesgroupbydepartment_id;selectavg(salary)fromselectsum(salary)fromselectmin(salary)fromemployees;selectmin(last_name)fromemployees;selectmax(salary)fromemployees;selectmax(last_name)fromemployees;selectcount(*)fromselectcount(*)fromemployeeswherecommission_pctisnotCOUNT(distinctexpr)返回不重復(fù)的非NULL值在EMPLOYEES表中顯示不重復(fù)的部門ID組函數(shù)與GROUPBYWhichtwostatementsaretrueregardingtheGROUPBYclauseinaSQLstatement?(Choosetwo.)YoucanusecolumnaliasintheGROUPBYUsingtheWHEREclauseaftertheGROUPBYclauseexcludestherowsaftercreatingTheGROUPBYclauseismandatoryifyouareusinganaggregatefunctionintheSELECTclause.UsingtheWHEREclausebeforetheGROUPBYclauseexcludestherowsbeforecreatinggroups.IftheSELECTclausehasanaggregatefunction,thenthoseindividualcolumnswithoutanaggregatefunctionintheSELECTclauseshouldbeincludedintheGROUPBYcause.HAVINGselectdepartment_id,avg(salary)fromemployeesgroupbydepartment_idhavingavg(salary)>5000orderby1;注意：1.HAVING子句不能使用分組函數(shù)別名進(jìn)行過濾；2.HAVING子句在ORDERBY子句前，ORDERBY總是放在最后。selectavg(salary)fromemployeesgroupbydepartment_id;selectsum(avg(salaryfromemployeesgroupbydepartment_id;selectcount(sum(avg(salary)))fromemployeesgroupbydepartment_id;selectcount(sum(avg(salary)))fromemployeesgroupbyERRORatlineORA-00935:groupfunctionisnestedtoo第5表連接查NATURALJOINselectdepartment_id,department_name,cityfromdepartmentsnaturaljoinselecta.department_id,a.department_name,b.cityfromdepartmentsa,locationsbwherea.location_id=b.location_id;也可以與WHERE子句進(jìn)行行的篩選。selectdepartment_id,department_name,cityfromdepartmentsnaturaljoinlocationswherecity='London';selecta.department_id,a.department_name,b.cityfromdepartmentsa,locationsbwherea.location_id=b.location_idandb.city='London';USINGdroptableapurge;createtablea(idnumber);insertintoavalues(1);insertintoavalues(2);insertintoavalues(3);insertintoavalues(4);insertintoavalues(5);insertintoavalues(6);insertintoavalues(7);droptablebpurge;createtableb(idnumber);insertintobvalues(1);insertintobvalues(2);insertintobvalues(4);insertintobvalues(5);insertintobvalues(6);select*froma;select*from/selectidfromanaturaljoindroptableacreatetablea(idnumber,namevarchar2(10));insertintoavalues(1,'A');insertintoavalues(2,'B');insertintoavalues(3,'C');insertintoavalues(4,'D');insertintoavalues(5,'E');insertintoavalues(6,'F');insertintoavalues(7,'G');droptablebpurge;createtableb(idnumber,namevarchar2(10));insertintobvalues(1,'A');insertintobvalues(2,'b');insertintobvalues(4,'c');insertintobvalues(5,'x');insertintobvalues(6,'y');select*froma;select*from/selectidfromanaturaljoinb;selectidfromajoinbusing(id);ONselecta.department_id,a.department_name,b.cityfromdepartmentsajoinlocationsbona.location_id=b.location_id;selectemployee_id,city,department_namefromemployeesejoindepartmentsdusing(department_id)joinlocationslusing(location_id);ON子句中whereand的區(qū)別AND:過濾之后再連接WHERE:連接之后再過濾selecte.employee_id,e.last_name,e.department_id,d.location_idfromemployeesejoindepartmentsdond.department_id=e.department_idande.employee_id=205;selecte.employee_id,e.last_name,e.department_id,d.location_idfromemployeesejoindepartmentsdond.department_id=e.department_idwheree.employee_id=205;自連接(SelfJoin)SQL語句中的一種特殊連接方式，使用自連接可以將自身表的一selecta.last_nameempname,b.last_namemanagernamefromemployeesajoinemployeesbon(a.manager_id=b.employee_id);selecta.ename,a.sal,b.gradefromempajoinsalgradebona.salbetweenb.losalandLEFTOUTERdroptableacreatetablea(idnumber,namevarchar2(5));insertintoavalues(1,'a');insertintoavalues(2,'b');insertintoavalues(2,'c');insertintoavalues(4,'b');droptablebcreatetableb(idnumber,namevarchar2(5));insertintobvalues(1,'A');insertintobvalues(2,'B');insertintobvalues(3,'C');/SQL99select*fromaleftouterjoinbonselect*froma,bwhereRIGHTOUTERSQL99select*fromarightouterjoinbonselect*froma,bwhereFULLOUTER全連接沒有Oracleselect*fromafullouterjoinbon交叉連接（積select*fromacrossjoin第6查例如：查詢EMPLOYEES表中所有工資高于Gatesselectlast_name,salaryfromemployeeswheresalary>(selectsalaryfromemployeeswherelast_name='Gates')/Gates在同一個部門且工資高于Gatesselectlast_name,salaryfromemployeeswherefromemployeeswhere)/

salary>selectsalarywhere)多行子查詢中使用比較運算符in,notselectlast_namefromemployeeswhereemployee_idin(selectdistinctmanager_idfromemployees);selectlast_namefromemployeeswhereemployee_idnotin(selectdistinctmanager_idfromemployees);考點：notin如果含有NULLselectlast_namefromemployeeswhereemployee_idnotin(selectdistinctnvl(manager_id,0)manager_idfromemployees);多行子查詢中使用selectlast_name,salaryfromemployeeswheresalary>all1000,3500,12000);selectlast_name,salaryfromemployeeswheresalary<all2500,8000,12000);selectlast_name,salaryfromemployeeswheresalary>all(selectavg(salary)fromemployeesgroupbydepartment_id);1Z0-071考題QuestionNo.21Z0-051QuestionNo.149ViewtheExhibitsandexaminethestructuresoftheCOSTSandPROMOTIONSEvaluatethefollowingSQLSQL>SELECTprod_idFROMcostsWHEREpromo_idIN(SELECTpromo_idFROMWHEREpromo_cost<ALL(SELECTMAX(promo_cost)FROMpromotionsGROUPBY(promo_end_date-promo_begin_date)));Whatwouldbe eoftheaboveSQLItdisysprodIDsinthepromowiththelowestItdisysprodIDsinthepromoswiththelowestcostinthesametimeItdisysprodIDsinthepromoswiththehighestcostinthesametimeItdisysprodIDsinthepromoswithcostlessthanthehighestcostinthesametime1Z0-051QuestionViewtheExhibitandexaminethestructureofthePRODUCTSYouwanttogenerateareportthatdisystheaveragelistpriceofproductcategorieswheretheaveragelistpriceislessthanhalfthe umineachcategory.WhichquerywouldgivethecorrectSELECTprod_category,avg(prod_list_price)FROMproductsGROUPBYprod_categoryHAVINGavg(prod_list_price)<ALL(SELECTmax(prod_list_price)/2FROMproductsGROUPBYprod_category);SELECTprod_category,avg(prod_list_price)FROMproductsGROUPBYprod_categoryHAVINGavg(prod_list_price)>ANY(SELECTmax(prod_list_price)/2FROMproductsGROUPBYprod_category);SELECTprod_category,avg(prod_list_price)FROMproductsHAVINGavg(prod_list_price)<ALL(SELECTmax(prod_list_price)/2FROMproductsGROUPBYSELECTprod_category,avg(prod_list_price)FROMproductsGROUPBYprod_categoryHAVINGavg(prod_list_price)>ANY(SELECTmax(prod_list_price)/2FROMproducts);成對比較droptableemployees1createtableemployees1asselect*fromupdateemployees1setcommission_pct=(selectcommission_pctfromemployees1wheredepartment_id=80andlast_name='Zlotkey'),salary=(selectsalaryfromemployees1wheredepartment_id=80andlast_name='Zlotkey')wheredepartment_id=30andlast_name='Tobias';selectlast_name,depart mission_pctfromemployees1where mission_pct)in(select mission_pctfromemployees1whereselectlast_name,depart mission_pctfromemployees1where mission_pct)in(select mission_pctfromemployees1wheredepartment_id=80)anddepartment_id<>80;非成對比較selectlast_name,depart mission_pctfromemployees1wheresalaryin(selectsalaryfromemployees1wheredepartment_id=80)andnvl(commission_pct,0)in(selectnvl(commission_pct,0)fromemployees1wheredepartment_id=80)anddepartment_id<>80;兩個子查詢返回的值分別與主查詢中的salary和commission_pct進(jìn)行比較，如果員LIKE:NOTLIKE :NOTINBETWEEN...AND...:NOTBETWEEN...ANDISNULL:ISNOTNULL NOTEXISTS指的是FROMbselectlast_name,salary,department_idfromemployeesempouterwheresalary>(selectavg(salary)fromemployeesempinnerwheredroptableemployees1createtableemployees1asselect*fromemployees;updateemployees1setsalary=null;updateemployees1emp1setemp1.salary=(selectemp.salaryfromemployeesempwhereemp1.employee_id=emp.employee_id);1Z0-071QuestionYouwanttoupdatetheCITYcolumnoftheDEPTtableforalltherowswiththecorrespondingvalueintheCITYcolumnoftheLOCATIONStableforeachdepartment.WhichSQLstatementwouldyouexecuteto plishthetask?UPDATEdeptdSETcity=ANY(SELECTcityFROMlocationsUPDATEdeptSETcity=(SELECTcityFROMlocationsl)WHEREd.location_id=UPDATEdeptSETcity=(SELECTcityFROMlocationslWHEREd.location_id=UPDATEdeptSETcity=ALL(SELECTcityFROMlocationslWHEREd.location_id=l.location_id);返回TRUE，反之FALSE。droptableuusercreatetableuuser(idnumber,usernamevarchar2(10),birthdaydate,varchar2(5),addressvarchar2(10));insertintouuservalues(0,'ZhangSan',add_months(sysdate,-2),'M','BeiJing');insertintouuservalues(2,'LiSi',add_months(sysdate,-1),'F','TianJin');insertintouuservalues(3,'WangWu',add_months(sysdate,-10),'F','HeNan');insertintouuservalues(10,'MaLiu',add_months(sysdate,-8),'M','HuBei');droptableoorderpurge;createtableoorder(idnumber,user_idnumber,order_namevarchar2(10));insertintooordervalues(1,0,'Order01');insertintooordervalues(2,3,'Order02');insertintooordervalues(3,10,'Order03');selectu.*fromuuseruwhereexists(selecto.*fromoorderowhereu.id=o.user_id);selectu.*fromuuseruwhereexists(select1fromoorderowhereu.id=o.user_id);selectu.*fromuuseruwhereexists(select1+2 fromoorderowhereWHERETRUE，主（外）查詢保留選，即比較積中user表的id是否和order表的userid一樣，一樣保留，不一樣刪除。所以相對內(nèi)表數(shù)據(jù)量比較小的時候，IN的速度比較快。select*fromuuseruwhereu.idin(selecto.user_idfromoorderINEXISTS的區(qū)別:如果子查詢得出的結(jié)果集記錄較少，主查詢中的表較大且又有索引時應(yīng)該用IN,反之如果外層的主查詢記錄較少，子查詢中的表大，又有索引時使用外IN時不對NULL進(jìn)行處理。7章合并droptabletbsacreatetabletbsa(idnumber,namevarchar2(10),addressvarchar2(10));insertintotbsavalues(10,'ZhangSan','BeiJing');insertintotbsavalues(20,'LiSi','TianJin');insertintotbsavalues(30,'WangWu','HeNan');insertintotbsavalues(40,'MaLiu','HuBei');droptabletbsbpurge;createtabletbsb(idnumber,namevarchar2(10),addressvarchar2(10));insertintotbsbvalues(10,'ZhangSan','BeiJing');insertintotbsbvalues(30,'WangWu','HeNan');insertintotbsbvalues(80,'MaLiu','HuBei');select*fromtbsaunionselect*fromUNIONSQL>select*fromtbsaunionallselect*fromID 10 20 30 40 10 30 80 SQL>select*fromtbsaintersectselect*fromID 10 30 1Z0-051QuestionNo:9WhichstatementistrueregardingtheINTERSECTItignoresNULLReversingtheorderoftheintersectedtablesalterstheThenamesofcolumnsinallSELECTstatementsmustbeThenumberofcolumnsanddatatypesmustbeidenticalforallSELECTstatementsinthequery.SQL>select*fromtbsaminusselect*fromID 20 40 SQL>select*fromtbsbminusselect*fromtbsa;IDNAME 80 1Z0-071QuestionNo:541Z0-051QuestionNo151ViewtheExhibitandexaminethestructureoftheCUSTOMERSandCUST_HISTORYTheCUSTOMERStablecontainsthecurrentlocationofallcurrentlyactiveThetablestoreshistoricaldetailsrelatingtoanychangesinthelocationofallcurrentaswellaspreviouscustomerswhoarenolongeractivewiththe Youneedtofindthosecustomerswhohaveneverchangedtheiraddress.WhichSEToperatorwouldyouusetogettherequiredoutput?UNIONTIMESTAMPdroptabletbsacreatetabletbsa(idnumber,namevarchar2(10),addressvarchar2(10));insertintotbsavalues(10,'ZhangSan','BeiJing');insertintotbsavalues(20,'LiSi','TianJin');insertintotbsavalues(30,'WangWu','HeNan');insertintotbsavalues(40,'MaLiu','HuBei');droptabletbsbpurge;createtabletbsb(suuidnumber,namechar(10));insertintotbsbvalues(10,'ZhangSan');insertintotbsbvalues(30,'WangWu');insertintotbsbvalues(80,'MaLiu');SQL>select*fromtbsaunionselect*fromtbsb;select*fromtbsaunionselect*fromtbsb*ERRORatlineORA-01789:queryblockhasincorrectnumberofresultSQL>selectid,namefromtbsaunionselectsuuid,namefromSQL>selectid,name,addressfromtbsaunionselectsuuid,name,nullfrom ORDERBY默認(rèn)和顯示排序select*fromtbsaunionselect*fromselectid,name,addressfromtbsaunionselect*fromtbsborderbyORDERBYORDERBYSELECT語句的列元素進(jìn)行排序，所有ORDERBY后面只能跟第一個SELECT語句使用的列、別名、列號。SQL>selectsuuid,name,nullfromtbsbunionselectid,name,addressfromtbsaorderby3;SQL>selectsuuid,name,to_char(null)tonullfromtbsbunionselectid,name,addressfromtbsaorderbytonull;ORDERBY最終排SQL>selectid,namefromtbsaorderbyidunionselectsuuid,namefromtbsborderbysuuid;selectid,namefromtbsaorderbyidunionselectsuuid,namefromtbsborderby*ERRORatlineORA-00933:SQLcommandnotproperly:WhichstatementistrueregardingthedefaultbehavioroftheORDERBYInacharactersort,thevaluesarecase-NULLvaluesarenotconsideredatallbythesortOnlythosecolumnsthatarespecifiedintheSELECTlistcanbeusedintheORDERBYclause.Numericvaluesaredisyedfromthe umtotheminimumvalueiftheyhavedecimalpositions.第8章用戶控selectusernamefrom（USERSselectproperty_name,PROPERTY_VALUEfromdatabase_propertieswhere,fromv$tablespacea,v$datafilebwhere創(chuàng)建用戶aaa且不指定默認(rèn)表空間SQL>createuseraaaidentifiedbyaaa;查看AAA用戶所屬的表空間SQL>createtablespacetestdatafile'/u01/app/oracle/oradata/PROD1/test1.dbf'size10m;createuserbbbidentifiedbybbbdefaulttablespaceSQL>selectusername,default_tab