【資料庫】Oracle -- 一文了解Oracle資料庫開發知識地圖-有解無憂

1.Oracle資料庫安裝

1.1.整合PL/SQL Developer軟體

1.點擊Tools—>Preferences—>Connection連接，填寫連接資訊：

2.連接Oracle資料庫，默認連接方式：

192.168.88.6：表示安裝Oracle資料庫的服務器地址；

1521：默認埠號

orcl：默認Oracle安裝資料庫名稱

3.使用修改tnsnames.ora檔案配置方式連接：

①復制遠程服務器Oracle安裝目錄中的tnsnames.ora到本地的instantclient_12_1軟體目錄下：

②修改本地復制的tnsnames.ora配置如下：

③設定本地tnsnames.ora到本地的環境變數配置中：

配置完環境變數之后在繼續登錄PL/SQL去連接Oracle資料庫就可以使用ORCL去連接資料庫了，

2.Oracle體系結構

2.1.資料庫

Oracle資料庫是資料的物理存盤，這就包括（資料檔案ORA或者DBF、控制檔案、聯機日志、引數檔案），其實Oracle資料庫的概念和其它資料庫不一樣，這里的資料庫是一個作業系統只有一個庫，可以看作是Oracle就只有一個大資料庫，

2.2.實體

一個Oracle實體（Oracle Instance）有一系列的后臺行程（Background Processes）和記憶體結構（Memory Structures）組成，一個資料庫可以有n個實體，

2.3.用戶

用戶是在實體下建立的，不同實體可以建相同名字的用戶，

2.4.表空間

表空間是Oracle對物理資料庫上相關資料檔案（ORA或者DBF檔案）的邏輯映射，一個資料庫在邏輯上被劃分成一到若干個表空間，每個表空間包含了在邏輯上相關的一組結構，每個資料庫至少有一個表空間（稱之為system表空間），

每個表空間由同一磁盤上的一個或多個檔案組成，這些檔案叫資料檔案（datafile），一個資料檔案只能屬于一個表空間，

2.5.資料檔案（dbf、ora）

資料檔案是資料庫的物理存盤單位，資料庫的資料是存盤在表空間中的，真正是在某一個或者多個資料檔案中，而一個表空間可以由一個或多個資料檔案組成，一個資料檔案只能屬于一個表空間，一旦資料檔案被加入到某個表空間后，就不能洗掉這個檔案，如果要洗掉某個資料檔案，只能洗掉所屬于的表空間才行，

注意：表的資料，是有用戶放入某一個表空間的，而這個表空間會隨機把這些表資料放到一個或者多個資料檔案中，

由于oracle的資料庫不是普通的概念，oracle是有用戶和表空間對資料進行管理和存放的，但是表不是有表空間去查詢的，而是由用戶去查的，因為不同用戶可以在同一個表空間建立同一個名字的表！這里區分就是用戶了，

3.創建表空間

表空間是Oracle資料庫的邏輯單元，資料庫--表空間，一個表空間可以與多個資料檔案（物理結構）關聯，

一個資料庫下可以建立多個表空間，一個表空間可以建立多個用戶、一個用戶下可以建立多個表，

create tablespace mytable
datafile 'c:\mytable.dbf'
size 100m
autoextend on
next 10m

其中

mytable：為表空間名稱

datafile：指定表空間對應的資料檔案

size：后定義的是表空間的初始大小

autoextend on：自動增長，當表空間存盤都占滿時，自動增長

next：后指定的是一次自動增長的大小

撰寫好sql陳述句之后，點擊小齒輪按鈕，Excute執行即可，

如下陳述句是創建表空間陳述句：

如下是洗掉表空間陳述句：

4.用戶

創建用戶并給用戶授權：

切換登錄用戶：

先選擇Log off進行退出當前用戶，再選擇Log on進行登錄；修改登錄用戶名與密碼進行登錄即可；

5.Oracle資料型別

No	資料型別	描述
1	varchar，varchar2	表示一個字串，可變長度，常用的型別是varchar2
2	NUMBER	NUMBER(n)表示一個整數，長度是n
3	NUMBER	NUMBER(m, n)表示一個小數，總長度是m，小數是n，整數是m-n
4	DATA	表示日期型別
5	CLOB	大物件，表示大文本資料型別，可存4G
6	BLOB	大物件，表示二進制資料，可存4G

6.表的管理

6.1.建表

語法：

create table 表名(

　　欄位1 資料型別 [default 默認值],

　　欄位2 資料型別 [default 默認值],

　　...

)

示例：

6.2.修改表結構

添加一列資料，如果多列資料使用(n1, n2)：

--- 修改表結構
--- 添加一列
alter table person add (gender number(1));

添加之后查看表結構：

修改列型別：

---修改列型別
alter table person modify gender char(1);

修改列名稱：

---修改列名稱
alter table person rename column gender to sex;

洗掉一列：

---洗掉一列
alter table person drop column sex;

6.3.資料的增刪改

添加資料insert：

---添加一條記錄
insert into person(pid, pname) values (1, '小明');
commit

修改資料update：

---修改一條記錄
update person set pname = '小馬' where pid = 1;
commit;

洗掉表資料delete/truncate/drop：

---三個洗掉
---洗掉表中全部記錄
delete from person;
---洗掉表結構
drop table person;
---先洗掉表，再次創建表，效果等同于洗掉表中全部記錄
---在資料量大的情況下，尤其在表中帶有索引的情況下，該操作效率高，
---索引可以提供查詢效率，但是會影響增刪改效率
truncate table person;

6.4.序列

---序列不真的屬于任何一張表，但是可以邏輯和表做系結
---序列：默認從1開始，依次遞增，主要用來給主鍵賦值使用
---dual：虛表，只是為了補全語法，沒有任何意義
create sequence s_person;
select s person.currval from dual;

---添加一條記錄
insert into person (pid, pname) values (s_person.nextval, '小明');
commit;
select * from person;

6.5.Scott用戶介紹

scott是給初學者學習的用戶，學習者可以用Scott登錄系統，注意scott用戶登錄后，就可以使用Oracle提供的資料庫和資料表，這些都是oracle提供的，學習者不需要自己創建資料庫和資料表，直接使用這些資料庫和資料表練習SQL，

---scott用戶，密碼是tiger
---解鎖scott用戶
alter user scott account unlock;
---解鎖scott用戶的密碼【此句也可以用來重置密碼】
alter user scott identified by tiger;

7.Oracle查詢

7.1.單行/多行函式

單行函式：作用于一行，回傳一個值，

多行函式：作用于多行，回傳一個值，

字符函式

select upper('yes') from dual;  -- YES
select lower('YES') from dual;  -- yes

數值函式

select round(26.16, 1) from dual;  -- 四舍五入，后面的引數表示保留的位數，結果26.2
select round(26.16, -1) from dual; -- 往前保留一位小數，結果30
select trunc(56.16, 1) from dual; -- 直接截取，不再看后邊位數的數字是否大于5，56.1
select mod(10, 3) from dual;  -- 求余數

日期函式

---查詢出emp表中所有員工入職距離現在幾天
select sysdate-e.hiredate from emp e;
---算出明天此刻
select sysdate+1 from dual;
---查詢出emp表中所有員工入職距離現在幾月
select months_between(sysdate, e.hiredate) from emp e;
---查詢出emp表中所有員工入職距離現在幾年
select months_between(sysdate, e.hiredate)/12 from emp e;
---查詢出emp表中所有員工入職距離現在幾周
select round((sysdate-e.hiredate)/7) from emp e;

轉換函式

日期轉換成字串

注意：fm表示如果是"04"格式前面會自動省略0，展示為"4"；

---轉換函式
select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual;

字串轉日期

---字串轉日期
select to_date('2018-6-7 16:39:50', 'fm yyyy-mm-dd hh24:mi:ss') from dual;

多行函式（聚合函式）

---多行函式【聚合函式】：作用于多行，回傳一個值，
select count(1) from emp;   ---查詢總數量
select sum(sal) from emp;   ---工資總和
select max(sal) from emp;   ---最大工資
select min(sal) from emp;   ---最低工資
select avg(sal) from emp;   ---平均工資

7.2.通用函式

--- 通用函式
--- 算出emp表中所有員工的年薪
--- 獎金里面有null值，如果null值和任意數字做算術運算，結果都是null
select e.sal*12+nvl(e.comm, 0) from emp e;

計算結果：

7.3.條件運算式

以下兩種運算式為mysql、oracle通用運算式：

第一種：

---條件運算式
---給emp表中員工起中文名
select e.ename,
       case e.ename
           when 'SMITH' then '曹賊'
           when 'ALLEN' then '大耳賊'
           when 'WARD'  then '諸葛小兒'
           else '無名'  --- 注意：如果else不寫則為null
           end
from emp e;

第二種：

---判斷emp表中員工工資，如果高于3000顯示高收入；如果高于1500低于3000顯示中等收入；其余顯示低收入
select e.sal,
    case
     when e.sal>3000 then '高收入'
     when e.sal>1500 then '中等收入'
     else '低收入'
    end
from emp e;

以下寫法為oracle中專用條件運算式：

其中"中文名"為別名，別名使用雙引號，其余都用單引號，

---oracle專用條件運算式
select e.ename,
    decode(e.ename,
          'SMITH', '曹賊',
          'ALLEN', '大耳賊',
          'WARD', '諸葛小兒',
          '無名') 中文名
from emp e;

7.4.聚合函式

---分組查詢
---查詢出每個部門的平均工資
---分組查詢中，出現在group by 后面的原始列，才能出現在select后面
---沒有出現在group by后面的列，想在select后面，必須加上聚合函式
---聚合函式有一個特性，可以把多行記錄變成一個值
select e.deptno, avg(e.sal)
from emp e
group by e.deptno;

別名問題：

---查詢出平均工資高于2000的部門資訊
select e.deptno, avg(e.sal) asal
from emp e
group by e.deptno
having avg(e.sal)>2000;
---所有條件都不能使用別名來判斷
---比如下面的條件陳述句也不能使用別名當條件
select ename, sal s from emp where sal>1500;

分組group by與having問題：

---where是過濾分組前的資料，having是過濾分組后的資料
---表現形式：where必須在group by之前，having是在group by之后
---查詢出每個部門工資高于800的員工的平均工資
---然后再查詢出平均工資高于2000的部門
select e.deptno, avg(e.sal) asal
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;

7.5.多表連接查詢

left join、right join、inner join

oracle中特有的連接方式，(+)在的地方表示right join等同

---oracle中專用外連接
select *
from emp e, dept d
where e.deptno(+) = d.deptno;

7.6.自連接查詢

---查詢出員工姓名，員工領導姓名
---自連接：自連接其實就是站在不同的角度把一張表看成多張表
select e1.ename, e2.ename
from emp e1, emp e2
where e1.mgr = e2.empno;

7.7.分頁查詢

---rownum行號：當我們做select操作的時候，
---沒查詢出一行記錄，就會在該行上加上一個行號，
---行號從1開始，依次遞增，不能跳著走
---emp表工資倒敘排列后，每頁五條記錄，查詢第二頁
---排序操作會影響rownum的順序
select rownum, e.* from emp e order by e.sal desc
---如果涉及到排序，但是還要使用rownum的話，我們可以再次嵌套查詢
select rownum, t.* from(
    select rownum, e.* from emp e order by e.sal desc) t;

分頁查詢固定格式：

---emp表工資倒敘排列后，每頁五條記錄，查詢第二頁
---rownum行號不能寫上大于一個正數
select * from(
    select rownum rn, tt.* from(
        select * from emp order by sal desc  ---這里可以寫需要進行分頁的資料
    ) tt where rownum < 11
) where rn > 5;

8.Oracle視圖

視圖：視圖就是提供一個查詢的視窗，所有資料來自于原表，

-- 查詢陳述句創建表
create table emp as select * from scott.emp;
select * from emp;
-- 創建視圖【必須有dba權限】
create view v_emp as select ename, job from emp;
-- 查詢視圖
select * from v_emp;
-- 修改視圖[不推薦]
update v_emp set job ='CLERK' where ename = 'ALLEN';
commit;
-- 創建只讀視圖
create view v_emp as select ename, job from emp with read only;

視圖的作用：

第一：視圖可以屏蔽掉一些敏感欄位；

第二：保證總部和分部資料及時統一；

9.Oracle索引

索引：索引就是在表的列上構建一個二叉樹，達到大幅度提高查詢效率的目的，但是索引會影響增刪改的效率，

索引分為：單列索引與復合索引，

-- 創建單列索引
create index idx_ename on emp(ename);
-- 單列索引觸發規則，條件必須是索引列中的原始值
-- 單行函式，模糊查詢，都會影響索引的觸發
select * from emp where ename = "SOCTT";
-- 復合索引
-- 創建復合索引
create index idx_enamejob on emp(ename, job);
-- 復合索引中第一列為優先檢索列
-- 如果要觸發復合索引，必須包含有優先檢索列中的原始值
select * from emp where ename = 'SCOTT' and job = 'XX';  -- 觸發復合索引
select * from emp where ename = 'SCOTT' or job = 'XX';  -- 不觸發索引
select * from emp where ename = 'SCOTT';  -- 觸發單列索引

創建結果如下：

10.PL/SQL編程語言

pl/sql編程語言是對sql語言的擴展，使得sql語言具有程序化編程的特性，

pl/sql編程語言比一般的程序化編程語言，更加靈活高效，

pl/sql編程語言主要用來撰寫存盤程序和存盤函式等，

10.1.pl/sql編程基礎語法

--- 宣告方法
--- 賦值操作可以使用:=也可以使用into查詢陳述句賦值
declare
    i number(2) := 10;
    s varchar2(10) := '小明';
    ena emp.ename%type; ---參考型變數
    emprow emp%rowtype; ---記錄型變數
begin
    dbms_output.put_line(i);
    dbms_output.put_line(s);
    select ename into ena from emp where empno = 7788;
    dbms_output.put_line(ena);
    select * into emprow from emp where empno = 7788;
    dbms_output.put_line(emprow.ename || '的作業為：' || emprow.job)
end;

10.2.pl/sql中的if判斷

--- 輸入小于18的數字，輸出未成年
--- 輸入大于18小于40的數字，輸出中年人
--- 輸入大于40的數字，輸出老年人
declare
    i number(3) := &ii;
begin
    if i<18 then
        dbms_output.put_line('未成年');
    elsif i<40 then
        dbms_output.put_line('中年人');
    else
        dbms_output.put_line('老年人');
    end if;
end;

10.3.pl/sql中的loop回圈

--- pl/sql中的loop回圈
--- 用三種方式輸出1到10是個數字
--- while回圈
declare
    i number(2) := 1;   --- 定義變數并賦值
begin
    while i<11 loop
        dbms_output.put_line(i);
        i := i+1;
    end loop
end;
--- exit回圈
declare 
    i number(2) := 1;
begin
  loop
    exit when i > 10;
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;
--- for回圈
declare 

begin
  for i in 1..10 loop
      dbms_output.put_line(i);
  end loop;
end;

10.4.pl/sql游標

游標：可以存放多個物件，多行記錄，

--- 輸出emp表中所有員工的姓名
declare
    cursor c1 is select * from emp;
    emprow emp%rowtype;
begin
    open c1;
        loop
            fetch c1 into emprow;
            exit when c1%notfound;
            dbms_output.put_line(emprow.ename);
        end loop;
    close c1;
end;

--- 給指定部門員工漲工資
declare
    cursor c2(eno emp.deptno%type) 
    is select empno from emp where deptno = eno;
    en emp.empno%type;
begin
    open c2(10);
        loop
            fetch c2 into en;
            exit when c2%notfound;
            update emp set sal=sal+100 where empno = en;
            commit;
        end loop;
    close c2;
end;

10.5.存盤程序

存盤程序：存盤程序就是提前已經編譯好的一段pl/sql語言，放置在資料庫中，

可以被直接呼叫，這一段pl/sql一般都是固定步驟的業務，

存盤程序的創建

--- 給指定員工漲100塊錢
--- 使用or replace
create or replace procedure p1(eno emp.empno%type)
is

begin
    update emp set sal=sal+100 where empno = eno;
    commit;
end;

--- 測驗p1
declare

begin
    p1(7788);
end;

--- 通過存盤函式實作計算指定員工的年薪
--- 存盤程序和存盤函式的引數都不能帶長度
--- 存盤函式的回傳值型別不能帶長度
create or replace function f_yearsal(eno emp.empno%type) return number
is
    s number(10);
begin
    select sal*12+nvl(comm, 0) into s from emp where empno = eno;
    return s;
end;

---測驗f_yearsql
---存盤函式在呼叫的時候，回傳值需要接收
declare
    s number(10);
begin
    s:= f_yearsal(7788);
    dbms_output.put_line(s);
end;

---out型別引數如何使用 out型別引數使用：
---使用存盤程序來算年薪
create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number)
is
    s number(10);
    c emp.comm%type;
begin
    select sal*12, nvl(comm, 0) into s, c from emp where empno = eno;
    yearsal := s+c;
end;

---測驗p_yearsal
declare
    yearsal number(10);
begin
    p_yearsal(7788, yearsal);
    dbms_output.put_line(yearsal);
end;

---in和out型別引數的區別是什么？
---凡是涉及到into查詢陳述句賦值或者:=賦值操作的引數，都必須使用out來修飾

存盤程序和存盤函式的區別：

語法區別：關鍵字不一樣；存盤函式比存盤程序多了兩個return

本質區別：存盤函式有回傳值，而存盤程序沒有回傳值，

如果存盤程序想實作有回傳值的業務，我們就必須使用out型別的引數，即便是存盤程序使用了out型別的引數，其本質也不是真的有了回傳值，而是在存盤程序內部給out型別引數賦值，在執行完畢后，我們直接拿到輸出型別引數的值，

我們可以使用存盤函式有回傳值的特性，來自定義函式，

而存盤程序不能用來自定義函式，

--- 案例需求：查詢出員工姓名，員工所在部門名稱

--- 案例準備作業：把scott用戶下的dept表復制到當前用戶下
create table dept as select * from scott.dept;
--- 使用傳統方式來實作案例需求
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;

---使用存盤函式來實作提供一個部門編號，輸出一個部門名稱
create or replace function fdna(dno dept.deptno%type) return dept.dname%type
is
    dna dept.dname%type;
begin
    select dname into dna from dept where deptno = dno;
    return dna;
end;
--使用fdna存盤函式來實作案例需求：查詢出員工姓名，員工所在部門名稱
select e.ename, fdna(e.deptno)
from emp e;

10.6.觸發器

觸發器：就是指定一個規則，在我們做增刪改操作的時候，主要滿足該規則，自動觸發，無需呼叫，

觸發器分為：陳述句級觸發器、行級觸發器

陳述句級觸發器：不包含有for each row的觸發器，

行級觸發器：包含有for each row的行級觸發器，

加for each row是為了使用：old或者:new物件或者一行記錄，

--- 陳述句級觸發器
--- 插入一條記錄，輸出一個新員工入職
create or replace trigger t1
after
insert
on person
declare 

begin
    dbms_output.put_line('一個新員工入職');
end;
---觸發t1
insert into person values (1, '小紅');
commit;
select * from person;

--- 行級觸發器
--- 不能給員工降薪
--- raise_application_error(-20001~20999之間, '錯誤提示資訊');
create or replace trigger t2
before 
update
on emp
for each row
declare

begin
    if :old.sal > :new.sal then
        raise_application_error(-20001, '不能給員工降薪');
    end if;
end;
---觸發t2
select * from emp where empno = 7788;
update emp set sal=sal-1 where empno = 7788;
commit;

觸發器實作主鍵自增：

用到的是行級觸發器

--- 分析：在用戶做插入操作之前，拿到即將插入的資料，給該資料中的主鍵列賦值
create or replace trigger auid
before
insert
on person
for each row
declare

begin
    select s_person.nextval into :new.pid from dual;
end;
---查詢person表資料
select * from person;
---使用auid實作主鍵自增
insert into person (pname) values ('a');
commit;

11.Java呼叫存盤程序

11.1.Java呼叫Oracle存盤程序環境準備

oracle10g使用ojdbc14.jar包；

oracle11g使用ojdbc6.jar包

1.創建Maven工程，匯入以下依賴jar包：

<dependencies>
    <dependency>
        <groupId>com.oralce</groupId>
        <artifactId>ojdbc14</artifactId>
        <version>10.2.0.4.0</version>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.10</version>
        <scope>test</scope>
    </dependency>
</dependencies>

11.2.呼叫存盤程序代碼實作

jdbc.properties配置：

String driverName = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@192.168.79.10:1521:orcl";
String username = "scott";
String password = "tiger";

1.執行查詢操作：

 @Test
public void javaCallOracle() throws Exception {
    //加載資料庫驅動
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //得到Connection連接
    Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "scott", "tiger");
    //得到預編譯的Statement物件
    PreparedStatement pstm = connection.prepareStatement("select * from emp where empno = ?");
    //給引數賦值
    pstm.setObject(1, 7788);
    //執行資料庫查詢操作
    ResultSet rs = pstm.executeQuery();
    //輸出結果
    while(rs.next()){
        System.out.println(rs.getString("ename"));
    }
    //釋放資源
    rs.close();
    pstm.close();
    connection.close();
}

2.java呼叫存盤程序：

/**
  * java呼叫存盤程序
  * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}   呼叫存盤函式使用
  *  {call <procedure-name>[(<arg1>,<arg2>, ...)]}   呼叫存盤程序使用
  * @throws Exception
  */
@Test
public void javaCallProcedure() throws Exception {
    //加載資料庫驅動
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //得到Connection連接
    Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "scott", "tiger");
    //得到預編譯的Statement物件
    CallableStatement pstm = connection.prepareCall("{call p_yearsal(?, ?)}");
    //給引數賦值
    pstm.setObject(1, 7788);
    pstm.registerOutParameter(2, OracleTypes.NUMBER);
    //執行資料庫查詢操作
    pstm.execute();
    //輸出結果[第二個引數]
    System.out.println(pstm.getObject(2));
    //釋放資源
    pstm.close();
    connection.close();
}

3.java呼叫存盤函式

 /**
   * java呼叫存盤函式
   * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}   呼叫存盤函式使用
   *  {call <procedure-name>[(<arg1>,<arg2>, ...)]}   呼叫存盤程序使用
   * @throws Exception
   */
@Test
public void javaCallFunction() throws Exception {
    //加載資料庫驅動
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //得到Connection連接
    Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "scott", "tiger");
    //得到預編譯的Statement物件
    CallableStatement pstm = connection.prepareCall("{?= call f_yearsal(?)}");
    //給引數賦值
    pstm.setObject(2, 7788);
    pstm.registerOutParameter(1, OracleTypes.NUMBER);
    //執行資料庫查詢操作
    pstm.execute();
    //輸出結果[第一個引數]
    System.out.println(pstm.getObject(1));
    //釋放資源
    pstm.close();
    connection.close();
}

本博客相關檔案筆記均已上傳至GitHub地址：

OracleDBReview

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/283132.html

標籤：其他

上一篇：【資料庫】Oracle -- 一文了解Oracle資料庫開發知識地圖

下一篇：怎么樣ps工具磨皮