何謂SET CONSTRAINTS[S]
- 它可以設定約束條件的驗證時機,可分為兩種驗證方式,第一種為每執行一次DML語法時驗證,第二種為執行COMMIT後驗證,它屬於延緩檢查
格式
SET CONSTRAINTS[S]
{ALL | constraints_name,[constraints_name...]}
{IMMEDIATE | DEFERRED}
constraints_name:約束條件名稱
使用方式
設定約束條件為延緩檢查,emp_pk為約束條件名稱
set constraints emp_pk deferred;
何謂SET TRANSACTION NAME
格式
SET TRANSACTION NAME string;
string:交易名稱
使用方式
設定交易名稱為hello
set transaction name 'hello';
何謂SET TRANSACTION USE ROLLBACK SEGMENT
- 它可以指定在一個交易中返回你指定的交易回復段,並搭配使用UNDO,注意10g之後導入自動UNDO管理,所以不能使用這個子句
格式
SET TRANSACTION
USE ROLLBACK SEGMENT rollback_segment
[NAME string];
rollback_segment:回復段名稱
string:交易名稱
使用方式
設定交易使用特定的回復段為rg01
set transaction use rollback segment rg01;
何謂SET TRANSACTION ISOLATION LEVEL
- 設定交易隔離等級,等級分為[可序列化]等級或[可提交讀取]等級,預設是可提交讀取等級,不論是哪種隔離等級,都能保證交易的同時執行性和資料的一致性,不會發生無法讀取內容
格式
SET TRANSACTION
ISOLATION LEVEL {SERIALIZABLE | READ COMMITTED}
[NAME string];
string:交易名稱
使用方式
將交易隔離等級設定為序列化,預設為READ COMMITTED
set transaction isolation level serializable;
何謂SET TRANSACTION READ
- 設定整個交易是唯讀或是可讀寫的狀態,只要執行ROLLBACK或COMMIT交易將會結束,你所設定的交易狀態也會失效,如果你設定交易為可讀不可寫狀態,執行DDL語句或DCL語句將會成功,因為DDL語句或DCL語句會自動執行COMMIT
格式
SET TRANSACTION READ {ONLY | WRITE}
[NAME string];
string:交易名稱
使用方式
設定整個交易為唯讀
set transaction read only;
設定整個交易為可讀可寫
set transaction read write;
何謂SET TRANSACTION
- 它算是一個DCL語句,可以設定整個交易的狀態,以下是可以設定的狀態模式
- 設定交易隔離等級
- 將交易設定唯讀或可讀寫
- 將交易指定到特定的回復段
- 替交易加上名稱
補充
- 如果在這個交易執行了SET TRANSACTION句,不會影響其他使用者的交易狀態,如果執行COMMIT或ROLLBACK,交易將會結束,你所設定的SET TRANSACTION句就失效了
何謂SAVEPOINT
- 在同個交易中設定保存點名稱,保存點名稱可設定多個,名稱唯一不可從覆,設定保存點的目的是要為了可以在一個交易中返回到交易的其中一個時間點,但只要交易做了確認你所設定的保存點全部都消失了
格式
SAVEPOINT savepoint_name;
savepoint_name:設定保存點名稱
使用方式
修改emp表格資料,之後設定保存點名稱為sp
update emp set salary=1;
savepoint sp;
何謂ROLLBACK
- 終止交易結束點,針對資料做回滾的動作,假如你對資料做新增,修改,刪除,在下COMMIT之前,你都可以返回剛剛的動作,新增,修改,刪除的資料內容會還原原本資料內容,COMMIT之後就無法對資料做返回
設定保存點
- 如你可以在同個交易中設定不同的保存點名稱,如果你不想全部返回,只想返回一些資料內容,你可以指定你設定的保存點名稱,返回到那個時間點的交易
格式
ROLLBACK [TO [SAVEPOINT] savepoint];
savepoint:保存點名稱
使用方式
修改emp表格資料,修改過後的資料做返回,所以做後結果是資料沒被修改
update emp set salary=1;
rollback;
設定保存點名稱為a,之後新增資料到emp表格,在設定保存點名稱為b,在修改emp表格資料,所以在一個交易中有兩個表存點,這時候做ROLLBACK命令指定保存點名稱為b,資料返回到保存點b交易中,所以資料只有被新增,沒有被修改
savepoint a;
insert into emp select * from employees;
savepoint b;
update emp set salary=1;
rollback to savepoint b;
何謂COMMIT
- 確定資料的交易點結束,保存點會全部消除,會釋放交易鎖,每結束一次交易點就會寫入交易的資訊到redo log files,它會用在DML語法,如果你寫的語法為INSERT,UPDATE,DELETE,MERGE,INSERT ALL,INSERT FIRST
格式
COMMIT;
使用方式
新增employees表格資料到emp,確定交易點結束
insert into emp select * from employees;
commit;
何謂MERGE
- 針對指定的條件可對資料做新增,修改,刪除,如果符合的條件就對資料做修改,刪除,如果不符合條件就對資料做新增
格式
MERGE INTO table_name [t_alias]
USING {table_reference | (subquery)} ON(condition)
WHEN MATCHED THEN
UPDATE SET column={expr | default}
[WHERE where_clause][DELETE WHERE where_clause]
WHEN NOT MATCHED THEN
INSERT (column[,column...])
VALUES({expr|default}[,{expr|default}...])
[WHERE where_clause]
table_name:目標表格
t_alias:目標表格的物件別名
table_reference:參考表格
subquery:子查詢
condition:條件
column:欄位名稱
expr:資料型別值
default:預設值
where_clause:指定條件
使用方式
如果emp表格的xid與employees表格的employee_id為相等,就修改emp表格的sal為100,如果為不相等就新增employees表格資料到emp
merge into emp e
using (select * from employees) e2 on(e.xid=e2.employee_id)
when matched then
update set e.sal=100
when not matched then
insert values(e2.employee_id,e2.salary);
何謂DELETE
- 可在表格內刪除資料,如果指定WHERE子句可以篩選要刪除的資料,如果不指定則會刪除表格內所有資料
格式
DELETE [FROM] table_reference
[WHERE condition]
table_reference:要刪除資料的目標表格
condition:指定條件,要刪除的資料行
使用方式
刪除emp_id為100的資料行
delete from emp where emp_id=100;
刪除emp_id為100的資料行
delete from emp where emp_id=(select emp_id from emp where emp_id=100);
何謂UPDATE
- 在表格內修改資料,可使用WHERE子句或子查詢指定要更改的資料,如果不指定會更新所有表格資料
格式
UPDATE table_clause
SET update_row
[WHERE where_clause]
使用方式
100的emp_id,薪水修改為1000
update emp set salary=1000
where emp_id=100;
100的emp_id,薪水修改為1000
update emp set salary=1000
where emp_id=(select emp_id from emp where emp_id=100);
何謂INSERT FIRST
- INSERT FIRST要搭配WHEN......THEN語句使用,判對第一個WHEN語句條件如果為TRUE就插入資料列,而其後的WHEN語句就省略判斷,如果為FIRST就往下列WHEN語句判斷,以此類推,它跟INSERT ALL一樣在多表格插入資料列
格式
INSERT FIRST
WHEN condition THEN insert_into_clause [insert_into_clause...]
[WHEN condition THEN insert_into_clause [insert_into_clause...]...]
[ELSE insert_into_clause [insert_into_clause...]]
(subquery);
使用方式
如果salary大於4000新增到emp2表格,如果第一個WHEN條件就為TRUE,而其後的WHEN語句就省略判斷,以此類推
insert first
when salary>4000 then into emp2
when salary>5000 then into emp3
when salary>6000 then into emp4
select salary from employees;
何謂INSERT ALL
- 可以在多個表格新增資料,可以使用2種新增模式,第一種為一般新增,第二種為附加條件新增(在INSERT ALL加上WHEN子句)
一般新增格式
INSERT ALL
INTO table_reference [(column [,column...])]
[VALUES ({expr | default} [,{expr | default}...])]
INTO table_reference [(column [,column...])]
[VALUES ({expr | default} [,{expr | default}...])]
(subquery);
table_reference:插入目標的表格名稱
column:欄位名稱
expr:資料來源表格的欄位名稱
default:資料來源表格的欄位預設值
subquery:子查詢
一般新增使用方式
新增employees表格資料到emp表格與emp2表格
insert all
into emp(xid,name) values(emp_id,last_name)
into emp2(xid,name) values(emp_id,last_name)
select emp_id,last_name from employees;
新增employees表格資料到emp表格與emp2表格(使用別名的方式)
insert all
into emp(xid,name) values(id+10,last_name)
into emp2(xid,name) values(id+20,last_name)
select emp_id as id,last_name from employees;
附加條件新增(在INSERT ALL加上WHEN子句)格式
INSERT ALL
WHEN condition THEN insert_into_clause [insert_into_clause...]
[WHEN condition THEN insert_into_clause [insert_into_clause...]...]
[ELSE insert_into_clause [insert_into_clause...]]
(subquery);
condition:條件
insert_into_clause:格式和INSERT句INTO子句以後的部分相同
subquery:子查詢
附加條件新增使用方式
如果employees表格裡的emp_id小於101就新增資料到emp2表格,如果employees表格裡的emp_id小於102就新增資料到emp3表格
insert all
when emp_id<101 then into emp2(xid,salary)
when emp_id<102 then into emp3(xid,salary)
select emp_id,salary from employees;
如果employees表格裡的emp_id小於101就新增資料到emp2表格,如果都不是就新增到emp3表格
insert all
when emp_id<101then into emp2(xid,salary)
else into emp3(xid,salary)
select emp_id,salary from employees;
何謂INSERT
- 在表格裡新增多筆資料,在INSERT句有分為兩總新增方式,第一總是一般新增,第二總是子查詢新增
一般新增格式
INSERT INTO table_reference[(column,column...)]
VALUES ({expr | default} [,{expr | default}...])
table_reference:要新增資料的表格名稱
column:欄位名稱
expr:新增資料型別的值
default:新增資料的預設值
一般新增使用方式
針對emp表格的last_name欄位與salary欄位做新增,新增Amy與25000
insert into emp(last_name,salary) values('Amy',25000);
針對emp表格的所有欄位做新增,新增Amy與25000與50
insert into emp values('Amy',25000,50);
補充
如果emp表格有10個欄位,新增資料時如果指定2個欄位,假如欄位沒有設定限制條件剩下8個欄位的值都是null,不指定欄位就代表要新增資料到10個欄位,而新增的資料也要與欄位數量一致,所新增的資料型別也要一致,如果使用到default關鍵字,新增的值是當初建立表格的表格欄位指定的值,建議使用指定欄位的方式新增資料
子查詢新增格式
INSERT INTO table_reference (subquery)
table_reference:要新增資料的表格名稱
subquery:子查詢
子查詢新增使用方式
新增employees所有資料到emp表格(不指定欄位)
insert into emp select * from employees;
新增employees的last_name與salary資料到emp表格(指定欄位)
insert into emp(last_name,salary) select last_name,salary from employees;
何謂表格合併
- 進行兩個以上的表格合併,並傳回單一結果的資料查詢,也可以稱為合併查詢
表格合併的類型
- 等價合併
- 自身合併
- 非等價合併
- 外部合併
- 迪卡兒積(直積)
等價合併介紹
SELECT select_list FROM table_reference [INNER] JOIN table_reference
ON condition | USING(column)
select_list:欄位名稱
table_reference:表格名稱和子查詢
condition:合併條件
column:合併條件所使用的欄位名稱
等價合併emp表格的dept_id欄位與dept表格的dept_id欄位,輸出合併後的查詢結果
select * from emp e join dept d on(e.dept_id=d.dept_id);
等價合併emp表格的dept_id欄位與dept表格的dept_id欄位,輸出合併後的查詢結果
select * from emp e join dept d using(dept_id);
select * from emp e,dept d where e.dept_id=d.dept_id;
自身合併介紹
- 在自己的表格裡,合併自己的欄位
- 格式(與等價合併一樣)
- 語法示範(只能使用ON,不能使用USING)
傳回經理與員工的工作關係
select e.last_name,m.last_name
from emp e join emp m on(e.manager_id=m.employee_id);
select e.last_name,m.last_name
from emp e ,emp m where e.manager_id=m.employee_id;
非等價合併
- 可合併表格都存在的列,不是使用=操作符進行合併而是使用BETWEEN語句進行範圍內的合併
- 格式(與等價合併一樣)
- 語法示範(只能使用ON,不能使用USING)
查詢每個員工的薪資等級
select e.last_name,g.level_grade from emp e join sal_grade g
on e.salary between g.lower_sal and high_sal;
select e.last_name,g.level_grade from emp e,sal_grade g
where e.salary between g.lower_sal and hight_sal;
外部合併介紹
- 外部合併是內部合併(等價合併/自身合併/非等價合併)的延伸,內部合併可傳回指定欄位相等的列,但外部合併可以傳回不滿足對方條件的列
- 外部合併可以分為三種方式
LEFT OUTER JOIN:進行左側外部合併
RIGHT OUTER JOIN:進行右側外部合併
FULL OUTER JOIN: 進行完全外部合併
SELECT select_list
FROM table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference
ON condition | USING(column)
select_list:欄位名稱
table_reference:表格名稱和子查詢
condition:合併條件
column:合併條件所使用的欄位名稱
查詢哪些員工沒有部門
select e.last_name,d.dept_name
from emp e right outer join dept d on(e.dept_id=d.dept_id);
查詢哪些部門沒有員工
select e.last_name,d.dept_name
from emp e left outer join dept d on(e.dept_id=d.dept_id);
查詢哪些部門沒有員工與哪些員工沒有部門
select e.last_name,d.dept_name
from emp e full outer join dept d on(e.dept_id=d.dept_id);
迪卡兒積(直積)介紹
- 多方表格的行數進行相乘,如果a表格有10筆資料,b表格有10筆資料,a與b表格進行合併所輸出的筆數為100行,因為10*10=100,這種型態的合併用於多種資料配對
- 語法示範
查詢結果為emp表格與dept表格的多種資料配對
select * from emp,dept;
何謂子查詢
- SELECT語句除了可以進行一般查詢外,還可以把查詢的結果再交給別的指令句,這樣稱為subquery,子查詢可以使SQL更進階的複雜查詢,雖然複雜但很方便,可以跨越多個表格進行查詢
子查詢可以用在哪些地方
- WHERE子句
- HAVING子句
- INSERT句
- FROM語句
- CREATE TABLE句
- CREATE VIEW句(CREATE MATERIALIZED VIEW句)
- UPDATE句
- DELETE句
子查詢所使用的操作符介紹
子查詢傳回的單一結果只能使用這些操作符(=,<,<=,>,>=,<>,!=),若子查詢的結果為多行,則會錯誤
子查詢回傳多列結果可使用這些操作符(IN,ALL,ANY),(IN,ALL,ANY)也可以搭配單列子查詢的操作符
使用在WHERE子句
查詢dept_id為50的員工姓名
select last_name from emp
where dept_id=(select deptid from dept where dept_id=50)
使用在HAVING子句
查詢哪個部門編號的總薪水大於部門編號10的總薪水
select dept_id,sum(salary) from emp
group by dept_id
having sum(salary)>(select sum(salary) from emp where dept_id=10);
使用在INSERT句
新增所有employees表格資料到emp表格裡
insert into emp select * from employees;
使用在FROM語句
查詢誰的薪水等於24000
select last_name from (select last_name from emp where salary=24000);
使用在CREATE TABLE語句
創建emp表格,並新增employees所有資料到emp表格
create table emp as select * from employees;
使用在CREATE VIEW語句和CREATE MATERIALIZED VIEW語句
創建vw_emp檢視,並檢索emp表格所有資料
create view vw_emp as select * from emp;
創建vw_emp實體檢視,新增emp表格所有資料
create materialized view vw_emp as select * from emp;
使用在UPDATE句
employee_id為100的薪水,修改為所有員工的總薪水
update emp set salary=(select sum(salary) from emp) where employee_id=100;
使用在DELETE句
刪除所有emp表格裡的資料
delete from (select * from emp);
何謂FOR UPDATE子句
- 在ORACLE世界裡,針對資料做新增,修改,刪除都會產生鎖定,唯一查詢時不會產生鎖定,而FOR UPDATE的用處就是加入鎖定在查詢上
如何解除鎖定
- 每次的交易都會有結束的時間點,只要下rollback或是commit即可解除鎖定
格式
FOR UPDATE [column [,column...]]
[NOWAIT | WAIT n | SKIP LOCKED]
column:欄位名稱
n:要等幾秒,才能再度使用列的資料
NOWAIT:馬上傳回控制值
WAIT:等到n秒,回傳列資料
SKIP LOCKED:略過已鎖定的列
使用方式
鎖定emp表格的salary資料,employee_id為100
select salary from emp where employee_id=100 for update;
如果employee_id為100本身就有鎖定,就不等待,如果沒有鎖就鎖定employee_id為100
select salary from emp where employee_id=100 for update nowait;
如果employee_id為100本身就有鎖定,我查詢這筆資料等5秒,時間一到就放棄鎖定這筆資料,除非對方下rollback或commit
select salary from emp where employee_id=100 for update wait 5;
如果employee_id為100本身就有鎖定,查詢資料時略過已鎖定的列
select salary from emp where employee_id=100 for update skip locked;
何謂ORDER BY
- 把列做排序,一般來說你查詢出來的資料順序是不固定的排序,使用ORDER BY子句,可以方便排序查詢資料,你可以設定查詢資料需要大到小或是小到大,它是連接在FROM語句下面
格式
ORDER BY {expr | position | alias} [ASC | DESC]
[NULLS FIRST | NULLS LAST]
expr:欄位名稱
position:欄位位置
alias:欄位別名
使用方式
排序emp表格裡的salary,由小到大
select salary from emp
order by salary asc;
排序emp表格裡的salary,由大到小
select salary from emp
order by salary desc;
排序emp表格裡的salary,由小到大,如果有null值為第一個輸出
select salary from emp
order by salary asc nulls first;
排序emp表格裡的salary,由大到小,如果有null值為最後一個輸出
select salary from emp
order by salary desc nulls last;
排序emp表格裡的salary,由小到大
select last_name,salary from emp
order by 2;
補充
ORDER BY預設為小到大,只要中間加個逗號將欄位區隔開來,就可進行多欄位排序
何謂GROUP BY子句
群組化函數介紹
count:總筆數
min:一列資料的最小值
max:一列資料的最大值
avg:一列資料平均值
sum:一列資料總共值
使用方式
查詢emp表格,每個部門的最低薪水
select dept_name,min(salary)
from emp
group by dept_name;
查詢emp表格,每個部門的最高薪水
select dept_name,max(salary)
from emp
group by dept_name;
查詢emp表格,每個部門的平均薪水
select dept_name,avg(salary)
from emp
group by dept_name;
查詢emp表格,每個部門的總薪水
select dept_name,sum(salary)
from emp
group by dept_name;
查詢emp表格,每個部門的總人數
select dept_name,count(*)
from emp
group by dept_name;
補充
使用群組化函數,不一定要搭配GROUP BY子句,除非你的資料查詢想要多一個欄位,就一定要用GROUP BY子句,免得語法編譯錯誤
何謂WHERE子句
- WHERE子句是限制你所查詢出來的資料,它是連接在FROM子句下面
格式
WHERE condition
condition:限制列的條件語法
WHERE子句使用
select salary from emp
where emp_id=100;(尋找emp_id為100的薪水)
補充
where它可以限制各種的資料型別
SELECT與FROM介紹
- select語法與from語法用的頻率非常的高,想從表格讀取資料,就一定會使用到這種語法
格式
- SELECT [ALL | DISTINCT] select_list FROM table_reference
select_list:表格欄位名稱
table_reference:要查詢的表格名稱
使用方式
select last_name,salary from emp;(查詢emp表格裡的last_name和salary)
select last_name,distinct salary from emp;(查詢emp表格裡的last_name和不從覆的salary)
select * form emp;(查詢emp表格的所有資料)
補充
星號(*):代表為所有欄位名稱
distinct:回傳不從覆的資料
何謂EXISTS條件
格式
subquery:子查詢
使用方式
- 下面一小段SQL語法示範(尋找哪個部門是有存在員工的)
select dept_name from dept d
where exists (select * from emp e where e.dept_id=d.dept_id);
- 下面一小段SQL語法示範(尋找哪個部門是沒有存在員工的)
select dept_name from dept d
where not exists (select * from emp e where e.dept_id=d.dept_id);
注意使用EXISTS條件,main query(外部查詢)和subquery(子查詢)一定要有連接,例如e.dept_id=d.dept_id,如果沒有連結EXISTS條件使用是無效的
何謂IN條件
格式
- expr1 [NOT] IN ({subquery | expr2 [,expr3...]})
expr1:欄位名稱
subquery:子查詢
expr2,expr3:要比較的值
IN使用
select salary from emp where salary in (300,500);(尋找薪水為300和500)
select salary from emp where salary not in (300,500);(尋找薪水不為300和500)
何謂BETWEEN條件
- 他是判斷值是否存在於指定(數值或日期) 範圍內的條件
格式
- expr1 [NOT] BETWEEN expr2 AND expr3
expr1:欄位名稱
expr2:起始範圍
expr3:終止範圍
BETWEEN條件使用
select last_name from emp where salary between 1000 and 5000;(尋找薪水在1000到5000之間)
select last_name from emp where salary not between 1000 and 5000;(尋找薪水不在1000到5000之間)
LIKE條件說明
- LIKE這種條件需要搭配通用字符,其實LIKE就是像的意思,使用這種LIKE條件在尋找資料是非常方便
通用字符
- -(底線):任何單一字符
- %:任何長度為零字符以上的字串
LIKE條件使用
char1 [NOT] LIKE char2 [ESCAPE esc_char]
char1:檢索值,欄位名稱
char2:檢索樣式
esc_char:逸出字符
select last_name from emp where last_name like '%a';(我要找在emp表格裡的last_name他的名子像是最後一個字元為a)
select last_name from emp where last_name like 'a%';(我要找在emp表格裡的last_name他的名子像是第一個字元為a)
select last_name from emp where last_name not like 'a%';(我要找在emp表格裡的last_name他的名子不像是第一個字元為a)
select last_name from emp where last_name like '%aa%';(我要找在emp表格裡的last_name他的名子像是中間字串為aa)
select last_name from emp where last_name like '_a';(我要找在emp表格裡的last_name他的名子是第二個字元為a)
select last_name from emp where last_name like 'cc_';(我要找在emp表格裡的last_name他的名子是倒數第二個字串為cc)