一、創建表空間及用戶授權

-- 創建表空間

create tablespace zhous --檔案的名字
datafile 'f:\zhou.dbf' --檔案的儲存地址
size 100m --檔案大小
autoextend on next 10m; --每次擴容大小 


--洗掉表空間
drop tablespace zhous;


--創建用戶
create user zhouxs --用戶名字
identified by 123123 --用戶密碼
default tablespace zhous --默認表空間

--授權
connect --連接角色
resource --開發者角色
dba --超級管理員

grant dba to zhouxs; --給用戶賦予超級管理員權限

二、對表的基本操作

--創建資料表
create table person(
       pid number(21),
       pname varchar(25)
)

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

alter table person modify sex varchar(2); --修改表欄位型別

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

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

insert into person values('1','張三'); --插入資料

delete from person; --洗掉整個表
drop from person; --洗掉表結構

truncate table person; --先洗掉表再創建表

 
update person set pname='李四' where pid=1 --修改

select * from person; --查詢



--創建序列 每次自增1
create sequence s_person;

insert into person values(s_person.nextval,'王五')

三、函式

3.1、單行函式

--scott用戶，密碼tiger
--解鎖scott用戶
alter user scott account unlock;

--解鎖用戶密碼(也可以設定密碼)
alter user scott identified by tiger;

select * from emp e


--單行函式：作用于一行，回傳一個值
--字符函式
select upper('hello') from dual --小寫轉大寫
select lower('HELLO') from dual --小寫轉大寫

--數值函式
select round(45.6,-1) from dual --四舍五入，后面顯示保留的小數
select trunc(46.7,-1) from dual --直接截取，不看后面的數字

--日期函式
--查出所偶有員工距離現在有多少天
select sysdate-e.hiredate from emp e 

--算出明天此刻
select sysdate+1 from dual

--算出所有員工入職距離現在有多少個月
select months_between(sysdate,e.hiredate) from emp e;

--算出所有員工入職距離現在有多少年
select months_between(sysdate,e.hiredate)/12 from emp e;

--算出所有員工入職距離現在有多少周
select round((sysdate-e.hiredate)/7) from emp e;

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

--字串轉日期
select to_date('2021-4-2 20:58:56','fm yyyy-mm-dd hh24:mi:ss') from dual;

--通用函式
--null值運算結果都為null
select e.sal*12+nvl(e.comm,0) from emp e;

3.1、多行函式

-多行函式 作用域多行函式，回傳一個值

select count(1) from emp; --查詢總數量

select sum(e.sal) from emp e; --求和

select min(e.sal) from emp e; --求最大值

select avg(e.sal) from emp e; --求平均值

select max(e.sal) from emp e; --求最大值

四、條件運算式

--條件運算式

--相當于switch

select e.ename,
       case e.ename
         when 'SMITH' then '史密斯'
           when 'ALLEN' then '艾倫'
             else '無名'
               end
 from emp e;
 
 
 --判斷工資范圍
 select e.sal,
          case
       when e.sal>3000 then '高收入'
         when e.sal>1500 then '中等收入'
           else '低收入'
             end
from emp e;


--oracle專用運算式
select e.ename,
 decode(e.ename,
 'SMITH','史密斯',
 'ALLEN','艾倫',
 '無名') "中文名" 
from emp e;

五、復雜查詢和分頁查詢

--分組查詢,group by后面的列才能出現在select 后面，想要出現在group by后面加聚合函式
select e.deptno,avg(e.sal) 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;

--where過濾分組前的資料，having過濾分組之后的資料
select e.deptno,avg(e.sal) from
emp e 
where e.sal>800
group by e.deptno;


--查詢部門大于800的員工工資，再查詢除部門平均工資大于2000的人
select e.deptno,avg(e.sal) 
from emp e 
where e.sal>800
group by e.deptno having avg(e.sal)>2000;


--笛卡爾積
select * from emp e,dept d;

--等值連接
select * from emp e,dept d where e.deptno=d.deptno;

--內連接
select * from emp e inner join dept d on e.deptno=d.deptno;

--外連接
--右連接
select * from emp e right join dept d on e.deptno=d.deptno;


--左連接
select * from emp e left join dept d on e.deptno=d.deptno;


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


--自連接
select e1.ename,e2.mgr
from emp e1,emp e2
where e1.mgr=e2.empno


--子查詢
select * from emp where sal in
(select sal from emp where ename='scott')

--查詢工資和10號部門任意工資一樣的員工
select * from emp where sal in
(select sal from emp where deptno=10)


--查詢每個部門的最低工資，最低工資員工姓名，員工所在部門
select t.deptno,t.msal,e.ename,d.dname 
from (
select deptno,min(sal) msal from emp group by deptno
)t,emp e,dept d
where t.deptno =e.deptno and t.msal=e.sal
and e.deptno =d.deptno


--分頁查詢
--rownum行號
--查詢一行記錄，就會在該行加上一個行號，從一開始，依次遞增，不能跳著走
select rownum,e.* from emp e order by e.sal desc

select rownum, t.* from (
select rownum,e.* from emp e order by e.sal desc
) t;

select * from (
select rownum rn,tt.* from(
select * from emp  order by sal desc
) tt where rownum<11 
)  where rn>5

六、視圖和索引

--創建視圖，必須有管理員權限
create table emp as select * from scott.emp;

select * from emp;
--創建視圖
create view v_emp as select ename,job from emp;

--查詢視圖
select * from v_emp;

--修改視圖[不推薦]
update v_emp set job='222' where ename='smith';

--創建只讀視圖
create view v_emp1 as  select ename,job from emp with read only;

--視圖的作用
--①屏蔽敏感欄位
--②保證資料的及時統一



--索引
--普通索引
create index idx_name on emp(ename)

select * from emp where ename='KING' --觸發單列索引

--復合索引
create index idx_name on emp(ename,job)

select * from emp where ename='KING' and job='xx'; --觸發索引

select * from emp where ename='KING' or job='xx'; --不觸發索引

七、PLSQL編程

plsql是對sql語言的擴展，使得sql具有程序化編程的特性，靈活高效，主要用來撰寫程序函式和存盤函式，

7.1、變數定義和賦值

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

7.2、PLSQL條件判斷和回圈

--PLSQL--IF判斷 dbms_output.put_line--輸出陳述句
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;


--PLSQL--回圈
--三種回圈輸出1-10
declare
  i number(2):=1;
begin
  while i<11 loop --i<10為退出條件
       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;

八、游標

--游標：可以存放多個物件，多行記錄，
--輸出emp表中的所有資料
declare
  cursor cl is select * from emp; --創建游標，游標的資料來自emp表
  emprow emp%rowtype; --用來保存一行資料
begin --開始
  open cl; --打開游標
       loop --開始回圈
         fetch cl into emprow; --將遍歷結果放到變數
         exit when cl%notfound; --沒找到退出
         dbms_output.put_line(emprow.ename); --輸出資訊
         end loop; --結束回圈
         close cl; --關閉游標
  end; --退出

--游標：可以存放多個物件，多行記錄，
--輸出emp表中的所有資料
declare
  cursor cl is select * from emp; --創建游標，游標的資料來自emp表
  emprow emp%rowtype; --用來保存一行資料
begin --開始
  open cl; --打開游標
       loop --開始回圈
         fetch cl into emprow; --將遍歷結果放到變數
         exit when cl%notfound; --沒找到退出
         dbms_output.put_line(emprow.ename); --輸出資訊
         end loop; --結束回圈
         close cl; --關閉游標
  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;
  
select * from emp where deptno=10;

九、存盤程序、存盤函式

9.1、存盤程序

存盤程序就是提交編譯好的plsql放到資料庫端可以直接被呼叫，

--存盤程序
create or replace procedure pl (eno emp.empno%type)
is
begin
  update emp set sal=sal+1000 where empno=eno;
  commit;
  end;

declare
begin
 pl(7788);
  end;

--存盤程序輸出引數
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;

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


--in 和out的區別
--涉及到into查詢陳述句或者:=賦值的引數，都需要out來修飾

9.2、存盤函式

--存盤函式
--計算員工年薪
create or replace function f_years_sals (eno emp.empno%type) return number --回傳型別為number
is
       s number(10); --定義變數用來接識訓傳結果
begin
  select sal*12+nvl(comm,0) into s from emp where empno=eno;
  return s;
end;

--測驗
declare
  s number(10);
begin
  s :=f_years_sals(7788);
   dbms_output.put_line(s);
  end;

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

語法區別：關鍵字不一樣，

本質區別：存盤函式有回傳值（比存盤程序多了兩個回傳值），存盤程序沒有回傳值，

十、觸發器

10.1、觸發器的概念

當我們做增刪改的時候如果需要做出什么操作，可以使用觸發器，

10.2、觸發器分類

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

行級觸發器：包含or each row 的觸發器，加觸發器是為了old或者new物件，

--陳述句級觸發器
create or replace trigger t1
after
insert
on person
declare
begin
   dbms_output.put_line('新員工入職');
  end;
  
insert into person values(4,'小黑');

--行級觸發器
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;

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;
    
select * from person;


insert into person(pname) values('星星')

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

標籤：其他

上一篇：Excel: 批量去除空格的函式——trim函式, substitute函式,clean函式

下一篇：資料庫關系模式的規范化

Oracle筆記