精品IT教程Oracle經(jīng)典學習

1、.Oracle & SQL基本介紹：OracleDBOracle公司 也提供應用系統(tǒng)；我們涉及的是數(shù)據(jù)庫管理系統(tǒng)DBMS（多用戶系統(tǒng)）；數(shù)據(jù)庫操作語言SQL結構化查詢語言；SQL操作對象為 DB中的數(shù)據(jù)，表現(xiàn)形式為庫和表。Oracle用表管理表系統(tǒng)表(數(shù)據(jù)字典)用戶表：telnet 3 通過sqlplus命令 與數(shù)據(jù)庫建立連接，才可操作DDL 、DML環(huán)境變量： PATH 中要加入sqlplus所在的目錄 ORACLE_HOME Oracle安裝目錄 ORACLE_SID 表示連接的數(shù)據(jù)庫 /一定要設置，否則無法連數(shù)據(jù)庫 sqlplus sd0701/sd0701

2、 SQL> 表示這是SQL環(huán)境1. telnet 32. 用戶 兩個環(huán)境變量：ORACLE_HOME ORACLE_SID 數(shù)據(jù)庫實例的名字 PATH 中追加 $ORACLE_HOME/bin3. sqlplus口令比如命令： show user /看登錄的用戶名 sqlplus命令一、Oracle數(shù)據(jù)庫中常用的數(shù)據(jù)類型varchar2(長度) 可變長字符串 date 日期類型char(長度) 定長 number() 表示整數(shù)或者浮點數(shù)clob 字符的大對象blob 二進制的大對象select table_name from user_tables; /查看當前有那

3、些表desc s_emp /查看表結構 以上兩條很有用二、數(shù)據(jù)庫查詢1、SELECT語句從表中提取查詢數(shù)據(jù).語法為：SELECT DISTINCT column1,column2, FROM tablename WHERE conditions GROUP BY conditions ORDER BY expressions ASC/DESC; 說明：SELECT子句用于指定檢索數(shù)據(jù)庫的中哪些列，F(xiàn)ROM子句用于指定從哪一張表或視圖中檢索數(shù)據(jù)。注：distinct會觸發(fā)排序2、WHERE子句。WHERE子句用來選擇符合條件的記錄。between . and . 表示結果在這之間，between

4、 and是一個閉區(qū)間；!=，<>，= 這三個都可以表示不等于；in (va1,val2,.) 判斷結果是否在這個集合中存在； in等價于：=any 注意順序會影響執(zhí)行效率like '.' 表示字符串通配查詢，'%'表示0或多個字符， '_' 表示一個字符； 注意字符大小寫敏感 知道轉義的用法：like S_% escape . and . 表示只有兩個條件同時滿足；. or . 表示條件只要滿足其中之一就可以；all . 是要求都滿足條件；not . 可以與以上的條件產(chǎn)生相反的效果； not in 等價于： <>all 注

5、意空值的影響，空值與任何值比較結果都為空!. is null 用來判斷值是否為空。3、ORDER BY子句ORDER BY 子句使得SQL在顯示查詢結果時將各返回行按順序排列，返回行的排列順序由ORDER BY 子句指定的表達式的值確定。 ASC（默認，升序） DESC（降序）order by 目標列名（別名） 排序順序（不寫則默認為升序）例：select first_name from s_emp order by first_name; /默認為升序select first_name from s_emp order by first_name desc; /降序三、SQL常用的命令分類及

6、例子create（創(chuàng)建）alter（更改）drop（刪除）數(shù)據(jù)定義語言<操作表的結構>： DDL必須要掌握，使用頻度高！insert（插入）select（選擇）delete（刪除）update（更新）命令數(shù)據(jù)操縱語言<操作表的數(shù)據(jù)>： DML記住，事務處處流淌在我們的應用中！commit（提交）savepoint（保存點）rollback（回滾）事務控制語言： TCL數(shù)據(jù)控制語言：grant（授予）和revoke（回收）。與權限有關 DCL1、數(shù)據(jù)定義語言DDL舉例： SQL> create table myTab(no number(4),name varcha

7、r2(20); /創(chuàng)建一個名為myTab的表，包含兩列分別為no和name；SQL> alter table myTab modify (name varchar2(25); /修改myTab中的name列，使此列能容納25個字符；SQL> alter table myTab add (tel_no varchar2(20); /給表myTab增加一列tel_no；SQL> alter table myTab drop column tel_no; /刪除表myTab的tel_no列;SQL> drop table myTab; /刪除表myTab；SQL> tr

8、uncate table myTab; /刪除表myTab中的所有行（截斷表）,注意:此操作不可以rollback。2、數(shù)據(jù)操縱語言DML舉例：SQL> insert into myTab values(001,John); /向表myTab中插入一行數(shù)據(jù)；SQL> select distinct salary “薪水” from s_emp where salary>1500 order by sal desc; /選擇表中salary大于1500的數(shù)據(jù)，以別名“薪水”顯示并按照salary的降序進行排列輸出；SQL> create table empa as sel

9、ect empno,ename,job,sal from emp;/從emp表中選擇“empno,ename,job,sal”四列的數(shù)據(jù)建立新表empa;SQL> create table empa as select * from emp where 1=2;/使用一個假條件根據(jù)現(xiàn)有表emp創(chuàng)建一個只包含結構的空表empa； SQL> delete from empa where sal<1500;/刪除表empa中sal小于1500的行；SQL> update empa set sal=1500 where sal<1500; /更新，將表empa中sal小于

10、1500的行的sal值全部改為1500。3、事務控制語言TCL舉例：SQL> commit; /用于提交并結束事務處理；SQL> savepoint mark1; /保存點類似于標記，用來標記事務中可以應用回滾的點；SQL> rollback to savepoint mark1; /回滾到保存點mark1。四、Oracle數(shù)據(jù)庫函數(shù)注意:dual表(虛表)是專門用于函數(shù)測試和運算的.單行函數(shù)1、字符函數(shù) 字符是大小寫敏感的 轉小寫 lower(字段名) 轉大寫 upper(字段名) 首字母大寫 initcap(字段名) 字符串拼接 concat(字段1, 字段2) 截取子串

11、 substr(字段名, 起始位置，取字符個數(shù)) 字符串長度 length() 忽略空值 nvl()例: select first_name,substr(first_name,2,2) sub from s_emp;（從名字的第二個字符開始取兩個字符）select first_name,substr(first_name,-2,2) sub from s_emp;（從名字的倒數(shù)第二個字符開始取兩個字符）2、數(shù)值函數(shù) 四舍五入函數(shù) round(數(shù)據(jù),保留到小數(shù)點后幾位) 1表示保留到小數(shù)點后一位，-1表示保留到小數(shù)點前一位。 例：select round(15.36,1) from dual;

12、 截取數(shù)值函數(shù) trunc(數(shù)據(jù)，保留到小數(shù)點后幾位) 例：select trunc(123.456,1) from dual;截取到小數(shù)點后一位,注意:與round函數(shù)不同,不會四舍五入。3、日期函數(shù) 缺省日期格式：日-月-年 dd-mon-rr 修改當前會話的日期格式，會按照指定的格式輸出日期 alter session set nls_date_format='yyyy mm dd hh24:mi:ss' 返回當前日期 sysdate 世紀、年、月、日、時、分、秒 例：select sysdate from dual; 另外，date 類型可以運算:select sysd

13、ate-1,sysdate,sysdate+1 from dual; 單位為1天select sysdate,sysdate+1/144 from dual; 加10分鐘兩個日期相減表示 相隔多少天。使用函數(shù)：select trunk(add_months(sysdate,1),MONTH) from dual; /取下個月首天4、不同數(shù)據(jù)類型間轉換函數(shù) 將日期轉成特定格式字符串 tochar(date,'日期格式') 日期格式要用有效格式，格式大小寫敏感 'yyyy mm dd hh24:mi:ss'(標準日期格式),'year'(年的全拼),

14、'month'(月的全拼)，'day'(星期的全拼)，'ddspth' (日期的全拼) 例：select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from dual;select to_char(sysdate,'year month day ddspth')from dual; /大小寫敏感select to_char(sysdate,'YEAR MONTH DAY DDSPTH')from dual; 將字符串轉成日期 to_date('.'

15、;,'日期格式') 例：select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from dual；五、表連接（關聯(lián)查詢）等值連接 select table1.column1，table2.column2 from table1 t1，table2 t2 where t1.column3=t2.column4; 表連接時，當表與表之間有同名字段時，可以加上表名或表的別名，加以區(qū)分，使用時要用表名.字段名或表別名.字段名（列名）。當表的字段名是唯一時，可以不用

16、加上表名或表的別名。注意：當為表起了別名，就不能再使用表名.字段名了。例如：select e.first_name | | e.last_name name, dept_namefrom s_emp e, s_dept dwhere e.dept_id=d.id;非等值連接 select 表別名1.字段名1，表別名2.字段名2,. from 表1 表別名1 ，表2 表別名2 where 表別名1.字段名3 . 表別名2.字段名4 .可以使比較運算符，也可以使其他的除了'='的運算符例：select first_name, salaryfrom s_emp wher

17、e salary between 1000 and 2000;自連接 把一個表的兩個字段關系轉換成兩個表字段之間的關系. select 表別名1.字段名1，表別名2.字段名2,. from 表1 表別名1 ，表1 表別名2 where 表別名1.字段名3=表別名2.字段名4;例：select a.first_name ename,b.first_name cname from s_emp a,s_emp b where a.manager_id=b.id; 以上三種連接為內連接，會嚴格匹配！外連接 在加(+)的這一邊模擬數(shù)據(jù)與之匹配。使用一張表中的所有記錄去和另一張表中的記錄按條件匹配(空值也

18、會匹配)，這個表中的所有記錄都會顯示。*要記錄一個都不能少，則在對方的那一邊加(+) 1. LEFT OUTER JOIN：左外連接select e.last_name, e.dept_id, from s_emp e left outer join s_dept d on (e.dept_id = d.id);掌握哪個部門沒有員工的寫法，用外連接。select e.deptno, d.deptno from emp e, dept dwhere e.deptno(+) = d.deptnoand e.deptno is nullselect e.last_name, e.dep

19、t_id, from s_emp e, s_dept d where e.dept_id=d.id(+);等價于 結果為：所有員工及對應部門的記錄，包括沒有對應部門編號dept_id的員工記錄。 select e.last_name, from s_emp e, s_dept d where e.dept_id(+)=d.id;select e.last_name, from s_emp e right outer join s_dept d on (e.dept_id = d.id);2. RIGHT OUTER JOIN：右外連接結果為：所有員工及對

20、應部門的記錄，包括沒有任何員工的部門記錄。 select e.dept_id,d.id from s_emp e, s_dept d where e.dept(+) = d.id(+) select e.dept_id,d.id from s_emp e full outer join s_dept d on (e.dept_id = d.id);3. FULL OUTER JOIN：全外關聯(lián)結果為：所有員工及對應部門的記錄，包括沒有對應部門編號department_id的員工記錄和沒有任何員工的部門記錄。六、組函數(shù)group by把 select 查詢的結果集分成幾個小組，這個group b

21、y 子句可以跟在 select 語句后面或是 having前面。group by子句也會觸發(fā)排序操作，會按分組字段排序。select 組函數(shù)或分組的字段名. from 表名 group by 字段名1,字段名2,.；例：select avg(salary) from s_emp group by dept_id; 注意： 組函數(shù)會忽略空值，但是count(*)除外，他會把空記錄也統(tǒng)計在內。 avg和sum這兩個函數(shù)的參數(shù)只能是number型的。 count、max、min可以使用任意類型做參數(shù)。 max(.), min(.)求最大值和最小值， count(*)統(tǒng)計表中記錄數(shù)。例：select

22、max(), avg(a.salary), max() from s_emp a, s_dept b, s_region c where a.dept_id=b.id and b.region_id=c.id group by b.id;注意：只要寫了group by子句，select后就只能用group by之后的字段或者是組函數(shù)。having子句可以過濾組函數(shù)結果或是分組的信息，并且寫在group by子句后。七、子查詢可以嵌在sql語句中的select語句。在select語句中嵌套子查詢時，會先執(zhí)行子查詢，返回結果再執(zhí)行主查詢。一般的會將子查詢放在運算符的右邊。注

23、意：在使用子查詢時，要注意這個運算符是單行的（也就是只能是單值）比如=要確認返回的是單行；還是多行運算符（范圍，多值.比如in）。配合使用子查詢返回的結果必須符合運算符的用法。例:select first_name|' '|last_name name / 用 | 表示字符串拼接from s_empwhere title in (select title from s_emp where dept_id=42);查詢和42部門員工職位相同的所有員工的姓名.在select后加子查詢： 誰的工資比本部門的平均工資高？select e.first_name, e.salary, a.

24、avgsalfrom s_emp, (select dept_id,avg(salary) avgsal from s_emp group by dept_id) awhere e.dept_id = a.dept_idand e.salary > a.avgsal范式：1NF 關系中每一分量不可再分。即不能以集合、序列等作為屬性值2NF 有一個字段唯一且非空的，比如ID。但是數(shù)據(jù)很可能冗余，會造成數(shù)據(jù)不一致3NF 非主屬性間無依賴關系了。在2NF的基礎上拆表。比如分為 student表 class表八、約束針對表中的字段進行定義的。1、primary key（主鍵約束 PK）保證實體的

25、完整性，保證記錄的唯一。主鍵約束，唯一且非空，并且每一個表中只能有一個主鍵，有兩個字段聯(lián)合作為主鍵時，將兩個字段組合在一起唯一標識記錄，叫做聯(lián)合主鍵。主鍵約束的定義：第一種定義形式：create table test(c number primary key ); 列級約束第二種定義形式：create table test(c number , primary key(c) ) ; 表級約束create table test(c1 number constraints pk_c1 primary key ); 此約束有名字: pk_c1create table test(c number ,

26、c1 number , primary key (c ,c1) ) ; 用表級約束可以實現(xiàn)聯(lián)合主鍵2、foreign key（外鍵約束 FK）保證引用的完整性，外鍵約束，外鍵的取值是受另外一張表中的主鍵或唯一值的約束，不能夠取其他值，只能夠引用主鍵或唯一鍵的值，被引用的表，叫做parent table（父表），引用方的表叫做child table（子表），要想創(chuàng)建子表，就要先創(chuàng)建父表；記錄的插入也是如此，先父表后子表；刪除記錄，要先刪除子表記錄，后刪除父表記錄；要修改記錄，如果要修改父表的記錄要保證沒有被子表引用。要刪表時，要先刪子表，后刪除父表。(可以通過使用cascade constrai

27、nts 選項來刪除父表)carete table parent(c1 number primary key ); 列級約束create table child (c number primary key , c2 number references parent(c1);或表級約束定義:create table child( c number primary key , c2 number , foreign key(c2) references parent(c1);3、not null（非空約束NN）這是一個列級約束,在建表時,在數(shù)據(jù)類型的后面加上 not null ，也就是在插入時不允許

28、插入空值。例：create table student(id number primary key,name varchar2(32) not null,address varchar2(32);4、unique（唯一約束UK）唯一約束，允許為空，要求插入的記錄中的值是唯一的。例：create table student(id number，name varchar2(32),address varchar2(32),primary key (id),unique (address);check約束檢查約束，可以按照指定條件，檢查記錄的插入。check中不能使用偽列，不能使用函數(shù)，不能引用其他

29、字段。例：create table sal (a1 number , check(a1>1000);九、數(shù)據(jù)字典 DD數(shù)據(jù)字典是由系統(tǒng)維護的，包含數(shù)據(jù)庫的信息數(shù)據(jù)字典視圖user_XXXXX 用戶視圖all_XXXXX 所有視圖dba_XXXXX 數(shù)據(jù)庫中所有視圖v$_XXXXX 動態(tài)性能視圖dict或 dictionary 表示數(shù)據(jù)字典的數(shù)據(jù)字典。user_constraints 用戶的表中管理約束的表其中有constraints_name字段存放的是約束名，constraint_type字段存放的是約束的類型, r_constraints_name字段表示外鍵引用自何處.user_c

30、ons_column表，是用戶的列級約束表, column_name字段存放的是約束字段的名字, position字段存放的是約束在聯(lián)合鍵中的位置.十、事務 Transaction原子操作，也就是不可分割的操作，必須一起成功一起失敗。事務的結束動作就是commit； DDL, DCL語句執(zhí)行會自動提交commit。sqlplus正常退出是會做提交動作的commit;，當系統(tǒng)異常退出時，會執(zhí)行回滾操作rollback;。一個沒有結束的事務，叫做活動的事務 (active transaction),活動的事務中修改的數(shù)據(jù)時，只有本會話(session)才能看見。十一、Oracle中的偽列偽列就像O

31、racle中的一個表列，但實際上它并未存儲在表中。偽列可以從表中查詢，但是不能插入、更新或刪除它們的值。常用的偽列：rowid和rownum。rowid：數(shù)據(jù)庫中的每一行都有一個行地址，rowid偽列返回該行地址?？梢允褂胷owid值來定位表中的一行。通常情況下，rowid值可以唯一地標識數(shù)據(jù)庫中的一行。rowid偽列有以下重要用途：1）能以最快的方式訪問表中的一行；2）能顯示表的行是如何存儲的。3）可以作為表中行的唯一標識。如：SQL> select rowid,ename from emp;rownum：對于一個查詢返回的每一行，rownum偽列返回一個數(shù)值代表的次序。rownum偽

32、列特點：1） 有個特點要么等于1 要么小于某個值， 不能直接等于某個值, 不能大于某個值。2）常用于分頁顯示。返回的第一行的rownum值為1，第二行的rownum值為2，依此類推。通過使用rownum偽列，用戶可以限制查詢返回的行數(shù)。如：SQL>select * from emp where rownum<11; 從emp表中提取10條記錄。十二、序列(sequence)：create sequence 序列名;(不帶參數(shù)時默認為從1 開始每次遞增 1，oracle中為了提高產(chǎn)生序列的效率一般一次性產(chǎn)生20個序列放入當前會話的序列池中備用以加快效率)sequence 的參數(shù)：in

33、crement by n 起始值start with n 遞增量 maxvalue n 最大值 minvalue n 最小值cycle|no cycle 循環(huán) cache n 緩存(第一次取時會一次取多少個id存起來)查看sequence 視圖：desc user_sequences ;select sequence_name , cache_size , last_number from user_sequences where sequence_name like 's_'select 序列名.currval from dual /查看當前的序列數(shù)select 序列名.ne

34、xtval from dual /查看下一個序列數(shù)，它會自動給當前的序列加drop sequence 序列名; /刪除序列sequence十三、 視圖(View)視圖就相當于一條select 語句,定義了一個視圖就是定義了一個sql語句, 視圖不占空間,使用視圖不會提高性能，但是能簡化sql語句創(chuàng)建視圖： creating views視圖名;如：create or replace views test as select * from test1 where c1=1;create or replace:如果view存在就覆蓋，不存在才創(chuàng)建。force|no force:基表存在時使用，不存在時則創(chuàng)建該表。注意:向視圖中插入數(shù)據(jù)時，會直接插進基表中，查看視圖中的數(shù)據(jù)時，相當于就是執(zhí)行創(chuàng)建時的select語句。刪除視圖:drop views視圖名;視圖的約束:with read only視圖只讀約束with check option 不允許插入與where條件不符的記錄，類似于check約束的功能.create view test_ccasselect * from testw