第 3 講： SQL.

1、1 / 107 第三部分第三部分 SQL 2 / 107 l SQL數(shù)據(jù)庫的結(jié)構(gòu)數(shù)據(jù)庫的結(jié)構(gòu) l SQL語句分類語句分類 3 / 107 SQL數(shù)據(jù)庫的結(jié)構(gòu)數(shù)據(jù)庫的結(jié)構(gòu) SQL數(shù)據(jù)庫的結(jié)構(gòu)基本上是三級(jí)結(jié)構(gòu)，也數(shù)據(jù)庫的結(jié)構(gòu)基本上是三級(jí)結(jié)構(gòu)，也 可以說它支持關(guān)系數(shù)據(jù)庫三級(jí)模式結(jié)構(gòu)可以說它支持關(guān)系數(shù)據(jù)庫三級(jí)模式結(jié)構(gòu) 外模式外模式 模式模式 內(nèi)模式內(nèi)模式 4 / 107 一個(gè)一個(gè)SQL數(shù)據(jù)庫是表的集合；數(shù)據(jù)庫是表的集合； 一個(gè)基本表可以跨越一個(gè)或多個(gè)存儲(chǔ)文件；一個(gè)基本表可以跨越一個(gè)或多個(gè)存儲(chǔ)文件； 而一個(gè)存儲(chǔ)文件可以存放一個(gè)或多個(gè)基本表而一個(gè)存儲(chǔ)文件可以存放一個(gè)或多個(gè)基本表 ； 視圖是由基本表或其他視

2、圖導(dǎo)出；視圖是由基本表或其他視圖導(dǎo)出； 用戶可以用用戶可以用SQL語言對(duì)視圖進(jìn)行查詢。語言對(duì)視圖進(jìn)行查詢。 5 / 107 SQL語句分類語句分類 l Select查詢語句查詢語句 l DML語句（數(shù)據(jù)操作語言）語句（數(shù)據(jù)操作語言） Insert、 Update、 Delete、Merge l DDL語句（數(shù)據(jù)定義語言）語句（數(shù)據(jù)定義語言） Create、Alter、 Drop、Truncate l DCL語句（數(shù)據(jù)控制語言）語句（數(shù)據(jù)控制語言） Grant、Revoke l 事務(wù)控制語句事務(wù)控制語句 Commit 、Rollback、Savepoint 6 / 107 SQL數(shù)據(jù)庫的查詢數(shù)據(jù)

3、庫的查詢 數(shù)據(jù)庫的查詢時(shí)數(shù)據(jù)庫的核心操作，也是使用最頻繁的語句數(shù)據(jù)庫的查詢時(shí)數(shù)據(jù)庫的核心操作，也是使用最頻繁的語句 ，其基本格式為：，其基本格式為： SELECTALL/DISTINCT FROM WHERE GROUP BY ORDER BY ASC/DESC； 在語句中，凡是在在語句中，凡是在中的項(xiàng)是不可缺的，中的項(xiàng)是不可缺的， 中的項(xiàng)可根中的項(xiàng)可根 據(jù)需要省略。據(jù)需要省略。 7 / 107 簡(jiǎn)單的表操作簡(jiǎn)單的表操作 /創(chuàng)建一個(gè)簡(jiǎn)單的表 create table student( name varchar2(20), age number(3) ); /插入新記錄 insert into

4、student values(Tom,18); /查詢記錄 select * from student; 8 / 107 簡(jiǎn)單的表操作簡(jiǎn)單的表操作 /查看表結(jié)構(gòu) desc student; /刪除表 drop table student; /回復(fù)表 FLASHBACK TABLE student TO BEFORE DROP; / 永久刪除表 DROP TABLE student PURGE; /清空垃圾箱 PURGE RECYCLEBIN; 9 / 107 使用算術(shù)表達(dá)式使用算術(shù)表達(dá)式 在在Select語句中，對(duì)語句中，對(duì)NUMBER型數(shù)據(jù)可以使用算術(shù)運(yùn)算符型數(shù)據(jù)可以使用算術(shù)運(yùn)算符 創(chuàng)建表達(dá)

5、式。創(chuàng)建表達(dá)式。 select empno, ename,sal, sal*12 from emp; 算術(shù)運(yùn)算符算術(shù)運(yùn)算符 +-*/ 運(yùn)算優(yōu)先級(jí)運(yùn)算優(yōu)先級(jí) 乘法和除法的優(yōu)先級(jí)高于加法和減法乘法和除法的優(yōu)先級(jí)高于加法和減法 同優(yōu)先級(jí)運(yùn)算的順序是從左到右同優(yōu)先級(jí)運(yùn)算的順序是從左到右 表達(dá)式中可使用小括號(hào)強(qiáng)行改變運(yùn)算順序表達(dá)式中可使用小括號(hào)強(qiáng)行改變運(yùn)算順序 select empno, ename,sal, sal*12 +1000 from emp; select empno, ename,sal, sal*(12 +1000) from emp; 10 / 107 連接運(yùn)算符連接運(yùn)算符 連接運(yùn)算符

6、連接運(yùn)算符|可以把列與字符、或其它表達(dá)式連接在一可以把列與字符、或其它表達(dá)式連接在一 起起 ，得到一個(gè)新的字符串，實(shí)現(xiàn)，得到一個(gè)新的字符串，實(shí)現(xiàn)合成合成列的功能。列的功能。 用法舉例：用法舉例： select ename | is a | job from emp; select empno, ename | s annual salary is | sal*12 from emp; 11 / 107 使用字段別名使用字段別名 字段別名字段別名 重命名查詢結(jié)果中的字段，以增強(qiáng)可讀性重命名查詢結(jié)果中的字段，以增強(qiáng)可讀性 如果別名中使用特殊字符，或者是強(qiáng)制大小寫敏感需使用如果別名中使用特殊字符，或

7、者是強(qiáng)制大小寫敏感需使用 雙引號(hào)雙引號(hào) 語法格式：語法格式： SELECT | AS , . FROM ; 用法舉例：用法舉例： select empno as 員工編號(hào)員工編號(hào), ename 員工姓名員工姓名, sal*12 年年 薪薪 from emp; select empno, ename Ename, sal*12 Anual Salary from emp; 12 / 107 空值空值 什么是空值？什么是空值？ 空值是無效的，未指定的，未知的或不可預(yù)知的值。空值是無效的，未指定的，未知的或不可預(yù)知的值。 空值不等同于空格或者空值不等同于空格或者0。 13 / 107 在表達(dá)式中使用

8、空值在表達(dá)式中使用空值 算術(shù)表達(dá)式中如果出現(xiàn)空值，則整個(gè)表達(dá)式結(jié)果為空算術(shù)表達(dá)式中如果出現(xiàn)空值，則整個(gè)表達(dá)式結(jié)果為空 連接表達(dá)式中出現(xiàn)的空值被當(dāng)作一個(gè)空的（長(zhǎng)度為零的）連接表達(dá)式中出現(xiàn)的空值被當(dāng)作一個(gè)空的（長(zhǎng)度為零的） 字字 符串處理符串處理 select ename, sal, comm, ename| - |comm, sal+comm from emp; 14 / 107 去除重復(fù)行去除重復(fù)行 在缺省情況下，查詢結(jié)果中包含所有符合條件的記錄行，在缺省情況下，查詢結(jié)果中包含所有符合條件的記錄行， 包包 括重復(fù)行。括重復(fù)行。 select deptno from emp; 使用使用DISTI

9、NCT關(guān)鍵字可從查詢結(jié)果中清除重復(fù)行關(guān)鍵字可從查詢結(jié)果中清除重復(fù)行 select distinct deptno from emp; DISTINCT的作用范圍是后面所有字段的組合的作用范圍是后面所有字段的組合 select distinct deptno, job from emp; 15 / 107 查詢結(jié)果排序查詢結(jié)果排序 查詢結(jié)果缺省按照記錄的插入順序進(jìn)行排列查詢結(jié)果缺省按照記錄的插入順序進(jìn)行排列,也可使用也可使用ORDER BY 子句對(duì)查詢結(jié)果進(jìn)行排序，排序方子句對(duì)查詢結(jié)果進(jìn)行排序，排序方 式包括升序（式包括升序（ASC，缺，缺 ?。┖徒敌颍ㄊ。┖徒敌颍―ESC）兩種：）兩種： se

10、lect empno, ename, sal from emp order by sal; select empno, ename, sal from emp order by sal desc ; 按多字段排序按多字段排序 select deptno, empno, ename, sal from emp order by deptno, sal; 使用字段別名排序使用字段別名排序 select empno, ename, sal*12 annsal from emp order by annsal; 16 / 107 條件查詢條件查詢 語法格式語法格式 SELECT * | DISTINC

11、T | , . FROM WHERE ; 如果如果 查詢語句中使用字符串和日期查詢語句中使用字符串和日期 字符串和日期值要用字符串和日期值要用單引號(hào)單引號(hào)擴(kuò)起來擴(kuò)起來 字符串大小寫敏感字符串大小寫敏感 日期值格式敏感，缺省的日期格式是日期值格式敏感，缺省的日期格式是 DD-MON-RR select * from emp where ename = SMITH; select * from emp where hiredate = 02-4月月-81; 獲取當(dāng)前缺省日期格式獲取當(dāng)前缺省日期格式 select sysdate from dual; 17 / 107 比較運(yùn)算符比較運(yùn)算符 運(yùn)算符運(yùn)

12、算符含義含義 =等于等于 大于大于 =大于等于大于等于 小于小于 =小于等于小于等于 不等于不等于 select * from emp where deptno 20; 18 / 107 比較運(yùn)算符（比較運(yùn)算符（2） 運(yùn)算符運(yùn)算符含義含義 between and 介于兩值之間介于兩值之間 in出現(xiàn)在集合中出現(xiàn)在集合中 like模糊查詢模糊查詢 is null為空值為空值 select * from emp where sal between 2500 and 3000; select * from emp where ename in (SMITH,BLAKE); 19 / 107 模糊查詢模

13、糊查詢 用用like運(yùn)算符執(zhí)行模糊查詢運(yùn)算符執(zhí)行模糊查詢 % 表示表示 0 或者多個(gè)字符或者多個(gè)字符 _ 表示一個(gè)字符表示一個(gè)字符 對(duì)于特殊符號(hào)可以使用對(duì)于特殊符號(hào)可以使用ESCAPE來查找來查找 select * from emp where ename like S_I%; select * from emp where ename like %_% escape ; 20 / 107 判斷空值判斷空值 使用使用is null 運(yùn)算符進(jìn)行空值判斷運(yùn)算符進(jìn)行空值判斷 select * from emp where comm is null; 21 / 107 邏輯運(yùn)算符邏輯運(yùn)算符 運(yùn)算符運(yùn)算

14、符含義含義 AND邏輯邏輯”與與“ OR邏輯邏輯”或或“ NOT邏輯邏輯“非非” select ename from emp where deptno =20 and sal2000; select * from emp where deptno not in(20,30,40) ; 22 / 107 oracle基本數(shù)據(jù)類型基本數(shù)據(jù)類型 23 / 107 24 / 107 函數(shù)函數(shù) Oracle函數(shù)分為單行函數(shù)和多行函數(shù)兩大類函數(shù)分為單行函數(shù)和多行函數(shù)兩大類 單行函數(shù)分類單行函數(shù)分類 字符函數(shù)字符函數(shù) 數(shù)值函數(shù)數(shù)值函數(shù) 日期函數(shù)日期函數(shù) 轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù) 通用函數(shù)通用函數(shù) 25 / 107

15、字符字符 函數(shù)函數(shù) 函數(shù)函數(shù)功能功能用法用法返回結(jié)果返回結(jié)果 lower()轉(zhuǎn)換為小寫轉(zhuǎn)換為小寫lower(Join Smith)join smith upper()轉(zhuǎn)換為大寫轉(zhuǎn)換為大寫upper (Join Smith)JOIN SMITH initcap()單詞首字母大寫單詞首字母大寫initcap (JOIN Smith)Join Smith concat() 字符串連結(jié)字符串連結(jié)concat(Join Smith)JoinSmith substr()截取字字符串截取字字符串substr(Join Smith,2,3)oin length()返回字符串長(zhǎng)度返回字符串長(zhǎng)度length(Jo

16、in Smith)10 instr()定位字串定位字串instr(Join Smith,mi)7 lpad()左側(cè)填充左側(cè)填充lpad(Join,10,*)*Join rpad()右側(cè)填充右側(cè)填充rpad(Join ,10,*)Join* trim()過濾首尾空格過濾首尾空格trim ( Join Smith )Join Smith replace()替換替換replace (Smith,m,oo)Sooith 26 / 107 數(shù)值數(shù)值 函數(shù)函數(shù) 函數(shù)函數(shù)功能功能用法用法返回結(jié)果返回結(jié)果 abs()取絕對(duì)值取絕對(duì)值abs(-3.14)3.14 round()四舍五人四舍五人round (3.

17、1415,2) round (314.15,-2) 3.14 300 trunc()截?cái)嘟財(cái)鄑runc(3.1415,3)3.141 ceil()向上取整向上取整ceil(3.14)4 floor()向下取整向下取整floor(3.14)3 sign()判斷數(shù)值正負(fù)判斷數(shù)值正負(fù)sign(-3.14)-1 sin()三角函數(shù)三角函數(shù)sin(3.14).001592653 power()冪運(yùn)算冪運(yùn)算power(3,2)9 sqrt()開平方根開平方根sqrt(9)3 mod()取模取模mod(10,3)1 exp()基數(shù)為基數(shù)為e的冪運(yùn)算的冪運(yùn)算exp(1)2.71828183 log()對(duì)數(shù)運(yùn)算

18、對(duì)數(shù)運(yùn)算log(4,16.0)4 ln()自然對(duì)數(shù)運(yùn)算自然對(duì)數(shù)運(yùn)算ln(7)1.94591015 27 / 107 日期類型日期類型 Oracle內(nèi)部以數(shù)字格式存儲(chǔ)日期和時(shí)間信息：世紀(jì)內(nèi)部以數(shù)字格式存儲(chǔ)日期和時(shí)間信息：世紀(jì),年年,月月 ,日日,小時(shí)小時(shí),分鐘分鐘,秒秒 缺省的日期格式是缺省的日期格式是 DD-MON-YY 可使用可使用sysdate函數(shù)獲取當(dāng)前系統(tǒng)日期和時(shí)間函數(shù)獲取當(dāng)前系統(tǒng)日期和時(shí)間 日期型數(shù)據(jù)可以直接加或減一個(gè)數(shù)值，結(jié)果仍為日期日期型數(shù)據(jù)可以直接加或減一個(gè)數(shù)值，結(jié)果仍為日期 兩個(gè)日期型數(shù)據(jù)可以相減，結(jié)果為二者相差多少天兩個(gè)日期型數(shù)據(jù)可以相減，結(jié)果為二者相差多少天 28 / 1

19、07 日期日期 函數(shù)函數(shù) 函數(shù)函數(shù)功能功能用法用法 add_months(x,y)計(jì)算在日期計(jì)算在日期x基礎(chǔ)上基礎(chǔ)上 增加增加y個(gè)月后的日期個(gè)月后的日期 add_months(sysdate,2) last_day(x)返回日期返回日期x當(dāng)月最后當(dāng)月最后 一天的日期一天的日期 last_day(sysdate) months_between(x,y)返回日期返回日期x和和y之間之間 相差的月數(shù)相差的月數(shù) months_between(sysdate,hiredate ) round(x,y)將日期將日期x四舍五入到四舍五入到 y所指定的日期單位所指定的日期單位 （月或年（月或年)的第一天的第一

20、天 round(sysdate,month) round(sysdate,year) trunc(x,y)將日期將日期x截?cái)嗟浇財(cái)嗟統(tǒng)所所 指定的日期單位（指定的日期單位（ 月或年月或年)的第一天的第一天 trunc(sysdate,month) trunc(sysdate,year) next_day(x,y)計(jì)算指定日期計(jì)算指定日期x后的后的 第一個(gè)星期第一個(gè)星期y對(duì)應(yīng)的對(duì)應(yīng)的 日期日期 next_day(sysdate, 星期二星期二) 29 / 107 轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù) 數(shù)據(jù)類型轉(zhuǎn)換包括顯式的和隱含數(shù)據(jù)轉(zhuǎn)換兩種，建議使用數(shù)據(jù)類型轉(zhuǎn)換包括顯式的和隱含數(shù)據(jù)轉(zhuǎn)換兩種，建議使用 顯式的轉(zhuǎn)換顯式

21、的轉(zhuǎn)換 數(shù)值類型數(shù)值類型字符類型字符類型日期類型日期類型 to_number()to_date() to_char()to_char() 30 / 107 日期轉(zhuǎn)字符串日期轉(zhuǎn)字符串 格式：格式： to_char(date) 轉(zhuǎn)換為轉(zhuǎn)換為dd-mm-yy格式格式 to_char(date,format model)轉(zhuǎn)換為指定格式轉(zhuǎn)換為指定格式 select ename, to_char(hiredate,yyyy-mm-dd) hiredate from emp; 31 / 107 通用函數(shù)通用函數(shù) 通用函數(shù)適用于任何類型數(shù)據(jù)（包括空值）：通用函數(shù)適用于任何類型數(shù)據(jù)（包括空值）： nvl ()

22、nvl2 () nullif () coalesce () case表達(dá)式表達(dá)式 decode() 32 / 107 nvl()函數(shù)函數(shù) nvl()函數(shù)用于將空值函數(shù)用于將空值null替換為指定的缺省值，適用于字符替換為指定的缺省值，適用于字符 ，數(shù)值，日期等類型數(shù)據(jù)，數(shù)值，日期等類型數(shù)據(jù) 語法格式：語法格式： nvl(exp1, exp2) 說明：如果表達(dá)式說明：如果表達(dá)式exp1為為null，則返回，則返回exp2的值，否則的值，否則 返回返回exp1的值。的值。 用法舉例：用法舉例： select ename, sal + nvl(comm,0) from emp; 33 / 107 D

23、ECODE()函數(shù)函數(shù) 和和case表達(dá)式類似，表達(dá)式類似，decode()函數(shù)也用于實(shí)現(xiàn)多路分支結(jié)構(gòu)函數(shù)也用于實(shí)現(xiàn)多路分支結(jié)構(gòu) 語法格式：語法格式： decode(col|expression, search1, result1 , search2, result2,., , default) 用法舉例：用法舉例： select empno, ename, sal, decode(deptno, 10, 財(cái)務(wù)部財(cái)務(wù)部, 20, 研發(fā)部研發(fā)部, 30, 銷售部銷售部, 未知部門未知部門) 部門部門 from emp; 34 / 107 函數(shù)嵌套函數(shù)嵌套 單行函數(shù)可以嵌套使用，嵌套層次無限制單行

24、函數(shù)可以嵌套使用，嵌套層次無限制 嵌套函數(shù)的執(zhí)行順序是由內(nèi)到外嵌套函數(shù)的執(zhí)行順序是由內(nèi)到外 select empno, lpad(initcap(trim(ename),10, ) name, job, sal from emp; 35 / 107 分組函數(shù)分組函數(shù) 分組函數(shù)對(duì)一組數(shù)據(jù)進(jìn)行運(yùn)算，針對(duì)一組數(shù)據(jù)（多行記錄分組函數(shù)對(duì)一組數(shù)據(jù)進(jìn)行運(yùn)算，針對(duì)一組數(shù)據(jù)（多行記錄 ） 只返回一個(gè)結(jié)果，也稱多行函數(shù)。只返回一個(gè)結(jié)果，也稱多行函數(shù)。 常用分組函數(shù)常用分組函數(shù) 函數(shù)函數(shù)功能說功能說 明明適用類型適用類型 avg() 計(jì)算平均值計(jì)算平均值數(shù)值型數(shù)值型 count() 返回查詢所得到的記錄行數(shù)返回查詢

25、所得到的記錄行數(shù)任何類型數(shù)據(jù)任何類型數(shù)據(jù) max() 計(jì)算最大值計(jì)算最大值任何類型數(shù)據(jù)任何類型數(shù)據(jù) min() 計(jì)算最小值計(jì)算最小值任何類型數(shù)據(jù)任何類型數(shù)據(jù) sum() 求和求和數(shù)值型數(shù)值型 select avg(sal), max(sal), min(sal), sum(sal) from emp; 36 / 107 COUNT()函數(shù)函數(shù) count(*)返回組中總記錄數(shù)目；返回組中總記錄數(shù)目； count(exp)返回表達(dá)式返回表達(dá)式exp值值非空的記錄數(shù)目；的記錄數(shù)目； count(distinct(exp)返回表達(dá)式返回表達(dá)式exp值不重復(fù)的、非空的記錄值不重復(fù)的、非空的記錄 數(shù)目。

26、數(shù)目。 select count(*) from emp; select count(comm) from emp; select count(distinct(deptno) from emp; 37 / 107 分組函數(shù)與空值分組函數(shù)與空值 分組函數(shù)分組函數(shù)省略列中的空值省略列中的空值 select avg(comm) from emp; select sum(comm) from emp; 可使用可使用NVL()函數(shù)強(qiáng)制分組函數(shù)處理空值函數(shù)強(qiáng)制分組函數(shù)處理空值 select avg(nvl(comm, 0) from emp; 38 / 107 GROUP BY子句子句 GROUP BY

27、 子句將表中數(shù)據(jù)分成若干小組子句將表中數(shù)據(jù)分成若干小組 語法格式語法格式 select column, group_function(column) from table where condition group by group_by_expression order by column; 說明：出現(xiàn)在說明：出現(xiàn)在SELECT列表中的字段，如果不是包含在組函列表中的字段，如果不是包含在組函 數(shù)中，那么該數(shù)中，那么該 字段必須同時(shí)在字段必須同時(shí)在GROUP BY子句中出現(xiàn)。子句中出現(xiàn)。 包含在包含在 GROUP BY子句中的字段則不必須出現(xiàn)在子句中的字段則不必須出現(xiàn)在SELECT 列表中。列

28、表中。 舉例舉例 select deptno, avg(sal) from emp group by deptno; 39 / 107 基于多個(gè)字段分組基于多個(gè)字段分組 select deptno, job, avg(sal) from emp group by deptno, job; 40 / 107 組函數(shù)的錯(cuò)誤用法組函數(shù)的錯(cuò)誤用法 如果沒有如果沒有GROUP BY子句，子句，SELECT列表中不允許出現(xiàn)字列表中不允許出現(xiàn)字 段（單行函數(shù)）與分組函數(shù)段（單行函數(shù)）與分組函數(shù)混用混用的情況。的情況。 select empno, sal from emp;/合法合法 select avg(s

29、al) from emp;/合法合法 select empno, initcap(ename), avg(sal) from emp;/非法非法 不允許不允許在在 WHERE 子句中使用分組函數(shù)。子句中使用分組函數(shù)。 select deptno, avg(sal) from emp where avg(sal) 2000; group by deptno; 41 / 107 HAVING子句子句 HAVING子句用于過濾分組子句用于過濾分組 語法格式語法格式 selectcolumn, group_function(column) fromtable where condition group

30、 by group_by_expression having group_condition order by column; 舉例舉例 selectdeptno, job, avg(sal) fromemp wherehiredate = to_date(1981-05-01,yyyy- mm-dd) group by deptno,job having avg(sal) 1200 order by deptno,job; 42 / 107 分組函數(shù)嵌套分組函數(shù)嵌套 分組函數(shù)最多可嵌套兩層分組函數(shù)最多可嵌套兩層 select max(avg(sal) from emp group by de

31、ptno; 43 / 107 Oracle表連接表連接 SQL/Oracle使用表連接從多個(gè)表中查詢數(shù)據(jù)使用表連接從多個(gè)表中查詢數(shù)據(jù) 語法格式：語法格式： select字段列表字段列表 fromtable1, table2 wheretable1.column1 = table2.column2; 說明：說明： 在在Where子句中指定連接條件子句中指定連接條件 當(dāng)被連接的多個(gè)表中存在同名字段時(shí)，必須在該字當(dāng)被連接的多個(gè)表中存在同名字段時(shí)，必須在該字 段前加上段前加上表表 名名.作為前綴。作為前綴。 44 / 107 連接的類型連接的類型 Oracle8i之前的表連接：之前的表連接： - 等值

32、連接（等值連接（ Equijoin ） - 非等值連接（非等值連接（Non-Equijoin） - 外連接（外連接（Outer join） 左外連接左外連接 右外連接右外連接 - 自連接（自連接（Self join） Oracle9i新引入的的連接形式（支持新引入的的連接形式（支持SQL99規(guī)范）：規(guī)范）： - 交叉連接（交叉連接（Cross join） - 自然連接（自然連接（Natural join） - 使用使用Using子句建立連接子句建立連接 - 使用使用On子句建立連接子句建立連接 - 外連接（外連接（ Outer join ） 左外連接左外連接 右外連接右外連接 全外連接全外連接

33、 45 / 107 等值連接等值連接(Equijoin) select empno, ename, sal, emp.deptno, dname from emp, dept where emp.deptno = dept.deptno; 46 / 107 非等值連接非等值連接(Non-Equijoin) 問題：如何查得每個(gè)員工的工資等級(jí)？問題：如何查得每個(gè)員工的工資等級(jí)？ SALGRADE表（表（5行）行） EMP表（表（14行）行） 47 / 107 外連接外連接(Outer join) 使用外連接可以看到參與連接的某一方不滿足連接條件使用外連接可以看到參與連接的某一方不滿足連接條件 的記

34、錄。外連接運(yùn)算符為的記錄。外連接運(yùn)算符為(+) 傳統(tǒng)的外連接分為左外連接和右外連接兩種傳統(tǒng)的外連接分為左外連接和右外連接兩種 語法格式：語法格式： select字段列表字段列表 fromtable1, table2 wheretable1.column1 = table2.column2 (+); select字段列表字段列表 fromtable1, table2 wheretable1.column1(+) = table2.column2; 48 / 107 自連接自連接(Self join) 問題：如何查得每個(gè)員工及其上司的工號(hào)和姓名？問題：如何查得每個(gè)員工及其上司的工號(hào)和姓名？ 49

35、/ 107 交叉連接交叉連接(Cross join) Cross join產(chǎn)生了一個(gè)笛卡爾集，其效果等同于在兩個(gè)表進(jìn)產(chǎn)生了一個(gè)笛卡爾集，其效果等同于在兩個(gè)表進(jìn) 行連接時(shí)未使用行連接時(shí)未使用WHERE子句限定連接條件子句限定連接條件; 舉例：舉例： select empno, ename, sal, emp.deptno, dname from emp cross join dept; 50 / 107 自然連接自然連接(Natural join) Natural join基于兩個(gè)表中的基于兩個(gè)表中的全部同名列全部同名列建立連接建立連接 從兩個(gè)表中選出同名列的值均對(duì)應(yīng)相等的所有行從兩個(gè)表中選出同

36、名列的值均對(duì)應(yīng)相等的所有行 如果兩個(gè)表中同名列的數(shù)據(jù)類型不同，則出錯(cuò)如果兩個(gè)表中同名列的數(shù)據(jù)類型不同，則出錯(cuò) 不允許在參照列上使用表名或者別名作為前綴不允許在參照列上使用表名或者別名作為前綴 舉例：舉例： select empno, ename, sal, deptno, dname from emp natural join dept; 51 / 107 Using子句子句 如果不希望參照被連接表的如果不希望參照被連接表的所有所有同名列進(jìn)行等值連接，自同名列進(jìn)行等值連接，自 然然 連接將無法滿足要求，可以在連接時(shí)使用連接將無法滿足要求，可以在連接時(shí)使用USING子句來子句來 設(shè)置用于等值連接

37、的列（參照列）名。設(shè)置用于等值連接的列（參照列）名。 舉例：舉例： select empno, ename, sal, deptno, dname from emp join dept using (deptno); 不允許在參照列上使用表名或者別名作為前綴不允許在參照列上使用表名或者別名作為前綴 52 / 107 On子句子句 如果要參照如果要參照非同名的列非同名的列進(jìn)行等值連接，或想設(shè)置任意的連進(jìn)行等值連接，或想設(shè)置任意的連 接接 條件，可以使用條件，可以使用ON子句。子句。 舉例：舉例： select empno, ename, sal, emp.deptno, dname from e

38、mp join dept on (emp.deptno = dept.deptno); 53 / 107 子查詢子查詢(Sub Query) 問題引入問題引入 如何查得所有比如何查得所有比“張三張三”工資高的員工的信息工資高的員工的信息 子查詢子查詢 子查詢?cè)谥鞑樵兦皥?zhí)行一次子查詢?cè)谥鞑樵兦皥?zhí)行一次 主查詢使用子查詢的結(jié)果主查詢使用子查詢的結(jié)果 語法格式：語法格式： select 字段列表字段列表 from table where 表達(dá)式表達(dá)式 operator (select 字段列表字段列表 from table); 54 / 107 子查詢的種類子查詢的種類 單行子查詢：子查詢結(jié)果返回單

39、行單行子查詢：子查詢結(jié)果返回單行 多行子查詢：子查詢結(jié)果返回多行多行子查詢：子查詢結(jié)果返回多行 55 / 107 使用子查詢注意事項(xiàng)使用子查詢注意事項(xiàng) 在查詢是基于未知值時(shí)應(yīng)考慮使用子查詢?cè)诓樵兪腔谖粗禃r(shí)應(yīng)考慮使用子查詢 子查詢必須包含在括號(hào)內(nèi)子查詢必須包含在括號(hào)內(nèi) 建議將子查詢放在比較運(yùn)算符的右側(cè)，以增強(qiáng)可讀性建議將子查詢放在比較運(yùn)算符的右側(cè)，以增強(qiáng)可讀性 。 除非進(jìn)行除非進(jìn)行Top-N 分析，否則不要在子查詢中使用分析，否則不要在子查詢中使用 ORDER BY 子句。子句。 對(duì)單行子查詢使用單行運(yùn)算符對(duì)單行子查詢使用單行運(yùn)算符 對(duì)多行子查詢使用多行運(yùn)算符對(duì)多行子查詢使用多行運(yùn)算符 56

40、 / 107 單行子查詢單行子查詢 單行子查詢只返回一行記錄單行子查詢只返回一行記錄 對(duì)單行子查詢可使用單行記錄比較運(yùn)算符對(duì)單行子查詢可使用單行記錄比較運(yùn)算符 舉例：舉例： select * from emp where sal (select sal from emp where empno = 7566); 57 / 107 子查詢空值子查詢空值/多值問題多值問題 如果子查詢未返回任何行，則主查詢也不會(huì)返回任何結(jié)果如果子查詢未返回任何行，則主查詢也不會(huì)返回任何結(jié)果 select * from emp where sal (select sal from emp where empno =

41、8888); 如果子查詢返回單行結(jié)果，則為單行子查詢，可以在主查如果子查詢返回單行結(jié)果，則為單行子查詢，可以在主查 詢中對(duì)其使用相應(yīng)的單行記錄比較運(yùn)算符詢中對(duì)其使用相應(yīng)的單行記錄比較運(yùn)算符 select * from emp where sal (select sal from emp where empno = 7566); 如果子查詢返回多行結(jié)果，則為多行子查詢，此時(shí)不允許如果子查詢返回多行結(jié)果，則為多行子查詢，此時(shí)不允許 對(duì)其使用單行記錄比較運(yùn)算符對(duì)其使用單行記錄比較運(yùn)算符 select * from emp where sal (select avg(sal) from emp gro

42、up by deptno); /非法非法 58 / 107 多行子查詢多行子查詢 含義含義 運(yùn)算符運(yùn)算符 IN等于列表中的任何一個(gè)等于列表中的任何一個(gè) ANY和子查詢返回的任意一個(gè)值比較和子查詢返回的任意一個(gè)值比較 ALL和子查詢返回的所有值比較和子查詢返回的所有值比較 59 / 107 多行子查詢多行子查詢 對(duì)多行子查詢只能使用多行記錄比較運(yùn)算符對(duì)多行子查詢只能使用多行記錄比較運(yùn)算符 舉例：舉例： select * from emp where sal any(select avg(sal) from emp group by deptno); select * from emp where

43、 sal all(select avg(sal) from emp group by deptno); select * from emp where job in (select job from emp where ename = MARTIN or ename = SMITH); 60 / 107 TopN查詢查詢 在在ORACLE中通常采用子查詢的方式來實(shí)現(xiàn)中通常采用子查詢的方式來實(shí)現(xiàn)TOP N查詢查詢 語法格式：語法格式： select 字段列表字段列表 from (select 字段列表字段列表 from table order by 排序字段排序字段 ) where rownum

44、 = n; 舉例：舉例： select * from (select * from emp order by sal desc) where rownum =0 and age =120); 92 / 107 查看約束查看約束 查詢用戶字典視圖查詢用戶字典視圖user_constraints 可得到用戶的所有約束可得到用戶的所有約束 查詢用戶字典視圖查詢用戶字典視圖user_cons_columns 可獲知約束建立在哪些字段上可獲知約束建立在哪些字段上 93 / 107 建表后添加約束建表后添加約束 基本語法基本語法 alter table tablename add constraint c

45、onstraint_name constraint_type (column); 舉例：舉例： create table student( sid number(10), name varchar2(20); alter table student add constraint student_sid_pk primary key(sid); 特例：特例：非空約束必須使用非空約束必須使用modify子句添加子句添加 alter table student modify (name char(15) default n/a not null); 94 / 107 刪除約束刪除約束 基本語法基本語

46、法 alter table tableName drop constraint constraint_name; 舉例：舉例： create table student( sid number(10) , name varchar2(20), constraint student_sid_pk primary key(sid) ); alter table student drop constraint student_sid_pk ; 刪除主鍵約束的另一種方式：刪除主鍵約束的另一種方式： alter table tableName drop primary key; alter table

47、student drop primary key ; 95 / 107 視圖視圖 什么是視圖什么是視圖(View)？ 視圖由一個(gè)或多個(gè)表（或視圖）中提取數(shù)據(jù)而成視圖由一個(gè)或多個(gè)表（或視圖）中提取數(shù)據(jù)而成 視圖是一種虛擬表視圖是一種虛擬表 視圖一經(jīng)創(chuàng)建，可以當(dāng)作表來使用。視圖一經(jīng)創(chuàng)建，可以當(dāng)作表來使用。 96 / 107 視圖（續(xù)）視圖（續(xù)） 使用視圖的好處使用視圖的好處 簡(jiǎn)化復(fù)雜數(shù)據(jù)查詢簡(jiǎn)化復(fù)雜數(shù)據(jù)查詢 提高運(yùn)行效率提高運(yùn)行效率 屏蔽數(shù)據(jù)庫表結(jié)構(gòu)，實(shí)現(xiàn)數(shù)據(jù)邏輯獨(dú)立性屏蔽數(shù)據(jù)庫表結(jié)構(gòu)，實(shí)現(xiàn)數(shù)據(jù)邏輯獨(dú)立性 限制數(shù)據(jù)庫訪問限制數(shù)據(jù)庫訪問 在相同數(shù)據(jù)上提供不同的視圖，便于數(shù)據(jù)共享在相同數(shù)據(jù)上提供不同的

48、視圖，便于數(shù)據(jù)共享 簡(jiǎn)單視圖和復(fù)雜視圖簡(jiǎn)單視圖和復(fù)雜視圖 特征項(xiàng)目特征項(xiàng)目簡(jiǎn)單視圖簡(jiǎn)單視圖復(fù)雜視圖復(fù)雜視圖 基表數(shù)目基表數(shù)目一個(gè)一個(gè)一個(gè)或多個(gè)一個(gè)或多個(gè) 使用函數(shù)使用函數(shù)無無有有 使用分組使用分組無無有有 支持支持DML操作操作支持支持不一定不一定 97 / 107 創(chuàng)建創(chuàng)建/刪除視圖刪除視圖 創(chuàng)建視圖創(chuàng)建視圖 通過在通過在create view語句中嵌入子查詢的方式創(chuàng)建視圖語句中嵌入子查詢的方式創(chuàng)建視圖 基本語法：基本語法： create or replace view schema. view (alias,aliasx.) as subquery; 舉例：舉例： create or re

49、place view myview1(編號(hào)編號(hào), 姓名姓名, 職位職位, 工資工資) as select empno, ename, job, sal from emp where deptno = 20; 查看視圖結(jié)構(gòu)查看視圖結(jié)構(gòu) desc myview1; 刪除視圖刪除視圖 drop view myview1; 98 / 107 更新視圖更新視圖 在可更新視圖上進(jìn)行在可更新視圖上進(jìn)行DML操作，可以修改基表中數(shù)據(jù)操作，可以修改基表中數(shù)據(jù) 可更新視圖的定義中不能使用分組函數(shù)、可更新視圖的定義中不能使用分組函數(shù)、group by子句子句 、 distinct關(guān)關(guān) 鍵字、鍵字、rownum偽列，

50、字段的定義不能為表達(dá)偽列，字段的定義不能為表達(dá) 式式 由兩個(gè)以上基表中導(dǎo)出的視圖不可更新由兩個(gè)以上基表中導(dǎo)出的視圖不可更新 基表中非空的列在視圖定義中未包括，則不可在視圖上進(jìn)基表中非空的列在視圖定義中未包括，則不可在視圖上進(jìn) 行行insert操作操作 在視圖上進(jìn)行在視圖上進(jìn)行DML操作，語法與在表上操作相同操作，語法與在表上操作相同 insert update delete 99 / 107 創(chuàng)建只讀視圖創(chuàng)建只讀視圖 在創(chuàng)建視圖時(shí)，可使用在創(chuàng)建視圖時(shí)，可使用with read only選項(xiàng)將之設(shè)置為只選項(xiàng)將之設(shè)置為只 讀讀 語法格式語法格式 create or replace force |

51、noforce view schema. view (alias,aliasx.) as subquery with read only; 舉例：舉例： create or replace force view myview2 as select empno, ename, job, sal from emp2 where deptno = 20 with read only; 100 / 107 索引索引 什么是索引什么是索引(index)？ 一種用于提升查詢效率的數(shù)據(jù)庫對(duì)象；一種用于提升查詢效率的數(shù)據(jù)庫對(duì)象； 通過快速定位數(shù)據(jù)的方法，減少磁盤通過快速定位數(shù)據(jù)的方法，減少磁盤 I/O操作；操

52、作； 索引信息與表獨(dú)立存放；索引信息與表獨(dú)立存放； Oracle 數(shù)據(jù)庫自動(dòng)使用和維護(hù)索引。數(shù)據(jù)庫自動(dòng)使用和維護(hù)索引。 索引分類索引分類 唯一性索引唯一性索引 非唯一索引非唯一索引 創(chuàng)建索引的兩種方式創(chuàng)建索引的兩種方式 自動(dòng)創(chuàng)建自動(dòng)創(chuàng)建 - 在定義主鍵或唯一鍵約束時(shí)系統(tǒng)會(huì)自動(dòng)在在定義主鍵或唯一鍵約束時(shí)系統(tǒng)會(huì)自動(dòng)在 相應(yīng)的字段相應(yīng)的字段 上創(chuàng)建唯一性索引。上創(chuàng)建唯一性索引。 手動(dòng)創(chuàng)建手動(dòng)創(chuàng)建 - 用戶可以在其它列上創(chuàng)建非唯一的索引，用戶可以在其它列上創(chuàng)建非唯一的索引， 以加速查詢。以加速查詢。 101 / 107 創(chuàng)建創(chuàng)建/刪除索引刪除索引 可使用可使用create index語句手動(dòng)創(chuàng)建索引語句手動(dòng)創(chuàng)建索引 create index schema.index on table (column, column.); create index myindex on emp(ename); 刪除索引刪除索引 使用使用drop index 語句刪除索引語句刪除索引 操作者須是索引的所有者、或擁有操作者須是索引的所有者、或擁有drop該該