Oracle資料庫入門(使用參考)
零、簡介
Oracle Database，又名Oracle RDBMS，或簡稱Oracle，是甲骨文公司的一款關系資料庫管理系統，它是在資料庫領域一直處于領先地位的產品，可以說Oracle資料庫系統是目前世界上流行的關系資料庫管理系統，系統可移植性好、使用方便、功能強，適用于各類大、中、小微機環境，它是一種高效率的、可靠性好的、適應高吞吐量的資料庫方案，
ORACLE資料庫系統是美國ORACLE公司（甲骨文）提供的以分布式資料庫為核心的一組軟體產品，是目前最流行的客戶/服務器(CLIENT/SERVER)或B/S體系結構的資料庫之一，比如SilverStream就是基于資料庫的一種中間件，ORACLE資料庫是目前世界上使用最為廣泛的資料庫管理系統，作為一個通用的資料庫系統，它具有完整的資料管理功能；作為一個關系資料庫，它是一個完備關系的產品；作為分布式資料庫它實作了分布式處理功能，但它的所有知識，只要在一種機型上學習了ORACLE知識，便能在各種型別的機器上使用它，
一、與mysql對比
內容挺多的，請參考這篇融合怪文章 https://blog.csdn.net/qq_44769485/article/details/114460712
一、Oracle基礎和表的CURD
1.表空間以及用戶的創建-用戶的授權
-- 創建表空間
create tablespace oracletest -- 創建表空間
datafile 'E:\oracle\tablespace\oracletest.dbf' -- 把表空間放在那，這里要指定路徑名以及檔案名，必須以.dbf結尾
size 100m -- 設定初始容量
autoextend on -- 設定自動擴容
next 10m; -- 每次擴容10兆
-- 洗掉表空間
drop tablespace oracletest;
-- 創建用戶
create user oracletest -- 創建用戶
identified by oracletest -- 設定密碼
default tablespace oracletest; -- 設定用戶出生位置，出生在那個表空間
-- 給用戶授權
-- oracle資料庫中常用角色
connect -- 連接角色 ，基本角色
resource -- 開發者角色
dba -- 超級管理員角色
--給oracletest用戶授予dba角色
grant dba to oracletest;
--切換到oracletest用戶
2.資料型別與表的創建
-- 創建一個person表
-- 資料型別
-- Varchar,varchar2 表示一個字串，可自動縮容，但不能自動擴容
-- char 不可變字符型別
-- NUMBER NUMBER(n)表示一個整數，長度是n 例: number(2) 這就是0到99
-- NUMBER(m,n)表示一個小數，總長度是m,小數是n,整數是m-n
-- DATE 表示日期型別
-- CLOB 大物件,表示大文本資料型別，可存4G
-- BLOB 大物件,表示二進制資料,可存4G
create table person(
pid number(20),
pname varchar2(10)
);
3.修改表結構
-- 修改表結構
-- 添加一列
alter table person add (sex number(1),money number(4));
-- 修改列型別 modify(修改)
alter table person modify sex char(1);
-- 修改列名稱 column(列)
alter table person rename column sex to gander;
-- 洗掉一列
alter table person drop column money;
4.資料的增刪改
-- 資料的增刪改
-- 資料的查詢
select * from person;
-- 添加一條記錄
insert into person(pid,pname,sex) values(1,'小明','男');
commit;
-- 修改一條資料
update person set pname='小馬' where pid=1;
commit;
-- 洗掉記錄
-- 洗掉表中全部記錄
delete from person;
-- 洗掉表結構
drop table person;
-- 先洗掉表,再創建表.效果等同于洗掉表中全部記錄 truncate(截斷=洗掉)
-- 在資料量大的情況下，尤其在表中帶有索引的情況下，該操作效率高
-- 索引可以提供查詢效率，但是會影響增刪改的效率
truncate table person;
5.序列的使用
-- 序列的使用
-- 序列：默認從1開始，一次遞增，主要用來給主鍵賦值使用
-- 序列不真的屬于任何一張表，但是可以邏輯和表做系結
-- dual：虛表，只為了補全語法，沒有任何意義
create sequence s_person;
select s_person.nextval from dual;
-- 序列的使用:添加一條記錄(設定主鍵增長)
insert into person values(s_person.nextval,'小王','男');
commit;
select * from person;
6.scott用戶解鎖
-- scott用戶,密碼默認是tiger
-- 解鎖scott用戶
alter user scott account unlock;
-- 解鎖scott的密碼[此句也可以用來重置密碼]
alter user scott identified by tiger;
-- 切換到scott用戶
select * from emp;
7.單行函式
-- 單行函式：作用于一行，回傳一個值
-- 字符函式
select upper('yes') from dual; --小寫變大寫
select lower('YES') from dual; --大寫變小寫
-- 數值函式
select round(26.14,1) from dual;-- 四舍五入，后面引數表示保留的位數，-1表示往前保留一位(試試就知道了)
select trunc(26.14,1) from dual;-- 直接截取，保留小數點后一位，再后面的直接截取掉
select mod(10,3) from dual;-- 取余
-- 日期函式
select sysdate-e.hiredate from emp e;-- 查詢出emp表中所有員工入職距離現在幾天
select sysdate+1 from dual;--獲取明天這個時候的時間
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;-- 查詢出emp表中所有員工入職距離現在幾周
-- 轉換函式
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;--當前日期轉換成字串,fm表示不帶0開頭，24表示用24小時計時法
select to_date('2020-8-12 11:45:32','fm yyyy-mm-dd hh24:mi:ss') from dual;-- 字串轉日期
-- 通用函式
-- null值和任意數字運算結果都為null
-- nvl(e.comm,0) 如果值為null，則換成0
select e.sal+nvl(e.comm,0) from emp e;-- 算出emp表中所有員工的年薪+獎金
8.條件運算式
-- 條件運算式
--下面兩個是mysql和oracle都統用的條件運算式
-- 給emp表中員工起中文名稱
select e.ename, -- 等值判斷用這種寫法
case e.ename
when 'SMITH' then '曹賊'
when 'ALLEN' then '大兒子'
when 'WARD' then '諸葛小兒'
else '無名'
end
from emp e;
--判斷emp表中員工工資，如果高于3000顯示高收入，如果高于1500低于3000顯示中收入，低于1500顯示低收入，其余都是低收入
select e.sal,-- 范圍判斷用這種寫法
case -- 這就類似一個switsh
when e.sal>300 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;
9.多行函式
-- 多行函式【聚合函式】：作用于多行，回傳一個值
select count(1) from emp;--查詢總數量 這里的1等于emp表的主鍵，推薦寫1
select sum(sal) from emp;--求和 求所有員工工資綜合
select max(sal) from emp;--求最大 求工資最大的值
select min(sal) from emp;--求最小 求工資最小的值
select avg(sal) from emp;--求平均 求工資平均值
10.分組查詢
-- 分組查詢 【重要】
--分組查詢中，出現再group by后面的原始列，才能出現在select后面
--沒有出現再group by后面的列，想在select后面，必須加上聚合函式
--聚合函式有一個特性，可以把多行記錄編程一個值
--所有條件都不能使用別名來判斷
--where執行先于select
-- 查詢出每個部門的平均工資
select e.deptno,avg(e.sal) --e.ename
from emp e
group by e.deptno;--分組依據
--查詢出平均工資高于2000的部門資訊
select e.deptno,avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal)>2000;--判斷工資大于2000
--查詢出每個部門工資高于800的員工的平均工資
select e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by e.deptno;
--【重要】where是過濾分組前的資料，having是過濾分組后的資料
-- 表現形式：where在group by之前,having在group by之后
--查詢出每個部門工資高于800的員工的平均工資
--然后再查詢出平均工資高于2000的部門
select e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;
11.多表查詢的一些概念
-- 多表查詢中的一些概念
--笛卡爾積
--兩張表的資料量進行相乘
select *
from emp,dept;
--等值連接
select *
from emp e,dept d
where e.deptno=d.deptno;
--內鏈接(這是sql最開始的等值連接寫法，推薦使用等值連接)
select *
from emp e inner join dept d
on e.deptno=d.deptno;
--查詢中所有部門，以及部門下的員工資訊，【外連接】
select * -- 右外查詢 right
from emp e right join dept d
on e.deptno=d.deptno;
--查詢所有員工資訊，以及員工所屬部門
select * -- 左外查詢 left
from emp e left join dept d
on e.deptno=d.deptno;
--oracle中專用的外連接
select * from emp e,dept d where e.deptno(+)=d.deptno; --(+)放在那邊，他的對面的全部資訊，和(+)邊的符合條件的資料
12.子鏈接概念和練習
--查詢出員工姓名，員工領導姓名
select e1.ename,e2.ename -- 自鏈接：自聯結其實就是站在不同的角色把一張表看成多張表
from emp e1,emp e2
where e1.mgr=e2.empno;
--查詢出員工部門名稱和員工領導名稱和部門名稱
select e1.ename,e2.ename,d.dname
from emp e1,emp e2,dept d
where e1.mgr=e2.empno
and e2.deptno=d.deptno
;
-- 查詢出每個員工編號，姓名，部門名稱，工資等級和他的上級領導的姓名，工資等級
select e1.empno,e1.ename,d.dname,
case
when e1.sal>3000 then '高收入'
when e1.sal>1500 then '中收入'
when e1.sal<1500 then '低收入'
else '低收入'
end ,d.dname,case
when e2.sal>3000 then '高收入'
when e2.sal>1500 then '中收入'
when e2.sal<1500 then '低收入'
else '低收入'
end
from emp e1,emp e2,dept d
where e1.mgr=e2.empno and e1.deptno=d.deptno ;
13.分頁查詢
-- 分頁查詢
--rownum行號:當我們做select操作得時候
--每查詢一行記錄，就會在該行上加一個行號
--行號從1開始，一次遞增，不能跳著走 【重要】
--排序操作會影響rownum得順序
--如果涉及到排序，但是還要使用rownum得話，我們可以再次嵌套查詢
--倒序查詢并加上第二次查詢得行號
select rownum,t.* from (select * from emp e order by e.sal desc) t; -- order by 排序操作，默認正序
--emp表工資倒敘排列后，每頁五條記錄，查詢第二頁
--rownum這個不能被別的表.(點)這個是個獨立的東西，不算在表里 【重要】
select *
from(
select rownum r,t.*
from (
select *
from emp
order by sal desc
) t
where rownum<11
)
where r>5;
二、Oracle加強部分
1.視圖
--視圖
--視圖的概念：視圖就是提供一個查詢的視窗，所有資料來自于原表
--查詢陳述句創建表
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_emp1 as select * from emp with read only;
--視圖的作用？
--第一：視圖可以屏蔽一些敏感欄位
--第二：保證總部和分部資料及時統一
2.索引
-- 索引
--索引的概念：索引就是再表的列上構建一個二叉樹
--達到大幅度提高查詢效率的目的，但是索引會影響增刪改的效率
--單列索引
--創建單列索引
create index idx_ename on emp(ename);-- 創建一個索引 idx_ename 作用在 emp表的 ename屬性上
--單列索引觸發規則，條件必須是索引列中的原始值
--單行函式，模糊查詢，都會影響索引的觸發
select * from emp where ename='SCOTT';--觸發單行索引
--復合索引
--創建復合索引
create index idx_enamejob on emp(ename,job);--ename為優先索引列
--復合索引中第一列為有先索引列
--如果要觸發復合索引，必須包含有優先檢索列中的原始值
select * from emp where ename='SCOTT' and job='xx';--觸發復合索引
select * from emp where ename='SCOTT' or job='xx';--不觸發索引
select * from emp where ename='SCOTT';--如果存在單列索引和多列索引，那么這種情況就觸發單列索引
3.PLSQL語言定義變數
--pl/sql編程語言
--pl/sql編程語言是對sql語言的擴展，使得sql語言具有程序化編程的特性
--pl/sql編程語言比一般的程序化編程語言，更加靈活高效
--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;-- 查詢emp表中 empno為7788的用戶的名字，將名字欄位的屬性和值都賦值給 ena
dbms_output.put_line(ena);
select * into emprow from emp where empno = 7788;-- 查詢emp表中 empno為7788的用戶的資訊列，將這一行資訊封裝給emprow，現在emprow相當于一行資料
dbms_output.put_line(emprow.ename || '的作業為' || emprow.job);
end;
4.PLSQL中的if判斷
--pl/sql中的if判斷
--輸入大于18的數字，判斷為未成年
--輸入大于18小于40，判斷為中年人
--輸入大于50 ，判斷為老年人
declare
i number(2):=&年齡;
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;
5.PLSQL中的回圈
--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;--注意這個when(什么時候)
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;
6.PLSQL中的游標
--游標：可以存放多個物件，多行記錄 (類似于java里面的集合)
--輸出emp表中所有員工的姓名
declare
cursor c1 is select * from emp;--將整個emp表裝進c1游標中
emprow emp%rowtype;--創建一個記錄型變數，變數型別為emp中的一行資料
begin
open c1;--開啟游標
loop
fetch c1 into emprow;--個人感覺像一個foreach，只不過是放判斷前面 fetch(取)
exit when c1%notfound;--當c1查不到東西的時候退出查詢
dbms_output.put_line(emprow.ename);--這個記錄型變數一定要點一個屬性，他不能當作一行資料來查
end loop;
close c1;--關閉游標
end;
--給指定的部門員工漲工資 (我感覺不用寫的這麼麻煩，只用一個where判斷不就完了)
select * from emp where deptno=10;
declare
cursor c2(eno emp.deptno%type) is select empno from emp where deptno=eno;--創建一個有引數的游標，將游標賦值where條件為deptno=傳入的指定引數
en emp.empno%type;--用于接受員工號碼
begin
open c2(10);--開啟游標并傳參，這個因為是含參游標，所以必須傳引數
loop
fetch c2 into en;--將c2的資料foreach到en中(每個回圈都類似一次重新賦值)
exit when c2%notfound;--退出條件為c2找不到東西為止
update emp set sal=sal+100 where empno=en;--將部門id等于c2的引數的員工的工資加100
commit;--提交事務
end loop;
close c2;--關閉就不必傳參了
end;
7.儲存程序
--存盤程序：只是類似于java的方法，這里叫程序
--存盤程序：春促程序就是提前已經編譯高的一段pl/sql語言，防止再資料庫端
----可以直接被呼叫，這一段pl/sql一般都是固定步驟的業務
--給指定員工工資漲100塊錢
--創建 可修改 方法 方法名 (引數名 in/out 引數型別)
create or replace procedure p1(eno emp.empno%type) -- or replace 這個加上之后可以修改錯誤(就是允許替換方法內容)
is
begin
update emp set sal=sal+100 where empno=eno; --方法呼叫后會執行這里面的方法
commit;
end;
select * from emp where empno=7788;
declare
begin
p1(7788);--方法的呼叫
end;
8.儲存函式
--存盤函式
--通過儲存函式實作計算指定員工的年薪
--儲存程序和儲存函式的引數都不能帶長度
--儲存函式和回傳值型別不能帶長度
--創建 可修改的 存盤函式 函式名 (引數串列 默認in) 回傳值型別
create or replace function f_yearssal(eno emp.empno%type)return number--創建存盤函式
is
s number(10);--定義一個接識訓傳值的物件
begin
select sal*12+nvl(comm,0)into s from emp where empno=eno;-- 計算指定員工的年薪 nvl(comm,0)獎金 如果為null就用0代替，將查詢的值存入s物件中
return s; //回傳年薪
end;
declare
s number(10);--接收物件
begin
s := f_yearssal(7788);--執行方法，存盤函式的回傳值必須被接收
dbms_output.put_line(s);--列印回傳值
end;
9.out型別引數如何使用
--out型別引數如何使用
-- in和out型別引數的區別是什么？
-- 凡是涉及到into查詢陳述句賦值或者:=賦值操作的引數，都必須使用out來修飾
--使用儲存程序來計算年薪
create or replace procedure p_yearsal(eno emp.empno%type,yearsal out number)--out修飾的引數在呼叫程序時不用傳參，只需要設定一個物件取接收即可
is
s emp.sal%type;--接收年工資
c emp.comm%type;--接收獎金
begin
select sal*12,nvl(comm,0) into s,c from emp where empno=eno;--查詢年工資賦值給s 查詢獎金并對null做化0處理然后賦值給c
yearsal:=s+c;--對out修飾的引數進行傳值(類似于回傳值)
end;
declare
yearsal number(10);--接收out修飾物件傳來的值，這個引數名不必須要和程序默認引數名一樣
begin
p_yearsal(7788,yearsal);--呼叫程序
dbms_output.put_line(yearsal);--列印接收的out引數
end;
10.儲存程序和儲存函式的區別
--儲存程序和儲存函式的區別
--語法區別：關鍵字不一樣
-----------存盤函式比存盤程序多了兩個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 d.dname
into dna
from dept d
where d.deptno=dno;
return dna;
end;
--使用fdna存盤函式來實作案例需求：查詢出員工姓名，員工所在部門名稱
select e.ename,fdna(e.deptno)
from emp e;
11.觸發器
--觸發器：就是制定一個規則，在我們做增刪改操作的時候
---只要滿足該規則，自動觸發，無需呼叫
---陳述句級觸發器：不包含有for each row的觸發器
---行級觸發器：包含有for each row的就是行級觸發器
-------------------加for each row是為了使用:old或者:new物件或者一行記錄
--陳述句級觸發器
---插入一條資料，輸出一個新員工入職
create or replace trigger t1 --創建陳述句級觸發器
after--在陳述句執行之后執行
insert--在insert陳述句執行這個觸發器
on person--觸發器作用于person表
declare
begin
dbms_output.put_line('有一位新員工加入');--觸發器觸發列印這段文字
end;
select * from person;
insert into person values(1,'小紅','女');--插入操作
commit;
--行級觸發器
--不能給員工降薪
create or replace trigger t2 -- 創建行級觸發器
before--在陳述句執行之后執行
update--觸發器作用在更新陳述句
on emp--觸發器作用于emp表
for each row--行級觸發器需要加這段，方便使用:old和:new (修改之前的舊物件，和修改之后的新物件)
declare
begin
if :old.sal>:new.sal then--判斷如果工資降了觸發下列例外
raise_application_error(-20001,'不能給員工降薪');-- raise_application_error(-20001~20999之間,'錯誤提示資訊');
end if;
end;
update emp set sal=sal-1 where emp.empno=7788;--更新操作
commit;
select * from emp;
12.觸發器實作主鍵的自增
--觸發器實作主鍵自增，【行級觸發器】
--分析：在用戶做插入操作的之前，拿到即將插入的資料
-----給該資料中的主鍵列復制
create or replace trigger auid--創建行級觸發器
before --在陳述句執行之后執行
insert --作用在插入陳述句
on person --作用在person表
for each row --添加這一段
declare
begin
select s_person.nextval into :new.pid from dual; --陳述句執行之前執行傳序列下一個的值，然后付給修改后的物件的pid
end;
select s_person.nextval from dual;
select * from person;
insert into person values(1,'小蘭','男');--這里無論pid傳什么都對自增長沒有影響
commit;
之前學的時候的一些筆記，歡迎參考
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/267108.html
標籤：其他
上一篇：Mysql資料庫知識總結
下一篇：MySQL資料優化的方式總結