一頭oracle11g第十三講源碼及筆錄

:Java學(xué)習(xí)路線圖：http: Java1234免費(fèi)資源匯總：http: Oracle11g64 第三節(jié)：PLSQLDeveloperPlsqlPlsqldeveloper 地址 /s/1o6sx7n0解plsqldeveloper_OracleOracle :第一節(jié)：Oracle'''''表空間改名：altertablespacets1renameto第二節(jié)：Oracleselectsysdatefrom Oracle序:createsequence序列名稱；指定初始值：startwithxx

nextval下一個(gè)MinvalueMinvaluemaxvalue11027Incrementby屬性序列增長(zhǎng)步長(zhǎng)默認(rèn)值1Cycle（了解即可，不使用）； Oracle表操:) 簡(jiǎn)寫(xiě)insertintodept更新指定數(shù)據(jù)：更新指定數(shù)據(jù)：updatedeptsetdname='司法部'wheredeptno=50;updatedeptsetdname='司法部',loc=' 'wheredeptno=50;查詢所有：查詢所有：select*fromwhere條件：select*fromempwheresal>=800;select*fromempwheresal>=1500andjob='SALESMAN';Distinct去重復(fù)記錄；Groupby分組查詢：selectjob,count(enameasnumfromEMPtgroupbyHaving過(guò)濾分組：selectjob,count(enameasnumfromEMPtgroupbyjobhavingcount(ename)>=2;Orderby排序：select*fromemporderbysaldesc;select*fromt_user1select*fromselect*fromt_user1unionallselect*fromselect*fromt_user1select*fromselect*fromt_user1select*fromselect*fromempt,deptdwhere類似：select*fromempeinnerjoindeptdone.deptno=d.deptnoinner左外連接：select*fromempeleftjoindeptdon右外連接：select*fromemperightjoindeptdon :字符類型分3種，char(n)、varchar(n)、varchar2(n)；char(n)固定長(zhǎng)度字符串，假如長(zhǎng)度不足n，右邊空格補(bǔ)齊；char(n)VSvarchar2(n)char(n)查詢效率相對(duì)較高，varchar2(n)空間相對(duì)較??；selectlength(userName)ascharlength,length(password)asvarchar2lengthfromt_user3;lpad()向左補(bǔ)全字符串：selectlpad(stuno,6,'0')fromt_user3;rpad()向右補(bǔ)全字符串：selectrpad(stuno,6,'0')fromt_user3;lower()返回字符串小寫(xiě)：selectlower(userName)fromt_user3;upper()返回字符串大寫(xiě)：selectupper(userName)fromt_user3;initcap()單詞首字符大寫(xiě)：selectinitcap(userNamefromt_user3;length()返回字符串長(zhǎng)度：selectlength(password)fromt_user3;substr()截取字符串：selectsubstr(userName,1,2)fromt_user3;instr()獲取字符串出現(xiàn)的位置：selectinstr(password,'23',2,2fromt_user3;ltrim()刪除左側(cè)空格:selectltrim(userName)fromt_user3;rtrim()刪除右側(cè)空格:selectrtrim(userNamefromt_user3;trim()刪除兩側(cè)空格:selecttrim(userName)fromt_user3;concat()串聯(lián)字符串：selectconcat(userName,passwordfromt_user3;reverse()反轉(zhuǎn)字符串：selectreverse(userName)fromt_user3;Precision，scale舉例：12345.678 Precision是8 scale是3；abs()求絕對(duì)值；selectabs(n1)fromt_numberwhereid=1;round()四舍五入：selectround(n1,2)fromt_numberwhereid=1;ceil()向上取整：selectceil(n1)fromt_numberwhereid=2;floor向下取整：selectfloor(n1)fromt_numberwhereid=2;Mod()取模：selectmod(5,3)fromdual;Sign()正負(fù)性：selectsign(n1)fromt_numberwhereid=1求平方根：selectsqrt(9)fromPower()求乘方：selectpower(2,3)fromdual;Trunc()截?。簊electtrunc(123.456,2fromdual;To_char()格式化數(shù)值：常見(jiàn)的字符匹配有0、9、，、$、FM、L、Cselectto_char(123.45,'0000.000')fromdual;selectto_char(123.45,'9999.999')fromdual;selectto_char(123123,'99,999,999.99')fromdual;selectto_char(123123.3,'FM99,999,999.99')fromdual;selectto_char(123123.3,'$99,999,999.99')fromdual;selectto_char(123123.3,'L99,999,999.99')fromdual;selectto_char(123123.3,'99,999,999.99C')fromdual;Datecentury（世紀(jì)信息）yearmonthdayhourminutesecondinsertintot_dateselectsysdatefromdual;selectsystimestampfromdual;Add_monthsselectadd_months(d1,2)fromt_datewhereLast_dayselectlast_day(d1)fromt_datewhereMonths_betweenselectmonths_between(d1,d3fromt_datewhereid=1;next_day返回特定日期之后的一周內(nèi)的日期：selectnext_day(d1,2)fromt_datewhereid=1;Truncselecttrunc(d1,'YYYY')fromt_datewhereid=1;selecttrunc(d1,'MM')fromt_datewhereid=1;selecttrunc(d1,'DD')fromt_datewhereid=1;selecttrunc(d1,'HH')fromt_datewhereid=1;selecttrunc(d1,'MI')fromt_datewhereid=1;Extractselectextract(yearfromsysdate)fromdual;selectextract(monthfromsysdate)fromdual;selectextract(dayfromsysdate)fromdual;selectextract(Hourfromsystimestamp)fromdual;selectextract(minutefromsystimestamp)fromdual;Max求最大值：selectmax(sal) fromemp;Min求最小值：selectmin(sal) fromemp;Avg求平均值：selectavg(sal) fromemp;Sum求和：selectsum(sal) fromemp;Count統(tǒng)計(jì)記錄數(shù)：select fromempNvl空值處理：selectename,nvl(sal,0fromemp;Oracle分頁(yè)：select*fromselecta.*,rownumrnfromselect*fromempAwhererownum<=10whereOracle中的運(yùn)算：select2+1fromdual;select2-1fromdual;select2*1fromdual;select2/1fromBetweenandselect*fromempwheresalbetween900and1500;select*fromempwheresal>=900andsal<=1500;Inselectename,hiredatefromempwhereenamein(selectdistinctenamefromLikeselect*fromempwhereenamelike'%M%'select*fromempwhereenamelike'M%'select*fromempwhereenamelike'_M%' Oracle視:CreateCreate