第九章-操作符及sql函數(shù)

1、第9章 操作符及SQL函數(shù)知識點回顧表連接原理多表連接查詢等值連接非等值連接交叉連接內(nèi)連接外連接特殊連接子查詢的概念及應(yīng)用單行子查詢多行子查詢2本章目標掌握 SQL 操作符掌握并熟練應(yīng)用SQL 函數(shù)單行函數(shù)：日期、字符、數(shù)字、轉(zhuǎn)換、其他分組函數(shù)分析函數(shù)3算術(shù)操作符算術(shù)操作符用于執(zhí)行數(shù)值計算可以在SQL語句中使用算術(shù)表達式，算術(shù)表達式由數(shù)值數(shù)據(jù)類型的列名、數(shù)值常量和連接它們的算術(shù)操作符組成算術(shù)操作符包括加(+)、減(-)、乘(*)、除(/)SELECT f_stuid, f_grade + 1 as new_grade FROM t_grade WHERE f_courseid=01;SELEC

2、T f_stuid, f_grade * 1.1 as new_grade FROM t_grade WHERE f_courseid=01;4SQL 操作符SQL 操作符集合操作符邏輯操作符比較操作符算術(shù)操作符連接操作符Oracle 支持的 SQL 操作符分類如下：5比較操作符 比較操作符用于比較兩個表達式的值比較操作符包括 =、!=、=、BETWEENAND、IN、LIKE 和 IS NULL等SQL SELECT f_id, f_name, f_birth FROM t_student WHERE f_birth SELECT f_id FROM t_student WHERE f_bi

3、rth IN (01-8月-84,30-8月-85);SQL SELECT f_id,f_name,f_sex,f_birth FROM t_student WHERE f_name LIKE _富%;6邏輯操作符邏輯操作符用于組合多個比較運算的結(jié)果以生成一個或真或假的結(jié)果。邏輯操作符包括與(AND)、或(OR)和非(NOT)。 SELECT * FROM t_student WHERE f_birth = 01-1月-80 AND f_birth =70 and f_courseid = 01INTERSECTselect f_stuid from t_grade where f_grade

4、=70 and f_courseid = 02;統(tǒng)計C語言或Java為良好的同學(xué)學(xué)號select f_stuid from t_grade where f_grade=70 and f_courseid = 01UNIONselect f_stuid from t_grade where f_grade=70 and f_courseid = 02;統(tǒng)計C語言良好但Java未達到良好的同學(xué)學(xué)號select f_stuid from t_grade where f_grade=70 and f_courseid = 01MINUSselect f_stuid from t_grade where

5、 f_grade=70 and f_courseid = 02;9連接操作符連接操作符用于將多個字符串或數(shù)據(jù)值合并成一個字符串 SELECT (學(xué)號為 | f_id| 的同學(xué)姓名是 |f_name) as ddress FROM t_student;通過使用連接操作符可以將表中的多個列合并成邏輯上的一行列10操作符的優(yōu)先級SQL 操作符的優(yōu)先級從高到低的順序是：算術(shù)操作符 -最高優(yōu)先級連接操作符比較操作符NOT 邏輯操作符AND 邏輯操作符OR 邏輯操作符 -最低優(yōu)先級 11SQL 函數(shù)Oracle 提供一系列用于執(zhí)行特定操作的函數(shù)SQL 函數(shù)帶有一個或多個參數(shù)并返回一個值以下是SQL函數(shù)的分

6、類：SQL 函數(shù)單行函數(shù)分析函數(shù)分組函數(shù)12單行函數(shù)分類單行函數(shù)對于從表中查詢的每一行只返回一個值可以出現(xiàn)在 SELECT 子句中和 WHERE 子句中 單行函數(shù)可以大致劃分為：日期函數(shù)數(shù)字函數(shù)字符函數(shù)轉(zhuǎn)換函數(shù)其他函數(shù)13日期函數(shù)日期函數(shù)對日期值進行運算，并生成日期數(shù)據(jù)類型或數(shù)值類型的結(jié)果日期函數(shù)包括：ADD_MONTHSMONTHS_BETWEENLAST_DAYROUNDNEXT_DAYTRUNCEXTRACT14字符函數(shù) 2-1 函數(shù) 輸入 輸出Initcap(char) select initcap(hello) from dual;Hello Lower(char) select l

7、ower(FUN) from dual;fun Upper(char) select upper(sun)from dual;SUN Ltrim(char,set) select ltrim( xyzadams,xyz) from dual;adamsRtrim(char,set) select rtrim(xyzadams,ams) from dual;xyzad Translate(char, from, to) select translate(jack,j ,b) from dual;back Replace(char, searchstring,rep string) select

8、replace(jack and jue ,j,bl) from dual;black and blue Instr (char, m) select instr (worldwide,d) from dual;5 Substr (char, m, n) select substr(abcdefg,3,2) from dual;cd Concat (expr1, expr2) select concat (Hello, world) from dual;Hello world字符函數(shù)接受字符輸入并返回字符或數(shù)值15字符函數(shù) 2-2以下是一些其它的字符函數(shù)：CHR和ASCIILPAD和RPADT

9、RIMLENGTHDECODESQL SELECT CHR(67) FROM dual;SELECT LPAD(function,15,=) FROM dual;SQL SELECT TRIM(9 from 9999876789999) FROM dual;SELECT LENGTH(student) FROM dual;SELECT f_id,f_name, DECODE(f_calss,1,class one,2,class two,other class) class FROM t_student;16數(shù)字函數(shù) 函數(shù) 輸入 輸出Abs(n) Select abs(-15) from du

10、al; 15Ceil(n) Select ceil(44.778) from dual; 45Cos(n) Select cos(180) from dual; -.5984601 Floor(n) Select floor(100.2) from dual; 100Power(m,n) Select power(4,2) from dual; 16 Mod(m,n) Select mod(10,3) from dual; 1Round(m,n) Select round(100.256,2) from dual; 100.26 Trunc(m,n) Select trunc(100.256,

11、2) from dual; 100.25 Sqrt(n) Select sqrt(4) from dual; 2 Sign(n)Select sign(-30) from dual;-1數(shù)字函數(shù)接受數(shù)字輸入并返回數(shù)值結(jié)果17轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)將值從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型常用的轉(zhuǎn)換函數(shù)有：TO_CHARTO_DATETO_NUMBERSELECT TO_CHAR(sysdate,YYYY年fmMM月fmDD日 HH24:MI:SS)FROM dual;Select TO_CHAR(0.123,0.999) FROM DUAL;SELECT TO_DATE(2005-12-06 , yyy

12、y-mm-dd)FROM dual;SELECT TO_NUMBER(100) FROM dual;18其它函數(shù)以下是幾個用來轉(zhuǎn)換空值的函數(shù)：NVLNVL2NULLIFSELECT f_id, f_name, NVL(avg(f_grade),0)FROM t_student left join t_grade on f_id = f_stuidGROUP BY f_id, f_name;SELECT f_id, f_name, NVL2(f_department,existed dep,other dep as dep)from t_student;SELECT NULLIF(100,200) FROM DUAL;19分析函數(shù) 2-1分析函數(shù)根據(jù)一組行來計算聚合值用于計算完成聚集的累計排名、移動平均數(shù)等分析函數(shù)為每組記錄返回多個行分析函數(shù)DENSE_RANKRANKROW_NUMBER20分析函數(shù) 2-2以下三個分析函數(shù)用于計算一個行在一組有序行中的排位，序號從1開始ROW_NUMBER 返回連續(xù)的排位，不論值是否相等RANK 具有相等值的行排位相同，序數(shù)隨后跳躍DENSE_RANK 具有相等值的行排