Oracle 學習筆記課件

Oracle筆記

曲Oracle部分

第一章數據庫介紹3

第二章Oracle簡介4

第三章用戶、權限6

第四章Oracle數據類型8

第五章SQL語句概述9

第六章表空間11

第七章表12

第八章函數14

第九章約束17

第十章單查詢查詢22

第十一章多表查詢-內連接23

第十二章多表查詢-外連接25

第十三章多表查詢-差不多26

第十四章多表查詢-連接29

第十五章子查詢32

第十七章組函數及分組統(tǒng)計34

第十八章數據庫對象-視圖37

第十九章數據庫對象-序列40

第二十章數據庫對象-同義詞43

第二十一章數據庫對象-索引44

第二十二章網絡配置47

第二十三章嵌套表、可變數組48

曲PL/SQL部分

第一章PL/SQL簡介51

第二章PL/SQL數據類型54

第三章LP/SQL操縱語句58

第四章動態(tài)SQL63

第五章錯誤處理64

第六章游標-隱式游標66

第七章游標-顯式游標68

第八章游標-REF游標72

第九章子程序-過程74

第十章子程序-函數77

第H-一章自主事物處理80

第十二章程序包82

第十三章觸發(fā)器講解85

第十四章觸發(fā)器實例89

第十五章數據庫設計和三大范式92

第十六章數據庫的備份與復原簡介97

第十七章導入導出工具99

第十八章數據庫歸檔方式102

能JDBC部分

JDBC部分104

第一章jdbc簡介104

第二章連接數據庫106

第三章常用數據庫的驅動程序和JDBCURL108

第四章連接池109

第五章數據操作-創(chuàng)建表111

第六章數據操作-查詢112

第七章預處理113

第八章批處理114

第九章數據的數據115

第十章調用函數116

第十一章調用過程119

第十二章DAO封裝122

第一章數據庫介紹

一、數據的儲存方法：

第一種方法：用大腦來記住數據

第二種方法：寫在紙上

第三種方法：寫在運算機的內存中

第四種方法:寫成磁盤文件

二、數據庫能做什么？

1.儲備大量數據，方便檢索和訪問

2.保持數據的一致、完整

3.共享和安全

4.通過組合分析，產生新的有用信息

三、數據庫的進展史

萌芽時期-文件系統(tǒng)

★使用磁盤文件儲存數據

初級時期-第一代數據庫

★顯現了網狀模型、層次模型的數據庫

中級時期-第一代數據數據庫

★關系型數據庫和結構化查詢語言

高級時期-新一代數據庫

★”關系-對象"型數據庫

四、當前的數據庫產品

Oracle——甲骨文

BD2IBM

SQLServer——微軟

Sybase賽貝思

MySqlSUN

五、數據庫和應用程序

向數據庫清求數據、存儲數據

并顯示結果

六、數據庫相關的差不多概念

概念模型：基于客戶的方法和觀點所形成的認識和對象

實體(Entiy):客觀存在的、能夠被描述的事物。如職員、部門

屬性(Attribute):用于描述實體所具有的特點或特性，如使用編號、姓名、部門、工資等屬性

職員的特點。

關系(Relationship):實體之間的聯(lián)系。如部門和職員之間有一對多的關系。

數據模型：也叫關系模型，是實體、屬性、關系在數據庫中的具體表現。

關系數據庫：用于儲存各種類型的''倉庫"，是二維表的集合

表：實體的映射

行和列：行代表一個具體的實體數據。也叫一條記錄。列是屬性的映射，用于描述實體的

主鍵和外鍵

七、數據庫治理系統(tǒng)(DBMS)

數據庫產品

檢索數據插入數據一更新數據刪除數據

數據庫管理系統(tǒng)-DBMS

第二章Oracle簡介

快速把握Oracle

課程目標：

?Oracle安裝及配置

?有關數據庫的DDL操作

?有關數據表的DDL操作

?有關數據表的CRUD操作

?事物操縱

?索引視圖

?儲備過程

?觸發(fā)器

?權限治理

?數據庫的備份與復原

?數據庫設計

Oracle是一個生產中間件和數據庫的較大生產商，Oracel的原本含義是''神喻"，指的是神

說的話。在中國的商朝的時代，把一些刻在龜殼上的文字當成了上天的指示，因此在中國也

將Oracle翻譯成''甲骨文"。

Oracle的進展實際上依靠了IMB公司。

Oracle的創(chuàng)始人是：LarryEllision倉|辦了Oracle公司。

Oracle的版本分為：

Oracle8

Oracle8i:Internet表示現在Oracle公司開始開始正式進軍互聯(lián)網.

Oracle9i:Oracle8i和Oracle9i相比是專門相似的

Oracle10g:g表示網格技術

網格技術：如我們在百度上下載一個軟件，那么那個軟件在離我們的遠處有一個，在我們的

近處也有一個，有可能我們通過搜索引擎下載的是遠的那個。忽略了近處的資源，如此就造

成了資源的白費。因此就產生了網格技術。確實是將網絡劃為了多個小格。通過網絡表示區(qū)

域。

OOracle是由甲骨文公司生產的以高級結構化查詢語言(SQL)為基礎的大型關系數據庫，通

俗地講它是用方便邏輯治理的語言操縱大量有規(guī)律數據的集合。是目前最流行的客戶/服務

器(CLIENT/SERVER)體系結構的數據庫之一。

?是目前市場占用率極高的一款數據庫產品

特點：

?提供了基于角色(ROLE)分工的安全保密治理。在數據庫治理功能、完整性檢查、安全性、

一致性方面都有良好的表現。

?支持大量多媒體數據，如二進制圖形、聲音、動畫以及多維數據結構等。

?提供了與第三代高級語言的接口

?提供了新的分布式數據庫能力?？赏ㄟ^網絡較方便地讀寫遠端數據庫里的數據，并有對稱

復制的技術。

一、儲備結構：

物理結構：

ORACLE數據庫在物理上是儲備于硬盤的各種文件。它是活動的，可擴充的，隨著數據的

添加和應用程序的增大而變化。

邏輯結構：

ORACLE數據庫在邏輯上是由許多表空間構成。要緊分為系統(tǒng)表空間和非系統(tǒng)表空間。非

系統(tǒng)表空間內儲備著各項應用的數據、索引、程序等相關信息。

二、啟動Oracle

在Window平臺下必須啟動的Oracle服務有：

1.OracleServiceSID

數據庫服務，那個服務會自動地啟動和停止數據庫。假如安裝了一個數據庫，它的缺省啟動

類型為自動。服務進程為ORACLE.EXE.

2.OracleHOME_NAMETNSListener

監(jiān)聽器服務。

3.sqlplus工具

sqlplus工具

登錄數據庫有以下幾種方式：

(1).sqlplus:以命令的方式進入數據庫連接

(2).sqlplusw：以窗口的形式啟動命令行

在使用此命令時，會提示一個主機的字符串，假如一臺電腦上有多個數據庫的話，那么要在

此處輸入數據庫的名稱，假如不輸入，會進行默認的，一樣默認的是最后一個數據庫。

那么登錄帳戶以后，就能夠在數據庫中進行增、冊h改、查等操作。

如我們能夠查看表：SELECT*FROMemp;(emp表是數據庫自帶的表)

當我們對表進行查看時，有時候顯示的表并不規(guī)范，如本來是一行的內容，但是有一部分被

補到了下一行。如此看下來表就專門紛亂，不規(guī)范。因此我們要對其環(huán)境進行一下設置。

如設置每行顯示的長度：setlinesize300;

有時候標題行還會重復顯現，在這因為在Oracle中數據是一頁一頁的顯示方式進行輸出的。

所我們可修改每頁顯示行數。

如setpagesize20;

4.命令

我們通常使用的sqlplusw

在sqlplusw中存在著大量的命令。

在sqlplusw下編輯代碼時，顯現了錯誤，不承諾我們使用向左方向鍵向右移動到相應的位

置上進行修改，專門不方便，因此通常我們使用記事本進行代碼的編輯，直截了當在命令行

中輸入、'ed文件名名稱即可"。如edtest,輸入之后會提示找不到test.sql文件，要創(chuàng)建新

文件嗎？我們選擇''是〃，那么就創(chuàng)建了一個test文件，我們就能夠在test文本中寫相應的

代碼。創(chuàng)建完成之后，能夠通過@文件名稱的方式執(zhí)行命令。如@1€$3就會執(zhí)行在test中

寫的代碼。

除了在sqlplus中創(chuàng)建文件之外，也能夠通過@符找到磁盤上的文件，如我們在D盤上建立

一個demo.txt的文件，里邊寫上查詢指令。執(zhí)行的時候，要指定文件的路徑：@路徑，如

@D:\demo.txt,也會執(zhí)行demo中的指令。成效也完全一樣的。

、'\"能夠省略。如@口也010其士假如文件的后綴是.sql,那么不寫后綴名稱也能夠找到。如：

@D:demoo因此默認找到的后綴是、'*.sql"。

在sqlplusw中能夠使用''/〃表示重復執(zhí)行上一句命令的操作。

第三章用戶、權限

一、用SQLPLUS登錄Oracle

1.Sqlplus用戶/密碼［as身份］

如登錄系統(tǒng)帳戶：connsys/systemassysdba;

conn:連接到數據庫的關鍵字

sys:系統(tǒng)用戶名

system:是驗證密碼

assysdba:是身份驗證

2.Oracle內置帳戶

sys具有最大的權限。

Oracle數據庫服務器啟動后，一樣至少有以下幾個用戶:

Internal,它不是一個真實的用戶名，而是具有SYSDBA優(yōu)先級的Sys用戶的別名，它由DBA

用戶使用來完成數據庫的治理任務，包括啟動和關閉數據庫；

sys:它是一個DBA用戶名，具有最大的數據庫操作權限；

system:它也是一個DBA用戶名，權限僅次于Sys用戶

scott:它是一個oracle例如/學習帳戶

3.停止和啟動Oracle

啟動/停止windows服務

Sqlplus/nolog利用那個命令能夠在DOS下不利用任何身份進入到SQL的狀態(tài)。之后再利

用身份登錄

Connect/assysdba

Shutdown/startup

/是以操作系統(tǒng)認證進行登錄

Nolog不創(chuàng)建初始聯(lián)接

4.創(chuàng)建、刪除用戶

△創(chuàng)建帳戶：

CREATEUSERusernameIDENTIFIEDBYpassword

[DEFAULTTABLESPACEtablespace1]——>默認表空間

[TEMPORARYTABLESPACEtablespace2]——>臨時表空間

[QUOTAnKONtablespacel];——>不足時自動增加nK或nM

M

UNLIMITED>沒有限制

如創(chuàng)建一個hellen的帳戶：createuserhellenidentifiedbyabed;

△刪除帳戶：

差不多語法：DROPUSERuserName[cascade];

假如加上cascade關鍵字可刪除該用戶所創(chuàng)建的對象。

如：刪除用戶hellen:dropuserhellen;

△用戶修改密碼：

差不多語法：ALTERUSERuserNameIDENTIFIEDBYpassword;

如：alteruserhellenidentifiedbyabcl23;

△用戶解鎖：

差不多語法：ALTERUSERuserNameACCOUNTUNLOCK;

如：alteruserhellenaccountunlock;

△查看當前登錄用戶

SHOWUSER;

△查看系統(tǒng)有哪些用戶：

SELECTUSERNAMEFROMDBA_USERS;

如：

第一步：descdba_users;

第二步：selectusername,USERJD,ACCOUNT_STATUSFROMDBA_USERS;

△查看用戶的默認表空間

selectusername,default_tablespacefromdba_users;

△查看用戶有哪些表空間

selectdistincttablespace_namefromdbatableswhereowner='USER';

△確定用戶帳戶所授予的權限

select*fromDBA_tab_privs;直截了當授予用戶帳戶的對象權限select*from

DBA_role_privs;授予用戶帳戶的角色select*fromDBA_sys_privsWHEREGRANTEE=?;

授予用戶帳戶的系統(tǒng)權限

5.授于用戶連接Oracle數據庫的權限

格式：grant權限to帳戶名。

如授予能夠連接到數據庫的權限：granthellentoscott;

常用的權限有：

connect(8)連上Oracle,做最差不多操作

resource(5)具有程序開發(fā)最差不多的權限

dba(77)數據庫治理員所有權限

exp-full-database可把數據庫整個備份輸出的權限

imp-full-datsabase可把數據庫整個備份復原輸入的權限

6.回收權限

差不多語法：

REVOKE權限FROM用戶名；

REVOKE實體權限|ALLON表空間FROM用戶名|角色名［PUBLIC;

如授回連接到數據庫的權限：revokeconnectfromhellen;

常見的實體權限：見附錄

第四章Oracle數據類型

Oracle提供了22種不同的SQL數據類型供我們使用：

★CHAR：這是一個定長字符串，會用空格填充來達到其最大長度。非null的CHARQ2.)

總是包含12字節(jié)信息。CHAR字段最多能夠儲備2,000字節(jié)的信息。

★NCHAR：這是一個包含UNICODE格式數據的定長字符串。最多能夠儲備2,000字節(jié)

的信息。

★VARCHAR2：這是一個變長字符串，與CHAR類型不同，它可不能用空格填充至最大

長度。VARCHAR2Q2)可能包含0?12字節(jié)的信息。VARCHAR2最多能夠儲備4,000字節(jié)

的信息。

★NVARCHAR2：這是一個包含UNICODE格式數據的變長字符串。NVARCHAR2(12)能

夠包含0?12字符的信息。NVARCHAR2最多能夠儲備4,000字節(jié)的信息。

★NUMBER：這種數據類型能儲備精度最多達38位的數字。這些數介于12X12(-130)-1

12X12(126)之間。

用法:number(p,s);p和s是能夠選的，用于表示整數部分和小數部分的精度

★BINARY_FLOAT：這是Oracle10gRelease1及以后版本中才有的一種新類型。它是一

個32位單精度浮點數，能夠支持至少6位精度，占用磁盤上5字節(jié)的儲備空間。

★BINARY_DOUBLE:這也是10g中新的一種類型

★CLOB：在Oracle分及往常的版本中，這種數據類型承諾儲備最多4GB的數據，在Oracle

10g及以后的版本中承諾儲備最多14GB)X(數據庫塊大小)字節(jié)的數據。這種數據類型

專門適合儲備純文本信息。

★BLOB：在Oracle為及往常的版本中，這種數據類型承諾儲備最多4GB的數據，在Oracle

10g及以后的版本中承諾儲備最多14GB)X(數據庫塊大小)字節(jié)的數據。適合于儲備圖

片/文檔

★LONG：這種類型能儲備最多2G的字符數據--建議使用CLOB代替

★DATE：這是一個7字節(jié)的定寬日期/時刻數據類型。其中總包含7個屬性，包括：世紀、

世紀中哪一年、月份、月中的哪一天、小時、分鐘和秒。

★TIMESTAMP：這是一個7字節(jié)或12.字節(jié)的定寬日期/時刻數據類型。它與DATE數據

類型不同，因為TIMESTAMP能夠包含小數秒(fractionalsecond)；帶小數秒的TIMESTAMP

在小數點右邊最多能夠保留9位。

★TIMESTAMPWITHTIMEZONE：與前一種類型類似，這是一個12.字節(jié)的定寬

TIMESTAMP,只是它還提供了時區(qū)(TIMEZONE)支持。數據中會隨TIMESTAMP儲備有

關時區(qū)的額外信息，因此原先插入的TIMEZONE會與數據一同保留。

★ROWID：ROWID實際上是數據庫中一行的12字節(jié)地址。ROWID中編碼有足夠的信

息，足以在磁盤上定位這一行，以及標識ROWID指向的對象。

(l)Oracle中偽列就像一個表列，然而它并沒有儲備在表中

(2)偽列能夠從表中查詢，但不能插入、更新和刪除它們的值

(3)常用的偽列有ROWID和ROWNUM

ROWID是表中行的儲備地址，該地址能夠唯獨地標識數據庫中的一行，能夠使用

ROWID偽列快速地定位表中的一行。

ROWNUM是查詢返回的結果集中行的序號，能夠使用它來限制查詢返回的行數

例：

列的類型要緊有如下幾種：

NUMBER(4):表示是數字，長度為4

VARCAHR2Q0):表示的是字符串，只能10的個的長度

DATE:表示日期

NUMBER(7,2):表示是數字，其中小數位占2位，整數位占5位，總共是7位。

第五章SQL語句概述

SQL語句概述

OSQL結構化查詢語言(StructuredQueryLanguage)

一樣讀作：［si:kju:］或者是字母SQL的發(fā)音。

O目前數據庫廠商實現的差不多上SQL92標準，還沒有任何一家廠商通過SQL99標準認證

OOracle對SQL92做了擴展,因此稱自己為加強版SQL(SQLPLUS)□

關于不同的數據庫來講，重點差不多上把握SQL語句，因為現在的數據庫全部是以SQL操

作的標準，在實際中，各個數據庫確實是提供的函數不同。

SQL語言要緊用于與數據庫的通訊。SQL語言功能強大,要緊分為以下同種：DMLDDL

DCL事物操縱語言。

一、SQL語句分類

1.DDL(DataDefinitionLanguage)數據定義語言:定義數據庫對象(表空間，表，歹!J,索引等)

如：CREATE,DROP,ALTER,TRUNCATE等

2.DML(DataManipulationLanguage)數據操縱語言:完成對數據記錄的操作

如：INSERT,DELETE,UPDATE,SELECT等。

3.DCL(DataControlLanguage)數據操縱語言:定義用戶的訪問權限和安全級別

如：GRANT,REVOKE

4.事物操縱(TransactionControl)事物操縱:如：COMMIT,ROLLBACK

(1)事務是最小的工作單元，作為一個整體進行工作

(2)保證事務的整體成功或失敗，稱為事務操縱

用于事務操縱的語句有：

(4)C0MMIT-提交并終止事務處理

當向表插入一個新值的時候，該事物并沒有被永久的寫到磁盤上去，重新打開窗口再次查

詢該表中的數據時，發(fā)覺并沒有剛才插入的記錄，這是因為那個事物還沒有終止，當遇到

commit或rollback才認為是終止了。

假如要永久性的提交能夠執(zhí)行：commit命令，再次打開新的窗口時該記錄已被寫到表中了。

(5)ROLLBACK-撤銷事務中已完成的工作

當我們做了和系列的操作以后，都沒有執(zhí)行commit命令，也確實是沒有提交，我們執(zhí)行了

rollback就可要回到原點了，也確實是剛才所做的都等于沒做，所有rollback回退是將所有

的回退。

(6)SAVEPOINT-標記事務中能夠回滾的點

因為rollback回退是將所有的都回退了，這明顯不太好，那么我們能夠設置幾個回退點，使

再次回退的時候，不讓其回退到原點，而是回退到我們固定的位置上去。

如：

UPDATE表名setid=2WHEREid=3——＞將id=3改為id=2

SAVEPOINTmarkl——＞設置一個還原點markl

DELETEFROM表名WHEREid=5一一＞冊|除id=5

SAVEPOINTmark2;--一＞再設置一個還原點mark2

二、Oracle支持的SQL操作符的分類：算術操作符比較操作符邏輯操作符集合操作符連

接操作符

1.算術操作符：

算術操作符用于執(zhí)行數值運算

能夠在SQL語句中使用算術表達式，算術表達式由數值數據類型的列名、數值常量和連接

它們的算術操作符組成

算術操作符包括加(+)、減(-)、乘(*)、除⑺。

2.連接操作符用于將多個字符串或數據值合并成一個字符串

例：要求查出雇員的編號、姓名、工作然而顯示的格式是：

編號是：7369的雇員，姓名是：SMITH,工作是:CLERK

SELECT編號是：［empno『的雇員，姓名是：Rename(工作是30比一一＞使用了連接操作符

在查詢中也能夠使用四那么運算功能。如我們要查每個雇員的姓名及年薪。

SELECTename,sal*12FROMemp;-一＞月薪*12表示年薪——＞使用了算術操作符

3.比較操作符用于比較兩個表達式的值

比較操作符包括=、！=、＜、＞、＜=、＞=、BETWEEN—AND,IN、LIKE和ISNULL等

例：

SQL＞SELECTitemdesc,re_level

FROMitemfile

WHEREqty_hand＜max_level/2;

SQL＞SELECTordernoFROMorder_master

WHEREdel_dateIN('06-1月-05','05-2月-05');

SQL＞SELECTvencode,venname,tel_no

FROMvendormaster

WHEREvennameLIKE*j___s';

4.邏輯操作符

邏輯操作符用于組合多個計較運算的結果以生成一個或真或假的結果。

邏輯操作符包括與(AND)、或(OR)和非(NOT)。

例:顯示2005-5月-10至2005-5月-26的訂單信息

SQL>SELECT*FROMorder_master

WHEREodate>'10-5月-05,

ANDdel_date<'26-5月-05';

5.集合操作符將兩個查詢的結果組合成一個結果，集合操作符有：UNION,UNIONALL,

INTERSECT>MINUS

(l)UNION:將兩個表中的所有的記錄合到一起，但重復的只合一遍

如：用法有兩個表(id和name)

SELECT*FROM表A

UNION

SELECT*FROM表B;

結果：A與B重復的行只拿一次

(2)UNIONALL:重復的行復取，也確實是取出所有的(A+B)

SELECT*FROMA

UNIONALL

SELECT*FROMB;

(3)INTERSECT:返回公有的(A與B的交集)

(4)MINUS差積(A-B或B-A)

用法都與UNION相同。

注一：在求差積時，假如A放前，去掉公共的行時，剩余的差不多上A中的，而B中剩余

了什么并不管。假如B放前去掉公共行時，剩余差不多上B中的。

注二、在使用集合操作符時，兩個表的類型一定要相同。

三、SQL操作符的優(yōu)先級從高到低的順序是：

算術操作符——最高優(yōu)先級

連接操作符

比較操作符

NOT邏輯操作符

AND邏輯操作符

OR邏輯操作符——最低優(yōu)先級

第六章表空間

表空間

1.創(chuàng)建表空間

差不多語法：

CREATETABLESPACEspacename

[LOGGING]|NOLOGGING

DATAFILE'd:\javasky.dbf'

SIZE200M

AUTOEXTENDONNEXT200M;

如：

createtablespacejavasky——＞表空間名

datafile'd:\javasky.dbf＞名目地址

size20M＞大小為20M

autoextendonnext5M;＞當空間不足時自動增加5M;

2.刪除表空間

差不多語法：

DROPTABLESPACE'、TABLESPANCENAME”

注意表空間的名字需要使用雙引號包圍，同時表空間的名稱需要大寫。

如：droptablespace"JAVASKY";

3.查看表空間的名稱和狀態(tài)

selecttablespace_name,statusfromdba_tablespaces;

表空間的狀態(tài)屬性要緊有在線（ONLINEk離線（OFFLINEK只讀（READONLY）和

讀寫（READWRITE）4種

4.修改表空間的狀態(tài)

altertablespace表空間名狀態(tài);能夠修改表空間的狀態(tài)

第七章表

查看該帳戶下所有的表：

第一種、select*fromcat;

第二種、select*fromtab;

?建表

CREATETABLE表名（列名列類型,…）；

如：createtablestudent（idint,namevarchar2（5）,addressvarchar2（10））;

一創(chuàng)建一個具有IDNAMEADDRESS的學生信息表

創(chuàng)建完表以后能夠利用:desc表名來查看

如deststudent;如名稱、類型、是否為空。

?修改表

1.增加新列

ALTERTABLE表名ADD列名列類型［ADD列類型］;

如新一個的列：altertablestudentaddtelnumber;

增加一個新的列后給其賦值：updatestudentsettelhereid=2;

2.刪除舊列

ALTERTABLE表名DROPCOLUMN列名；

3.修改列類型（要求，列中許多據）

ALTERTABLE表名MODIFY列名列類型

4.修改列名

ALTERTABLE表名RENAMECOLUMN列名TO新列名；

5.修改表名

RENAME表名TO新表名；

?插入（記錄）數據，也確實是給列賦值

1.給其相應的字段賦值：INSERTINTO表名［（列名1,,,,）］VALUES（值1,,,）;注：口中的內容

可寫可不寫

如：INSERTINTOstudent（id,name,addredd）VALUESQ,'李小龍':河北省'）；

2.賦全值

INSERTINTOstudentVALUES。,'李小龍？河北省'）；

3.賦值以后查看該表的內容：select*fromstudent;

4.插入日期格式的值：

INSERTINTO列名（列名）

VALUES（TO_DATE（'2005-10-18','YYYY-MM-DD'））;

?修改記錄

UPDATE表名SET列名=值，….

［WHERE條件］;

如update表名settname='李小龍，

wheretname='李建龍>這是一個條件限制

?刪除（記錄）數據

第一種方法：DELETEFROM表名

［WHERE條件］;

第二種方法：TRUNCATETABLE表名；

?利用現有的表創(chuàng)建表

語法：

CREATETABLE<new_table_name>AS

SELECTcolumn_namesFROM<old_table_name>;

如：

SQL>CREATETABLEnewitemfile

ASSELECT*FROMitemfile;——>所有的歹U

SQL>CREATETABLEnewitemfile1

ASSELECTitemcode,itemdesc,qty_hand>選擇特定的列

FROMitemfile;

SQL>CREATETABLEnewitemfile2

ASSELECT*FROMitemfile

WHERE1=2;——>建表時的條件，那個地點是1=2,明顯為假，然而但是以建表，然

而空的內容為空。

?不帶條件的DELETE和TRUNCATETABLE的區(qū)別：

*在功能上，TRUNCATETABLE是清空一個表的內容，它相當于DELETEFROM

table_name。

*DELETE是dml操作,truncatetable是ddl操作；因此DELETE能夠回滾,TRUNCATETABLE

不可回滾。

*TRUNCATETABLE調整highwatermark而DELETE不；TRUNCATETABLE之后，

TABLE的HWM退回到INITIAL和

NEXT的位置［默認）delete那么不能夠。

*TRUNCATETABLE只能對TABLE進行操作，DELETE能夠是table,view,synonym。

"TRUNCATETABLE可不能觸發(fā)DELETE觸發(fā)器

*日志記錄方式不同,DELETE逐行記錄刪除日志,TRUNCATETABLE只記錄在磁盤上某一

第八章函數

函數

數據庫系統(tǒng)中，每個數據庫之中唯獨最大的區(qū)別確實是函數的支持上，使用函數能夠完成一

系列的操作能。Oracle提供一系列用于執(zhí)行特定操作的函數。

SQL函數帶有一個或多個參數并返回一個值

以下是SQL函數的分類：單行函數、分組函數、分析函數

一、單行函數

單行函數關于從表中查詢的每一行只返回一個值

能夠顯現在SELECT子句中和WHERE子句中

單行函數能夠大致劃分為：

字符函數：同意字符輸入同時返回字符或數值

數值函數：同意數值輸入并返回數值

日期函數：對日期型數據進行操作

轉換函數：從一種數據類型轉換為另一種數據類型

通用函數：NVL函數、DECODE函數單行函數的的語法：

function_name(columnleexpression,[arg1,arg2.….])

function_name:函數的名稱

culumnle:數據庫列名

expression:字符串或運算表達式

argl,arg2:函數中使用的參數

時刻類型函數

SYSDATE返回當前的系統(tǒng)時刻

ADD_MONTHS(date,x)返回加上x月后的日期DATE的值，X能夠是任意的整數，假如結果的月份

中所包含的日份量不于DATE的月份的日份量，那么返回結果月份的最后一天，假如不小于,

那么結果與DATE的日份量相同。

LAST_DAY(日期)指定日期所在月份的最后一天的日期，

TRUNC(日期，'MONTH\YEAR，)返回指定月份的第一天。

日期運算：

日期函數對日期值進行運算，并生成日期數據類型或數值類型的結果

日期函數包括：ADD_MONTHS、MONTHS_BETWEEN>LAST_DAY,ROUND,NEXTJDAY、TRUNC

范例一：

返回datel與date2之間相差的天數。該值是一個數值，其小數部分代表一天的幾分之幾。

SQL>selectto_dateC2010-01-31*,'yy-mm-dd，)-to_date('2010-01-01",'yy-mm-dd')as

相差天數fromdual;1相差天數是一個別名)

相差天數

30

范例二：

返回date1與date2之間的時刻間隔

selectto_date(,2020-01-0110:30*,'yy-mm-ddhh:mi')

-to_date(,2020-01-3109:31",'yy-mm-ddhh:mi')fromdual;

T0_DATE('2020-01-0110:30',"YY-MM-DDHH:MI')T0_DATE('2020-01-3109:31',"YY-MM-DDHH:MI')

-29.959028

2、字符函數

Initcap(char)：將首字母轉換為大寫

eg：SelectinitcapChello^fromdual;>Hello

Lower(char)：轉化為小寫

eg：Selectlower('FUN')fromdual;>fun

Upper(char)：轉化為大這與

eg：Selectupper('sun')fromdual;>SUN

Ltrim(char,set)：從左邊開始截取字符(一樣用來截取空格)

eg：Selectltrim('xyzadams'/xyz')fromdual;>adams

Trim():從兩端截取

eg：SQL>SELECTTRIM。"FROM'abcda')FROMdual;--->abc

Rtrim(char,set)：從右邊開始截取字符(右截空格)

eg：Selectrtrim('xyzadams'/ams')fromdual;>xyzad

Translate(char,from,to)：替換單個字符

eg：Selecttranslate('jack'j'b')fromdual;>back

Replace(char,searchstring,[repstring])：替換多個字符

eg：Selectreplace('jackandjue'/j':bl')fromdual;>blackandblue

Instr(char,m,n)：返回一個數值型，標識截取的字符的位置，從1開始計，第一次顯現的

位置

eg：Selectinstr('worldwide'd')fromdual;>5

Substr(char,m,n)：從第m開始截取n個字符

eg：Selectsubstr(abcdefg,3,2)fromdual;>cd

Concat(expr1,expr2)：合并

eg：Selectconcat('Hello'/world')fromdual;>Helloworld

作用：以UPPER。為例

1.UPPER。強制大寫

如：SELECTUPPER('hellen')FROMdual;

范例：一樣用戶在查詢一個人的姓名的時候可能考慮到那個人的姓名是以大寫字母儲存的依

舊以小寫字母儲存的呢？

SELECT*FROMempWHEREename=UPPER(,smith');

3、字符函數

以下是一些其它的字符函數：CHR和ASCII、LPAD和RPAD、TRIM、LENGTH、DECODE

1、CHR和ASCII：通過一個字符求ASCII碼

SQL>selectchr(67)fromdual;

C

C

2、LPAD和RPAD：左填充、右填充

SQL>SELECTLPAD(，abc；10,田)FROMdual;

LPAD('ABC

*******abc

，abc不足10個字符，在其左邊填加7個補齊10個，右填充同理

3.LENGTH():求給定的字符的長度如：

SQL>SELECTLENGTH('abcd')FROMdual;

LENGTH('ABCD')

4

假如是''中國"結果也是2,因為這是按字符算的

LENGTH。與LENGTHC0是等同的。

還有一個LENGTHB()L中國，的結果是4,是按字節(jié)算的。

LENGTH2。和LENTH4。差不多上按字符算的，我們常用的是LENGTH。和LENGTHB()

4.DECODE:相當于if語句如：

SQL>SELECTDECODE(id,l,'tom',2,'jack',3,'hellen')FROMstudent;

DECODE

tom

jack

hellen

假如id是123分別返回tomjackhelen相當于做了一個多行的判定

5.數字函數

函數范例結果

Abs(n)Selectabs(-15)fromdual;15

Ceil(n)Selectceil(44.778)fromdual;45

Cos(n)Selectcos(180)fromdual;-.5984601

Cosh(n)Selectcosh(O)fromdual;1

Floor(n)Selectfloor(100.2)fromdual;100

Power(m,n)Selectpower(4,2)fromdual;16

Mod(m,n)Selectmod(10,3)fromdual;1

Round(m,n)Selectround(100.256,2)fromdual;100.26

Trunc(m,n)Selecttrunc(100.256,2)fromdual;100.25

Sqrt(n)Selectsqrt(4)fromdual;2

Mod(m,n)Selectmod(4,2)fromdual;0

Sign(n)Selectsign(-30)fromdual;-1

dbms_random.value(x,y)Selectdbms_random.value(2,4)fromdual;3.980765

6.轉換函數

轉換函數將值從一種數據類型轉換為另一種數據類型

常用的轉換函數有：TO_CHAR、TCLDATE、TO_NUMBER

1、TO_DATE

SQL>SELECTTO_DATE(,2005-12-06','yyyy-mm-dd,)FROMdual;

TO_DATEC2005-

06-12月-05

2、TO_NUMBER

SQL>SELECTTO_NUMBER('100')FROMdual;

TO_NUMBER('100')

100

3、TO_CHAR

SQL>SELECTTO_CHAR(sysdate,'YYYY"年"fmMMn月nfmDDH日nHH24:MI:SS')FROM

dual;

TO_CHAR(SYSDATE,'YYYY”

2010年n月13日11:01:45

7.其它函數:NVL、NVL2、NULLIF

1、NVL

SELECTcomm,NVL(comm,0)FROM表

假如為空就用。來代替，不為空自己。

2、NVL2

有3個參數，假如第1個為空，就走第3個表達式，假設不為空，走第2個表達式

SELECTcomm,NVL(comm,comm,0)FROM表

3.NULLIF

假如兩個表達式的值相等返回空，不相等返回第一個表達式

SQL>SELECTNULLIF(22,23)FROMdual;

NULLIF(22,23)

22

SQL>SELECTNULLIF(22,23)FROMdual;

NULLIF(22,23)

22

二、組函數

詳細見組函數及分組統(tǒng)計一章

第九章約束

CREATETABLE深入

?為表增加約束

NOTNULL非空約束要求值不能為空

UNIQUE唯獨約束要求值不能重復

PRIMARYKEY主鍵約束關于整個表表中的記錄不能重復

FOREIGNKEY外鍵約束當多個表建立關聯(lián)時設置的一個引用約束

CHECK檢查檢查某一個列的值要符合某一個規(guī)范，如年齡(age>18)

?設置表所在的表空間

?使用序列實現自動增長

一、什么是約束？

約束是在表上強制執(zhí)行的一些數據校驗規(guī)那么，被插入/修改/刪除的數據必須符合在相關字

段上設置的這些約束條件

二、約束定義的語法

列級約束：在定義列的同時定義約束

語法：列定義約束類型

表級約束：在定義了所有的列之后定義的約束

語法：

列定義

[CONSTRAINT約束名]約束類型(列名)

約束名的命名規(guī)那么：

舉薦采納：表名一列名.約束類型簡寫

約束能夠在創(chuàng)建表時就定義，也能夠在創(chuàng)建完表后再添加

語法：ALTERTABLE表名

ADDCONSTRAINT約束名約束類型(要約束的列名)

三、各約束介紹

1.NOTNULL

(1)該列的值不能為空

(2)列級約束

如名字不能為空

createtabledepts(dept_idint,namevarchar(20)NOTNULL,descriptionvarchar(lOO));

假如創(chuàng)建了以后再加不能為空的約束能夠以修改列的形式：如

altertableemployeemodifyempnamevarchar2(6)notnull;

2.UNIQUE

(1)要求該列的值唯獨，承諾為空。注：Oracle承諾有多個空什和null

(2)列級約束、表級約束

(3)取名：表名一列名.uk

如：名字不能為空且唯獨

createtabledepts(dept_idint,namevarchar(20)NOTNULLUNIQUE,descriptionvarchar(100));

假如是先創(chuàng)建了表來要增加約束能夠寫成：

altertabledeptsaddconstraintdepts_name_ukunique(name);--將名字加上唯獨約束

3.PRIMARYKEY

(1)用來唯獨標識這一行記錄，一個表中只能有一個主鍵

⑵功能上相當于非空且唯獨

(3)列給約束、表級約束

(4)取名：表名—列名_pk

例1：將id號設置主鍵(在列級上定義)

createtabledepts(

idintprimarykey,——＞將id設置為主鍵

namevarchar(lO)uniquenotnull,——＞名字不為空且唯獨

descriptionvarchar(lOO));

例2：在表級上定義

CREATETABLEstudent(

firstnameVARCHAR(20),

lastnameVARCHAR(20),

descriptionVARCHAR(IOO),

[CONSTRAINTstudent_name_pk]PRIMARYKEY(firstname,lastname));

那個例子是聯(lián)合主鍵，所謂聯(lián)合主鍵確實是firstname和lastname他們的組合不重復，就認

為是有效。如AB和AC這不確實是重復。

⑸主鍵有一個最要緊的作用是：當兩個表關聯(lián)時，主外鍵時，要求引用的主表中的字段一

定要為主鍵。

4.FOREIGNKEY

(1)用于兩表間建立關系，需要指定引用主表的那列

(2)列級約束表級約束

(3)命名:主表名一子表名一FK

⑷語法：

[CONSTRAINT約束名]FOREIGNKEY(子表外鍵列名)

REFERENCES主表名(主鍵列名)

如我們把depts表和employees表的通過dept_id關聯(lián)起來。

一部門表注表

createtabledepts(

dept_idintprimarykey,

descriptionvarchar(20));

--職員表子表

createtableemployees(

emp_idintprimarykey,

addressvarchar(20),

dept_idint,

constraintdepts_employees_fk

foreignkey(dept_id)

referencesdepts(dept_id)一主鍵

)；

表之間的關聯(lián)關系，建議創(chuàng)建好表之后通過ALTERTABLE語句來添加：

語法：

ALTERTABLE表名

ADDCONSTRAINT外鍵約束名

FOREIGNKEY(本表外鍵列名)

REFERENCES主表名(主表主鍵列名)

[ONDELETE[RESTRICT|CASCADE|SETNULL|NOACTION]]

例：

altertableemployees

addconstraintdepts_employees_fkforeignkey(depts_id)

referencesdepts(depts_id);

ONDELETE:用來指定在刪除主表中的數據時，對關聯(lián)表(從表)所做的相關操作。

RESTRICT(限制):跟NOACTION(沒有動作)，成效一樣，不采取動作，即當主表中的主鍵在

子表中被使用，那么不承諾修改此主鍵值。

CASCADE(級聯(lián))：級聯(lián)更新子表

SETNULL(設置為空)：主表刪除(更新)行，那么設置子表中的外鍵列為NULL。

例如：圖

如上表employees表和depts表本來沒有關系的，然而通過dept_id能夠將他們聯(lián)系起來。

注：depts表中的dept_id必須為主鍵。才能通過dept_id與employees表聯(lián)合起來。

錄depts表與employees表進行聯(lián)合的時候，他們之間便有了聯(lián)系，如：在depts表中一共有

四個部門部,部門號代號分別是1、2、3、4。

然而假如我們在employeess表中插記錄時，就不能給某個人所在部門號設置為5,因為全然

沒有那個部門。

假如我們在employees表中加了兩個人差不多上2號部門，那么兩個表關聯(lián)下來以后就不

承諾我們刪除depts表的2號部門了。別的部門只要是在employees表中沒有被引用的都能

夠被刪除。

當子表中引用了主表中的某一個記錄時，那么那個主記錄不承諾刪除。也不承諾更換。

我們能夠在刪除以后做一些操作如：

例：

SQL>altertableemployees

2addconstraintdepts_employees_id

3foreignkey(dept_id)

4referencesdepts(dept_id)

5ondeletesetnull;-一>刪除后設置為空

也確實是主表的部門被刪除后，子表的記錄走向為空。也確實是原先引用該部門的記錄現在

部門不存在了，該記錄的引用就就變?yōu)榭樟恕?/p>

5.CHECK

(1)對某列的值進行范疇限制、格式限制等

(2)列級約束、表級約束

(3)取名：表名一列名一ck

例：

SQL>createtablestudent(

2idintprimarykey,

3namevarchar(lO)uniquenotnull,

4ageintcheck(age>18)—能夠使用like等

5)；

6.刪除約束

先找到表的約束名稱，執(zhí)行：

selectTABLENAME,CONSTRAINTNAMEfromuserconstraintswhereowner=c,

其中CONSTRAINT_NAME為表的約束名稱

然后刪除約束：

altertableTABLE_NAMEdropconstraintCONSTRAINT_NAME[cascade];

使用CASCADE關鍵字使相關約束也失效.

如：刪除sal上的約束

altertableemployeedropconstraintemployee_sal_ck;

例：

SQL>descuser_constraints;

名稱是否為空？類型

OWNERNOTNULLVARCHAR2(30)

CONSTRAINT_NAMENOTNULLVARCHAR2(30)

CONSTRAINT_TYPEVARCHAR2(1)

TABLE_NAMENOTNULLVARCHAR2(30)

SEARCH_CONDITIONLONG

SQL>selectCONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPEfrom

user_constraintswhe

retable_name=,employees,;

能夠看到employees表的表名、約束名字和類型等。

7.指定表空間

CREATETABLE()TABLESPACEspacename

如：

descuser_tablespace;>查看所有的表空間

createtablestudent(idint)tablespacesystem;

8.創(chuàng)建序列

序列：是Oracle的一個對象，表也是一個對象。所謂序列是指生成了一個有順序的數字對象。

序號生成器

創(chuàng)建語法：

CREATESEQUENCE序列名MINVALUEnumberlSTARTWITHnumber2INCREMENT

BYnumber3nocache|cachenumber3;

MINVALUE：最小的值

INCREMENTBY：從那開始

STARTWITH：增量、步長

是否可被緩存

是否能夠循環(huán)

從序列中取值:Select序列名.nextvalfromdual;—nextval序列中的下一個值

讀取下一個值：Selectseq.nextvalfromdual;

讀取當前值：Selectseq.currvalfromdual;

如創(chuàng)建一個序列：

SQL>createsequenceseq_dept

2startwith1

3incrementby2

4maxvalue10;

查看：

SQL>selectseq_dept.nextvalfromdual;

NEXTVAL

1

第執(zhí)行一次就+2.

SQL>selectseq_dept.currvalfromdual;

NEXTVAL

1

currval不論執(zhí)行多少次差不多上1.

往部口表中插：

insertintodeptsvalues（seq_dept.nextval,“貝才務部”）；

seq_dept.nextval是一個數字對象，與部門號對應。

修改序列：如將最大值改為100.

altersequenceseq_deptmaxvalue100;

刪除序列：dropsequenceseq_dept;

第十章單查詢查詢

SQL查詢的差不多語法結構:

SELECTselection_list選擇哪些列

FROMtable_list從何處選擇

WHEREprimary_constraint行必須滿足的條件

GROUPBYgrouping_columns結果如何樣分組

OREDERBYsort_columns如何樣對結果排序

ROWNUMoffset結果限定

以emp表和部門表（dept）為例：

一、一般查詢

1.查詢所有記錄的所有列

SELECT*FROMemp

2.查詢特定行

SELECT*FROMempWHEREname=，紀小嵐

3.查詢特定列

SELECTname,sarlaryFROMemp;

4.去掉重復的值

SELECTDISTINCTdept_idFROMemp;

5.給列取別名

SELECTnameAS姓名