Oracle自學筆記.doc

Oracle命令一.連接命令1. conn system/adminsaOrcl as sysdba(sysoper)2. disc3. passw4. show user;5. exit二.文件操作1. start D:query.sql | D:query.sql2. edit D:query.sql3. spoolspool D:query.sql;select * from emp;spool off;三.交互命令1. Select * from emp where ename=&ename;四.環(huán)境變量1. set linesize 80;2. set pagesize 5;Oracle用戶管理1. 創(chuàng)建用戶(需要管理員用戶權限)create user username identified by password;2. 給用戶修改密碼(需要管理員用戶權限)password username3. 刪除用戶(需要管理員用戶權限)drop user username注:如果要刪除的用戶已經(jīng)創(chuàng)建表,需加參數(shù)cascade ,將同時刪除表4. 授權用戶(需要管理員用戶權限)grant connect to username;注:Oracle權限分為兩種 a.系統(tǒng)權限:用戶對數(shù)據(jù)庫的相關權限,如登錄,建庫表索引修改密碼等140 b.對象權限:用戶對其他用戶的數(shù)據(jù)對象的訪問的權限25數(shù)據(jù)對象:表 視圖 存儲過程等角色是對用戶權限的批量授權角色分為兩種 a.預定義角色 b.自定義角色三種比較重要的角色1.connect2.dba3.resource對象權限的授權grant select on tableName to username;-只授權select對象權限給某用戶select * from scott.emp;-涉及到方案的概念對象權限的傳遞grant select on tableName to username with grant option;系統(tǒng)權限的傳遞grant connect to username with admin option;5. 鎖定和解鎖用戶alter user scott account lock;alter user scott account unlock;6. 撤銷授權revoke select on tableName from username;注:使用傳遞授權時 如果上級權限被撤銷時 下級的權限同時被撤銷 形象比喻為株連政策7. 查看表結(jié)構desc tableName8. 使用profile管理用戶的密碼(1).create profile profilename limit failed_login_attempts 3 password_lock_time 1;alter user scott profile Account.lock;注:創(chuàng)建名為profilename的配置文件,內(nèi)容為登錄失敗嘗試次數(shù)3 密碼鎖定 1 天.(2).create profile profilename limit password_life_time 27 password_grace_time 3;注:創(chuàng)建名為profilename的配置文件,內(nèi)容為強制27天修改一次密碼 寬限3天(3).create profile profilename limit password_life_time 27 password_grace_time 3 password_reuse_time 30;注:啟用password歷史(4).drop profile profilename cascade;注:刪除用戶配置文件 加參數(shù)cascade同時刪除相關約束Oracle表管理1. 表的操作(1). 創(chuàng)建表create table student( studentId number(4), studentName varchar2(12), studentSex char(2), studentBirthday date, studentSalary number(7,2) );(2). 添加表字段alter table student add(studentAdd varchar2(50);(3). 修改表字段alter table student modify(studentAdd varchar2(100);(4). 刪除表字段alter table student drop column(studentSalary number(7,2);(5). 表重命名rename student to studentTable;(6). 表刪除drop table student;注: 表中有數(shù)據(jù)時 修改表字段 刪除表字段和修改表名操作盡量避免 有風險(7). 插入記錄Insert into scott.student (studentID,studentName,sex,studentBirthday) values (1,祁連山,男,1986-5-21);(8). 修改記錄update student set sex=男,studentBirthday=1986-5-20 where studentID=1;(9). 刪除記錄delete from student;-刪除student表中所有數(shù)據(jù)truncate table student;-刪除student表中所有數(shù)據(jù)注:以上兩者的區(qū)別在于delete刪除時寫日志 以便回滾 回滾操作見下面第十條 truncate刪除時不寫日志 當數(shù)據(jù)量很大時可明顯提高速度delete from student where studentID10;-刪除學號大于10的所有記錄(10). 使用Oracle的寫日志功能來恢復數(shù)據(jù)savepoint pointname;delete from student;rollback to pointname;注:在delete操作之前先做好回滾標記 可在delete操作之后使用rollback to pointname回滾2. 表的查詢(1). 查詢表結(jié)構desc student;(2). 查詢表所有記錄select * from student;(3). 查詢并計算有空值的列 以scott.emp表計算雇員的年工資select ename 姓名,sal*12+nvl(comm,0)*12 年工資 from scott.emp;(4). 其他一些查詢示例select * from emp where sal between 2000 and 2500;select * from emp where sal2000 and sal(select avg(sal) from emp);用上面的理解方法來理解這個查詢也很簡單3). 這樣理解就可以寫出更復雜一些的查詢了,再看看怎么給工資低于平均工資并且雇用日期在1981-6-1之前的員工工資上調(diào)10%.select ename 姓名,sal 原工資,sal*1.1 調(diào)整后工資 from scott.emp where sal(select avg(sal) from scott.emp) and hiredate(to_date(1981-6-1,yyyy-mm-dd);(2). group by和having子句group by:用來對查詢結(jié)果的分組統(tǒng)計having:用于限制分組顯示的結(jié)果1). 計算各個部門的平均工資和最高工資select avg(sal),max(sal),deptno from emp group by deptno;注:分組字段必須出現(xiàn)在查詢中,不然會出錯.2). 查詢各個部門里各種職位的最低,最高和平均工資select min(sal),max(sal),avg(sal),deptno,job from emp group by deptno,job;3). 查詢部門平均工資低于2000的部門編號和平均工資select deptno,avg(sal),max(sal),min(sal) from emp group by deptno having avg(sal)all(select sal from emp a1 where deptno=30);還有另一種表示如下select a1.ename,a2.dname,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno and a1.sal(select max(sal) from emp a1 where deptno=30);4). 查詢工資比部門編號為30的部門中所有員工工資還高的員工信息select a1.ename,a2.dname,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno and a1.salany(select sal from emp a1 where deptno=30);同上還有另一種表示select a1.ename,a2.dname,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno and a1.sal(select min(sal) from emp a1 where deptno=30);5). 查詢與SMITH所在的部門和職位完全相同的員工信息(多列子查詢)select * from emp where (deptno,job)=(select deptno,job from emp where ename=SMITH);同樣,要是把查詢分開來也是可以的,只是比起來要麻煩很多!select * from emp where deptno=(select deptno from emp where ename=SMITH) and job=(select job from emp where ename=SMITH);6). 顯示各部門工資高于本部門平均工資水平的員工信息select a1.ename,a1.sal,a1.deptno,a2.avgsal from emp a1,(select deptno,avg(sal) avgsal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sala2.avgsal;6. Oracle的分頁查詢 分三種1). rowid分頁 執(zhí)行速度最快2). rownum分頁 執(zhí)行速度較快查詢emp表的4-10條記錄 這個查詢很簡單吧 寫出來看看select * from emp where rownum=4 and rownum=10;奇怪 怎么查出來沒有結(jié)果呢 道理想來想去就是這樣沒錯 但是這個查詢Oracle應該這樣寫select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum=4;注:好好想想為什么要這樣寫,看了好多遍還是沒有想明白,這個查詢只要改一下就查不出結(jié)果來,實在費解!3). 分析函數(shù)分頁 執(zhí)行速度最慢7. 合并查詢(集合操作符union,union all,intersect,minus) 有人說速度比and和or快的多1). union求兩個集合的并集(去重復)select ename,sal from emp where sal2500 union select ename,sal from emp where job=MANAGER;2). union all求兩個集合的并集(不去重復)select ename,sal from emp where sal2500 union all select ename,sal from emp where job=MANAGER;3). intersect求兩個集合的交集select ename,sal from emp where sal2500 intersect select ename,sal from emp where job=MANAGER;4). minus求兩個集合的差集select ename,sal from emp where sal2500 minus select ename,sal from emp where job=MANAGER;8. 用查詢結(jié)果創(chuàng)建新表create table empnew (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;Oracle數(shù)據(jù)庫的創(chuàng)建和刪除1. 使用Oracle Database Configuration Assistant來創(chuàng)建新的或刪除已有的數(shù)據(jù)庫Oracle的事務處理數(shù)據(jù)一致 銀行轉(zhuǎn)賬只讀事務 定時統(tǒng)計set transaction read only;Oracle函數(shù)1. 字符函數(shù)lower(char) upper(char) length(char) substr(char) replace(char,a,A)把a換成A instr(china,in)2. 數(shù)學函數(shù)round()/四舍五入round (1.553)-2round (1.553,1)-1.6round (1.553)-1.55trunk()/截取數(shù)字 trunk(123.123)-123trunc(123.123,1)-123.1trunc(123.123,-2)-100mod()/取模floor()/向下取整ceil()/向上取整cos()cosh()exp()ln()log()sin()sinh()sqrt()tan()tanh()acos()asin()atan()abs()power(m,n)/m的n次冪exp(n)/e的n次冪atan()/反正切函數(shù)3. 日期函數(shù)to_date(1986-6-29,yyyy-mm-dd)sysdateadd_months(d,n)last_day(n)/返回指定日期所在月份的最后一天應用舉例查找已經(jīng)八個月多的員工信息select * from emp where sysdateadd_months(hiredate,8);查詢每個員工入職的天數(shù)select ename,sal,trunc(sysdate-hiredate) hiredays from emp;找出各月倒數(shù)第三天入職的員工select * from emp where last_day(hiredate)-hiredate=2;4. 轉(zhuǎn)換函數(shù)to_char()查看當前系統(tǒng)時間(帶時分秒)select to_char(sysdate,yyyy/mm/dd hh24:mi:ss) from dual;查看所有12月份入職的員工信息select * from emp where to_char(hiredate,mm)=12;to_dateinsert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7796,MICCO,MANAGER,7782,to_date(1983-9-10,yyyy-mm-dd),2000,null,10);5. sys_context()terminallanguagedb_namenls_date_formatsession_usercurrent_schema(當前會話默認方案名)host用法select sys_context(userenv,db_name) from dual;/紅色部分固定Oracle管理員的基本職責1. 安裝和升級Oracle數(shù)據(jù)庫2. 建庫、表空間、表、視圖、索引等3. 制定并實施備份與恢復計劃4. 數(shù)據(jù)庫權限管理、優(yōu)化和故障排除5. 高級DBA 要求能參與項目開發(fā) 會編寫SQL語句 存儲過程 觸發(fā)器 規(guī)則 約束 包等用戶sys和 system 用戶的權限區(qū)別sys用戶角色dbasysdbasysopersystem用戶角色dbasysdbasys用戶登錄必須指定是以sysdba身份還是sysoper身份登錄系統(tǒng) 不能以Normal方式登錄Oracle數(shù)據(jù)庫的備份與恢復1. 數(shù)據(jù)庫的邏輯備份(1). 導出表(結(jié)構+數(shù)據(jù))exp userid=scott/tigerOrcl tables=(emp,dept) file=D:scott.dmp;(2). 導出其他用戶的表exp userid=scott/tigerOrcl tables=(scott.emp,scott.dept) file=D:scott.dmp;(3). 導出表結(jié)構exp userid=scott/tigerOrcl tables=(emp,dept) file=D:scott.dmp rows=n;(4). 直接導出 速度上會有優(yōu)勢 但是要求源和目標字符集一致exp userid=scott/tigerOrcl tables=(emp,dept) file=D:scott.dmp direct=y;(5). 導出方案(針對用戶)導出整個數(shù)據(jù)庫exp userid=scott/tigerOrcl owner=(system,scott) file=D:scott.dmp;(6). 數(shù)據(jù)庫的完全備份exp userid=system/managerOrcl full=y inctype=complete file=D:full.dmp;2. 數(shù)據(jù)庫的導入(1). 導入自己的表imp userid=scott/tigerOrcl tables=(emp) file=D:emp.dmp;(2). 導入其他用戶的表imp userid=system/managerOrcl tables=(emp) file=D:emp.dmp touser=scott;(3). 只導入表結(jié)構imp userid=scott/tigerOrcl tables=(emp) file=D:scott.dmp rows=n;(4). 導入數(shù)據(jù)imp userid=scott/tigerOrcl tables=(emp) file=D:emp.dmp ignore=y;(5). 導入自身方案imp userid=scott/managerOrcl file=D:scott.dmp;(6). 導入其他用戶方案imp userid=system/manager file=D:scott.dmp fromuser=system touser=scott;(7). 導入數(shù)據(jù)庫imp userid=system/manager full=y file=D:full.dmp;數(shù)據(jù)字典、數(shù)據(jù)字典基表和動態(tài)性能視圖數(shù)據(jù)字典記錄了數(shù)據(jù)庫的系統(tǒng)信息，它是只讀表(數(shù)據(jù)字典基表)和視圖(數(shù)據(jù)字典視圖)的集合。其所有者是sys。用戶只能在數(shù)據(jù)字典上執(zhí)行查詢操作，其維護和修改是由系統(tǒng)自動完成的。數(shù)據(jù)字典由數(shù)據(jù)字典基表和數(shù)據(jù)字典視圖組成，基表存儲數(shù)據(jù)庫的基本信息，數(shù)據(jù)字典視圖是基于數(shù)據(jù)字典基表建立的視圖，普通用戶不能訪問數(shù)據(jù)字典的基表，但可能查詢數(shù)據(jù)字典視圖取得相關系統(tǒng)信息。數(shù)據(jù)字典視圖的命名主要有user_ tables、all_ tables和dba_tables三種。user_tables屬于當前用戶的表all_ tables所有當前用戶可以訪問的表dba_tables(dba才可以查詢)所有方案的表用戶、權限、角色對應的數(shù)據(jù)字典用戶dba_users系統(tǒng)權限dba_sys_privs角色dba_role_privs對象權限dba_tab_privs列權限dba_col_privs查詢SCOTT用戶的角色select * from dba_role_privs where GRANTEE=SCOTT;查詢Oracle中的所有角色select * from dba_roles;查詢Oracle中的所有系統(tǒng)權限select * from system_privilege_map;查詢Oracle中的表空間select distinct privilege from dba_tab_privs;管理表空間和數(shù)據(jù)文件表空間是數(shù)據(jù)庫的邏輯組成部分。從物理上講，數(shù)據(jù)庫數(shù)據(jù)存放在數(shù)據(jù)文件(最大500MB)中，從邏輯上講，數(shù)據(jù)庫則是存放在表空間中。表空間是由一個或多個數(shù)據(jù)文件組成。默認表空間為SYSTEM，該表空間不能更改狀態(tài)。Oracle中邏輯結(jié)構包括表空間、段、區(qū)和塊。表空間分為段、段分為區(qū)、區(qū)又分為塊。1. 創(chuàng)建表空間create tablespace ext datafile D:ext.dbf size 20M unoform size 128K;2. 使用表空間create table newexttab(userid number(4),username varchar2(8),usersex char(2),passwd varchar2(16) tablespace ext;3. 改變表空間狀態(tài)脫機/聯(lián)機/只讀/讀寫alter tablespace ext offline/online/read only/read write;4. 改變表空間大小(1). 增加數(shù)據(jù)文件alter tablespace ext add datafile D:exts.dbf size 20M;(2). 改變數(shù)據(jù)文件大小alter tablespace ext D:ext.dbf resize 20M;(3). 設置數(shù)據(jù)文件自動增長alter tablespace ext D:ext.dbf autoextend on next 10M maxsize 500M;5. 查看表空間中的表select * from all_tables where tablespace_name=ext;6. 查看表所在表空間select tablespace_name,table_name from user_tables where table_name=emp;7. 刪除表空間drop tablespace ext including contents and datafiles;8. 移動數(shù)據(jù)文件(1). 確定數(shù)據(jù)文件所在的表空間select * from dba_data_files;(2). 脫機正在使用的表空間alter tablespace ext offline;(3). 物理移動數(shù)據(jù)文件到指定位置host move D:ext.dbf F:ext.dbf(4). 邏輯移動數(shù)據(jù)文件alter tablespace ext rename datafile D:ext.dbf to F:ext.dbf;(5). 聯(lián)機表空間alter tablespace ext online;Oracle數(shù)據(jù)完整性約束、觸發(fā)器和應用程序(過程、方法)約束:not nulluniqueprimary keyforeign keycheck例如商店售貨系統(tǒng)表的設計如下:1. 創(chuàng)建商品表create table goods(goodsId char(8) primary key,-主鍵 goodsName varchar2(30), uniquePrice number(7,2) check (uniquePrice 0), category varchar2(8), provider varchar2(30);2. 創(chuàng)建客戶表create table customer(customerId char(8) primary key, name varchar2(20) nor null, sex char(2) default 男 check (sex in (男,女), address varchar2(50), email varchar2(50) unique, cardId char(18);3. 創(chuàng)建銷售表create table purchase(customerId char(8) references customer(customerId), goodsId char(8) references goods(goodsId), nums number(2) check (nums between 1 and 30);4. 增加goods表中goodsName的非空約束alter table goods modify goodsName not null;5. 增加客戶表中的cardId唯一約束alter table customer add constraint cardUnique unique(cardId);6. 增加客戶表address的check約束alter table customer add constraint addressCheck check(address in(address1,address2,address3);增加約束時應注意，除not null用modify外，其他四種約束都用add constraint constraintName來定義。7. 刪除約束alter table customer drop constraint constraintName;8. 刪除主鍵約束時，需要使用cascade選項。如刪除goods表中的goodsId字段時，應使用alter table goods drop primary key cascade;9. 約束的表級定義和列級定義(1). 表級定義表定義完后再定義約束的形式。create table emp(empno char(4),empName varchar2(12),deptno char(4), constraint pk_emp primary key (empno), constraint fk_dept foreign key(deptno) references dept(deptno);(2). 列級定義定義完一列后馬上定義約束。create table emp(empno char(4) constraint pk_emp primary key, empName varchar2(12),deptno char(4), constraint pk_emp primary key (empno);Oracle索引1. 單列索引create index enameIndex on emp(ename);2. 復合索引create index name_job on emp(ename,job);3. 索引的分類按存儲方式B*樹(適用于重復性少的列)、反向索引、位圖索引(重復值多的列)按索引列數(shù)單列索引、復合索引按列唯一性唯一索引、非唯一索引此外還有函數(shù)索引、全局索引、分區(qū)索引等。管理權限和角色1. 權限(前面也有講過權限，可前后結(jié)合起來。)(1). 授權系統(tǒng)權限grant create session,create table to scott with admin option;(2). 授權角色權限grant select on emp to scott with grant option;grant update on emp(sal) to scott;注意:系統(tǒng)權限的傳遞用with admin option，而對象權限的傳遞用with grant option；系統(tǒng)權限的傳遞者的系統(tǒng)權限被revoke后，傳遞者傳遞后的系統(tǒng)權限依然可用，而對象權限的傳遞者對象權限被revoke后，傳遞者傳遞后的對象權限同時被revoke了。2. 角色常用預定義角色connect、resource、dba(不含啟動和關閉數(shù)據(jù)庫權限)創(chuàng)建角色(不驗證:以后修改角色時不需要驗證身份)create role roleName not identified;-創(chuàng)建不驗證角色create role roleName identified by adminsa;-創(chuàng)建帶驗證的角色角色授權-例:有三個用戶，現(xiàn)在要授權給這三個用戶登錄數(shù)據(jù)庫和查、刪、改scott.emp的權限。SQLcreate user user1 identified by adminsa;SQLcreate user user2 identified by adminsa;SQLcreate user user3 identified by adminsa;SQLgrant create session to roleName with admin option;SQLgrant select,update,delete on scott.emp to roleName;SQLgrant roleName to user1;SQLgrant roleName to user2;SQLgrant roleName to user3;注意:SQLdrop role roleName;后三個用戶將不再具有該角色對應的權限。PL/SQL基礎1. 創(chuàng)建存儲過程(1). 簡單的存儲過程create or replace procedure scott_upd(uname varchar2,upass varchar2) isbeginupdate scott set passwd=upass where name=uname;end;(2). 塊declare-聲明變量 v_ename varchar2(5); v_sal number(7,2);begin-執(zhí)行部分 select ename,sal into v_ename,v_sal from scott.emp where empno=&empno; dbms_output.put_line(The salary of |v_ename| is |v_sal|.);-例外處理 exception when no_data_found then dbms_output.put_line(Sorry,the number you typed can not be found!);end;2. 調(diào)用存儲過程使用關鍵詞call或exec;3. 函數(shù)-創(chuàng)建函數(shù)create function GetSal(inName varchar2)return number isyearSal number(7,2);begin-執(zhí)行部分select (sal+nvl(comm,0)*12 into yearSal from emp where ename=inName;return yearSal;end;-sqlplusw中調(diào)用函數(shù)-定義接收函數(shù)返回值的變量var income number;-調(diào)用函數(shù)call GetSal(SCOTT) into:income;4. 包-創(chuàng)建包規(guī)范create package packOne isprocedure update_sal(name varchar2,newsal number);function GetSal(name varchar2) return number;end;-創(chuàng)建包體create package body packOne isprocedure update_sal(name varchar2,newsal number)isbeginupdate emp set sal=newsal where ename=name;end;function GetSal(name varchar2)return number isyearSal number(7,2);beginselect (sal+nvl(comm,0)*12 into yearSal from empwhere ename=name;return yearSal;end;end;5. PL/SQL中定義并使用常量和變量(1). 標量類型(scalar) 定義一個變長字符串v_ename varchar2(10); 定義一個小數(shù) 范圍-9999.99-9999.99v_sal number(6,2); 定義一個小數(shù)并賦給初始值v_sal number(6,2):=5.4注:在PL/SQL中賦值號:= 定義一個日期類型變量v_hireDate date; 定義一個布爾變量 初始值為False且不能為空v_valid boolean not null default false; 輸入員工編號，顯示員工姓名、工資、個人所得稅(稅率為3%)。declarec_taxrate number(3,2):=0.03; -v_ename的類型和scott方案下的emp表中的ename字段的類型一致v_ename scott.emp.ename%type;v_sal number(5,2);v_taxsal number(7,2);beginselect ename,sal into v_ename,v_sal from emp where empno=&no;v_taxsal:=v_sal*c_taxrate;dbms_output.put_line(姓名:|v_ename|,工資:|v_sal|,應繳稅款:|v_taxsal);end;(2). 復合類型(composite) pl/sql記錄 類似高級語言中的結(jié)構體declaretype emp_record_type is record(name scott.emp.ename%type,salary scott.emp.sal%type,jobname scott.emp.job%type)emp_record emp_record_type;beginselect ename,sal,job into emp_record from scott.emp where empno=&no;dbms_output.put_line(姓名:|emp_);end; pl/sql表 類似高級語言中的數(shù)組(下標可以為負數(shù))declaretype emp_table_type is table of scott.emp.ename%typeindex by binary_integer;-下標為整數(shù)emp_table emp_table_type;beginselect ename into emp_table(0) from emp where empno=&no;dbms_out.put_line(姓名:|emp_table(0);end; 嵌套表 varray(動態(tài)數(shù)組)(3). 參照類型(reference) 游標變量(ref cursor)輸入部門編號,顯示所有該部門員工的姓名和工資declare-定義一個游標類型type emp_cursor_type is ref cursor;-定義一個游標變量emp_cursor emp_cursor_type;v_ename scott.emp.ename%type;v_sal scott.emp.sal%type;begin-執(zhí)行-把emp_cursor和一個select結(jié)合open emp_cursor for select ename,sal from scott.emp where deptno=&no;-循環(huán)取出loopfetch emp_cursor into v_ename,v_sal;-判斷emp_cursor是否為空exit when emp_cursor%notfound;dbms_output.p