語法說明
- 建立實體化檢視日誌,須進行實體化檢視高速復新就必須建立實體化檢視日誌
- 實體化檢視日誌物件稱為MLOG$_table_name的表格,來源表格資料更新時,變更資訊會存放此表格,高速復新時,以變更資訊為來源,實體化檢視只會反映出變更差異
格式
CREATE MATERIALIZED VIEW LOG ON [schema.]table_name
[WITH with_clause] [{INCLUDING | EXCLUDING} NEW VALUES];
schema : 結構名稱
table_name : 物件名稱
with_clause : 指定要描述的資訊
with_clause子句說明
- WITH子句可在實體化檢視日誌指定要描述資訊,可指定項目包括物件ID,主鍵,ROWID,
序列,表格欄位等,省略WITH子句,預設描述主鍵
with_clause格式
{OBJECT ID | PRIMARY KEY | ROWID | SEQUENCE | (column [,column...])
[,OBJECT ID | PRIMARY KEY | ROWID | SEQUENCE | (column [,column...]) ... ] }
column : 對於更新全列指定要記錄在實體化檢視日誌的欄位
OBJECT ID : 記錄對於更新全列的物件識別符,建立物件表格日誌時可以指定
PRIMARY KEY : 記錄更新全列主鍵
ROWID : 記錄更新全列ROWID
SEQUENCE : 紀錄表示附加序列資訊的序列值,進行高速復新時,必須要在實體化檢視日誌裡記錄此值
NEW VALUES子句說明
- NEW VALUES子句可指定更新前後的資訊處理方法
- NEW VALUES子句指定值
- INCLUDING : 記錄更新前後資訊,此為預設值
- EXCLUDING : 只記錄更新前的資訊
語法示範
說明 : 建立實體化檢視日誌
create materialized view log on emp;
說明 : 建立實體化檢視日誌,加入描述
create materialized view log on emp
with sequence,rowid(ename,salary)
including new values;
語法說明
刪除MATERIALIZED VIEW物件
格式
DROP MATERIALIZED [schema.]mview_name [PRESERVE TABLE];
schema : 結構名稱
mview_name : 物件名稱
語法示範
說明 : 刪除mview_emp物件
drop materialized view mview_emp;
語法使用說明
- 針對 MATERIALIZED VIEW物件做設定,可設定項目如下:
- 復新類型
- 復新模式
- 依據重新編譯所做的變更
- 設定是否使用查詢覆寫
- 根據CONSIDER FRESH子句所做的變更
- 分割的維護操作
格式
ALTER MATERIALIZED VIEW [schema.]mview_name
{REFRESH alter_mv_refresh | {ENABLE | DISABLE } QUERY REWRITE | COMPILE};
schema : 結構名稱
mview_name : 物件名稱
alter_mv_refresh : 復新類型和復新模式
設定查詢覆寫所需權限
- 實體化檢視內所有實體表格都在內部結構內(狀況) : QUERY REWRITE 權限
- 實體化檢視的任一實體表格在外部結構內(狀況) : GLOBAL QUERY REWRITE 權限
- 實體化檢視在別的使用者的結構內(狀況) : 使用者與該結構擁有者兩方需有QUERY REWRITE 權限
語法示範
說明 : 重新編譯實體化檢視mv_emp
alter materialized view mv_emp compile;
說明 : 指定REFRESH子句可變更實體化檢視所設定的復新類型和復新模式
alter materialized view mv_emp refresh fast;
何謂CREATE MATERIALIZED VIEW
- 建立實體化檢視,表格查詢情境中參照頻率較高,比一般檢視具有較高的效能,透過子查詢新增資料到實體化檢視,假如主表格被移除,實體化檢視的資料仍然存在,可以進行正常查詢操作
格式
CREATE MATERIALIZED VIEW mview_name
[BUILD {IMMEDIATE | DEFERRED}]
[REFRESH {COMPLETE | FAST | FORCE}] [ON {COMMIT | DEMAND}]
[FOR UPDATE]
[{ENABLE | DISABLE} QUERY REWRITE]
AS SELECT select_statement;
mview_name : 實體化檢視名稱
BUILD IMMEDIATE : 透過子查詢所新增的資料,立即交易
BUILD DEFERRED : 透過子查詢所新增的資料,延遲交易
REFRESH COMPLETE : 完全更新實體化檢視資料,當主表格資料做更新
REFRESH FAST : 遞增更新,當主表格資料更新時,只反映更新部分的資料,注意必須要建立實體化檢視日誌,才可使用這項功能
REFRESH FORCE : 先以FAST進行更新,無法進行再以COMPLETE進行更新,此為預設值
ON COMMIT : 自動更新,當主表格交易結束
ON DEMAND : 手動更新,必須搭配DBMS_MVIEW包裝的REFERSH程序使用進行更新,此為預設值
FOR UPDATE : 可對實體化檢視操作DML語法
ENABLE QUERY REWRITE : 使用查詢覆寫
DISABLE QUERY REWRITE : 關閉查詢覆寫
select_statement : 子查詢
使用方式
說明 : 建立實體化檢視,名稱為emp_view,透過emp表格做資料新增,並可以使用查詢覆寫,所使用的交易型態為立即交易
create materialized view emp_view
build immediate
refresh complete
enable query rewrite
as select * from emp;
何謂DROP INDEX
格式
DROP INDEX [schema.]index_name;
schema : 架構名稱
index_name : 索引名稱
使用方式
說明 : 刪除hr使用者的索引,名稱為app
drop index hr.app;
何謂ALTER INDEX
- 針對索引的架構做修改,此處說明索引常用的變更方式,分別為索引重建與變更索引名稱
為何要索引重建
- 這項操作不是新增索引,而是針對索引架構內的內容做更新,因為索引所記錄的資料塊位置被變動,所以索引才需要從新指向資料塊位置
格式
ALTER INDEX [schema.]index_name
{REBUILD | RENAME TO new_index_name};
使用方式
說明 : 重建索引架構,索引名稱為app
alter index app rebuild;
說明 :變更索引名稱app為app2
alter index app rename to app2;
何謂CREATE INDEX
- 建立索引在表格欄位上,它可以大幅提升查詢效能,可是對於新增,修改,刪除的操作會降低資料庫的效能,所以建立索引一般是配合查詢操作使用
索引型態介紹
- 複合式索引 : 所建立的索引可參考多個欄位
- 函數索引 : 與函數搭配建立索引
- 一般索引 : 不屬於複合式索引與函數索引
格式
CREATE [UNIQUE | BITMAP] INDEX [schema.]index_name
ON [schema.]table_name(column [,column...]);
UNIQUE : 建立唯一索引,唯一索引所參考到的欄位不能有重複值
BITMAP : 建立位元映射索引,通常使用位元映射索引是針對重複性高的欄位值
schema : 結構名稱
index_name : 索引名稱
table_name : 表格名稱
column : 欄位名稱
使用方式
說明 : 建立唯一索引(又可稱為一般索引)在hr使用者的emp表格的xid欄位上,索引名稱為id
create unique index id on hr.emp(xid);
說明 : 建立位元映射索引(又可稱為複合式索引)在hr使用者的emp表格的xid欄位與name欄位上,索引名稱為x_id
create bitmap index x_id on hr.emp(xid,name);
說明 : 建立索引(又可稱為函數索引)在hr使用者的emp表格的name欄位上,該欄位被索引參考到的值是為大寫,因為使用大寫函數,索引名稱為app
create index app on hr.emp(upper(name));
何謂GRANT-物件權限
格式
GRANT {privilege_name | ALL [PRIVILEGES]}
[(column [,column...])]
ON { [schema.]object_name |
DIRECTORY directory_name |
JAVA {SOURCE | RESOURCE} [schema.]object_name }
TO {grantee | PUBLIC}
[WITH GRANT OPTION];
privilege_name : 物件權限,例如SELECT,INSERT,UPDATE,DELETE
ALL [PRIVILEGES] : 所有物件權限
column : 欄位名稱
schema : 結構名稱
object_name : 物件名稱
directory_name :目錄名稱
grantee : 被授予的使用者或角色
PUBLIC : 所有使用者
WITH GRANT OPTION : 被授予物件權限的使用者可在授予別人,稱為轉授權
使用方式
說明 : 授予aa使用者物件權限,可針對hr使用者emp表格的salary與employee_id欄位進行修改
grant update (salary,employee_id) on hr.emp to aa;
說明 : 授予aa使用者所有物件權限,可針對hr使用者emp表格進行新增,修改,刪除,查詢等,並轉授權
grant all on hr.emp to aa with grant option;
說明 : 授予所有使用者物件權限,可針對hr使用者emp表格進行查詢
grant select on hr.emp to public;
何謂GRANT-系統權限
格式
GRANT {privilege_name [,privilege_name...] | ALL PRIVILEGES}
TO {grantee [,grantee ...] | PUBLIC}
[WITH ADMIN OPTION];
privilege_name : 系統權限或角色
ALL PRIVILEGES : 所有系統權限
grantee : 被授予的使用者或角色
PUBLIC : 所有使用者
WITH ADMIN OPTION : 被授予系統權限或角色的使用者可在授予別人,簡稱轉授權
使用方式
說明 : 授予connect角色與create table系統權限給hr使用者,並轉授權
grant connect,create table to hr with admin option;
說明 : 授予所有系統權限給aa使用者
grant all privileges to aa;
說明 ; 授予create view系統權限給所有使用者
grant create view to public;
何謂DROP DIRECTORY
格式
DROP DIRECTORY directory_name;
directory_name : 目錄名稱
使用方式
說明 : 刪除作業系統上的目錄為awi
drop directory awi;
何謂CREATE DIRECTORY
- 它是一個目錄物件,它會在作業系統建立目錄,並可以提供使用者針對資料庫的資料做匯入,匯出所使用的資料存放路徑,並透過expdp(匯出)與impdp(匯入)來針對資料做匯入或匯出,expdp與impdp是屬於Oracle的應用程式,注意在Oracle 10g前使用exp與imp,在Oracle 10g後則使用expdp與impdp
權限
- 使用者想對資料做匯入,匯出,則必須有READ物件權限與WRITE物件權限,才能使用目錄
格式
CREATE [OR REPLACE] DIRECTORY directory_name
AS 'directory_path';
OR REPLACE : 從覆使用目錄
directory_name : 目錄名稱
directory_path : 資料存放在作業系統入徑
使用方式
說明 : 建立目錄名稱為awi,在作業系統的/home/oracle/Desktop路徑
create directory awi as '/home/oracle/Desktop';
說明 : 授予權限,讓使用者hr可以使用目錄,名稱為awi
grant read,write on directory awi to hr;
何謂DROP DATABASE LINK
- 移除資料庫連結,如果指定PUBLIC可移除公用資料庫連結
格式
DROP [PUBLIC] DATABASE LINK dblink_name;
dblink_name : 資料庫連結名稱
使用方式
說明 : 移除資料庫連結名稱為king
drop database link king;
說明 : 移除公用的資料庫連結名稱為app
drop public database link app;
何謂CREATE DATABASE LINK
- 可建立資料庫連結,透過資料庫連結名稱可以使用遠端資料庫的物件進行操作,所使用的連結符號為@
格式
CREATE [PUBLIC] DATABASE LINK dblink_name
[CONNECT TO user IDENTIFIED BY password]
USING 'connect_string';
PUBLIC : 本端的資料庫使用者,都可以使用資料庫連結名稱
dblink_name : 連結名稱
user : 連入遠端資料庫的使用者名稱
password : 連入遠端資料庫的使用者密碼
connect_string : 遠端資料庫的服務名稱(Local Naming)
使用方式
說明 : 建立遠端資料庫連線到prod,資料庫連結名稱為hr_emp,使用hr_emp可操作遠端資料庫的hr使用者的所有物件
create database link hr_emp connect to hr identified by hr using 'prod';
說明 : 查詢遠端資料庫物件
select count(*) from employees@hr_emp;
說明 : 建立遠端資料庫連線到prod,資料庫連結名稱為king,使用king可操作遠端資料庫的物件,此物件必須存在本端使用者的物件,而如果指定public關鍵字,代表本端使用者都可以使用資料庫連結名稱
create public database link king using 'prod';
補充 : 如果沒有指定public關鍵字,則代表建立資料庫連結名稱的使用者,只能給自己使用,其它使用者則不能存取
何謂COMMENT
- 可以為物件加上註解,可針對表格,檢視,實體化檢視,表格欄位,檢視欄位,實體化檢視欄位,操作符,索引類型
格式
COMMENT ON object_type [schema.]object_name IS 'string';
object_name : 加上註解的物件種類
schema : 結構名稱
object_name : 加上註解的物件名稱
string : 註解內容
使用方式
說明 : 在hr使用者的emp表格加上註解,內容為'員工表格'
comment on table hr.emp is '員工表格';
說明 : 在emp表格的salary欄位加上註解,內容為'薪水'
comment on column emp.salary is '薪水';
何謂NOAUDIT
- 停止監控使用者對資料庫的操作,稱為停止稽核,可停止三種稽核,分別為陳述稽核,權限稽核,物件稽核
格式
NOAUDIT no_audit_option
[WHENEVER SUCCESSFUL | WHENEVER NOT SUCCESSFUL];
no_audit_option : 停止稽核目標
WHENEVER SUCCESSFUL : 停止稽核對於正確執行SQL與操作
WHENEVER NOT SUCCESSFUL : 停止稽核對於錯誤執行SQL與操作
no_audit_option格式
{statement_option [,statement_option...] | ALL}
[BY {proxy | user}]
statement_option : 陳述選項,例如INDEX,INSERT TABLE等SQL語句
ALL : 所有使用者的陳述選項
proxy : 代理伺服器名稱
user : 使用者名稱
{system_privilege [,system_privilege...] | ALL PRIVILEGES}
[BY {proxy | user}]
statement_option : 系統權限,例如CREATE ANY TABLE,CREATE ANY INDEX
ALL PRIVILEGES : 所有使用者的系統權限
proxy : 代理伺服器名稱
user : 使用者名稱
{object_option [,object_option...] | ALL}
{ON [schema.]object_name | ON DIRECTORY directory_name | ON DEFAULT}
object_option : 物件權限,例如SELECT,DELETE,UPDATE,INSERT
ALL : 所有使用者的物件權限
ON : 可以指定目標物件為表格,檢視,序列,預儲程序,預儲函數,預儲程式包裝,實體化檢視,函數庫
DEFAULT : 建立預設的物件選項,指定AUDIT執行後,所建立的物件選項會當成預設的稽核選項應用
schema : 結構名稱
object_name : 物件名稱
directory_name : 目錄名稱
使用方式
說明 : 停止權限稽核,針對hr使用者的alter any table系統權限
noaudit alter any table by hr;
說明 : 停止物件稽核,針對hr使用者emp表格的SELECT物件權限
noaudit select on hr.emp;
說明 : 停止陳述稽核,針對hr使用者執行DDL語句
noaudit index by hr;
何謂AUDIT
- 它被稱為稽核,所謂的稽核是可以監控使用者對資料庫的操作,可分為三種稽核方式,分別為陳述稽核,權限稽核,物件稽核,在使用稽核前,要先設定資料庫的稽核參數
何謂稽核參數
- 稽核參數又稱為AUDIT_TRAIL,它有多種值可以使用,以下是介紹常用的參數值
DB:稽核資料記錄在DBA_AUDIT_TRAIL表格裡
OS:稽核資料記錄在OS裡,所在的路徑為AUDIT_FILE_DEST參數所指定的目錄內
XML:稽核資料記錄在V$XML_AUDIT_TRAIL表格裡,也會存放在OS裡,檔案格式為xml檔,所在的路徑為AUDIT_FILE_DEST參數所指定的目錄內
NONE: 禁止使用資料庫稽核
格式
AUDIT audit_option
[BY {SESSION | ACCESS}]
[WHENEVER SUCCESSFUL | WHENEVER NOT SUCCESSFUL];
audit_option : 稽核目標
BY SESSION : 在同一連線中,多次執行稽核目標操作時,所有的操作輸出稽核資訊,此為預設值
BY ACCESS : 在同一連線中,多次執行稽核目標操作時,只對第一次操作輸出稽核資訊
WHENEVER SUCCESSFUL : 稽核正常執行的SQL與操作
WHENEVER NOT SUCCESSFUL : 稽核失敗或發生錯誤的SQL和操作
audit_option格式
{statement_option [,statement_option...] | ALL}
[BY {proxy | user}]
statement_option : 陳述選項,例如ALTER SYSTEM,ALTER INDEX等SQL語句
proxy : 代理伺服器名稱
user : 使用者名稱
ALL : 所有SQL語句稽核
{system_privilege [,system_privilege...] | ALL PRIVILEGES}
[BY {proxy | user}]
system_privilege : 系統權限,例如CREATE TABLE系統權限或CREATE INDEX系統權限等
proxy : 代理伺服器名稱
user : 使用者名稱
ALL PRIVILEGES : 所有權限稽核
{object_option [,object_option...] | ALL}
{ON [schema.]object_name | ON DIRECTORY directory_name | ON DEFAULT}
object_option : 成為稽核目標操作,例如DELETE,READ,INSERT,UPDATE等操作
ALL : 會稽核所有可在object_option指定操作
ON : 可以指定目標物件為表格,檢視,序列,預儲程序,預儲函數,預儲程式包裝,實體化檢視,函數庫
DEFAULT : 建立預設的物件選項,指定AUDIT執行後,所建立的物件選項會當成預設的稽核選項應用
schema : 結構名稱
object_name : 物件名稱
directory_name : 目錄名稱
使用方式
說明 : 稽核hr使用者擁有表格查詢操作
audit select table by hr;
說明 : 針對使用者hr使用create any table系統權限作稽核
audit create any table by hr;
說明 : 對hr使用者擁有的emp表格的查詢進行稽核
audit select on hr.emp;
何謂ANALYZE
- 它可以分析表格(table),索引(index),叢集(cluster)架構,並收集統計訊息
格式
ANALYZE object_clause validation_clause;
object_clause : 分析目標物件
validation_clause : 分析內容
object_clause格式
{TABLE | INDEX | CLUSTER} [schema.]object_name
schema : 結構名稱
object_name : 物件名稱
validation_clause格式
能對目標表格REF值的妥當性進行檢查,指定SET DANGLING TO NULL的情況中,若指定表格內REF值為無效物件,或者指定不存在物件情況中,REF值設為NULL
格式
VALIDATE REF UPDATE [SET DANGLING TO NULL]
驗證目標物件架構
格式
VALIDATE STRUCTURE [CASCADE]
[INTO [schema.]table_name]
[OFFLINE | ONLINE]
schema : 結構名稱
table_name : 物件名稱
CASCADE : 確認表格各列都擁有索引記錄,確認各項索引指向表格列
INTO : 只有在分割表格有效子句,擁有無法正確對應到分割表格列的情況中,則ROWID會在table_name指定存放的清單表格
OFFLINE | ONLINE : Oracle在DML執行驗證則指定ONLINE,但分析叢集(cluster)與 索引(index)無法指定ONLINE,預設為OFFLINE
可以辨識分析過的表格與叢集的遷徒列與鏈結列,INTO子句指定列出遷徒列與鏈結列清單表格
格式
LIST CHAINED ROWS [INTO [schema.]table_name]
schema:結構名稱
table_name:物件名稱
刪除統計訊息,刪除後Oracle將無法使用統計訊息,如果指定SYSTEM則只會刪除系統統計訊息
格式
DELETE [SYSTEM] STATISTICS
使用方式
說明:分析emp表格有關的索引
analyze table emp validate structure cascade;
說明:刪除emp表格統計資訊
analyze table emp delete statistics;
何謂ALTER SYSTEM SWITCH LOGFILE
- 切換目前online redo log group到下一組online redo log group,這時候redo log buffer會透過LGWR寫入到新的online redo log group,如果在Archive模式下,再切換的同時會產生archive redo log檔,如果在No Archive模式下,再切換的同時不會產生archive redo log檔
格式
ALTER SYSTEM SWITCH LOGFILE;
使用方式
說明:切換online redo log group
alter system switch logfile;
何謂ALTER SYSTEM...SESSION
- 它可以中斷使用者連線,需要使用到V$SESSION表格裡的sid欄位值與serial#欄位值進行中斷
格式
ALTER SYSTEM { KILL SESSION 'n1,n2'} [IMMEDIATE];
n1: V$SESSION表格裡的sid欄位值
n2: V$SESSION表格裡的serial#欄位值
使用方式
說明:查詢HR使用者的sid與serial#,然後中斷HR使用者連線
select sid,serial# from v$session where username='HR';
alter system kill session '140,336' immediate;
補充:如果該使用者的交易還沒結束,你中斷使用者,而使用者的資料會自動rollback;
何謂ALTER SYSTEM FLUSH
- 它可對記憶體(SGA)的資料做刪除,分別針對Database Buffer Cache與Shared Pool做刪除,其它的記憶體區塊無法做刪除
格式
ALTER SYSTEM FLUSH {SHARED_POOL | BUFFER_CACHE}
SHARED_POOL:指定Shared Pool區塊
BUFFER_CACHE:指定Database Buffer Cache區塊
使用方式
說明:刪除Shared Pool區塊資料
alter system flush shared_pool;
何謂ALTER SYSTEM CHECK DATAFILES
- 檢查是否能存取所有線上檔案(datafiles)資料,除了唯讀檔以外
格式
ALTER SYSTEM CHECK DATAFILES;
使用方式
說明:檢查是否能存取所有線上檔案(datafiles)資料,除了唯讀檔以外
alter system check datafiles;
何謂ALTER SYSTEM CHECKPOINT
- 強制性把Database buffer cache變更的資料寫入到硬碟裡的資料檔案裡(datafiles),並寫入檢查點訊息到CONTROLFILES與DATAFILES,執行這段語句是要確認資料庫的一致性
格式
ALTER SYSTEM CHECKPOINT;
使用方式
說明:強制執行檢查點
alter system checkpoint;
補充:Oracle Server會自動執行檢查點,可以不用手動去執行
何謂ALTER SYSTEM ARCHIVE LOG
- 針對online redo log group作切換,切換模式分為ARCHIVELOG模式與NOARCHIVELOG模式,如果是ARCHIVELOG模式,再切換的同時會把目前的online redo log group的資料訊息備份到FRA(flash_recovery_area)變成archive redo log檔,如果是NOARCHIVELOG模式,再切換的同時不會產生archive redo log檔
格式
ALTER SYSTEM ARCHIVE LOG [INSTANCE 'instance_name']
{SEQUENCE n |
CHANGE n |
CURRENT [NOSWITCH] |
GROUP n |
LOGFILE 'file_name' [USING BACKUP CONTROLFILE] |
NEXT |
ALL
} [TO 'location'] ;
instance_name:實體名稱(RAC環境才能指定)
SEQUENCE的n為redo log排序號碼
CHANGE的n為系統變更號碼(SCN)
GROUP的n為群組值
CURRENT:切換下一組online redo log group
file_name:redo log檔名稱
location:放置redo log檔位置
使用方式
說明:切換redo log group
alter system archive log current;
說明: 切換redo log group,手動指定redo log排序號碼,注意要在MOUNT狀態
alter system archive log sequence 8;
說明: 切換redo log group,手動指定群組值,注意要在MOUNT狀態
alter system archive log group 2;
何謂ALTER SYSTEN RESET
- 它可以刪除資料庫的實體層(SPFILE)參數,資料庫需要從新啟動才會生效
格式
ALTER SYSTEM RESET parameter_name;
parameter_name:參數名稱
使用方式
刪除起始參數TIMED_STATISTICS
alter system reset TIMED_STATISTICS;
何謂ALTER SYSTEM SET
- 針對資料庫的系統參數做設定,可分為兩種參數設定模式,第一種可設定在記憶體層,但當資料庫從新啟動,在記憶體層所設定的參數也就失效了,第二種可設定在實體層(SPFILE),它為永久生效,也就是說當資料庫從新起動也不會失效
格式
ALTER SYSTEM SET parameter_name=parameter_value
[SCOPE={MEMORY | SPFILE | BOTH}];
parameter_name:參數名稱
parameter_value:參數值
MEMORY:設定在記憶體層
SPFILE:設定在實體層,從新啟動資料庫才會生效
BOTH:設定在記憶體層與實體層,為馬上生效
使用方式
設定FRA(flash_recovery_area)空間為2G,馬上生效
alter system set db_recovery_file_dest_size=2G scope=both;
設定記憶體管理空間為手動管理,從新啟動資料庫就失效了
alter system set sga_target=0 scope=memory;
何謂SET ROLE
- 使用者連入資料庫的作業階段,它可以設定使用者的角色在作業階段中是使用或是停用
格式
SET ROLE {role IDENTIFIED BY password] | ALL [EXCEPT role] | NONE};
role:角色名稱
password:密碼
使用方式
設定使用者在作業階段中使用abc角色
set role abc;
設定使用者在作業階段中使用所有角色,停用aa角色
set role all except aa;
何謂ALTER SESSION CLOSE DATABASE LINK
格式
ALTER SESSION CLOSE DATABASE LINK dblink;
dblink:遠端連線名稱
使用方式
關閉遠端資料庫連線,名稱為db_1
alter session close database link db_1;
何謂ALTER SESSION SET
- 使用者連入資料庫,使用者可以使用ALTER SESSION SET語句動態設定作業階段參數,而如果使用者離線或斷線這項設定也就失效,除非再次連入資料庫,從新在設定一次才有效
格式
ALTER SESSION SET parameter_name=parameter_valus
parameter_name:更新作業階段參數名稱
parameter_valus:更新作業階段參數的值
使用方式
設定連線階段的時間格式,nls_date_format為資料庫的時間參數
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
補充:資料庫有多種參數,有需要再針對資料庫參數進行設定
何謂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)
何謂CASE語法
- CASE語法等同於在SQL語法加上if子句,寫入你想要的判斷轉換資料數值,功能跟DECODE一樣,在閱讀上,CASE語法可讀性較高,建議使用CASE語法
CASE格式
CASE [expr]
WHEN comp_expr THEN return_expr
[WHEN comp_expr THEN return_expr]
[ELSE else_expr]
END;
expr:表格欄位
comp_expr:表格欄位條件判斷
return_expr:傳回值
else_expr:與表格欄位條件判斷不符合的傳回值
[]:代表裡面的語句可有可無,至少要一個WHEN comp_expr THEN return_expr語句
select case salary when salary>1000 then 500
when salary>2000 then 600
else 400
end;
from emp;
說明:在emp表格裡如果salary欄位的薪水大於1000就為500,如果薪水大於2000就為600,如果都不是就為400
select case when salary>1000 then 500
when salary>2000
then 600
else 400 end;
from emp;
說明:在emp表格裡如果salary欄位的薪水大於1000就為500,如果薪水大於2000就為600,如果都不是就為400
何謂集合操作符
- 多個查詢的結果進行合併,輸出成單一查詢結果,如果你想得到不同表格的相關資訊,可以使用這種方式達成
集合操作符介紹
- UNION:傳回不包含重複列的所有查詢結果
- UNION ALL:傳回包含重複列的所有查詢結果
- INTERSECT:傳回兩方查詢結果都有的部分(不包含重複列)
- MINUS:傳回第一個查詢結果有,而後一個查詢結果沒有的部分(不包含重複列)
集合操作符使用
select salary,department_id from emp
union
select salary,department_id from emp2
order by department_id ;
- UNION ALL使用方式,下列一小段sql語法示範
select salary,department_id from emp
union all
select salary,department_id from emp2
order by department_id ;
- INTERSECT使用方式,下列一小段sql語法示範
select salary,department_id from emp
intersect
select salary,department_id from emp2
order by department_id ;
select salary,department_id from emp
minus
select salary,department_id from emp2
order by department_id ;
補充
- 集合操作符可以合併多個select語句,要記住欄位數量要一致,資料型別要一致,order by 只能放在最後一行
註解介紹
- 註解是對SQL語法所做的解說(註釋),表達處理內容,目標欄位名稱,或是建立說明,實際處理SQL時會予以忽略,基本上就是程式碼註解
註解格式
--string (單行註解)
/* string */ (多行註解)
備註:string為註解內容
欄位別名方便性
- 某些欄位因為使用欄位連接符號造成欄位名稱較長,在欄位名稱閱讀上非常的不方便,使用別名可大幅縮短欄位名稱,易閱讀
欄位別名使用方式
- 在員工表格的薪資欄位你的別名想稱為salary或薪水,下列一小段sql語法示範(有兩種用法)
select 薪資 salary from 員工;(不易閱讀偷懶寫法)
select 薪資 as salary from 員工;(易閱讀正規寫法)
select 薪資 薪水 from 員工;(不易閱讀偷懶寫法,雙引號可加可不加)
select 薪資 as "薪水" from 員工;(易閱讀正規寫法,雙引號可加可不加)
備註:使用英文為別名名稱不論是大小寫,Oracle會自動把別名名稱轉大寫,如果想區分大小寫或想在別名中加入空格,就在別名名稱使用雙引號刮起來,例如"Salary"或 "salary"或"Sal ary",使用中文為別名名稱可用或可不用雙引號都可以,除非你的中文別名名稱有空格
何謂NULL值
- 在表格欄位也可以包含null值,他不是數字和字串也不是0值,他是一種空值,何謂空值呢?空值在表格欄位上顯示就是NULL,對空值做+-*/運算出來所輸出的結果還是NULL,除非使用函數對NULL值做轉換,NULL值可以放在各種資料型別欄位上
如何使用NULL做條件式比較
- 如果你想取出在員工表格的薪資為NULL值,下列一小段sql語法做示範
select 薪資 from 員工 where 薪資 is null;
- 如果你想取出在員工表格的薪資不為NULL值,下列一小段sql語法做示範
select 薪資 from 員工 where 薪資 is not null;
補充
- is 或 is not 的使用方式只能用在NULL值
第一種欄位連接符號(||)使用
- 在員工表格裡有薪資欄位和姓名欄位,如果你想把薪資欄位和姓名欄位做合併可以使用||符號,下面一小段sql語法示範:
select 姓名||薪資 from 員工;
- 如果你想在薪資欄位和姓名欄位加入文字可這樣使用,下面一小段sql語法示範:
select 姓名||'是'||薪資 from 員工;
- 你也可以使用多從合併(沒有限制可以合併多少表格欄位),下面一小段sql語法示範:
select 姓名||'是'||薪資||'元' from 員工;
第二種欄位連接符號(q'[]')使用
- 這第二種的欄位連接符號是方便表格欄位可連接單引號或雙引號等,如果你想連接單引號或雙引號可以這樣使用,下面一小段sql語法示範:
select 姓名||q'['是']'||薪資||q'['元']' from 員工;
select 姓名||q'['是']'||薪資 from 員工;
select 姓名||q'<'是'>'||薪資 from 員工;
select 姓名||q'#'是'#'||薪資 from 員工;
select 姓名||q'('是')'||薪資 from 員工;
備註:q可以為Q
補充
字符資料型別
- CHAR[(n [BYTE | CHAR] )]:固定長度字串資料型別
- NCHAR[(n)]:固定長度Unicode字串資料型別
- VARCHAR2[(n [BYTE | CHAR] )]:可變長度字串資料型別
- NVARCHAR2(n):可變長度Unicode字串資料型別
- CLOB:字元大型物件(bigfile)資料型別
- NCLOB:Unicode字元大型物件(bigfile)資料型別
n為引數
數值資料型別
- NUMBER[(n1[,n2])]:帶有精密度和位數的數值資料型別
- FLOAT[(n)]:NUMBER的子型別
- BINARY_FLOAT:浮點數(32位元)資料型別
- BINARY_DOUBLE:浮點數(64位元)資料型別
n為引數
n1為整數值幾位
n2為小數點下幾位
日期時刻及期間資料型別
- DATE:日期格式
- TIMESTAMP[(n)]:日期和時間格式
- TIMESTAMP[(n)] WITH TIME ZONE:整個TIMESTAMP與時區及個世界地區時差
- TIMESTAMP[(n)] WITH LOCAL TIME ZONE:整個TIMESTAMP沒有世界地區時差
- INTERVAL YEAR[(n)] TO MONTH:期間(年月單位)
- INTERVAL DAY[(n1)] TO SECODE[(n2)]:期間(日時分秒單位)
n為引數
n1為日期的位數
n2為秒的小數部分位數
二進制資料型別
- BLOB:二進制大型物件(例如:影像檔,圖片檔)
- BFILE:二進制檔案的定位符
- RAW(size):二進制資料
size:RAW資料的位元組數
ROWID資料型別
- ROWID:用來表示列位址的BASE64字串
- UROWID[(n)]:用來表示索引構成表格列之邏輯位址的BASE64字串
n為資料型別長度