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

Oracle10g(二Oracle10gSQL適用于數(shù)據(jù)庫管理員SQL240分鐘示例原位于SQL(StructuredQuerySQL的標準由ANSI(化)開發(fā)并制定,被眾多數(shù)據(jù)庫廠商遵守并擴和完善SQL1970IBME.F.Codd1974BoyceChamberlin提出了StructuredEnglishQueryLanguage(SEQUEL),簡稱1975-1979IBMSanJoseIBM370SystemR1977ORACLESQL第一個SQL標準是1986年10月由ANSI頒布的，它是一個標準，即1989年頒布第二個SQL1992SQL92,1999SQLSQL可以分為以下幾類：DDL(DataDefinition數(shù)據(jù)語言DML(DataManipulation如:select,update,insert,delete事務控制DML語句組合起來形成一個事務并進行事務控制.會話控制(Session屬性系統(tǒng)控制(SystemOracle10g(二SQL基礎查詢數(shù)據(jù)是數(shù)據(jù)庫的操作查詢數(shù)據(jù)是關系代數(shù),關系演算在SQL語言中的主要體現(xiàn)查詢數(shù)據(jù)時,即可以查詢所有的列,也可以查詢指定的列,所以必須要清楚表的結構.OracleSQL的重要改進之一select*fromdistinct可以取消重復的行---selectdeptno,jobfrom---selectdistinctdeptno,jobfromROWID用于記錄數(shù)據(jù)庫中記錄的唯一行號。如：selectrowid,jobfromemp;SQL--selectlosal+500fromsalgrade;SQL基本語法--2500的員工select*fromempwheresal2500;SQL基本語法3、邏輯運算符(or、and--10001500select*fromemptwheret.sal>=1000andt.sal<=SQL4IN和NOT 成 NULL和ISNOT 找出NULL值或非NULL例select*fromemptwheret.deptnoin(10,select*fromemptwheret.salbetween2000andselect*fromemptwhere misnotnull;SQL基本語法模式匹配(或模糊查詢“%”和”_”0個或多個符串中使用一個轉義字符(ESCAPEcharacter),如果查一個叫以”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職務fromselectename,job職務fromselectenamefromempwhereename=‘SMITH’;select*fromemporderbyempnodesc;selectename,count(job)fromempgroupbyselectcount(job)fromempgroupby函 描 返回非NULL行的行數(shù) 組處理函數(shù)只能出現(xiàn)在選擇列表,ORDERBY子句,HAVING子句中,而不能出現(xiàn)在WHERE子句GROUPBY子句中如果選擇列表同時包含列,表達式和組處理函數(shù),則這些列,GROUPBY字ALL和DISTINCT選項.ALL是默認選項,表示統(tǒng)計所有的行(包括重復的行),而DISTINCT只會統(tǒng)計不同的行 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分組報表格式Having子句Having字句用來限制(過濾)分組處理后的結果顯示,并且必須跟再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=在一個表中進行的連接,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使用的運算符號使用的運算符號(in,notin,exists,not相關子查詢(correlatedselect列名,(select語句from標量子查詢(scalarDDLDML語句中使用子查詢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,所以子查詢（注意：單行子查詢可以單行多列Selectcount(*)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基礎Oracle10g(二SQL基礎Java這是默認的自動提交,都應該設置為false;Oracle中:設置 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