新入oracle數(shù)據(jù)庫攀痊,把目前學(xué)到的知識點記錄下來丽涩,可能都比較基礎(chǔ)棺滞,但還是比較全的,里面的示例都是自己在PL/SQL中跑過的矢渊,如果有錯誤继准,還望各位大俠指出哈。
創(chuàng)建用戶
1.創(chuàng)建用戶(使用管理員身份創(chuàng)建矮男,即用system進(jìn)行登錄)
create user rick identified by ric account unlock;
--創(chuàng)建一個用戶名是rick的用戶锰瘸,密碼是ric,用戶狀態(tài)未鎖定
2.上訴創(chuàng)建后昂灵,如果直接用設(shè)置的用戶名和密碼進(jìn)行登錄避凝,則會提示下面的錯誤
3.可以理解為用戶沒有會話權(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來登錄含思,則會提示下面錯誤(一開始在創(chuàng)建用戶時如果不設(shè)置狀態(tài)為unlock,則系統(tǒng)會默認(rèn)為lock狀態(tài))
創(chuàng)建表及約束
create table infos
(
stuid varchar2(7) not null,
age number(2) not null
)
alter table infos add constraint infos_check --給infos表加一個約束甘晤,限制age在20到60之間
check(age>20 and age<60)
根據(jù)結(jié)果集創(chuàng)建表
create table infos2 as select * from infos; --會把數(shù)據(jù)一起復(fù)制到新表中
create table infos3 as select * from infos where 1=2;
--后面加了一個where條件含潘,這個條件不成立,則只會復(fù)制表結(jié)構(gòu)线婚,不包括數(shù)據(jù)
插入數(shù)據(jù)時遏弱,如果表中字段是date類型,需要先使用to_date('2012-12-29','YYYY-MM-DD')將數(shù)據(jù)轉(zhuǎn)換成date類型
當(dāng)兩個表的結(jié)構(gòu)全部相同時塞弊,可以直接插入結(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)容會在一列中顯示出來
消除重復(fù)項
select distinct deptno from emp;
in操作查詢指定列表的值
select ename,job from emp where job in ('salesman','president','analyst');
--只要job是salesman漱逸、president泪姨、analyst中任意一個即滿足條件
between……and操作查詢范圍內(nèi)的數(shù)據(jù)
select ename,job from emp where sal between 1000 and 2000; --包括邊界
like模糊查詢
- % 表示零個或多個任意字符
- _ 表示一個任意字符
- 如果查詢條件中有特殊字符,則使用[ ]括起來
集合運(yùn)算
- intersect (交集) 返回兩個查詢共有的記錄
- union all(并集) 返回各個查詢的所有記錄饰抒,包括重復(fù)記錄
- union(并集) 返回各個查詢的所有記錄肮砾,不包括重復(fù)記錄
- minus(補(bǔ)集) 返回第一個查詢的記錄減去第二個查詢的記錄后剩余的記錄
使用union實現(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是一個oracle自帶的特殊的表,使用只是為了符合select的查詢結(jié)構(gòu)袋坑,它只會返回一行記錄
連接查詢
- 內(nèi)連接:join……on 兩個表中只有滿足條件的才會被查詢出來
- 左外連接:left join ……on 左邊的表不受限制仗处,右邊的表需要滿足條件
- 右外連接:right join……on 右邊的表不受限制,左邊的表需要滿足條件
- 全外連接:full join……on 左右兩張表都不受限制
ANY子查詢(只要滿足ANY中的一個即可)
select ename,job from emp where sal<any(select sal from emp where job='salesman');
--查詢出比任意一個銷售的工資低的員工姓名和工作
ALL子查詢(需要滿足all中的所有)
select ename,job from emp where sal>all(select sal from emp where job='salesman');
--查詢出比所有銷售工資都高的員工的姓名和工作
偽列
- ROWID:表中每一行在數(shù)據(jù)文件中都有一個物理地址枣宫,ROWID就是返回這個物理地址
- 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ù)
- ADD_MONTHS(d,n) 在日期d的基礎(chǔ)上加上n個月婆誓,返回新日期
- LAST_DAY(d) 返回指定日期當(dāng)月的最后一天
- ROUND(d,fmt) 返回一個以fmt為格式的四舍五入的日期值
- EXTRACT(fmt FROM d) 從日期d中提取fmt部分(YEAR、MONTH……)
- TO_CHAR(d,'YYYY-MM-DD HH24:MI:SS') 按照右邊的格式轉(zhuǎn)換日期成字符串镶柱,HH24表示是采用24小時制
- 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)計某一列中的數(shù)據(jù)的和,COUNT()統(tǒng)計有多少條數(shù)據(jù)(即行)
同義詞
create synonym myemp for scott.emp; --給scott用戶的emp表創(chuàng)建別名myemp
oracle可以為表故觅、視圖厂庇、序列、過程输吏、函數(shù)权旷、程序包等指定別名,比如上面的贯溅,當(dāng)當(dāng)前用戶擁有權(quán)限時拄氯,訪問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; --指定每個分區(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恨胚,只要用戶沒退出骂因,這個變量都可以使用
var emp_name varchar(30);
begin
select ename into :emp_name from emp where empno=9527;
end;
!! 注意在使用這種宿主變量時,需要在變量名前加:
屬性數(shù)據(jù)類型
- %ROWTYPE 引用表中的一行作為數(shù)據(jù)類型
declare
myemp emp%rowtype; --創(chuàng)建一個以emp表中一行作為數(shù)據(jù)類型的變量
begin
select * into myemp from emp where empno=9527; --賦值
dbms_output.put_line(myemp.ename); --ename相當(dāng)于一個對象,可以使用.來調(diào)用它里面的屬性值
end;
- %TYPE 引用表中的某列的屬性作為數(shù)據(jù)類型
declare
cursal emp.sal%TYPE; --引用emp表中的sal列的類型作為cursal的數(shù)據(jù)類型
mysal number(4):=3000; --聲明一個變量
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這個范圍
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; --前往第一個標(biāo)簽處
else
goto second_label; --前往第二個標(biāo)簽處
end if;
<<first_label>> --<<>>是goto可識別的標(biāo)志
dbms_output.put_line(sumsal);
<<second_label>>
null; --無操作,語句接著往下執(zhí)行
end;
動態(tài)SQL
declare
sql_stmt varchar2(200); --存放SQL語句
emp_id number(4):=7566; --實參
dept_id number(2):=90; --實參
dept_name varchar2(14):='PERSOLLNNEL'; --實參
location varchar2(13):='DALLAS'; --實參
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)于實參
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)于實參
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; --定義一個名為myexp的異常
begin
select sal into cursal from emp where ename='JAMES';
if cursal<5000 then
raise myexp; --raise用于引發(fā)myexp這個異常
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)建存儲過程
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í)行上述存儲過程
declare
my_salary number; --聲明一個變量用于接收輸出參數(shù)
my_eno number:=7369; --作為傳入的參數(shù)
begin
my_procedure(7369,my_salary); --執(zhí)行存儲過程
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表示這個觸發(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)自動控制開啟和關(guān)閉)
begin
update emp set sal=sal+100 where empno=7876; --增刪改查時系統(tǒ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;
--聲明一個游標(biāo),這個游標(biāo)時指向emp表的返回集(多行),可以理解為這個地方的游標(biāo)是一個游標(biāo)工廠
my_cursor emp_cursor%rowtype; --聲明一個自己的游標(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;