【文章內(nèi)容簡介】 SELECT 排重 * 字段名 別名 FROM 表名列表 WHERE 查詢條件： BETWEEN...AND... 、IN 、IS NULL、 NOT、 AND、 OR 、子查詢 ORDER BY 字段1, 字段2[ ASC|DESC]第二天 Oracle 10g單行函數(shù)和聚合函數(shù) 函數(shù)是一種有0個或多個參數(shù)并且有一個返回值的程序，Oracle的函數(shù)主要分為兩大類：單行函數(shù)和聚合函數(shù)（組函數(shù)）。SQL和PL/SQL中自帶很多類型的函數(shù)，如字符、數(shù)字、日期、轉(zhuǎn)換和混合型等多種函數(shù)用于處理單行數(shù)據(jù)，這些函數(shù)統(tǒng)稱為單行函數(shù)，這些函數(shù)均可用于select、where、order by等子句中。當(dāng)需要對查詢結(jié)果的數(shù)據(jù)行的同類信息合并在一起操作時，就需要用聚合函數(shù)來匯總這些數(shù)據(jù)行；oracle提供了基于組的多行函數(shù)，它們可以在select或select的having子句中使用。 單行函數(shù)單行函數(shù)對查詢的表或視圖的每一行返回一個結(jié)果行，分為：數(shù)值函數(shù)、字符函數(shù)、日期函數(shù)、轉(zhuǎn)換函數(shù)等。單行函數(shù)也可以使用在update的set子句中、insert的values子句中、delete的where子句中等，但重點(diǎn)是用在select子句。啞表：dual是oracle提供一個特使的表，主要用于測試函數(shù)。 NVL函數(shù)因?yàn)樵谖覀兊谋碇薪?jīng)常會有null值出現(xiàn)，而null無法參與其它的運(yùn)算，如salary+null是非法的，為了解決這個問題，系統(tǒng)提供了NVL函數(shù)專門處理null。1）該函數(shù)的格式：NVL(x1，x2)其中x1，x2都是表達(dá)式，當(dāng)x1的值為null時返回x2的值，否則返回x1的值。2）舉例，找出問題所在先看上述語句會出現(xiàn)什么問題，解決辦法如下：注意：xx2的數(shù)據(jù)類型要相匹配。 字符串函數(shù)1） ASCII(c)：返回字符c的ASCII碼值（十進(jìn)制數(shù)）。2） CHR(i)：返回整數(shù)i對應(yīng)的字符。3） CONCAT(C1,C2)：連接字符串C1和C2。注意一個字符串的連接運(yùn)算符：||，如：select 39。姓名:39。||teachername from teacher4） INITCAP(C)：返回字符串C，并將C中的每個單詞的第一個字母大寫，其它字母小寫。5） INSTR(C1,C2,i,j)：在一個字符串中搜索指定的字符，返回發(fā)現(xiàn)指定的字符的位置。C1是被搜索的字符串，C2是搜索的字符串，i是搜索的開始位置（默認(rèn)為1），j是出現(xiàn)的次數(shù)（默認(rèn)為1）例如：求“oracle traing”中從第1個字符開始‘tr’第2次出現(xiàn)的位置。Select instr(‘oracle training’,’ra’,1,2) from dual。6） LENGTH(C)：返回字符串C的長度，注意一個漢字和一個字母的長度都是17） LOWER(C)：返回字符串C的所有字符為小寫。8） UPPER(C)：返回字符串C的所有字符為大寫。9） LPAD(C1, i[, C2])：CC2均為字符串，i為整數(shù)。在C1的左側(cè)用C2補(bǔ)足長度i，可多次重復(fù)；如果i小于C1的長度，只返回i那么長的C1字符，其它的被截去；C2的缺省值為單空格。例如：在字符串‘幸?！挠疫呌谩?’補(bǔ)足到10個字符，然后在結(jié)果的左邊用‘a(chǎn)mp?！a(bǔ)足到17個字符。SQL select LPAD(Rpad(39。幸福39。,10,39。*39。),17,39。amp。39。) FROM DUAL。說明：lpad是左填充，rpad是右填充。10） LTRIM(C)和RTRIM(C)：分別是刪除C的前導(dǎo)空格和刪除C尾部空格。11） TRIM(C)：既刪除C的前導(dǎo)空格又刪除C尾部的空格。12） SUBSTR(string，start，count)：從母串string中取從start開始的count個字符作為一個子串返回。13） Replace(string,s1,s2)：用s2替換出現(xiàn)在string中的s1字符串。 如：select replace(39。aabbccddeeaaa39。,39。aa39。,39。AA39。) FROM DUAL。14） TRANSLATE(C1,C2,C3)：它是分級替換的，具體做法如下：a) 若在C1中能找到與C2全部相同的，就用C3把相應(yīng)的字符全部代替。（第一級）結(jié)果：b) 若在C1中能找到與C2部分相同的，就用C3中與C2對應(yīng)位置上的字符代替（第二級）?？煞秩缦氯N情況：I) 若C2長度等于C3的長度，做對應(yīng)位置字符替換。結(jié)果：Ii）若C2長度大于C3的，相當(dāng)于把C3的末尾加上幾個空串直到與C2長度一樣，然后再做對應(yīng)位置字符替換。結(jié)果：Iii）若C2長度小于C3的，相當(dāng)于只保留C3前面的與C2長度的字符串，然后再做對應(yīng)位置字符替換。實(shí)際上這種情況與I)是一樣的。結(jié)果： 數(shù)值函數(shù)1）ABS(a)：求指定數(shù)a的絕對值。2）EXP(n)：返回e的n次冪。3）MOD(m，n)：返回m除以n的余數(shù)。4）ROUND(n1，n2)： n2=0的正整數(shù)時返回n1所保留的小數(shù)位數(shù)，其余的被舍入掉；n20的負(fù)整數(shù)時，返回的是n1舍入小數(shù)點(diǎn)左邊的n2位。5）SIGN(n)：若n為負(fù)數(shù)返回1；n為整數(shù)返回1；n為0,返回0。6）SQRT(n)：返回n的平方根。7）TRUNC(n1，n2)：nn2同ROUND函數(shù)，只是這個是按截尾法處理。 日期函數(shù) 1）ADD_MONTHS(D,I)：返回日期D加上I個月后的結(jié)果，I可以是任意的整數(shù)，如果I含有小數(shù)，那么系統(tǒng)將小數(shù)部分截去。 2）LAST_DAY(D)：返回包含日期D的月份的最后一天。3）MONTHS_BETWEEN(D1,D2)：返回D1和D2之間的月數(shù)目，如果D1和D2的日期相同、或者都是該月的最后一天，那么將返回一個整數(shù)，否則返回的結(jié)果將包含一個分?jǐn)?shù)。select months_between(sysdate+20,sysdate) FROM DUAL。的結(jié)果為： 4）NEXT_DAY(d，dow)：返回日期d后由dow給出的條件的第一天，dow使用當(dāng)前會話中給出的語言指定了一周中的某一天，返回的時間分量與d的時間分量相同。例如：select next_day(sysdate,39。星期二39。) from dual。 注意在oracle中文環(huán)境下，用中文“星期幾”，若用英文（如Monday等）則測試不成功。5） ROUND(D[,FMT])：返回FMT指定格式的日期D。如：select round(sysdate,39。year39。) from dual。6）TRUNC(D[,FMT])：返回FMT指定格式的日期D，用法同round函數(shù)。 select sysdatetrunc(sysdate,39。year39。) from dual 從年初到現(xiàn)在的天數(shù)7）SYSDATE沒有參數(shù)，返回當(dāng)前的系統(tǒng)日期和時間。應(yīng)用：如果給教師根據(jù)年滿某個工齡而漲工資，則可以用months_between(sysdate,workday)工齡*12作為條件。 轉(zhuǎn)換函數(shù) 轉(zhuǎn)換函數(shù)是在日期、字符和數(shù)值之間的相互轉(zhuǎn)換。 to_char(number/date,’formatmodel’)：按照formatmodel的格式把數(shù)值number或日期date轉(zhuǎn)換為相應(yīng)的字符串。1）例如：select to_char(sysdate,39。yyyymmdd39。) from dual。2）有關(guān)formatmodel的說明：（1）日期的顯示格式：YYYY：4位年份YY：兩位年份Year：年的英文全名 如nineteen nityeightMM：月的兩位數(shù)字Month：月的英文全名 如MAYMON：英文月的前三位簡寫 如OCTDAY：星期幾的英文全名 如MondayDD：2位日期（2）數(shù)字的顯示格式：9：代表一個數(shù)字位0：代表數(shù)字前面補(bǔ)0，如to_char(1200,’’)的結(jié)果：.：小數(shù)點(diǎn)的位置，：標(biāo)記數(shù)字位置的逗號L：根據(jù)數(shù)據(jù)庫字符集加貨幣符號 如to_char(1200,’’)。結(jié)果：$：給浮點(diǎn)數(shù)增加$結(jié)果：To_char函數(shù)中formatmodel固定格式: 9代表一個數(shù)字0強(qiáng)迫0顯示$顯示美元符號L強(qiáng)制顯示一個當(dāng)?shù)氐呢泿欧?顯示一個小數(shù)點(diǎn),顯示一個千位分隔符號 to_date(c[,’formatmodel’])：c表示字符串，formatmodel表示一種特殊格式的字符串。返回formatmodel格式的字符串c所對應(yīng)的日期。其實(shí)該函數(shù)將字符串c轉(zhuǎn)換為date數(shù)據(jù)類型。如： to_number(c)：c表示字符串，formatmodel表示一種特殊格式的字符串。返回formatmodel格式的數(shù)字。其實(shí)該函數(shù)將字符串c轉(zhuǎn)換相應(yīng)的數(shù)值。 聚合函數(shù)（組函數(shù)） 聚合函數(shù)的應(yīng)用MAX(x)：返回某個字段的所有值中的最大值。MIN(x)：返回某個字段的所有值中的最小值。 例如：查詢教工中工資最高和最低的員工AVG(x)：返回指定字段中的平均值。注意avg（）函數(shù)只對數(shù)值型字段使用，即x的的數(shù)值類型只能是數(shù)值型的，如果空值，忽略不計。 COUNT(*)：返回查詢中行的數(shù)目，*表示返回所有的行，用來統(tǒng)計一個表中有多少條記錄（包含有空值的記錄）。 SUM(x)：返回選擇列表項(xiàng)目x的數(shù)值的總和。 GROUP BY子句的應(yīng)用聚合函數(shù)一般是處理那些已經(jīng)分好組的數(shù)據(jù)，分組可以用group by子句來完成。例如： 統(tǒng)計各教研室的人數(shù)求各個教研室中的最高工資 HAVING與WHERE關(guān)鍵字的使用這兩個都是條件子句，where主要與select搭配使用，而having只能出現(xiàn)在有g(shù)roup by子句的sql語句中。注意，聚合函數(shù)不能出現(xiàn)where子句中。例題：1）查詢教研室工資總和超過10000元的教研室2）查詢除了助教外哪些教研室的工資總和多于8000元。 ORDER BY子句的使用使用order by子句可以對查詢結(jié)果進(jìn)行排序，其中包括升序（asc，缺省值）和降序（desc）。舉例： Oracle 10g集合運(yùn)算進(jìn)行如下三種集合運(yùn)算的條件：字段個數(shù)相同、對應(yīng)列的類型匹配。 Union 并例如：結(jié)果是兩個查詢結(jié)果的并集，若有重復(fù)的記錄，則只取它們中的一條。 Intersect 交結(jié)果為空Minus 差兩個集合A、B，AB的結(jié)果是從A中去掉A與B共同的元素而剩余的元素。AB=AA∩B 統(tǒng)計每個班的學(xué)生數(shù) 統(tǒng)計每個專業(yè)的學(xué)生數(shù) 查詢每個教研室中的最低或最高總工資，并按總工資降序排列。 查詢每個多于1人的教研室中最高和最低總工資，要求顯示出教研室名、人數(shù)、最低工資和最高工資。 查詢至少有3人的教研室的名稱及人數(shù)。 求每個教研室的平均工資（要求看到教研室名稱、人數(shù)和平均工資）。第二天總結(jié) 單行函數(shù) 聚合函數(shù)：min（）、max（）、sum（）、avg（）、count（）等 Select查詢語句擴(kuò)充 SELECT [DISTINCT]{*,column [alias],...} FROM table1,table2[WHERE condition(s)][GROUP BY column [HAVING condition(s)]] [ORDER BY {column,expr,alias}[ASC|DESC]]。 三種集合運(yùn)算：union、intersect、minusOracle第三天上午 交叉連接（廣義笛卡爾積）：廣義笛卡爾積是不帶連接謂詞的連接，兩個表的廣義笛卡爾積即是兩表中元組的交叉乘積，其連接的結(jié)果會產(chǎn)生一些沒有意義的元組，所以這種運(yùn)算實(shí)際很少使用。舉例 select *