java面試題-21sql基礎(chǔ)本課程主要講授Oracle10gSQL

Oracle10g(二Oracle10gSQL適用于數(shù)據(jù)庫(kù)管理員SQL240分鐘示例原位于SQL(StructuredQuerySQL的標(biāo)準(zhǔn)由ANSI(化)開發(fā)并制定,被眾多數(shù)據(jù)庫(kù)廠商遵守并擴(kuò)和完善SQL1970IBME.F.Codd1974BoyceChamberlin提出了StructuredEnglishQueryLanguage(SEQUEL),簡(jiǎn)稱1975-1979IBMSanJoseIBM370SystemR1977ORACLESQL第一個(gè)SQL標(biāo)準(zhǔn)是1986年10月由ANSI頒布的，它是一個(gè)標(biāo)準(zhǔn)，即1989年頒布第二個(gè)SQL1992SQL92,1999SQLSQL可以分為以下幾類：DDL(DataDefinition數(shù)據(jù)語(yǔ)言DML(DataManipulation如:select,update,insert,delete事務(wù)控制DML語(yǔ)句組合起來形成一個(gè)事務(wù)并進(jìn)行事務(wù)控制.會(huì)話控制(Session屬性系統(tǒng)控制(SystemOracle10g(二SQL基礎(chǔ)查詢數(shù)據(jù)是數(shù)據(jù)庫(kù)的操作查詢數(shù)據(jù)是關(guān)系代數(shù),關(guān)系演算在SQL語(yǔ)言中的主要體現(xiàn)查詢數(shù)據(jù)時(shí),即可以查詢所有的列,也可以查詢指定的列,所以必須要清楚表的結(jié)構(gòu).OracleSQL的重要改進(jìn)之一select*fromdistinct可以取消重復(fù)的行---selectdeptno,jobfrom---selectdistinctdeptno,jobfromROWID用于記錄數(shù)據(jù)庫(kù)中記錄的唯一行號(hào)。如：selectrowid,jobfromemp;SQL--selectlosal+500fromsalgrade;SQL基本語(yǔ)法--2500的員工select*fromempwheresal2500;SQL基本語(yǔ)法3、邏輯運(yùn)算符(or、and--10001500select*fromemptwheret.sal>=1000andt.sal<=SQL4IN和NOT 成 NULL和ISNOT 找出NULL值或非NULL例select*fromemptwheret.deptnoin(10,select*fromemptwheret.salbetween2000andselect*fromemptwhere misnotnull;SQL基本語(yǔ)法模式匹配(或模糊查詢“%”和”_”0個(gè)或多個(gè)符串中使用一個(gè)轉(zhuǎn)義字符(ESCAPEcharacter),如果查一個(gè)叫以”S%”開頭的名字:select*fromemptwheret.enamelike's\%%'escapeSQL5Select[all|distinct]select_column_list][form{table_name|view_name}][whereclause][groupbyclause][havingclause][orderbyclause]select*fromemp;selectename,jobfromemp;selectenameas,jobas職務(wù)fromselectename,job職務(wù)fromselectenamefromempwhereename=‘SMITH’;select*fromemporderbyempnodesc;selectename,count(job)fromempgroupbyselectcount(job)fromempgroupby函 描 返回非NULL行的行數(shù) 組處理函數(shù)只能出現(xiàn)在選擇列表,ORDERBY子句,HAVING子句中,而不能出現(xiàn)在WHERE子句GROUPBY子句中如果選擇列表同時(shí)包含列,表達(dá)式和組處理函數(shù),則這些列,GROUPBY字ALL和DISTINCT選項(xiàng).ALL是默認(rèn)選項(xiàng),表示統(tǒng)計(jì)所有的行(包括重復(fù)的行),而DISTINCT只會(huì)統(tǒng)計(jì)不同的行 mfromempWheredeptno=30;Selectavg(sal),avg(distinctsal),max(sal),min(sal),sum(sal),count(*),count(sal),count(distinctsal),count(distinctsal),count(comm)FromempwhereSelectdeptno,avg(sal),max(sal)fromempgroupbySelectdeptno,job,avg(sal),max(sal)fromgroupbydeptno,ROLLUPCUBESelectdeptno,job,avg(sal),max(sal)fromgroupbySelectdeptno,job,avg(sal),max(sal)fromgroupbycube(deptno,job);Cube分組報(bào)表格式Having子句Having字句用來限制(過濾)分組處理后的結(jié)果顯示,并且必須跟再Groupby--2000selectdeptno,avg(sal),max(sal)fromempgroupbydeptnohaving--whereselectdeptno,avg(sal),max(sal)from whereavg(sal)>2000groupbySelectd.deptno,d.dname,e.ename,e.salFromdeptd,empe;Selectd.deptno,d.dname,e.ename,e.salFromdeptd,empeWhereSelecta.ename,a.sal,b.gradefromempa,salgradeba.salbetweenb.losalanda.deptno=在一個(gè)表中進(jìn)行的連接,FROM2次.--Selecte.empno,e.ename Fromempe,empmWherem.empnoe.mgr;SELECT[UNION|UNIONALL|INTERSECT|SELECTSelectempno,ename,mgrfromempWheredeptno=30Selectempno,ename,mgrfromempWherejob=‘MANAGER’;unionSelectempno,ename,mgrfromempWheredeptno=30UnionSelectempno,ename,mgrfromempWherejob=‘MANAGER’;Selectempno,ename,mgrfromempWheredeptno=30Selectempno,ename,mgrfromempWherejob=‘MANAGER’;Selectempno,ename,mgrfromempWheredeptno=30Selectempno,ename,mgrfromempWherejob=‘MANAGER’;單行子查詢(single-row使用的運(yùn)算符號(hào)使用的運(yùn)算符號(hào)(in,notin,exists,not相關(guān)子查詢(correlatedselect列名,(select語(yǔ)句from標(biāo)量子查詢(scalarDDLDML語(yǔ)句中使用子查詢Selectename,deptno,salfromempWheresal(selectmax(sal)from--20Selectename,deptno,sal,fromWherejob(selectdistinctjobfromempwheredeptno=--20Selectename,deptno,sal,fromwherejobnot(selectdistinctjobfromempwheredeptno=--20selectename,deptno,sal,fromwheresal>(selectsalfromempwheredeptno=?思考:怎么把上句變?yōu)閱涡?-20selectename,deptno,sal,fromwheresal>(selectsalfromempwheredeptno=子查詢中了父查詢中某些表或者selectdeptno,(selectmax(sal)fromempbwhereb.deptno=a.deptno)fromemporderbyselectdistinct(selectmax(sal)fromempbwhereb.deptno=a.deptno)fromempaorderbydeptno;--NEWYORKselectename,deptno,sal,fromwhereexists(selectfromwheredept.deptno=anddept.loc='NEWExiststrue或false,所以子查詢（注意：?jiǎn)涡凶硬樵兛梢詥涡卸嗔蠸electcount(*)fromemp;Selectsum(salfromemp;Selectavg(sal)fromemp;--SMITH部門和崗位完全相同的所有雇員信息，selectename,deptno,sal,fromwhere(deptno,job)(selectdeptno,jobfromempwhereename=--20的所有雇員信息，selectename,deptno,sal,job,fromwherejobin(selectjobfromempwheredeptno=20)andmgrin(selectmgrfromempwheredeptno=20)orderbydeptnoDDL--dept1,deptcreatetabledept1(deptno,dname,loc)selectdeptno,dname,locfromcreatetableemp1select*fromDDL--dept_2020的雇員信息.createorreplaceviewdept_20select*fromempwheredeptno=20orderbyDMLWARD完全相同selectename,job,sal,fromwherejob=(selectjobfromemp1whereename=updateset(sal,comm)(selectsal,commfromemp1whereename=wherejob=(selectjobfromemp1whereename=selectename,job,sal,fromwherejob=(selectjobfromemp1whereename=DMLDELETE--ACCOUNTING部門的雇員信息。selectename,job,fromwheredeptno(selectdeptnofromdept1wheredname=DELETEFROMWHEREdeptno(selectdeptnofromdept1wheredname=SELECTENAME,JOB,fromwheredeptno(selectdeptnofromdept1wheredname=DMLinsertintoselectfromwheredeptno(selectdeptnofromdeptwheredname=selectename,job,fromwheredeptno(selectdeptnofromdept1wheredname=insertintoemp1(empno,selectempno,fromwheredeptno(selectdeptnofromdeptwheredname=INNERJOIN tab_name1.col_name,tab_name2.col_name…. (INNER)JOIN ONtab_name1.col=col是兩表的公共列 e.ename, emp dept e.deptno= e.ename, empe,dept e.deptno= ONtab_name1.col=Selectd.dname,fromdeptleftjoinempeond.deptno=andd.deptno= ONtab_name1.col= d.dname,from d.deptno= d.deptno= ONtab_name1.col= d.dname,from d.deptno= d.deptno=selectt1.col_name,fromtable1t1innerjointable1ont1.col_name= EMP INNER EMP M.EMPNO= selecta.empno,a.ename,count(*)fromempa,empbwherea.empno=b.mgrgroupbya.empnoa.ename;Oracle10g(二)SQL基礎(chǔ)Oracle10g(二SQL基礎(chǔ)Java這是默認(rèn)的自動(dòng)提交,都應(yīng)該設(shè)置為false;Oracle中:設(shè)置 mit{on|查看 mit例-- mit--updateUpdateempsetename=‘zoof’where--SelectenamefromempwhereSelectenamefromempwhere--Updateempsetename=‘zoof’whereempno=‘7369’;insertintoemp(empno,ename,job,hiredatevalues(1234,'jack','clerk','29-4月-savepointinsertintoemp(empno,ename,job,hiredatevalues(5678,'lucy','clerk','04-4月-95');selectempno,enamefromempwhere