太陽(yáng)雨

SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere
                        UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename)

column name emp_id salary bonuskey type pk nulls/unique nn,
                        u nnfk table datatype number number numberlength 11.2 11.2

update empset salary=(salary+bonus)*1.1

update empset salary=(salary+nvl(bonus,0)*1.1

SELECT ASCII('A') BIG_A,ASCII('z') BIG_z FROM empBIG_A BIG_z65 122

select CHR(65),CHR(122),CHR(223) FROM empCHR65 CHR122 CHR223A z B

select concat('slobo ','Svoboda') username from dualusernameslobo Syoboda

select INITCAP('veni,vedi,vici') Ceasar from dualCeasarVeni,Vedi,Vici

select INSTR('Mississippi','i',3,3)
                        from dualINSTR('MISSISSIPPI','I',3,3)11
                        select INSTR('Mississippi','i',-2,3)
                        from dualINSTR('MISSISSIPPI','I',3,3)2

select LENGTH('Ipso Facto') ergo from dualergo10

select LOWER(colorname) from itemdetail WHERE LOWER(colorname) LIKE '%white%'COLORNAMEWinterwhite

select LPAD(answer,7,'') padded,answer unpadded from question;
                        PADDED UNPADDED Yes YesNO NOMaybe maybe

select LTRIM('Mississippi','Mis') from dualLTRppi

select REPLACE('uptown','up','down') from dualREPLACEdowntown

select SUBSTR('Message',1,4) from dualSUBSMess

select SOUNDEX('dawes') Dawes SOUNDEX('daws') Daws,
                        SOUNDEX('dawson') from dualDawes Daws DawsonD200 D200 D250

select TRANSLATE('fumble','uf','ar') test from dualTEXTramble

select TRIM(' space padded ') trim from dual TRIMspace padded

select name from dual where UPPER(name) LIKE 'KI%'NAMEKING

ABS()

返回n的絕對(duì)值

ACOS()

反余玄函數(shù)，返回-1到1之間的數(shù)。n表示弧度

select ACOS(-1) pi,ACOS(1) ZERO FROM dualPI ZERO3.14159265 0

ASIN()

反正玄函數(shù)，返回-1到1，n表示弧度

ATAN()

反正切函數(shù)，返回n的反正切值，n表示弧度。

CEIL()

返回大于或等于n的最小整數(shù)。

COS()

返回n的余玄值，n為弧度

COSH()

返回n的雙曲余玄值，n 為數(shù)字。

select COSH(<1.4>) FROM dualCOSH(1.4)2.15089847

EXP()

返回e的n次冪，e=2.71828183.

FLOOR()

返回小于等于N的最大整數(shù)。

LN()

返回N的自然對(duì)數(shù)，N必須大于0

LOG(,)

返回以n1為底n2的對(duì)數(shù)

MOD()

返回n1除以n2的余數(shù)，

POWER(,)

返回n1的n2次方

ROUND(,)

返回舍入小數(shù)點(diǎn)右邊n2位的n1的值，n2的缺省值為0，這回將小數(shù)點(diǎn)最接近的整數(shù)，如果n2為負(fù)數(shù)就舍入到小數(shù)點(diǎn)左邊相應(yīng)的位上，n2必須是整數(shù)。

select ROUND(12345,-2),ROUND(12345.54321,2)
            FROM dualROUND(12345,-2) ROUND(12345.54321,2)12300 12345.54

SIGN()

如果n為負(fù)數(shù)，返回-1,如果n為正數(shù)，返回1，如果n=0返回0.

SIN（)

返回n的正玄值,n為弧度。

SINH()

返回n的雙曲正玄值,n為弧度。

SQRT()

返回n的平方根,n為弧度

TAN（)

返回n的正切值,n為弧度

TANH()

返回n的雙曲正切值,n為弧度

TRUNC(,)

返回截尾到n2位小數(shù)的n1的值，n2缺省設(shè)置為0，當(dāng)n2為缺省設(shè)置時(shí)會(huì)將n1截尾為整數(shù)，如果n2為負(fù)值，就截尾在小數(shù)點(diǎn)左邊相應(yīng)的位上。

單行日期函數(shù)

單行日期函數(shù)操作DATA數(shù)據(jù)類型，絕大多數(shù)都有DATA數(shù)據(jù)類型的參數(shù)，絕大多數(shù)返回的也是DATA數(shù)據(jù)類型的值。

ADD_MONTHS(,＜i＞)

返回日期d加上i個(gè)月后的結(jié)果。i可以使任意整數(shù)。如果i是一個(gè)小數(shù)，那么數(shù)據(jù)庫(kù)將隱式的他轉(zhuǎn)換成整數(shù)，將會(huì)截去小數(shù)點(diǎn)后面的部分。

LAST_DAY()

函數(shù)返回包含日期d的月份的最后一天

MONTHS_BETWEEN(,)

返回d1和d2之間月的數(shù)目,如果d1和d2的日的日期都相同，或者都使該月的最后一天，那么將返回一個(gè)整數(shù)，否則會(huì)返回的結(jié)果將包含一個(gè)分?jǐn)?shù)。

NEW_TIME(,,)

d1是一個(gè)日期數(shù)據(jù)類型，當(dāng)時(shí)區(qū)tz1中的日期和時(shí)間是d時(shí)，返回時(shí)區(qū)tz2中的日期和時(shí)間。tz1和tz2時(shí)字符串。

NEXT_DAY(,)

返回日期d后由dow給出的條件的第一天，dow使用當(dāng)前會(huì)話中給出的語(yǔ)言指定了一周中的某一天，返回的時(shí)間分量與d的時(shí)間分量相同。

select NEXT_DAY('01-Jan-2000'

,'Monday') "1st Monday",
            NEXT_DAY('01-Nov-2004','Tuesday')+7 "2nd Tuesday")
            from dual;1st Monday 2nd Tuesday03-Jan-2000 09-Nov-2004

ROUND([,])

將日期d按照f(shuō)mt指定的格式舍入，fmt為字符串。

SYADATE

函數(shù)沒(méi)有參數(shù)，返回當(dāng)前日期和時(shí)間。

TRUNC([,])

返回由fmt指定的單位的日期d.

單行轉(zhuǎn)換函數(shù)

單行轉(zhuǎn)換函數(shù)用于操作多數(shù)據(jù)類型，在數(shù)據(jù)類型之間進(jìn)行轉(zhuǎn)換。

CHARTORWID()

c 使一個(gè)字符串，函數(shù)將c轉(zhuǎn)換為RWID數(shù)據(jù)類型。

SELECT test_id from test_case where rowid=CHARTORWID('AAAA0SAACAAAALiAAA')

CONVERT(,[,])

c尾字符串，dset、sset是兩個(gè)字符集，函數(shù)將字符串c由sset字符集轉(zhuǎn)換為dset字符集，sset的缺省設(shè)置為數(shù)據(jù)庫(kù)的字符集。

HEXTORAW()

x為16進(jìn)制的字符串，函數(shù)將16進(jìn)制的x轉(zhuǎn)換為RAW數(shù)據(jù)類型。

RAWTOHEX()

x是RAW數(shù)據(jù)類型字符串，函數(shù)將RAW數(shù)據(jù)類轉(zhuǎn)換為16進(jìn)制的數(shù)據(jù)類型。

ROWIDTOCHAR()

函數(shù)將ROWID數(shù)據(jù)類型轉(zhuǎn)換為CHAR數(shù)據(jù)類型。

TO_CHAR([[,)

x是一個(gè)data或number數(shù)據(jù)類型，函數(shù)將x轉(zhuǎn)換成fmt指定格式的char數(shù)據(jù)類型，如果x為日期nlsparm=NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的語(yǔ)言。如果x為數(shù)字nlsparm=NLS_NUMERIC_CHARACTERS 用來(lái)指定小數(shù)位和千分位的分隔符，以及貨幣符號(hào)。

NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"

TO_DATE([,[,）

c表示字符串，fmt表示一種特殊格式的字符串。返回按照f(shuō)mt格式顯示的c,nlsparm表示使用的語(yǔ)言。函數(shù)將字符串c轉(zhuǎn)換成date數(shù)據(jù)類型。

TO_MULTI_BYTE()

c表示一個(gè)字符串，函數(shù)將c的擔(dān)子截字符轉(zhuǎn)換成多字節(jié)字符。

TO_NUMBER([,[,)

c表示字符串，fmt表示一個(gè)特殊格式的字符串，函數(shù)返回值按照f(shuō)mt指定的格式顯示。nlsparm表示語(yǔ)言，函數(shù)將返回c代表的數(shù)字。

TO_SINGLE_BYTE()

將字符串c中得多字節(jié)字符轉(zhuǎn)化成等價(jià)的單字節(jié)字符。該函數(shù)僅當(dāng)數(shù)據(jù)庫(kù)字符集同時(shí)包含單字節(jié)和多字節(jié)字符時(shí)才使用

其它單行函數(shù)

BFILENAME( ,)

dir是一個(gè)directory類型的對(duì)象，file為一文件名。函數(shù)返回一個(gè)空的BFILE位置值指示符，函數(shù)用于初始化BFILE變量或者是BFILE列。

DECODE(,,[,,,[])

x是一個(gè)表達(dá)式，m1是一個(gè)匹配表達(dá)式，x與m1比較，如果m1等于x，那么返回r1,否則,x與m2比較，依次類推m3,m4,m5....直到有返回結(jié)果。

DUMP(,[,[,[,]]])

x是一個(gè)表達(dá)式或字符，fmt表示8進(jìn)制、10進(jìn)制、16進(jìn)制、或則單字符。函數(shù)返回包含了有關(guān)x的內(nèi)部表示信息的VARCHAR2類型的值。如果指定了n1,n2那么從n1開(kāi)始的長(zhǎng)度為n2的字節(jié)將被返回。

EMPTY_BLOB()

該函數(shù)沒(méi)有參數(shù)，函數(shù)返回 一個(gè)空的BLOB位置指示符。函數(shù)用于初始化一個(gè)BLOB變量或BLOB列。

EMPTY_CLOB()

該函數(shù)沒(méi)有參數(shù)，函數(shù)返回 一個(gè)空的CLOB位置指示符。函數(shù)用于初始化一個(gè)CLOB變量或CLOB列。

GREATEST()

exp_list是一列表達(dá)式，返回其中最大的表達(dá)式，每個(gè)表達(dá)式都被隱含的轉(zhuǎn)換第一個(gè)表達(dá)式的數(shù)據(jù)類型，如果第一個(gè)表達(dá)式是字符串?dāng)?shù)據(jù)類型中的任何一個(gè)，那么返回的結(jié)果是varchar2數(shù)據(jù)類型，同時(shí)使用的比較是非填充空格類型的比較。

LEAST()

exp_list是一列表達(dá)式，返回其中最小的表達(dá)式，每個(gè)表達(dá)式都被隱含的轉(zhuǎn)換第一個(gè)表達(dá)式的數(shù)據(jù)類型，如果第一個(gè)表達(dá)式是字符串?dāng)?shù)據(jù)類型中的任何一個(gè)，將返回的結(jié)果是varchar2數(shù)據(jù)類型，同時(shí)使用的比較是非填充空格類型的比較。

UID

該函數(shù)沒(méi)有參數(shù)，返回唯一標(biāo)示當(dāng)前數(shù)據(jù)庫(kù)用戶的整數(shù)。

USER

返回當(dāng)前用戶的用戶名

USERENV()

基于opt返回包含當(dāng)前會(huì)話信息。opt的可選值為：

ISDBA　 　　會(huì)話中SYSDBA腳色響應(yīng)，返回TRUE

SESSIONID 　返回審計(jì)會(huì)話標(biāo)示符

ENTRYID 　　返回可用的審計(jì)項(xiàng)標(biāo)示符

INSTANCE　　在會(huì)話連接后，返回實(shí)例標(biāo)示符。該值只用于運(yùn)行Parallel 服務(wù)器并且有 多個(gè)實(shí)例的情況下使用。

LANGUAGE　　返回語(yǔ)言、地域、數(shù)據(jù)庫(kù)設(shè)置的字符集。

LANG　　　　返回語(yǔ)言名稱的ISO縮寫。

TERMINAL　　為當(dāng)前會(huì)話使用的終端或計(jì)算機(jī)返回操作系統(tǒng)的標(biāo)示符。

VSIZE()

x是一個(gè)表達(dá)式。返回x內(nèi)部表示的字節(jié)數(shù)。

SQL中的組函數(shù)

組函數(shù)也叫集合函數(shù)，返回基于多個(gè)行的單一結(jié)果，行的準(zhǔn)確數(shù)量無(wú)法確定，除非查詢被執(zhí)行并且所有的結(jié)果都被包含在內(nèi)。與單行函數(shù)不同的是，在解析時(shí)所有的行都是已知的。由于這種差別使組函數(shù)與單行函數(shù)有在要求和行為上有微小的差異.

組（多行）函數(shù)

與單行函數(shù)相比，oracle提供了豐富的基于組的，多行的函數(shù)。這些函數(shù)可以在select或select的having子句中使用，當(dāng)用于select子串時(shí)常常都和GROUP BY一起使用。

AVG([{DISYINCT|ALL}])

返回?cái)?shù)值的平均值。缺省設(shè)置為ALL.

SELECT AVG(sal),AVG(ALL sal),AVG(DISTINCT sal)
            FROM scott.empAVG(SAL) AVG(ALL SAL) AVG(DISTINCT SAL)
            1877.94118 1877.94118 1916.071413

COUNT({*|DISTINCT|ALL} )

返回查詢中行的數(shù)目，缺省設(shè)置是ALL,*表示返回所有的行。

MAX([{DISTINCT|ALL}])

返回選擇列表項(xiàng)目的最大值，如果x是字符串?dāng)?shù)據(jù)類型，他返回一個(gè)VARCHAR2數(shù)據(jù)類型，如果X是一個(gè)DATA數(shù)據(jù)類型，返回一個(gè)日期，如果X是numeric數(shù)據(jù)類型，返回一個(gè)數(shù)字。注意distinct和all不起作用，應(yīng)為最大值與這兩種設(shè)置是相同的。

MIN([{DISTINCT|ALL}])

返回選擇列表項(xiàng)目的最小值。

STDDEV([{DISTINCT|ALL}])

返回選者的列表項(xiàng)目的標(biāo)準(zhǔn)差，所謂標(biāo)準(zhǔn)差是方差的平方根。

SUM([{DISTINCT|ALL}])

返回選擇列表項(xiàng)目的數(shù)值的總和。

VARIANCE([{DISTINCT|ALL}])

返回選擇列表項(xiàng)目的統(tǒng)計(jì)方差。

用GROUP BY給數(shù)據(jù)分組

正如題目暗示的那樣組函數(shù)就是操作那些已經(jīng)分好組的數(shù)據(jù)，我們告訴數(shù)據(jù)庫(kù)用GROUP BY怎樣給數(shù)據(jù)分組或者分類，當(dāng)我們?cè)赟ELECT語(yǔ)句的SELECT子句中使用組函數(shù)時(shí)，我們必須把為分組或非常數(shù)列放置在GROUP BY子句中，如果沒(méi)有用group by進(jìn)行專門處理，那么缺省的分類是將整個(gè)結(jié)果設(shè)為一類。

select stat,counter(*) zip_count from zip_codes GROUP BY state;
            ST ZIP_COUNT-- ---------AK 360AL 1212AR 1309AZ 768CA 3982

在這個(gè)例子中，我們用state字段分類；如果我們要將結(jié)果按照zip_codes排序,可以用ORDER BY語(yǔ)句，ORDER BY子句可以使用列或組函數(shù)。

select stat,counter(*) zip_count from zip_codes GROUP
            BY state ORDER BY COUNT(*) DESC;ST COUNT(*)-- --------
            NY 4312PA 4297TX 4123CA 3982

用HAVING子句限制分組數(shù)據(jù)

現(xiàn)在你已經(jīng)知道了在查詢的SELECT語(yǔ)句和ORDER BY子句中使用主函數(shù)，組函數(shù)只能用于兩個(gè)子串中，組函數(shù)不能用于WHERE子串中，例如下面的查詢是錯(cuò)誤的：

錯(cuò)誤SELECT sales_clerk,SUN(sale_amount)
            FROM gross_sales WHERE sales_dept='OUTSIDE'
            AND SUM(sale_amount)>10000 GROUP BY sales_clerk

這個(gè)語(yǔ)句中數(shù)據(jù)庫(kù)不知道SUM()是什么，當(dāng)我們需要指示數(shù)據(jù)庫(kù)對(duì)行分組，然后限制分組后的行的輸出時(shí)，正確的方法是使用HAVING語(yǔ)句：

SELECT sales_clerk,SUN(sale_amount)
            FROM gross_sales WHERE sales_dept='OUTSIDE'
            GROUP BY sales_clerkHAVING SUM(sale_amount)>10000;