ORACLE 資料庫 查詢語句與DML語句

一、查詢語句

ORACLE 資料庫中獲得資料表中的結果，稱為查詢語句，其關鍵詞是 select（下面其所有的示例均為scott模式下的物件）

1.1 查詢語句基礎

1.1.1 獲得表中全部欄位與結果

語法如下

select *  from table_name;

示例

查詢 僱員表的全部資訊

1.1.2 獲得表中需要的欄位結果

說明

通過顯示指定表中的欄位名可以只顯示需要欄位

語法如下

select col1[,col2,col3....]  from table_name;

示例

查詢僱員表的僱員編號和僱員名

1.1.3 按照過條件過濾結果

說明

可以通過where關鍵字卡條件來過濾結果， 條件欄位等於或者大於或者小於某個值等等，與可以多個條件連用

語法如下

select *  from table_name where col1 ='XXXX';

示例1

查詢僱員表的僱員編號為7934的結果

示例2

查詢僱員表的部門編號為30，並且獎金大於300的結果

1.1.4  按照某個值排序查詢的結果

預設的查詢結果是隨機顯示的，但是可以通過關鍵詞 order by 來使結果按照一定的規則排序顯示,可以通過 ASC或者desc來指定升序還是降序排列,預設為asc

語法

select * from table_name order by col1 [asc|desc];

示例

通過部門編號降序顯示結果

1.2 高階查詢語句

1.2.1 子查詢

說明

子查詢，主表的條件欄位結果引用另一個表中的結果，其中子表的查詢欄位只能查詢一個，可以引用子集的多行結果關鍵字為 in或者 not in，也可以只返回一行結果，關鍵字是=

語法

select *  from table_name1 where col1 in | not in|= （select col1 from table_name2）;

示例1

返回多個子結果

（注意子查詢的條件，子查詢的條件篩選是多個）

示例二

返回單個子結果

（注意子查詢的條件，子查詢的條件篩選是單個）

1.2.2多表等值關聯查詢

說明

    1. 當查詢結果需要引用其他表中結果，需要進行關聯查詢

    2. 在from 後面接兩個或兩個以上的表

    3. 確保關聯表中的關聯欄位值是唯一的，否則會產生笛卡爾積

    4. 如果在查詢欄位中有，兩個表的欄位都包含了該欄位，則需要顯示的指定欄位來自於哪個表

    5. 可以在表名後面跟別名

語法

select col1[,col2,col3....]  from table_name1，table_name2 where tba1.col1=tab2.col1;

示例

將僱員表和部門表進行連結查詢

（注意看，deptno欄位，因為兩個表都有該欄位，所以需要顯示的指定一個表，如果給表起了別名，則欄位前指定表名也需要寫表別名）

1.2.3 左關聯查詢

說明

1.哪個表名在前，哪個表就是左邊，在後面的為右表

2. 左表的全部資訊都會查詢出來，右表的資訊只有關聯上的資訊才會查詢到，右表沒有關聯上的資訊，則會顯示為空，關鍵詞 left join，也可以用（+）來關聯

語法

select col1[,col2,col3....]  from table_name1，table_name2 where tba1.col1=tab2.col1(+);
或
select col1[,col2,col3....]  from table_name1 left join table_name2 on tba1.col1=tab2.col1;

示例1

使用（+）寫法，（+）在右表上

示例二

使用 left join on 關鍵詞

1.2.4 右關聯查詢

說明

1.哪個表名在前，哪個表就是左邊，在後面的為右表

2. 右表的全部資訊都會查詢出來，左表的資訊只有關聯上的資訊才會查詢到，左表沒有關聯上的資訊，則會顯示為空，關鍵詞 right join， 也可以用（+）來關聯

語法

select col1[,col2,col3....]  from table_name1，table_name2 where tba1.col1(+)=tab2.col1;
或
select col1[,col2,col3....]  from table_name1 right join table_name2 on tba1.col1=tab2.col1;

示例1

使用（+）寫法，（+）在左表上

示例二

使用 right join on 關鍵詞

1.2.5 內連線

說明

1. 內連線實際不分左右表

2. 內連線只會顯示兩個表能關聯上的結果，沒有關聯上的資料不會顯示

3. 內連線使用inner join關鍵詞
   

語法

select col1[,col2,col3....]  from table_name1 [inner] join table_name2 on tba1.col1=tab2.col1;

示例

1.2.6 全連線

說明

1. 全連線不分左右表

2. 全連線會顯示兩個表的所有結果，沒有關聯上的資料則會顯示為空

3. 全連線使用full join關鍵詞

語法

select col1[,col2,col3....]  from table_name1 full join table_name2 on tba1.col1=tab2.col1;

1.2.7 使用 exists 和 not  exists 查詢

說明

使用 exist 可以查詢主表資料存在附表中的結果

使用 not exist 可以查詢主表資料不在附表中的結果

語法

select * from tab1 where [not] exists（select 1 from tab2 where tab1.col=tab2.col）;

示例1

查詢部門表部門標號在僱員表中的資料

 

 

示例2

查詢部門表部門標號不在僱員表中的資料

 

 

二 、DML語句

Oracle 資料庫的 DML 表資料的操作有三種：

1 insert （插入）

2 update （更新）

3 delete （刪除）

 

（為了避免更改掉原始的資料，所以我把emp複製了一份出來，表名為emp_new,表空沒有資料。以下的用例都使用這張emp_new）

 

 

2.1 insert 語句使用

2.1.1 insert 基本用法

說明 

1. 如果想要給表中新增資料的話，則需要使用insert into和values關鍵字來新增我們想要的資料

2. 可以選擇要填充的欄位名與值來插入資料

3. 也可以全表所有欄位來插入資料

4. 在給定值的時候一定要注意值要符合表規定（比如欄位型別，是否為空，是否唯一等等）

5.需要注意的是，在插入之後需要commit（提交）後才能永久的生效我們的操作。

 

語法

按照選定欄位插入，values後面值的位置，對應前面欄位的順序，不指定的欄位預設都為空

insert into tab_name(col1[,col2,col3....]) values('value1'[,'value2','value3'....]);

插入表中全部值，這種寫法需要注意的是，不能跳過欄位，否則會串列

 

insert into tab_name values('value1','value2','value3'....);

 

示例1

按照指定欄位插入值

 

 

示例2

插入所有欄位

 

 

2.1.2 引用另一個表的結果插入

說明

可以將另一個表中的查詢結果插入目標表，可以是選擇欄位插入，也可以是全欄位列的插入，但是需要注意的是無論哪種方式，都要注意表限制和欄位順序，以及兩個表值的個數數量要一致。

 

語法

insert into tab1(col1[,col2,col3....]) select col1[,col2,col3....]  from tab2;
或
insert into tab1  select  * from tab2;

示例1 

按欄位插入另一個的結果

（該插入是引用了emp表的僱員編號為7902的查詢結果）

 

 

示例2

引用另一個表中的全部欄位插入

 

2.2 update語句使用

2.2.1 update 基本用法

說明 

1. 如果想要在表中修改已存在資料的話，則需要使用 update  set 關鍵字來修改我們想要的資料

2. 更新是針對列修改的

3. 如果想修改某行或者某幾行的某一列，則必須加條件否則會把整個表得該列都更新成一樣的

4. 在給定更新值也必須符合到表限制（比如欄位型別，是否為空，是否唯一等等）

5.需要注意的是，在更新之後需要commit（提交）後才能永久的生效我們的操作。

語法

update tab 
set col1='xxx'
[where col1='xxx'......];;

示例1

無條件更新整個列

（將所有人的獎金都加100。需要注意的是，由於獎金欄位（comm）的型別是數字的，而且值為null，所以數字型別為null值預設不會更新，但是字元格式的不存在這種情況）

示例2

按照條件更新值

（將所有人獎金為空的人的獎金更新成為200。需要注意的是：更新數字格式欄位值為null的情況，必須指定要更新欄位值為null）

2.2.2 update  高階用法

說明

1.更新還可以同時更新多個值，通過逗號隔開

2.還可以引用另一個表的查詢結果來更新

多值更新

語法

update tab 
set col1='xxx',col2='xxxx'
[where col1='xxx'......];

示例

同時更新mgr和sal值

引用查詢結果

語法

update tab1 
set col1=（select col1 from tab2 where tab1.col=tab2.col）
[where col1='xxx'......];

示例

根據emp表中查詢得到dept欄位和mgr欄位結果更新emp_new表中的deptno欄位和mgr欄位

2.2 delete語句使用

2.2.1 delete基本用法

說明 

1. 如果想要在表中刪除已存在資料的話，則需要使用 delete關鍵字來刪除我們不想要的資料

2. 刪除資料可以不卡條件直接刪除表中所有的資料

3. 也可以卡條件欄位來刪除特定的資料

4.需要注意的是，在刪除之後需要commit（提交）後才能永久的生效我們的操作。

5.特別要強調的是，要在刪除前把delete改成select 執行下，確保查詢到的結果就是要刪除的正確資料，防止誤刪除事件發生

語法

delete from tab [where col1='xxx'......];

示例

按條件刪除

刪除部門編號為null的資料

刪除表中全部資料

今天想表達的關於技術的東西都寫完了，說些題外話，這是我寫的第一遍部落格，就挑選了入門的基礎知識來幫助入門oracle的朋友們供一個思路算是拋磚引玉吧。我個人能力有限，寫的東西水平業有限，不足之處還望大家提出來，在日後的部落格中改進，然後希望日後能跟大家多多交流共同學習進步。

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