【正文】 ) FROM DUAL。,39。,39。SELECT REPLACE(39。別名 別名ENAME 位數(shù)不足 ENAME 左對(duì)齊 SAL 80 800 80016 1600 160012 1250 125029 2975 297512 1250 125028 2850 285024 2450 245050 5000 500015 1500 150095 950 95030 3000 300013 1300 1300Lpad左鋪墊，rpad右鋪墊，一般的用途是美化輸出的結(jié)果。 39。 39。ENAME ENAME SMITH SMITHALLEN ALLENWARD WARDJONES JONESMARTIN MARTINBLAKE BLAKECLARK CLARKKING KINGTURNER TURNERJAMES JAMESFORD FORDMILLER MILLER左鋪墊和右鋪墊，20是總共鋪墊到多少位，是要鋪墊的字符串。39。39。Trim函數(shù)是截掉頭或者尾連續(xù)的字符，一般我們的用途是去掉空格。AAAAABABABAAAAA39。A39。) from dual。 from 39。AAAAABABABAAAbabab截掉連續(xù)的前置和后置的aSQL select trim(39。A39。) from dual。 from 39。AAAAABABAaaaaababab截掉連續(xù)的后置的aSQL select trim(both 39。A39。) from dual。 from 39。AAAAABABABbababaaaaa截掉連續(xù)的前置的aSQL select trim(trailing 39。A39。) from dual。 from 39。截?cái)嘧址吞砑幼址暮瘮?shù)SQL select trim(leading 39。) A在第幾位 from emp。select ename,instr(ename,39。ENAME first last SMITH S HALLEN A NWARD W DJONES J SMARTIN M NBLAKE B ECLARK C KKING K GTURNER T RJAMES J SFORD F DMILLER M Rsubstr(字符串,m,n),m是從第幾個(gè)字符開(kāi)始，如果為負(fù)的意思是從后邊的第幾個(gè)開(kāi)始。39。200。213。) from dual。) 4SQL select lengthc(39。LENGTHB(39。張三39。張三39。) from dual。unicode的長(zhǎng)度SQL select length(39。張三39。) from dual。按照字select lengthb(39。張三39。ENAME JOB CONCAT(ENAME,JOB) SMITH CLERK SMITHCLERKALLEN SALESMAN ALLENSALESMANWARD SALESMAN WARDSALESMANJONES MANAGER JONESMANAGERMARTIN SALESMAN MARTINSALESMANBLAKE MANAGER BLAKEMANAGERCLARK MANAGER CLARKMANAGERKING PRESIDENT KINGPRESIDENTTURNER SALESMAN TURNERSALESMANJAMES CLERK JAMESCLERKFORD ANALYST FORDANALYSTMILLER CLERK MILLERCLERK將兩個(gè)字符連接到一起下面三句話(huà)是求字符串的長(zhǎng)度，字符串要單引。) mf tr MF TR Mf TrDual是虛表，讓我們用表的形式來(lái)訪(fǎng)問(wèn)函數(shù)的值。) INITCAP(39。) UPPER(39。LOWER(39。mf TR39。mf TR39。mf TR39。l 單行函數(shù)的分類(lèi)字符操作函數(shù)數(shù)字操作函數(shù)日期操作函數(shù)數(shù)據(jù)類(lèi)型轉(zhuǎn)換函數(shù)綜合數(shù)據(jù)類(lèi)型函數(shù)l 字符操作函數(shù)大小寫(xiě)操作函數(shù)Lower,upper,initcap字符串操作函數(shù)Concat,length,substr,instr,trim,replace,lpad,rpad實(shí)驗(yàn)12：操作字符串的函數(shù)該實(shí)驗(yàn)的目的是掌握常用的字符串操作的函數(shù).字符串的大小寫(xiě)操作Select lower(ename)，upper(ename)，initcap(ename) from emp。單行函數(shù)：每一行都有一個(gè)返回值，但可以有多個(gè)變量。函數(shù)可以嵌套。變量可以有好多。查詢(xún)員工的名稱(chēng)和上班日期，日期反序排列。查詢(xún)第三個(gè)字母為A的員工。查詢(xún)30號(hào)部門(mén)的員工，顯示名稱(chēng)和工資。DEPTNO JOB ENAME SAL 10 CLERK MILLER 1300 10 MANAGER CLARK 2450 10 PRESIDENT KING 5000 20 ANALYST FORD 3000 20 CLERK SMITH 800 20 MANAGER JONES 2975 30 CLERK JAMES 950 30 MANAGER BLAKE 2850 30 SALESMAN TURNER 1500 30 SALESMAN WARD 1250 30 SALESMAN ALLEN 160030 SALESMAN MARTIN 1250先按照部門(mén)排序，部門(mén)相同的再按照工作排序。位置排序,對(duì)集合操作時(shí)比較方便.Select ename,sal from emp order by 2。ENAMESMITHJAMESWARDMARTINMILLERTURNERALLENCLARKBLAKEJONESFORDKING別名排序Select sal*12 salary from emp order by salary。隱式排序，顯示的結(jié)果里沒(méi)有工資，但是按照工資的順序顯示的。Select ename,sal from emp order by sal desc。默認(rèn)是升序asc降序要指定desc 實(shí)驗(yàn)11：使用order by子句來(lái)進(jìn)行排序操作該實(shí)驗(yàn)的目的是掌握排序操作.Select ename,sal from emp order by sal。 IS [NOT] NULL, LIKE, [NOT] IN5。連接運(yùn)算3。l 優(yōu)先級(jí)1。T%39。ENAME DEPTNO SAL ALLEN 30 1600WARD 30 1250JONES 20 2975MARTIN