有時間可以看看複習一下oracle-總結(轉)

一.Oracle資料庫中常用的資料型別
varchar2(長度)可變長字串
char(長度) 定長
number()表示整數或者浮點數number(8) number(8,2)
clog 字元的大物件
blog 二進位制的大物件

二.資料庫查詢
1）SELECT語句
從表中提取查詢資料.語法為SELECT [DISTINCT] {column1,column2,…} FROM tablename WHERE {conditions} GROUP BY {conditions} ORDER BY {expressions} [ASC/DESC];
說明：SELECT子句用於指定檢索資料庫的中哪些列，FROM子句用於指定從哪一個表或檢視中檢索資料。
2）WHERE子句。
WHERE子句用來選擇符合條件的的記錄.
like '...' 通配查詢 _,%
between ... and ... ,表示結果在這之間，between and是一個閉區間。
!=，<>，^=，這三個都可以表示不等於。
in (va1,val2,...) 判斷結果是否在這個集合中存在 。
like '...' 字串通配查詢，'%'表示0或多個字元，'_'表示一個字元。
... and ... 表示只有兩個條件同時滿足
... or ... 表示條件只要滿足其中之一就可以
all ... 是要求都滿足條件。
not .....，則是可以與以上的條件產生反效果。
... is null 使用來判斷值是不是空。
3) ORDER BY子句
ORDER BY 子句使得SQL在顯示查詢結果時將各返回行按順序排列，返回行的排列順序由ORDER BY 子句指定的表示式的值確定。
ASC（預設，升序） DESC（降序）
order by 目標列名（別名） 排序順序（不寫排序順序，會預設為升序排序）
例：select first_name from s_emp order by first_name;
select first_name from s_emp order by first_name desc;

三.SQL常用的命令分類及例子
資料定義語言：create（建立）、alter（更改）和drop（刪除）命令。
資料操縱語言：insert（插入）、select（選擇）、delete（刪除）和update（更新）命令。
事務控制語言：commit（提交）、savepoint（儲存點）和rollback（回滾）命令。
資料控制語言：grant（授予）和revoke（回收）。
1.資料定義語言舉例：
SQL> create table myTab(no number(4),name varchar2(20));建立一個名為myTab的表，包含兩列no和name；
SQL> alter table myTab modify (name varchar2(25));修改myTab中的name列，使此列能容納25個字元；
SQL> alter table myTab add (tel_no varchar2(20));給表myTab增加一列tel_no；
SQL> alter table myTab drop column tel_no;刪除表myTab的tel_no列;
SQL> drop table myTab;刪除表myTab；
SQL> truncate table myTab;刪除表myTab中的所有行（截斷表）,注意:不可以回滾。

2.資料操縱語言舉例：
SQL> insert into myTab values(‘001’,’John’);
向表myTab中插入一行資料；
SQL> select distinct salary “薪水” from s_emp where salary>1500 order by sal desc;
選擇表中salary大於1500的資料，以別名“薪水”顯示並按照salary的降序進行排列輸出；
SQL> create table empa as select empno,ename,job,sal from emp;
從emp表中選擇“empno,ename,job,sal”四列的資料建立新表empa;
SQL> create table empa as select * from emp where 1=2;
使用一個假條件根據現有表emp建立一個只包含結構的空表empa；
SQL> delete from empa where sal<1500;
刪除表empa中sal小於1500的行；
SQL> update empa set sal=1500 where sal<1500;更新，將表empa中sal小於1500的行的sal值全部改為1500。

3.事務控制語言舉例：
SQL> commit;用於提交併結束事務處理；
SQL> savepoint mark1;儲存點類似於標記，用來標記事務中可以應用回滾的點；
SQL> rollback to savepoint mark1;回滾到儲存點mark1。

四.Oracle資料庫函式
注意:dual表(虛表)是專門用於函式測試和運算的.
1.字元函式
   字元是大小寫敏感的
   轉小寫 lower(欄位名)
   轉大寫 upper(欄位名)
   首字母大寫 initcap(欄位名)
   字串拼接 concat(欄位1, 欄位2)
   擷取子串 substr(欄位名, 起始位置，取字元個數)
例: select first_name,substr(first_name,2,2) sub from s_emp;（從名字的第二個字元開始取兩個字元）
select first_name,substr(first_name,-2,2) sub from s_emp;（從名字的倒數第二個字元開始取兩個字元）
2.數值函式
   四捨五入函式 round(資料,保留到小數點後幾位)
   1表示保留到小數點後一位，-1表示保留到小數點前一位。
   例：select round(15.36,1) from dual;
   擷取數值函式 trunc(資料，保留到小數點後幾位)
   例：select trunc(123.456,1) from dual;
擷取到小數點後一位,注意:與round函式不同,不會四捨五入。
3.日期函式
   預設日期格式，日-月-年 dd-mon-rr
   修改當前會話的日期格式，會按照指定的格式輸出日期
   alter session set nls_date_format='yyyy mm dd hh24:mi:ss';
   返回當前日期 sysdate
   例：select sysdate from dual;
4.不同資料型別間轉換函式
   將日期轉成字元 tochar(date,'日期格式')
   日期格式要用有效格式，格式大小寫敏感 'yyyy mm dd hh24:mi:ss'(標準日期格式),'year'(年的全拼),'month'(月的全拼)，'day'(星期的全拼)，'ddspth' (日期的全拼)
   例：select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from dual;
select to_char(sysdate,'year month day ddspth')from dual;
   將字串轉成日期 to_date('...','日期格式')
   例：select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from dual；

五.表連線（關聯查詢）
等值連線
select table1.column1，table2.column2
from table1 t1，table2 t2
where t1.column3=t2.column4;
表連線時，當表與表之間有同名欄位時，可以加上表名或表的別名，加以區分，使用時要用表名.欄位名或表別名.欄位名（列名）。當表的欄位名是唯一時，可以不用加上表名或表的別名。
注意：當為表起了別名，就不能再使用表名.欄位名了。
例如：select e.first_name ||’ ’|| e.last_name name,
d.name dept_name
from s_emp e, s_dept d
where e.dept_id=d.id;

非等值連線
select [表別名1.欄位名1]，[表別名2.欄位名2],...
from 表1 表別名1 ，表2 表別名2
where 表別名1.欄位名3 ..... 表別名2.欄位名4
....可以使比較運算子，也可以使其他的除了'='的運算子
例：select first_name, salary
from s_emp
where salary between 1000 and 2000;

自連線
把一個表的兩個欄位關係轉換成兩個表欄位之間的關係.
select [表別名1.欄位名1]，[表別名2.欄位名2],...
from 表1 表別名1 ，表1 表別名2
where 表別名1.欄位名3=表別名2.欄位名4;
例：select a.first_name ename,b.first_name cname
from s_emp a,s_emp b
where a.manager_id=b.id;

外連線
使用一張表中的所有記錄去和另一張表中的記錄按條件匹配(空值也會匹配)這個表中的所有記錄都會顯示。
//想在哪邊模擬記錄就在哪邊加上(+)
1. LEFT OUTER JOIN：左外連線
SELECT e.last_name, e.dept_id, d.name
FROM s_emp e
LEFT OUTER JOIN s_dept d
ON (e.dept_id = d.id);
等價於
SELECT e.last_name, e.dept_id, d.name
FROM s_emp e, s_dept d
WHERE e.dept_id=d.id(+);
結果為：所有員工及對應部門的記錄，包括沒有對應部門編號dept_id的員工記錄。
2. RIGHT OUTER JOIN：右外連線
SELECT e.last_name, d.name
FROM s_emp e
RIGHT OUTER JOIN s_dept d
ON (e.dept_id = d.id);
等價於
SELECT e.last_name,d.name
FROM s_emp e, s_dept d
WHERE e.dept_id(+)=d.id;
結果為：所有員工及對應部門的記錄，包括沒有任何員工的部門記錄。
3. FULL OUTER JOIN：全外關聯
SELECT e.dept_id,d.id
FROM s_emp e
FULL OUTER JOIN s_dept d
ON (e.dept_id = d.id);

結果為：所有員工及對應部門的記錄，包括沒有對應部門編號department_id的員工記錄和沒有任何員工的部門記錄。
六.組函式
group by把 select 的結果集分成幾個小組，這個group by 子句可以跟在 select 語句後或是 having前面。group by子句也會觸發排序操作，會按分組欄位排序。
select [組函式或分組的欄位名]... from 表名 group by [欄位名1],[欄位名2],.....；
例：select avg(salary) from s_emp group by dept_id;
注意：組函式會忽略空值，但是count(*)除外，他會把空記錄也記錄在內。avg和sum這兩個函式的引數只能是number型的。
以下所提到的函式可以使用任意型別做引數。
max(..),min(..)求最大值和最小值，
count(*)統計表中記錄數。
例：select max(b.name),avg(a.salary), max(c.name)
from s_emp a,s_dept b,s_region c
where a.dept_id=b.id
and b.region_id=c.id
group by b.id;
注意：只要寫了group by子句，select後就只能用group by之後的欄位或者是組函式。having子句可以過濾組函式結果或是分組的資訊，並且寫在group by子句後。

七.子查詢
可以嵌在sql語句中的select語句。
在select語句中巢狀子查詢時，會先執行子查詢。一般的會將子查詢放在運算子的右邊。
注意：在使用子查詢時，要注意這個運算子是單行的（也就是隻能是單值），還是多行運算子（範圍，多值）。配合使用子查詢返回的結果必須符合運算子的用法。
例:
select first_name||' '||last_name name
from s_emp
where title in (select title from s_emp
where dept_id=42);
查詢和42部門員工職位相同的所有員工的姓名

八.約束
針對表中的欄位進行定義的。
primary key（主鍵約束 PK）保證實體的完整性，保證記錄的唯一
主鍵約束，唯一且非空，並且每一個表中只能有一個主鍵，有兩個欄位聯合作為主鍵時，將兩個欄位放在一起唯一標識記錄，叫做聯合主鍵。
主鍵約束的定義：
第一種定義形式：
create table test(c number primary key );        列級約束
第二種定義形式：
create table test(c number , primary key(c) ) ;        表級約束
create table test(c1 number constraints   pk_c1 primary key );   此約束有名字: pk_c1
create table   test(c number , c2 number , primary key (c ,c1) ) ; 用表級約束可以實現聯合主鍵

foreign key（外來鍵約束 FK）保證引用的完整性，外來鍵約束，外來鍵的取值是受另外一張表中的主鍵或唯一鍵的約束，不能夠取其他值，只能夠引用主鍵或唯一鍵的值，被引用的表，叫做 parent table（父表），引用方的表叫做child table（子表），要想建立子表，就要先建立父表，記錄的插入也是如此，先父表後子表，刪除記錄，要先刪除子表記錄，後刪除父表記錄，要修改記錄，如果要修改父表的記錄要保證沒有被子表引用。要刪表時，要先刪子表，後刪除父表。(可以通過使用cascade constraints 選項來刪除父表)
carete   table     parent(c1 number primary key );
create   table    child (c number primary key ,   c2 number references parent(c1));
或表級約束定義:
create   table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
非空約束（not null）這是一個列級約束,在建表時,在資料型別的後面加上 not null ，也就是在插入時不允許插入空值。
例：create table student(id number primary key,name varchar2(32) not null,address varchar2(32));
unique 唯一約束
唯一約束，允許為空，要求插入的記錄中的值是唯一的。
例：create table student(id number，name varchar2(32),address varchar2(32),primary key (id),unique (address));
check約束
檢查約束，可以按照指定條件，檢查記錄的插入。check中不能使用偽列，不能使用函式，不能引用其他欄位。
例：create table sal (a1 number , check(a1>1000));

九.資料字典
資料字典是由系統維護的，包含資料庫的資訊
資料字典示圖
user_XXXXX 使用者示圖
all_XXXXX 所有示圖
dba_XXXXX 資料庫中所有示圖
v$_XXXXX   動態效能示圖

dict或 dictionary 表示資料字典的資料字典。
user_constraints 使用者的表中約束的表
其中有constraints_name欄位存放的是約束名，constraint_type欄位存放的是約束的型別,r_constraints_name欄位表示外來鍵引用自何處.
user_cons_column表，是使用者的列級約束表,column_name欄位存放的是約束欄位的名字,position欄位存放的是約束在聯合鍵中的位置.

十.事務transaction
原子操作，也就是不可分割的操作，必須一起成功一起失敗。
事務的結束動作就是commit，DDL,DCL語句執行會自動提交commit。
sqlplus正常退出是會做提交動作的commit;，當系統異常推出是，會執行回滾操作rollback;。
一個沒有結束的事務，叫做活動的事務 (active transaction),活動的事務中修改的資料，只有本會話才能看見。

十一.Oracle中的偽列
偽列就像Oracle中的一個表列，但實際上它並未儲存在表中。偽列可以從表中查詢，但是不能插入、更新或刪除它們的值。常用的偽列：rowid和rownum。
rowid：資料庫中的每一行都有一個行地址，rowid偽列返回該行地址。可以使用rowid值來定位表中的一行。通常情況下，rowid值可以唯一地標識資料庫中的一行。
rowid偽列有以下重要用途：
1）能以最快的方式訪問表中的一行；
2）能顯示錶的行是如何儲存的。
3）可以作為表中行的唯一標識。
如：SQL> select rowid,ename from emp;
rownum：對於一個查詢返回的每一行，rownum偽列返回一個數值代表的次序。
rownum偽列特點：
1） 有個特點要麼等於1 要麼小於某個值， 不能直接等於某個值, 不能大於某個值。
2）常用於分頁顯示。
返回的第一行的rownum值為1，第二行的rownum值為2，依此類推。通過使用rownum偽列，使用者可以限制查詢返回的行數。
如：SQL>select * from emp where rownum<11; 從emp表中提取10條記錄。

十二.序列(sequence)
create sequence 序列名;
(不帶引數時預設為從1 開始每次遞增 1，oracle中為了提高產生序列的效率一般一次性產生20個序列放入當前會話的序列池中備用以加快效率)

sequence 的引數：
increment by n   起始值
start with n     遞增量
maxvalue n       最大值
minvalue n       最小值
cycle|no cycle     迴圈
cache n          快取(第一次取時會一次取多少個id存起來)

檢視sequence 示圖：
desc    user_sequences ;
select   sequence_name , cache_size , last_number from user_sequences   where   sequence_name like 's_';
select 序列名.currval from   dual    檢視當前的序列數
select 序列名.nextval from   dual    檢視下一個序列數，它會自動給當前的序列加１
刪除序列sequence
drop sequence 序列名;

十三. 檢視(View)
檢視就相當於一條select 語句,定義了一個檢視就是定義了一個sql語句, 檢視不佔空間,使用檢視不會提高效能，但是能簡化sql語句
建立檢視：
creating views檢視名;
如：
create or replace views test as select * from test1 where c1=1;
create or replace:如果view存在就覆蓋，不存在才建立。
force|no force:基表存在時使用，不存在時則建立該表。
注意:向檢視中插入資料時，會直接插進基表中，檢視檢視中的資料時，相當於就是執行建立時的select語句。
刪除檢視:
drop views檢視名;
試圖的約束:
with read only檢視只讀約束
with check option 不允許插入與where條件不符的記錄，類似於check約束的功能.
create view test_cc
as select * from test
where c1>10
with check option;

十四.索引（index）
建立索引的目的就是為了加快查詢速度,建立索引後會使DML操作效率慢，但是對使用者查詢會提高效率。刪除一個表時，相對應的索引也會刪除。另外,索引是會進行排序。
建立一個索引：
create index 索引名 on 表名 (欄位名);
create index test_index on test(c1);
刪除索引:
drop index test_index;
注意：建立索引就是為了減少物理讀，索引會減少掃描的時間。在經常要用到where的子句的欄位，應該使用索引，另外還要看所查詢的資料與全部資料的百分比，表越大，查詢的記錄越少，索引的效率就越高.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11320622/viewspace-675059/，如需轉載，請註明出處，否則將追究法律責任。