標籤：
Oracle 兩種最佳化模式
1.cbo cbo模式下 where字句對查詢速度沒有影響
2.rbo 在rbo下 會先執行索引。。
oltp 聯機交易處理 指經常變更資料的系統。
olap 表資料分析 指資料不經常變的系統 只取資料做分析使用
子查詢解決的問題：問題不能一步求解時 考慮使用子查詢。。
子查詢注意的問題：
1.將子查詢放入括弧中
2.採用合理的換行和縮排
3.可以在主查詢的where from select having 放子查詢
3.1 select 後面的子查詢必須是單行子查詢
select ename,(select job from emp where empno=‘‘)
from emp;
3.2 where,having 後面的子查詢 用
= > >= < <= <> 必須是單行子查詢
in any all not in 可以是單行子查詢 也 可以是多行子查詢
3.3 from 後面的子查詢 是一張虛擬表
3.4 查詢每個部門最小工資 大於 部門Id是 50 最小工資的部門
select department_id, min(salary)
from employees
group by department_id
having min(salary) > (select min(salary)
from employees
where department_id =50)
4.不能在主查詢的group by 後面放子查詢
5.強調一下在from後面放置子查詢
6.主查詢和子查詢可以不是同一張表，只要子查詢返回的結果，主查詢可以使用
7.一般來講：不在子查詢中使用order by;但在Top-N 分析中，必須使用order by;
8.單行子查詢只能使用單行操作符;多行子查詢只能使用多行操作符
9.子查詢中的空值
多表查詢可以解決的問題盡量用多表查詢 但是 相互關聯的子查詢例外..
子查詢(內查詢) 在主查詢之前執行完成
子查詢的結果被主查詢使用(外查詢)
多行查詢符
1.any :和子查詢的任意一個值比較
--查詢工資比10號部門任意一個員工工資都高的員工資訊
select * from emp
where sal > any (select sal from emp where deptno = 10);
--其實大於 就是大於一個部門薪資最小值
-- 小於 就是小於一個部門薪資最大值
2.all:和集合的所有值進行比較
select * from emp
where sal > all (select sal from emp where deptno = 10);
--其實大於 就是大於一個部門薪資最大值
-- 小於 就是小於一個部門薪資最小值
子查詢中的空值問題
not in(10,20,null) 查不出資料
使用not in 不能有空值。。 因為not in 是跟所有值做比較
in （10，20，null）能有資料
因為 in 是跟任意一個值做比較
集合運算
並集 union/union all
交集 intersect
差集 minus
集合運算的問題
1.參與運算的各個集合必須列數相同，且類型一致
2.採用第一個集合的表頭作為最終的表頭
3.order by 必須放在每個集合後
group by rollup(A,B)
=group by a,b;
uoion
group by a;
uoion
group by null;
開啟sql執行的時間
set timing on
隱式插入空值 把隱藏的列空出來 insert into (ename,age）values（‘HAH‘,18）其他未有的列預設都插入空值
顯式插入空值 insert into emp(‘xx‘,‘xx‘,null,.....)
& 地址符
insert into emp(empno,ename,sal,deptno) values (&empno,‘&ename‘,&sal,&deptno);
select empno,ename,&sal from emp;
地址符可以放入所有的 （DML）動作陳述式中
往一個表中插入一個集合
insert into emp10 select * from emp where deptno =10
--插入要注意列的個數類型必須一致。。。
update tableName set 欄位=??
where
欄位 =？？
delete tableName
where
欄位 =？？
更新和修改要考慮資料的完整性 一定得加where子句 除了刪除或修改 所有
常見的資料庫物件
表 基本資料存放區集合，由行和列組成
視圖 從表中抽出的邏輯上相關的資料集合。
序列 提供有規律的數值
索引 提供查詢的效率
同義字 給對象起別名
命名規則
1.必須以字母開頭
2.必須在1-30個字元之間
3.必須只能包含A-Z a-z 0-9 _ $ #
4.必須不能和使用者定義的其他對象重名
5.必須不能是oracle的保留字
6.oralce預設儲存是都村委大寫
7.資料庫名只能是1-8位，datalink（資料鏈）是可以是128位，和其他一些特殊字元
create table
必須具備
create table 許可權
儲存空間
create table 【schema.】table
（column datatype 【default 運算式】）
查詢其他使用者的表
其他使用者的表不屬於本使用者的空間
如果要查詢其他使用者下的表，要是用其他使用者的使用者名稱作為首碼
資料類型
varchar2(size) 可變長字元資料
char(size) 定長字元資料
number(p,s) 可變長數值資料
date 日期型資料
long 可變長字元資料，最大可達到2G
clob 字元資料，最大可達到4G
raw and long raw 原始的位元據
blob 位元據，最大可達4G
bfile 儲存外部檔案的位元據，最大可達4G
rowid 行地址
--使用子查詢快速建表
create table empincome
as
select empno,ename,sal from emp;
--快速建表不要資料
create table empincome
as
select empno,ename,sal from emp where 1<>1;
--create 建立 alter 修改 drop 刪除 （表）
--追加列 ，修改列 ， 刪除列 ，重新命名列 （列）
1.追加列
alter talbe test2 add image blob; --add 增加
2.修改列
alter table test2 modify tname varchar2(30);--modify 修改
3.刪除列
alter talbe test2 drop column image;--drop 刪除
4.重新命名列
alter table test2 rename column tname to username;--rename 重新命名
--刪除表
drop table emp;-- 刪除在ORACLE資源回收筒中
--oralce資源回收筒
1.查看資源回收筒
show recyclebin;
--徹底刪除表
drop talbe emp purge; --徹底刪除不會清除
--管理員沒有資源回收筒 就是有dba許可權的就沒資源回收筒
--約束
--約束類型
1. NOT NULLL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
GENDER VARCHAR2(4) CHECK (GENDER IN (‘男‘,‘女‘));
或 constraint emp_yueshu_name check (salary>0);
--外鍵約束
1.分兩步刪除
刪除資料 先刪子表 在刪主表 或者先設子表資料為 null 在刪主表
2.一步刪除
FOREIGN KEY:在子表中，定義了一個表級的約束
REFERENCES:指定表和父表中的列
2.1 ON DELETE CASCADE:當刪除父表時，串聯刪除子表記錄
2.2 ON DELETE SET NULL: 將子表的相關依賴記錄的外鍵值置為null --建議使用 因為串聯刪除太危險
--列子
create table myperson
(
personID varchar2(18) constraint myperson_pk primary key,
name varchar2(20) constraint myperson_name not null,
gender varchar2(4) constraint myperson_gender check (gender in(‘男‘,‘女‘));
email varchar2(40) constraint myperson_email_u unique
constraint myperson_email_n not null,
deptno numberconstraint myperson_FK references dept(deptno) on delete cascade --串聯刪除子表資訊
)
--添加約束
-- primary key check unique foreign key
alter table tableName add constraint 約束名字 {【primary key】 (talbecolumnName) | 【check】(talbecolumnName in (‘‘,‘‘)|talbecolumnName >=< 10...) | 【unique】 (talbecolumnName) |
【foreign key(talbecolumnName) references tableName(tablecolumnName ) on delete cascade】};
--修改約束
--not null
alter table tableName modify columnName constraint 約束名 not null;
--defalut
alter table tableName modify columnName char(2) DEFAULT ‘M‘;
create view view1
as
select * from emp where deptno=10
with check option; --只能插入滿足視圖條件的資料
insert into view1(**,10);--ok
insert into view1(**,20);--error
或with read only; --唯讀
--禁用約束
alter table tableName disable 約束名
--啟用約束
alter table tableName enable 約束名
--延遲約束
alter table tableName employee add constraint fk_emp_infro foreign key (deptno)
references dept(dptno)
deferrable initially deferred;
--向表中添加註釋
comment on table tableName is ‘注釋’;
--向列中添加註釋
comment on column talbeName.columnName is ‘注釋‘;
--序列 ：可供多個使用者用來產生位移數值的資料庫物件
1.自動提供唯一的數值
2.共用對象
3.主要用於提供主鍵值
4.將序列值裝入記憶體可以提供訪問效率 記憶體訪問要比硬碟快。。
--[] 內容是可以省略的 [{}] 大括弧中必須選一項
create sequence sequencename
[increment by n]
[start with n] 開始數值
[{maxvalue n | nomaxvalue}]最大值
[{minvalue n | nominvalue}]最小值
[{cycle | nocycle}]迴圈還是不迴圈 預設不迴圈 迴圈的意思是當序列是最大一個的時候 在迴圈以 start n 數開始取值
[{cache n | nocache}];緩衝數 預設是 20
--select xx_seq.CURRVAL form dual; 取一個序列當前的值
select xx_seq.NEXTVAL form dual; 取一個序列下一個值
--修改序列
alter sequence sequenceName
increment by 20
maxvalue 999999
nocache
nocycle;
--刪除序列
DROP sequence sequenceName;
--索引
一種獨立於表的模式對象, 可以儲存在與表不同的磁碟或資料表空間中
索引被刪除或損壞, 不會對錶產生影響, 其影響的只是查詢的速度
索引一旦建立, Oracle 管理系統會對其進行自動維護, 而且由 Oracle 管理系統決定何時使用索引. 使用者不用在查詢語句中指定使用哪個索引
在刪除一個表時, 所有基於該表的索引會自動被刪除
通過指標加速 Oracle 伺服器的查詢速度
通過快速定位元據的方法，減少磁碟 I/O
create index indexName
on emp(deptno);
1.建立一個索引 類似於建立一個索引表
2.索引的作用就是讓不連續的資料 在索引表中連續起來
--什麼時候建立索引
1.列中資料值分布範圍很廣
2.列經常在where 子句或串連條件中出現
3.表經常被訪問 而且資料量很大，訪問的資料大概占資料總量的2% 到 4%
--什麼時候不要建立索引
1.表很小
2.列不經常作為串連條件或出現在where子句中
3.查詢的資料大於2% 到 4%
4.表經常更新
分為--B樹索引
位元影像索引
--建立索引
自動建立:
在定義 PRIMARY KEY 或 UNIQUE 約束後系統自動在相應的列上建立唯一性索引
手動建立:
使用者可以在其它列上建立非唯一的索引，以加速查詢
-- 刪除索引
drop index indexName
--查詢索引
可以使用資料字典視圖 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的資訊
SELECT ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND
ic.table_name = ‘EMPLOYEES‘;
--同義字 就是別名
--為了提高安全性
--賦許可權 grant create synonym to 使用者;
--建立同義字 create synonym hremp for emp.employees;
--刪除同義字 drop synonym synonymName;
oracle基礎二
