Oracle數(shù)據(jù)基礎(一)

一. Oracle字符串操作

字符串類型

    1. CHAR和VARCHAR2類型

          表示字符串數(shù)據(jù)類型，用來在表中存放字符串信息；

          CHAR存放定長字符，即存不滿補空格（浪費空間，節(jié)省時間）；VARCHAR2存放變長字符，存多少占用多少（浪費時間，節(jié)省空間）；

          按照字符的自然順序排序。

    2. CHAR和VARCHAR2的存儲編碼

          默認單位是字節(jié)，可指定為字符

               — CHAR(10)，等價于CHAR(10 BYTE)

               — 指定單位為字符：CHAR(10 CHAR)，20個字節(jié)

               — VARCHAR2(10)，等價于VARCHAR2(10 BYTE)

               — 指定單位為字符：VARCHAR2(10 CHAR)，20個字節(jié)

          每個英文字符占用一個字節(jié)，每個中文字符按編碼不同，占用2-4個字節(jié)

               — ZHS16GBK：2個字節(jié)

               — UTF-8：2－4個字節(jié)

   3. CHAR和VARCHAR2的最大長度

          CHAR最大取值為2000字節(jié)，最多保存2000個英文字符

          VARCHAR2最大取值為4000字節(jié)

          CHAR可以不指定長度，默認為1，VARCHAR2必須指定長度

    4. LONG和CLOB類型

          LONG：VARCHAR2加長版，存儲變長字符串，最多達2GB的字符串數(shù)據(jù)

          LONG有諸多限制：每個表只能有一個LONG類型列；不能作為主鍵；不能建立索引；不能出現(xiàn)在查詢條件中

          CLOB：存儲定長或變長字符串，最多達4GB的字符串數(shù)據(jù)

          ORACLE建議開發(fā)中使用CLOB替代LONG類型

字符串函數(shù)

    1. CONCAT和“||”

          CONCAT(char1, char2);

               — 返回兩個字符串連接后的結果，兩個參數(shù)char1，char2是要連接的兩個字符串

          等價操作：連接操作符“||”

          如過char1和char2任何一個為NULL，相當于連接了一個空格

          “||”在數(shù)據(jù)庫中是連接字符串，相當于java中的“+”，注意和java“||”區(qū)分

          eg：

               java中：”hello” + “world” ==> “helloworld"

               DB中：’hello’||’world’ ==> ‘hello world'

               oracle中：CONCAT(‘hello’,’world’) ==> ‘hello world'

    2. FROM DUAL(虛表)

          DUAL：虛表，沒有這么一個表，只為了滿足SELECT的語法要求。

               — 我們常用虛表來測試表達式的結果。

               — 在數(shù)據(jù)庫中，我們想測試某個表達式的結果只能使用SELECT語句來實現(xiàn)

               — 什么時候使用虛表：當SELECT語句中沒有任何表中的字段參與時

               — eg: 假設表emp中name和sal兩個字段存儲的內(nèi)容分別是名字和薪資

                    SELECT name||’:’||sal FROM emp

                    則所得結果為：boss:5000

    3. LENGTH

          LENGTH(char)：用于返回字符串的長度

          如果字符類型是VARCHAR2，返回字符的實際長度，如果字符類型是CHAR，長度還要包括后補的空格

          eg: SELECT name,LENGTH(name) FROM emp;

               所得結果：boss 4(name為CHAR)

    4. UPPER，LOWER和INITCAP

          大小寫轉(zhuǎn)換函數(shù)，用來轉(zhuǎn)換字符的大小寫

          UPPER(char)用于將字符轉(zhuǎn)換為大寫形式

          LOWER(char)用于將字符轉(zhuǎn)換為小寫形式

          INITCAP(char)用于將字符串中每個單詞的首字符大寫，其它字符小寫，單詞之間用空格和非字母字符分隔

          如果熟人的參數(shù)是NULL值，仁返回NULL值

    5. TRIM，LTRIM，RTRIM

          作用：截去子串

          語法形式：

               — TRIM(c2 FROM c1)從c1的前后截去c2

               — LTRIM(c1[, c2])從c1的左邊(Left)截去c2

               — RTRIM(c1[, c2])從c1的右邊(Right)截去c2

               如果沒有c2就去除空格

               eg：SELECT TRIM(‘e’ from ‘eeeeliteeee’) FROM DUAL;

          參數(shù)中from前面只能是單一字符

          若沒有from以及前面的字符，則是去除空白

               eg：SELECT LTRIM(‘eeeeliteeee’,’e’) FROM DUAL;

                      SELECT RTRIM(‘eeeliteee’,’e’) FROM DUAL;

          不指定第二個參數(shù)，默認是去除空白

    6. LPAD, RPAD

          補位函數(shù)，用于在字符串char1的左端或右端用char2補足到n位，char2可重復多次

          — LPAD(char1, n, char2)左補位函數(shù)

          — RPAD(char1, n, char2)右補位函數(shù)

          在emp表中使用左補位，將sal用$補齊20位

               eg：SELECT name, LPAD(sal, 20, ‘$’) as “salary” FROM emp;

               作用：要求顯示20個字符，若sal的值不足長度，則補充若干個’$’，以達到20個字符

               eg：SELECT RPAD(‘a(chǎn)aaaAAAAA’) FROM DUAL;

               得到結果為aaaaA

    7. SUBSTR

          SUBSTR(char, [m[, n]])

               — 用于獲取字符串的子串，返回char中從m位開始取n個字符

          如果m=0，則從首字符開始，如果m取負數(shù)，則從尾部開始

          如果沒有設置n，或者n的長度超過了char的長度，則取到字符串末尾為止

          字符串的首位計數(shù)從1開始

    8. INSTR

          INSTR(char1, char2[, n [, m]]);

               返回子串char2在原字符串char1中的位置

          參數(shù)：

               — 從n的位置開始搜索，沒有指定n，從第一個字符開始搜索

               — m用于指定子串的第m次出現(xiàn)次數(shù)，如果不指定取值1

               — 如果在char1中沒有找到子串char2，返回0

二. Oracle數(shù)值操作

數(shù)值類型

    1. NUMBER(P)表示整數(shù)

          完整語法：NUMBER(precision, scale)

               — 如果沒有設置scale，則默認取值0，即NUMBER(p)表示整數(shù)

               — P表示數(shù)字的總位數(shù)，取值為1-38

          用來在表中存放如編碼，年齡，次數(shù)等用整數(shù)記錄的數(shù)據(jù)

    2. NUMBER(P, S)表示浮點數(shù)

          NUMBER(precision, scale)

               — precision：NUMBER可以存儲的最大數(shù)字長度（不包括左右兩邊的0）

               — scale：在小數(shù)點右邊的最大數(shù)字長度（包括左側0）

          指定了s但是沒有指定p，則p默認為38

          經(jīng)常用于表中存放金額，成績等有小數(shù)的數(shù)據(jù)。

          NUMBER的變種數(shù)據(jù)類型：內(nèi)部實現(xiàn)是NUMBER，可以將其理解為NUMBER的別名，目的是多種數(shù)據(jù)庫及編程語言兼容

               — NUMERIC(p, s)：完全映射至NUMBER(p, s)

               — DECIMAL(p, s)或DEC(p, s)：完全映射至NUMBER(p, s)類型

               — INTEGER或INT：完全映射至NUMBER(38)類型

               — SMALLINT：完全映射至NUMBER(38)類型

               — FLOAT(b)：映射至NUMBER類型

               — DOUBLE PRECISION：映射至NUMBER類型

               — REAL：映射至NUMBER類型

數(shù)值函數(shù)

    1. ROUND

          ROUND(n[, m])：用于四舍五入

               — 參數(shù)中的n可以是任何數(shù)字，指要被處理的數(shù)字

               — m必須是整數(shù)

               — m取正數(shù)則四舍五入到小數(shù)點后第m位

               — m取0值則四舍五入到整數(shù)位

               — m取負數(shù)，則四舍五入到小數(shù)點前m位

               — m缺省，默認是0

               eg：SELECT ROUND(45.678, -1) FROM DUAL; —50

    2. TRUNC

          TRUNC(n[, m])：用于截取

               — n和m的定義和ROUND(n[, m])相同，不同的是功能上按照截取的方式處理數(shù)字n

               eg：SELECT TRUNC(45.678, -1) FROM DUAL; —40

    3. MOD

          MOD(m, n)：返回m除以n后的余數(shù)

               —n為0則直接返回m

    4. CEIL和FLOOR

          CEIL(n)，F(xiàn)LOOR(n)這兩個函數(shù)，一個是取大于或等于n的最小整數(shù)值，另一個是取小于或等于n的最大整數(shù)值

          eg：SELECT CEIL(45.678) FROM DUAL; —46

                  SELECT FLOOR(45.678) FROM DUAL; —45

三. Oracle日期操作

日期類型

    1. DATE

          ORACLE中最常用的日期類型，用來保存日期和時間

          DATE表示的日期范圍可以是公元前4712年1月1日至公元9999年12月31日

          DATE類型在數(shù)據(jù)庫中的存儲固定為7個字節(jié)，格式為：

               — 第一個字節(jié)：世紀+100

               — 第二個字節(jié)：年

               — 第三個字節(jié)：月

               — 第四個字節(jié)：天

               — 第五個字節(jié)：小時+1

               — 第六個字節(jié)：分+1

               — 第七個字節(jié)：秒+1

    2. TIMESTAMP

          ORACLE常用的日期類型

          與DATE的區(qū)別是不僅可以保存日期和時間，還能保存小數(shù)秒，最高精度可以到ns（納秒）

          數(shù)據(jù)庫內(nèi)部用7或者11字節(jié)存儲，精度為0，用7字節(jié)存儲，與DATE功能相同，精度大于0則用11字節(jié)存儲

          格式為：

               — 第1字節(jié)-第7字節(jié)：和DATE相同

               — 第8-11字節(jié)：納秒，采用4個字節(jié)存儲，內(nèi)部運算類型為整型

日期關鍵字

    1. SYSDATE

          其本質(zhì)是一個ORACLE的內(nèi)部函數(shù)，返回當前的系統(tǒng)時間，精確到秒

          默認顯示格式是DD-MON-RR

    2. SYSTIMESTAMP

          內(nèi)部函數(shù)，返回當前系統(tǒng)日期和時間，精確到毫秒

日期轉(zhuǎn)換函數(shù)

    1. TO_DATE

          TO_DATE(char[, fmt[, nlsparams]])：將字符串按照定制格式轉(zhuǎn)換為日期類型

               — char：要轉(zhuǎn)換的字符串

               — fmt：格式

               — nlsparams：指定日期語言

               — 常用的日期格式見表

    2. TO_CHAR

          將其它類型的書籍轉(zhuǎn)換為字符類型

          TO_CHAR(date[, fmt[, nlsparams]])：將日期類型數(shù)據(jù)date按照fmt的格式輸出字符串。nlsparams用于指定日期語言

          需要注意的是：在日期格式字符串中，出現(xiàn)的非關鍵字符或符號的其它字符時，需要使用雙引號

               eg：SELECT TO_CHAR(SYSDATE, “yyyy”year”mm”month”dd”day”hh:mi:ss”) FROM DUAL;

          兩個日期可以進行減法操作，差為相差的天數(shù)

日期常用函數(shù)

    1. LAST_DAY

          LAST_DAY(date)：返回日期date所在月的最后一天

          在按照自然月計算某些業(yè)務邏輯，或者安排月末周期性活動時很有用處

          eg：SELECT LAST_DAY(SYSDATE) FROM DUAL;  —30-9月－17

                    SELECT LAST_DAY(’20-2月-09’) FROM DUAL; —28-2月－09

    2. ADD_MONTHS

          ADD_MONTHS(date, i)：返回日期date加上i個月后的日期值

               — 參數(shù)i可以是任何數(shù)字，大部分時候取正值整數(shù)

               — 如果i是小數(shù)，將會被截取整數(shù)后再參與運算

               — 如果i負數(shù)，則獲得的是減去i個月后的日期值

               eg：計算職員入職20周年紀念日

                      SELECT name, ADD_MONTHS(hiredate, 20 * 12) as ‘20周年’ FROM emp;

    3. MONTHS_BETWEEN

          MONTHS_BETWEEN(date1, date2)：計算date1和date2兩個日期值之間間隔了多少個月

          實際運算是date1-date2，如果date2時間比date1晚，會得到負值

          除非兩個日期間隔是整數(shù)月，否則會得到帶小數(shù)位的結果。

               — 此時可以使用FLOOR得到整月

                    eg：SELECT name, FLOOR(MONTHS_BETWEEN(SYSDATE, hiredate)) FROM emp;

    4. NEXT_DAY

          NEXT_DAY(date, char)：返回date日期數(shù)據(jù)的下一個周幾，周幾是由參數(shù)char來決定的

          在中文環(huán)境下，直接使用“星期三”這種形式，英文環(huán)境下，需要使用“WEDNESDAY”這種英文的周幾。位避免麻煩，可以直接使用1-7表示周日－周六

          NEXT_DAY不是明天

          SELECT NEXT_DAY(SYSDATE, 3) FROM DUAL;  —離現(xiàn)在最近的周2（不包含今天）

    5. LEAST, GREATEST

          GREAGEST(expr1[, expr2[, expr3]]...)

          LEAST(expr1[, expr2[,expr3]]...)

          也被稱作比較函數(shù)，可以有多個參數(shù)值，返回結果是參數(shù)列表中最大或最小的值

          參數(shù)類型必須一致

          在比較之前，在參數(shù)列表中第二個以后的參數(shù)會被隱含的轉(zhuǎn)換為第一個參數(shù)的數(shù)據(jù)類型，所以如果可以轉(zhuǎn)換，則繼續(xù)比較，如果不能轉(zhuǎn)換將會報錯。

    6. EXTRACT

          EXTRACT(date FROM datetime)：從參數(shù)date time中提取參數(shù)date指定的數(shù)據(jù)，比如提取年，月，日

          eg：SELECT EXTRACT(HOUR FROM TIMESTAMP ‘2008-01-01 10:10:10’) FROM DUAL;

四. 空值操作

NULL的含義

          數(shù)據(jù)庫里的重要概念：NULL，即空值

          有時表中的某些字段值，數(shù)據(jù)未知或暫時不存在，取值NULL

          任何數(shù)據(jù)類型均可取值NULL

NULL的操作

    1. 插入NULL值和更新成NULL時只有在非空約束時才可操作

          查詢條件：WHERE name IS NULL/ WHERE name IS NOT NULL

          任何數(shù)據(jù)和NULL相加都是NULL

    2. 非空約束

          非空（NOT NULL）約束用于確保字段值不為空

          默認情況下，任何列都允許有空值

          當某個字段被設置了非空約束條件，這個字段中必須存在有效值。

空值函數(shù)

    1. NVL

          NVL(expr1, expr2)：將NULL轉(zhuǎn)變?yōu)榉荖ULL值

               — 如果expr1為NULL，則取值expr2，expr2是實際值

               — expr1和expr2可以是任何數(shù)據(jù)類型，但兩個參數(shù)的數(shù)據(jù)類型必須是一致的

    2. NVL2

          NVL2(expr1, expr2, expr3)：和NULL函數(shù)功能類似，都是將NULL轉(zhuǎn)變?yōu)閷嶋H值

          NVL2用來判斷expr1是否為NULL，如果不是NULL，返回expr2，如果是NULL，返回expr3