oracle學(xué)習(xí)文檔

1、第一章 Oracle入門一、 數(shù)據(jù)庫概述數(shù)據(jù)庫（Database）是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲和管理數(shù)據(jù)的倉庫，它產(chǎn)生于距今五十年前。簡單來說是本身可視為電子化的文件柜存儲電子文件的處所，用戶可以對文件中的數(shù)據(jù)運(yùn)行新增、截取、更新、刪除等操作。常見的數(shù)據(jù)模型1. 層次結(jié)構(gòu)模型: 層次結(jié)構(gòu)模型實(shí)質(zhì)上是一種有根結(jié)點(diǎn)的定向有序樹，IMS(Information Manage-mentSystem)是其典型代表。2. 網(wǎng)狀結(jié)構(gòu)模型：按照網(wǎng)狀數(shù)據(jù)結(jié)構(gòu)建立的數(shù)據(jù)庫系統(tǒng)稱為網(wǎng)狀數(shù)據(jù)庫系統(tǒng)，其典型代表是DBTG(Data Base Task Group)。3. 關(guān)系結(jié)構(gòu)模型：關(guān)系式數(shù)據(jù)結(jié)構(gòu)把一些復(fù)雜的數(shù)據(jù)結(jié)構(gòu)歸

2、結(jié)為簡單的二元關(guān)系(即二維表格形式)。常見的有Oracle、mssql、mysql等二、 主流數(shù)據(jù)庫數(shù)據(jù)庫名公司特點(diǎn)工作環(huán)境mssql微軟只能能運(yùn)行在windows平臺，體積比較龐大，占用許多系統(tǒng)資源， 但使用很方便，支持命令和圖形化管理，收費(fèi)。中型企業(yè)Mysql甲骨文是個(gè)開源的數(shù)據(jù)庫server，可運(yùn)行在多種平臺, 特點(diǎn)是響應(yīng)速度特別快，主要面向中小企業(yè)中小型企業(yè)PostgreSQL號稱“世界上最先進(jìn)的開源數(shù)據(jù)庫“，可以運(yùn)行在多種平臺下，是tb級數(shù)據(jù)庫，而且性能也很好中大型企業(yè)oracle甲骨文獲得最高認(rèn)證級別的ISO標(biāo)準(zhǔn)安全認(rèn)證，性能最高， 保持開放平臺下的TPC-D和TPC-C

3、的世界記錄。但價(jià)格不菲大型企業(yè)db2IBMDB2在企業(yè)級的應(yīng)用最為廣泛，  在全球的500家最大的企業(yè)中,幾乎85%以上用DB2數(shù)據(jù)庫服務(wù)器。收費(fèi)大型企業(yè)Access微軟Access是一種桌面數(shù)據(jù)庫，只適合數(shù)據(jù)量少的應(yīng)用，在處理少量 數(shù)據(jù)和單機(jī)訪問的數(shù)據(jù)庫時(shí)是很好的，效率也很高小型企業(yè)三、 Oracle數(shù)據(jù)庫概述 ORACLE數(shù)據(jù)庫系統(tǒng)是美國ORACLE公司（甲骨文）提供的以分布式數(shù)據(jù)庫為核心的一組軟件產(chǎn)品，是目前最流行的客戶/服務(wù)器(CLIENT/SERVER)或B/S體系結(jié)構(gòu)的數(shù)據(jù)庫之一。ü 拉里·埃里森ü 就業(yè)前景從就業(yè)與擇業(yè)的角度來講，

4、計(jì)算機(jī)相關(guān)專業(yè)的大學(xué)生從事oracle方面的技術(shù)是職業(yè)發(fā)展中的最佳選擇。其一、就業(yè)面廣：全球前100強(qiáng)企業(yè)99家都在使用ORACLE相關(guān)技術(shù)，中國政府機(jī)構(gòu)，大中型企事業(yè)單位都能有ORACLE技術(shù)的工程師崗位。其二、技術(shù)層次深：如果期望進(jìn)入IT服務(wù)或者產(chǎn)品公司（類似畢博、DELL、IBM等），Oracle技術(shù)能夠幫助提高就業(yè)的深度。其三、職業(yè)方向多：Oracle數(shù)據(jù)庫管理方向、Oracle開發(fā)及系統(tǒng)架構(gòu)方向、Oracle數(shù)據(jù)建模數(shù)據(jù)倉庫等方向。四、 如何學(xué)習(xí)認(rèn)真聽課、多思考問題、多動手操作、有問題一定要問、多參與討論、多幫組同學(xué)高級部分PL/SQL編程存儲過程和觸發(fā)器ORACLE鎖管理索引、約束

5、視圖和事務(wù)ORACLE表的查詢ORACLE的函數(shù)ORACLE角色和權(quán)限備份與恢復(fù)ORACLE表管理ORACLE體系結(jié)構(gòu)ORACLE基本使用ORACLE用戶管理基礎(chǔ)部分五、 體系結(jié)構(gòu)oracle的體系很龐大，要學(xué)習(xí)它，首先要了解oracle的框架。oracle的框架主要由物理結(jié)構(gòu)、邏輯結(jié)構(gòu)、內(nèi)存分配、后臺進(jìn)程、oracle例程、系統(tǒng)改變號 (System Change Number)組成ü 物理結(jié)構(gòu)物理結(jié)構(gòu)包含三種數(shù)據(jù)文件:1) 控制文件2) 數(shù)據(jù)文件3) 在線重做日志文件ü 邏輯結(jié)構(gòu)功能：數(shù)據(jù)庫如何使用物理空間組成：表空間、段、區(qū)、塊的組成層次六、 oracle安裝、卸載和

6、啟動ü 硬件要求物理內(nèi)存：1GB可用物理內(nèi)存：50M交換空間大?。?.25GB硬盤空間：10GBü 安裝1. 安裝程序成功下載，將會得到如下2個(gè)文件：解壓文件將得到database文件夾，文件組織如下：點(diǎn)擊setup.exe執(zhí)行安裝程序，開始安裝。2. 點(diǎn)擊安裝程序?qū)霈F(xiàn)如下安裝界面，步驟 1/9：配置安全更新填寫電子郵件地址(可以不填)，去掉復(fù)選框，點(diǎn)擊下一步3. 步驟2/9：選擇安裝選項(xiàng)勾選第一個(gè)，安裝和配置數(shù)據(jù)庫，點(diǎn)擊下一步4. 步驟3/8：選擇系統(tǒng)類勾選第一個(gè)：桌面類，點(diǎn)擊下一步5. 步驟4/8：配置數(shù)據(jù)庫安裝選擇安裝路徑，選擇數(shù)據(jù)庫版本(企業(yè)版)，選擇字符集(默

7、認(rèn)值)填寫全局?jǐn)?shù)據(jù)庫名，管理口令6. 步驟5/8：先決條件檢查如果你的電腦滿足要求但仍然顯示檢查失敗，這時(shí)候直接忽略，勾選全部忽略7. 步驟6/8：概要信息核對將要安裝數(shù)據(jù)的詳細(xì)信息，并保存響應(yīng)文件，以備以后查看。然后點(diǎn)擊完成數(shù)據(jù)庫安裝8. 步驟7/8：安裝產(chǎn)品產(chǎn)品安裝過程中將會出現(xiàn)以上2個(gè)界面9. 步驟8/8：完成安裝ü 卸載Oracle1. 在運(yùn)行services.msc打開服務(wù)，停止Oracle的所有服務(wù)。2. oracle11G自帶一個(gè)卸載批處理appAdministratorproduct11.2.0dbhome_1deinstalldeinstall.bat3. 運(yùn)行該批

8、處理程序?qū)⒆詣油瓿蒾racle卸載工作，最后手動刪除app文件夾（可能需要重啟才能刪除）4. 運(yùn)行regedit命令，打開注冊表窗口。刪除注冊表中與Oracle相關(guān)的內(nèi)容，具體如下：       Ø 刪除HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE目錄。Ø 刪除HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services中所有以oracle或OraWeb為開頭的鍵。Ø 刪除HKEY_LOCAL_MACHINE/SYSETM/Current

9、ControlSet/Services/Eventlog/application中所有以oracle開頭的鍵。Ø 刪除HKEY_CLASSES_ROOT目錄下所有以O(shè)ra、Oracle、Orcl或EnumOra為前綴的鍵。Ø 刪除HKEY_CURRENT_USER/SOFTWARE/Microsoft/windows/CurrentVersion/Explorer/MenuOrder/Start Menu/Programs中所有以oracle 開頭的鍵。Ø 刪除HKDY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI中除Micro

10、soft ODBC for Oracle注冊表鍵以外的所有含有Oracle的鍵。Ø 刪除環(huán)境變量中的PATHT CLASSPATH中包含Oracle的值。Ø 刪除“開始”/“程序”中所有Oracle的組和圖標(biāo)。Ø 刪除所有與Oracle相關(guān)的目錄，包括：C:Program fileOracle目錄。ORACLE_BASE目錄。C:Documents and Settings系統(tǒng)用戶名、LocalSettingsTemp目錄下的臨時(shí)文件。七、 oracle中的數(shù)據(jù)庫存儲的是數(shù)據(jù)對象觸發(fā)器視圖表同義詞Oracle存儲的是多個(gè)數(shù)據(jù)庫用戶數(shù)據(jù)庫PubsModelMaste

11、rSQLSERVER八、 常用的工具ü Sql Plusü Sql Developerü Oracle Enterprise Manager第二章 用戶和權(quán)限一、 用戶介紹ORACLE用戶是學(xué)習(xí)ORACLE數(shù)據(jù)庫中的基礎(chǔ)知識，下面就介紹下類系統(tǒng)常用的默認(rèn)ORACLE用戶:1. sys用戶：超級用戶，完全是個(gè)SYSDBA(管理數(shù)據(jù)庫的人)。擁有dba，sysdba，sysoper等角色或權(quán)限。是oracle權(quán)限最高的用戶，登錄時(shí)不能用normal。2. system用戶：超級用戶，默認(rèn)是SYSOPT(操作數(shù)據(jù)庫的人)，不過它也能以SYSDBA的權(quán)限登陸。擁有普通db

12、a角色權(quán)限。3. scott用戶：是個(gè)演示用戶，是讓你學(xué)習(xí)Oracle用的。二、 常用命令學(xué)習(xí)oracle，首先我們必須要掌握常用的基本命令，oracle中的命令比較多，常用的命令如下：1. 登錄命令(sqlplus)說明：用于登錄到oracle數(shù)據(jù)庫用法：sqlplus 用戶名/密碼 as sysdba/sysoper注意：當(dāng)用特權(quán)用戶登錄時(shí)，必須帶上sysdba或sysoper例子：普通用戶登錄sys用戶登錄操作系統(tǒng)的身份登錄2. 連接命令(conn)說明：用于連接到oracle數(shù)據(jù)庫，也可實(shí)現(xiàn)用戶的切換用法：conn 用戶名/密碼 as sysdba/sysoper注意：當(dāng)用特權(quán)用戶連接

13、時(shí)，必須帶上sysdba或sysoper例子：3. 斷開連接(disc)說明：斷開與當(dāng)前數(shù)據(jù)庫的連接用法：disc4. 顯示用戶名(show user)說明：顯示當(dāng)前用戶名用法：show user5. 退出(exit)說明：斷開與當(dāng)前數(shù)據(jù)庫的連接并會退出用法：exit6. 編輯腳本(edit/ed)說明：編輯指定或緩沖區(qū)的sql腳本用法：edit 文件名列子：7. 運(yùn)行腳本 (start/)說明：運(yùn)行指定的sql腳本用法：start/ 文件名列子：8. 印刷屏幕 (spool)說明：將sql*plus屏幕中的內(nèi)容輸出到指定的文件用法：開始印刷->spool 文件名 結(jié)束印刷->sp

14、ool off列子：文件內(nèi)容9. 顯示寬度 (linesize)說明：設(shè)置顯示行的寬度，默認(rèn)是80個(gè)字符用法：set linesize 12010. 顯示頁數(shù) (pagesize)說明：設(shè)置每頁顯示的行數(shù)，默認(rèn)是14頁用法：set pagesize 20三、 用戶管理1. 創(chuàng)建用戶說明：Oracle中需要創(chuàng)建用戶一定是要具有dba(數(shù)據(jù)庫管理員)權(quán)限的用戶才能創(chuàng)建，而且創(chuàng)建的新用戶不具備任何權(quán)限，連登錄都不可以。用法：create user 新用戶名 identified by 密碼例子：2. 修改密碼說明：修改用戶密碼一般有兩種方式，一種是通過命令password修改，另一種是通過語句alt

15、er user實(shí)現(xiàn)，如果要修改他人的密碼，必須要具有相關(guān)的權(quán)限才可以用法：方式一 password 用戶名 方式二 alert user 用戶名 identified by 新密碼例子：修改當(dāng)前用戶(方式一)修改當(dāng)前用戶(方式二)修改其他用戶(方式一)修改其他用戶(方式二)3. 用戶禁用與啟用說明：Oracle中想要禁用或啟用一個(gè)賬戶也同樣是使用alter user 命令來完成，只是語法和修改密碼有所不同。用法：禁用 alter user 用戶名 account lock啟用 alter user 用戶名 account unlock4. 刪除用戶說明：Oracle中要刪除一個(gè)用戶，必須要具有

16、dba的權(quán)限。而且不能刪除當(dāng)前用戶，如果刪除的用戶有數(shù)據(jù)對象，那么必須加上關(guān)鍵字cascade。用法：drop user 用戶名 cascade四、 用戶權(quán)限與角色1. 權(quán)限Oracle中權(quán)限主要分為兩種，系統(tǒng)權(quán)限和實(shí)體權(quán)限。Ø 系統(tǒng)權(quán)限：系統(tǒng)規(guī)定用戶使用數(shù)據(jù)庫的權(quán)限。（系統(tǒng)權(quán)限是對用戶而言)。ü DBA: 擁有全部特權(quán)，是系統(tǒng)最高權(quán)限，只有DBA才可以創(chuàng)建數(shù)據(jù)庫結(jié)構(gòu)。ü RESOURCE:擁有Resource權(quán)限的用戶只可以創(chuàng)建實(shí)體，不可以創(chuàng)建數(shù)據(jù)庫結(jié)構(gòu)。ü CONNECT:擁有Connect權(quán)限的用戶只可以登錄Oracle，不可以創(chuàng)建實(shí)體，不可以創(chuàng)建

17、數(shù)據(jù)庫結(jié)構(gòu)。注意：對于普通用戶：授予connect, resource權(quán)限。對于DBA管理用戶：授予connect，resource, dba權(quán)限。ü 授予系統(tǒng)權(quán)限說明：要實(shí)現(xiàn)授予系統(tǒng)權(quán)限只能由DBA用戶授出。用法：grant 系統(tǒng)權(quán)限1,系統(tǒng)權(quán)限2 to 用戶名1,用戶名2.例子：ü 系統(tǒng)權(quán)限回收：說明：系統(tǒng)權(quán)限只能由DBA用戶回收用法：revoke 系統(tǒng)權(quán)限 from 用戶名例子：Ø 實(shí)體權(quán)限：某種權(quán)限用戶對其它用戶的表或視圖的存取權(quán)限。（是針對表或視圖而言的）。主要包括select, update, insert, alter, index, delete,

18、 all其中all包括所有權(quán)限。ü 授予實(shí)體權(quán)限用法：grant 實(shí)體權(quán)限1,實(shí)體權(quán)限2 on 表名 to用戶名1,用戶名2.例子：ü 實(shí)體權(quán)限回收用法：revoke 實(shí)體權(quán)限 on 表名from 用戶名例子：Ø 查詢用戶擁有哪里權(quán)限：SQL> select * from role_tab_privs;/查詢授予角色的對象權(quán)限SQL> select * from role_role_privs;/查詢授予另一角色的角色SQL> select * from DBA_tab_privs;/查詢直接授予用戶的對象權(quán)限SQL> select * f

19、rom dba_role_privs;/查詢授予用戶的角色SQL> select * from dba_sys_privs;/查詢授予用戶的系統(tǒng)權(quán)限SQL> select * from role_sys_privs;/查詢授予角色的系統(tǒng)權(quán)限SQL> Select * from session_privs;/ 查詢當(dāng)前用戶所擁有的權(quán)限2. 角色角色。角色是一組權(quán)限的集合，將角色賦給一個(gè)用戶，這個(gè)用戶就擁有了這個(gè)角色中的所有權(quán)限。Ø 系統(tǒng)預(yù)定義角色預(yù)定義角色是在數(shù)據(jù)庫安裝后，系統(tǒng)自動創(chuàng)建的一些常用的角色。下面我們就簡單介紹些系統(tǒng)角色：ü CONNECT, RE

20、SOURCE, DBA這些預(yù)定義角色主要是為了向后兼容。其主要是用于數(shù)據(jù)庫管理。oracle建議用戶自己設(shè)計(jì)數(shù)據(jù)庫管理和安全的權(quán)限規(guī)劃，而不要簡單的使用這些預(yù)定角色。將來的版本中這些角色可能不會作為預(yù)定義角色。ü DELETE_CATALOG_ROLE， EXECUTE_CATALOG_ROLE，SELECT_CATALOG_ROLE這些角色主要用于訪問數(shù)據(jù)字典視圖和包。ü EXP_FULL_DATABASE， IMP_FULL_DATABASE這兩個(gè)角色用于數(shù)據(jù)導(dǎo)入導(dǎo)出工具的使用。Ø 自定義角色Oracle建議我們自定義自己的角色，使我們更加靈活方便去管理用戶&

21、#252; 創(chuàng)建角色SQL> create role admin;ü 授權(quán)給角色SQL> grant connect,resource to admin;ü 撤銷角色的權(quán)限SQL> revoke connect from admin;ü 刪除角色SQL> drop role admin;第三章 Sql查詢與函數(shù)一、 SQL概述SQL（Structured Query Language)結(jié)構(gòu)化查詢語言，是一種數(shù)據(jù)庫查詢和程序設(shè)計(jì)語言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng)。同時(shí)也是數(shù)據(jù)庫腳本文件的擴(kuò)展名。Ø SQL語言主要包

22、含5個(gè)部分ü 數(shù)據(jù)定義語言Data Definition Language(DDL），用來建立數(shù)據(jù)庫、數(shù)據(jù)對象和定義其列。例如：CREATE、DROP、ALTER等語句。ü 數(shù)據(jù)操作語言Data Manipulation Language(DML），用來插入、修改、刪除、查詢，可以修改數(shù)據(jù)庫中的數(shù)據(jù)。例如：INSERT（插入）、UPDATE（修改）、DELETE（刪除）語句ü 數(shù)據(jù)查詢語言 (Data Query Language, DQL) 是SQL語言中，負(fù)責(zé)進(jìn)行數(shù)據(jù)查詢而不會對數(shù)據(jù)本身進(jìn)行修改的語句，這是最基本的SQL語句。例如：SELECT（查詢）

23、2; 數(shù)據(jù)控制語言Data Controlling Language（DCL），用來控制數(shù)據(jù)庫組件的存取允許、存取權(quán)限等。例如：GRANT、REVOKE、COMMIT、ROLLBACK等語句。ü 事務(wù)控制語言（Transactional Control Language，TCL），用于維護(hù)數(shù)據(jù)的一致性，包括COMMIT（提交事務(wù)）、ROLLBACK（回滾事務(wù)）和SAVEPOINT（設(shè)置保存點(diǎn)）3條語句二、 Oracle的數(shù)據(jù)類型類型參數(shù)描述字符類型char12000字節(jié)固定長度字符串，長度不夠的用空格補(bǔ)充varchar214000字節(jié)可變長度字符串，與CHAR類型相比，使用VARCH

24、AR2可以節(jié)省磁盤空間，但查詢效率沒有char類型高數(shù)值類型Number(m,n)m(138) n(-84127) 可以存儲正數(shù)、負(fù)數(shù)、零、定點(diǎn)數(shù)和精度為38位的浮點(diǎn)數(shù)，其中，M表示精度，代表數(shù)字的總位數(shù)；N表示小數(shù)點(diǎn)右邊數(shù)字的位數(shù)日期類型date7字節(jié)用于存儲表中的日期和時(shí)間數(shù)據(jù)，取值范圍是公元前4712年1月1日至公元9999年12月31日，7個(gè)字節(jié)分別表示世紀(jì)、年、月、日、時(shí)、分和秒二進(jìn)制數(shù)據(jù)類型row12000字節(jié)可變長二進(jìn)制數(shù)據(jù)，在具體定義字段的時(shí)候必須指明最大長度nlong raw12GB可變長二進(jìn)制數(shù)據(jù)LOB數(shù)據(jù)類型clob14GB只能存儲字符數(shù)據(jù)nclob1

25、4GB保存本地語言字符集數(shù)據(jù)blob14GB以二進(jìn)制信息保存數(shù)據(jù)三、 DDL語言1. Create table命令用于創(chuàng)建表。在創(chuàng)建表時(shí)，經(jīng)常會創(chuàng)建該表的主鍵、外鍵、唯一約束、Check約束等Ø 語法結(jié)構(gòu)create table 表名( 字段名 類型 約束 . CONSTRAINT fk_columnFOREIGN KEY(column1,column2,.column_n)REFERENCES tablename(column1,column2,.column_n)Ø 例子：create table student(stuNo char(32) primary key,-

26、主鍵約束stuName varchar2(20) not null,-非空約束cardId char(20) unique,-唯一約束sex char(2) check(sex='男' or sex='女'),-檢查約束address varchar2(100) default '地址不詳'-默認(rèn)約束)create table mark(mid int primary key,-主鍵約束stuNo char(32) not null,courseName varchar2(20) not null,-非空約束score number(3) not

27、 null check(score>=0 and score<=100),-非空約束,檢查約束constraint fk_stuno foreign key(stuno) references student(stuNo)-表級外鍵約束)2. Alter table命令對已經(jīng)存在的表進(jìn)行修改，可以新增或刪除字段，修改字段名或其類型和類型長度。Ø 修改表名alter table old_table rename to new_tableØ 修改字段名alter table table_name rename column old_column to new_col

28、umnØ 添加字段alter table table_name add new_column varhcar2(200) default nullØ 修改數(shù)據(jù)類型alter table table_name modifiy filedname carchar2(100)Ø 刪除字段alter table table_name drop column column_nameØ 添加主鍵alter table table_name add constraint pk_name primary key(column_id)Ø 刪除主鍵alter ta

29、ble table_name drop constraint pk_nameØ 添加外鍵alter table table_name add constraint fk_name foreign key(column_id)Ø 刪除外鍵alter table table_name drop constraint fk_name3. Drop table命令用于從數(shù)據(jù)庫中刪除表及全部數(shù)據(jù)drop table table_name4. Truncate table命令可以快速刪除表的記錄并釋放空間，不使用事務(wù)處理，速度快且效率高，但無法回滾事務(wù)。truncate table t

30、able_name5. 其他create命令Ø CREATE INDEX：創(chuàng)建數(shù)據(jù)表索引Ø CREATE PROCEDURE：創(chuàng)建存儲過程Ø CREATE FUNCTION：創(chuàng)建用戶函數(shù)Ø CREATE VIEW：創(chuàng)建視圖Ø CREATE TRIGGER：創(chuàng)建觸發(fā)程序Ø CREATE SEQUENCE6. SEQUENCE在oracle中sequence就是所謂的序列號，每次取的時(shí)候它會自動增加，一般用在需要按序列號排序的地方。Ø 創(chuàng)建語法CREATE SEQUENCE sequence_name INCREMENT BY

31、1 -每次加幾個(gè) START WITH 1 -從1開始計(jì)數(shù) NOMAXVALUE -不設(shè)置最大值 NOCYCLE -一直累加，不循環(huán) CACHE 10 -使序列號預(yù)分配Ø 列子SELECT sequence_marks.currval from marksinsert into table_name values(sequence_marks.nextval)Ø 刪除drop sequence sequence_name四、 DML語言1. INSERT語句Insert into table_name(column1,column2.column_n) values(val

32、1.)2. UPDATE語句update table_name set column1=value,. where condition3. DELETE語句Delete from table_name where condition五、 DQL語言1. 概述Ø 基本語法SELECT column_list*查詢所有數(shù)據(jù) INTO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression

33、 ASC | DESC Ø 例子SQL>select * from em-查詢所有數(shù)據(jù)SQL>select ename,job from em-查詢指定的字段數(shù)據(jù)SQL> select * from emp where sal>1000-加條件2. 聚合函數(shù)聚合函數(shù)對一組值執(zhí)行計(jì)算并返回單一的值。聚合函數(shù)忽略空值。聚合函數(shù)經(jīng)常與 SELECT 語句的 GROUP BY 子句一同使用。不能在 WHERE 子句中使用組函數(shù)。Ø AVG(expression): 返回集合中各值的平均值-查詢所有人都的平均工資select avg(sal) from emp

34、Ø COUNT(expression): 以 Int32 形式返回集合中的項(xiàng)數(shù)-查詢工資低于2000的人數(shù)select count(*) from emp where sal<2000Ø MAX(expression): 返回集合中的最大值-查詢最高工資select max(sal) from emp Ø MIN(expression): 返回集合中的最小值-查詢最低工資select max(sal) from emp Ø SUM(expression): 返回集合中所有值的總和-查詢部門編號為20的工資總和select sum(sal) from

35、 emp where deptno=203. 排序函數(shù)用于根據(jù)指定的列對結(jié)果集進(jìn)行排序Ø ORDER BY 語句-查詢所有信息并按工資排序select * from emp order by salØ 升序(asc)-查詢所有信息并按工資升序排序select * from emp order by sal ascØ 降序(desc)-查詢所有信息并按工資降序排序select * from emp order by sal desc4. 分組函數(shù)分組函數(shù)作用于一組數(shù)據(jù)，并對一組數(shù)據(jù)返回一個(gè)值。Ø GROUP BY 子句-查詢每個(gè)部門的平均工資select

36、avg(sal) from emp group by deptnoØ HAVING 子句-查詢部門的平均工資大于2000select avg(sal) from emp group by deptno having avg(sal)>20005. 連接查詢連接查詢是關(guān)系數(shù)據(jù)庫中最主要的查詢，主要包括內(nèi)連接、外連接和交叉連接等。通過連接運(yùn)算符可以實(shí)現(xiàn)多個(gè)表查詢。Ø 內(nèi)連接內(nèi)連接也叫連接，是最早的一種連接。還可以被稱為普通連接或者自然連接，內(nèi)連接是從結(jié)果表中刪除與其他被連接表中沒有匹配行的所有行，所以內(nèi)連接可能會丟失信息。ü 等值連接:select * from

37、 emp inner join dept on emp.deptno=dept.deptnoselect * from emp,dept where emp.deptno=dept.deptnoü 不等值連接:select * from emp inner join dept on emp.deptno!=dept.deptnoØ 外連接外連接分為三種：左外連接，右外連接，全外連接。對應(yīng)SQL：LEFT/RIGHT/FULL OUTER JOIN。通常我們省略outer 這個(gè)關(guān)鍵字。寫成：LEFT/RIGHT/FULL JOIN。ü 左外連接(left join)

38、: 是以左表的記錄為基礎(chǔ)的select * from emp left join dept on emp.deptno=dept.deptnoü 右外連接(right join): 和left join的結(jié)果剛好相反,是以右表(BL)為基礎(chǔ)的select * from emp right join dept on emp.deptno=dept.deptnoü 全外連接(full join): 左表和右表都不做限制，所有的記錄都顯示，兩表不足的地方用null 填充select * from emp full join dept on emp.deptno=dept.dept

39、noØ 交叉連接交叉連接即笛卡兒乘積，是指兩個(gè)關(guān)系中所有元組的任意組合。一般情況下，交叉查詢是沒有實(shí)際意義的。select * from cross full join dept6. 常用查詢ü like模糊查詢-查詢姓名首字母為S開始的員工信息select * from emp where ename like 'S%'-查詢姓名第三個(gè)字母為A的員工信息select * from emp where ename like '_A%'ü is null/is not null 查詢-查詢沒有獎金的雇員信息select * from

40、emp where comm is null-查詢有獎金的雇員信息select * from emp where comm is not nullü in查詢-查詢雇員編號為7566、7499、7844的雇員信息select * from emp where empno in(7566,7499,7844)ü exists/not exists查詢(效率高于in)-查詢有上級領(lǐng)導(dǎo)的雇員信息select * from emp e where exists (select * from emp where empno=e.mgr)-查詢沒有上級領(lǐng)導(dǎo)的雇員信息select * f

41、rom emp e where not exists (select * from emp where empno=e.mgr)ü all查詢-查詢比部門編號為20的所有雇員工資都高的雇員信息select * from emp where sal > all(select sal from emp where deptno=20)ü union合并不重復(fù)select * from emp where comm is not nullunionselect * from emp where sal>3000ü union all合并重復(fù)select *

42、from emp where comm is not nullunion allselect * from emp where sal>30007. 子查詢當(dāng)一個(gè)查詢是另一個(gè)查詢的條件時(shí)，稱之為子查詢。子查詢是一個(gè) SELECT 語句，它嵌套在一個(gè) SELECT、SELECT.INTO 語句、INSERT.INTO 語句、DELETE 語句、或 UPDATE 語句或嵌套在另一子查詢中。ü 在CREATE TABLE語句中使用子查詢-創(chuàng)建表并拷貝數(shù)據(jù)create table temp(id,name,sal) as select empno,ename,sal from emp&

43、#252; 在INSERT語句中使用子查詢-當(dāng)前表拷貝insert into temp(id,name,sal) select * from temp-從其他表指定字段拷貝insert into temp(id,name,sal) select empno,ename,sal from empü 在DELETE語句中使用子查詢-刪除SALES部門中的所有雇員delete from emp where deptno in(select deptno from dept where dname='SALES')ü 在UPDATE語句中使用子查詢-修改scott用

44、戶的工資和smith的工資一致update emp set sal=(select sal from emp where ename='SMITH') where ename='SCOTT'-修改black用戶的工作,工資,獎金和scott一致update emp set(job,sal,comm)=(select job,sal,comm from emp where ename='SCOTT') where ename='BLAKE'ü 在SELECT語句中使用子查詢-查詢和ALLEN同一部門的員工信息select

45、* from emp where deptno in(select deptno from emp where ename='ALLEN')-查詢工資大于部門平均工資的雇員信息select * from emp e (select avg(sal) asal,deptno from emp group by deptno) t where e.deptno=t.deptno and e.sal>t.asal六、 TCL語言1. COMMITcommit -提交事務(wù)2. ROLLBACKrollback to p1 -回滾到指定的保存點(diǎn)rollback -回滾所有的保存點(diǎn)3

46、. SAVEPOINTsavepoint p1 -設(shè)置保存點(diǎn)4. 只讀事務(wù)只讀事務(wù)是指只允許執(zhí)行查詢的操作，而不允許執(zhí)行任何其它dml操作的事務(wù)，它的作用是確保用戶只能取得某時(shí)間點(diǎn)的數(shù)據(jù)。set transaction read only七、 oracle函數(shù)1. 字符串函數(shù)字符串函數(shù)是oracle中比較常用的，下面我們就介紹些常用的字符串函數(shù):ü concat：字符串連接函數(shù)，也可以使用|-將職位和雇員名稱顯示在一列中select concat(ename,concat('(',concat(job,')') from empselect ename

47、 | '(' | job | ')' from empü length：返回字符串的長度-查詢雇員名字長度為5個(gè)字符的信息select * from emp where length(ename)=5ü lower：將字符串轉(zhuǎn)換成小寫-以小寫方式顯示雇員名select lower(ename) from empü upper：將字符串轉(zhuǎn)換成大寫-以大寫方式顯示雇員名select upper (ename) from empü substr：截取字符串-只顯示雇員名的前3個(gè)字母select substr(ename,0,3)

48、 from empü replace：替換字符串-將雇員的金額顯示為*號select ename,replace(sal,sal,*) from empü instr：查找字符串-查找雇員名含有LA字符的信息select * from emp where instr(ename,LA)>02. 日期函數(shù)ü sysdate：返回當(dāng)前session所在時(shí)區(qū)的默認(rèn)時(shí)間-獲取當(dāng)前系統(tǒng)時(shí)間select sysdate from dualü add_months：返回指定日期月份+n之后的值，n可以為任何整數(shù)-查詢當(dāng)前系統(tǒng)月份+2的時(shí)間select add_m

49、onths(sysdate,2) from dual-查詢當(dāng)前系統(tǒng)月份-2的時(shí)間select add_months(sysdate,-2) from dualü last_day：返回指定時(shí)間所在月的最后一天-獲取當(dāng)前系統(tǒng)月份的最后一天select last_day(sysdate) from dualü months_between：返回月份差，結(jié)果可正可負(fù)，當(dāng)然也有可能為0-獲取入職日期距離當(dāng)前時(shí)間多少天select months_between(sysdate, hiredate) from empü trunc：為指定元素而截去的日期值-獲取當(dāng)前系統(tǒng)年，其

50、他默認(rèn)select trunc(sysdate,'yy') from dual-查詢81年2月份入職的雇員select * from emp where trunc(hiredate,'mm')=trunc(to_date('1981-02','yyyy-mm'),'mm')3. 轉(zhuǎn)換函數(shù)ü to_char：將任意類型轉(zhuǎn)換成字符串-日期轉(zhuǎn)換select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual-數(shù)字轉(zhuǎn)換select to_cha

51、r(-100.789999999999,'L99G999D999') from dual ü 數(shù)字格式控制符符號描述9代表一位數(shù)字，如果當(dāng)前位有數(shù)字，顯示數(shù)字，否則不顯示(小數(shù)部分仍然會強(qiáng)制顯示)0強(qiáng)制顯示該位，如果當(dāng)前位有數(shù)字，顯示數(shù)字，否則顯示0$增加美元符號顯示L增加本地貨幣符號顯示.小數(shù)點(diǎn)符號顯示,千分位符號顯示ü to_date：將字符串轉(zhuǎn)換成日期對象-字符轉(zhuǎn)換成日期select to_date('2011-11-11 11:11:11', 'yyyy-mm-dd hh24:mi:ss') from dual 

52、52; to_number：將字符轉(zhuǎn)換成數(shù)字對象-字符轉(zhuǎn)換成數(shù)字對象select to_number('209.976')*5 from dual select to_number('209.976', '9G999D999')*5 from dual 4. 數(shù)學(xué)函數(shù)ü abs：返回?cái)?shù)字的絕對值select abs(-1999) from dual ü ceil：返回大于或等于n的最小的整數(shù)值select ceil(2.48) from dual ü floor：返回小于等于n的最大整數(shù)值select floor(2

53、.48) from dual ü round：四舍五入select round(2.48) from dual select round(2.485,2) from dualü bin_to_num：二進(jìn)制轉(zhuǎn)換成十進(jìn)制select bin_to_num(1,0,0,1,0) from dual 第四章 鎖一、 概述鎖是實(shí)現(xiàn)數(shù)據(jù)庫并發(fā)控制的一個(gè)非常重要的技術(shù)。當(dāng)事務(wù)在對某個(gè)數(shù)據(jù)對象進(jìn)行操作前，先向系統(tǒng)發(fā)出請求，對其加鎖。加鎖后事務(wù)就對該數(shù)據(jù)對象有了一定的控制，在該事務(wù)釋放鎖之前，其他的事務(wù)不能對此數(shù)據(jù)對象進(jìn)行更新操作。在數(shù)據(jù)庫中有兩種基本的鎖類型：排它鎖（Exclusive

54、Locks，即X鎖）和共享鎖（Share Locks，即S鎖）。當(dāng)數(shù)據(jù)對象被加上排它鎖時(shí)，其他的事務(wù)不能對它讀取和修改。加了共享鎖的數(shù)據(jù)對象可以被其他事務(wù)讀取，但不能修改。根據(jù)保護(hù)的對象不同，Oracle數(shù)據(jù)庫鎖可以分為以下幾大類：ü DML鎖（data locks，數(shù)據(jù)鎖），用于保護(hù)數(shù)據(jù)的完整性ü DDL鎖（dictionary locks，字典鎖），用于保護(hù)數(shù)據(jù)庫對象的結(jié)構(gòu)，如表、索引等的結(jié)構(gòu)定義ü 內(nèi)部鎖和閂（internal locks and latches），保護(hù)數(shù)據(jù)庫的內(nèi)部結(jié)構(gòu)二、 DML鎖DML鎖的目的在于保證并發(fā)情況下的數(shù)據(jù)完整性，在Oracle

55、數(shù)據(jù)庫中，DML鎖主要包括TM鎖和TX鎖，其中TM鎖稱為表級鎖，TX鎖稱為事務(wù)鎖或行級鎖。1. 行級鎖當(dāng)事務(wù)執(zhí)行數(shù)據(jù)庫插入、更新、刪除操作時(shí)，該事務(wù)自動獲得操作表中操作行的排它鎖-不允許其他用戶對雇員表的部門編號為20的數(shù)據(jù)進(jìn)行修改select * from emp where deptno=20 for update-不允許其他用戶對雇員表的所有數(shù)據(jù)進(jìn)行修改select * from emp for update-如果已經(jīng)被鎖定，就不用等待select * from emp for update nowait-如果已經(jīng)被鎖定，更新的時(shí)候等待5秒select * from emp for up

56、date wait 52. 鎖模式Ø 0(none)Ø 1(null)Ø 2(rs)：行共享Ø 3(rx)：行排他Ø 4(s)：共享Ø 5(srx)：共享行排他Ø 6(x)：排他數(shù)字越大，鎖級別越高3. 表級鎖當(dāng)事務(wù)獲得行鎖后，此事務(wù)也將自動獲得該行的表鎖(行排他),以防止其它事務(wù)進(jìn)行DDL語句影響記錄行的更新Ø 行共享鎖(RS鎖)：允許用戶進(jìn)行任何操作，禁止排他鎖lock table emp in row share modeØ 行排他鎖(RX鎖)：允許用戶進(jìn)行任何操作，禁止共享鎖lock table

57、emp in row exclusive modeØ 共享鎖(R鎖)：其他用戶只能看，不能修改lock table emp in share modeØ 排他鎖(X鎖)：其他用戶只能看，不能修改，不能加其他鎖lock table emp in exclusive modeØ 共享行排他(SRX鎖)：比行排他和共享鎖級別高，不能添加共享鎖lock table emp in share row exclusive mode4. 鎖兼容性SXRSRXSRXN/ASYNYNNYXNNNNNYRSYNYYYYRXNNYYNYSRXNNYNNYN/YYYYYYY5. 死鎖當(dāng)兩個(gè)事務(wù)需要一組有沖突的鎖，而不能將事務(wù)繼續(xù)下去的話，就出現(xiàn)死鎖。1) 用戶A修改A表，事務(wù)不提交2) 用戶B修改B表，事務(wù)不提交3) 用戶A修改B表，阻塞4) 用戶B修改A表，阻塞Oracle系統(tǒng)能自動發(fā)現(xiàn)死鎖，并會自動選擇工作量最少的事務(wù)進(jìn)行撤銷和