1.偽表,偽列

1.1.偽表dual

1. 概念:

   • 就是一個(gè)不真實(shí)存在的虛擬表.偽表的作用就是用來構(gòu)造一個(gè)符合sql規(guī)則的select語句.簡單來說,偽表就是為讓select語句不出現(xiàn)錯(cuò)誤.

2. 應(yīng)用場景

   • 用來查詢系統(tǒng)參數(shù)

   • 可以用來調(diào)用函數(shù)

   • 可以用來查詢計(jì)算結(jié)果

           -- 查詢一下的當(dāng)前的登錄用戶
           select user from dual; 
           
           -- 查詢系統(tǒng)當(dāng)前的時(shí)間
           select sysdate from dual;
           
           -- 按照指定的格式輸出當(dāng)前系統(tǒng)的時(shí)間 yyyy-MM-dd,調(diào)用函數(shù)
           select to_char(sysdate,'yyyy-MM-dd HH:mm:ss') from dual;
           
           -- 查詢計(jì)算的結(jié)果
           select 1+2 from dual;
     

1.2偽列rowid

1. 在ORacle數(shù)據(jù)庫中插入數(shù)據(jù)的時(shí)候,Oracle數(shù)據(jù)庫會(huì)為該條數(shù)據(jù)創(chuàng)建一個(gè)ID.該ID號(hào)就是這條數(shù)據(jù)在磁盤上的物理地址.

        -- rowid;注意:如果需要查詢*和其他的字段,就需要給*取別名
        -- 如果只是單獨(dú)查詢*不需要?jiǎng)e名
        select rowid,e.* from emp e;
   

rowid.png

2. 但是在實(shí)際開發(fā)中,一般是不會(huì)直接使用rowid的查詢表記錄,我們是根據(jù)主鍵列查詢表的記錄.
3. 注意:rowid只能作為條件進(jìn)行查詢,不可以修改rowid的值

1.3.偽列rownum

1. Oracle執(zhí)行查詢的時(shí)候,rownum是根據(jù)sql查詢出的結(jié)果給每行分配一個(gè)邏輯編號(hào)；每次的查詢都會(huì)有不同的編號(hào)墨林。編號(hào)從1開始.該編號(hào)就是偽列rownum,rownum的作用就是用來實(shí)現(xiàn)分頁的功能.

        -- 查詢emp表中的第二頁的數(shù)據(jù),每一頁顯示5行
        select * from (select rownum r,e.* from emp e where rownum <= 10) where r >= 5;
   

2. 注意:使用rownum進(jìn)行查詢的時(shí)候,不可以使用大于,大于等于,等于號(hào).如果是從1開始就除外.因?yàn)閞ownum是邏輯編號(hào),如果新增了數(shù)據(jù),rownum就會(huì)改變,所以等于號(hào)沒有意義.

2.運(yùn)算符

1. 算術(shù)運(yùn)算符: + - * / 沒有%

        -- 查詢員工的年薪(工資*12+獎(jiǎng)金)
        select ename,sal*12+comm as 年薪 from emp;
   

2. 比較運(yùn)算符: > >= <= < = != in between...and is null is not null

            -- 獲取工資大于等于2000,而且工資要小于等于3500的員工信息
            select * from emp where sal between 2000 and 3000;
            
            -- 查詢所有獎(jiǎng)金不為null的信息
            select * from emp where comm is not null
   

3. 邏輯運(yùn)算符 and or not

4. 連接運(yùn)算符 ||
   -- 查詢員工的職位信息
   select ename || '的職位是:' || job from emp;

11.png

5. 集合運(yùn)算符

   • 并集:union(去重復(fù)); union all(有重復(fù))

       -- 并集:有重復(fù),就是查詢結(jié)果A中的子集與查詢結(jié)果B中的子集相加
       select ename from emp where sal > 1000 and sal <3500
       union all
       select ename from emp where sal > 1000 and sal < 2500
       
       -- 并集:無重復(fù),就是查詢結(jié)果A中的子集與查詢結(jié)果B中的子集相加然后取出重復(fù)部分
       select ename from emp where sal > 1000 and sal <3500
       union 
       select ename from emp where sal > 1000 and sal < 2500
     

   • 交集:intersect交集 minus 剪集

       -- 交集 是AB的公共數(shù)據(jù)
       select ename from emp where sal > 1000 and sal <3500
       intersect 
       select ename from emp where sal > 1000 and sal < 2500
       
       -- 減集 是A-AB中的公共部分 
       select ename from emp where sal > 1000 and sal <3500
       minus 
       select ename from emp where sal > 1000 and sal < 2500
     

3.函數(shù)

1. 數(shù)值函數(shù)

        -- 數(shù)值函數(shù)
            select mod(10,3) from dual;
            select round(3.1415,3) from dual;
            select round(1314.1415,0) from dual;
            select trunc(1314.1415,3),trunc(1113.1415,-1) from dual;

2. 字符型的函數(shù)

        -- 字符型函數(shù)
        -- 查詢姓名長度大于5的員工信息
        select * from emp where length(ename)>5
        
        -- 替換
        select replace('明天放假嗎?真的放假嗎?','放假','自習(xí)') from dual;
        
        -- 截取電話號(hào)碼中的所有的8
        select substr('13988888888',4,8) from dual;

3. 日期函數(shù)

    -- 獲取到當(dāng)前的時(shí)間
    select systimestamp from dual;
    
    -- 獲取某個(gè)日期的年份
    select extract(year from sysdate) from dual;
    
    -- 需求:查詢1987年入職的員工信息
    select * from emp where extract(year from hiredate) = 1987
    
    -- 獲取到昨天的日期
    select sysdate - interval '1' day from dual;
    select sysdate - interval '1' month from dual;
    select systimestamp - interval '1' hour from dual;

4. 轉(zhuǎn)換函數(shù)

    -- 轉(zhuǎn)換函數(shù)
    select ename,job,to_char(sal,'$999,999,999') from emp;
    select ename,job,to_char(hiredate,'YYYY"年"MM"月"DD HH24:MM:SS') from emp;
    select to_date('2017/4/02','yyyy/MM/dd') from dual;
    select date '2017-04-01' from dual
    select to_timestamp('2017/04/01 12:56:31','yyyy/MM/dd HH24:mi:ss') from dual;
    select timestamp '2017-04-01 12:56:31' from dual

5. 其他函數(shù)

   函數(shù)	功能
   nvl(exp1,exp2)	如果exp1為空,就返回exp2,否則返回exp1
   decode(條件,值1,翻譯值1,值2,翻譯值2....值n,翻譯值n,缺省值)	判斷條件,如果等于值1,就返回翻譯值1,以此類推,如果都不等于,返回缺省值

    -- 給所有入職超過1年的員工獎(jiǎng)金加300塊
    update emp
    set comm = nvl(comm,0) + 300 -- 如果獎(jiǎng)金為null就返回0,如果不為null就返回本身
    where months_between(sysdate,hiredate) > 12
    
    -- 查詢員工的部門名稱,如果10是對應(yīng)accounting,如果是20對應(yīng).
    select ename,deptno,decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES','無部門') from emp;

4.表空間

4.1 表空間的概念

1. 表空間就是用來存儲(chǔ)用戶數(shù)據(jù)的地方.
2. 一個(gè)Oracle數(shù)據(jù)庫可以有多個(gè)表空間,Oracle數(shù)據(jù)庫創(chuàng)建用戶的時(shí)候,Oracle數(shù)據(jù)庫會(huì)給該用戶分配一個(gè)表空間,也就是說,用戶的數(shù)據(jù)是默認(rèn)保存在自己的表空間下面.一個(gè)表空間并不是一個(gè)用戶獨(dú)享的.用戶可以把數(shù)據(jù)保存在不同的表空間里面.
3. 但是表空間值是一個(gè)存儲(chǔ)數(shù)據(jù)的邏輯單元.一個(gè)表空間里面包含了多個(gè)數(shù)據(jù)文件,用戶的數(shù)據(jù)就是保存在這些數(shù)據(jù)文件中.
4. 一個(gè)數(shù)據(jù)庫中有多個(gè)表空間,一個(gè)表空間中有多個(gè)數(shù)據(jù)文件.
5. 每一個(gè)數(shù)據(jù)庫中都有一個(gè)名為SYSTEM的表空間,就是系統(tǒng)表空間,該表空間是在創(chuàng)建數(shù)據(jù)庫或者數(shù)據(jù)庫的安裝的時(shí)候自動(dòng)創(chuàng)建的,用于存儲(chǔ)系統(tǒng)的數(shù)據(jù)字典表,程序單元,過程,包和觸發(fā)器.
6. 數(shù)據(jù)文件都是以DBF結(jié)尾的.

dbf.png

6. 問題:是現(xiàn)有表空間還是先有用戶呢?
   • 先有表空間,用戶創(chuàng)建好后分配表空間.

4.2 創(chuàng)建表空間

1. 語法格式:

         create tablespace 表空間名
         datafile '數(shù)據(jù)文件的路徑'size 默認(rèn)大小 
         [autoextend on] [next 擴(kuò)展大小]
         [maxsize 最大容量]
   

tablespace.png

2. 注意:一個(gè)表空間至少要包含一個(gè)數(shù)據(jù)文件.

4.3 查詢表空間

1. 語法 dba_data_files:查詢表空間以及數(shù)據(jù)文件的信息

dba_data_files.png

2. 問題:如果表空間中的數(shù)據(jù)文件容量超過了他的最大容量,怎么辦?
   • 辦法一:修改數(shù)據(jù)文件的最大容量
   • 辦法二:添加數(shù)據(jù)文件
   • 注意:Oracle數(shù)據(jù)庫不允許修改數(shù)據(jù)文件.

4.4 擴(kuò)展表空間

1. 語法格式:

    alter tablespace 表空間 
    add datafile '數(shù)據(jù)文件的路徑' size 默認(rèn)大小 
    [autoextends on next 擴(kuò)展大小] 
    [maxsize 最大容量]
   

altertablespace.png

2. 注意:Orcale用戶只會(huì)跟表空間打交道,而不會(huì)跟數(shù)據(jù)文件打交道.

4.5 刪除表空間

1. 語法格式: drop tablespace 表空間 [including contents and datafiles];

droptablespace.png

2. 如果指定了 including contents and datafiles 參數(shù),刪除表空間的時(shí)候Oracle數(shù)據(jù)庫就會(huì)把表空間中的所有的數(shù)據(jù)文件也一起刪除

4.6 常用的表空間有哪些

1. 臨時(shí)表空間:用來存儲(chǔ)一些中期活動(dòng)的數(shù)據(jù).(Oracle數(shù)據(jù)庫自動(dòng)維護(hù))

2. 撤銷表空間:是用來保存未提交事務(wù)的數(shù)據(jù).修改數(shù)據(jù)的時(shí)候,修改前的數(shù)據(jù)就會(huì)保存到撤銷表空間里面,如果執(zhí)行了事務(wù)提交的操作,在撤銷表空間下的數(shù)據(jù)就會(huì)被清空,如果執(zhí)行了事務(wù)回滾的操作,Oracle數(shù)據(jù)庫就會(huì)把撤銷表空間下的數(shù)據(jù)覆蓋修改后的數(shù)據(jù).(Oracle數(shù)據(jù)庫的自動(dòng)維護(hù))

3. 永久表空間,它是用來存儲(chǔ)用戶的永久數(shù)據(jù)的,包括了保存表,視圖,過程,索引等數(shù)據(jù).(我們自己維護(hù)的一個(gè)表空間).

5.用戶管理

5.1 查詢Oracle數(shù)據(jù)庫的用戶

1. dba_users:查詢當(dāng)前系統(tǒng)中所有用戶的詳細(xì)信息

dba_users.png

5.2 創(chuàng)建用戶

語法格式:

    create user 用戶名 identified by 密碼[default tablespace 表空間]

createuser.png

5.3 用戶授權(quán)

5.3.1 系統(tǒng)權(quán)限

1. 系統(tǒng)權(quán)限:就是可以創(chuàng)建系統(tǒng)對象(表,索引,視圖,同義詞)的權(quán)限.
2. 授予系統(tǒng)權(quán)限:grant create XXX to 用戶;
   • create session: 用戶登錄的權(quán)限
   • create tbale:創(chuàng)建表
   • craete index:創(chuàng)建索引的權(quán)限
   • .....注意只有管理員才有權(quán)利授予系統(tǒng)權(quán)利

grantcreate.png

3. 模式Schema
   • Oracle數(shù)據(jù)庫管理員創(chuàng)建用戶的時(shí)候,Oracle數(shù)據(jù)庫會(huì)為該用戶創(chuàng)建一個(gè)模式,模式就是一個(gè)用戶中所有對象的集合.一個(gè)用戶對應(yīng)著一個(gè)模式.模式名與用戶名是相同的.如果我們要訪問其他用戶的對象,那么就必須要通過'模式.對象'進(jìn)訪問.

5.3.2 對象權(quán)限

1. 對象權(quán)限:就是對數(shù)據(jù)庫對象的操作權(quán)限.

2. 授予對象權(quán)限的語法:grant 操作 on 對象 to 用戶

grantall.png

3. 常見的操作:select; insert; delete; update; all

4. 注意:只有系統(tǒng)管理員或者是對象的擁有者才有權(quán)利授權(quán)給其他用戶訪問該用戶的對象.

5.3.3 查看用戶權(quán)限

1. user_sys_privs:查詢當(dāng)前用戶的系統(tǒng)權(quán)限

user_sys_privs.png

2. user_tab_privs:查詢當(dāng)前用戶的對象權(quán)限

user_tab_privs.png

5.3.4 回收權(quán)限

1. 回收對象權(quán)限

   revoke 操作 on 對象 from 用戶

revokedelete.png

2. 回收系統(tǒng)權(quán)限

   revoke create XXX from 用戶

revokecreate.png

5.4 修改用戶密碼

1. 修改密碼的語法: alter user 用戶名 identified by 密碼

alteruser.png

5.5 刪除用戶的語法

1. 語法:drop user 用戶 [cascade];
   • 如果刪除用戶的時(shí)候,如果該用戶下已經(jīng)創(chuàng)建了對象,那么就需要指定cascade關(guān)鍵字把這些對象一起刪除.

dropuser.png

6.視圖

6.1 視圖的概念

1. 視圖是由一個(gè)或者是多個(gè)表組成的數(shù)據(jù)庫對象.這些表也稱之為視圖的基表,如果要?jiǎng)?chuàng)建視圖,就需要有create view的權(quán)限
2. 限制對表中某些列的訪問
3. 簡化select語句

6.2 創(chuàng)建視圖

1. 創(chuàng)建視圖語法:

   create or replace view 視圖名 as select 語句

    -- 需求:授予lyric用戶訪問emp表,但是限制lyric訪問comm和sal
    create or replace view v_emp
    as 
    select empno,ename,job,hiredate,deptno from emp;
    -- 使用視圖
    select * from v_emp
   

注意:視圖本身是不保存數(shù)據(jù)的,視圖的數(shù)據(jù)是從視圖中的基表查詢出來的.
對視圖的操作實(shí)際上是對視圖中基表的操作.

6.3 刪除視圖

1. 語法格式: drop view 視圖名稱;

7.同義詞

7.1 概念

1. 同義詞就是一個(gè)對象的別名.
2. 使用別名的好處:
   • 簡化對對象的訪問
   • 把對象的名字隱藏起來

7.2 創(chuàng)建同義詞(私有同義詞)

1. 語法格式:create synonym 同義詞 for 對象

2. 注意:如果要?jiǎng)?chuàng)建同義詞,必須要具有create synonym權(quán)限.

    -- 創(chuàng)建一個(gè)表
    create table itbaima_crm_dept_user_data (
      id number(4) primary key,
      name nvarchar2(10) unique
    )
    
    -- 創(chuàng)建同義詞(私有同義詞)
    create synonym syn_aa for itbaima_crm_dept_user_data;
    
    -- 使用同義詞
    insert into syn_aa values (1,'狗娃');
    select * from syn_aa
   

7.3 公有同義詞

1. 公有同義詞是可以被public用戶組的所有用戶訪問.共有同義詞一般是由數(shù)據(jù)庫管理員負(fù)責(zé)創(chuàng)建,通常使用公有同義詞去代替一些普通的對象.

2. 語法:

   • create public synonym 公有同義詞 for 對象;

3. 把公有同義詞授權(quán)給public權(quán)限組

   grant select on 公有同義詞 to public;

4. 注意:使用公有同義詞是不需要指定模式.

createpublicsynonym.png

7.4 刪除同義詞

1. 刪除私有的同義詞

`drop synonym 同義詞`

2. 刪除公有的同義詞

   drop public sysnonym 同義詞

dropsynonym.png

3. 注意:只有同義詞的創(chuàng)建者或者是管理員才可以刪除同義詞或者公有同義詞

8.索引

8.1 索引概念

1. 索引就是創(chuàng)建在某一列或者是多列上面的數(shù)據(jù)庫對象.

2. 作用:可以提高對表的查詢效率.

3. 原理:

   • 如果沒有索引:Oracle數(shù)據(jù)庫會(huì)從表的第1條數(shù)據(jù)一條一條往下查詢,直到找到滿足條件的記錄為止.
   • 為什么對表的某一列創(chuàng)建索引之后會(huì)提高表的查詢效率?
   • 如果對表的某一列創(chuàng)建了索引,那么查詢表數(shù)據(jù)的時(shí)候,Oracle數(shù)據(jù)庫就不會(huì)直接查詢目標(biāo)表了,而是先去查詢索引(二叉樹的算法).找到之后就根據(jù)該索引值的rowid直接定位到目標(biāo)表的某一行.

4. 索引缺點(diǎn):索引會(huì)影響到數(shù)據(jù)的更新效率.

   • 因?yàn)閯?chuàng)建了索引之后會(huì)對目標(biāo)表的增刪改操作都要對索引進(jìn)行增刪改操作.如果數(shù)據(jù)量越大,執(zhí)行的效率就會(huì)越低.

8.2 創(chuàng)建索引

1. 語法格式:create [unique] index 索引名 on 表(列..)

        -- 創(chuàng)建唯一索引
        create unique index idx_emp_ename on emp(ename)
   

2. unique: 如果指定了unique關(guān)鍵字,那么這一列的數(shù)據(jù)就不允許重復(fù).

3. 注意:如果創(chuàng)建了索引之后,那么執(zhí)行查詢的時(shí)候,Oracle數(shù)據(jù)庫就會(huì)先從索引表中查詢,而不會(huì)直接查詢目標(biāo)表.

8.3 刪除索引

1. 語法格式: drop index 索引名字

        -- 刪除索引
        drop index idx_emp_ename
   

8.4 使用索引的建議

1. 如果執(zhí)行查詢比較少,但是更新比較多的時(shí)候,不建議創(chuàng)建索引;
2. 對于不經(jīng)常查詢的列,也不適合創(chuàng)建索引.
3. 有些數(shù)據(jù)類型不適合創(chuàng)建索引,比如:大文本類型,二進(jìn)制類型;因?yàn)樗饕胁恢С謑ine關(guān)鍵字,'%關(guān)鍵字%'.

9.序列

9.1 概念

1. 序列是一個(gè)用來生成唯一值的數(shù)據(jù)庫對象,一般序列是用來做為主鍵的自增長
2. 一個(gè)表對應(yīng)著一個(gè)序列.

9.2 創(chuàng)建序列

1. 語法:

2.          create sequence 序列名 
            start n -- 開始值
            increment by n -- 步長
            maxvalue n | minvalue n -- 指定序列的最大值或者是最小值 
            nocycle | cycle -- 是否循環(huán)正常
            cache n; -- 預(yù)先會(huì)生成n個(gè)序列值保存在緩沖中,
   
   
        -- 創(chuàng)建一個(gè)序列
        create sequence seq_emp_empno04
        start with 1000 -- 開始值
        increment by 2 -- 步長
        maxvalue 9999 -- 最大值
        nocycle -- 不循環(huán)生成
        cache 10; 
   

9.3 使用序列

1. nextval:生成并返回一個(gè)序列值
2. currval:返回當(dāng)前的序列值
3. 注意:如果創(chuàng)建了序列之后沒有調(diào)用nextval方法就調(diào)用了currval屬性,就會(huì)引發(fā)錯(cuò)誤.
4. 注意:序列保證唯一性,但是不能保證序列的連續(xù)性.

9.4 修改序列

1. 語法格式:

2.          alter sequence 序列名
            increment by n -- 步長
            maxvalue n | minvalue n -- 指定序列的最大值或者是最小值 
            nocycle | cycle -- 是否循環(huán)正常
            cache n; -- 預(yù)先會(huì)生成n個(gè)序列值保存在緩沖中,
   
        -- 使用序列
    select seq_emp_empno.nextval from dual;
    select seq_emp_empno.currval from dual;
    
    -- 插入數(shù)據(jù)
    insert into emp (empno,ename)
    values (seq_emp_empno.nextval,'狗娃' || seq_emp_empno.currval);
   

9.5 刪除序列

1. 語法格式:

    drop sequence 序列名.
   

2. 注意:序列是誰創(chuàng)建,誰就可以刪除,管理員也可以刪除.

    -- 刪除序列
    drop sequence seq_emp_empno04;
   

總結(jié)今天學(xué)習(xí)內(nèi)容

1. 偽表和偽列
2. 運(yùn)算符
3. 函數(shù)
4. Oracle表空間
5. 用戶與權(quán)限(DDL)
6. Oracle數(shù)據(jù)庫對象:視圖,同義詞,索引,序列