sqlplus “sys/test1234 as sysbda”
desc $controlfile
select status,name form v$controlfile;
desc v$datafile
select file#,status from v$datafile;
desc v$logfile
select member from v$logfile;
SGA ：
DB buffer， 大共享區， 共享池， Redo buffer， 固定SGA
DB高速緩存池（DB buffer）： 預設緩存池， 保持緩池， 再生緩存池
共享池： 庫緩存區（共享SQL區 PL／SQL區）， 字典緩存區
塊(block 8K 作業系統的整數倍) 盤區(extent) 段(segment) 表空間(tablespace) 資料檔案(datafile)
我們隻能指定表在那個表空間中
sqlplus/nolog
connect sys/test1234 as sysdba
startup 執行個體 控制檔案 資料檔案
startup mount 啟動資料檔案 但是不啟動控制檔案
archive log list
startup mount alter database open 非歸檔方式改為歸檔方式
startup nomount 控制檔案失的時間，重新建立控制檔案
shutdown immediate
shutdown
shutdown transactional
shutdown abort 強行關閉資料庫
http://192.168.1.93:5560/isqlplusalter user TESTUSER account unlock;
create user “test” identified by “test”;
grant connect to “test”;
sqlplus scott/tiger
help index
select * from dept
? set
set sqlblanklines on 支援空格行
替代變量
select * from dept where deptno=10
select * from dept where deptno=&tt
檢視指令
list l
l 1 2
c /n/m
l
/
? change
del 4
l
del 2 3
l
a from dept
save d:/oracle/test.txt
l
@c:/oracle/test.txt
get c:/orcle/text.txt
edit
/
? col
col deptno heading “編号”
desc dept
col dname format a10 heading “部門名稱”
col deptno format 999,999,999
connect sys/test1234 as sysdba
set linesize 50
ttitle center “我的标題” skip 1-
left “測試報表” right “頁” -
format 999 sql.pno skip 2
ttitle off
break
? conp
break on pub
select * from books
conp count label “計數” of books_name on pub
spool d:/1.txt
spool off
edit d:/1.txt
視圖學習
視圖稱為虛表
視圖的作用 安全性 友善 一緻性
create or replace view myview
as
select * from dept
create or replace view myview
as
select * from books where price>30
with check option
edit c:/1.txt
@ c:/1.txt
create or replace view myview
as
select * from books where price>30
with read only
dba_views
desc all_views
desc user_views
select text from user_views where view_name=’u_views’
oracle的同義詞
select user from dual
select * from scott.dept
同義詞
create synonym dept for scott.dept
select * from dept
drop synonym dept
create public synonym dept for scott.dept
select * from dept
connect tt/tt11
select * from dept
desc dba_synonyms
desc user_synonyms
序列
create sequence myseq
start with 1
increment by 1
order
nocycle;
select myseq.nextval from dual;
select myseq.currual form dual;
create table auto((a number,b varchar2(10)))
create sequence myseq
insert into auto values(myseq.nextval,”dd”)
desc dba_sequences
select sequence_name,sequence_owner from dba_sequences where sequence_owner=’TT’
select user from dual
alter sequence myseq increment by 3
select myseq.nextval from dual
sql語言基礎
connect scott/tiger
DDL
create table abc(a varchar2(20),b char(20))
alter table abc add c number
alter table abc drop column c
DCL
grant select on dept to tt
revoke select on dept from tt
DML
insert into abc values(’aa’,'cc’)
delete
update
常用系統函數
字元
length ltrim,replace,rtrim,substr,trim
日期
Sysdate,current_date,next_day
轉換
To_char,to_date,to_number
聚集函數
sum,avg,max,min,count
其它
user,decode,nvl
select length(’ddd’) from dual
select lengthb(’dd好d’) from dual
select trim(’ ddd ‘) from dual
select rtrim(’ ddd ‘) from dual
select ltrim(’ ddd ‘) from dual
select SUBSTR(’abacedf’,1,3) from dual
select SUBSTR(’abacedf’,length(’abacedf’)-3+1,3) from dual
SELECT current_date FROM dual
ALTER SESSION SET NLS_DATE_FORMAT=’dd-mon-yyyy hh:mi:ss’
SELECT NEXT_DAY(sysdate,’星期五’) from dual
SELECT TO_CHAR(sysdate,’yyyy-mm-dd hh:mi:ss’) from dual
SELECT TO_DATE(’12-3月-04′) from dual
SELECT TO_NUMBER(’222′) from dual
select user from dual
select sum(decode(sex,’男’,1,0)) 男人數,sum(decode(sex,’女’,1,0)) 女人數 from e;
select a1,nvl(a2,’地輸入’) a2 from aa;
select * from aa where a2 is null
select * from aa where a2 is not null
分組查詢
聚集函數不能在where中，如果要用則用having
select a,count(a) from aa group by a having count(a)>1
模糊查詢
select * from aa where a2 like ‘a_’
select * from aa where a2 like ‘a%’
select * from aa where a2 like ‘_a’
select * from aa where a2 like ‘__a’
select * from aa where a2 like ‘%a’
select * from aa where a2 like ‘%a%’
表的連接配接
from a,b where a.=b.
a join b on a.=b.
from a,b where a.id=b.id(+)左連接配接 左邊為全部顯示出來，有比對值，則寫上，無則以空值填充
右連接配接則相反
子查詢
無關子查詢
select * from e where id in (select id from d);
相關子查詢
select * from e where id in (select id from d where id=e.id and id=’03′);
select * from e where id not in (select id from d where id=e.id and id=’03′);
select * from e where exists (select id from d where id=e.id and id=’03′);
select * from e where not exists (select id from d where id=e.id and id=’03′);
select * from a union select * from d
select * from a intersect select * from d 傳回兩者教比對的記錄
insert into e(id,name) select id,name from d ;
create table ttt as (select * from e)
PL/SQL基礎
declare
…
begin
…
exception
…
end
declare
x varchar2(20);
begin
x:=’this is..’;
dbms_output.put_line(’x的值為:’||x);
end;
/
set serveroutput on size 10000
l
/
save D:/1.txt
@ D:/1.txt
/**/塊注釋
declare
x varchar2(20):=’456kkk’;
–y integer:=123;
y string(10):=’123′;
begin
–x:=’this is..’;
–dbms_output.put_line(’x的值為:’||x);
dbms_output.put(’x的值為:’||x||’y的值是：’||y);
dbms_output.new_line;
end;
/
declare
a number;
b varchar2(10);
begin
a:=2;
if a=1 then
b:=’a';
elsif a=2 then
b:=’b';
else
b:=’c';
end if;
dbms_output.put_line(’B值是：’||b);
end;
/
declare
a number;
b varchar2(10);
begin
a:=10;
case
when a=1 then b:=’a';
when a=2 then b:=’b';
when a=3 then b:=’c';
when a=4 then b:=’d';
else
b:=’others’;
end case;
dbms_output.put_line(’B值是：’||b);
end;
/
declare
cursor mycur IS
select * from dept;
myrecord dept%rowtype;
begin
open mycur;
fetch mycur into myrecord;
while mycur%found loop
dbms_output.put_line(myrecord.deptno||’,'||myrecord.dname);
fetch mycur into myrecord;
end loop;
close mycur;
end;
/
declare
cursor mycur_para(id varchar2) IS
select dname from dept where deptno=id;
t_name dept.dname%type;
begin
open mycur_para(’10′);
loop
fetch mycur_para into t_name;
exit when mycur_para%notfound;
dbms_output.put_line(t_name);
end loop;
close mycur_para;
end;
/
declare
cursor mycur_para(id varchar2) IS
select dname from dept where deptno=id;
begin
dbms_output.put_line(’*******結果集為********’);
for mycur in mycur_para(’10′) loop
dbms_output.put_line(mycur.dname);
end loop;
end;
/
declare
t_name dept.dname%type;
cursor cur(id varchar2) IS
select dname from dept where deptno=id;
begin
if cur%isopen then
dbms_output.put_line(’遊标己被打開’);
else
open cur(’10′);
end if;
fetch cur into t_name;
close cur;
dbms_output.put_line(t_name);
end;
/
declare
t_name varchar2(20);
cursor mycur IS
select dname from dept;
begin
open mycur;
loop
fetch mycur into t_name;
exit when mycur%notfound or mycur%notfound is null;
dbms_output.put_line(’遊标mycur的rowcount是:’||mycur%rowcount);
end loop;
close mycur;
end;
/
declare
cursor IS
select dname from dept for update;
text varchar2(20);
begin
open cur;
fetch cur into text;
while cur%found loop
update dept set dname=name||’_t’ where current of cur;
fetch cur into text;
end loop;
close cur;
end ;
/
begin
for cur in(select dname from dept) loop
dbms_output.put_line(cur.dname);
end loop;
end;
/
建議不要使用遊标
因為效率不是很高
存儲過程
create or replace procedure myproc(id in number)
IS
name varchar2(10);
begin
select dname into name from dept where deptno=id;
dbms_output.put_line(name);
end myproc;
/
show errors procedure myproc;
declare
tid number(10);
begin
tid:=10;
myproc(tid);
end;
/
begin
myproc(10);
end;
/
execute myproc(10);
create or replace procedure myproc2(id varchar2,name out varchar2)
is
begin
select dname into name from dept where deptno=id;
end;
/
declare
tid varchar2(10);
dname varchar2(10);
begin
tid:=’10′;
myproc2(tid,tname);
end;
/
事務與觸發器
delete from books where books_id=’21′
commit;
delete from books where books_id=’22′
rollback;
事務
用于確定資料完整性和并發處理的能力
它将一條／一組SQL語當作成一個邏輯上的單元，用于保障這些語句都成功／失敗
原子性atomicity
一緻性consistency
隔離性isolation
永久性durability
行級觸發器
create or replace trigger del_deptmentid
after delete on deptment
for each row
begin
delete from empl where id=:old.id;
end del_deptmentid;
/
delete from deptment where id=1;
rollback;
create or replace trigger insert_dept
after insert on deptment
for each row
begin
insert into empl(eid,ename,id) values(’123′,’dd’,:new.id);
end;
/
create or replace trigger update_dept
after update
on deptment
for each row
begin
update empl set id=:new.id where id=:old.id;
end;
/
在觸發器中不能寫rollback,commit等，可以用以下語句實作某些記錄不更新
create or replace trigger books_delete
after delete on books
for each row
begin
if :old.books_id=22 then
raise_application_error(-20000,’不充許删除’);
end if ;
end;
/
語句級觸發器
create table mylog(curr_user varchar2(100),curr_date date,act char(1));
create or replace trigger dml_books
after insert or delete or update on books
begin
if inserting then
insert into mylog values(user,sysdate,’I');
elsif deleting then
insert into mylog values(user,sysdate,’D');
else
insert into mylog values(user,sysdate,’U');
end if;
end;
/
update books set books_name=’中途鎬’ where books_id=43
insert into books values(myseq.nextval,’二級戰犯’,33.5,5,’人民文學’)
select curr_user,to_char(curr_date,’yyyy-mm-dd hh24:mi:ss’) 日期 ,act 動作 from mylog
create or replace trigger set_number
before insert on books
for each row
declare
sn number(5);
begin
select myseq.nextval into sn from dual;
:new.books_id:=sn;
end;
/
create or replace view empl_deptment
as
select eid,ename,sex,e.id,d.name from empl e,deptment d where e.id=d.id
/
select * from empl_deptment
create or replace trigger tr_empl_deptment
instead of insert on empl_deptment
for each row
begin
insert into deptment values(:new.id,:new.name);
insert into empl values(:new.eid,:new.ename,:new.sex,:new.id);
end;
/
insert into empl_deptment values(1,’羅’,'男’,1,’銷售部’)
/
安全管理
oracle的安全管理體系
使用者管理
角色管理
配置檔案的設定
(使用者,角色)相當于作業系統的使用者群組
查詢目前賬号
select user from dual;
conn scott/tiger as sysdba;
create or replace trigger tr_empl_deptment;
grant select on scott.dept to test
alter user test default tablespace tt
alter user test identified by test1234
alter user test account lock
alter user test account unlock
create user test identified by test1234
grant connect to test;
conn /as sysdba
grant select on scott.dept to test with grant option 把權限下放給test
grant all on scott.dept to test with grant option
grant execute on scott.mypro to test with grant option
grant create user to test
grant drop user to test
conn /as sysdba
grant create user to test with admin option
conn test/test1234
grant create user to abc
revoke select on scott.dept from test;
revoke create user from test;
總結:無論是系統授權還是對象授權都可以續聯選項
系統授權加的是with admin option
對象授權加的是with grant option
角色授權
create role myrole
grant myrole to test
/
grant select on scott.dept to myrole
概要檔案實作全局設定
特别是對密碼的管理與設定
表空間
create tablespace mytabs
datafile ‘E:/oracle/product/10.1.0/oradata/test/mytabs.dbf’ size 10M
/
alter user test default tablespace mytabs
grant unlimited tablespace,dba to test
create table test(id number(10),name char(10)) tablespace tt
做項目時，先建立表空間 再建立使用者
将使用者設定所建立的表空間
表的管理
表的完整性與限制
實體完整性
域完整性
參照完整性
alter table empl add constraint pk_nn primary key(eid)
/
alter table empl add constraint fk_empl foreign key(id) references deptment(id)
/
alter table deptment add constraint pk_deptment primary key(id)
/
insert into empl values(myseq.nextval,’張’,'女’,3)
/
alter table empl add constraint ck_empl_sex check(sex=’男’ or sex=’女’)
/
desc
select constraint_name,constraint_type from user_constraints
where table_name=’EMPL’
/
desc all_constraints
create index my_dept on deptment(id)
/
create bitmap index bit_empl on empl(sex)
/
create unique index myidx on empl(eid)
/
SQL*Loader的使用
sqlldr
d:/loader.txt
abc,xyz
def,bbb
eee,ttt
d:/cont.ctl
load data
infile ‘D:/loader.txt’
append
into table mm(
m1 position(1:3)) char,
m2 position(5,7) char)
create table mm(m1 varchar2(10),m2 varchar2(10))
/
sqlldr scott/tiger control=d:/cont.ctl data=d:/loader.txt
d:/cont.ctl
load data
infile ‘D:/loader.txt’
append
into table mm(
m1 char terminated by “,”,
m2 char terminated by “,”)
sqlldr scott/tiger control=d:/cont.ctl data=d:/loader.txt
oem的配置
sqlplus /nolog
connect / as sysdba
alter user sys identified by angel918
alter user system identified by angel918
/
sqlplus “/@服務名 as sysdba”
然後在sqlplus中
alter user sys identified by 新密碼;
alter user system identified by 新密碼;
監聽
lsnrctl status
lsnrctl start
lsnrctl stop
資料的備份
exp scott/tiger@testd:/mybak.dmp
imp scott/tiger
connect sys/test1234@test as sysdba
shutdown immediate
startup
archive log list
alter system set log_archive_start=true scope=spfile
/
shutdown immediate
startup mount
alter database archivelog
alter database open;
alter tablespace test begin backup
/
考備表空間到指定目錄
alter tablespace test end backup
/
alter system archive log current
/
alter system switch logfile
/
alter system switch logfile
/
select * from v$recover_file
/
alter database datafile 6 offline drop
/
表空間到指定目錄
select * from v$recover_file
/
auto
alter database datafile 6 online;
備份控制檔案
alter database backup controlfile to trace
/
shutdown immediate
@c:/create_ctl.txt
直接考備控制檔案
ho cls
revover database until concel
alter database open resetlogs
開發人員
create user abc identified by abc;
grant connect,resource to abc;
select table_name from user_tables;
DDL create drop alter trancate
DML select update insert delete
DCL grant revoke
show user
create sequence myseq increment by 1 start with 1;
select myseq.currentval from dual
select myseq.nextval from dual
總結：
oracle體系結構
實列和資料庫
sql*plus
pl/sql
安全管理
表空間、表的完整性
備份與恢複
===============================================================
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)