oracle數(shù)據(jù)庫——知識點(diǎn)總結(jié)（加示例）

新入oracle數(shù)據(jù)庫，把目前學(xué)到的知識點(diǎn)記錄下來，可能都比較基礎(chǔ)，但還是比較全的，里面的示例都是自己在PL/SQL中跑過的，如果有錯(cuò)誤，還望各位大俠指出哈。

創(chuàng)建用戶

1.創(chuàng)建用戶（使用管理員身份創(chuàng)建，即用system進(jìn)行登錄）

create user rick identified by ric account unlock;
--創(chuàng)建一個(gè)用戶名是rick的用戶，密碼是ric，用戶狀態(tài)未鎖定

2.上訴創(chuàng)建后，如果直接用設(shè)置的用戶名和密碼進(jìn)行登錄，則會(huì)提示下面的錯(cuò)誤


沒有權(quán)限.png

3.可以理解為用戶沒有會(huì)話權(quán)限，無法進(jìn)行數(shù)據(jù)庫登錄。則，接下來給予用戶rick基本的connect權(quán)限

grant connect to rick; --將connect權(quán)限給予rick

4.這次rick可以作為normal用戶登錄（如果選擇dba身份，則需要更高的權(quán)限）
5.重新使用system登錄數(shù)據(jù)庫，更改rick的信息

alter user rick account lock; --將rick的狀態(tài)設(shè)置為鎖定

6.再次以rick來登錄，則會(huì)提示下面錯(cuò)誤（一開始在創(chuàng)建用戶時(shí)如果不設(shè)置狀態(tài)為unlock，則系統(tǒng)會(huì)默認(rèn)為lock狀態(tài)）


locked.png

創(chuàng)建表及約束

create table infos
(
  stuid varchar2(7) not null,
  age number(2) not null
)
alter table infos add constraint infos_check --給infos表加一個(gè)約束，限制age在20到60之間
check(age>20 and age<60)

根據(jù)結(jié)果集創(chuàng)建表

create table infos2 as select * from infos;  --會(huì)把數(shù)據(jù)一起復(fù)制到新表中

create table infos3 as select * from infos where 1=2;
 --后面加了一個(gè)where條件，這個(gè)條件不成立，則只會(huì)復(fù)制表結(jié)構(gòu)，不包括數(shù)據(jù)

插入數(shù)據(jù)時(shí)，如果表中字段是date類型，需要先使用to_date('2012-12-29','YYYY-MM-DD')將數(shù)據(jù)轉(zhuǎn)換成date類型

當(dāng)兩個(gè)表的結(jié)構(gòu)全部相同時(shí)，可以直接插入結(jié)果集

insert into infos3 select * from infos;

清空表中的數(shù)據(jù)（不可恢復(fù)）

turncate table infos;

字符串拼接

select (ename||' is a '||job) as "employee detail" from emp; 
--括號內(nèi)的內(nèi)容會(huì)在一列中顯示出來

消除重復(fù)項(xiàng)

select distinct deptno from emp;

in操作查詢指定列表的值

select ename,job from emp where job in ('salesman','president','analyst');
--只要job是salesman、president、analyst中任意一個(gè)即滿足條件

between……and操作查詢范圍內(nèi)的數(shù)據(jù)

select ename,job from emp where sal between 1000 and 2000; --包括邊界

like模糊查詢

  • % 表示零個(gè)或多個(gè)任意字符
  • _ 表示一個(gè)任意字符
  • 如果查詢條件中有特殊字符，則使用[ ]括起來

集合運(yùn)算

  • intersect （交集） 返回兩個(gè)查詢共有的記錄
  • union all（并集） 返回各個(gè)查詢的所有記錄，包括重復(fù)記錄
  • union（并集） 返回各個(gè)查詢的所有記錄，不包括重復(fù)記錄
  • minus（補(bǔ)集） 返回第一個(gè)查詢的記錄減去第二個(gè)查詢的記錄后剩余的記錄

使用union實(shí)現(xiàn)多條數(shù)據(jù)插入

insert into dept
select 50,'公關(guān)部','武漢' from dual
union
select 60,'研發(fā)部','重慶' from dual
union
select 70,'培訓(xùn)部','四川' from dual

其中dual是一個(gè)oracle自帶的特殊的表，使用只是為了符合select的查詢結(jié)構(gòu)，它只會(huì)返回一行記錄

連接查詢

  • 內(nèi)連接：join……on 兩個(gè)表中只有滿足條件的才會(huì)被查詢出來
  • 左外連接：left join ……on 左邊的表不受限制，右邊的表需要滿足條件
  • 右外連接：right join……on 右邊的表不受限制，左邊的表需要滿足條件
  • 全外連接：full join……on 左右兩張表都不受限制

ANY子查詢（只要滿足ANY中的一個(gè)即可）

select ename,job from emp where sal<any(select sal from emp where job='salesman'); 
--查詢出比任意一個(gè)銷售的工資低的員工姓名和工作

ALL子查詢（需要滿足all中的所有）

select ename,job from emp where sal>all(select sal from emp where job='salesman');
 --查詢出比所有銷售工資都高的員工的姓名和工作

偽列

  • ROWID：表中每一行在數(shù)據(jù)文件中都有一個(gè)物理地址，ROWID就是返回這個(gè)物理地址
  • ROWNUM：表中每行數(shù)據(jù)前面的序號
select t.* from (select ename,job sal from emp order by sal desc) t where rownum<5;
--查出工資排行前四的員工信息

select t.* from (select ename,job,rownum r from emp where rownum<10) t where r>5; 
--查詢第6到第9條數(shù)據(jù)
數(shù)學(xué)函數(shù).png

日期函數(shù)

  • ADD_MONTHS(d,n) 在日期d的基礎(chǔ)上加上n個(gè)月，返回新日期
  • LAST_DAY(d) 返回指定日期當(dāng)月的最后一天
  • ROUND(d,fmt) 返回一個(gè)以fmt為格式的四舍五入的日期值
  • EXTRACT(fmt FROM d) 從日期d中提取fmt部分（YEAR、MONTH……）
  • TO_CHAR(d,'YYYY-MM-DD HH24:MI:SS') 按照右邊的格式轉(zhuǎn)換日期成字符串，HH24表示是采用24小時(shí)制
  • TO_DATE(x, 'YYYY-MM-DD') 按照右邊的格式把字符串x轉(zhuǎn)換成對應(yīng)的date類型

其他

  • NVL(x,value) 如果x為空，則返回value，否則正常返回x
  • NVL2(x,value1,value2) 如果x非空，返回value1，否則返回value2
  • SUM()統(tǒng)計(jì)某一列中的數(shù)據(jù)的和，COUNT()統(tǒng)計(jì)有多少條數(shù)據(jù)（即行）

同義詞

create synonym myemp for scott.emp; --給scott用戶的emp表創(chuàng)建別名myemp

oracle可以為表、視圖、序列、過程、函數(shù)、程序包等指定別名，比如上面的，當(dāng)當(dāng)前用戶擁有權(quán)限時(shí)，訪問emp表，可以直接訪問myemp。而不需要使用scott.emp

創(chuàng)建視圖

create view view_name as select ……;  --將查詢到的結(jié)果保存成視圖

create view empdetail
as
select empno,ename,emp.deptnp,dname
from emp join dept on emp.deptno=dept.deptno;

上圖是將emp表與dept表聯(lián)合查詢后的結(jié)果保存成視圖，視圖相當(dāng)于一張新的表（虛表），可以直接來用

創(chuàng)建索引

create index ename_index on emp(ename);  --為emp表的ename創(chuàng)建索引

創(chuàng)建表空間 (目前不是很懂表空間)

create tablespace myspace
datafile 'd:/a.ora' size 10M;
         'd:/b.ora' size 5M;
extent management local  --指出表空間類型是：本地管理表空間
uniform size 1M;  --指定每個(gè)分區(qū)的統(tǒng)一大小

擴(kuò)充表空間

alter tablespace myspace
add datafile 'd:/c.ora' size 10M;

為表和索引指定表空間，只需要在其創(chuàng)建語句后面加上tablespace space_name即可，這種情況的表空間是不能修改的

PL/SQL塊

declare
  --聲明部分，如常量和變量
begin
  --執(zhí)行部分，這部分不能少
exception
  --異常處理部分
end;

如下例子

declare
   sname varchar2(20) default 'jerry'; --聲明變量sname并設(shè)置初始值
begin
   select ename into sname from emp where empno=9527; --將查詢到的值賦給sname變量
   dbms_output.put_line(sname);  --輸出sname
end;

還可以聲明宿主變量，該變量屬于全局變量，類似于web中的session，只要用戶沒退出，這個(gè)變量都可以使用

var emp_name varchar(30);
begin
select ename into :emp_name from emp where empno=9527;
end;

!! 注意在使用這種宿主變量時(shí)，需要在變量名前加：

屬性數(shù)據(jù)類型

  • %ROWTYPE 引用表中的一行作為數(shù)據(jù)類型
declare
   myemp emp%rowtype; --創(chuàng)建一個(gè)以emp表中一行作為數(shù)據(jù)類型的變量
begin
   select * into myemp from emp where empno=9527; --賦值
   dbms_output.put_line(myemp.ename); --ename相當(dāng)于一個(gè)對象,可以使用.來調(diào)用它里面的屬性值
end;
  • %TYPE 引用表中的某列的屬性作為數(shù)據(jù)類型
declare
   cursal emp.sal%TYPE; --引用emp表中的sal列的類型作為cursal的數(shù)據(jù)類型
   mysal number(4):=3000; --聲明一個(gè)變量
   totalsal mysal%TYPE;  --引用變量mysal的數(shù)據(jù)類型作為當(dāng)前變量的數(shù)據(jù)類型
begin
   select sal into cursal from emp where empno=9527;
   total:=cursal+mysal;
   dbms_output.put_line(totalsal);
end;

IF ELSE語句

declare
   newsal emp.sal%TYPE; --
begin
   select sal into newsal from emp where ename='JAMES';
   if newsal>2000 then
      update emp set comm=800 where ename='JAMES';
   else
      update emp set comm=500 where ename='JAMES';
   end if;
end;

IF THEN ……ELSIF THEN

declare
   newsal emp.sal%TYPE;
begin
   select sal into new sal from emp where ename='JAMES';
   if newsal>1500 then
      update emp set comm=1000 where ename='JAMES';
   elsif newsal>1000 then
      update emp set comm=800 where ename='JAMES';
   else
      update emp set comm=500 where ename='JAMES';
   end if;
end;

!!! 注意是elsif ， 不是elseif

CASE語句

declare
  v_grade char(1):=upper('&p_grade'); --&表示彈出輸入框，接收用戶的輸入值
begin
  case v_grade
     when 'A' then
        dbms_out.put_line('Excellent');
     when 'B' then
        dbms_out.put_line('Very Good');
     when 'C' then
        dbms_out.put_line('Good');
     else
        dbms_out.put_line('no such grade');
     end case;
end;

還可以把case的值賦給變量，如下

declare
   v_grade char(1):=upper('&grade');
   p_grade varchar(20);
begin
   p_grade:=
   case v_grade
       when 'A' then
           'Excellent'
       when 'B' then
           'Very Good'
       when 'C' then
           'Good'
       else 
           'no such grade'
   end case;
   dbms_output.put_line(v_grade);
end;

LOOP循環(huán)

declare
   counter number(3):=0;
   sumResult number:=0;
begin
   loop
     counter:=counter+1;
     sumResult:=sumResult+counter;
     if counter>=100 then
        exit;  --退出loop循環(huán)
     end if;
   end loop;
end;

WHILE循環(huán)

declare
   counter number(3):=0;
   sunResult number:=0;
begin
   while counter<100 loop
      counter:=counter+1;
      sunResult:=sumResult+counter;
   end loop;
end;

FOR循環(huán)

declare 
   counter number(3):=0;
   sumResult number:=0;
begin
   for counter in 1..100 loop  -- 1..100表示1到100這個(gè)范圍
       sumResult:=sumResult+counter;
   end loop;
end;

GOTO語句

declare
   sumsal emp.sal%TYPE;
begin
   select sum(sal) into sumsal from emp;
   if sumsal>20000 then
      goto first_label;  --前往第一個(gè)標(biāo)簽處
   else
      goto second_label;  --前往第二個(gè)標(biāo)簽處
   end if;
   <<first_label>> --<<>>是goto可識別的標(biāo)志
   dbms_output.put_line(sumsal);
   <<second_label>>
   null;  --無操作,語句接著往下執(zhí)行
end;

動(dòng)態(tài)SQL

declare
   sql_stmt varchar2(200); --存放SQL語句
   emp_id number(4):=7566; --實(shí)參
   dept_id number(2):=90;  --實(shí)參
   dept_name varchar2(14):='PERSOLLNNEL'; --實(shí)參
   location varchar2(13):='DALLAS'; --實(shí)參
   emp_rec emp%ROWTYPE; --一行數(shù)據(jù)
begin
   sql_stmt:='insert into dept values(:1,:2,:3)'; --里面的:1,:2,:3相當(dāng)于形參
   execute immediate sql_stmt using dept_id,dept_name,location; --using后面的相當(dāng)于實(shí)參
   sql_stmt:='select * from emp where empno=:id'; --:id相當(dāng)于形參
   execute immediate sql_stmt into emp_rec using emp_id; --把執(zhí)行sql_stmt的結(jié)果賦給emp_rec,using后面的相當(dāng)于實(shí)參
end;

異常處理

declare
   newsal emp.sal%TYPE;
begin
   select sal into newsal from emp;
exception
   when TOO_MANY_ROWS then  --TOO_MANY_ROWS是常用異常名
       dbms_output.put_line('返回的記錄太多');
   when OTHERS then
       dbms_output.put_line('未知異常');
end;

自定義異常

declare
   cursal emp.sal%TYPE;
   myexp exception; --定義一個(gè)名為myexp的異常
begin
   select sal into cursal from emp where ename='JAMES';
   if cursal<5000 then
     raise myexp;  --raise用于引發(fā)myexp這個(gè)異常
   end if;
exception
   when NO_DATA_FOUND then
     dbms_output.put_line('no resource found');
   when MYEXP then
     dbms_output.put_line('sal is less 5000');
end;

創(chuàng)建存儲(chǔ)過程

create procedure my_procedure(eno in number,salary out number)
 --in表示是傳入的參數(shù)，out表示輸出的參數(shù)，這里聲明的參數(shù)不寫范圍
as
begin
  select sal into salary from emp where empno=eno;
end;

執(zhí)行上述存儲(chǔ)過程

declare
  my_salary number; --聲明一個(gè)變量用于接收輸出參數(shù)
  my_eno number:=7369; --作為傳入的參數(shù)
begin 
  my_procedure(7369,my_salary); --執(zhí)行存儲(chǔ)過程
  dbms_output.put_line(my_salary);
end;

創(chuàng)建觸發(fā)器

create trigger my_trigger 
after insert or update on dept for each row 
--after表示在操作之后觸發(fā)，on dept表示這個(gè)觸發(fā)器是綁定在dept表上的
declare  --不需要變量的話，可以不要declare
begin
  if inserting then  --如果執(zhí)行的是插入操作，則執(zhí)行下面語句
     dbms_output.put_line('已經(jīng)插入');
  elsif updating then  --如果執(zhí)行的是更新操作，則執(zhí)行下面語句
     dbms_output.put_line('已經(jīng)更新');
  end if;
end;

創(chuàng)建游標(biāo)（游標(biāo)可以對結(jié)果集進(jìn)行一行行處理）

  • 隱式游標(biāo)（不用創(chuàng)建，由系統(tǒng)自動(dòng)控制開啟和關(guān)閉）
begin
   update emp set sal=sal+100 where empno=7876; --增刪改查時(shí)系統(tǒng)會(huì)自動(dòng)打開隱式游標(biāo)
   if sql%found then  --如果有游標(biāo)存在，就說明之前的操作成功
      dbms_output.put_line('修改成功');
   else 
      dbms_output.put_line('修改失敗');
   end if;
end;
  • 顯示游標(biāo)
declare
   cursor emp_cursor is select * from emp; 
--聲明一個(gè)游標(biāo)，這個(gè)游標(biāo)時(shí)指向emp表的返回集(多行),可以理解為這個(gè)地方的游標(biāo)是一個(gè)游標(biāo)工廠
   my_cursor emp_cursor%rowtype;  --聲明一個(gè)自己的游標(biāo)，它指向的是一行數(shù)據(jù)
begin
   for my_cursor in emp_cursor loop --循環(huán)，只要在游標(biāo)工廠中還有自己的位置就繼續(xù)
      dbms_output.put_line('job is '||my_cursor.job||' and salary is '||salary);
   end loop;
ennd;
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 1.簡介 數(shù)據(jù)存儲(chǔ)有哪些方式？電子表格，紙質(zhì)文件，數(shù)據(jù)庫。 那么究竟什么是關(guān)系型數(shù)據(jù)庫？ 目前對數(shù)據(jù)庫的分類主要是...
    喬震閱讀 2,011評論 0贊 2
  • 5.多表查詢 多表查詢 目的：從多張表獲取數(shù)據(jù) 前提：進(jìn)行連接的多張表中有共同的列 等連接 通過兩個(gè)表具有相同意義...
    喬震閱讀 1,540評論 0贊 0
  • 1.PLSQL入門 Oracle數(shù)據(jù)庫對SQL進(jìn)行了擴(kuò)展,然后加入了一些編程語言的特點(diǎn),可以對SQL的執(zhí)行過程進(jìn)行...
    隨手點(diǎn)燈閱讀 687評論 0贊 8
  • 以下文章就是警醒你的…轉(zhuǎn)載自知乎！ 運(yùn)動(dòng)的同時(shí)必須要對胸器做好十二分的愛好。 1.為什么要選專業(yè)的sport br...
    姚小白少校閱讀 1,456評論 0贊 0
  • 晚飯的時(shí)候，他告訴她明天四點(diǎn)多要去西北出差。少則一星期，多則半個(gè)月。 她說：“你咋不早說，好讓我準(zhǔn)備準(zhǔn)備?！?“有...
    河洛夢話閱讀 3,657評論 13贊 22

友情鏈接更多精彩內(nèi)容