1Z0-061 Oracle Database 12c: SQL Fundamentals
SELECT col1 || q'[ iven's test ]' || col2 "col name" from
escape identifier
WHERE job_id LIKE 'AD\_%' ESCAPE '\'; --使用\當作脫逸字元,將_當作一般的底線.使用ESCAP
抓前5筆資料
Select * from A order by B
fetch first 5 rows only;
跳過前n筆, 抓下面m筆
offset 5 rows fetch next 5 rows only;
##############################
INITCAP(‘HeLLo frAnk’) -> Hello Frank
CHR(70) -> F
ASCII(‘F’) -> 70
##############################
INSTR(‘HeLLo frAnk’,’L’) ->3 第一次出現L的位置
INSTR(‘HeLLo frAnk’,’L’,2,2) -> 4 第二次出現第二個L的位置
LPAD('HeLLo frAnk',15,'#') ->####HeLLo frAnk 左邊用#填滿到15個字元
POWER(3,2) -> 9 三的二次方
SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN
rank() over(order by )
SQL> CREATE TABLE t2
2 (col1 NUMBER(4) primary key,
3 col2 VARCHAR2(10),
4 col3 VARCHAR2(1) CONSTRAINT t2_col3_fk_t1_d REFERENCES t1(d),
5 col4 NUMBER NOT NULL CHECK(col4>0),
6 CONSTRAINT t2_col23_uk UNIQUE(col2,col3));
COLUMN LEVEL:
CONSTRAINT t2_col3_fk REFERENCES t1(col4)
TABLE LEVEL:
CONSTRAINT t2_col3_fk FOREIGN KEY(col3) REFERENCES t1(col4)
ON DELETE CASCADE
CONSTRAINT t2_col3_fk FOREIGN KEY(col3) REFERENCES t1(col4)
ON DELETE SET NULL
ALTER table A
SET UNUSED [COLUMN] (col1,col2);
ALTER table B
DROP UNUSED COLUMNS;
##############################
WorkshopII
1. DICTIONARY
2. user_objects
3.
COMMENT on TABLE t1
IS 'this is a comment';
COMMENT on column t1.col1
IS 'this is a column comment';
user_tab_comments
user_col_comments
4. DATABASE OBJECTS: Table/View/Sequence/Index/Syno
LESSON 3
1.Sequence
DESCRIBE user_sequences
2.Synonym 同義字
CREATE [public] SYNONYM d_sum
FOR dept_sum_vu;
DROP SYNONYM d_sum;
DESCRIBE user_synonyms;
3. INDEX
自動建立, 有設定PRIMARY KEY或UNIQUE的column
CREATE [unique] INDEX idx__name_emp_last_name
ON employees (last_name);
CREATE TABLE emp
(employee_id number(6) PRIMARY KEY USING INDEX (CREATE INDEX idx_emp_id ON emp (employee_id)),
first_name varchar2(20),
last_name varchar2(25));
DESCRIBE user_indexes
也可使用已經存在的index
ALTER TABLE new_emp ADD PRIMARY KEY (emp_id) USING INDEX idx_emp_id;
使用function-based index需要有QUERY REWRITE系統權限
QUERY_REWRITE_ENABLED 要設定為TRUE
ALTER INDEX idx_1 INVISIBLE;
desc user_ind_columns
DROP INDEX idx_emp_id [ONLINE];
online代表刪除的時間內可以DML
LESSON 4
CREATE or REPLACE VIEW v_abc
as
SELECT FROM WHERE
DESCRIBE user_views
CREATE VIEW 後面帶WITH CHECK OPTION可以確保DML的範圍是在where條件範圍內
WITH READ ONLY
DROP VIEW;
LESSON 5
Manage schema object
ALTER TABLE tb_abc
MODIFY col_id PRIMARY KEY;
ALTER TABLE tb_abc
ADD PRIMARY KEY (col_id);
加限制, 設定為Foreign Key
ALTER TABLE tb_abc
ADD CONSTRAINT tb_abc_col_fk
FOREIGN KEY (man_id)
REFERENCE tb_abc (emp_id);
ALTER TABLE tb_abc
DROP CONSTRAINT tb_abc_col_fk [ONLINE];
ALTER TABLE tb_abc
DROP PRIMARY KEY CASCADE;
連同參考此p key的f key條件drop掉
新增F key限制時, 可用on delete cascade, on delete set null
ALTER TABLE tb_abc
ADD CONSTRAINT fk_col
FOREIGN KEY col2
REFERENCE table1 (col_id) on delete cascade;
ALTER TABLE tb_abc
DROP COLUMN col CASCADE CONSTRAINTS;
用cascade constraints 去一併刪除這個欄位上的constraint
改名 for table, column, constraint
ALTER TABLE xxx
RENAME [column | constraint ] aaa to bbb;
ALTER TABLE
DISABLE|ENABLE constraints xxx;
DISABLE PRIMARY KEY CASCADE;
enable或disable,
ENABLE | DISABLE | VALIDATE | NOVALIDATE
ALTER TABLE
ENABLE NOVALIDATE primary key;
表示這個constraint在新資料insert時就要驗證, 已存在的資料可以不驗證
CONSTRAINT可延遲到commit時才validate
DEFERRABLE INITIALLY IMMEDIATE
DEFERRABLE INITIALLY DEFERRED
SET CONSTRAINT ALL DEFERRED;
SET CONSTRAINT ALL IMMEDIATE;
刪除TABLE
只做drop, 表格是被rename,也可在recycle bin中用flashback找回,purge直接刪掉,空出空
DROP TABLE ... PURGE
暫存表格
CREATE GLOBAL TEMPORARY TABLE xxx
ON COMMIT [ PRESERVE | DELETE ] ROWS
delete rows <- 表示transaction結束,表格就被刪除所有的rows
preserve rows <- 表示session結束才刪除rows
外部表格: 唯讀, metadata在資料庫中, 而資料在資料庫之外
EXTERNAL TABLE
先create一個 Dictionary
CREATE or REPLACE DICTIONARY xxx
as '/.../emp_dir';
GRANT READ ON DICTIONARY xxx to ora_21;
CREATE TABLE xxx
ORGANIZATION EXTERNAL
(
TYPE [ ORACLE_DATAPUMP | ORACLE_LOADER ]
DEFAULT DICTIONARY
ACCESS PARAMETERS
LOCATION
PARALLEL 4
REJECT LIMIT UNLIMITED
)
LESSON 6
Retrieving data by using sub-queries
SCALAR subquery
CORRELATED subquery
使用EXISTS
只要找到一個,子查詢就不會繼續
select * from table
where exists (select null from table1 where )
WITH xxx as
(select from )
xxx可以重複使用
LESSON 7
Manipulating data by using sub-queries
WITH CHECK OPTION
LESSON 8
Controlling User Access
給(系統)權限
GRANT create session, create table, create view, create sequence
to user;
建立腳色, 給腳色權限, 給使用者腳色
CREATE ROLE manager;
GRANT create table, create view TO manager;
GRANT manager TO user;
變更密碼
ALTER USER user1
IDENTIFIED BY password;
給(物件)權限
GRANT select
ON table1
TO user;
GRANT update (col1,col2)
ON table2
TO user, role;
[WITH GRANT OPTION] 授權之外,也給此使用者有授權的權限
移除權限
REVOKE select, insert
ON table
FROM user;
[CASCADE CONSTRAINTS]
授權給A WITH GRANT OPTION, 若A離職, 可以用REVOKE + CASCADE CONSTRAINTS
若直接DROP USER則 A授權的其他帳號則不影響
LESSON 9
Manipulating Data
在insert或update使用DEFAULT
INSERT ALL
into target_a VALUES()
into target_b VALUES()
into target_c VALUES()
conditional insert all
conditional insert first
INSERT [ ALL | FIRST ]
WHEN ...
THEN into values ()
WHEN ...
THEN into values ()
ELSE into values()
FROM table;
PIVOTING INSERT
類似將非正規化的資料轉換成正規化, non-relational -> relational
INSERT ALL
INTO sales_info VALUES ()
INTO sales_info VALUES ()
INTO sales_info VALUES ()
INTO sales_info VALUES ()
INTO sales_info VALUES ()
SELECT ...
FROM table;
MERGE INTO table1 a
USING (select * from table2 b)
ON (a.col1=b.col1)
WHEN MATCHED THEN
UPDATE SET
a.col1=b.col1,
a.col2=b.col2,
a.col3=b.col3
DELETE WHERE (b.col4 is not null)
WHEN NOT MATCHED THEN
INSERT VALUES (,,,);
救回被drop的table
FLASHBACK TABLE
DROP TABLE abc;
select original_name,operation, droptime from recyclebin;
flashback table abc to before drop;
清空回收桶
purge recyclebin;
select某一個時間點的值
SELECT * FROM TABLE
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE)
SELECT * FROM TABLE
VERSIONS BETWEEN [ SCN | TIMESTAMP ] between MINVALUE and MAXVALUE
LESSON 10
Managing data in different time zone
ALTER SESSION set time_zone = '-05:00';
current_date 回傳目前的日期, data type: date
current_timestamp 回傳目前的日期及時間 data type: timestamp with time zone
localtimestamp 回傳目前的日期及時間 data type: timestamp
SYSDATE 目前日期 data type:date
dbtimezone
sessiontimezone
區間
INTERVAL year to month
INTERVAL day to second
create table tbl1
(col1 number, warranty_time INTERVAL YEAR(3) TO MONTH);
insert into tbl1 values (123, INTERVAL '8' MONTH);
使用 EXTRACT
取出日期中的某個單位
SELECT EXTRACT ( { YEAR | MONTH | DAY | HOUR | ...| SECOND } FROM col1)
FROM table;
TZ_OFFSET 回傳 -04:00, +01:00
FROM_TZ(timestamp_value , time_zone_value)
轉換 timestamp 變成 timestamp with time zone
time_zone_value是character string
TO_TIMESTAMP('2017-07-20 11:00:00','YYYY-MM-DD HH:MI:SS')
轉換 string 變成 timestamp value
to_yminterval('01-02') 一年兩個月
to_dsinterval('100 10:00:00') 一百天十小時
LESSON 11
LESSON 12
SQL Developer
LESSON 13
SQL *PLUS
LESSON 14
SQL COMMANDs
LESSON 15
GROUP BY 後面可以接ROLLUP或CUBE
ROLLUP有加總的功能
select dept_no
, job_no
, sum(salary) salary
, grouping(dept_no) is_dept_total -- (可有可無) 判斷是否為 dept_no 欄位的彙總
, grouping(job_no) is_job_total -- (可有可無) 判斷是否為 job_no 欄位的彙總
from tomkuo1
group by cube(dept_no, job_no);
GROUP BY grouping sets
留言
張貼留言