Oracle
基礎(chǔ)部分
創(chuàng)建表空間、創(chuàng)建用戶、給用戶授權(quán)
Oracle體系結(jié)構(gòu):??
數(shù)據(jù)庫(kù)-->數(shù)據(jù)庫(kù)實(shí)例-->表空間-->數(shù)據(jù)文件, ? ?創(chuàng)建用戶并為用戶授予訪問(wèn)表空間的權(quán)限.
? ? --創(chuàng)建表空間, 邏輯單位, 通常我們新建一個(gè)項(xiàng)目, 就會(huì)去創(chuàng)建表空間, 在表空間中創(chuàng)建用戶來(lái)創(chuàng)建表.
? ? --需要管理員權(quán)限, 普通數(shù)據(jù)庫(kù)用戶可能不行.
? ? 語(yǔ)法:
????????? ? ? create tablespace 表空間名稱
????????? ? ? datafile '文件的實(shí)際路徑'
????????? ? ? size 大小
????????? ? ? autoextend on 自動(dòng)擴(kuò)展
????????? ? ? next 每次擴(kuò)展的大小
? ? 例如: ?
????????? ? ? create tablespace file_tablespace
????????? ? ? datafile 'E:\Oracle_DB\product\12.2.0\dbhome_1\oradata\sample\myFile.dbf'
????????? ? ? size 500M
????????? ? ? autoextend on
????????? ? ? next 100M
? ? --刪除表空間, 刪除后數(shù)據(jù)實(shí)體文件依然存在, 只是將其從Oracle進(jìn)程中斷開(kāi)引用, 徹底刪除需要親自去刪除文件.
? ? ? ????????drop tablespace 表空間名稱;
? ?--如果需要同時(shí)刪除文件可以使用:
? ? ? ? ? ????drop tablespace xxxx including contents and datafiles
? ?--創(chuàng)建用戶, 剛創(chuàng)建的用戶無(wú)任何權(quán)限, 甚至不能登錄.
? ?--格式: ?
????????? ? ? create user ?用戶名
????????? ? ? identified by 密碼 ?
????????? ? ? default tablespace 表空間名稱;
????????? ? ? CREATE USER USER_ONE
????????? ? ? IDENTIFIED BY 12345
????????? ? ? ACCOUNT UNLOCK
????????? ? ? DEFAULT TABLESPACE FILE_TABLESPACE;
? ? --為用戶授權(quán)
? ? ??????????grant connect,resource to USER_ONE; ?
????????????? 表示把 connect,resource權(quán)限授予news用戶
? ? ??????????grant dba to USER_ONE; ?
????????????? 表示把 dba權(quán)限授予給news用戶
? ? ? --如果需要指定特定的幾張表給特定的用戶:
? ? ? ????????grant select,update,delete,insert on 表 to 用戶;
數(shù)據(jù)庫(kù)角色權(quán)限: ?
SQL的分類
1.DDL:數(shù)據(jù)定義語(yǔ)言??
? ? create alter drop truncate ?
2.DML:數(shù)據(jù)操縱語(yǔ)言??
? ? insert update delete
3.DCL:數(shù)據(jù)控制語(yǔ)言??
? ? 安全 授權(quán) grant revoke
4.DQL:數(shù)據(jù)查詢語(yǔ)言
? ? select
列的類型
? ? varchar: 在Oracle中, 目前是支持的, ?但不保證以后支持. ?
? ? varchar2(長(zhǎng)度): 可變字符長(zhǎng)度 varchar2(10) ? hello ?占用5個(gè)字符 ?
? ? char(長(zhǎng)度):?不可變字符長(zhǎng)度 char(10) ? hello ?占用10個(gè)字符, 用空格填充其他空位 ?
? ? number(總長(zhǎng)度,小數(shù)長(zhǎng)度): 數(shù)字類型 例如: number(12,3)
? ? --注意: number小數(shù)位數(shù)不能大于等于總長(zhǎng)度 ?
? ? date: 年月日時(shí)分秒 ?select current_date from dual; ?
? ? timestamp: 時(shí)間戳, 比date類型更加精確 ? select current_timestamp from dual;
基本操作
增:??
? ? insert into 表名(表字段) values(...);
????表名后不加括號(hào)表示全數(shù)據(jù)添加.
????MySQL如果使用該方式一次添加多條數(shù)據(jù)時(shí),使用逗號(hào)分隔, 但oracle不行.
????insert into students values(3,'艾達(dá)','女','嗯嗯'),(4,'萌萌','女','啊啊'); -MySQL可以這樣寫.
刪:
? ? delete from 表名 where 條件
? ? --delete 和 truncate 區(qū)別:
? ? --delete 屬于DML, 逐條刪除, 支持事務(wù)操作;
? ? --truncate 屬于DDL, 先刪除表再創(chuàng)建表, 不支持事務(wù)操作;
改:??
? ? update 表名 set 表字段=... where 條件
查:??
? ? select * from 表名;
? ? --查看數(shù)據(jù)庫(kù)中所有的表
? ? select * from all_tables
使用子查詢的方式創(chuàng)建表
create table 表名 as 查詢語(yǔ)句??
? ? 1. 該方式只會(huì)復(fù)制表結(jié)構(gòu)和表數(shù)據(jù), 不會(huì)復(fù)制表中列的約束
? ? 2. 如果查詢語(yǔ)句有結(jié)果會(huì)復(fù)制表結(jié)構(gòu)和數(shù)據(jù), 否則只會(huì)復(fù)制表結(jié)構(gòu)
使用子查詢插入數(shù)據(jù)
? ? insert into 表名 查詢語(yǔ)句
? ? --例如:
? ? insert into emp2 select * from emp1;
修改表
添加列??
? ? alter talbe 表名 add 列名 列類型;
? ? alter talbe 表名 add (列名1 列類型,列名2 列類型);
修改列類型??
? ? alter table 表名 modify 列名 列類型;
修改列名
? ? alter table 表名 rename column 原列名 to 新列名;
刪除列??
? ? alter table 表名 drop column 列名
修改表名
? ? rename 舊名 to 新名
刪除表
? ? drop table 表;
dual
虛表, 偽表用于補(bǔ)齊語(yǔ)法結(jié)構(gòu)??
? ? select 1+1;-- 在oracle中會(huì)報(bào)錯(cuò), 但在mysql中正常輸出2.??
? ? select 1+1 from dual; -- oracle
別名
1. 給表起別名 ?
（1）表的別名要在from子句中指定，并且別名位于查詢中其余列之前 ?
（2）使用表的別名可以減少查詢中輸入的文本的數(shù)量，并且可能還會(huì)減少在輸入過(guò)程中的錯(cuò)誤。 ?
2. 給字段起別名 ?
（1）使用AS 在該字段后面空一格，使用AS 空格 別名，這種方式來(lái)給字段起別名
（2）直接在字段的后面空格，然后再起別名也是可以的
3. 別名中不能存在特殊字符或者關(guān)鍵字, 如果有需要使用引號(hào).??
注意: 雙引號(hào)主要是別名, 單引號(hào)是使用的值,是字符串.
四則運(yùn)算
null值, 代表不確定 不可預(yù)知, 不能進(jìn)行四則運(yùn)算; ?
使用函數(shù)nvl(xxxx,0)處理空值, 在mysql中ifnull函數(shù),與此類似.??
條件查詢
where后面的寫法. ?
關(guān)系運(yùn)算符:??
? ? >? ? >=? ? =? ? <? ? <=? ?? !=? ?? <>
邏輯運(yùn)算符:??
? ? and or not ?
其他運(yùn)算符:??
? ? like 模糊查詢 ?
? ? in 在某個(gè)集合內(nèi) ? ?
? ? between...and 在某個(gè)區(qū)間內(nèi) ?
? ? is null 判斷為空
? ? is not null 判斷不為空??
排序
select * from emp order by comm desc nulls last
? ? nulls first -- null排序完成后放在前面
? ? nulls last -- null排序完成后放在后面 ?
? ? order by 字段1 asc,字段2 desc --實(shí)現(xiàn)復(fù)合排序
表的五大約束
用來(lái)約束表中數(shù)據(jù)的規(guī)則 (4個(gè)單表, 1個(gè)表外).
單表約束(列級(jí)約束)
? 1. not null 非空 ?
? 如果在列上定義了not null 那么當(dāng)插入數(shù)據(jù)時(shí) ，必須為列提供數(shù)據(jù). ?
? 2. unique 唯一 ?
? 當(dāng)定義了唯一約束后，該列值不能重復(fù)， 但是可以為null (可以出現(xiàn)多個(gè)null) ?
? 3. primary 主鍵 ?
? 用于唯一的標(biāo)識(shí)表中一行的數(shù)據(jù)，當(dāng)定義主鍵約束后，該列不但不可以重復(fù)而且不能為? ? ? null。 ?
? 主鍵包含了not null 和unique. ?
? 一張表最多可以有一個(gè)主鍵，但是可以有多個(gè)unique 約束 ?
? 4. check 檢查 判讀 ?
? 用于強(qiáng)制行數(shù)據(jù)必須滿足的條件，假定在sal 列上定義了check 約束，并要求sal列值在? ? ? 1000——2000 之間，如果不在1000——2000之間，就會(huì)提示報(bào)錯(cuò)。
? (檢查約束在 MySQL?中寫將會(huì)被忽略)??
**后續(xù)追加約束:**??
? ? --未指定名稱oracle將會(huì)自動(dòng)命名
? ? alter table 表名
? ? add constraint 約束名 約束類型(列名或檢查條件);
????添加非空:
????alter table table_name
? ? modify column_name null;
**后續(xù)移除約束:** ?
? ? alter table 表名
? ? drop constraint 約束名;
? ? 移除非空約束(一般不需要?jiǎng)h除)：
? ? alter table table_name
? ? modify column_name null;
表級(jí)約束(外鍵約束)
????foreign key 外鍵 ?
用于定義表和表之間的關(guān)系，外鍵約束要定義在從表上，主表則必須具有主鍵約束或是unique 約束，當(dāng)定義外鍵約束后，要求外鍵列數(shù)據(jù)必須在主表的主鍵存在或是為null.??
**添加外鍵約束:**??
? ? alter table 從表 add foreign key(從表列) references 主表(主表列)
? ? --如果主從表已經(jīng)存在違背外鍵的數(shù)據(jù), 鍵無(wú)法創(chuàng)建
**強(qiáng)制刪除外鍵:**
? ? drop table 表名 cascade constraint;??
????如果是普通刪除使用上文的約束刪除即可.
**級(jí)聯(lián)刪除:** ?
首先去從表中找有沒(méi)有關(guān)聯(lián)數(shù)據(jù), 找到了先刪除從表的, 后刪除主表中的關(guān)聯(lián)數(shù)據(jù)??
? ? --允許外鍵使用級(jí)聯(lián)刪除
? ? alter table 主表 add foreign key(主表列) references 從表(從表列) delete cascade;
**級(jí)聯(lián)更新:** ?
[Oracle使用設(shè)置延遲約束并結(jié)合觸發(fā)器實(shí)現(xiàn)級(jí)聯(lián)更新](https://www.2cto.com/database/201507/417496.html)
函數(shù)
[oracle函數(shù)大全](https://blog.csdn.net/ruiguang21/article/details/80049578)??
單行函數(shù)
對(duì)某一行中的某一個(gè)值進(jìn)行處理.
? ???數(shù)值函數(shù)??
? ---------------------------
? ? ? select ceil(45.926) from dual; --46
? ? ? select ceil(45.111) from dual; --46
? ? ? select floor(45.926) from dual; --45
? ? ? select round(45.926,3) from dual; --保留兩位小數(shù), 45.93
? ? ? select round(45.926,-1) from dual; --50
? ? ? ------------截?cái)?--------------
? ? ? select trunc(45.926,2) from dual; --直接截?cái)啾Ａ?位小數(shù), 不會(huì)四舍五入, 45.92
? ? ? select trunc(45.926,-1) from dual; --40
? ? ? select trunc(45.926,-2) from dual; --0
? ? ? ------------求余---------------
? ? ? select mod(9,3) from dual; --0
? ? 字符函數(shù)
? ? ? substr(str1,起始索引,長(zhǎng)度)
? ? ? select substr('abcdefgh',3,2) from dual; -- cd
? ? ? select substr('abcdefg',0,3) from dual; -- abc
? ? ? select substr('abcdefg',1,3) from dual; -- 結(jié)果同上， 是的你沒(méi)看錯(cuò)
? ? ? -- 注意：起始索引不管是從0還是1, 都是從第一個(gè)字符開(kāi)始截取. ?
? ? ? ----------------------------------
? ? ? --獲取字符串的長(zhǎng)度
? ? ? select length('abcdefghijklmnopqrstuvwxyz') from dual; -- 26
? ? ? ----------------------------------
? ? ? --去除字符串左右兩邊的空格
? ? ? select trim(' ? ?hello ? ?') from dual; --并不是什么空格都能夠去除，全角的空格不能去除.
? ? ? select replace('hello','l','a') ?from dual;-- heaao
? ? 日期函數(shù)
? ? ? select sysdate from dual; -- 查詢系統(tǒng)時(shí)間
? ? ? --查詢?nèi)齻€(gè)月后的今天
? ? ? select add_months(sysdate,3) from dual;
? ? ? --查詢?nèi)旌蟮臅r(shí)間
? ? ? select sysdate+3 from dual;
? ? ? -- 需要注意的是sysdate查詢的是服務(wù)器時(shí)間, 就是數(shù)據(jù)庫(kù)所在的機(jī)器的時(shí)間.
? ? ? --查詢員工入職的天數(shù)
? ? ? select sysdate-hiredate from emp;
? ? ? select ceil(sysdate-horedate) from emp; --向上取整的天數(shù)
? ? ? -- 兩個(gè)時(shí)間之間的的月數(shù)
? ? ? select months_between(sysdate,sysdate-100) from dual；
? ? 轉(zhuǎn)換函數(shù)
? ? ? --字符轉(zhuǎn)數(shù)值 to_number(str) 雞肋
? ? ? select 100+'10' from dual; --110 默認(rèn)已經(jīng)幫我們轉(zhuǎn)化了
? ? ? select 100+to_number('10') from dual;
? ? ? --數(shù)值轉(zhuǎn)字符
? ? ? select to_char(1800,'$9,999.99')from dual; --$1,800.00
? ? ? select to_char(1800,'L9,999,99')from dual; --￥1,800.00
? ? ? --日期轉(zhuǎn)字符
? ? ? select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
? ? ? select to_char(sysdate,'d') from dual;--代表一周中的第幾天
? ? ? select to_char(sysdate,'dd')from dual;--代表一個(gè)月中的第幾天
? ? ? select to_char(sysdate,'ddd')from dual;--代表一年中的第幾天
? ? ? select to_char(sysdate,'day') from dual;--星期幾
? ? ? --字符轉(zhuǎn)日期
? ? ? select to_date('20190819','yyyy:mm:dd')from dual;--2018/08/19 無(wú)論第二個(gè)參數(shù)的符號(hào)是什么都轉(zhuǎn)化為通用的日期格式. ?
? 通用函數(shù)
? ? nvl(參數(shù)1,參數(shù)2) ? ? 如果參數(shù)1為null, 就返回參數(shù)2
? ? nvl2(參數(shù)1,參數(shù)2,參數(shù)3) ? ? 如果參數(shù)1=null, ? 返回參數(shù)3, 否則返回參數(shù)2 ?
? ? nullif(參數(shù)1,參數(shù)2) ? 當(dāng)參數(shù)1=參數(shù)2, 返回null, ?否則返回參數(shù)1 ?
? ? --coalesce: 返回第一個(gè)不為null 的函數(shù)
? ? select coalesce(null,1,null,5) from dual;
聚合函數(shù)(多行函數(shù))
對(duì)某一列中的所有行進(jìn)行處理??
? ? sum()? ?count()? ? max()? ? ?min()? ? ?avg()
1. 直接忽略空值
2. 對(duì)于avg()這樣的函數(shù), 在進(jìn)行平均計(jì)算時(shí), 將直接忽視空值, 最終除數(shù)是非空的那幾列, 可能不是我們想要的結(jié)果, 使用avg(nvl(xxx,0))或者使用sum()/count()這樣的組合可以解決. ?
比較復(fù)雜的聚合函數(shù): listagg()
listagg(列,'分隔符') within group (order by 排序字段) 可以將字段按順序拼接成一行,以指定的分隔符分割. ?
例如:
? select deptno,listagg(ename,',')within group(order by sal)name from emp group by deptno; ?
也可以進(jìn)行分析函數(shù)聯(lián)合,
select deptno,ename,sal,listagg(ename,',')within group(order by sal)over(partition by deptno)name from emp; ?
分析函數(shù)
該分析函數(shù)可以進(jìn)行分組排序，group by分組后會(huì)把同一組的融合，但分析函數(shù)不會(huì)把同一組的融合, 每一行都有一個(gè)統(tǒng)計(jì)值.
語(yǔ)法:
分析函數(shù)帶有一個(gè)開(kāi)窗函數(shù)over()，包含三個(gè)分析子句:分組(partition by), 排序(order by), 窗口(rows/range) ，它們的使用形式如下：
? ? over(partition by xxx order by yyy rows/range between ..........)
? ? --這里的rows/range, 如不需要, 可以省略.
? ? --over() 前面可以接聚合函數(shù)(sum、max、min、count、avg)或者行比較函數(shù)(lead、lag), over開(kāi)窗將通知系統(tǒng)不能將它們當(dāng)作普通的聚合函數(shù)或者行比較處理.??
對(duì)語(yǔ)法的另一種解釋:
? ? --分析函數(shù)由三個(gè)部分組成：分區(qū)子句、排序子句和開(kāi)窗子句
? ? function(arg1,arg2,...argN)
? ? over([partition-by-clause] [order-by-clause] [windowing-clause])
over前的函數(shù)除了聚合函數(shù)可以用之外, 還可使用:??
窗口子句:
? ? unbounded preceding表示以分組數(shù)據(jù)的第一行最為上邊界；
? ? unbounded following表示以分組數(shù)據(jù)的最后一行最為下邊界；
? ? current row表示當(dāng)前數(shù)據(jù)行；
? ? n preceding表示當(dāng)前數(shù)據(jù)行的前n 行；
? ? n following表示當(dāng)前數(shù)據(jù)行的后n 行。
1、窗口子句必須和order by 子句同時(shí)使用，且如果指定了order by 子句未指定窗口子句，則默認(rèn)為RANGE BETWEEN unbounded preceding AND CURRENT ROW.
2、如果分析函數(shù)沒(méi)有指定ORDER BY子句，也就不存在ROWS/RANGE窗口的計(jì)算；
**注：行比較分析函數(shù)lead和lag無(wú)window(窗口)子句。**
關(guān)于rows和range的不同:
使用下面的SQL:
? ? SELECT
? ? ? ? id,
? ? ? ? SUM(ID) OVER(ORDER BY ID) AS default_sum,
? ? ? ? SUM(ID) OVER(ORDER BY ID RANGE BETWEEN unbounded preceding AND CURRENT ROW) AS range_ub_sum,
? ? ? ? SUM(ID) OVER(ORDER BY ID ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS rows_ub_sum,
? ? ? ? SUM(ID) OVER(ORDER BY ID RANGE BETWEEN 1 preceding AND 2 following) AS range_sum,
? ? ? ? SUM(ID) OVER(ORDER BY ID ROWS BETWEEN 1 preceding AND 2 following) AS rows_sum
? ? FROM t
結(jié)果如下:
**1. rows方式**
>rows是物理窗口，即根據(jù)order by 子句排序后，取的前N行及后N行的數(shù)據(jù)計(jì)算（與當(dāng)前行的值無(wú)關(guān)，只與排序后的行號(hào)相關(guān)）
如果是上面的例子, ROWS_SUM取當(dāng)前行的前一行到后倆行共四行的數(shù)據(jù), 進(jìn)行操作.
**2. range方式**
>range是邏輯窗口，是指定當(dāng)前行對(duì)應(yīng)值的范圍取值，列數(shù)不固定，只要行值在范圍內(nèi)，對(duì)應(yīng)列都包含在內(nèi).
如果是上面的例子, RANGE_SUM取的范圍, 與當(dāng)前行的值(此處是id)有關(guān), 這里是指取比當(dāng)前id小1大2的行, 當(dāng)然以實(shí)際命中為準(zhǔn), 比如id=3這一行, 得到的結(jié)果為3,就是因?yàn)樗d在2到5之間的, 就只能取到3導(dǎo)致的.
條件表達(dá)式
MySQL和Oracle都有的功能
? ?case 字段
? ? ? ????when 值1 ? then 值
? ? ? ????when 值2 ? then 值
? ? ? ????else 默認(rèn)值 ?
? ?end
Oracle特有的寫法
? decode(字段, if1, 值1, if2, 值2, else值)??
group by中使用case when
? ? select
? ? ? ? (case id when 9 then 6 else id end),
? ? ? ? count(1) ?
? ? from students
? ? group by (case id when 9 then 6 else id end);
分組表達(dá)式
格式:
? select 分組字段,分組之后的操作 from 表名 group by 分組字段?having 條件過(guò)濾
例如:
? --分組總計(jì)所有部門的平均工資, 選出超過(guò)1000的
? select deptnum,avg(sal) from emp group by deptnum having avg(sal)>1000
? --如果使用別名將出錯(cuò)
? select deptnum,avg(sal) dd from emp group by deptnum having dd>1000
? SQL的編寫順序:
? ? select ...from....where...group by ...having...order by ...
? SQL的執(zhí)行順序
? ? from...where...group by... having...select...order by...
? where和having區(qū)別:
? ? where后面不能接聚合函數(shù), 可以接單行函數(shù)
? ? having是在group by之后執(zhí)行, 可以接聚合函數(shù)
模糊查詢
instr(a,b)本身是一個(gè)統(tǒng)計(jì)b位于a中的哪一個(gè)位置的函數(shù), 但用在where中可以像like一樣進(jìn)行模糊查詢.
select instr(name,'徐') from students a;
左右模糊查詢（INSTR相比LIKE效率高）：??
? INSTR(column_name,value) > 0 或 LIKE '%value%' ?
? INSTR(column_name,value) = 0 或 NO LIKE '%value%'
??如:? select * from students where instr(name,'徐')>0;
轉(zhuǎn)義字符
使用 q'[]'?,中括號(hào)中的字符會(huì)被轉(zhuǎn)義處理
? select q'[']' from dual;
多表查詢
笛卡爾積
兩張表的乘積.??
格式: select * from table1,table2
內(nèi)連接
隱式內(nèi)連接:
? 等值內(nèi)連接: ?例如: ?where table1.id=table2.id ?
? 不等值內(nèi)連接: ?例如: ?where table1.id<>table2.id ?
? 自連接: ?自己表關(guān)聯(lián)自己 select * from table1 t1 ,table1 t2
顯示內(nèi)連接:
? select * from 表1 inner join 表2 on 連接條件
? inner 關(guān)鍵字可以省略
外連接
左外連接: left outer join ? ?
右外連接: right outer join
outer 可以省略
? Oracle中的外連接:
? 例如: ?
? 左外連接, 當(dāng)右表關(guān)聯(lián)條件沒(méi)有與之對(duì)應(yīng)的值使用空值與之對(duì)應(yīng). ?
? select * from emp e1,dept d1 where e1.deptno=d1.deptno(+);
? 使用(+)表示添加null值對(duì)應(yīng).??
子查詢
查詢語(yǔ)句中嵌套查詢語(yǔ)句; 用來(lái)解決復(fù)雜的查詢語(yǔ)句. ?
單行子查詢:?子句只有一個(gè)值,可以使用 >? >=? <? <=? != ?
多行子查詢:?使用in, not in, any, all, exists 需要注意的是子查詢的結(jié)果集中如果存在null, 通常需要將null去除, 另外, 當(dāng)一個(gè)字段設(shè)置為一定長(zhǎng)度, 比如char(100), 即使是null也是占用100的空間.??
? 例如: select * from emp where empno not in (select mgr from emp where mgr is not null)
? 又例如: ?
? select * from file_my where "FileName" ?in ('文件1',null);
? 用in可以顯示,但是少了一條null的數(shù)據(jù), 但是用not in, 就不會(huì)顯示任何一條數(shù)據(jù), 這個(gè)原因如下圖:
exists
使用exists可以在數(shù)據(jù)量較大的時(shí)候提高效率.
? 用法一:??
? ? select * from emp where exists (select * from emp where empno=112);表中沒(méi)有112這個(gè)記錄時(shí),條件由于恒等于false不會(huì)查到任何記錄
? ? select * from emp where exists (select * from emp where empno=11);表中有11這個(gè)記錄時(shí),條件由于恒等于true會(huì)查到所有記錄
? 用法二:
? ? --查詢有員工的部門
? ? select * from dept d1 where exists (select * from emp e1 where e1.deptno=d1.deptno)
Oracle使用rowid進(jìn)行去重
rank()
Oracle中row_number()、rank（）、dense_rank()的區(qū)別
相關(guān)鏈接如下:
<http://www.cnblogs.com/wuyisky/archive/2010/02/24/oracle_rank.html> ?
<https://www.cnblogs.com/qiuting/p/7880500.html>
Oracle使用rownum進(jìn)行分頁(yè)
偽列：rownum,系統(tǒng)自動(dòng)生成, 表示行號(hào), 起始值為1, 查到結(jié)果后再添加1.? ??
注意: select rownum,"FileName" from file_my where rownum>2 ?--無(wú)任何記錄
select rownum from table1 ?
rownum前面加不了表的前綴(別名) ?
select name from(select name,rownum rm from table1) where rm<10 and rm>3;
如果要限定范圍查詢需要使用子查詢，無(wú)法使用rownum直接進(jìn)行范圍限定，rownum<10 and rownum>3是不能查詢出正確的結(jié)果，需要使用子句中的別名rm. ?
原因:
[ORACLE——ROWNUM解析（使用ROWNUM大于條件，無(wú)法得到任何查詢結(jié)果）]
鏈接:(https://blog.csdn.net/jQuerys/article/details/52432770) ?
關(guān)于分頁(yè)的鏈接：<https://www.cnblogs.com/zhaotiancheng/p/6262635.html>??
集合運(yùn)算
并集
將兩個(gè)查詢結(jié)果進(jìn)行合并. ?
union和union all ?
<https://www.cnblogs.com/macavalier/p/3154269.html>
交集
差集
[minus關(guān)鍵字](https://www.1keydata.com/cn/sql/sql-minus.php)
注意:
? 1. 運(yùn)算時(shí)兩結(jié)果集的列數(shù)需要一致, 空的列需要用null補(bǔ)齊, 不能3列對(duì)2列.
? 2. 列的數(shù)據(jù)類型需要一致.
PLSQL編程
過(guò)程語(yǔ)言, 編寫一些復(fù)雜業(yè)務(wù)邏輯.??
? ? --輸出菱形
? ? ?abs(x)+abs(y)<=m
--------------------------
? ? declare
? ? ? ????m number:=3;
? ? begin
????? ? ? for y in -m..m loop
????????????? ? ? ? for x in -m..m loop
????????????????????? ? ? ? ? if abs(x)+abs(y)<=m then
????????????????????? ? ? ? ? dbms_output.put('*');
????????????????????? ? ? ? ? else
????????????????????? ? ? ? ? dbms_output.put(' ');
????????????????????? ? ? ? ? end if;
????????????? ? ? ? end loop;
????????????? ? ? ? dbms_output.new_line();
????? ? ? end loop;
? ? end;
引用型變量:
? ? --例如 定義一個(gè)與員工表中sal(工資)類型相同的變量
? ? vsal emp.sal%type
記錄型變量:
? ? row emp%rowtype
游標(biāo)
用來(lái)操作查詢的結(jié)果集, 相當(dāng)于JDBC中的ResultSet.
不帶參的普通游標(biāo)
語(yǔ)法:??
? cursor 游標(biāo)名 is 查詢結(jié)果集
開(kāi)發(fā)步驟:??
? 1. 聲明游標(biāo)
? 2. 打開(kāi)游標(biāo) ? ?open 游標(biāo)名稱
? 3. 從游標(biāo)中取數(shù) ?fetch 游標(biāo)名 into 變量
? ? ? 游標(biāo)名%found: 找到數(shù)據(jù)
? ? ? 游標(biāo)名%notfound: 沒(méi)有找到數(shù)據(jù)
? 4. 關(guān)閉游標(biāo) ? close 游標(biāo)名
帶參數(shù)的游標(biāo)
聲明游標(biāo)時(shí)聲明參數(shù), 在打開(kāi)游標(biāo)時(shí)輸入?yún)?shù). ?
系統(tǒng)引用游標(biāo)
開(kāi)發(fā)步驟:??
? 1. 聲明游標(biāo) : 游標(biāo)名 ?sys_refcursor
? 2. 打開(kāi)游標(biāo) ?open 游標(biāo)名 for 結(jié)果集
? 3. 從游標(biāo)中取數(shù)據(jù)
? 4. 關(guān)閉游標(biāo)
使用for循環(huán)遍歷游標(biāo)
for循環(huán)遍歷游標(biāo):??
? 1. 不需要聲明額外的變量
? 2. 不需要打開(kāi)游標(biāo)
? 3. 不需要關(guān)閉游標(biāo)
例如:??
? ? declare
? ? ? ? cursor my_cursor is select * from file_my;
? ? begin
? ? ? ? for one_row in my_cursor loop
? ? ? ? ? ? dbms_output.put_line('文件名:'||one_row."FileName");
? ? ? ? end loop;
? ? end;
異常, 例外:(意外)程序運(yùn)行的過(guò)程發(fā)生異常.
? declare
? ? ????????--聲明變量
? begin
? ? ????????--業(yè)務(wù)邏輯
? exception
? ? ????????--處理異常
? ? ????????when 異常1 then
? ? ? ? ????........
? ? ????????when 異常2 then
? ? ????????........
? ? ????????when others then
? ? ????????--處理其他異常
? end;
可直接使用的異常:
zero_divide :除零異常 ?
value_error :類型轉(zhuǎn)換異常 ?
too_many_rows :查詢出多行記錄,賦值給只能保存一行記錄的變量 ?
no_data_found :沒(méi)有找到數(shù)據(jù) ?
自定義異常
存儲(chǔ)過(guò)程
相關(guān)鏈接: ?
<https://blog.csdn.net/qq_37057095/article/details/76669489> ?
<https://www.cnblogs.com/taiguyiba/p/7809310.html>
封裝在服務(wù)器上的一段PLSQL代碼片斷, 已經(jīng)編譯完成.??
格式:
? ? create (or replace) procedure 存儲(chǔ)過(guò)程的名稱(參數(shù)名1 in|out 參數(shù)類型, 參數(shù)名2 in|out 參數(shù)類型)
? ? is | as
? ? --聲明部分
? ? begin
? ? --業(yè)務(wù)邏輯
? ? end;
in,out, in out三種參數(shù)類型,
in表示存儲(chǔ)過(guò)程的輸入?yún)?shù),用于procedure內(nèi)部使用;
out表示procedure對(duì)外傳遞所需要的介質(zhì),調(diào)用存儲(chǔ)過(guò)程時(shí),傳入out型變量, 一旦procedure運(yùn)行完成, 變量的值就能裝填完畢;?
in out型綜合兩者的功能;
使用JDBC調(diào)用存儲(chǔ)過(guò)程
JDBC的開(kāi)發(fā)步驟:
? 1. 導(dǎo)入驅(qū)動(dòng)包
? 2. 注冊(cè)驅(qū)動(dòng)
? 3. 獲取鏈接
? 4. 獲取執(zhí)行SQL的statement
? 5. 封裝參數(shù)
? 6. 執(zhí)行SQL
? 7. 獲取結(jié)果
? 8. 釋放資源
merge?into用法
? merge?into A using B on (比較語(yǔ)句)
? when matched then
? ? update 語(yǔ)句
? when not matched then
? ? insert 語(yǔ)句
比較A和B, 是否符合on中的條件, 如果滿足條件則update, 否則insert.
這里的update和insert是經(jīng)過(guò)簡(jiǎn)化的語(yǔ)句.
例如:??
? MERGE INTO A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON (A.id=C.AID)
? WHEN MATCHED THEN
? UPDATE SET A.YEAR=C.YEAR
? WHEN NOT MATCHED THEN
? INSERT(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR);
? commit;
可以加where條件:??
? merge into A_MERGE A USING (select B.AID,B.name,B.year,B.city from B_MERGE B) C
? ON(A.id=C.AID)
? when matched then
? update SET A.name=C.name where C.city != '江西'
? when not matched then
? insert(A.ID,A.name,A.year) values(c.AID,C.name,C.year) where C.city='江西';
? commit;
后接delete:
? ?merge into target a using source b on(a.id=b.aid)
? ?when matched then
? ?update set a.year=b.year
? ?delete where a.id=2
存儲(chǔ)函數(shù)
一段封裝在Oracle服務(wù)器的一段PLSQL代碼片斷??
語(yǔ)法:
? create [or replace] function 存儲(chǔ)函數(shù)的名稱(參數(shù)名 in?參數(shù)類型)
? return 參數(shù)類型
? is | as
? begin
? end;
存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)的區(qū)別:
??1. 本質(zhì)上沒(méi)有區(qū)別
? 2. 函數(shù)存在的意義是給過(guò)程調(diào)用 存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)函數(shù).
? 3. 函數(shù)可以在sql中直接調(diào)用, 存儲(chǔ)過(guò)程不能.
? 4. 存儲(chǔ)過(guò)程可以實(shí)現(xiàn)的存儲(chǔ)函數(shù)也可以實(shí)現(xiàn), 反之亦然.
? 5. 存儲(chǔ)函數(shù)使用的參數(shù)只能是in類型(可省略), 返回值擔(dān)任out類型的功能
例:??
? --查詢指定員工的年薪
? --參數(shù): 員工的編號(hào) ? ?返回: 年薪
? create or replace function func_getsal(vempno number) return number
? is
????????? ? --聲明變量, 保存年薪
????????? ? vtotalsal number;
? begin
????????? ? select sal*2+nvl(comm,0) into vtotalsal from emp where empno=vempno; ?
????????? ? return ?vtotalsal;
? end;
? --調(diào)用
? declare
? ? ????????vsal number;
? begin
????????? ? vsal:=func_getsal(123);
????????? ? dbms_output.put_line(vsal);
? end;
? --調(diào)用
? select ename,func_getsal(empno) from emp;
觸發(fā)器
當(dāng)用戶執(zhí)行了insert | update | delete這些操作后,可以觸發(fā)一系列其他動(dòng)作.??
作用:?
在動(dòng)作執(zhí)行之前或者之后, 觸發(fā)業(yè)務(wù)處理邏輯.
語(yǔ)法:
? create [or replace] trigger 觸發(fā)器名稱
? before | after
? insert | update | delete
? on 表名
? [for each row]
? declare
? begin
? end;
觸發(fā)器的分類:
? 1. 語(yǔ)句級(jí)觸發(fā)器, 也叫表級(jí)觸發(fā)器, 無(wú)論語(yǔ)句影響多少行只會(huì)觸發(fā)一次
? 2. 行級(jí)觸發(fā)器, 影響幾行觸發(fā)幾次
? ? 行級(jí)觸發(fā)器可以使用 :new ?新紀(jì)錄 ? :old ? 操作前的舊記錄
使用行級(jí)觸發(fā)器可以實(shí)現(xiàn)將null值插入主鍵時(shí),自動(dòng)填充相應(yīng)的值.??
事務(wù)
一組操作需要同時(shí)成功或者同時(shí)失敗. ?
事務(wù)的特性:
? 原子性, 隔離性, 持久性, 一致性
控制隔離級(jí)別可以用來(lái)解決一些數(shù)據(jù)讀取的問(wèn)題: 臟讀, 幻讀, 不可重復(fù)讀
MySQL隔離級(jí)別:
? READ UNCOMMITTED: 允許事務(wù)讀取未被其他事務(wù)提交的變更. ?
? READ COMMITTED: 只允許事務(wù)讀取其他事務(wù)已經(jīng)提交的變更. ?
? REPEATABLE READ: 確保事務(wù)可以多次從一個(gè)字段中獲取相同的值, 在整個(gè)事務(wù)持續(xù)期間, 禁止其他事務(wù)對(duì)這個(gè)字段進(jìn)行修改.? (MySQL默認(rèn)級(jí)別)
? SERIALIZABLE: ?確保事務(wù)可以從一個(gè)表中讀取相同的行, 在整個(gè)事務(wù)持續(xù)期間, 禁止其他事務(wù)對(duì)該表執(zhí)行插入、更新和刪除操作. (表鎖)
Oracle隔離級(jí)別:
? READ COMMITTED, SERIALIZABLE, READ ONLY(此時(shí)事務(wù)中不允許DML)
? 默認(rèn)的隔離級(jí)別: READ COMMITTED
提交:?commit
事務(wù)的保存點(diǎn)/回滾點(diǎn):?savepoint 保存點(diǎn)的名稱
回滾:?rollback 或者 rollback to 保存點(diǎn)
關(guān)于事務(wù):
read?uncommited, 一個(gè)事務(wù)中可以讀取到另一個(gè)事務(wù)未提交的修改即臟讀, 數(shù)據(jù)無(wú)鎖, 沒(méi)有隔離性.
read commited, 不會(huì)發(fā)生臟讀, 只能讀取到已經(jīng)提交的修改, 會(huì)發(fā)生重復(fù)讀和幻讀, 行鎖, 一種排他鎖, 一事務(wù)中數(shù)據(jù)修改提交, 但事務(wù)未結(jié)束, 此時(shí)該數(shù)據(jù)加鎖, 另一個(gè)事務(wù)讀取本應(yīng)阻塞, 但是有數(shù)據(jù)初始版本可供其用. (這部分還得再研究研究)
repeatable read, 重復(fù)讀取數(shù)據(jù)未發(fā)生改變, 行鎖, 防止臟讀和不可重復(fù)讀, 不能防止幻讀, 幻讀更多的體現(xiàn)在數(shù)據(jù)數(shù)量的變化, 只鎖行是規(guī)避不了的, 但是有大佬糾正說(shuō): 數(shù)據(jù)行數(shù)的變化不一定是幻讀, 幻讀更準(zhǔn)確的定義是新的select操作不支持后續(xù)的業(yè)務(wù)操作.比如,?select 某記錄是否存在，不存在，準(zhǔn)備插入此記錄，但執(zhí)行 insert 時(shí)發(fā)現(xiàn)此記錄已存在，無(wú)法插入，此時(shí)就發(fā)生了幻讀。又比如, 事務(wù)隔離級(jí)別設(shè)置為RR, 事務(wù)A查詢發(fā)現(xiàn)兩條記錄, 事務(wù)B此時(shí)執(zhí)行插入2條記錄提交, 事務(wù)A再查詢發(fā)現(xiàn)記錄仍為2條, 沒(méi)有出現(xiàn)幻讀的情況,?但此時(shí)是RR情況, 是沒(méi)有解決幻讀的能力的, 此時(shí)發(fā)生的是不可重復(fù)讀的問(wèn)題的解決. 第二次查詢時(shí)進(jìn)行的是快照讀, 即讀取之前的版本.?
serializable, 串行, 鎖表.
事務(wù)部分非常重要, 在后臺(tái)框架部分Spring的事務(wù)支持還需要再合并到一起研究研究.
視圖
對(duì)查詢結(jié)果的一個(gè)封裝,視圖中所有的數(shù)據(jù)都來(lái)自于源表, 視圖本身不存儲(chǔ)任何數(shù)據(jù)
? 1. 能夠封裝復(fù)雜的查詢結(jié)果
? 2. 屏蔽表中的細(xì)節(jié)
語(yǔ)法:
? create or replace view 視圖名 as 查詢語(yǔ)句 [with read only]
注意:
通常不要通過(guò)視圖修改數(shù)據(jù), 視圖創(chuàng)建時(shí), 通常要加上with read only, 因?yàn)橐晥D數(shù)據(jù)修改會(huì)影響原表, 而且很有可能是出乎意料的修改, 因?yàn)橐晥D可能是復(fù)雜SQL的結(jié)果集.
同義詞
作用是字面上的意思.
create [public] synonym 同義詞名 for 表或視圖(可以是db link的表)
序列(sequence)
序列（Sequence），又叫序列生成器，用于提供一系列的數(shù)字，開(kāi)發(fā)人員使用序列生成唯一鍵。每次訪問(wèn)序列，序列按照一定的規(guī)律增加或者減少。 ?
序列的定義存儲(chǔ)在SYSTEM表空間中，序列不像表，它不會(huì)占用磁盤空間。 ?
序列獨(dú)立于事務(wù)，每次事務(wù)的提交和回滾都不會(huì)影響序列。 ?
MySQL使用的是auto_increment.
創(chuàng)建序列:
? ? create sequence 序列名稱
? ? start with 從幾開(kāi)始
? ? increment by 步長(zhǎng),每次增長(zhǎng)多少
? ? maxvalue 最大值 | nomaxvalue
? ? minvalue 最小值 | nominvalue
? ? cycle | nocycle 是否循環(huán)
? ? cache 緩存的數(shù)量 | nocache
? 例如:
? ? create sequence seq1
? ? start with 1
? ? increment by 2
? ? maxvalue 30
? ? cycle
? ? cache 10;
?使用最多的寫法:
? ? create sequence 序列名稱; --無(wú)最大值,從1開(kāi)始,每次增長(zhǎng)1, 不循環(huán), 不緩存
?使用序列值:
? ? sequence名.currval ?--當(dāng)前值
? ? sequence名.nextval --下一個(gè)值
注意:?currval需要在調(diào)用一次nextval之后才能使用
索引
<https://www.cnblogs.com/tianmingt/articles/4444885.html> ?
語(yǔ)法:?
? create index 索引的名稱 on 表名(列)
注意:
主鍵約束自帶主鍵索引, 唯一約束自帶唯一索引 ?
SQL優(yōu)化的索引策略:??
? 1. F5調(diào)用explain 查看計(jì)劃
? 2. cost CPU調(diào)用次數(shù)
? 3. Cardinality 影響行數(shù)
索引的原理
btree ?(balance Tree) 平衡二叉樹(shù) (在數(shù)據(jù)結(jié)構(gòu)里面再說(shuō)吧)
索引未生效的原因
<https://www.cnblogs.com/gaoyuechen/p/8058246.html>
SQL執(zhí)行計(jì)劃
使用情景:??
? 1.當(dāng)某一條SQL執(zhí)行速度緩慢, 可以使用方式一或者四;
? 2.跟蹤某條SQL最簡(jiǎn)單的是方法一,其次是方法二;
? 3.如果想要觀察某條或者多條SQL的執(zhí)行情況, 只能使用方法四和方法六;
? 4.如果SQL含有多函數(shù), 函數(shù)中存在SQL嵌套, 多層遞歸調(diào)用, 想準(zhǔn)確分析, 只能使用方法五;
? 5.要想確保看到真實(shí)的執(zhí)行計(jì)劃, 不能使用方法一和二;
? 6.想要獲取表被訪問(wèn)的次數(shù), 只能使用方法三;
方式一: explain plan for
? ? 步驟1:explain plan for 你的SQL
? ? 步驟2:select * from table(dbms_xplan.display());
如果使用PLSQL, 可以按F5查看, 或使用 explain plan 專用窗口進(jìn)行顯示.
????id：為每個(gè)操作的唯一序列號(hào)
? ? operation：為每個(gè)操作的名稱和方式
? ? name：是操作對(duì)象
? ? Rows：Oracle估算當(dāng)前操作影響的行數(shù)
? ? bytes：Oracle估算當(dāng)前操作的影響的數(shù)據(jù)量（單位byte）
? ? Cost：Oracle計(jì)算出來(lái)的執(zhí)行該操作的代價(jià)
? ? Time：Oracle估算出來(lái)的該操作花費(fèi)的時(shí)間
按照顯示的邏輯: 并列的操作從上向下執(zhí)行, 靠右的操作優(yōu)先于靠左的操作.
對(duì)于操作Id前面帶*號(hào)的,表示帶有謂語(yǔ)條件(訪問(wèn)條件Access或者過(guò)濾條件Filter).
操作說(shuō)明:
一.Table Access...
1. 全表掃描:讀取表中所有的行，并檢查每一行是否滿足SQL語(yǔ)句中的 Where 限制條件.
2. RowId的表存取: 通過(guò)RowId可以定位數(shù)據(jù)所在的數(shù)據(jù)文件、數(shù)據(jù)塊、以及在數(shù)據(jù)塊中的位置.
3. 索引掃描: 在索引塊中存儲(chǔ)索引值和RowId, 索引的概念結(jié)構(gòu)可以如下理解,
所以,索引掃描分為兩步,
Ⅰ：掃描索引得到對(duì)應(yīng)的ROWID
Ⅱ：通過(guò)ROWID定位到具體的行讀取數(shù)據(jù)
唯一索引掃描: 每次至多返回一條數(shù)據(jù), 對(duì)于唯一性約束或者主鍵經(jīng)常會(huì)使用該策略.
范圍索引掃描: 發(fā)生范圍掃描的情況,
1. 對(duì)唯一索引使用`<`,`>`,`>=`,`<=`,`<>`,between
2. 在組合索引上,只使用部分列(查詢時(shí)使用前導(dǎo)列,否則走全表掃描)
3. 對(duì)非唯一索引列上進(jìn)行的任何查詢
快速索引掃描: 掃描索引中的所有的數(shù)據(jù)塊，與Index Full Scan類似，但是一個(gè)顯著的區(qū)別是它不對(duì)查詢出的數(shù)據(jù)進(jìn)行排序（即數(shù)據(jù)不是以排序順序被返回）.
全索引掃描: 進(jìn)行全索引掃描時(shí),查詢的數(shù)據(jù)必須從索引中可以直接得到,(全索引掃描只有在CBO模式下會(huì)生效).
跳躍索引掃描: 復(fù)合索引的前導(dǎo)列（索引包含的第一列）沒(méi)有在查詢語(yǔ)句中出現(xiàn)，oralce也可能會(huì)使用該復(fù)合索引，這時(shí)候使用Index Skip Scan;
? 什么時(shí)候會(huì)觸發(fā) INDEX SKIP SCAN 呢？
? 前提條件：表有一個(gè)復(fù)合索引，且在查詢時(shí)有除了前導(dǎo)列（索引中第一列）外的其他列作為條件，并且優(yōu)化器模式為CBO時(shí).
? 當(dāng)Oracle發(fā)現(xiàn)前導(dǎo)列的唯一值個(gè)數(shù)很少時(shí)，會(huì)將每個(gè)唯一值都作為常規(guī)掃描的入口，在此基礎(chǔ)上做一次查找，最后合并這些查詢；
? 例如：
? 假設(shè)表emp有ename（雇員名稱）、job（職位名）、sex（性別）三個(gè)字段，并且建立了如 create index idx_emp on emp (sex, ename, job) 的復(fù)合索引；
? 因?yàn)樾詣e只有 '男' 和 '女' 兩個(gè)值，所以為了提高索引的利用率，Oracle可將這個(gè)復(fù)合索引拆成 ('男', ename, job)，('女', ename, job) 這兩個(gè)復(fù)合索引；
? 當(dāng)查詢 select * from emp where job = 'Programmer' 時(shí)，該查詢發(fā)出后：
? Oracle先進(jìn)入sex為'男'的入口，這時(shí)候使用到了 ('男', ename, job) 這條復(fù)合索引，查找 job = 'Programmer' 的條目；
? 再進(jìn)入sex為'女'的入口，這時(shí)候使用到了 ('女', ename, job) 這條復(fù)合索引，查找 job = 'Programmer' 的條目；
? 最后合并查詢到的來(lái)自兩個(gè)入口的結(jié)果集。
二.Nested Loop
循環(huán)嵌套, 描述的是表連接方式.??
方式一, 優(yōu)點(diǎn): 無(wú)需真正的執(zhí)行
缺點(diǎn):沒(méi)有輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息(產(chǎn)生多少邏輯讀,多少次遞歸調(diào)用,多少次物理讀等),無(wú)法判斷處理了多少行,無(wú)法判斷表被訪問(wèn)了多少次.
方式二: set autotrace on(跟蹤性能統(tǒng)計(jì))
需要在SqlPlus執(zhí)行.
在sqlplus中,
set autotrace on (得到執(zhí)行計(jì)劃,輸出運(yùn)行結(jié)果)
set autotrace traceonly (得到執(zhí)行計(jì)劃, 不輸出運(yùn)行結(jié)果)
set autotrace traceonly explain (得到執(zhí)行計(jì)劃,不輸出運(yùn)行結(jié)果和統(tǒng)計(jì)信息部分, 僅展現(xiàn)執(zhí)行計(jì)劃部分)
set autotrace traceonl statistics (不輸出運(yùn)行結(jié)果和執(zhí)行計(jì)劃部分,僅展現(xiàn)統(tǒng)計(jì)信息部分)
方式二,優(yōu)點(diǎn):可以看到統(tǒng)計(jì)信息(產(chǎn)生的邏輯讀,多少次遞歸調(diào)用,多少次物理讀等)
缺點(diǎn):必須等到執(zhí)行完畢后,才能出結(jié)果, 不顯示表被訪問(wèn)了多少次.
統(tǒng)計(jì)信息解釋如下:
recursive calls 遞歸調(diào)用
db block gets 從buffer cache中讀取block的數(shù)量,用于 insert,delete,update,select for update.
consistent gets 從buffer cache中讀取undo數(shù)據(jù)的block的數(shù)量,用于查詢(除去select for update)的block數(shù)量
physical reads 從磁盤讀取的block數(shù)量
redo size DML生成的redo的大小
sorts (memory) 在內(nèi)存執(zhí)行的排序量
sorts (disk) 在磁盤上執(zhí)行的排序量
整個(gè)邏輯讀=(db block gets)+(consistent gets)
undo和redo(這個(gè)感覺(jué)沒(méi)啥用)
方式三: statistics_level=all(獲取表的訪問(wèn)次數(shù))
推薦使用sqlplus, 否則可能信息不全.
步驟1: alter session set statistics_level=all ;
步驟2: 在此處執(zhí)行你的SQL
步驟3: select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
解讀:??
? 1. Starts為該sql執(zhí)行的次數(shù)。
? 2. E-Rows為執(zhí)行計(jì)劃預(yù)計(jì)的行數(shù)。
? 3. A-Rows為實(shí)際返回的行數(shù)。A-Rows跟E-Rows做比較，就可以確定哪一步執(zhí)行計(jì)劃出了問(wèn)題。
? 4. A-Time為每一步實(shí)際執(zhí)行的時(shí)間（HH：MM：SS.FF），根據(jù)這一行可以知道該sql耗時(shí)在了哪個(gè)地方。
? 5. Buffers為每一步實(shí)際執(zhí)行的邏輯讀或一致性讀。
? 6. Reads為物理讀。
? 7. OMem:當(dāng)前操作完成所有內(nèi)存工作區(qū)(Work Aera)操作所總共使用私有內(nèi)存(PGA)中工作區(qū)的大小，這個(gè)數(shù)據(jù)是由優(yōu)化器統(tǒng)計(jì)數(shù)據(jù)以及前一次執(zhí)行的性能數(shù)據(jù)估算得出的
? 8. 1Mem:當(dāng)工作區(qū)大小無(wú)法滿足操作所需的大小時(shí)，需要將部分?jǐn)?shù)據(jù)寫入臨時(shí)磁盤空間中(如果僅需要寫入一次就可以完成?操作，就稱一次通過(guò)，One-Pass;否則為多次通過(guò)，Multi_Pass).該列數(shù)據(jù)為語(yǔ)句最后一次執(zhí)行中，單次寫磁盤所需要的內(nèi)存大小，這個(gè)由優(yōu)化器統(tǒng)計(jì)數(shù)據(jù)以及前一次執(zhí)行的性能數(shù)據(jù)估算得出的。
? 9. User-Mem:語(yǔ)句最后一次執(zhí)行中，當(dāng)前操作所使用的內(nèi)存工作區(qū)大小，括號(hào)里面為(發(fā)生磁盤交換的次數(shù),1次即為One-? ?Pass,大于1次則為Multi_Pass,如果沒(méi)有使用磁盤，則顯示OPTIMAL)
? 10. OMem、1Mem為執(zhí)行所需的內(nèi)存評(píng)估值，0Mem為最優(yōu)執(zhí)行模式所需內(nèi)存的評(píng)估值，1Mem為one-pass模式所需內(nèi)存的評(píng)估值。
? 11. 0/1/M 為最優(yōu)/one-pass/multipass執(zhí)行的次數(shù)。Used-Mem耗的內(nèi)存
優(yōu)點(diǎn):
1. 可以清晰的從STARTS得出表被訪問(wèn)多少（因?yàn)楸淼脑L問(wèn)開(kāi)銷是很大的，這是很重要的一點(diǎn)）。
2. 可以清晰的從E-ROWS和A-ROWS中得到預(yù)測(cè)的行數(shù)和真實(shí)的行數(shù)，從而可以準(zhǔn)確判斷Oracle評(píng)估是否準(zhǔn)確（很重要的一點(diǎn)）。
3. 雖然沒(méi)有專門的輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息，但是執(zhí)行計(jì)劃中的BUFFERS就是真實(shí)的邏輯讀的多少? ? ? ? ??
缺陷:
1. 必須要等到語(yǔ)句真正執(zhí)行完畢后，才可以出結(jié)果。
2. 無(wú)法控制記錄輸屏打出，不像autotrace有 traceonly 可以控制不將結(jié)果打屏輸出。
3. 看不出遞歸調(diào)用的次數(shù)，看不出物理讀的多少（不過(guò)邏輯讀才是重點(diǎn)）
方式四: 通過(guò)dbms_xplan.display_cursor輸入sql_id參數(shù)直接獲取
步驟： select ?* from table(dbms_xplan.display_cursor('sql_id')); （該方法是從共享池里得到）??
SQL_ID可以通過(guò):??
? select * from v$sql;
注：
1. 還有一個(gè)方法，select ?* from table(dbms_xplan.display_awr('sql_id'));（這是awr性能視圖里獲取到的）
2. 如果有多執(zhí)行計(jì)劃，可以用類似方法查出（0、1、2...）
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',0));
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',1));
優(yōu)點(diǎn):
1. 知道sql_id立即可得到執(zhí)行計(jì)劃，和explain plan for 一樣無(wú)需執(zhí)行；
2. 可以得到真實(shí)的執(zhí)行計(jì)劃。（停，等等，啥真實(shí)的，剛才這幾個(gè)套路中，還有假的執(zhí)行計(jì)劃的嗎？)
缺陷:
1. 沒(méi)有輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息（產(chǎn)生多少邏輯讀，多少次遞歸調(diào)用，多少次物理讀的情況）；
2. 無(wú)法判斷是處理了多少行； ?
3. 無(wú)法判斷表被訪問(wèn)了多少次。
方式五: 10046 trace跟蹤
在sqlplus中執(zhí)行,
步驟1: alter session set events '10046 trace name context ?forever,level 12'; （開(kāi)啟跟蹤）
步驟2: 執(zhí)行你的語(yǔ)句
步驟3: alter session set events '10046 trace name context off'; ? （關(guān)閉跟蹤）
步驟4: 找到跟蹤后產(chǎn)生的文件
步驟5: tkprof ?trc文件 ?目標(biāo)文件（沒(méi)有目標(biāo)文件會(huì)彈出打開(kāi)窗口）? sys=no sort=prsela,exeela,fchela ?（格式化命令）
如果需要找到跟蹤文件, 可以使用:??
? select d.value
? || '\'
? || LOWER (RTRIM(i.INSTANCE, CHR(0)))
? || '_ora_'
? || p.spid
? || '.trc' trace_file_name
? from (select p.spid
? ? from v$mystat m,v$session s, v$process p
? ? where ?m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
? ? (select t.INSTANCE
? ? FROM v$thread t,v$parameter v
? ? WHERE v.name='thread'
? ? AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
? ? (select value
? ? from v$parameter
? ? where name='user_dump_dest') d;
格式化文件,并輸出到指定位置: ? ?
然后在D://1.txt就能看到.
優(yōu)點(diǎn):
1. 可以看出SQL語(yǔ)句對(duì)應(yīng)的等待事件
2. 如果SQL語(yǔ)句中有函數(shù)調(diào)用，SQL中有SQL，將會(huì)都被列出，無(wú)處遁形。
3. 可以方便的看出處理的行數(shù)，產(chǎn)生的物理邏輯讀。
4. 可以方便的看出解析時(shí)間和執(zhí)行時(shí)間。
5. 可以跟蹤整個(gè)程序包
缺陷:
1. 步驟繁瑣，比較麻煩
2. 無(wú)法判斷表被訪問(wèn)了多少次。
3. 執(zhí)行計(jì)劃中的謂詞部分不能清晰的展現(xiàn)出來(lái)。
方式六: awrsqrpt.sql工具
在sqlplus操作, ?
步驟1：@?/rdbms/admin/awrsqrpt.sql
步驟2：依次輸入, 你想要的文件格式,比如html,你想要的天數(shù), 選擇你要的斷點(diǎn)（begin snap 和end snap)
步驟3：輸入你的sql_id,輸入一個(gè)報(bào)告名稱. ?
最終生成報(bào)表.
