Oracle資料庫是相對於其他資料庫來說比較難的一個。Oracle Database，又名Oracle RDBMS，簡稱Oracle。是甲骨文公司推出的一款關聯式資料庫管理系統。Oracle資料庫系統是目前世界上流行的關聯式資料庫管理系統，擁有可移植性好、使用方便、功能強等優點，在各類大、中、小、微機環境中都適用。Oracle是一種高效率、可靠性好的、適應高吞吐量的資料庫解決方案。下面我們來具體的學習一下：

首先我們來看什麼是Oracle資料，他的組成都有哪些？

Oracle資料庫伺服器由一個資料庫和至少一個資料庫例項組成。資料庫是一組儲存資料的檔案，而資料庫例項則是管理資料庫檔案的記憶體結構。此外，資料庫是由後臺程式組成。資料庫和例項是緊密相連的，所以我們一般說的Oracle資料庫，通常指的就是例項和資料庫。

Oracle 11G如何安裝

這是一個Oracle11g的下載連結，大家可以根據自己的電腦系統下載不同的安裝包。大家這裡需要注意，會下載一個檔案一和檔案二。

如圖，下載好後，選中2個壓縮包，解壓到一個資料夾就可以了，這點非常重要。選擇setup.exe 安裝就行。

資料庫的建立

安裝完成後我們在CMD命令視窗中執行sqlplus命令，來開啟終端，此時他會提示我們輸入使用者名稱和密碼，這個時候使用者可以輸入sys，密碼則是你在安裝的時候設定的密碼。

接下來我們建立一個使用者：

CREATE USER user1 IDENTIFIED BY 1234;

我們給user1使用者來授權：

GRANT CONNECT,RESOURCE,DBA TO user1;

上面我們建立了一個使用者，並且授予了登入和DBA的許可權，下面我們用user1來進行登入看看：

CONNECT user1@orcl;

會提示你輸入密碼，登入成功。注意，user1使用者僅存在於orcl資料庫中，因此，必須在CONNECT命令中明確指定使用者名稱為user1@orcl。

Oracle建立資料庫有三種方式：

用oracle dbca來建立
手工建立資料庫（這個是比較複雜的）
使用oracle managed Field來建立

帶著問題去學習

常用的查詢方面的有：

Select 演示如何查詢單個表中的資料。

排序方面有：

Order By 按升序或降序對查詢的結果集進行排序。

過濾方面有：

Distinct 介紹如何消除查詢輸出中的重複行。
Where 演示如何為查詢返回結果集中的行記錄指定過濾條件。
And 組合兩個或兩個以上的布林表示式，如果所有表示式都為true，則返回true。
Or 組合兩個或兩個以上的布林表示式，如果其中一個表示式為true，則返回true。
Fetch 演示如何使用行限制子句限制查詢返回的行數。
in 演示如何使用行限制子句限制查詢返回的行數。
Between 基於一系列值(區間值)過濾資料。
Like 根據特定模式執行匹配。

連結表方面有：

Inner join 演示如何從表中查詢具有與其他表匹配的行記錄。
Left join 介紹左連線概念，並學習如何使用它選擇左表中具有，但右表中不具有的行記錄。
Right join 解釋右連線概念，並演示如何從右表查詢具有，但左表中不具有的行記錄。
Cross join –介紹如何從多個表中構建笛卡爾乘積。
Self join 演示如何將表連線到自身以查詢分層資料或比較同一個表中的行記錄。

分組方面有：

Group By 演示如何將行分組為子組，併為每個分組應用聚合函式。
Having 演示如何過濾分組中的行記錄。

子查詢方面有：

子查詢 - 介紹子查詢的概念以及如何使用子查詢來執行高階資料選擇技術。
相關子查詢 - 瞭解相關的子查詢，它是一個依賴於外部查詢返回的值的子查詢。
EXISTS和NOT EXISTS - 檢查子查詢返回的行是否存在。
ANY，SOME和ALL - 將值與列表或子查詢進行比較。

設定運算子

UNION - 演示如何將兩個查詢的結果合併為一個結果。
INTERSECT - 演示如何實現兩個獨立查詢的結果的交集。
MINUS - 學習如何從一個結果集中減去另一個結果(也就是求差集)。

修改資料

INSERT - 學習如何在表中插入一行。
INSERT INTO SELECT - 從查詢結果中將資料插入到表中。
INSERT ALL - 討論多重插入語句，將多行插入到一個或多個表中。
UPDATE - 演示如何更新表的存在的資料值。
DELETE - 演示如何從表中刪除一行或多行。
MERGE - 使用單個語句逐步完成插入，更新和刪除操作。

資料定義：

CREATE TABLE - 演示如何在資料庫中建立新表。
IDENTITY列 - 瞭解如何使用IDENTITY子句來定義表的標識列。
ALTER TABLE - 演示如何改變表的結構。
ALTER TABLE ADD列 - 顯示如何將一個或多個列新增到現有表
ALTER TABLE MODIFY列 - 演示如何更改表中現有列的定義。
DROP COLUMN - 瞭解如何使用各種語句從表中刪除一列或多列。
DROP TABLE - 演示如何從資料庫中刪除表。
TRUNCATE TABLE - 更快，更有效地刪除表中的所有資料。
RENAME TABLE - 學習如何重新命名錶和處理其依賴物件的過程。

資料型別：

Oracle資料型別 - 內建Oracle資料型別的概述。
NUMBER - 介紹數字資料型別，並展示如何使用它為表定義數字列。
FLOAT - 透過例項來解釋Oracle中的浮點資料型別。
CHAR - 瞭解固定長度的字串型別。
NCHAR - 演示如何儲存固定長度的Unicode字元資料，並解釋CHAR和NCHAR資料型別之間的區別
VARCHAR2 - 向您介紹可變長度字元，並向您展示如何在表中定義可變長度字元列。
NVARCHAR2 - 瞭解如何在資料庫中儲存可變長度的Unicode字元。
DATE - 討論日期和時間資料型別，並說明如何有效地處理日期時間資料。
TIMESTAMP - 介紹如何以小數秒精度儲存日期和時間。
INTERVAL - 介紹區間資料型別，主要用來儲存時間段。
TIMESTAMP WITH TIME ZONE - 瞭解如何使用時區資料儲存日期時間。

約束：

主鍵 - 解釋主鍵概念，並演示如何使用主鍵約束來管理表的主鍵。
外來鍵 - 解釋介紹外來鍵概念，並演示如何使用外來鍵約束來強制表之間的關係。
NOT NULL約束 - 演示如何確保列不接受NULL值。
UNIQUE約束 - 討論如何確儲存儲在一列或一組列中的資料在整個表內的行之間是唯一的。
CHECK約束 - 在將資料儲存到表中之前新增用於檢查資料的邏輯的過程。

建立表空間

建立臨時表空間

CREATE TEMPORARY TABLESPACE ttf_temp 
TEMPFILE 'F:\oracledata\ttf_temp.dbf' SIZE 50m  
AUTOEXTEND on  NEXT 50m MAXSIZE 40960m  
EXTENT MANAGEMENT LOCAL;

建立資料表空間

CREATE TEMPORARY TABLESPACE ttf_data LOGGING
DATAFILE 'F:\oracledata\ttf_data.dbf' SIZE 50m  
AUTOEXTEND on  NEXT 50m MAXSIZE 40960m  
EXTENT MANAGEMENT LOCAL;

建立使用者並指定表空間

CREATE USER USER1 IDENTIFIED BY 1234DEFAULT TABLESPACE ttf_data
TEMPORARY TABLESPACE ttf_temp;//給使用者授予許可權GRANT CONNECT,RESOURCE,DBA to user1

建立表

建立表的一般語法格式如下：

CREATE TABLE <table_name> ( 
    <column_name_1> <data_type_1>, 
    <column_name_2> <data_type_2>, 
    <column_name_N> <data_type_N> ); 
====================================示例如下：CREATE TABLE authors ( 
 id number(38), 
 name varchar2(100), 
 birth_date date, 
 gender varchar2(30) 
);

建立表並且指定主鍵等約束：

//建立一個學生表CREATE TABLE STU(
  STUID NUMBER(10) PRIMARY KEY, //申明為主鍵
  STUNAME VARCHAR2(20) NOT NULL , //不為null
  STUSEX VARCHAR2(2) DEFAULT '男'   CHECK(STUSEX IN('男','女')) 
);//建立一個課程表CREATE TABLE COURSE(
   COURSEID NUMBER(10) PRIMARY KEY,
   COURSENAME VARCHAR2(20) NOT NULL,
   COURSETYPE VARCHAR2(4)
);//建立一個學生和課程的關聯表CREATE TABLE STU_COURSE(
  ID NUMBER(10) PRIMARY KEY,
  STUID NUMBER(10) REFERENCES STU(STUID), //外來鍵
  COURSEID NUMBER(10),  CONSTRAINT FF_COURSEid FOREIGN KEY(COURSEID) REFERENCES COURSE(COURSEID)  ON DELETE CASCADE //級聯刪除
)

新增資料--Insert

//插入的格式一般為INSERT INTO table [(column1,column2,...)] VALUE (value1,value2,...)INSERT INTO STU(id,name) VALUES(1,'張三');//多表多行插入INSERT [ALL] [condition_insert_clause]
[insert_into_clause values_clause] (subquery)INSERT ALLINTO stu(sid,sname) VALUES(ssid,ssname)INTO tea(tid,tname) VALUES(ttid,ttname)SELECT ssid,ssname,ttid,ttname,state FROM stu_tea WHERE state != 0//有條件的INSERTINSERT [ALL | FIRST]WHEN condition THEN insert_into_clause values_clause
[WHEN condition THEN] [insert_into_clause values_clause]
......
[ELSE] [insert_into_clause values_clause] 
Subquery;INSERT ALLWHEN id > 5 THEN INTO stu(sid,sname) VALUES(ssid,ssname)WHEN id < 5 THEN INTO tea(tid,tname) VALUES(ttid,ttname)ELSE INTO tt(sid,tid) VALUES(ssid,ttid)SELECT ssid,ssname,ttid,ttname FROM stu_tea;//旋轉Insert（pivoting Insert）create table sales_source_data (
	employee_id number(6),
	week_id number(2),
	sales_mon number(8,2),
	sales_tue number(8,2),
	sales_wed number(8,2),
	sales_thur number(8,2),
	sales_fri number(8,2)
);insert into sales_source_data values (176,6,2000,3000,4000,5000,6000); 
create table sales_info (
	employee_id number(6),
	week number(2),
	sales number(8,2)
);
看上面的表結構,現在將要sales_source_data表中的資料轉換到sales_info表中,這種情況就需要使用旋轉Insert
 示例如下:	insert all
	into sales_info values(employee_id,week_id,sales_mon)	into sales_info values(employee_id,week_id,sales_tue)	into sales_info values(employee_id,week_id,sales_wed)	into sales_info values(employee_id,week_id,sales_thur)	into sales_info values(employee_id,week_id,sales_fri)	select employee_id,week_id,sales_mon,sales_tue,
	sales_wed,sales_thur,sales_fri	from sales_source_data;
 
從該例子可以看出,所謂旋轉Insert是無條件 insert all 的一種特殊應用,但這種應用被oracle官方,賦予了一個pivoting insert的名稱,即旋轉insert

更新資料 -- Update

UPDATE 表名稱 SET 列名稱 = 新值 <WHERE 條件>UPDATE stu SET sid = 1,sname = '張三' WHERE state = 0

刪除資料 -- Delete

//語法如下DELETE FROM <table/view> [WHERE <condition>]//注意事項：//如果有外來鍵關聯，則刪除資料之前,需先刪除外來鍵關聯資料DELETE FROM stu WHERE sid = 1;//DELETE 與 TRUNCATE 應用區別：1、對於刪除整個表的所有資料時，delete並不會釋放表所佔用的空間2、如果使用者確定是 刪除 整表的所有資料，那麼使用 truncate table 速度更快//刪除所有學生資訊，使用DELETEDELETE FROM stu//刪除所有部門資訊使用 TRUNCATETRUNCATE TABLE stu

查詢語句 -- Select

//語法如下SELECT column_1,column_2 FROM table_name;//查詢單個列的資料SELECT sid FROM stu//查詢多個列SELECT sid,sname FROM stu//查詢所有列的資料SELECT * FROM stu//分組查詢SELECT id,name,age FROM stu GROUP BY age//排序查詢,按照id降序排序SELECT id,name,age FROM stu ORDER BY id DESC//唯一查詢  語法格式如下SELECT DISTINCT column_1,column_2 FROM table_name//多個條件查詢 ANDSELECT id,name,age,state FROM stu WHERE age = 1 AND state = 0//多個條件查詢 ORSELECT id,name,age,state FROM stu WHERE age = 1 OR age = 2

連結查詢

//內連結  inner joinSELECT * FROM stu INNER JOIN course ON cid = ccid ORDER BY ccid DESC//使用USING //1.查詢必須是等值連線。//2.等值連線中的列必須具有相同的名稱和資料型別。SELECT * FROM stu INNER JOIN course USING(cid) ORDER BY cid DESC----------------------------------------------------------------------------//左連結SELECT * FROM stu LEFT JOIN course ON cid == ccid ORDER BY cid DESC//使用USING ,這裡的c1和c2，在stu表和course表中都必須要相同型別的相同欄位SELECT * FROM stu LEFT JOIN course USING(c1,c2) ORDER BY cid----------------------------------------------------------------------------//右連結SELECT * FROM stu RIGHT JOIN course ON cid = ccid ORDER BY cid DESC//使用USINGSELECT * FROM stu RIGHT JOIN course USING(c1,c2) ORDER BY cid DESC----------------------------------------------------------------------------//笛卡爾積 CROSS JOINSELECT * FROM stu CROSS JOIN course----------------------------------------------------------------------------//自身連結SELECT m.id ,c.name FROM stu AS m,LEFT JOIN stu AS c ON c.cid = m.pid

Oracle 常用分頁

//1、透過MINUS分頁SELECT * FROM STU WHERE ROWNUM < 3 MINUS SELECT * FROM STU WHERE ROWNUM < 2//2、透過ROWNUM分頁SELECT * FROM STU WHERE ROWNUM < 10//查詢前10條SELECT * FROM (SELECT * FROM STU) WHERE ROWNUM <= 10//3、透過BETWEEN分頁 (查詢1到10)SELECT * FROM STU WHERE ROWNUM BETWEEN 1 AND 10SELECT * FROM (SELECT a.*,ROWNUM RN FROM STU) WHERE RN <=10

Oracle 建立檢視

檢視的優點有如下：

對資料庫的訪問，因為檢視可以有選擇性的選取資料庫裡的一部分。
使用者透過簡單的查詢可以從複雜查詢中得到結果。
維護資料的獨立性，試圖可從多個表檢索資料。
對於相同的資料可產生不同的檢視。

檢視分為簡單檢視和複雜檢視：

簡單檢視只從單表裡獲取資料，複雜檢視從多表；
簡單檢視不包含函式和資料組，複雜檢視包含；
簡單檢視可以實現DML操作，複雜檢視不可以。

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
    [(alias[, alias]...)]AS subquery
     [WITH CHECK OPTION [CONSTRAINT constraint]]
     [WITH READ ONLY]//建立檢視CREATE OR REPLACE VIEW SV AS SELECT sid,sname FROM STU WITH READ ONLY//查詢檢視SELECT * FROM SV;//查詢檢視定義SELECT SV,text FROM SV;
語法解析：OR REPLACE    ：若所建立的試圖已經存在，則替換舊檢視；
FORCE：不管基表是否存在ORACLE都會自動建立該檢視(即使基表不存在，也可以建立該檢視，但是該檢視不能正常使用，當基表建立成功後，檢視才能正常使用)；
NOFORCE  ：如果基表不存在，無法建立檢視，該項是預設選項(只有基表都存在ORACLE才會建立該檢視)。
alias：為檢視產生的列定義的別名；
subquery  ：一條完整的SELECT語句，可以在該語句中定義別名；WITH CHECK OPTION  ：插入或修改的資料行必須滿足檢視定義的約束；WITH READ ONLY       ：預設可以透過檢視對基表執行增刪改操作，但是有很多在基表上的限制(比如：基表中某列不能為空，但是該列沒有出現在檢視中，則不能透過檢視執行insert操作)，WITH READ ONLY說明檢視是隻讀檢視，不能透過該檢視進行增刪改操作。現實開發中，基本上不透過檢視對錶中的資料進行增刪改操作。

刪除檢視

DROP VIEW 檢視名稱;

Oracle 中EXISTS 和 NOT EXISTS

EXISTS （sql 返回結果集為真）
NOT EXISTS (sql 不返回結果集為真）

//如果有值就返回  EXISTS SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID = B.ID)//如果有值就返回  NOT EXISTS SELECT * FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE A.ID = B.ID)

EXISTS 和 NOT EXISTS 用的也是比較多的，效率相對來收也比較優。

IN、NOT IN的用法

// IN的基本語法如下SELECT columnsFROM tablesWHERE column1 in (value1, value2, .... value_n);SELECT * FROM STU WHERE ID IN / NOT IN (SELECT ID FROM B WHERE state = 0)

//舉個例子來說明 “exists” 和 “in” 的效率問題SELECT * FROM B1 WHERE EXISTS(SELECT * FROM B2 WHERE B1.a = B2.a)//B1資料量小而B2資料量非常大時， B1 << B2 時，查詢效率高SELECT * FROM B1 WHERE B1.a in (SELECT a FROM B2)//B1的資料量非常大而B2資料量小時， B1 >> B2 時 查詢效率高

自定義函式

//建立語法create [or replace] function function_name
　　[(parameter_list)]
　　return datatype
　　{is/as}
　　[local_declarations]
　　begin　　　　executable_statements;
　　[exception
　　　　exception_handlers;]
　　end;
　　說明：
　　function_name：函式名稱。
　　parameter_list：函式列表，可選。
　　return 自居：指定函式的返回型別，不能指定大小。
　　local_declarations：區域性變數宣告，可選。
　　executable_statements：要執行的PL-SQL語句。
　　exception_handlers：異常處理，可選。
　　or repalce：是否覆蓋，可選。
引數的模式有3種:(如果沒有註明, 引數預設的型別為 in.)    in: 為只讀模式, 在函式中, 引數的值只能被引用, 不能被改變;    out: 為只寫模式, 只能被賦值, 不能被引用;    in out:  可讀可寫.//注意 
1.在Oracle自定義函式中, else if 的正確寫法是 elsif 而不是 else if2.使用 if 需要加 then  "if 條件 then 操作"/////////////////////////////////////////////////////////////例如，讀入兩個值，返回比較大的值create or replace function get_max(para1 in number, para2 in number) 
return number 
as begin
  if para1 > para2 then
      return para1;  else
      return para2; 
  end if;end get_max;//使用select get_max(666, 333) from dual;///////////////////////////////////////////////////////////////CREATE or REPLACE FUNCTION useEasy(a1 in number,a2 in number) RETURN NUMBER IS
  fres NUMBER;  BEGIN
    fres := a1 + a2;    RETURN fres; END useEasy;//呼叫select useEasy(1,30) from dual;///////////////////////////////////////////////////////////////CREATE OR REPLACE FUNCTION get_emp_id(usernameq varchar2) RETURN NUMBERAS
 sid emp.id%TYPE; BEGIN
    SELECT id INTO sid FROM emp WHERE name = usernameq;    RETURN sid;END get_emp_id;//呼叫select get_emp_id('張三') from dual;//注意 
在Oracle的儲存過程和函式中，其實IS和AS是同義詞，沒有什麼區別。
還有在自定義型別（TPYE）和包（PACKAGE）時，使用IS和AS也並沒有什麼區別。
但是在建立檢視（VIEW）時，只能使用AS而不能使用IS。
在宣告遊標（CURSOR）時，只能使用IS而不能使用AS。

Oracle資料庫初學者入門教程

相關文章