數(shù)據(jù)庫培訓-SQL基礎(chǔ)

Oracle數(shù)據(jù)庫基礎(chǔ)山東眾陽軟件有限公司張錄Oracle數(shù)據(jù)庫概述SQL基礎(chǔ)處理數(shù)據(jù)第三方工具簡單使用Oracle數(shù)據(jù)庫概述Oracle（甲骨文）公司是世界上最大的企業(yè)軟件公司，主要向全球用戶提供數(shù)據(jù)庫、工具和應(yīng)用軟件以及相關(guān)的咨詢、培訓和支持服務(wù)。Oracle數(shù)據(jù)庫（OracleDatabase）是甲骨文公司推出的關(guān)系型數(shù)據(jù)庫產(chǎn)品，是世界上使用最為廣泛的數(shù)據(jù)庫管理系統(tǒng)，到目前仍舊在數(shù)據(jù)庫市場上占有主要份額。關(guān)系型數(shù)據(jù)庫:介紹關(guān)系型數(shù)據(jù)庫：簡而言之就是關(guān)系/二維表的集合。Oracle數(shù)據(jù)庫表名:EMPLOYEES表名:DEPARTMENTS關(guān)系型數(shù)據(jù)庫：表的術(shù)語2.主鍵3.列4.外鍵6.空值1.行5.域空值既不是空格也不是0多表關(guān)聯(lián)表中每一行數(shù)據(jù)均由主鍵保證唯一性。多表通過外鍵關(guān)聯(lián)表:EMPLOYEES表:DEPARTMENTS主鍵主鍵外鍵…Oracle的總體框架結(jié)構(gòu)Oracle實例一個oracle實例由一系列的后臺進程和內(nèi)存結(jié)構(gòu)組成當啟動實例的時候，oracle會開辟一塊內(nèi)存（SGA）用于存放數(shù)據(jù)庫信息，并啟動后臺進程，SGA內(nèi)的信息是被后臺進程所共享的。實例和數(shù)據(jù)庫的關(guān)系實例是“內(nèi)存”和“后臺進程”的集合，“數(shù)據(jù)庫”是數(shù)據(jù)的物理存儲，要特別注意，一個實例可以用于一個數(shù)據(jù)庫，多個實例也可同時用于一個數(shù)據(jù)庫（RAC），實例和數(shù)據(jù)庫是多對一的關(guān)系。Oracle的內(nèi)存結(jié)構(gòu)Oracle的內(nèi)存由PGA（ProgramGlobalArea）和SGA（SystemGlobalArea）組成。PGA中包括服務(wù)器進程的數(shù)據(jù)和控制信息，其中的信息是不被共享的。SGA中包括數(shù)據(jù)庫實例的數(shù)據(jù)和控制信息，是可以被共享的。SGA被劃分成多個區(qū)域，主要的區(qū)域如下：數(shù)據(jù)庫高速緩沖區(qū)（DatabaseBufferCache），用于存放數(shù)據(jù)文件的副本，所有的用戶進程都可以并發(fā)的連接到這個實例，共享數(shù)據(jù)庫高速緩沖區(qū)中的數(shù)據(jù)。共享池（SharedPool）又分為庫緩沖區(qū)（LibraryCache）和字典緩沖區(qū)（DictionaryCache）。庫緩沖區(qū)用于存放使用過的sql，包括這些sql語句的執(zhí)行計劃及這些sql的語法分析樹。數(shù)據(jù)庫對象的信息存儲在數(shù)據(jù)字典表中，包括用戶賬號數(shù)據(jù)、數(shù)據(jù)文件名、段名、盤區(qū)位置、表的說明和權(quán)限等，當數(shù)據(jù)庫需要這些信息時就會讀取數(shù)據(jù)字典并將這些信息存儲在字典緩沖區(qū)中。重做日志緩沖區(qū)（RedoLogBuffer）用于存放用戶對數(shù)據(jù)庫的改變，這是一片循環(huán)使用的內(nèi)存區(qū)域。oracle的存儲結(jié)構(gòu)Oracle的存儲結(jié)構(gòu)分為物理存儲結(jié)構(gòu)和邏輯存儲結(jié)構(gòu)。物理存儲結(jié)構(gòu)由數(shù)據(jù)文件、聯(lián)機日志文件、控制文件構(gòu)成，這些是數(shù)據(jù)庫嚴格意義上的“物理結(jié)構(gòu)組成”，除此之外，組成oracle的還有其他的一些文件。Oracle存儲的邏輯結(jié)構(gòu)由數(shù)據(jù)塊（DataBlock）、區(qū)（Extent）、段（Segment）和表空間（Tablespace）組成，塊是最基本的存儲單元，它們是一個自下而上的邏輯結(jié)構(gòu)。數(shù)據(jù)塊數(shù)據(jù)塊數(shù)據(jù)塊由塊頭、表目錄、行目錄、行數(shù)據(jù)和空余空間五部分組成。我們又把塊頭、表目錄、行目錄合稱為頭部信息區(qū)，存放塊的信息而不存放數(shù)據(jù)。數(shù)據(jù)塊與操作系統(tǒng)塊不同，但是又有關(guān)系，一個數(shù)據(jù)塊的大小是操作系統(tǒng)塊大小的整數(shù)倍。行鏈接：如果插入的一行數(shù)據(jù)很大以至于一個塊存不下，oracle就會把一行數(shù)據(jù)分成幾段存在數(shù)據(jù)塊中，這個過程叫行鏈接。行遷移：數(shù)據(jù)塊中存在一條記錄，update操作使記錄變長，而當前塊沒有足夠的空間容納這條記錄，無奈之下，只能把整行數(shù)據(jù)移到一個新的數(shù)據(jù)塊中，并在原數(shù)據(jù)塊中留下一個指針指向新的數(shù)據(jù)塊，被移動的數(shù)據(jù)的rowid保持不變，這就是行遷移。注意行鏈接和行遷移都會影響數(shù)據(jù)庫的性能。區(qū)和段區(qū)是數(shù)據(jù)庫的一個邏輯存儲單元，它由連續(xù)的數(shù)據(jù)塊組成，特別強調(diào)的是區(qū)是一段連續(xù)的存儲空間，當段中的空間耗盡時oracle會分配一個新的區(qū)給這個段。可以通過存儲參數(shù)來控制區(qū)的分配。段是存放數(shù)據(jù)的邏輯存儲單元，由一系列的區(qū)組成，包括數(shù)據(jù)段、索引段、臨時段、回滾段等。表空間系統(tǒng)默認創(chuàng)建的是小文件表空間，是傳統(tǒng)的表空間類型。大文件表空間由一個單獨的很大的數(shù)據(jù)文件組成，使oracle能夠使用和管理超級大的文件。系統(tǒng)表空間（SYSTEM）：存放數(shù)據(jù)字典的數(shù)據(jù)，一旦打開數(shù)據(jù)庫，系統(tǒng)表空間必須在線。輔助表空間（SYSAUX）：對系統(tǒng)表空間的補充，存放數(shù)據(jù)庫組件的信息，必要時可分擔系統(tǒng)表空間的負載?；貪L表空間（UNDOTBS）：存放回滾數(shù)據(jù)。每個數(shù)據(jù)庫可以有多個回滾表空間，但只能有一個活動的。臨時表空間：是臨時存放數(shù)據(jù)的地方，創(chuàng)建用戶的時候應(yīng)該給用戶指定一個臨時表空間。USERS表空間:如果創(chuàng)建用戶時沒有使用DEFAULTTABLESPACE關(guān)鍵字指定默認表空間，oracle會將其指定給用戶作為默認表空間。SQL基礎(chǔ)什么是

SQL?SQL：結(jié)構(gòu)化查詢語言

(StructuredQueryLanguageSQL)是訪問Oracle數(shù)據(jù)庫中數(shù)據(jù)的描述方法SQL語言主要提供下述功能：查詢表中數(shù)據(jù)行插入、修改、刪除表中的數(shù)據(jù)行創(chuàng)建、修改、刪除數(shù)據(jù)庫對象保證數(shù)據(jù)庫的一致性和完整性控制數(shù)據(jù)庫及其訪問主要的SQL語句SELECTINSERTUPDATEDELETEMERGECREATEALTERDROPRENAMETRUNCATECOMMITROLLBACKSAVEPOINTGRANTREVOKE數(shù)據(jù)獲取數(shù)據(jù)處理語言(DML)數(shù)據(jù)定義語言(DDL)事務(wù)控制語言(TCL)數(shù)據(jù)控制語言(DCL)COMMENT基本SQLSELECT語句SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable[WHEREcondition(s)][ORDERBY{column,expr,alias}[ASC|DESC]];在最簡單的形式中，SELECT語句必須包含下面的內(nèi)容：一個SELECT子句，指定被顯示的列一個FROM子句，指定表，該表包含SELECT子句中的字段列表選擇所有列與指定列SELECT*FROMdepartments;

用SELECT關(guān)鍵字后面的星號(*)，能夠顯示表中數(shù)據(jù)的所有列。SELECTdepartment_id,location_idFROMdepartments;

用SELECT語句來顯示表的指定列，指定列名之間用逗號分隔。SELECT后也可以指定偽列，一個偽列的行為像表中的列一樣，但不是真的存儲在表中。不能插入或刪除偽列的值。一些可用的偽列如下：CURRVAL、NEXTVAL、LEVEL、ROWID和ROWNUM。算數(shù)運算符用算術(shù)運算符創(chuàng)建數(shù)字和日期數(shù)據(jù)的表達式上表列出了SQL提供的算術(shù)運算符。除了FROM子句，你可以在任何一個SQL語句的子句中使用算術(shù)運算符，注意優(yōu)先級。對于日期數(shù)據(jù)，只能對DATE和TIMESTAMP數(shù)據(jù)類型使用加和減操作。操作+-*/說明加減乘除空值null是一個未分配的、未知的值，既然是未知的，就有無數(shù)種可能性null不是0，也不是空格判斷一個字段是否為NULL，應(yīng)該用ISNULL或ISNOTNULL，而不能用‘=’，同樣的，對NULL的!=、>、<、>=、<=等操作的結(jié)果都是未知的，也就算說，這些操作的結(jié)果仍然是NULL包含空值的算術(shù)表達式計算結(jié)果為空

總結(jié)：除了ISNULL、ISNOTNULL以外，對NULL的任何操作的結(jié)果還是NULL經(jīng)典的例子SQL> CREATEORREPLACEPROCEDUREP1(P_ININNUMBER)AS

2BEGIN

3IFP_IN>=0THEN

4DBMS_OUTPUT.PUT_LINE('TRUE');

5ELSE

6DBMS_OUTPUT.PUT_LINE('FALSE');

7ENDIF;

8END;

SQL> CREATEORREPLACEPROCEDUREP2(P_ININNUMBER)AS

2BEGIN

3IFP_IN<0THEN

4DBMS_OUTPUT.PUT_LINE(‘FALSE’);

5ELSE

6DBMS_OUTPUT.PUT_LINE(‘TRUE’);

7ENDIF;

8END;

如果傳入的是null值，結(jié)果是什么呢？定義列的別名列別名：改變列標題的名字可用于計算結(jié)果跟在列名后面或在列名和別名之間使用AS關(guān)鍵字如果別名中包含有空格、或特殊字符、或大小寫敏感，要求用雙引號

SELECTlast_nameASname,commission_pctcommFROMemployees;連字運算符連字運算符：連接列或者字符串到其它的列用兩個豎線表示(||)構(gòu)造一個字符表達式的合成列

SELECTfirst_name||last_nameASEmployeesFROMemployees;文字字符串文字字符串是包含在SELECT列表中的一個字符串，一個數(shù)字或者一個日期日期和字符的文字字符串值必須用單引號括起來每個文字字符串在每行輸出一次

SELECTlast_name||'isa'||job_idAS“EmployeeDetails”FROMemployees;使用WHERE子句

用WHERE子句限制返回的行

SELECT*|{[DISTINCT]

column|expression[alias],...}FROMtable[WHEREcondition(s)];

WHERE子句跟著FROM子句

WHERE限制查詢滿足條件的行

condition由列名、表達式、常數(shù)和比較操作組成

WHERE子句能夠比較列值、文字值、算術(shù)表達式或者函數(shù)，WHERE子句由三個元素組成：列名，比較條件，列名、常量或值列表。字符串和日期WHERE子句中：字符串和日期的值必須包含在單引號中，但是，數(shù)字常數(shù)不應(yīng)該包含在單引號中字符串的值是大小寫敏感的，日期值是格式敏感的日期的默認格式是DD-MON-RR.

SELECTlast_name,job_id,department_idFROMemployeesWHERElast_name='Whalen';

比較條件

運算含義

=等于

>大于

>=大于等于

<小于

<=小于等于

<>!=

^=

不等于

比較條件被用于一個表達式與一個值或與另一個表達式的比較。 ...WHEREhire_date='01-JAN-95' ...WHEREsalary>=6000 ...WHERElast_name='Smith'其他比較條件(一)

操作含義

BETWEEN...AND...在兩個值之間(包含)

IN(set)匹配一個任意值列表

LIKE匹配一個字符模板

ISNULL是一個空值

使用BETWEEN: SELECTlast_name,salary

FROMemployees

WHEREsalaryBETWEEN2500AND3500;

使用IN:

SELECTemployee_id,last_name,salary,manager_id

FROMemployees

WHEREmanager_idIN(100,101,201);其他比較條件(二)使用LIKE條件使用LIKE條件執(zhí)行有效搜索串值的通配符搜索搜索條件既可以包含文字也可以包含數(shù)字：

%表示任意順序的零個或多個字符

_表示一個字符

SELECTfirst_nameFROMemployeesWHEREfirst_nameLIKE'%S_';

使用NULL條件用ISNULL操作來測試空值

SELECTlast_name,manager_idFROMemployeesWHEREmanager_idISNULL;

NULL條件，包括ISNULL條件和ISNOTNULL條件。邏輯條件

運算含義

AND

如果兩個組成部分的條件都為真，返回TRUE

OR如果兩個組成部分中的任一個條件為真，返回TRUE

NOT如果跟隨的條件為假，返回TRUE

可以在WHERE子句中用AND和OR運算符使用多個條件。

如果要得到正確的結(jié)果：使用AND時要求兩個條件同時為真使用OR時要求兩者或多者之一為真即可NOT運算符則可以用于另一個SQL運算符，例如，IN、BETWEEN、LIKE、和NULL。注意：表達式NOT(column_nameISNULL)是正確的優(yōu)先規(guī)則表達式求值順序

1算術(shù)運算

2連字操作

3比較操作

4IS[NOT]NULL,LIKE,[NOT]IN5[NOT]BETWEEN6NOT邏輯條件

7AND邏輯條件

8OR邏輯條件使用圓括號改變優(yōu)先規(guī)則集合操作UNION

由每個查詢選擇的所有不重復的行，即

并集不包含重復值

UNIONALL

由每個查詢選擇的所有的行，包括所有

重復的行完全，即并集包含重復值

INTERSECT

由每個查詢選擇的所有不重復的相交行

即交集

MINUS

在第一個查詢中，不在后面查詢中，并

且結(jié)果行不重復，即差集ORDERBY子句用ORDERBY子句排序

–ASC:升序排序，默認

–DESC:降序排序語法:SELECTexprFROMtable[WHEREcondition(s)][ORDERBY{column,expr}[ASC|DESC]];注意：ORDERBY子句在查詢完成后執(zhí)行，如果使用了ORDERBY子句，它必須位于SQL語句的最后?？梢灾付ㄒ粋€表達式，或者一個別名，或者列序號作為排序條件。SQL函數(shù)函數(shù)是SQL的一個非常強有力的特性，函數(shù)能夠用于下面的目的：執(zhí)行數(shù)據(jù)計算修改單個數(shù)據(jù)項操縱輸出進行行分組格式化顯示的日期和數(shù)字轉(zhuǎn)換列數(shù)據(jù)類型SQL函數(shù)有輸入?yún)?shù)，并且總有一個返回值。SQL函數(shù)(續(xù))函數(shù)分為單行函數(shù)和多行函數(shù)單行函數(shù)

僅對單個行進行運算，并且每行返回一個結(jié)果。

我們主要討論以下幾種常用的函數(shù)類型：字符數(shù)字日期轉(zhuǎn)換多行函數(shù)

能夠操縱成組的行，每個行組給出一個結(jié)果，這些函數(shù)也被稱為組函數(shù)。單行函數(shù)單行函數(shù)：操縱數(shù)據(jù)項接受多個參數(shù)（列或者表達式），并返回一個值作用于每一個返回行每行返回一個結(jié)果可以修改數(shù)據(jù)類型可以嵌套單行函數(shù)(續(xù))常用的單行函數(shù)：字符函數(shù)：接受字符輸入，可以返回字符或者數(shù)字值數(shù)字函數(shù)：接受數(shù)字輸入，返回數(shù)字值日期函數(shù)：對DATE數(shù)據(jù)類型的值進行運算(除了MONTHS_BETWEEN函數(shù)返回一個數(shù)字，所有日期函數(shù)都返回一個DATE數(shù)據(jù)類型的值。)轉(zhuǎn)換函數(shù)：從一個數(shù)據(jù)類型轉(zhuǎn)換為另一個數(shù)據(jù)類型通用函數(shù)：

NVL、NVL2、NULLIF、COALSECE、CASE、DECODE字符函數(shù)字符函數(shù)可以被分為大小寫處理和字符處理兩種：大小寫處理函數(shù)如下：LOWER(column|expression) 轉(zhuǎn)換字符值為小寫 UPPER(column|expression) 轉(zhuǎn)換字符值為大寫 INITCAP(column|expression)轉(zhuǎn)換每個單詞的首字母值為大寫，所有其它值為小寫字符處理函數(shù)如下：

CONCAT(column1|expression1,column2|expression2)

連接第一個字符值到第二個字符值；等價于連接運算符(||)SUBSTR(column|expression,m[,n])

從字符值中返回指定的字符，開始位置在m，n字符長度(如果m是負數(shù)，計數(shù)從字符值末尾開始；如果n被忽略，返回到串結(jié)束的所有字符)。字符函數(shù)（續(xù)）LENGTH(column|expression)返回表達式中的字符數(shù)INSTR(column|expression,’string’,[,m],[n])

返回一個命名串的數(shù)字位置。隨意地，你可以提供一個位置m作為查找的開始，在字符串中第n次發(fā)現(xiàn)的位置。m和n的默認值是1，意味著在起始開始查找，并且報告第一個發(fā)現(xiàn)的位置。 LPAD(column|expression,n,'string')

RPAD(column|expression,n,'string')

填充字符值左、右調(diào)節(jié)到n字符位置的總寬度 TRIM([leading|trailing|both]trim_characterFROMtrim_source)

使你能夠從一個字符串修整頭或尾字符(或兩者)。如果trim_character或trim_source是字符文字，你必須放在單引號中。REPLACE(text,search_string,replacement_string)

從字符串查找一個文本表達式，如果找到，用指定的值串代替它字符函數(shù)(續(xù))

函數(shù)結(jié)果 CONCAT('Hello','World')HelloWorld SUBSTR('HelloWorld',1,5)Hello LENGTH('HelloWorld')10 INSTR('HelloWorld','W')6 LPAD(salary,10,'*')*****24000 RPAD(salary,10,'*')24000***** TRIM('H'FROM'HelloWorld')elloWorld數(shù)字函數(shù)ROUND(45.926,2)45.93TRUNC(45.926,2)45.92MOD(1600,300)100

ROUND(column|expression,n)

四舍五入列、表達式或值為n位小數(shù)位，如果n被忽略，無小數(shù)位。(如果n是負值，小數(shù)點左邊的數(shù)被四舍五入)。TRUNC(column|expression,n)

截斷列、表達式或值到n位小數(shù)，如果n被忽略，那么n默認為0。MOD(m,n)

返回m除以n的余數(shù)。常用于確定一個數(shù)是奇數(shù)還是偶數(shù)。日期的使用Oracle數(shù)據(jù)庫用內(nèi)部數(shù)字格式存儲日期：世紀，年，月，日，小時，分鐘和秒，其內(nèi)部存儲格式舉例如下： CENTURYYEARMONTHDAYHOURMINUTESECOND 201607159

2043默認日期顯示格式是DD-MON-RR.

SYSDATE函數(shù)返回：

?Date?TimeSYSDATE是一個日期函數(shù)，它返回當前數(shù)據(jù)庫服務(wù)器的日期和時間。

SELECTSYSDATEFROMDUAL;用日期計算既然數(shù)據(jù)庫以數(shù)字方式存儲日期，那么就可以用算術(shù)運算符進行計算，例如，加或減。日期可以加或減數(shù)字常數(shù)以及日期，如下示例：

運算 結(jié)果 說明 date+number

日期加一個天數(shù)到一個日期上date-number

日期從一個日期上減一個天數(shù)date-date

天數(shù)用一個日期減另一個日期date+number/24日期加一個小時數(shù)到一個日期上從日期加或者減一個數(shù)，結(jié)果是一個日期值兩個日期相減，得到兩個日期之間的天數(shù)用小時數(shù)除以24，可以加小時到日期上日期函數(shù)

函數(shù)說明 MONTHS_BETWEEN兩個日期之間的月數(shù) ADD_MONTHS加日歷月到日期 NEXT_DAY下個星期幾是幾號 LAST_DAY指定月的最后一天 ROUND四舍五入日期 TRUNC截斷日期日期函數(shù)(續(xù))MONTHS_BETWEEN(date1,date2)：計算date1和date2之間的月數(shù)，其結(jié)果可以是正的也可以是負的。如果date1大于date2，結(jié)果是正的，反之，結(jié)果是負的。結(jié)果的小數(shù)部分表示月的一部分。ADD_MONTHS(date,n)：添加n個日歷月到date。n的值必須是整數(shù)，但可以是負的。NEXT_DAY(date,‘char’)：計算在date之后的下一個周(‘char’)指定天的日期。char的值可能是一個表示一天的數(shù)或者是一個字符串。LAST_DAY(date)：計算包含date的月的最后一天的日期ROUND(date[,‘fmt’])：返回用格式化模式fmt四舍五入到指定單位的date，如果格式模式fmt被忽略，date被四舍五入到最近的天。TRUNC(date[,‘fmt’])：返回用格式化模式fmt截斷到指定單位的帶天的時間部分的date，如果格式模式fmt被忽略，date被截斷到最近的天。使用日期函數(shù)MONTHS_BETWEEN(‘01-SEP-15’,‘11-JAN-14’) 19.6774194ADD_MONTHS(‘11-JAN-16’,6) '11-JUL-16'NEXT_DAY(‘01-SEP-15’,‘3’)下個星期二是幾號 '08-SEP-15'LAST_DAY('01-FEB-16') '29-FEB-16'使用日期函數(shù)(續(xù))假定SYSDATE='25-JUL-95':ROUND(SYSDATE,'MONTH')01-AUG-95ROUND(SYSDATE,'YEAR')01-JAN-96TRUNC(SYSDATE,'MONTH')01-JUL-95TRUNC(SYSDATE,'YEAR')01-JAN-95TRUNC(TO_DATE('25-JUL-95'),'YEAR') 01-JAN-95轉(zhuǎn)換函數(shù)數(shù)據(jù)類型轉(zhuǎn)換隱式數(shù)據(jù)類型轉(zhuǎn)換顯式數(shù)據(jù)類型轉(zhuǎn)換對于直接賦值，Oracle服務(wù)器能夠自動地進行下面的轉(zhuǎn)換：

從

到

VARCHAR2orCHAR NUMBER VARCHAR2orCHAR DATE NUMBERVARCHAR2 DATEVARCHAR2對于表達式賦值，Oracle服務(wù)器能自動地進行下面的轉(zhuǎn)換：

從 VARCHAR2orCHAR

到 DATE

從 VARCHAR2orCHAR

到 NUMBER轉(zhuǎn)換函數(shù)(續(xù))顯式數(shù)據(jù)類型轉(zhuǎn)換SQL提供三種函數(shù)來從一種數(shù)據(jù)類型轉(zhuǎn)換值到另一種：

TO_CHAR(number|date[,fmt][,nlsparams])

轉(zhuǎn)換一個數(shù)字或日期值為一個VARCHAR2字符串，帶格式化樣式fmt。數(shù)字轉(zhuǎn)換：nlsparams參數(shù)指定下面的字符，它由數(shù)字格式化元素返回：小數(shù)字符 99999.99前導0 09999本地貨幣符號 L9999國際貨幣符號 $9999如果忽略nlsparams或其它參數(shù)，該函數(shù)在會話中使用默認參數(shù)值。轉(zhuǎn)換函數(shù)(續(xù))轉(zhuǎn)化字符串到數(shù)字TO_NUMBER(char[,'format_model'])轉(zhuǎn)化字符串到日期TO_DATE(char[,'format_model'])格式模板必須放在單引號中。常用日期格式的模板 YYYY/MM/DDHH24:MI:SSselectto_timestamp('2016-07-1615:13:28.45','yyyy-mm-ddhh24:mi:ss.ff')fromdual;通用函數(shù)以下函數(shù)可用于任意數(shù)據(jù)類型，并且適用于空值NVL(expr1,expr2)NVL2(expr1,expr2,expr3)NULLIF(expr1,expr2)COALESCE(expr1,expr2,...,exprn)NVL

轉(zhuǎn)換空值為一個實際值。NVL2

如果expr1非空，返回expr2；如果expr1為空，返回expr3。NULLIF比較兩個表達式，如果相等返回空；如果不相等，返回第一個表達式COALESCE返回表達式列表中的第一個非空表達式。條件表達式在SQL語句中提供IF-THEN-ELSE邏輯的使用。兩種用法：

–CASE表達式

–DECODE函數(shù)CASE表達式

CASEexpr

WHENcomparison_expr1THENreturn_expr1

WHENcomparison_expr2THENreturn_expr2

WHENcomparison_exprnTHENreturn_exprn

ELSEelse_expr]

ENDCASE使用示例 SELECTlast_name,job_id,salary, CASEsalary WHEN‘5000'THEN1.10*salary WHEN‘4000'THEN1.15*salary WHEN‘3500'THEN1.20*salary ELSEsalary

END"REVISED_SALARY"

FROMemployees;DECODE函數(shù) DECODE(col|expression,search1,result1

[,search2,result2,...,][,default]) DECODE函數(shù)在比較表達式(expression)和每個查找(search)值后解碼表達式，如果表達式與查找相同，返回結(jié)果。

如果省略默認值，當沒有查找值與表達式相匹配時返回一個空值。SELECTlast_name,job_id,salary,

DECODE(salary,

‘5000’,1.10*salary,

‘4000’,1.15*salary,

‘3500’,1.20*salary,

salary)

REVISED_SALARYFROMemployees;多表連接查詢連接表的方式：內(nèi)連接外連接（左外連接、右外鏈接）全連接（全外連接）內(nèi)連接(innerjoin)實際上就是等值連接，表中作為連接條件的列的值必須相等，返回2個表中完全符合條件的記錄。外連接（outerjoin）除了顯示匹配相等連接條件的數(shù)據(jù)外,還可以顯示基于某一個表中無法匹配相等連接條件的記錄。全連接（fulljoin）的查詢結(jié)果是左外連接和右外鏈接查詢結(jié)果的并集，即使是記錄關(guān)聯(lián)不上，也會把這部分信息查詢出來。

外連接語法外連接運算符是加號(+)SELECTt1.column,t2.columnFROMt1,t2WHEREt1.column(+)=t2.column;SELECTt1.column,t2.columnFROMt1,t2WHEREt1.column=t2.column(+);外連接可以從一個表中返回那些在另一個表中沒有直接匹配的行外連接操作符是一個在圓括號中的加號(+)，它被放置在連接的缺少信息的一側(cè)。為了使來自不完善表的一行或多行能夠被連接，該操作符有產(chǎn)生一個或多個空行的作用。組函數(shù)組函數(shù)不象單行函數(shù)，組函數(shù)對行的集合進行操作，對每組給出一個結(jié)果。這些集合可能是整個表或者是表分成的組。

以下為常用的組函數(shù)，每個函數(shù)接收一個參數(shù)：

函數(shù)

說明

AVG([DISTINCT|ALL]n)

n的平均值，忽略空值COUNT({*|[DISTINCT|ALL]expr})

計數(shù)，求除了空計算，*包括重

復和帶空值的行。MAX([DISTINCT|ALL]expr)

最大值，忽略空值MIN([DISTINCT|ALL]expr)

最小值，忽略空值STDDEV([DISTINCT|ALL]x)

n的標準差，忽略空值SUM([DISTINCT|ALL]n)

合計n的值，忽略空值VARIANCE([DISTINCT|ALL]x)

n的方差，忽略空值GROUPBY子句GROUPBY就是通過列將數(shù)據(jù)進行分組SELECTcolumn,group_function(column)FROMtable[WHEREcondition][GROUPBYgroup_by_expression][ORDERBYcolumn];在語法中，group_by_expression指定那些用于將行分組的列，這些列的值作為行分組的依據(jù)。使用WHERE子句，你可以在劃分行成組以前過濾行。在GROUPBY子句中必須包含列。在GROUPBY子句中你不能用列別名。默認情況下，行以包含在GROUPBY列表中的字段的升序排序。你可以用ORDERBY子句覆蓋這個默認值。HAVING子句用HAVING子句約束分組，執(zhí)行過程：如果語句有WHERE子句，服務(wù)器建立候選行行被分組應(yīng)用組函數(shù)匹配HAVING子句的組被顯示SELECTcolumn,group_functionFROMtable[WHEREcondition][GROUPBYgroup_by_expression][HAVINGgroup_condition][ORDERBYcolumn];數(shù)據(jù)庫對象表

基本存儲單元,由行和列組成視圖

邏輯地從一個或多個表中表示數(shù)據(jù)子集序列

數(shù)字值發(fā)生器索引

改善一些查詢的性能同義詞給對象可選擇的名字還有其他的數(shù)據(jù)庫對象，如存儲過程、觸發(fā)器、包、函數(shù)、約束等。命名規(guī)則表命名和列命名：?必須以字母開始?必須是1–30個字符長度?只能包含A–Z,a–z,0–9,_,$,和#?同一個用戶所擁有的對象之間不能重名?不能用Oracle服務(wù)器的保留字注意：默認情況下名字是大小寫不敏感的，例如，EMPLOYEES與eMPloyees或eMpLOYEES作為同一個名字來處理，oracle會自動轉(zhuǎn)成大寫。如果在創(chuàng)建時使用了“”，那么oracle就會區(qū)分大小寫。Oracle數(shù)據(jù)庫中的表?用戶表：

–由用戶創(chuàng)建和維護的表的集合，包含用戶信息?數(shù)據(jù)字典：

–由Oracle服務(wù)器創(chuàng)建和維護的表的集合，包含數(shù)據(jù)庫信息有四種數(shù)據(jù)字典視圖，每一種有一個特定的前綴來反映其不同的目的。USER_這些視圖包含關(guān)于用戶所擁有的對象的信息。 ALL_這些視圖包含所有用戶可訪問的表(對象表和相關(guān)的表)的信息。DBA_這些視圖是受限制的視圖，它們只能被分配有DBA角色的用戶所訪問。 V$ 這些視圖是動態(tài)執(zhí)行的視圖，包含數(shù)據(jù)庫服務(wù)器的性能、存儲器和鎖的信息。常用數(shù)據(jù)類型

數(shù)據(jù)類型說明VARCHAR2(size)

可變長度的字符數(shù)據(jù)（最大為4000字符）CHAR(size)

固定長度的字符數(shù)據(jù)（默認為1，最大為2000）NUMBER(p,s)

可變長度的數(shù)字數(shù)據(jù)DATE

日期和時間值TIMESTAMP(size) 時間戳，默認長度為6LONG

最大2G的可變長度字符數(shù)據(jù)CLOB

最大4G的字符數(shù)據(jù)RAWandLONGRAW

原始二進制數(shù)據(jù)BLOB

最大4G的二進制數(shù)據(jù)BFILE

最大4G的，存儲在外部文件中的二進制數(shù)據(jù)ROWID一個64進制的數(shù)制系統(tǒng)，表示表中一行的唯一

地址CREATETABLE語句?用戶必須有：

–CREATETABLE權(quán)限

–一個存儲區(qū)域CREATETABLE[schema.]table(columndatatype[DEFAULTexpr][,...]);?必須指定：

–表名

–列名、列數(shù)據(jù)類型和列的大小schema與所有者的名字一樣table表的名字DEFAULTexpr指定默認值column列的名字datatype列的數(shù)據(jù)類型和長度用子查詢創(chuàng)建表該方法既可以創(chuàng)建表還可以將從子查詢返回的行插入新創(chuàng)建的表中。CREATETABLEdept80[(column[,column...])]ASSELECTemployee_id,last_name,salary*12ANNSAL,hire_dateFROMemployeesWHEREdepartment_id=80;被創(chuàng)建的表要帶指定的列名，并且由SELECT語句返回的行被插入到新表中。字段的定義只能包括列名和默認值。如果給出了指定的列，列的數(shù)目必須等于子查詢中列表的列數(shù)目。如果沒有給出了指定的列，表的列名應(yīng)和子查詢中的列名相同。完整性規(guī)則不會被傳遞到新表中，僅列的數(shù)據(jù)類型被定義。ALTERTABLE語句用ALTERTABLE語句可以：?添加一個新列?修改一個已存在的列?為新列定義一個默認值?刪除一個列添加列ALTERTABLEtableADD

(columndatatype[DEFAULTexpr][,columndatatype]...);修改列ALTERTABLEtableMODIFY

(columndatatype[DEFAULTexpr][,columndatatype]...);刪除列ALTERTABLEtableDROP(column);修改列可以改變列的數(shù)據(jù)類型、大小和默認值A(chǔ)LTERTABLEdept80MODIFY(last_nameVARCHAR2(30));對默認值的改變只影響后來插入表中的數(shù)據(jù)原則可以增加寬度或一個數(shù)字列的精度?？梢栽黾訑?shù)字列或字符列的寬度?？梢詼p少一個列的寬度，但僅在列中只包含空值或表中沒有行時。可以改變數(shù)據(jù)類型，但僅在列中只包含空值時。CHAR列與VARCHAR2數(shù)據(jù)類型可以相互轉(zhuǎn)換，但僅當列中只包含空值時，或者不改變列的大小時。對默認值的改變僅影響以后插入的列。刪除表注意：在表中的所有數(shù)據(jù)和結(jié)構(gòu)都被刪除任何未決的事務(wù)都被提交所有的索引被刪除你不能回退DROPTABLE語句子查詢SELECTselect_listFROMtableWHEREexproperator

(SELECTselect_listFROMtable);子查詢放在圓括號中，在主查詢之前執(zhí)行一次將子查詢放在比較條件的右邊子查詢的結(jié)果被用于主查詢(外查詢)在子查詢中的ORDERBY子句一般不需要子查詢可以被放在CREATEVIEW語句中、CREATETABLE語句、UPDATE語句、INSERT語句的INTO子句和UPDATE語句的SET子句中。子查詢類型單行子查詢：從內(nèi)SELECT語句只返回一行的查詢多行子查詢：從內(nèi)SELECT語句返回多行的查詢單行子查詢僅返回一行，使用單行比較符

運算符含義

=等于

>大于

>=大于或等于

<小于

<=小于或等于

<>不等于多行子查詢返回多于一行使用多行比較符

操作含義

IN等于列表中的任何成員

ANY比較子查詢返回的每個值

ALL比較子查詢返回的全部值<ANY意思是小于最大值，>ANY意思是大于最小值，=ANY等同于IN。<ALL意思是小于最小值，>ALL意思是大于最大值。處理數(shù)據(jù)DML-數(shù)據(jù)操縱語言（

datamanipulationlanguage）是SQL的一個核心部分，用于操作數(shù)據(jù)庫中的數(shù)據(jù)，DML語言不隱含COMMIT語句，因此需要用戶手動提交對數(shù)據(jù)庫的修改。當你做下面操作時，DML語句被執(zhí)行：–添加新行到表中（增）

–刪除表中的行（刪）

–修改表中的行（改）事務(wù)：由一系列的DML語句組成，成為一個邏輯工作單元。事務(wù)中的語句必須全部成功，否則必須全部回退。

DML語言包括：INSERTUPDATEDELETESELECT（DMLorDQL?）MERGECALLEXPLAINPLANLOCKTABLEINSERT語句語法使用INSERT語句添加新行到表中 INSERTINTOtable[(column[,column...])] VALUES(value[,value...]);

在語法中：table是表的名字column是表中的列名value是列的相應(yīng)值用該語法一次只能插入一行插入新行插入包含每一個列值的新行值以表中列的默認順序列表在INSERT子句中字段可以隨意列表

INSERTINTOdepartments(department_id,department_name,manager_id,location_id)VALUES(70,'PublicRelations',100,1700);字符和日期要用單引號括起來插入帶空值的行隱式方法:省略字段列表中的列INSERTINTOdepartments(department_id,epartment_name)VALUES(30,'Purchasing');顯式方法:在VALUES子句中指定NULL關(guān)鍵字INSERTINTOdepartments

(department_id,department_name,manager_id,location_id)VALUES(100,'Finance',NULL,NULL);插入特殊的值SYSDATE函數(shù)取出當前的日期時間insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7196,'GREEN','SALESMAN',7782,SYSDATE,2000,null,10);TO_DATE函數(shù)可作日期轉(zhuǎn)換，插入特定的日期時間的值

insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7196,'GREEN','SALESMAN',7782,

TO_DATE('2016-06-0108:30:20',‘YYYY-MM-DDHH24:MI:SS')

,

2000,null,10);從另一個表中復制行創(chuàng)建帶有子查詢的INSERT語句INSERTINTOsales_reps(id,name,salary,commission_pct)SELECTemployee_id,last_name,salary,commission_pctFROMemployeesWHEREjob_idLIKE'%REP%';不要使用VALUES子句在子查詢中列的數(shù)目要匹配INSERT子句中列的數(shù)目UPDATE語句使用UPDATE語句更改現(xiàn)有的行UPDATEtableSETcolumn=value[,column=value,...][WHEREcondition];

table是表的名字column是表中列的名字value是相應(yīng)的值或?qū)?yīng)列的子查詢condition是where條件的內(nèi)容，以此確定要被更新的行。用查詢表來顯示受更新的行以確認更新操作。如果需要，一次更新多行更新表中的行如果使用了WHERE子句，指定的一行或多行將被修改UPDATEemployees

SETdepartment_id=70WHEREemployee_id=113;如果遺漏WHERE子句，表中所有的行都會被修改UPDATEcopy_empSETdepartment_id=110;用子查詢更新多個列更改7698號雇員的工作和部門，使其與7499號雇員的相同

UPDATEemp

SET(job,deptno)=

(selectjob,deptnofromemp

whereempno=7499)

WHEREempno=7698;對基于另一個表的行進行更改在UPDATE語句中，用子查詢來更新基于另一個表中值的那些行。UPDATEcopy_empSETdepartment_id=(SELECTdepartment_id

FROMemployees

WHEREemployee_id=100)WHEREjob_id=any(SELECTjob_id

FROMemployees

WHEREemployee_id=200);用基于來自EMPLOYEES表的值更新COPY_EMP表，它用employee100的部門號改變所有工作崗位與employee200的工作崗位相同的那些雇員的部門號DELETE語句使用DELETE語句從表中刪除已存在的行?如果指定了WHERE子句，則刪除指定的行DELETEFROMdepartmentsWHEREdepartment_name='Finance';?如果遺漏了WHERE子句，表中所有的行都被刪除DELETEFROMcopy_emp;刪除基于另一個表的行在DELETE語句中用子查詢來刪除表中的基于另一個表中值的行DELETEFROMemployeeWHEREdeptno=

(SELECTdeptnofromdeptwheredname='SALES');TRUNCATETRUNCATEtablecopy_emp;

DELETEFROMcopy_emp;清空表中所有數(shù)據(jù)刪除數(shù)據(jù)的效率明顯比DELETE高恢復數(shù)據(jù)需要備份，代價高基本SQLMERGE合并語句按照指定的條件執(zhí)行插入或刪除操作如果滿足條件的行存在，執(zhí)行更新操作；否則執(zhí)行插入操作:避免多次重復執(zhí)行插入和刪除操作提高效率而且使用方便在數(shù)據(jù)倉庫應(yīng)用中經(jīng)常使用合并語句的語法可以使用merge語句，根據(jù)指定的條件進行插入或更新操作MERGEINTOtable_name

table_aliasUSING(table|view|sub_query)aliasON(joincondition)WHENMATCHEDTHENUPDATESET

col1=col_val1,col2=col2_valWHENNOTMATCHEDTHENINSERT(column_list)VALUES(column_values);MERGEINTOcopy_empcUSINGemployeeseON(c.employee_id=e.employee_id)WHENMATCHEDTHENUPDATESETc.first_name=e.first_name,c.last_name=e.last_name,...c.department_id=e.department_idWHENNOTMATCHEDTHENINSERTVALUES(e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,e.hire_date,e.job_id,e.salary,mission_pct,e.manager_id,e.department_id);合并語句舉例在對表COPY_EMP使用merge語句，根據(jù)指定的條件從表EMPLOYEES中插入或更新數(shù)據(jù)。數(shù)據(jù)庫事務(wù)Oracle數(shù)據(jù)庫基于事務(wù)處理確保數(shù)據(jù)的一致性。要么全部完成，要么全部廢棄的操作集合。數(shù)據(jù)庫事務(wù)可以由下面的語句組成：?對數(shù)據(jù)做出一致性修改的DML語句?一個DDL語句?一個DCL語句事務(wù)的開始與結(jié)束開始于第一個被執(zhí)行的DML語句遇到下面事件之一結(jié)束：

COMMIT或ROLLBACK語句被運行

DDL或DCL語句被執(zhí)行(自動提交)

用戶正常退出（自動提交）

用戶非正常退出（自動回滾）

系統(tǒng)異常終止（自動回滾）COMMIT和ROLLBACK語句COMMIT--提交，簡單來說就是結(jié)束當前事務(wù)，把所做的修改保存到數(shù)據(jù)庫ROLLBACL

--回滾，結(jié)束當前事務(wù)，丟棄所有未決的數(shù)據(jù)改變用COMMIT和ROLLBACK語句，你能夠：確保數(shù)據(jù)的一致性在數(shù)據(jù)永久改變之前進行預覽分組邏輯相關(guān)的操作控制事務(wù)使用COMMIT、SAVEPOINT和ROLLBACK語句控制事務(wù)邏輯。SAVEPOINTname

在當前事務(wù)中標記保存點

ROLLBACKTOSAVEPOINTname

回滾當前事務(wù)到指定的保存點，從而丟棄保存點創(chuàng)建后的任何改變。如果忽略了TOSAVEPOINT子句，ROLLBACK語句回滾整個事務(wù)。由于保存點是邏輯的，因此，沒有辦法列出已經(jīng)創(chuàng)建的保存點。舉個例子用SAVEPOINT語句在當前事務(wù)中創(chuàng)建一個標記用ROLLBACKTOSAVEPOINT語句回退到該標記UPDATE...SAVEPOINTupdate_done;savepointcreated.INSERT...ROLLBACKTOupdate_done;Rollbackcomplete.隱式事務(wù)處理在下面的情況下，一個自動提交發(fā)生：

–DDL語句被發(fā)送

–DCL語句被發(fā)送

–正常退出iSQL*Plus，沒有明確地發(fā)送COMMIT或ROLLBACK語句當iSQL*Plus非正常退出時，或者發(fā)生系統(tǒng)故障時，一個自動回退發(fā)生COMMIT或ROLLBACK之前數(shù)據(jù)的狀態(tài)以前的數(shù)據(jù)狀態(tài)能夠被恢復當前用戶能用SELECT語句查看DML操作的結(jié)果其他用戶不能觀察當前用戶DML語句的結(jié)果受影響的行被鎖定，其他用戶不能改變受影響的行中數(shù)據(jù)COMMIT之后數(shù)據(jù)的狀態(tài)數(shù)據(jù)在數(shù)據(jù)庫中被永久地改變數(shù)據(jù)的以前狀態(tài)被永久地丟失所有用戶都能觀察該結(jié)果受影響行的鎖定被釋放，其它用戶可以操縱那些行所有保存點被擦除ROLLBACK之后的數(shù)據(jù)狀態(tài)用ROLLBACK語句丟棄所有未決的改變：數(shù)據(jù)的改變被撤消數(shù)據(jù)的以前狀態(tài)被恢復受影響行的鎖定被釋放讀一致性讀一致性確保在任意時間點，數(shù)據(jù)庫都能提供數(shù)據(jù)的一致性視圖讀一致性要求對于同一份數(shù)據(jù)：查詢不需要等待修改修改不需要等待查詢修改需要等待修改視圖視圖是基于表或另一個視圖的邏輯表，一個視圖并不包含它自己的數(shù)據(jù)，它象一個窗口，通過該窗口可以查看或改變表中的數(shù)據(jù)。視圖基于其上的表稱為基表。視圖在數(shù)據(jù)字典中作為一個SELECT語句存儲。視圖的作用：?限制數(shù)據(jù)訪問?使得復雜的查詢?nèi)菀?提供數(shù)據(jù)的獨立性?表現(xiàn)相同數(shù)據(jù)的不同觀察簡單視圖和復雜視圖下面是簡單視圖：數(shù)據(jù)僅來自一個表不包含函數(shù)或數(shù)據(jù)分組能通過視圖執(zhí)行DML操作下面是復雜視圖：數(shù)據(jù)來自多個表包含函數(shù)或數(shù)據(jù)分組不總是允許通過視圖進行DML操作Top-N分析?Top-N查詢尋找一列的n個最大或最小值，例如：

–銷售最好的前10位產(chǎn)品是什么?–銷售最差的前10位產(chǎn)品是什么??最大值和最小值在Top-N查詢中設(shè)置?Top-N查詢在需要基于一個條件，從表中顯示最前面的n條記錄或最后面的n條記錄時是有用的。執(zhí)行Top-N分析Top-N分析查詢的高級結(jié)構(gòu)是：SELECT[column_list],ROWNUMFROM(SELECT[column_list]FROMtableORDERBYTop-N_column)WHEREROWNUM<=N;Top-N分析的例子為了從EMPLOYEES表中顯示掙錢最多的3個人的名字及其薪水：SELECTROWNUMasRANK,last_name,salaryFROM(SELECTlast_name,salaryFROMemployeesORDERBYsalaryDESC)WHEREROWNUM<=3;序列是自動產(chǎn)生的唯一的數(shù)，可被多個用戶共享以產(chǎn)生唯一的整數(shù)典型的用途是創(chuàng)建一個主鍵值可以代替應(yīng)用程序編號，是一個節(jié)省時間的對象序列號獨立于表被存儲和產(chǎn)生，因此，相同的序列可以被多個表使用當使用高速緩存存儲器時，訪問序列值的效率提高。CREATESEQUENCE語法定義一個序列來自動產(chǎn)生有順序的數(shù)：CREATESEQUENCEsequence

[INCREMENTBYn][STARTWITHn][{MAXVALUEn|NOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOC