第一篇：oracle數據庫使用總結

Oracle數據庫遇到問題與使用心得總結

一、快速得到存儲過程運行的結果。步驟方法：

1、在c://temp目錄中得到存儲過程調用的參數語句，然后復制。

2、在PL/SQL中打開Test Window窗口，粘貼復制的內容，然后在內容中如下圖增加2個參數（r1和r2）。

3、在Test Window窗口的變量和類型中入下圖那樣設置增加的參數。

4、點Test Window窗口中的圖標，等運行按鈕亮起時，再點擊運行按鈕。

5、在Test Window窗口中值哪列點擊就能查看運行存儲過程的結果。

二、解決數據庫彈出“本地計算機上的OracleOraDb11g_home1TNSListener服務啟動后停止。某些服務在未由其他服務或程序使用時將自動停止”問題。

分析原因：是因為數據庫監聽配置文件添加這句話（如下圖），然后重啟監聽服務OracleOraDb11g_home1TNSListener就報上面的錯誤。

報錯現象：接著打開”開始-》所有程序-》oracle_home-》Net Manager “程序發現不能配置監聽程序 ；啟動監聽服務提示“本地計算機上的OracleOraDb11g_home1TNSListener服務啟動后停止。某些服務在未由其他服務或程序使用時將自動停止”信息

解決方法步驟：

1、刪除這句話。

2、修改為

三、解決數據庫提示“ora-12514 無監聽程序處理”問題。

原因分析：數據庫監聽配置都好好，用sqlplus能正常連接數據庫，但是用PL/SQL連接就報“ora-12514 無監聽程序處理”錯誤。

1、找到數據庫的安裝目錄文件下的listener.log文件（本人的監聽文件放在D:orcladmindiagtnslsnrtzzlistenertrace目錄），發現文件很大有幾個G。

2、在cmd中輸入如下命令

lsnrctl set log_status off;(暫停日志監控)

3、在cmd中通過輸入“D:”進入D盤，然后通過 “cd orcl”進入D:orcl目錄，一直到D:orcladmindiagtnslsnrtanzizilistenertrace為止

4、再輸入命令：copy listener.log listener.log.20150114（復制文件）

5、輸入命令：echo >listener.log(清空listener文件)

6、lsnrctl set log_status on;（啟動日志監控）

四、oralce查詢鎖定進程和關閉window系統中的鎖定進程

1、查詢鎖定進程語句： SELECT /*+ rule */ s.username, decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL)LOCK_LEVEL, o.owner, o.object_name, o.object_type, s.sid, s.serial#, s.terminal, s.machine, s.program, s.osuser FROM v$session s, v$lock l, dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+)AND s.username is NOT Null;

2、殺掉鎖定的進程

alter system kill session 'sid,serial#';找到鎖定進程對應的spid select a.SID,a.USERNAME,a.status,a.process,b.SPID from v$session a,v$process b where a.PADDR=b.ADDR and a.sid='sid';

4、在cmd中輸入關閉鎖定進程命令： orakill orcl spid;

五、oracle數據庫中的jobs

1、查詢所有的jobs select * from user_jobs;

2、查看怎在運行的jobs select * from dba_jobs_running;

六、數據庫表空間增加空間

1、允許已存在的數據文件自動增長

ALTER DATABASE DATAFILE 'D:orcladminoradataorclAPP03.DBF' AUTOEXTEND ON NEXT 50M MAXSIZE 20480M;手工改變已存在數據文件的大小

ALTER DATABASE DATAFILE 'D:orcladminoradataorclAPP02.DBF' RESIZE 20480M;

七、數據庫服務器端數據的導出與導入（必須在數據庫服務器端使用），導出和導入使用服務器端命令好處是速度快，不會出現空表不導出現象。

1、cmd

2、expdp kdcrm/1@orcl version=11.1.0.6.0 數據庫名/數控密碼@實例名 要導入數據庫的版本號

（版本號一般是用在高版本數據庫向低版本數據庫導入數據。）

3、查看導出日志，根據日志找到導出文件

4、進入D:orcladminadminorcldpdump目錄，把EXPDAT.DMP壓縮下。

5、把壓縮之后的文件從服務器拷入本地計算機。

6、找到本地計算機的安裝目錄下的D:orcladminadminorcldpdump文件夾，把壓縮的文件解壓到此地。

7、在本地計算機中輸入cmd

8、在本地計算機創建表空間，通過PL/SQL工具用oracle數據庫的系統管理員進入數據庫。CREATE TABLESPACE CMBC_PMS_TBS

LOGGING

DATAFILE 'D:orcladminoradataorclCMBC_PMS_TBS.ora'

SIZE 100M AUTOEXTEND

ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

--Create the user create user CMBC_PMS identified by 1 default tablespace CMBC_PMS_TBS temporary tablespace TEMP profile DEFAULT password expire;--Grant/Revoke role privileges grant connect to CMBC_PMS;grant dba to CMBC_PMS;grant resource to CMBC_PMS;--Grant/Revoke system privileges grant create any table to CMBC_PMS;grant create any view to CMBC_PMS;grant create database link to CMBC_PMS;grant create table to CMBC_PMS;grant debug connect session to CMBC_PMS;grant drop any table to CMBC_PMS;grant select any table to CMBC_PMS;grant unlimited tablespace to CMBC_PMS;

CREATE TABLESPACE TS_CRM_01

LOGGING

DATAFILE 'D:orcladminoradataorclTS_CRM_01.dbf'

SIZE 100M AUTOEXTEND

ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;CREATE TEMPORARY TABLESPACE ts_tmp_01

TEMPFILE 'D:orcladminoradataorclts_tmp_01.dbf'

SIZE 100M

AUTOEXTEND ON

NEXT 100M maxsize 2000M;

CREATE TABLESPACE TS_CC_01

LOGGING

DATAFILE 'D:orcladminoradataorclTS_CC_01.dbf'

SIZE 100M AUTOEXTEND

ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE TS_TA_01

LOGGING

DATAFILE 'D:orcladminoradataorclTS_TA_01.dbf'

SIZE 100M AUTOEXTEND

ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

--系統管理員

select * from dba_directories;CREATE OR REPLACE DIRECTORY dir_crm_db AS 'D:orcldb_bak';--drop directory dir_crm_db;CREATE OR REPLACE DIRECTORY dir_dp AS 'D:orcldp_bak';--drop directory dir_dp;CREATE OR REPLACE DIRECTORY expdp_dir AS 'D:orcldir_bak';--drop directory expdp_dir;

9、在本地計算機創建用戶,通過PL/SQL工具用oracle數據庫的系統管理員進入數據庫。--Create the user

create user KDCRM identified by 1

default tablespace TS_CRM_01

temporary tablespace TS_TMP_01

profile DEFAULT

password expire;--Grant/Revoke object privileges

grant select, insert, update, delete grant select, insert, update, delete KDCRM;grant select, insert, update, delete M;grant select, insert, update, delete CRM;grant select, insert, update, delete KDCRM;grant select, insert, update, delete REHOLD to KDCRM;grant select, insert, update, delete RM;grant select, insert, update, delete grant select, insert, update, delete NFO to KDCRM;grant select, insert, update, delete KDCRM;grant select, insert, update, delete KDCRM;grant select, insert, update, delete ACT to KDCRM;grant select, insert, update, delete;grant select, insert, update, delete grant select, insert, update, delete CRM;grant select, insert, update, delete M;

on CMBC_PMS.BENEFIT to KDCRM;on CMBC_PMS.COLLATERAL_CONTRACT toon CMBC_PMS.ENTRUST_AGENCY to KDCRon CMBC_PMS.FINANCE_CONTRACT to KDon CMBC_PMS.FINANCE_COUNTERPARTY toon CMBC_PMS.FINANCE_COUNTERPARTY_SHAon CMBC_PMS.FINANCE_PROJECT to KDCon CMBC_PMS.FUND_RAISE to KDCRM;on CMBC_PMS.GENERAL_FINANCE_PROJECTIon CMBC_PMS.GUARANTEE_CONTRACT to on CMBC_PMS.INVESTMENT_ADVISER to on CMBC_PMS.INVESTMENT_ADVISER_CONTRon CMBC_PMS.KD_RIGHT_ROLE to KDCRMon CMBC_PMS.PMS_PROJECT to KDCRM;on CMBC_PMS.PMS_PROJECT_USER to KDon CMBC_PMS.SYS_BUSINESSNO to KDCRgrant select, insert, update, delete on CMBC_PMS.SYS_REPORTFIELD to KDCRM;grant select, insert, update, delete on CMBC_PMS.WF_BUSINESSDATA to KDCRM;grant select, insert, update, delete on CMBC_PMS.WF_ROLE_FLOW_AUTH to KDCRM;grant select, insert, update, delete on CMBC_PMS.WF_ROUGH_DRAFT to KDCRM;grant read, write on directory SYS.DIR_CRM_DB to KDCRM;grant execute, read, write on directory SYS.DIR_DP to KDCRM with t option;grant read, write on directory SYS.EXPDP_DIR to KDCRM;--Grant/Revoke role privileges grant connect to KDCRM;grant dba to KDCRM;grant resource to KDCRM;--Grant/Revoke system privileges grant create any table to KDCRM;grant create any view to KDCRM;grant create database link to KDCRM;grant create table to KDCRM;grant debug connect session to KDCRM;grant drop any table to KDCRM;grant select any table to KDCRM;grant unlimited tablespace to KDCRM;

8、impdp kdcrm/1@orcl remap_schema=kdcrm:kdcrm dumpfile=EXPDAT.DMP 數據庫名/密碼@實例名 導出的數據庫名:導入的數據庫名 數據庫備份文件名

gran

第二篇：Oracle數據庫使用心得

Oracle數據庫使用心得

092909 謝弘毅

經過助教對oracle和sybase數據庫的講解后，我下載使用和體驗了這兩個數據庫，但主要的還是使用了Oracle數據庫，經過一段時間的使用，對其使用有了一定的心得體會，并且通過閱讀資料對其現在的前景狀況有了一定的了解。

【選擇Oracle的原因】

（1）在Gartner/Dataquest報告中Oracle依然在主流操作系統Unix和Linux, Windows NT/2000/XP上的關系數據庫市場上占據絕對的領先地位，在UNIX上占63%, Windows+Unix上占49.9%。在關系型數據庫市場（包括AS/400,Mainframe），Oracle以39.8%仍然處于領先地位。

（2）Oracle數據庫依然是Fortune 100公司的首選數據庫,其中51%的Fortune 100公司選用Oracle Database作為構建企業的傳統應用和電子商務平臺。

（3）Oracle數據庫與Sybase數據庫相比，無論從體系架構、并行支持、完整性控制等各方面均有很大的優勢。

【Oracle相比于sybase的優勢】（1）體系結構的比較

Oracle采用多線索多進程體系結構，直接在內核中支持分布式數據庫操作、多線索處理、并行處理以及聯機事務處理等。Sybase采用單進程多線索體系結構，其核心是SQL Server

（2）多線索多進程與單進程多線索的比較

Oracle和Sybase都采用多線索。Oracle多服務器進程結構，能實現數據庫事務的并行處理，提高并發事務處理的響應速度。并且多服務器結構具有非常靈活的擴充性，當硬件平臺處理能力提高時，服務器進程的個數也能隨之增加，數據庫性能也隨之提高。Sybase采用單進程多線索方式。當并發用戶數達到一定的數量時，會引起系統處理性能大大下降，和服務器進程瓶頸及死鎖等現象；同時系統的可擴展余地非常狹窄。

（3）聯機事務處理

Oracle的多線索技術在相同硬件環境下，所支持的用戶數是其他數據庫 系統的5至10倍。

【我對Oracle一些概念的理解】

（1）ORACLE SERVER

一個運行著的ORACLE數據庫就可以看成是一個ORACLE SERVER，該SERVER由數據庫(Database)和實例(Instance)組成，在一般的情況下一個ORACLE SERVER包含一個實例和一個與之對應的數據庫。

一系列物理文件（數據文件，控制文件，聯機日志等）的集合或與之對應的邏輯結構（表空間，段等）被稱為數據庫，簡單的說，就是一系列與磁盤有關系的物理文件的組成。ORACLE內存結構和后臺進程被成為數據庫的實例，一個實例最多只能安裝(Mount)和打開(Open)在一個數據庫上，負責數據庫的相應操作并與用戶交互。

實例與數據庫的關系如下圖所示：

圖一 ORACLE SERVER

（2）ORACLE內存結構(Memory structure)Oracle內存結構主要可以分共享內存區與非共享內存區，共享內存區主要包含SGA(System Global Area)，非共享內存區主要由PGA(Program Global Area)組成，可以用如下圖形表示。

圖二 ORACLE MEMOERY STRUCTRUE

（3）后臺進程(Background process)后臺進程是Oracle的程序，用來管理數據庫的讀寫，恢復和監視等工作。Server Process主要是通過他和user process進行聯系和溝通，并由他和user process進行數據的交換。在Unix機器上，Oracle后臺進程相對于操作系統進程，也就是說，一個Oracle后臺進程將啟動一個操作系統進程；在Windows機器上，Oracle后臺進程相對于操作系統線程，打開任務管理器，我們只能看到一個ORACLE.EXE的進程，但是通過另外的工具，就可以看到包含在這里進程中的線程。

Oracle系統有5 個基本進程他們是：

? DBWR(數據文件寫入進程)：將修改過的數據緩沖區的數據寫入對應數據文件，維護系統內的空緩沖區。? LGWR(日志文件寫入進程)：將重做日志緩沖區的數據寫入重做日志文件。

? SMON(系統監護進程)：工作主要包含：清除臨時空間，在系統啟動時，完成系統實例恢復，聚結空閑空間，從不可用的文件中恢復事務的活動，OPS中失敗節點的實例恢復等。

? PMON(用戶進程監護進程)：主要用于清除失效的用戶進程，釋放用戶進程所用的資源。

? CKPT(檢查點進程,同步數據文件, 日志文件,控制文件)：同步數據文件，日志文件和控制文件，由于DBWR/LGWR的工作原理，造成了數據文件，日志文件，控制文件的不一至，這就需要CKPT進程來同步。CKPT會更新數據文件/控制文件的頭信息。

（4）ORACLE的物理結構 1)數據文件

每一個ORACLE數據庫有一個或多個物理的數據文件(data file)。一個數據庫的數據文件包含全部數據庫數據。邏輯數據庫結構(如表、索引)的數據物理地存儲在數據庫的數據文件中。數據文件有下列特征：

? 一個數據文件僅與一個數據庫聯系。? 一旦建立，數據文件不能改變大小

? 一個表空間（數據庫存儲的邏輯單位）由一個或多個數據文件組成。

數據文件中的數據在需要時可以讀取并存儲在ORACLE內存儲區中。例如：用戶要存取數據庫一表的某些數據，如果請求信息不在數據庫的內存存儲區內，則從相應的數據文件中讀取并存儲在內存。當修改和插入新數據時，不必立刻寫入數據文件。為了減少磁盤輸出的總數，提高性能，數據存儲在內存，然后由ORACLE后臺進程DBWR決定如何將其寫入到相應的數據文件。2)日志文件

每一個數據庫有兩個或多個日志文件（redo log file）的組，每一個日志文件組用于收集數據庫日志。日志的主要功能是記錄對數據所作的修改，所以對數據庫作的全部修改是記錄在日志中。在出現故障時，如果不能將修改數據永久地寫入數據文件，則可利用日志得到該修改，所以從不會丟失已有操作成果。

日志文件主要是保護數據庫以防止故障。為了防止日志文件本身的故障，ORACLE允許鏡象日志(mirrored redo log)，以致可在不同磁盤上維護兩個或多個日志副本。

日志文件中的信息僅在系統故障或介質故障恢復數據庫時使用，這些故障阻止將數據庫數據寫入到數據庫的數據文件。然而任何丟失的數據在下一次數據庫打開時，ORACLE自動地應用日志文件中的信息來恢復數據庫數據文件。3)控制文件

每一ORACLE數據庫有一個控制文件(control file)，它記錄數據庫的物理結構，包含下列信息類型：

? 數據庫名；

? 數據庫數據文件和日志文件的名字和位置； ? 數據庫建立日期。

? 為了安全起見，允許控制文件被鏡象。

每一次ORACLE數據庫的實例啟動時，它的控制文件用于標識數據庫和日志文件，當著手數據庫操作時它們必須被打開。當數據庫的物理組成更改時，ORACLE自動更改該數據庫的控制文件。數據恢復時，也要使用控制文件。

【我對Oracle圖形界面使用心得】

Oracle數據庫是一個大型系統，具有強大的數據定義、操縱、組織和存儲和維護等管理功能，還具有一定的數據通信接口功能，且分有圖形操作界面和文本操作界面。聽老師說學習數據庫技術就是要學習其語言，熟悉掌握文本操作能有效提高數據庫技術。但作為一個初次與實體數據庫接觸的學生，我更喜歡圖形操作界面。在幾次嘗試中我有以下體會： 【優點】

（1）圖形界面給人的第一印象就是直觀，相比起純語言的模式，圖形界面能更好的體現你的操作。因為學習數據庫以來，老師所講解的都是一些查詢語言，和命令行之類的語言的知識。而通過直觀的圖形界面，使我數據庫的基本結構（物理存儲結構、邏輯存儲結構）有了更為深刻的了解體會，看到了oracle數據庫的一些優秀的特性。和一些優秀的操作功能。

（2）使用圖形界面能夠更加快捷且準確的行使數據庫的各種操作。因為oracle數據庫支持圖形等操作，使得一些需要用SQL語句或是命令行的操作可以通過簡單的拖放點擊圖形即可完成，這一方面減少了錯誤的發生，一方面也簡化了工作任務。

（3）圖形操作界面包含了語句操作的所有功能，而且還能通過教程或是文檔獲得幫助，這使得你能在使用的過程中克服一些困難。最重要的是，在圖形界面的使用過程中你可以直觀清晰的看到各種操作產生的結果，加深你對數據庫中各種關系的理解。

【缺點】

（1）使用圖形界面不能做到像語言命令操作的多變性，一部分操作通過語言能夠簡單化，例如對于很多對象的查詢和修改，就比較適合用語言操作，這體現了圖形界面操作的局限性。

（2）圖形界面所占用系統資源大，不利于多用戶的并行操作。也減低了系統的處理能力。最關鍵的是圖形界面不容易維護和修改對象(3）無邏輯

圖形操作是一個非結構化的操作集合，先后順序完全掌握在操作者手中。這種操作過程很容易出錯，而文本操作著可以利用語句有效控制。

【總結】

雖然使用oracle一段時間，但是要學的東西實在是太多了，不僅是數據庫軟件本身，還有SQL語言也是要好好學的，雖然說覺得圖形界面好，方便，但是如果最基本的語言沒學好，還是不能有所作為的。

第三篇：Oracle數據庫總結范文

創建表及命名規則？

表名和列名:

必須以字母開頭 必須在1–30個字符之間

必須只能包含A–Z, a–z, 0–9, _, $, 和# 必須不能和用戶定義的其他對象重名 必須不能是Oracle 的保留字 Oracle默認存儲是都存為大寫

增刪改查語法？

增加: 例如:使用INSERT語句往customers表中插入數據，指定相關列和值 INSERT INTO customers(customer_id, first_name, last_name, dob, phone)VALUES(6, 'Fred', 'Brown', '01-1月-1970', '800-555-1215');

如果為表所有列都指定值，那么可以忽略列清單

INSERT INTO customersVALUES(6, 'Fred', 'Brown', '01-1月-1970', '800-555-1215');

可以使用NULL為某些列指定空值

INSERT INTO customersVALUES(8, 'Sophie', 'White', NULL, NULL);

查詢：select * fromcustomers；或者select字段 fromcustomerswhere條件 刪除：deletefromcustomerswhere條件

更改：update customersset name = 'xiaoming',age = ‘16’(更改多個字段時候用逗號隔開)where 條件

對查詢結果進行排序？

語句：select * fromcustomersorderby字段 desc；

（asc（升序），desc（降序）如果不寫，默認升序）

NULL值了解么？

NULL值表示未知的值。它是一個特殊的值，但并不是空字符串，NULL值表示該列是未知的。當某些查詢語句在輸出結果列上看不到值的時候，可能就是NULL值

NVL（）和NVL2（）：

NULL值被查詢出來的時候沒有顯示信息，如何告知用戶這是空字符串還是NULL，這可以通過NVL()函數來進行處理

NVL(x，value)是有value顯示本身，null顯示為替換的value NVL2(x,value1,value2)是如果x不為NULL值，返回value1，否則返回value2 例程：

select name,nvl2(email,'已知','未知')from student;【代碼含義：代表如果email字段中有值，則顯示已知，null則顯示未知，如果想顯示本來的查詢結果select name,nvl(email,'未知')from student;】 update student set name = replace(name,'小','大');【代碼含義：代表將STUDENT表中NAME 字段中如果含有小字，那么就將小字替換為大字（操作的不是顯示結果，而是將表中數據進行更改）】

Oracle中的簡單函數？

字符串函數

? concat：將x和y拼接起來，并返回新字符串

例程：

select concat(first_name,'-'||last_name)姓名 from customers;? Instr字符查找，從1開始。

select instr('asdbcrdbewqrbmde','b')from dual;select instr('asdbcrdbewqrbmde','b'，5，2)from dual;【代表從第5個字符開始，第二個b所在的位置】

? Ltrim : LTRIM(x，[trim_string])從x字符串左側去除所有的trim_string字符串，如果沒有指定trim_string字符串，則默認為去除左側空白字符

? Rtrim RTRIM(x，[trim_string])從x字符串右側去除所有的trim_string字符串，如果沒有指定trim_string字符串，則默認為去除右側空白字符 ? Trim TRIM(trim_string FROM x)從x字符串兩側去除trim_string字符串

? Replace REPLACE(x, search_string, replace_string)從字符串x中搜索search_string字符串，并使用replace_string字符串替換。用select執行并不會修改數據庫中原始值，但是用update執行可以修改。

? Substr SUBSTR(x, start，[length])返回字符串中的指定的字符，這些字符從字符串的第start個位置開始，長度為length個字符；如果start是負數，則從x字符串的末尾開始算起；如果length省略，則將返回一直到字符串末尾的所有字符

例程：

select substr('abcd月fg',4,2)from dual;（結果：d月）select substr('abcdefg',-2)from dual;（結果：fq）

日期函數

? Sysdate 例程：

Selectsysdatefromdual；

Select to_char(sysdate, 'yyyy-mm-dd-hh-mm-ss')from dual;

? Add_months(d1,n1)? last_day()：

轉換函數

? To_char TO_CHAR(x，[ format])將x轉化為字符串。format為轉換的格式，可以為數字格式或日期格式

select to_char(sysdate,'yyyy-mm-dd')from dual;【一般在查詢時候使用，使返回的值成為指定格式】

? to_date TO_DATE(x，[format])將x字符串轉換為日期

insert into student values(seq_stu.nextval,'小明',to_date('1992-2-18','yyyy-mm-dd'),default,'北京',null);【一般在添加使用】 返回所查詢的值中最后一個日期數據。

聚合函數

? Avg:平均數 ? Sum：求和 ? Max：最大值 ? Min：最小值 ? Count：返回統計的行數 ? Round：四舍五入

例程：

select round(avg(bid),1)from bug;分組了解么？

有時需要對表中的行進行分組，然后統計每組的信息，可以使用GROUP BY進行分組，然后再對每組進行統計。

(1)可以使用GROUP BY對多個列進行分組

例：

SELECT product_id, avg(customer_id)FROM purchases GROUP BY product_id;

(2)可以對分組后的行使用聚集函數，聚集函數會統計每組中的值，對于每組分別統計后返回一個值 例:

SELECT

product_type_id，BY

COUNT(ROWID)FROM

BY

productsGROUP product_type_id;注意:

product_type_idORDER a）如果查詢中使用了聚集函數，被查詢的列未使用聚集函數處理，那么這些列必須出現在GROUP BY子句后，否則，會提示ORA-00937錯誤

b)不能使用聚集函數作為WHERE子句的篩選條件，否則，會提示ORA-00934錯誤

c)可以使用HAVING子句過濾分組后的行

SELECT...FROM...WHERE GROUP BY...HAVING...ORDER BY...;(GROUP BY使用時可以不使用HAVING，但是使用HAVING時必須有GROUP BY才有意義)

(3)同時使用WHERE, GROUP BY和HAVING

a)首先，執行WHERE篩選掉不符合條件的行 b)然后，將符合條件的行使用GROUP BY進行分組 c)最后，使用HAVING對分組統計的結果進行再次篩選 例:

SELECT product_type_id, AVG(price)FROM products

WHERE price < 15

GROUP BY product_type_id HAVING AVG(price)> 13 ORDER BY product_type_id;

表的約束條件？

目的：

確保表中數據的完整性。

常用的約束類型： 主鍵約束(PRIMARY KEY)：要求主鍵列數據唯一，并且不允許為空 非空約束(NOT NULL)：指定的列的值不允許為空

唯一鍵約束(UNIQUE)：要求該列唯一，允許為空，但只能出現一個空

值

檢查約束(CHECK)：指定表中一列或多列可以接受的數據值格式 默認約束(DEFAULT)：指定某列的默認值

外鍵約束(FOREIGN KEY)：用于建立和加強兩個表數據之間連接的一

列或多列。通過將表中的主鍵列添加到另一個表中。可以創建兩個表之間的連接。這個主鍵的列就稱為第二個表的外鍵。外鍵約束就可以確保添加到外鍵表中的任何行都在主表中都存在相應的行

多表查詢？

不同的數據存儲在不同的表中，通常要查詢多張表才能找到需要的數據

例程： SELECT products.name, product_types.name FROM products, product_types WHERE

products.product_type_id

= product_types.product_type_id AND products.product_id = 3;

products表和product_types表相關字段會用在SELECT語句及WHERE子句上，可以給表起別名，提高代碼可讀性、降低書寫難度 例程：

SELECT p.name, pt.name FROM products p, product_types pt WHERE p.product_type_id = pt.product_type_id AND p.product_id = 3 注意：

如果查詢兩張表，并且沒有定義連接條件，那么查詢的結果集是兩表相乘的結果，這樣的情況稱之為笛卡爾乘積。總結：多表查詢WHERE時，連接次數=查詢時連接表的數量-1

常見的三種連接類型：

內連接：

內連接返回的行只有滿足連接條件才會返回。如果連接條件的列中有NULL值，那么該行則不會返回 外連接：

外連接返回的行滿足連接條件，也包括在連接條件的列包含空值的行

自連接：

連接的表為同一張表

子查詢？

子查詢是嵌入到另一個SELECT語句中的一個SELECT語句。通過使用子查詢，可以使用簡單的語句組成強大的語句。當需要從表中選擇行，而選擇條件卻取決于該表自身中的數據時，子查詢非常有用。

單行子查詢:（1）可以將另外一個查詢作為WHERE子句的子查詢

例：查詢尾名是‘Brown’的首名和尾名

SELECT first_name, last_name FROM customers

WHERE customer_id =

(SELECT customer_id FROM customers WHERE last_name = 'Brown');(2)在單行子查詢還可以使用其他比較運算符，如<>、<、>、<=和>= 例：查詢價格大于平均價格的商品編號、名稱及價格

WHERE子句中使用>，以及子查詢中使用AVG()聚集函數

SELECT product_id, name, price FROM products WHERE price >(SELECT AVG(price)FROM products);(3)在HAVING子句中使用子查詢

HAVING是在分組統計后用于過濾行，同樣在HAVING子句后面可以跟子查詢。單行子查詢將返回結果用于HAVING子句過濾分組統計的行

例如：查詢平均價格小于最大平均值的商品編號及平均值

SELECT product_type_id, AVG(price)FROM products GROUP BY product_type_id HAVING AVG(price)<(SELECT MAX(AVG(price))FROM products GROUP BY product_type_id)ORDER BY product_type_id;

分頁查詢？

可以通過ROWNUM來實現。

序列？

序列是一個數據庫對象，用于生成一系列的整數。

索引？

索引是與表關聯的可選結構。可以創建索引以加快對表執行SQL語句的速度。就像書的索引可以幫助我們更快速的查找信息一樣，Oracle中的索引也提供了一種更快地訪問表數據的途徑。

視圖？

視圖是基于一張表或多張表或另外一個視圖的邏輯表。視圖不同于表，視圖本身不包含任何數據。表是實際獨立存在的實體，是用于存儲數據的基本結構。而視圖只是一種定義，對應一個查詢語句。視圖的數據都來自于某些表，這些表被稱為基表。數據庫中只在數據字典中存儲對視圖的定義。

第四篇：Oracle數據庫學習總結

Oracle數據庫學習總結

1.set linesize xx;設置行間距，常用數值有100，200，300

2.set pagesize xx;設置每頁顯示行數

3.ed x;表示新建一個x.sql文件，通過文件編輯SQL語句，然后用@x命令可以調用剛才的命令

4.CONN username/password;命令可以建立用戶的連接，需要注意的是sys用戶是超級管理員，連接是時需要在末尾加上AS SYSDBA 以系統管理員的身份進行連接

5.如果表是歸某個用戶特有的，在查詢的時候需要加上用戶名 即以 用戶名.表名 的格式查詢

6.SHOW USER;命令可以顯示當前連接的用戶名

7.SELECT * FROM tab;可以顯示當前用戶下的所有數據表

8.“ / ”表示重復執行上一次的SQL命令操作

9.SELECT xx別名,xx 別名 FROM xx;搜索指定列名，并指定別名，方便顯示

10.關鍵字DISTINCT 可以消除重復值 如 SELECT DISTINCT xx FROM xx;

11.Oracle中提供的字符串連接操作，使用“||”表示，相當于Java的“+”普通字符用“ ' ” 括起來

如: SELECT'員工姓名是'||ename||'員工卡號是'||empnoFROM emp;

12.查詢語句 BETWEEN xx AND xx 是包括邊界的13.查詢日期的時候要加上''把日期引起來

例如：SELECT * FROM emp WHERE hiredate BETWEEN '1-1月-81' AND '08-9月-81';

14.模糊查詢中“%”可以匹配任意長度的內容，“_”可以匹配一個長度的內容，如果沒輸入模糊查詢關鍵字，那么默認查詢全部數據，like關鍵字可以用在任何地方，可以匹配數字、字符、日期等。

15.SQL中不等于可以用“<>”或者“!=”表示

16.ORDER BY語句中 ASC表示升序，DESC表示降序，在沒指定的時候默認按照升序排序

17.Oracle中的單行函數有如下，默認的所有的函數都要到表中執行，加上關鍵字DUAL只會產生一個臨時表

UPPER('xxx')將小寫轉換為大寫

LOWER('xxx')將大寫轉換為小寫

INITCAP('xxx')將首字母大寫

CONCAT('','')字符串連接

SUBSTR('xxx',x,x)字符串截取，從0或1開始截取效果是一樣的，因為Oracle比較智能,要是輸入的參數為負數，則表示倒著截取

LENGTH('xxx')字符串長度

REPLACE('xxx','x','x')字符串替換

ROUND(xxx,xx)四舍五入 xxx需要四舍五入的數值，xx保留的小數位，可以加負數 TRUNC(xxx)截斷操作，默認小數點后的全部截斷，也可以指定小數點保留位數如TRUNC(789.536,2)得到的結果是789.53，也可以加負數如TRUNC(789.536,-2)結果是700

18.SELECT sysdate FROM DUAL;可以求出當前的日期

19.Oracle 中提供了以下日期函數支持：

MONTHS_BETWEEN()求出給定日期范圍的月數

ADD_MONTHS(xxx,xxx)在指定日期加上指定的月數

NEXT_DATE(xxx,'')求出下一個給定日期數

TO_CHAR()可以將年、月、日進行分割

例如

TO_CHAR(hirdate,'yyyy')year,TO_CHAR(hirdate,'mm')months,TO_CHAR(hirdate,'dd')day 還可以對時間進行格式化輸出 如TO_CHAR(hirdate,'yyyy-mm-dd')

TO_CHAR(hirdate,'fmyyyy-mm-dd')可以去掉前導0

TO_CHAR()還可以對數字進行格式化 如

SELECT ename,TO_CHAR(SAL,'99,999')FROM emp;

注意：一定要用9來表示

$表示美元符號，L表示Local的縮寫，以本地語言進行金額顯示

TO_NUMBER()將字符串變為數字

TO_DATE()將字符串變為Date類型 例如 SELECT TO_DATE('2009-12-8','yyyy-mm-dd')FROM dual;

TO_NVL()可以將NULL的內容變為指定的內容

DECODE()相當于Java的if else else語句

例如SELECT DECODE(1,1,'內容是1',2,'內容是2',3,'內容是3')FROM dual;將輸入 內容是1

20.左右連接

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d WHERE

e.deptno(+)=d.deptno;此例中是右連接，以deptno表為準。

21.SQL1999語法

CROSS JOIN 交叉連接 會產生笛卡爾積

NATURAL JOIN 自然連接 自動進行關聯字段匹配 可以消除笛卡爾積

USING 子句：直接關聯操作列 如 SELECT * FROM emp e JOIN dept USING(deptno)WHERE deptno=30;

ON 子句 用戶自己編寫連接條件

LETF JOIN/RIGHT JOIN 左右連接

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e right OUTER JOIN dept d ON(e.deptno=d.deptno);

22.分組查詢GROUP BY 放在where之后

常見的組函數有

COUNT();

MAX();

MIN();

AVG();

SUM();

用法如下：SELECT deptno,count(empno)FROM emp GROUP BY deptno;

語法：SELECT deptno,empno,count(empno)FROM emp GROUP BY deptno;是錯誤的，原因是使用分組函數的時候，不能出現分組函數和分組條件以外的字段。

語法：SELECT deptno,count(empno)FROM emp；是錯誤的，原因是不使用分組的時候，則只能單獨使用分組函數

分組函數只能在分組中使用，不允許子啊where語句中個使用，要使用個分組條件可以加上HAVING

例如：SELECT deptno,avg(sal)FROM emp GROUP BY deptno having avg(sal)>2000;注意：分組函數可以嵌套使用，但是在組函數嵌套使用的時候不能再出現分組條件的查詢語句

如下語法是錯誤的：SELECT deptno,max(avg(sal))FROM emp GROUP BY deptno;不能出現deptno

如下語法是正確的：SELECT max(avg(sal))FROM emp GROUP BY deptno;

23.子查詢中

>ANY 比里面的最小值大

=ANY 與IN用法相同

>ALL 比里面的最大值大

24.表復制

CREATE TABLE myemp AS SELECT * FROM emp;既復制表結構，又復制表內容

CREATE TABLE myemp AS SELECT * FROM emp where 1=2;后面的條件不可能成立，只復制表結構

25.Oracle 中常用的數據類型

VARCHAR、VARCHAR2 代表一個字符串，有長度限制，為255

NUMBER 分為兩種

1)NUMBER(n)代表一個整數，數字的長度是n,可以使用INT

2)NUMBER(m,n)代表一個小數，小數長度為n，整數長度為m-n，可以使用FLOAT

DATE 代表日期的類型，日期要按照標準的日期格式進行存放

CLOB 大對象，表示大文本數據,一般可以存放4G的文本

BLOB 大對象，表示二進制數據最大可以存放4G，例如存放歌曲、電影、圖片

26.表的創建

CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200),birthdate DATE,age NUMBER(4),sex VARCHAR2(2)DEFAULT '男');

27.插入數據

INSERT INTO person(pid,name,birthdate,age)VALUES('222','里斯

',TO_DATE('1989-02-09','yyyy-mm-dd'),45);

28.更改表中數據

增加表結構：ALTER TABLE person ADD(address VARCHAR2(50)DEFAULT '暫無地址');

修改已存在的列：ALTER TABLE person MODIFY(name VARCHAR2(40)DEFAULT '無名氏');

29.表的重命名（只能在Oracle中使用）

RENAME XXX TO XXX;

30.約束（主要分為5類）

1）主鍵約束 主鍵表示是一個唯一的標識。本身不能為空

2）唯一約束 在一個表中只允許建立一個主鍵約束，而其他列如果不希望重復值的話，則可以使用唯一約束

3）檢查約束 檢出一個列的內容是否合法

4）非空約束

5）外鍵約束 在兩張表中進行約束的操作 刪除時應該先刪除子表，再刪除父表 創建主鍵：

語法1

CREATE TABLE person（pid VARCHAR2(18)PRIMARY KEY,name VARCHAR2(200),birthdate DATE,age NUMBER(4),sex VARCHAR2(2)DEFAULT '男');

語法2：

CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200),birthdate DATE,age NUMBER(4),sex VARCHAR2(2)DEFAULT '男',CONSTRAINT p_id PRIMARY KEY(pid));

創建非空約束

CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4),sex VARCHAR2(2)DEFAULT '男');

創建唯一約束

語法一

CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200)UNIQUE NOT NULL,birthdate DATE,age NUMBER(4),sex VARCHAR2(2)DEFAULT '男');

語法二

CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4),sex VARCHAR2(2)DEFAULT '男',CONSTRAINT p_name UNIQUE(name),);

創建檢查約束

語法一

CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4)NOT NULL CHECK(age BETWEEN 0 AND 150),sex VARCHAR2(2)DEFAULT '男' CHECK(sex IN('男','女','中')),);

語法二

CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4)NOT NULL ,sex VARCHAR2(2)DEFAULT '男' ,CONSTRAINT p_age CHECK(age BETWEEN 0 AND 150),CONSTRAINT p_sex CHECK(sex IN('男','女','中')));

創建外鍵約束

--person表

CREATE TABLE person（pid VARCHAR2(18)PRIMARY KEY,name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4)NOT NULL ,sex VARCHAR2(2)DEFAULT '男');

--book表

CREATE TABLE book（bid NUMBER PRIMARY KEY,bname VARCHAR2(30),bprice NUMBER(5,2),pid VARCHAR2(18),CONSTRAINT b_pid_fk FOREIGN KEY(pid)REFERENCES person(pid));

對于刪除，應該先刪除book表再刪除person表

也可以使用級聯刪除,強制刪除某張表

DROP TABLE person CASCADE CONSTRAINT;

設置外鍵約束級聯刪除

--person表

CREATE TABLE person（pid VARCHAR2(18)PRIMARY KEY,name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4)NOT NULL ,sex VARCHAR2(2)DEFAULT '男');

--book表

CREATE TABLE book（bid NUMBER PRIMARY KEY,bname VARCHAR2(30),bprice NUMBER(5,2),pid VARCHAR2(18),CONSTRAINT b_pid_fk FOREIGN KEY(pid)REFERENCES person(pid)ON DELETE CASCADE);

31.修改約束

如果一張表已經建立完成之后，則可以為其添加約束

ALTER TABLE 表名稱 ADD CONSTRAINT 約束名稱 約束類型(約束字段);

關于約束名稱的命名最好要統一：

PRIMARY KEY ：主鍵字段_PK

UNIQUE:字段_UK

CHECK:字段_CK

FOREIGH KEY:父字段_子字段_FK

例如：

DROP TABLE person;

CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4)NOT NULL ,sex VARCHAR2(2)DEFAULT '男');

ALTER TABLE person ADD CONSTRAINT pid_PK PRIMARY KEY(pid);

ALTER TABLE person ADD CONSTRAINT name_UK UNIQUE(name);

ALTER TABLE person ADD CONSTRAINT age_CK CHECK(age BETWEEN 1 AND 150);CREATE TABLE book（bid NUMBER ,bname VARCHAR2(30),bprice NUMBER(5,2),pid VARCHAR2(18));

ALTER TABLE book ADD CONSTRAINT book_PK PRIMARY KEY(bid);

ALTER TABLE book ADD CONSTRAINT pid_FK FOREIGN KEY(pid)REFERENCES person(pid);刪除約束

ALTER TABLE person DROP CONSTRAINT name_UK;

ALTER TABLE person DROP CONSTRAINT age_CK;

第五篇：Oracle數據庫 知識點總結

1.constraint約束：

alter table [table_name] add constraint [pk_name] primary key(pkname);//添加主鍵

alter table [table_name] drop constraint [pk_name];//刪除主鍵

alter table [table_name] add constraint [fk_name] foreign key(fkname)references [tablename](fkname);//添加外

鍵

alter table [table_name] drop constraint [fk_name];//刪除外鍵

2.union 關鍵字：

A username, B username

rod

bruce

rose

marina

select username from A

union

select username from B

2、幾個高級查詢運算詞

A： UNION 運算符

UNION 運算符通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）并消去表中任何重復行而派生出一個結果表。當

ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重復行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來 自 TABLE2。

B： EXCEPT 運算符

EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復行而派生出一個結果表。當 ALL 隨

EXCEPT 一起使用時(EXCEPT ALL)，不消除重復行。

C： INTERSECT 運算符

INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復行而派生出一個結果表。當 ALL 隨

INTERSECT 一起使用時(INTERSECT ALL)，不消除重復行。

注：使用運算詞的幾個查詢結果行必須是一致的。

3.復合主鍵一般不設外鍵

4.組函數也稱為聚合函數。

例如：我們把學生可以分為男生和女生兩個組，如果想求每組的人數，平均身高，平均年齡等，就需要用到分組函數了。

在SQL中常用的組函數有以下幾個：

COUNT()：求出全部的記錄數

MAX()：求出一組中的最大值

MIN()：求出一組中的最小值

AVG()：求出一組中的平均值

SUM()：求和

范例：COUNT()函數

SELECT COUNT(empno)FROM emp;

我們常用COUNT(*)，最好能夠用字段代替*

范例：MAX()、MIN()函數，求最大最小值，一般是針對于數值的字段的，求出所有員工的的最高工資，和最底工資和平均工

資。

SELECT MAX(sal)最高工資,MIN(sal)最底工資,AVG(sal)平均工資 FROM emp;

范例：求出部門１０的所有員工工資的總合

SELECT SUM(sal)工資綜合 FROM emp WHERE deptno=10;

如果如下查詢輸出部門編號和其部門所有員工的工資總和，會產生錯誤。

SELECT deptno ,SUM(sal)工資綜合 FROM emp WHERE deptno=10;

錯誤： “不是單組分組函數”

發生以上的錯誤信息，是因為這樣的查詢需要進行分組統計。

分組統計有其固定的語法格式：

SELECT {DISTINCT} *| 查詢列 列別名1，查詢列2 列別名2,……

FROM 表名稱１ 表別名１，表名稱２ 表別名２,……

{WHERE 條件(s)}

{ORDER BY 排序的字段1,排序的字段2 ASC|DESC}

{GROUP BY 分組字段}

所以老師寫的是錯的！

5.//從t_student表中刪除名字重復的記錄的信息

delete from t_student where sid not in(select sid from（select min(sid)sid,sname from t_student group by sname));

// delete from tablename where id not in(select max(id)from tablename group by col1,col2,...)

6.sequence

在oracle中sequence就是序號，每次取的時候它會自動增加。sequence與表沒有關系。

（1）

CREATE SEQUENCE seqTest

INCREMENT BY 1--每次加幾個

START WITH 1--從1開始計數

NOMAXvalue--不設置最大值

NOCYCLE--一直累加，不循環

CACHE 10;--設置緩存cache個序列，如果系統down掉了或者其它情況將會導致序列不連續，也可以設置為----NOCACHE（2）

定義好sequence后，你就可以用currVal，nextVal取得值。

CurrVal：返回 sequence的當前值

NextVal：增加sequence的值，然后返回 增加后sequence值

eg：

SELECT Sequence名稱.CurrVal FROM DUAL;

select seqtest.currval from dual（3）

在Sql語句中可以使用sequence的地方：

-不包含子查詢、snapshot、VIEW的 SELECT 語句

-INSERT語句的子查詢中

-INSERT語句的values中

-UPDATE 的 SET中

如在插入語句中

insert into 表名(id,name)values(seqtest.Nextval,'sequence 插入測試');

7.范式：

所謂第一范式（1NF）是指在關系模型中，對域添加的一個規范要求，所有的域都應該是原子性的，即數據庫表的每一

列都是不可分割的原子數據項，而不能是集合，數組，記錄等非原子數據項。即實體中的某個屬性有多個值時，必須拆分

為不同的屬性。在符合第一范式（1NF）表中的每個域值只能是實體的一個屬性或一個屬性的一部分。簡而言之，第一范式

就是無重復的域。

第二范式（2NF）是在第一范式（1NF）的基礎上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。

第二范式（2NF）要求數據庫表中的每個實例或記錄必須可以被唯一地區分。選取一個能區分每個實體的屬性或屬性組，作

為實體的唯一標識。第二范式（2NF）要求實體的屬性完全依賴于主關鍵字。所謂完全依賴是指不能存在僅依賴主關鍵字一

部分的屬性，如果存在，那么這個屬性和主關鍵字的這一部分應該分離出來形成一個新的實體，新實體與原實體之間是一

對多的關系。為實現區分通常需要為表加上一個列，以存儲各個實例的唯一標識。簡而言之，第二范式就是在第一范式的基礎上屬性完全依賴于主鍵。例如在員工表中的身份證號碼即可實現每個一員工的區分，該身份證號碼即為候選鍵，任何

一個候選鍵都可以被選作主鍵。在找不到候選鍵時，可額外增加屬性以實現區分，如果在員工關系中，沒有對其身份證號

進行存儲，而姓名可能會在數據庫運行的某個時間重復，無法區分出實體時，設計辟如ID等不重復的編號以實現區分，被

添加的編號或ID選作主鍵。

第三范式（3NF）是第二范式（2NF）的一個子集，即滿足第三范式（3NF）必須滿足第二范式（2NF）。簡而言之，第三范式（3NF）要求一個關系中不包含已在其它關系已包含的非主關鍵字信息。例如，存在一個部門信息表，其中每個部門

有部門編號（dept_id）、部門名稱、部門簡介等信息。那么在員工信息表中列出部門編號后就不能再將部門名稱、部門簡

介等與部門有關的信息再加入員工信息表中。如果不存在部門信息表，則根據第三范式（3NF）也應該構建它，否則就會有

大量的數據冗余。簡而言之，第三范式就是屬性不依賴于其它非主屬性，也就是在滿足2NF的基礎上，任何非主屬性不得傳

遞依賴于主屬性。