oracle實例練習(xí)

1、1. 用sys賬戶登錄，解鎖scott賬戶代碼：Connect sys/orclorcl_client AS SYSDBA ALTER USER "SCOTT" ACCOUNT UNLOCK2. 以scott身份登錄數(shù)據(jù)庫conn scott/tigerorcl3. 創(chuàng)建學(xué)生表student(sno,sname,sgender,sbirthday,sadd) score(sno,math,english)代碼：create table student(sno char(3),sname varchar2(10),sgender char2(20),sbirthday dat

2、e,sadd varchar2(50) create table score(sno char(3),math number(4,1),english number(4,1)4. 插入記錄student插入記錄： 001，小張，女，1980-8-20,濟南 002，小王，男，1983-4-1，萊蕪 003，小李，女，1980-5-20，濟南 004，小趙，女，1980-5-20，萊蕪 005, 小孔, 女, 1982-6-18 威海 score插入記錄：(005沒參加考試,800是個進修生,不是學(xué)校的正式生) 001，90，92 002，85，79 003，80，94 004，78，77 80

3、0 79, 88代碼：alter session set nls_date_format ='YYYY-MM-DD HH24:MI:SS' insert into student values('001','小張','女',to_date('1980-08-20','yyyy-mm-dd'),'濟南'); insert into student values('002','小王','男',to_date('1983-04-01&

4、#39;,'yyyy-mm-dd'),'萊蕪'); insert into student values('003','小李','女',to_date('1980-05-20','yyyy-mm-dd'),'濟南'); insert into student values('004','小趙','女',to_date('1980-05-20','yyyy-mm-dd'),'萊蕪&#

5、39;); insert into student values('005','小孔','女',to_date('1982-06-18','yyyy-mm-dd'),'威海'); insert into score values('001','90','92'); insert into score values('002','85','79'); insert into score values(

6、9;003','80','94'); insert into score values('004','78','77'); insert into score values('800','79','88');5. a統(tǒng)計各個地區(qū)的學(xué)生數(shù) b計算各個學(xué)生的總成績（數(shù)學(xué)+英語），并且按照成績由高到低做出學(xué)生的成績單報告(沒考試的學(xué)生名字不要出現(xiàn)在報告單上,進修生的成績也不在報告單上) 報告單標(biāo)題顯示：學(xué)號 姓名 數(shù)學(xué) 英語 總成績 c計算各個學(xué)生的總成績（數(shù)學(xué)+英

7、語），并且按照成績由高到低做出學(xué)生的成績單報告(沒考試的學(xué)生名字也要出現(xiàn)在報告單上,進修生的成績不在報告單上) 報告單標(biāo)題顯示：學(xué)號 姓名 數(shù)學(xué) 英語 總成績代碼：select sadd 地區(qū),count(*) as 人數(shù) from student group by saddselect student.sno 學(xué)號, sname 姓名, math 數(shù)學(xué), english 英語, (math+english) 總成績 from student inner join score on score where student.sno=score.sno order by 總成績 descselec

8、t student.sno 學(xué)號, sname 姓名, math 數(shù)學(xué), english 英語, math+english 總成績 from student left outer join score on student.sno=score.sno order by 總成績 desc或select student.sno 學(xué)號, sname 姓名, math 數(shù)學(xué),english 英語, (math+english) 總成績 from student,score where student.sno=score.sno(+) order by 總成績 desc;6. 根據(jù)student表，創(chuàng)建

9、一個新表student_copy（結(jié)構(gòu)相同，數(shù)據(jù)只有濟南的兩個學(xué)生） 從student表中查出萊蕪得同學(xué)信息，插入到student_copy表中 commit/提交剛才的插入代碼：create table student_copy as select * from student Where sadd='濟南' insert into student_copy(select * from student where sadd='萊蕪'); commit;7. 插入一條新的學(xué)生紀(jì)錄： 006 小林 男 1979-7-9 泰安 savepoint a /設(shè)置保存點a

10、 刪除掉學(xué)號為003的學(xué)生紀(jì)錄（誤刪） rollback to savepoint a 察看結(jié)果 commit(提交插入紀(jì)錄的操作）/rollback（回滾到插入006記錄前的數(shù)據(jù)狀態(tài)）代碼：insert into student values('006','小林','男',to_date('1979-07-09','yyyy-mm-dd'),'泰安');select * from student; savepoint a; delete from student where sno='00

11、3' rollback to savepoint a;select * from student; commit;/rollback;8. 修改student_copy表名為student2 刪除表student2的數(shù)據(jù) (注意delete/truncate的區(qū)別) 刪除表student,score,student2代碼：rename student_copy to student2; delete from student2;rollback;select * from student2;truncate table student2;rollback;select * from s

12、tudent2;drop table student;drop table score;drop table student2;9. 創(chuàng)建100個表，table_0到table_99，分別插入數(shù)據(jù)，第1條數(shù)據(jù)插入到第1個表。第99條數(shù)據(jù)插入到第99個表代碼：create or replace procedure create_table(startnumber in number,endnumber in number) as begin for v_counter in startnumber.endnumber loop execute immediate 'create tabl

13、e table_'|v_counter|' (col1 number)' execute immediate 'insert into table_'|v_counter| ' values('|v_counter|')' End loop; end; exec create_table('0','99');10. 創(chuàng)建一個emp1表,其結(jié)構(gòu)和數(shù)據(jù)與emp表完全一致.用游標(biāo)完成操作:再sql*plus中顯示工資低于1500的職員信息,并顯示如果給他們漲30%工資后的工資.代碼：create

14、table emp1 as select * from emp;set serveroutput ondeclarecursor cur is select * from emp where sal<1500;beginfor v_counter in cur loopdbms_output.put_line(v_counter.empno|' '|v_counter.ename|' '|v_counter.mgr|'' |v_counter.hiredate|' '|v_m|' '|v_counter.sa

15、l|' '|v_counter.sal*1.3);end loop;end;11. 編寫一個pl/sql塊，輸出所有員工的員工名、員工號、工資和部門號。代碼：declarecursor c_emp is select * from emp;beginfor v_emp in c_emp loopdbms_output.put_line(v_emp.ename|'' |v_emp.empno|' '|v_emp.deptno|' '|v_emp.sal);end loop; end;12. 查詢名為“smith”的員工信息，并輸出其

16、員工號、工資、部門號。如果該員工不存 在，則插入一條新記錄，員工號為2007，員工名為“smith”，工資為1500，部門號為10。如果存在多個名為“smith”的員工，則輸出所有名為“smith”的員工號、工資和部門號。代碼：Declare v_emp emp%rowtype;begin select * into v_emp from emp where ename='smith'dbms_output.put_line(v_emp.empno|'' |v_emp.sal|' '|v_emp.deptno);exceptionwhen no_

17、data_found then insert into emp(empno,ename,sal,deptno) values(2007,'smith',1500,10);when too_many_rows thenfor v in (select * from emp where ename='smith') loopdbms_output.put_line(v.empno|' '|v.sal|' '|v.deptno);end loop;end;13. 創(chuàng)建一個存儲過程，以員工號為參數(shù)，輸出該員工的工資。create or r

18、eplace procedure showsal(p_empno emp.empno%type)as v_sal emp.sal%type;beginselect sal into v_sal from emp where empno=p_empno;dbms_output.put_line(v_sal);end;beginshowsal(7844);end;14. 創(chuàng)建一個函數(shù)，以員工號為參數(shù)，返回該員工所在部門的平均工資。create or replace function fun_sal(p_empno emp.empno%type)return emp.sal%typeas v_sal

19、 emp.sal%type;beginselect avg(sal) into v_sal from emp where deptno=(select deptno from emp where empno=p_empno);return v_sal;end;begindbms_output.put_line (fun_sal (7844);end;15. 在emp表上創(chuàng)建一個觸發(fā)器，當(dāng)插入、刪除或修改員工信息時，統(tǒng)計各個部門的人數(shù)及平均工資，并輸出。create or replace trigger trg_empafter insert or update or delete on emp

20、declare v_sal emp.sal%type; v_count number;begin select avg(sal),count(*) into v_sal,v_count from emp; dbms_output.put_line(v_sal|' '|v_count);end;update emp set sal=500 where empno=7844;16. 創(chuàng)建一個包，包含一個過程和一個游標(biāo)。游標(biāo)返回所有員工的信息，過程實現(xiàn)每次輸出游標(biāo)中的5條記錄。create or replace package pkg_persistcursorascursor c_

21、emp is select * from emp; procedure displayemp;end; create or replace package body pkg_persistcursorAs procedure displayemp As v_emp emp%rowtype; begin if not c_emp%isopen then open c_emp;end if;for i in 1.5 loop fetch c_emp into v_emp; dbms_output.put_line(v_emp.empno|' '| v_emp.ename); end

22、 loop;end;end;/begin pkg_persistcursor.displayemp;end;17. 創(chuàng)建一個存儲過程，以2個整數(shù)為參數(shù)，輸出工資在兩者間的員工信息。create or replace procedure salbetween(min binary_integer,max binary_integer)asbegin for v_emp in (select * from emp where sal between min and max) loop dbms_output.put_line(v_emp.empno|' '| v_emp.ename

23、|' '| v_emp.job|' '| v_emp.mgr|' '| v_emp.hiredate|' '| v_emp.sal|' '| v_m|' '| v_emp.deptno); end loop;end;beginsalbetween(1000,3000);end;18. 在emp表上創(chuàng)建一個觸發(fā)器，保證修改員工工資時，改后的工資低于同部門的最高工資，同時高于同部門的最低工資。create or replace package pkg_deptnoas v_deptno emp.dept

24、no%type; v_sal emp.sal%type;end;/create or replace trigger trg_updateempbefore update on empfor each row begin pkg_deptno.v_sal:=:new.sal; pkg_deptno.v_deptno:=:new.deptno;end;/create or replace trigger trg_statementafter update on empdeclare v_highsal emp.sal%type; v_lowsal emp.sal%type;begin selec

25、t max(sal),min(sal) into v_highsal,v_lowsal from emp where deptno= pkg_deptno.v_deptno; if pkg_deptno.v_sal>v_highsal or pkg_deptno.v_sal<v_lowsal then raise_application_error(-20001,'the sal is beyond!'); end if;end;update emp set sal=500 where empno=7844;19. 一個用戶user1具有dba權(quán)限他擁有scott方

26、案他要給人力資源經(jīng)理授予管理scott的所有對象的權(quán)限他要給人力資源職員授予查詢所有對象的權(quán)限conn user1create role rolemagr;create role roleclerk;grant create session to rolemagr;grant all on scott.emp to rolemagr;grant create session to roleclerk;grant select on scott.emp to roleclerk;create user magr1 identified by magr1;create user clerk1 id

27、entified by clerk1;-指定用戶名及密碼grant rolemagr to magr1 with admin option;grant roleclerk to clerk1;20. truncate,delete,drop的比較:注意:這里說的delete是指不帶where子句的delete語句相同點:truncate和不帶where子句的delete, 以及drop都會刪除表內(nèi)的數(shù)據(jù)不同點: 1) truncate和 delete只刪除數(shù)據(jù)不刪除表的結(jié)構(gòu)(定義) drop語句將刪除表的結(jié)構(gòu)被依賴的約束(constrain),觸發(fā)器(trigger),索引(index); 依

28、賴于該表的存儲過程/函數(shù)將保留,但是變?yōu)閕nvalid狀態(tài). 2) delete語句是DML,這個操作會放到rollback segement中,事務(wù)提交之后才生效;如果有相應(yīng)的trigger,執(zhí)行的時候?qū)⒈挥|發(fā). truncate,drop是DDL, 操作立即生效,原數(shù)據(jù)不放到rollback segment中,不能回滾. 操作不觸發(fā)trigger. 3) delete語句不影響表所占用的extent, 高水線(high watermark)保持原位置不動 顯然drop語句將表所占用的空間全部釋放 truncate 語句缺省情況下見空間釋放到 minextents個 extent,除非使用r

29、euse storage; truncate會將高水線復(fù)位(回到最開始). 4) 速度,一般來說: drop> truncate > delete 5) 安全性:小心使用drop 和truncate,尤其沒有備份的時候.否則哭都來不及 使用上,想刪除部分?jǐn)?shù)據(jù)行用delete,注意帶上where子句. 回滾段要足夠大. 想刪除表,當(dāng)然用drop 想保留表而將所有數(shù)據(jù)刪除. 如果和事務(wù)無關(guān),用truncate即可. 如果和事務(wù)有關(guān),或者想觸發(fā)trigger,還是用delete. 如果是整理表內(nèi)部的碎片,可以用truncate跟上reuse stroage,再重新導(dǎo)入/插入數(shù)據(jù)。21.

30、Bind Variables(綁定變量：)和Substitution Variables（替代變量&）的區(qū)別答：&變量只能使用在sql*plus中，其它地方無法使用。僅僅是作為變量替換用的，目的估計是為了代碼與人交互用的。而綁定變量在程序里面用:i來表示環(huán)境里面的一個值，放在sql里面就是一個占位符，以便于下次執(zhí)行該sql如果這個數(shù)變了，oracle還是認(rèn)為是一個sql提高效率22. 索引分類邏輯上：Single column 單行索引 Concatenated 多行索引 Unique 唯一索引NonUnique 非唯一索引 Function-based函數(shù)索引 Domain

31、域索引物理上：Partitioned 分區(qū)索引 NonPartitioned 非分區(qū)索引B-tree：Normal 正常型B樹 Rever Key 反轉(zhuǎn)型B樹 Bitmap 位圖索引23. 索引結(jié)構(gòu)：1) B-tree：適合與大量的增、刪、改（OLTP）；不能用包含OR操作符的查詢；適合高基數(shù)的列（唯一值多）；典型的樹狀結(jié)構(gòu)；每個結(jié)點都是數(shù)據(jù)塊；大多都是物理上一層、兩層或三層不定，邏輯上三層；葉子塊數(shù)據(jù)是排序的，從左向右遞增；在分支塊和根塊中放的是索引的范圍；2) Bitmap:適合與決策支持系統(tǒng)；做UPDATE代價非常高；非常適合OR操作符的查詢； 基數(shù)比較少的時候才能建位圖索引；3) 樹型

32、結(jié)構(gòu)：索引頭 開始ROWID，結(jié)束ROWID（先列出索引的最大范圍）4) BITMAP每一個BIT對應(yīng)著一個ROWID，它的值是1還是0，如果是1，表示著BIT對應(yīng)的ROWID有值；24. 數(shù)據(jù)庫聯(lián)機備份完全恢復(fù)進行熱備份（1）查看數(shù)據(jù)庫是否己經(jīng)啟動歸檔日志：SQL> archive log list; SQL> startup mount（2）修改數(shù)據(jù)庫的歸檔日志模式：SQL>alter database archivelog; （3）Alter database open;打開數(shù)據(jù)庫，查看數(shù)據(jù)庫中的表空間文件：select name from v$datafile;（4）

33、使數(shù)據(jù)庫表空間處于熱備份狀態(tài)；SQL> alter tablespace users begin backup;（5）此時可以直接將表空間數(shù)據(jù)文件復(fù)制到另一個目錄中進行備份。（6）使用如下命令結(jié)束熱備份狀態(tài)：SQL>alter tablespace users end backup;（）對數(shù)據(jù)庫進行一些數(shù)據(jù)修改操作。故障模擬：關(guān)閉數(shù)據(jù)庫，刪除users01.dbf打開數(shù)據(jù)庫SQL> startup數(shù)據(jù)庫完全恢復(fù)：（1）將原先備份的表空間文件復(fù)制到其原來所在的目錄，并覆蓋原有文件。（2）使用 recover命令進行介質(zhì)恢復(fù)：SQL> recover datafile &#

34、39;E:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF'將數(shù)據(jù)庫打開SQL> alter database open;至此表空間數(shù)據(jù)恢復(fù)完成。查看數(shù)據(jù)庫已恢復(fù)到最新狀態(tài)。25. 不完全恢復(fù)(恢復(fù)一個刪除的表)1) 正常關(guān)閉數(shù)據(jù)庫，備份所有的數(shù)據(jù)文件SQL> shutdown immediate數(shù)據(jù)庫已經(jīng)關(guān)閉。 此時備份所有的數(shù)據(jù)文件2) 刪除一個表SQL> select * from dba_objects where owner='SCOTT'SQL> drop table scott.employee;表

35、已刪除。SQL>alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss'會話已更改。SQL> select sysdate from dual;SYSDATE- 2006.12.20 10:45:03SQL> drop table scott.emp2; 表已刪除。3) 實施恢復(fù);正常關(guān)閉數(shù)據(jù)庫;以mount方式啟動數(shù)據(jù)庫;復(fù)制全部數(shù)據(jù)文件的備份文件;實施基于時間的恢復(fù)SQL> recover database until time '2006.12.20 10:45:00'完成介

36、質(zhì)恢復(fù)。打開數(shù)據(jù)庫，打開的時候要用resetlogs把歷史日志都清空，日志序列號從0開始記錄SQL> alter database open resetlogs;數(shù)據(jù)庫已更改。SQL> select * from v$log;在列 FIRST_CHANGE# 前截斷 (按要求)26. 閃回查詢刪除一個表的數(shù)SQL> delete from scott.emp2;SQL> commit;SQL> alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss'SQL> select sysdat

37、e from dual; 估計一下時間，做閃回查詢SYSDATE-2006.12.20 11:09:21SQL> select * from scott.emp2 as of timestamp to_timestamp('2006.12.20 11:07:00','yyyy.mm.dd hh24:mi:ss');把查詢的數(shù)據(jù)重新插入到emp2表中。SQL> insert into scott.emp2(select * from scott.emp2 as of timestamp to_timestamp('2006.12.20 11:0

38、7:00','yyyy.mm.dd hh24:mi:ss');27. 刪除閃回當(dāng)用戶刪除一個表，Oracle會將該表放到回收站中?；厥照局械膶ο笠恢睍Ａ?，直到用戶決定永久刪除它們或者出現(xiàn)表空間的空間不足時才會被刪除。SQL>drop table emp2;SQL> show recyclebin查看回收站SQL>Flashback table emp2 to before drop;SQL> select * from emp2;不知道原表名，可以用回收站中的名稱進行閃回：SQL>Flashback table "BIN$YmQ

39、aoJQ7T9+WMpis/50FxQ=$0" to before drop;在回收的同時可以修改表名SQL>Flashback table "BIN$YmQaoJQ7T9+WMpis/50FxQ=$0" to before drop rename to emp1;SQL>Drop table emp2 purge; -永久刪除表SQL>Purge recyclebin; -清空當(dāng)前用戶的回收站SQL>Purge dba_recyclebin;28. SQL語句1） 基本查詢SQL>select emp.deptno,ename fr

40、om scott.emp;SQL>select empno,ename,sal,sal*1.08 from emp;SQL>select 2+2 from dual;SQL>select sysdate from dual; -dual為偽表, 查詢系統(tǒng)時間SQL>select user from dual; -當(dāng)前登錄用戶SQL>desc dual; -對dual表進行描述SQL>select empno,ename,nvl(mgr,0) from emp; -如果mgr列有null值，則代替為零SQL>select distinct job fro

41、m emp;2） Oracle日期 'DD-MM-YY HH:MI:SS' 'DD-MM-YY HH12:MI:SS'SQL>SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') "Right Now" From Dual; 3） 排序 desc 降序 asc升序SQL>select ename,deptno,sal from emp order by 2 asc,3 desc;4） 限制SQL>select empno,ename,sal from emp whe

42、re sal<1500 or ename between 'QUENTIN' and 'ZYRYRAB'SQL>select * from emp where ename like 'A%'5） 全連接SQL>select e.ename,e.deptno,d.dname from emp e full outer join dept d on e.deptno=d.deptno;6） 左外連接where e.deptno=d.deptno(+); 右面可為null7） 右外連接from emp e right outer jo

43、in dept d on e.deptno=d.deptno;8） 使用組函數(shù)avg(sal) count(*) max(sal) min(sal) sum(sal)9） group by：select deptno,job,avg(sal) from emp group by deptno,job;select deptno,job,avg(sal) from emp group by deptno,job order by 3 desc;select deptno,job,avg(sal) from emp group by deptno,job having avg(sal)>20

44、00;10） whereselect ename,deptno,sal from emp where deptno in(select deptno from dept where dname in ('ACCOUNTING','SALES');select e.ename,e.job,e.sal from emp e where exists(select d.deptno from dept d where d.loc='NEW YORK' and d.deptno=e.deptno);11） from子查詢select e.ename,su

45、bq.loc from emp e,(select deptno,loc from dept where loc in('NEW YORK','DALLAS') subqwhere e.deptno=subq.deptno;12） 使用替代變量13） 操作表alter table employee drop column hire_date;Alter table employee add(hire_date date);Alter table employee modify(lastname varchar2(25); 長度由10改為25Drop table emp_copy_2;Truncate table employees; 截斷表Rename employees to employee1;Alter table employee1 rename to employees;14） 驗