oracle基本運(yùn)算符

• between ....and .... 運(yùn)算符泊愧，取一個(gè)區(qū)間的值，僅限一個(gè)條件中
• oracle基本語(yǔ)法

select  [distinct]{*|column|expression [alisa],...}
from table

select 子句表示所檢索的數(shù)據(jù)列
from子句表示檢索的數(shù)據(jù)來(lái)自哪個(gè)表
不區(qū)分大小寫(xiě)，建議小寫(xiě)，建議分多行書(shū)寫(xiě)衷笋，可以增強(qiáng)代碼的可讀性；通常以子句為單位進(jìn)行分行矩屁；
關(guān)鍵字不可縮寫(xiě)辟宗、分開(kāi)以及跨行書(shū)寫(xiě)；
選擇所有列

select *
from emp;

選擇指定的列

select deptno,sal
from emp;

算數(shù)運(yùn)算符主要包含：
（+ - * /）
算數(shù)運(yùn)算符的運(yùn)用

select enanme,sal,sal*12
from emp

算數(shù)優(yōu)先級(jí)

• 乘除優(yōu)先于加減
• 相同優(yōu)先權(quán)的表達(dá)式按照從左至右 的順序依次計(jì)算
• 括弧可以提高優(yōu)先權(quán)吝秕，并使表達(dá)式的描述更為清晰
  空值NULL
• 空值null指一種無(wú)效的泊脐、未賦值的或不可用的值
• 空值不同于零或者空格
  連接操作符
• 用于連接列于列、列與字符
• 形式上是以兩個(gè)豎杠||
• 用于創(chuàng)建字符表達(dá)式的結(jié)果列

select ename||job as "employess"
from emp

原義字符串

• 原意字符串是包含在select列表中的一個(gè)字符烁峭、一個(gè)數(shù)字或者一個(gè)日期
• 日期和字符面值必須用單引號(hào)引起來(lái)
• 每個(gè)原義字符串都會(huì)在每個(gè)數(shù)據(jù)行輸出中出現(xiàn)

select ename ||'is a'|| job
from emp

上面其中的is a就為原義字符串 容客、
消除重復(fù)行

• 在select子句中使用關(guān)鍵字distinct可以消除重復(fù)行

select distinct depton
from emp

“*”代表所有的

where運(yùn)算語(yǔ)句的運(yùn)算符

• between...and..運(yùn)算符 取x與y之間的值，僅限一個(gè)條件中用
• in 運(yùn)算符 判斷比較的值是否和集合列表中的任何一個(gè)值相等
• like 運(yùn)算符 模糊查找“%”代表一個(gè)或者多個(gè)约郁，無(wú)窮的"_"只代表一個(gè)缩挑，不常用
• is null 判斷要比較的值是否為空值
• and 兩個(gè)條件都為真 結(jié)果才為真
• or 只需一個(gè)條件為真，結(jié)果就返回真
• not 否定詞 代表反義 通常用在“in”“l(fā)ike”“between..and..”這些運(yùn)算符的前邊鬓梅，在“is null”這個(gè)運(yùn)算符的中間供置，
• order by 釋義為排序運(yùn)算符 在字符串或者數(shù)字后邊加asc為升序 這個(gè)一般為默認(rèn)值，加desc為降序
• 空值null 升序在最后 降序在最前
  包含以上所有運(yùn)算符

select * 
from emp
where 
sal in(1500,3000) and ename like '%M_' and comm is not null 
ORDER BY sal,deptno desc 

分組函數(shù)

別名：聚集函數(shù)

• 分組函數(shù)代碼
  min最小值绽快，列個(gè)可以是數(shù)字芥丧、日期或字符串；
  max最大值坊罢，列個(gè)可以是數(shù)字续担、日期或字符串；
  sum求和艘绍，列只可以是數(shù)字;
  avg平均值, 列只可以是數(shù)字;
  count表中所有記錄數(shù)赤拒，滿足非空（null）行的個(gè)數(shù)
  distinct消除重復(fù)記錄
  nvl（參數(shù)a，參數(shù)b）a一般為列名，b為任意值挎挖；如果第一個(gè)參數(shù)列名的值是null这敬，這個(gè)函數(shù)的返回值就是b，反之則為原值
• 練習(xí)

1. 查詢部門(mén)20的員工蕉朵，每個(gè)月的工資總和及平均工資崔涂。
2. 查詢工作在CHICAGO的員工人數(shù)，最高工資及最低工資始衅。
3. 查詢員工表中一共有幾種崗位類(lèi)型冷蚂。

SELECT
    SUM (sal),
    AVG (sal)
FROM
    EMP
WHERE
    DEPTNO = 20

SELECT
    COUNT (*),
    MAX (sal),
    MIN (sal)
FROM
    emp A
JOIN dept b ON A .deptno = b.deptno
WHERE
    b.loc = 'CHICAGO'

SELECT
    COUNT (DISTINCT(JOB))
FROM
    EMP

• group by 分組語(yǔ)句
  select 語(yǔ)句中出了分組函數(shù)那些項(xiàng)其他列的語(yǔ)句只能寫(xiě)group by 后邊接的列明，或者用函數(shù)調(diào)用汛闸；
  oracle-sql多表連接

多表連接

• 需要多考慮一個(gè)條件 三張表 兩兩有相同的一列

等值連接

• 兩張表里有相同列蝙茶，為條件連接
• 以下為等值連接代碼

select *
from emp,dept
where emp.deptno =dept.deptno

非等值連接

• 以下為非等值連接的代碼

select a.ename,a.sal,b.GRADE 
from emp a,salgrade b
where a.sal between b.losal and b.hisal

內(nèi)部連接

• （典型的聯(lián)接運(yùn)算，使用像 = 或 <> 之類(lèi)的比較運(yùn)算符）诸老。包括相等聯(lián)接和自然聯(lián)接隆夯。
  內(nèi)聯(lián)接使用比較運(yùn)算符根據(jù)每個(gè)表共有的列的值匹配兩個(gè)表中的行。例如别伏，檢索 emp和dept表中員工部門(mén)編號(hào)號(hào)相同的所有行

外部連接

• 外聯(lián)接可以是左向外聯(lián)接蹄衷、右向外聯(lián)接或完整外部聯(lián)接。
  在 FROM子句中指定外聯(lián)接時(shí)厘肮，可以由下列幾組關(guān)鍵字中的一組指定：

1）LEFT JOIN或LEFT OUTER JOIN
左向外聯(lián)接的結(jié)果集包括 LEFT OUTER子句中指定的左表的所有行愧口，而不僅僅是聯(lián)接列所匹配的行。如果左表的某行在右表中沒(méi)有匹配行类茂，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值耍属。

2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外聯(lián)接是左向外聯(lián)接的反向聯(lián)接。將返回右表的所有行大咱。如果右表的某行在左表中沒(méi)有匹配行恬涧，則將為左表返回空值注益。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部聯(lián)接返回左表和右表中的所有行碴巾。當(dāng)某行在另一個(gè)表中沒(méi)有匹配行時(shí)，則另一個(gè)表的選擇列表列包含空值丑搔。如果表之間有匹配行厦瓢，則整個(gè)結(jié)果集行包含基表的數(shù)據(jù)值。

笛卡爾積

• 第一個(gè)表中的所有行和第二個(gè)所有行中都發(fā)生
• 一定不要發(fā)生此現(xiàn)象
• 以下為笛卡爾積代碼

select *
from emp,dept

交叉聯(lián)接

• 交叉連接返回左表中的所有行啤月，左表中的每一行與右表中的所有行組合煮仇。交叉聯(lián)接也稱(chēng)作笛卡爾積。
  FROM 子句中的表或視圖可通過(guò)內(nèi)聯(lián)接或完整外部聯(lián)接按任意順序指定谎仲；但是浙垫，用左或右向外聯(lián)接指定表或視圖時(shí)，表或視圖的順序很重要。有關(guān)使用左或右向外聯(lián)接排列表的更多信息夹姥，請(qǐng)參見(jiàn)使用外聯(lián)接杉武。
  例子：
  a表

b表

1） 內(nèi)連接

select a.*,b.*   
from a inner join b on a.id=b.parent_id 

2）左連接

   select a.*,b.*   
  from a left join b on a.id=b.parent_id    

3） 右連接

select a.*,b.*   
from a right join b on a.id=b.parent_id     

4） 完全連接

  select a.*,b.*   
  from a full join b on a.id=b.parent_id   

• 自身連接
  一張表格中通過(guò)有關(guān)系的條件進(jìn)行連接的一種方式
  把同一張表看成兩張表來(lái)思考

查出員工的姓名及其編號(hào)和他直接上級(jí)的姓名及員工編號(hào)

select e2.ename,e2.empno.e1.ename,e1.empno  
from emp e1
join emp e2 on e1.empno = e2.mgr

a,b兩個(gè)表那個(gè)是員工那個(gè)是經(jīng)理，需要看連接條件

• SQL-1999語(yǔ)法的連接
  join
  on子句
  只起到連接多張表的作用辙售；
  可以提高代碼的可讀性轻抱；

子查詢和高級(jí)子查詢

子查詢

• 子查詢可以嵌于where、having旦部、from子句中祈搜；
• 子查詢的類(lèi)型
  單行子查詢
  多行子查詢
  多列子查詢
• 單行運(yùn)算符
  =、>士八、<容燕、>=、<=婚度、<>
• 多行運(yùn)算符
  in缰趋、any、all

子查詢結(jié)果中有一條是空值陕见，這條空值會(huì)導(dǎo)致查詢沒(méi)有記錄返回秘血；這是因?yàn)樗袟l件和空值比較的結(jié)果都是空值，因此無(wú)論什么時(shí)候只要空值有可以能成為子查詢結(jié)果集合中的一部分评甜，就不能使用not in運(yùn)算符

• rownum
  rownum是一個(gè)偽列灰粮，偽列是沒(méi)有存儲(chǔ)在表中的特殊列，他可以輸出為順序號(hào)忍坷，但是沒(méi)有實(shí)際意義粘舟，不能作為關(guān)系列，但可以為這個(gè)表增添查詢條件佩研。
  對(duì)于rownum只能執(zhí)行小于柑肴、小于等于運(yùn)算，不能執(zhí)行大于旬薯、大于等于或一個(gè)區(qū)間運(yùn)算between 晰骑、、绊序、and等
  rownum和order by一起使用時(shí)硕舆，因?yàn)閞ownum在記錄輸出時(shí)生成，而order by子句在最后只執(zhí)行骤公，所以當(dāng)兩者一起使用時(shí)抚官，需要注意rownum實(shí)際是已經(jīng)被排了序的rownum；
• top - n 查詢
  asc查詢最小的n條記錄
  desc查詢最大的n條記錄
  列> 查詢公司員工工資最低的3個(gè)人

select rownum,sal
from (select sal from emp 
        order by  sal   asc)
        where rownum <= 3

分頁(yè)
按照每頁(yè)顯示五條記錄阶捆，分別查詢第一頁(yè)凌节，第二頁(yè)钦听，第三頁(yè)信息，要求顯示員工姓名倍奢，入職日期彪见、部門(mén)名稱(chēng)。

第一頁(yè)
SELECT
    A .ename,
    A .hiredate,
    b.dname
FROM
    (
        SELECT
            ROWNUM,
            EMP.*
        FROM
            emp
        WHERE
            ROWNUM < 5
    ) A
JOIN dept b ON A .DEPTNO = b.deptno
第二頁(yè)
SELECT
    A .ename,
    A .hiredate,
    b.dname
FROM
    (
        SELECT
            ROWNUM rb,
            EMP.*
        FROM
            emp
        WHERE
            ROWNUM <= 10
    ) A
JOIN dept b ON A .DEPTNO = b.deptno
WHERE
    A .rb > 5
    第三頁(yè)
SELECT
    A .ename,
    A .hiredate,
    b.dname
FROM
    (
        SELECT
            ROWNUM rb,
            EMP.*
        FROM
            emp
        WHERE
            ROWNUM <= 15
    ) A
JOIN dept b ON A .DEPTNO = b.deptno
WHERE
    A .rb > 10

集合運(yùn)算

是用來(lái)把兩個(gè)或多個(gè)查詢結(jié)果集做并娱挨、交余指、差的集合運(yùn)算，包含集合運(yùn)算的查詢稱(chēng)為復(fù)合查詢跷坝，
聯(lián)合 union完全聯(lián)合union all相交intersect相減minus

• 聯(lián)合運(yùn)算
• 完全聯(lián)合運(yùn)算
• 相交運(yùn)算
• 相減運(yùn)算
  返回有任意查詢結(jié)果集包含的行酵镜，并且去除重復(fù)行，并且按照查詢結(jié)果集的第一序列升序排序柴钻。
  使用原則
  多個(gè)被聯(lián)合的查詢語(yǔ)句所選擇的列數(shù)和列的數(shù)據(jù)類(lèi)型必須一致淮韭，列的名字不必相同。
  如果多個(gè)查詢結(jié)果都有null值贴届，整個(gè)結(jié)果中只包含一個(gè)null值
  每個(gè)查詢不能包含自己的order by子句靠粪，只能在聯(lián)合之后使用。
  列>查詢編號(hào)為7839的員工當(dāng)前工資毫蚓、崗位及歷史工資占键、崗位？

select sal,job
from emp
where empno = 7839
union
select sal,job
from emp_jobhistory
where empno = 7839

使用union all會(huì)比union的速度快元潘，因?yàn)槭∪チ巳サ糁貜?fù)記錄和排序的時(shí)間

相關(guān)子查詢

列>查詢比本部門(mén)平均薪水高的員工姓名畔乙，薪水

嵌套子查詢的寫(xiě)法
select empno,ename,sal
from emp e
join (select deptno,avg(sal) avgsal
from emp
group by deptno) d  on e.deptno = d.deptno
where  e.sal > d.avgsal

相關(guān)子查詢的寫(xiě)法
select empno,ename,sal,deptno
from emp a
where sal > 
(select avg(sal)
from emp
where deptno = a.deptno)

列>查詢所有部門(mén)名稱(chēng)和人數(shù)

select dname,
(select count(empno)
from emp
where deptno = d.deptno)
from dept d

• exists****not exists
• exists判斷是否“存在”，
• 子查詢中如果有記錄找到翩概，子查詢語(yǔ)句不會(huì)繼續(xù)執(zhí)行牲距，返回值為true
• 子查詢中如果到表的末尾也沒(méi)有記錄找到，返回值為false
• 子查詢并沒(méi)有確切的記錄返回钥庇，只是判斷記錄是是否存在牍鞠，而且只要找到相關(guān)的記錄，子查詢就不需要在執(zhí)行评姨，然后在進(jìn)行下面的操作难述，這樣大大提高了語(yǔ)句的執(zhí)行效率。
• not exists正好相反参咙，判斷子查詢是否沒(méi)有返回值龄广。如果沒(méi)有返回值硫眯，表達(dá)式為真蕴侧，如果找到一條返回值，則為假两入。
  列>查詢那些人是經(jīng)理

select ename,job,sal,deptno
from emp e
where exists 
(select  1
from emp
where mgr = e.empno)

列>查詢那些人不是經(jīng)理

select ename,job,sal,deptno
from emp e
where not exists 
(select  1
from emp
where mgr = e.empno)

子查詢中select 后邊可以是任何值净宵，沒(méi)有確切的返回記錄，因?yàn)閑xists只看存不存在返回記錄，
數(shù)據(jù)庫(kù)操作與事務(wù)控制

dml數(shù)據(jù)庫(kù)操作語(yǔ)言

• 增加行數(shù)據(jù)：使用insert
• 修改行數(shù)據(jù)：使用update
• 刪除行數(shù)據(jù)：使用delete
• 合并行數(shù)據(jù)：使用merge
  創(chuàng)建biaoming這個(gè)表择葡，借用emp里的所有列

create table 'biaoming' as
select * from emp
where 1=0

向biaoming表中插入職位為MANAGER的記錄

insert into biaoming
select *
from emp
where job = 'MANAGER'

插入多行數(shù)據(jù)
不必書(shū)寫(xiě)values子句
INSERT子句和數(shù)據(jù)類(lèi)型必須和子查詢中列的數(shù)量和類(lèi)型相匹配中列的數(shù)量

• 修改數(shù)據(jù)
  修改數(shù)據(jù)使用UPDATE子句完成紧武，語(yǔ)法結(jié)構(gòu)如下：
  把員工編號(hào)為7782的部門(mén)編號(hào)修改為20

UPDATE  emp
SET deptno = 20
WHERE   empno = 7782

WHERE子句用來(lái)限定修改哪些行。
SET子句用來(lái)限定修改哪些列敏储。
修改數(shù)據(jù)中可以應(yīng)用子查詢阻星、相關(guān)子查詢

• 刪除數(shù)據(jù)
  語(yǔ)法結(jié)構(gòu)
  使用 DELETE 語(yǔ)句刪除表中滿足條件的行記錄
  刪除職位是CLERK的員工記錄

DELETE FROM emp
    WHERE   job = 'CLERK'; 

使用刪除數(shù)據(jù)時(shí)一定要看清條件，避免錯(cuò)誤刪除而發(fā)生無(wú)法挽回的可怕事件已添；
刪除數(shù)據(jù)也可以使用子查詢妥箕、相關(guān)子查詢；

事務(wù)(Transaction)概念

• 事務(wù)
  也稱(chēng)工作單元更舞，是由一個(gè)或多個(gè)SQL語(yǔ)句所組成的操作序列畦幢，這些SQL語(yǔ)句作為一個(gè)完整的工作單元，要么全部執(zhí)行成功缆蝉，要么全部執(zhí)行失敗宇葱。在數(shù)據(jù)庫(kù)中，通過(guò)事務(wù)來(lái)保證數(shù)據(jù)的一致性刊头。
• 事務(wù)處理語(yǔ)言
  Transaction Process Language ,簡(jiǎn)稱(chēng)TPL黍瞧，主要用來(lái)對(duì)組成事務(wù)的DML語(yǔ)句的操作結(jié)果進(jìn)行確認(rèn)或取消。確認(rèn)也就是使DML操作生效原杂，使用提交(COMMIT)命令實(shí)現(xiàn)雷逆；取消也就是使DML操作失效，使用回滾(ROLLBACK)命令實(shí)現(xiàn)污尉。
  通過(guò)事務(wù)的使用膀哲，能防止數(shù)據(jù)庫(kù)中出現(xiàn)數(shù)據(jù)不一致現(xiàn)象。如兩個(gè)銀行賬戶進(jìn)行轉(zhuǎn)賬被碗，涉及到兩條更新操作某宪，這兩條更新操作只允許全部成功或失敗，否則數(shù)據(jù)會(huì)出現(xiàn)不一致的現(xiàn)象锐朴。
• 事務(wù)組成
  在數(shù)據(jù)庫(kù)中兴喂，事務(wù)由一組相關(guān)的DML或SELECT語(yǔ)句，加上一個(gè)TPL語(yǔ)句（COMMIT焚志、ROLLBACK）或一個(gè)DDL語(yǔ)句(CREATE衣迷、ALTER、DROP酱酬、TRUNCATE等)或一個(gè)DCL（GRANT壶谒、REVOKE）
• 事物特性
  • 原子性
    事務(wù)就像“原子”一樣，不可被分割膳沽，組成事務(wù)的DML操作語(yǔ)句要么全成功汗菜，要么全失敗让禀，不可能出現(xiàn)部分成功部分失敗的情況
  • 一致性
    一旦事務(wù)完成，不管是成功的陨界，還是失敗的巡揍，整個(gè)系統(tǒng)處于數(shù)據(jù)一致的狀態(tài)。
  • 隔離性
    一個(gè)事務(wù)的執(zhí)行不會(huì)被另一個(gè)事務(wù)所干擾菌瘪。比如兩個(gè)人同時(shí)從一個(gè)賬戶從取錢(qián)腮敌，通過(guò)事務(wù)的隔離性確保賬戶余額的正確性。
  • 持久性
    也稱(chēng)為永久性俏扩，指事務(wù)一旦提交缀皱，對(duì)數(shù)據(jù)的改變就是永久的，不可以再被回滾
• 事物結(jié)束
• 顯示結(jié)束
  提交(COMMIT)：使用COMMIT命令實(shí)現(xiàn)动猬，以成功的方式結(jié)束事務(wù)啤斗，組成事務(wù)的DML語(yǔ)句操作全部生效。
  回滾(ROLLBACK)：使用ROLLBACK命令實(shí)現(xiàn)赁咙，以失敗的方式結(jié)束事務(wù)钮莲，組成事務(wù)的DML語(yǔ)句操作全部被取消。
  隱式結(jié)束
  隱式提交：當(dāng)下列任意一種情況發(fā)生時(shí)彼水，會(huì)發(fā)生隱式提交
  執(zhí)行一個(gè)DDL(CREATE崔拥、ALTER、DROP凤覆、TRUNCATE链瓦、RENAME）語(yǔ)句；
  執(zhí)行一個(gè)DCL(GRANT盯桦、REVOKE)語(yǔ)句慈俯；
  從SQLPlus正常退出（即使用EXIT或QUIT命令退出）；
  隱式回滾：當(dāng)下列任意一種情況發(fā)生時(shí)拥峦，會(huì)發(fā)生隱式回滾
  從SQLPlus中強(qiáng)行退出
  客戶端連接到服務(wù)器端異常中斷
  系統(tǒng)崩潰
• 設(shè)置保存點(diǎn)SAVEPOINT
  如果一個(gè)事物內(nèi)贴膘，設(shè)置了保存點(diǎn),執(zhí)行回滾命令直接回滾到保存點(diǎn)而不是起點(diǎn)；

數(shù)據(jù)定義語(yǔ)言

• 數(shù)據(jù)定義語(yǔ)言
  (Data Definition Language, DDL) 是SQL語(yǔ)言集中負(fù)責(zé)數(shù)據(jù)結(jié)構(gòu)定義與數(shù)據(jù)庫(kù)對(duì)象定義的語(yǔ)言略号，由CREATE刑峡、ALTER與DROP三個(gè)語(yǔ)法所組成。
• 創(chuàng)建表

CREATE TABLE DOSSIER(創(chuàng)建表的表名) (
    ID NUMBER(4), 
   CNAME VARCHAR2(20 ), 
    BIRTHDAY DATE,               
    STATURE    NUMBER(3), 
    WEIGHT NUMBER(5, 2), 
    COUNTRY_CODE CHAR(2 ) DEFAULT ‘01’);（表里面列的名稱(chēng)） 

• 表的數(shù)據(jù)類(lèi)型
  字符型()char()xx)玄柠、varchar2(xx))突梦、數(shù)值型(number(x,y))、日期型(date)(timestamp羽利，此為9i之后新增的可以精確到毫秒)
  字符型宫患，char為固定長(zhǎng)度字符型數(shù)據(jù)，varchar2是可變長(zhǎng)度字符數(shù)據(jù)
  數(shù)值型后邊不加修飾的铐伴，可以是小數(shù)撮奏，整數(shù)俏讹，范圍在-10的125次方到10的126次方当宴。
  （x）表示整型（x畜吊，y）總長(zhǎng)度為x，小數(shù)位最大為y户矢，整數(shù)范圍最大為x-y位x的范圍從1到38玲献，y的范圍從-84到127
• 修改表
  語(yǔ)法

ALTER TABLE table  
ADD (columnname datatype[DEFAULT expr]
          [, columnname datatype]...);