oracle數(shù)據(jù)庫(kù)筆記

  /*前言：這份資料是結(jié)合老師筆記，課堂案例，TTS6.0課件以及個(gè)人的理解整理，時(shí)間倉(cāng)促，如有紕漏敬請(qǐng)?jiān)彙?/
Oracle 數(shù)據(jù)庫(kù)
語(yǔ)法順序
select from on where group by having order by
執(zhí)行順序
from on where group by having select order by


一、關(guān)鍵字語(yǔ)法介紹
1、from on from后面接的是需要查詢的表格
on后面接的是表的連接條件和過濾條件
單表查詢直接跟上源表的名字
多表查詢跟上的是源表的名字和兩表之間的連接種類

2、where where語(yǔ)句實(shí)現(xiàn)的是對(duì)查詢表的結(jié)果集的篩選
where語(yǔ)句后面跟的是條件表達(dá)式(可以是列名、常量，比較運(yùn)算符，文字值)
between ..and.. 語(yǔ)句表示一個(gè)范圍，是兩邊的閉區(qū)間范圍
in 運(yùn)算符是表示范圍是一個(gè)由離散值組成的集合
like運(yùn)算符配合通配符進(jìn)行相關(guān)字符性的信息查找
%表示0或多個(gè)字符 _表示任意單個(gè)字符
is null表示值是空值null的行信息


3、group by 根據(jù)group by子句指定的表達(dá)式將要處理的數(shù)據(jù)分組


4、having 根據(jù)統(tǒng)計(jì)結(jié)果添加條件對(duì)分組后的組進(jìn)行過濾
只有符合having條件的組被保留
{where和having的區(qū)別}
where過濾的是行，having過濾的是分組
where可以跟任意列名、單行函數(shù)，having只能包含group by的表達(dá)式和組函數(shù)
where執(zhí)行在前，having執(zhí)行在后
where和having都不允許用列別名

5、select select語(yǔ)句把需要顯示的列的名稱或者是表達(dá)式進(jìn)行設(shè)定(查詢的核心步驟)
通過select語(yǔ)句得到需要的信息進(jìn)行顯示
distinct(去重)跟在select后面，用于對(duì)顯示的記錄進(jìn)行去重操作


6、order by 用于對(duì)select語(yǔ)句的結(jié)果集進(jìn)行排序的語(yǔ)句
后面跟上需要依賴進(jìn)行排序的列名以及asc(升序，默認(rèn))或者desc(降序)
7、insert into… 用于把記錄添加到表格當(dāng)中
insert into tabname(col1,col2/*不寫括號(hào)默認(rèn)添加行的所有列，書寫可以寫進(jìn)去相應(yīng)列的記錄，其他保持空值*/) values (val1,val2);
8、drop 用于刪除表格
drop table tabname cascade constraints purge;
/*cascade constraints用在刪除表格前先中斷與其他表格的外鍵約束關(guān)系*/
9、alter table 修改表格中的列的信息
alter table tabname modify (colname null);/*not null列改為null列*/
alter table tabname modify (colname default 1 not null);
/* null列改為not null列*/
alter table tabname add(建表時(shí)列定義方法);/*添加列*/
alter table tabname drop(colname);/*刪除列*/
10、synonym 同義詞
create synonym account for tarena.account;
/*設(shè)置account與tarena.account一致*/


二、數(shù)據(jù)類型
1、number類型 number(int a,int b) 表示該數(shù)值有a位有效數(shù)字，b位小數(shù)位
ex: number(6);第二個(gè)參數(shù)不寫默認(rèn)為0，表示從-999999~999999的整數(shù)
umber(4,3);4位有效數(shù)字，3位小數(shù)位，表示從-9.999~9.999的三位小數(shù)數(shù)字
umber(3,-3);3位有效數(shù)字，-3位小數(shù)位，表示（+-）1~(+-)999*103的數(shù)字
2、字符類型
a、char類型 按定義的字符長(zhǎng)度存
可以不定義長(zhǎng)度，缺省為1字節(jié)，最大長(zhǎng)度2000字節(jié)
b、varchar2類型 按字符串的實(shí)際長(zhǎng)度存
必須定義長(zhǎng)度，最大值為4000字節(jié)
/*列的取值是定長(zhǎng)，定義為char類型；列的取值長(zhǎng)度不固定，定義為varchar2類型*/
3、日期函數(shù)
缺省日期格式為DD-MON-RR
alter session set nls_date_format = “yyyy-mm-dd hh24:mi:ss”;
/*調(diào)整系統(tǒng)date函數(shù)缺省格式函數(shù)*/


三、比較和邏輯運(yùn)算符 
比較運(yùn)算符 : = , > , >= , < , <=
SQL比較運(yùn)算符 : between and , in , like , is null
邏輯運(yùn)算符 : and , or , not
否定形式 : 比較運(yùn)算符 : <> , != , ^=
SQL比較運(yùn)算符 : not between and , not in , not like , is not null


四、函數(shù)
1、字符函數(shù)
lower 把字符中的字母降為小寫
upper 把字符中的字母升為大寫
initcap 把字符中的首字符轉(zhuǎn)為大寫
concat 拼接函數(shù)的內(nèi)容
substr 求子串的函數(shù)
length 字符串的長(zhǎng)度
lpad 右對(duì)齊函數(shù)，位數(shù)不夠的，左邊補(bǔ)指定字符   
lpad('a',5,'b') 在a前方補(bǔ)4個(gè)b到5位
rpad 左對(duì)齊函數(shù)，位數(shù)不夠的，右邊補(bǔ)指定字符
trim 去掉字符串兩邊的空格和制表符
2、數(shù)值函數(shù)
round(num,x);對(duì)傳進(jìn)的num數(shù)值進(jìn)行四舍五入，保留x位小數(shù)
trunc(num,x);對(duì)傳進(jìn)的num數(shù)值進(jìn)行去尾操作，保留x位小數(shù)
3、日期函數(shù)
add months(Date date,number a) 一個(gè)日期加a各月
months between 兩個(gè)日期之間相差幾個(gè)月
ext day 下一天
last day 前一天
4、轉(zhuǎn)換函數(shù)
to date(str);把字符串轉(zhuǎn)為時(shí)間函數(shù)
to char(Date date,String reg);根據(jù)reg傳入的格式把date轉(zhuǎn)換為字符串
to number(str);把數(shù)字的字符串轉(zhuǎn)為number數(shù)據(jù)類型
5、一般函數(shù)
vl(col1,String str);遍歷col1列所有元素，有空值的會(huì)置換為str字符串


五、SQL語(yǔ)句中的分支
1、 case ...  then... 對(duì)where語(yǔ)句進(jìn)行優(yōu)先級(jí)篩選，從上到下，有一個(gè)滿足便馬上退出case
select  base_duration,base_cost,
case when  base_duration = 20 then unit_cost + 0.05
when  base_duration = 40 then unit_cost + 0.03
else 
unit_cost
end 
ew_unit_cost from cost;
當(dāng)base_duration找到符合的值便馬上套用值退出；
如果不添加else語(yǔ)句，不符合case中的base_duration的值此時(shí)會(huì)輸出null


2、decode 用法與case...when接近
SELECT a2.real_name cunstomer,
  DECODE(a1.real_name,a2.real_name,'No recommender', a1.real_name) recommender
FROM account a1 JOIN account a2
ON a1.id = NVL(a2.recommender_id,a2.id);
后面的參數(shù)分別是: 需要檢查的列名，條件1，執(zhí)行語(yǔ)句1，條件2，執(zhí)行語(yǔ)句2，... 不符合所有條件時(shí)返回的執(zhí)行語(yǔ)句
語(yǔ)句執(zhí)行的方法是: 找到當(dāng)前表格的列，檢查列中的每一個(gè)值是否符合各項(xiàng)的條件， 一有符合 的條件，馬上跳轉(zhuǎn)到執(zhí)行語(yǔ)句上并執(zhí)行后退出decode; 若列中的值沒有符合條件，則會(huì)執(zhí)行” 不符 合所有條件時(shí)返回的執(zhí)行語(yǔ)句”后結(jié)束decode。


六、子查詢
子查詢的概念是在一個(gè)查詢里面包含另外一個(gè)select語(yǔ)句(一般是where條件語(yǔ)句中)
1、非關(guān)聯(lián)子查詢
非關(guān)聯(lián)子查詢，先執(zhí)行子查詢，子查詢的返回結(jié)果作為主查詢的條件，再執(zhí)行主查詢。
子查詢只執(zhí)行一遍，子查詢結(jié)果若返回多值，會(huì)自動(dòng)去重。
2、關(guān)聯(lián)子查詢
關(guān)聯(lián)子查詢，采用循環(huán)的方式，查詢先從外部查詢，獲得一條記錄后，并將其傳入內(nèi)部查詢，內(nèi)部查詢從其結(jié)果中把值傳回外部查詢，若符合條件，就放入結(jié)果集，否則放棄。重復(fù)執(zhí)行以上步驟。
3、in函數(shù)和exist函數(shù)
in函數(shù)
多用在非關(guān)聯(lián)子查詢中，后面跟值或者是表達(dá)式
exist函數(shù) 多用在關(guān)聯(lián)子查詢中
采用的是循環(huán)(loop)方式，判斷outer表中是否存在記錄只要在inner表中找到一條匹配的記錄即可
in和extst的比較
exist使用循環(huán)方式，由outer表的記錄決定循環(huán)的次數(shù)，所以外表的記錄數(shù)要少
in先執(zhí)行子查詢，返回結(jié)果去重后再執(zhí)行主查詢，子查詢結(jié)果越少越適合用in函數(shù)


/*哪些客戶申請(qǐng)遠(yuǎn)程登錄服務(wù)*/
/*關(guān)聯(lián)子查詢*/ SELECT real_name,id FROM account o
WHERE EXISTS
  (SELECT 1 FROM service i WHERE i.account_id = o.id );
/*非關(guān)聯(lián)子查詢*/ SELECT real_name FROM account
WHERE id IN (SELECT account_id FROM service );


七、多表連接和多表查詢
1、交叉連接 tabname1 cross join tabname2
交叉連接得到的是表1和表2的笛卡爾積(兩個(gè)表里的元素一一對(duì)應(yīng))
2、內(nèi)連接 tabname1 join tabname2 on tabname.c1 = tabname2.c2 and
內(nèi)連接所達(dá)到的效果是兩表內(nèi)相關(guān)的信息保留，不會(huì)顯示沒有交叉的信息
多個(gè)條件的情況下，可以加上and繼續(xù)添加
執(zhí)行順序 : 先根據(jù)on后面的過濾條件進(jìn)行過濾，篩選出符合條件的行，
再根據(jù)on的連接條件將兩個(gè)表進(jìn)行連接。
{自連接}指的是驅(qū)動(dòng)表和匹配表都是同一張表，方法同內(nèi)連接一樣，給表起不同表別名
/*哪些os帳號(hào)的開通時(shí)間比同一臺(tái)機(jī)器上os帳號(hào)的平均開通時(shí)間長(zhǎng)*/
/*關(guān)聯(lián)子查詢方法*/ SELECT unix_host,os_username,
  ROUND(sysdate-create_date) days
FROM service o
WHERE ROUND(sysdate-create_date) >
(SELECT ROUND(AVG(sysdate-create_date))
  FROM service i
WHERE i.unix_host = o.unix_host);
/*多表查詢方法*/ SELECT s.unix_host,s.os_username,
ROUND(sysdate-create_date) day,d.days
FROM service s
JOIN (SELECT unix_host,ROUND(AVG(sysdate-create_date)) days
  FROM service
  GROUP BY unix_host) d
ON s.unix_host = d.unix_host
AND ROUND(sysdate-s.create_date) > d.days;
3、外連接
(1)left join 驅(qū)動(dòng)表是左邊的表，匹配表是右邊的表
(2)right join 驅(qū)動(dòng)表是右邊的表，匹配表是左邊的表
(3)full join
執(zhí)行順序 : 先根據(jù)on和and條件對(duì)要連接的表進(jìn)行過濾，將過濾后的結(jié)果集進(jìn)行外連接操作(join on)，再對(duì)外連接的結(jié)果集用where子句進(jìn)行過濾，最后用select語(yǔ)句生成最終結(jié)果集。
/*列出客戶姓名以及他的推薦人*/
select t2.real_name cunstomer,nvl(t1.real_name,'No recommender') recommender
from account t1 right join account t2
on t1.id = t2.recommender_id;
4、內(nèi)連接和外連接的區(qū)別
簡(jiǎn)單來說，內(nèi)連接的結(jié)果集只顯示兩張關(guān)聯(lián)表中關(guān)聯(lián)值的交集記錄，驅(qū)動(dòng)表和匹配表交 換對(duì)結(jié)果集影響不大；外連接的結(jié)果集與驅(qū)動(dòng)表和匹配表的設(shè)置有密切關(guān)系，驅(qū)動(dòng)表會(huì)顯示其所有的記錄，沒有跟匹配表匹配的也會(huì)顯示出來。
{表連接，子查詢 共同解決問題}
1.匹配問題 (結(jié)果集出自一張表)in exist、表連接(inner join)
匹配問題 (結(jié)果集出自多張表) 表連接(inner join)
2.不匹配問題 (結(jié)果集出自一張表) not in  not exist 表連接(outer join + where ...is null)
3.匹配+不匹配 表連接(outer join)


八、集合
數(shù)據(jù)庫(kù)中的集合的概念與數(shù)學(xué)上集合的概念基本一致，同樣有union/union all(并運(yùn)算)， intersert(交運(yùn)算)，minus(減運(yùn)算)。
1、union/union all 
union all 實(shí)現(xiàn)的是集合(結(jié)果集)的并集，簡(jiǎn)單做并集，不去重
select name,base_duration,unit_cost+0.05 new_unit_cost from cost
where base_duration = 20
union all
select name,base_duration,unit_cost+0.03  from cost
where base_duration = 40
union all
select name,base_duration,unit_cost from cost 
where base_duration not in(20,40)
or base_duration is null
/*這里要注意，要加上空值的保留，否則會(huì)忽略了計(jì)時(shí)和包月兩種套餐*/;
union 實(shí)現(xiàn)的是結(jié)果的并集，得到的結(jié)果集會(huì)去掉重復(fù)的記錄
2、intersect 實(shí)現(xiàn)的是對(duì)結(jié)果集取交集的運(yùn)算
/*sun280和sun-server上的遠(yuǎn)程登錄業(yè)務(wù)使用了哪些相同的資費(fèi)標(biāo)準(zhǔn)*/
  select name,id from cost        
  where id in 
( /*加上select from cost where id in ()語(yǔ)句用以獲取相應(yīng)套餐的名字*/       
      select cost_id from service s join host h
      on s.unix_host = h.id
      and h.name = 'sun280'/*利用on的過濾條件把相應(yīng)的名字篩選出來*/
      intersect
      select cost_id from service s join host h
      on s.unix_host = h.id
      and h.name = 'sun-server');
3、minus 實(shí)現(xiàn)的是集合A減去A和B的交集，結(jié)果集是A中與B不一樣的記錄
select name,id,location from host 
  minus
  select h.name,s.unix_host,h.location from service s join host h
  on s.unix_host = h.id;


九、排名分頁(yè)
rownum 是一個(gè)偽列，對(duì)查詢返回的行編號(hào)即行好，由1開始一次遞增
=>oracle的rownum數(shù)值是在獲取每行之后才賦予的，rownum是不能作為表的一列 存在， 無法通過rownum = 2得到第二行的數(shù)據(jù)
要利用小于等于偽列某個(gè)值之后再利用where確定范圍
/*最晚開通netCTOSS系統(tǒng)的第四到第六名客戶*/
  select rn,real_name,create_date
from(
    select rownum rn,real_name,create_date
    from(
          select real_name,create_date
          from account 
        order by create_date desc)
    where rownum <=6)
  where rn between 4 and 6;
  /*經(jīng)典例題，這里的做法是先通過對(duì)account里的記錄按照開通時(shí)間的早晚降序排列，
    再引入rownum進(jìn)行編號(hào)(如果在排序前引入，則會(huì)依照讀取account記錄的順序并添上rownum，再做降序，此時(shí)的結(jié)果并不能達(dá)到預(yù)想的效果)，并通過where rownum <= a抽出前a個(gè)記錄后在嵌套多一個(gè)select語(yǔ)句把處在靠后位置的第四到第六的記錄顯示*/


十、約束
1、約束的類型
a、根據(jù)約束的性質(zhì)分類
主鍵約束(primary key,pk) 特性是非空且唯一
  唯一約束(unique key,uk) 特性是唯一
  檢查約束(check key,ck) 特性是填入的記錄的范圍
  非空約束(not null,nn) 特性是非空
  外鍵約束(foreign key,fk) 特性是表與表之間的聯(lián)系列
b、根據(jù)約束的范圍分類
列級(jí)約束: 約束直接在列中聲明，只約束一列
表級(jí)約束: 約束在列以外表中聲明，可以只約束一列，也可以多列同時(shí)約束
2、建立數(shù)據(jù)庫(kù)表格時(shí)約束的寫法
create table testmt(
c1 number(3) constraint testmt_c1_pk primary key,/*主鍵約束，列級(jí)約束*/
c2 varchar2(10) constraint testmt_c2_uk unique,/*唯一性約束，列級(jí)約束*/
c3 char not null,/*非空約束，列級(jí)約束*/
c4 number(1) constraint testmt_c4_ck check(c4 in(0,1,2,3)),/*檢查約束，列級(jí)約束*/
c5 number(4) constraint testmt_c5_fk references tarena.account(id),
/*外鍵約束，列級(jí)約束*/
constraint testmt_c2_c3_uk unique(c2,c3)/*唯一性約束，表級(jí)約束*/ );
3、外鍵約束
a、表格的一對(duì)多關(guān)系的實(shí)現(xiàn)
表和表之間的關(guān)系: 外鍵約束 1:n
      child table(fk) -> parent table(pk/uk)
      account 1[id pk]
      service m[id(服務(wù))，account_id(客戶)
                account_id fk-->account(id)]
create table testchild (/*列級(jí)約束 外鍵約束寫法*/
c1 number(2) constraint testchild_c1_pk primary key,
c2 number(3) constraint testchild_c2_fk references wzqparent(c1));
  
create table testchild (/*表級(jí)約束 外鍵約束寫法*/
c1 number(2) constraint testchild_c1_pk primary key,
c2 number(3) ,
constraint testchild_c2_fk foreign key(c2) references testparent(c1));
b、表格的多對(duì)多關(guān)系的實(shí)現(xiàn)
表格之間的多對(duì)多關(guān)系
m : n  通過中間表((t1_pk列名)-->t1.pk列，(t2_pk列名)-->t2.pk列)
表達(dá)兩張表(t1,t2)的關(guān)系
c、表格的一對(duì)一關(guān)系的實(shí)現(xiàn)
表格之間的一對(duì)一關(guān)系
把一對(duì)一信息的兩列信息各定義成一張表，每張表的id列定義成pk列，其中一張 表的pk列同時(shí)定義成fk列，實(shí)現(xiàn)一一對(duì)應(yīng)關(guān)系
d、合表問題
=>合表問題的三個(gè)范式
第一范式 有pk，每一列不可再分。
  第二范式 每個(gè)非主屬性必須完全依賴pk列(多對(duì)多關(guān)系的合表)
  第三范式 每個(gè)非主屬性不能依賴于另一個(gè)非主屬性(非主屬性之間不能有依賴關(guān)系)
=>表格的一對(duì)多關(guān)系進(jìn)行合表--數(shù)據(jù)冗余
一對(duì)多的兩張表要合表會(huì)導(dǎo)致數(shù)據(jù)冗余，違反了第三范式
    占用空間，insert每一條服務(wù)信息，同時(shí)寫account信息。
    同一用戶信息出現(xiàn)多條記錄，很可能數(shù)據(jù)不一致。
=>表格的多對(duì)多關(guān)系進(jìn)行合表—違反第二范式
e、外鍵約束定義時(shí)的兩種形式
=>on delete cascade--級(jí)聯(lián)刪除，刪除父表的記錄前，先刪除子表里的相關(guān)記錄
create table testchild1 (
  c1 number(2) constraint testchild1_c1_pk primary key,
  c2 number(3) constraint testchild1_c2_fk references testparent(c1)
                on delete cascade);
/*在級(jí)聯(lián)刪除的情況下，刪除父表的值，子表的同一值下的行也會(huì)被自動(dòng)刪除*/

=>on delete set null-- 刪除父表的記錄前，先將子表中外鍵列的相關(guān)值置空
create table testchild1 (
  c1 number(2) constraint testchild1_c1_pk primary key,
  c2 number(3) constraint testchild1_c2_fk references testparent(c1)
                on delete set null 
/*update wzqchild2 set c2 = null where c1 = 1;*/
);
4、檢查約束
檢查約束也可以在列級(jí)和表級(jí)之間實(shí)現(xiàn)。
/*列級(jí)*/create table testmt(
c1 number(3) constraint testmt_c1_pk primary key,
c2 number(3) constraint testmt_c2_ck check(c2 > 100)
);
/*表級(jí)*/create table testmt(
c1 number(3) constraint testmt_c1_pk primary key,
c2 number(2),
c3 number(2),
constraint testmt_c2_c3_ck check((c2+c3) > 100)
/*check的表達(dá)式可以是in，可以是= ，也可以是算術(shù)表達(dá)式*/
);
5、約束的暫停和恢復(fù)
在為表格增加記錄的時(shí)候，有些記錄在表格所有記錄完全寫入之前受制于表格創(chuàng)建時(shí)所 設(shè)下的約束條件，導(dǎo)致無法向表格中填入記錄。在這種情況下，除了在創(chuàng)建表格時(shí)候不 添加相應(yīng)的約束，等到記錄全部填入后再寫，或者可以選擇暫時(shí)暫停約束的條件，待記 錄錄入完成后恢復(fù)。具體的操作如下: 
alter table testmt disable constraint  testmt_c5_fk;
/*關(guān)鍵字disable可以暫時(shí)暫停命名為testmt_c5_fk的constraint(約束) */
alter table testmt enable constraint  testmt_c5_fk;
/*關(guān)鍵字enable可以恢復(fù)命名為testmt_c5_fk的constraint(約束) */
注意: 約束的存在不僅僅是為了保持表格于表格之間的聯(lián)系(foreign key)，同時(shí)還有其他例如唯一性(unique key)和主鍵(primary key)等用于限定表格中的記錄以符合業(yè)務(wù)邏輯的。因此，在暫停約束的時(shí)候不能夠隨便暫停，一旦在暫停過程中輸入違反約束條件的記錄就不能正?；謴?fù)相應(yīng)的約束，導(dǎo)致出現(xiàn)工作的延誤和不合理。所以，要根據(jù)記錄的輸入和現(xiàn)實(shí)中的業(yè)務(wù)邏輯考慮使用約束的暫停。
6、約束的查詢
select c.constraint_name,c.constraint_type,cc.column_name，position
from user_constraints c join user_cons_columns cc
on c.table_name = cc.table_name
and c.table_name = “ACCOUNT”;
constraint_name:約束名
table_name:表名
column_name:被約束的列
position:單列約束值為1，聯(lián)合約束時(shí)表示該列在約束定義中的位置
7、約束的操作
alter table tabname drop constraint_name;/*根據(jù)約束名刪除約束*/
alter table tabname drop primary key;/*刪除主鍵約束*/
alter table tabname add 表級(jí)約束語(yǔ)法;/*添加約束*/


十一、update和delete語(yǔ)法
1、update的語(yǔ)法
--用于更新表中已經(jīng)存在的記錄，即修改記錄的某列/某些列的值
update tabname set colname1 = value1[,colname2 = value2]
where condition;
2、delete的語(yǔ)法
--刪除已經(jīng)存在的記錄
delete from tabname where condition;
如果要?jiǎng)h除表格里面的所有記錄，可以簡(jiǎn)寫成delete tabname;
{delete和truncate的區(qū)別}
delete from tabname 時(shí)間長(zhǎng)(是否寫rollback/undo segment)
    truncate table tabname 把表所有數(shù)據(jù)刪除(刪除的速度比delete from tabname塊)    
    delete 不釋放表占用的空間
    truncate 釋放表占用的空間
    全部刪除大表中的數(shù)據(jù)用truncate table


十二、事務(wù)
事物是由一組DML語(yǔ)句和commit/rollback組成，是改變數(shù)據(jù)庫(kù)數(shù)據(jù)的最小邏輯單元。如果是commit，表示數(shù)據(jù)入庫(kù)；如果是rollback，表示取消所有的DML操作。
1、事物的特性
a、原子性: 一個(gè)事務(wù)或者完全發(fā)生，或者完全不發(fā)生
b、一致性: 事務(wù)把數(shù)據(jù)庫(kù)從一個(gè)一致狀態(tài)轉(zhuǎn)變到另一個(gè)一致狀態(tài)
c、隔離性: 隔離級(jí)別(read committed只可讀取已經(jīng)修改的和正在修改的信息)，事務(wù)提交之前，其他事務(wù)覺察不到事務(wù)的影響
d、持久性: 一旦事務(wù)提交，它是永久的
2、數(shù)據(jù)庫(kù)開發(fā)的關(guān)鍵挑戰(zhàn)
=>使并行的訪問量達(dá)到最大化
=>保證每一個(gè)用戶(會(huì)話)可以以一致的方式讀取并修改數(shù)據(jù)
鎖機(jī)制:用來管理對(duì)一個(gè)共享資源的并行訪問


十三、視圖
1、帶子查詢的create table
=>根據(jù)子查詢語(yǔ)句創(chuàng)建表并插入數(shù)據(jù)
=>表結(jié)構(gòu)由子查詢的select語(yǔ)句決定，create table指定列的數(shù)量要跟select語(yǔ)句指定的列的數(shù)量一致。
/*create table 定義列只能定義命名，缺省值，完整性約束。*/
/*20機(jī)器上的業(yè)務(wù)信息*/
create table service_a
as 
select * from tarena.service
where unix_host = '192.168.0.20';
2、帶子查詢的insert
=>根據(jù)子查詢語(yǔ)句向表中插入數(shù)據(jù)
=>insert指定的裂地?cái)?shù)量要跟select語(yǔ)句指定的列的數(shù)量一致。
=>一次可以插入多條記錄，不能用values子句
  /*account_90表中含有所有的90后用戶*/
  create table account_w90
  as
  select * from tarena.account
  where 1 = 2;/*用于復(fù)制表格的列，但不會(huì)復(fù)制表格里的數(shù)據(jù)(where 1 = 2;)*/
  insert into account_w90
  select * from tarena.account
  where to_char(tarena.account.birthdate,'yyyy')
        between '1990' and '1999';
3、view視圖的概念
=>類似于快捷方式，利用select語(yǔ)句實(shí)現(xiàn)，只在系統(tǒng)表中存儲(chǔ)對(duì)視圖的定義
=>視圖在數(shù)據(jù)庫(kù)中不存儲(chǔ)數(shù)據(jù)值，即不占空間
select test from user_views where view_name = 'test_v11';
select 'c1','c2'from test where c1 = 11;
      
      系統(tǒng)表 user_tables user_views user_objects(系統(tǒng)insert)
      用戶表 account service cost(用戶DDL)
      
      select object_name,object_type,status
      from user_objects
      where object_name = 'test_v11';
        [如果在where語(yǔ)句后面加上with check option，就可以只允許符合要求的數(shù)據(jù)
         例如該題目，加上with check option，就只有當(dāng)c1 = 11情況下才能插入]
        [如果在select語(yǔ)句后加上with read only，表明該視圖是只讀屬性]
      drop一張表系統(tǒng)自動(dòng)將所有依賴該表的數(shù)據(jù)庫(kù)對(duì)象
(view procedure function)的狀態(tài)變?yōu)閕nvalid(無效的)
      當(dāng)一個(gè)對(duì)象是無效時(shí)，系統(tǒng)會(huì)執(zhí)行
                   alter view test_v11 compile; 重新編譯該視圖
4、視圖的應(yīng)用場(chǎng)景
a、簡(jiǎn)化操作，屏蔽了復(fù)雜的sql語(yǔ)句，直接對(duì)視圖操作
b、控制權(quán)限，只允許查詢一張表中的部分?jǐn)?shù)據(jù)。解決辦法：對(duì)其創(chuàng)建視圖，授予用戶讀視圖的權(quán)限，而非讀表的權(quán)限。
c、通過視圖將多張表union all成一張邏輯表，作為單獨(dú)一個(gè)數(shù)據(jù)庫(kù)對(duì)象，實(shí)現(xiàn)表的超集
5、視圖的維護(hù)
視圖是一個(gè)依賴表的數(shù)據(jù)庫(kù)對(duì)象，查詢視圖最終都要通過查詢?cè)幢韺?shí)現(xiàn)。如果源表的結(jié)構(gòu)發(fā)生變化，對(duì)視圖的操作就有可能出問題。查看視圖的狀態(tài)是幫助我們發(fā)現(xiàn)視圖是否可用的方法。


十四、索引
創(chuàng)建方式: create index index_name on table(column);
1、掃描表的方式
a、全表掃描FTS
將掃描高水位線一下的所有數(shù)據(jù)塊
/*高水位線:(high water mark)曾經(jīng)插入數(shù)據(jù)的最遠(yuǎn)塊*/
b、通過rowid來掃描數(shù)據(jù)
/*rowid是一個(gè)偽列，rowid包含如下信息：
data_object_id 該記錄是屬于哪張表的(哪個(gè)數(shù)據(jù)庫(kù)對(duì)象)
    file_id        該記錄在哪個(gè)數(shù)據(jù)文件里
    block_id       該記錄在數(shù)據(jù)文件的第幾個(gè)數(shù)據(jù)塊里
    row_id         該記錄在數(shù)據(jù)塊里是第幾條記錄
*/
在oracle看來，沒有重復(fù)記錄，每條記錄都有rowid。
delete from test o
where rowid<
          (select max(rowid) from test i
            where o.c1=i.c1
            and o.c2=i.c2) 刪除表中重復(fù)數(shù)據(jù)的方法
對(duì)于所有的重復(fù)記錄，留rowid最大的那條記錄。
delete from test o
where rowid<
          (select min(rowid) from test i
           where o.c1=i.c1
             and o.c2=i.c2) 刪除表中重復(fù)數(shù)據(jù)的方法
對(duì)于所有的重復(fù)記錄，留rowid最小的那條記錄。
2、索引的結(jié)構(gòu)
B*tree索引由 根塊（root block）、屬于date_block
            分支塊（branch block）、
          葉子塊（leaf block）組成
*根塊下面是分支塊，擁于導(dǎo)航結(jié)構(gòu)，包含了索引列范圍和另一非根塊(可以是分支塊或葉子塊)的地址
*最底層為葉子塊,包含索引項(xiàng)（index entry），索引項(xiàng)有key值（被索引列的值）和該列所在行的rowid組成
*葉子塊實(shí)際上是雙向鏈表。一旦找到葉子塊的“開始”點(diǎn)（一旦找到第一個(gè)值），對(duì)值進(jìn)行順序掃描（索引范圍掃描）是很容易的。不必要做結(jié)構(gòu)導(dǎo)航，只要通過葉子塊轉(zhuǎn)發(fā)就行
id列沒建索引，用FTS
id列建索引，找索引，從root塊通過幾步找到葉子塊，找index entry，獲得rowid，select變成where rowid = ''
    
select real_name,rowid
    from tarena.account
    索引的順序就是按照order by的順序排列，不然就按照rowid的順序排列
3、索引的類型
唯一性索引（unique）   等價(jià)于唯一性約束
非唯一性索引  用于提高查詢效率
單列索引  索引建在一列上
聯(lián)合索引  索引建在多列上
4、哪些列適合建索引
經(jīng)常出現(xiàn)在WHERE子句的列
經(jīng)常用于表連接的列
該列是高基數(shù)數(shù)據(jù)列（高基數(shù)數(shù)據(jù)列是指有很多不同的值）
該列包含許多null值
表很大，查詢的結(jié)果集小
主鍵（PK）列、唯一鍵（UK）列
外鍵（FK）列
經(jīng)常需要排序（ORDER BY）和分組（GROUP BY）的列,DISTINCT也適合
=>索引不是萬能的,沒有索引是萬萬不能的
5、索引用不了的寫法
函數(shù)導(dǎo)致索引用不了－－WHERE UPPER（COLNAME）＝‘CARMEN’
  表達(dá)式導(dǎo)致索引用不了－－WHERE COLNAME＊12＝12000
  部分隱式數(shù)據(jù)類型導(dǎo)致索引用不了－－WHERE COLNAME1＝2（C1為varchar2類型）
  LIKE和SUBSTR－－WHERE COLNAME LIKE ‘CA％’ 
               －－WHERE SUBSTR（COLNAME，1，2）＝‘CA’
  查詢所有的null值－－WHERE COLNAME IS NULL
  否定形式－－NOT IN 、 < >


十五、序列號(hào)
1、序列號(hào)的概念
=>為了解決主鍵值和唯一鍵值的唯一性
按照預(yù)定義的模式自動(dòng)生成整數(shù)的一種機(jī)制，保證數(shù)字的自動(dòng)增長(zhǎng)
2、序列號(hào)的創(chuàng)建
create sequence sequence1
      start with 1/*起始值*/
      increment by 1/*步長(zhǎng)，下一步所增加的數(shù)值*/
      maxvalue 999/*最大值，序列號(hào)的最大值*/
cycle /*是否循環(huán)，默認(rèn)值為nocycle*/
cache 20/*循環(huán)值，表示需要是從1-20的循環(huán)*/;
3、實(shí)際案例的應(yīng)用
create table test(c1 number constraint test_c1_pk primary key);
create sequence s_test_c1 start with 1302001;


insert into test values (s_test_c1.nextval);
insert into test values (s_test_c1.nextval); commit;
insert into test values (s_test_c1.nextval); rollback;
insert into test values (s_test_c1.nextval);
select * from test;
        C1
----------
    1302001