Oracle數(shù)據(jù)庫筆記1

1、java企業(yè)級項目開發(fā)班oracle 10g課堂筆記2015年10月30日目 錄第一章：oracle 10g介紹一、oracle 10g安裝二、oracle 10g 設(shè)置命令開始運行 輸入 sqlplus打開oracel操作客戶端登錄用戶： scott 密碼:tiger在輸入用戶名： 用戶名/密碼注銷登錄： quit 可以退出當前登錄狀態(tài)從當前賬戶切換到另一個指定賬戶上connect 用戶名/密碼對一個賬戶進行鎖定和解鎖操作第一步： 以管理員的身份登錄，然后去解鎖普通鎖定的用戶說明需要登錄的用戶被鎖定，必須通過解鎖后才能登錄解鎖命令： alter user 普通賬戶名 account unlo

2、ck|lock;unlock ：解鎖 lock：鎖定注意：必須在管理員身份登錄后才能進行解鎖或鎖定操作清理屏幕host cls 將當前屏幕清理對命令窗口進行屬性的修改：為了方便顯示數(shù)據(jù)將命令窗口的寬： 300 個字符， 行：9999設(shè)置查詢數(shù)據(jù)顯示的一行字符數(shù)：set linesize 300;注意：每一次進入客戶端進行數(shù)據(jù)顯示的時候，都必須重新設(shè)定設(shè)置一頁顯示行數(shù)set pagesize 30; 注意：每一次進入客戶端進行數(shù)據(jù)顯示的時候，都必須重新設(shè)定若system用戶被鎖定，怎么樣來進行解鎖鎖定systemSystem/managerAlter user system account lo

3、ck；解鎖systemSystem/manager as sysdbaAlter user system account unlock;登錄 sys賬戶Sys/admin as sysoperSql語言： sql89、sql92、sql99結(jié)構(gòu)化的查詢語言：定義功能（DDL）: create drop alter 主要對于結(jié)構(gòu)進行修改操作功能（DML）:insert update delete 主要對記錄進行修改查詢功能（DQL）:select 查詢控制功能（DCL）:第二章：SQL查詢語言的語法Scott用戶下表結(jié)構(gòu)：雇員表字段名類型字段中文說明EmpnoNumber(4) 主鍵雇員編號En

4、ameVarchar2(10) 雇員姓名JobValurchar2(9)職稱MgrNumber(4)領(lǐng)導編號Hiredatedate入職日期SalNumber(7,2)工資CommNumber(7,2)獎金DeptnoNumber(2)部門編號部門表字段名類型字段中文說明DeptnoNumber(2)部門編號DnameVarchar2(14)部門名稱LocVarchar2(13)地址工資等級表字段名類型字段中文說明GradeNumber工資等級LosalNumber最低工資HisalNumber最高工資SQL查詢語言：一、 簡單查詢SELECT ALL|DISTINCT<字段名列表>

5、;|常量|表達式 Select <所求內(nèi)容> ：查詢結(jié)果中的列Select <所求內(nèi)容> from <來自的表名列表>查詢出雇員表的所有信息Select * from emp;* :代表指定表的所有字段名列表select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;Select后面：可以跟指定的字段名列表，每一個字段之間用逗號隔開查詢出雇員姓名和工資信息（投影）select ename as 雇員姓名,sal 工資 from emp;Select后面查詢某一個字段，給結(jié)果字段重新取名（別名）A)

6、 字段名 as 別名B) 字段名 空格 別名可以一個表取別名Select a.empno,a.ename from emp a;通常給表名取別名： 表名 空格 別名 給字段取別名 ，字段名 as 別名Ed命令的使用（可以將sql語句寫在指定的文本文件中，然后引入執(zhí)行）Ed 回車會自動修改上一條執(zhí)行過sql語句，并且打開文件后可以對語句進行修改，修改后保存按/回車就可以執(zhí)行修改好的命令Ed 文件名 回車文件名：是一指定的文件，若文件存在，直接打開，若不存在會提示創(chuàng)建該文件可以引入外部寫好sql語句文件直接執(zhí)行 文件名 回車查詢出雇員表的職稱，重復出現(xiàn)只顯示一次Select distinct jo

7、b from emp;Select all|distinct ：all:默認情況，將重復記錄顯示出來 Distinct ：去掉查詢結(jié)果中的重復記錄重復記錄：水平上一橫數(shù)據(jù)都一模一樣將雇員表按下列格式顯示出來雇員編號為：7369 雇員姓名：jack 職稱是：manager 入職日期：select 'empno: '|empno|' ename:'|ename|' job: '|job|' mgr: '|mgr |' sal: '|sal from emp;Select 后面可以出現(xiàn)常量,和其他不連接用 “|”該常量所

8、在位置，是結(jié)果中一個列，并且其值是其本身查詢出雇員的年薪select ename as 雇員姓名,sal*12 as 年薪 from empSelect后面可能出現(xiàn)表達式，查詢不會改變原表中的值，若結(jié)果中的值需要改變時，必須在select后面跟表達式二、限定查詢格式： select <所求內(nèi)容> from <表名列表>where <篩選條件>所求內(nèi)容： distinct <字段名列表><常量><表達式>查詢出雇員工資大于或等于3000的所有雇員信息思路：篩選條件：sal >= 3000所求內(nèi)容：empno,ename

9、,job,mgr,hiredate,sal,comm,deptno所用的表：empselect empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal >= 3000;書寫條件：字段名 運算符 具體的值查詢出JONES員工的信息思路：篩選條件：ename = JONES所求內(nèi)容： *所用的表： empSelect * from emp where ename = JONES注意問題：1. 查詢的值是字符型，寫值的時候必須使用定界符單引號內(nèi)2. 查詢值必須分大小寫，sql語句書寫的其他地方部分大小寫查詢出工資大于等于1

10、000并小于等于2500之間的所有員工信息思路：篩選條件：sal >= 1000 and sal <=2500所求內(nèi)容：*所用的表：empselect * from emp where sal>=1000 and sal <=2500與運算：必須兩個條件都要同時滿足的記錄才會顯示在結(jié)果中條件1 and 條件2 and 條件3查詢出工資在3000以上，或職稱為MANAGER的所有職工篩序條件：sal >3000 or job = MANAGER所求內(nèi)容：*所用的表：empselect * from emp where sal > 3000 or job = &

11、#39;MANAGER'或運算：表示只要滿足其中一個條件即可條件1 or 條件2 or條件3查詢出職稱為MANAGER或SALESMAN，并且工資在2000到3000之間（含）的雇員信息。思路：篩選條件：(job=MANAGER OR JOB=SALESMAN) AND (SAL>=2000 AND SAL<=3000)所求內(nèi)容：*所用的表：empselect * from emp where (job='MANAGER' OR JOB='SALESMAN') AND (SAL>=800 AND SAL<=3000);注意：運算過

12、程中and優(yōu)先級別高于or的，注意加括號來改變優(yōu)先級別設(shè)置日期顯示的格式：alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'查詢出獎金為空的所有員工信息篩選條件: comm is null Select * from emp where comm is null;查詢獎金不為空的雇員信息Select * from emp where comm is not null;查詢出職稱為 MANAGER,SALESMAN,CLERK的雇員信息做法1： Select * from emp where job='MAN

13、AGER' OR JOB='SALESMAN' OR JOB='CLERK'做法2：Select * from emp where job in('MANAGER', 'SALESMAN', 'CLERK');字段名 in|not in (值的列表)In： 屬于Not in：不屬于查詢出以雇員姓名中包含有N字符的所有雇員信息select * from emp where ename like '%N%'模糊查詢條件： 字段名 like 帶有通配符的字段值通配符： %：可以和任意多個字符相匹配

14、（匹配即相等） _:可以和任意一個字符相匹配查詢出雇員姓名中第二個字符為I的所有雇員信息select * from emp where ename like '_I%'查詢出工資在1000到3000之間的職工 姓名和職稱思路：篩選條件： sal >=1000 and sal <=3000所求內(nèi)容： ename，job所用的表： empSelect ename，job from emp where sal >=1000 and sal <=3000查詢出工資在1000到2000之間（含）的所有雇員信息字段名 between <下限值> and

15、<上限值> ：寫在where后面，表示指定字段的值在上下限值之間并且包含上下限值。select * from emp where sal>=1000 and sal<=2000;select * from emp where sal between 1000 and 2000;Oracle數(shù)據(jù)庫中的單行函數(shù)單行函數(shù)：只對一個值進行操作單行函數(shù)的語法單行函數(shù)名(字段名|表達式,參數(shù)1,參數(shù)2,)參數(shù)說明:Fuction_name 函數(shù)名稱column 列名expression 表達式arg1,arg2, 參數(shù)單行函數(shù)分類:字符函數(shù)：接收字符輸入并返回字符或數(shù)值數(shù)值函數(shù)：接

16、受數(shù)據(jù)輸入并返回數(shù)值日期函數(shù)：對日期型數(shù)據(jù)進行操作轉(zhuǎn)換函數(shù)：從一種數(shù)據(jù)類型轉(zhuǎn)換為另外一種數(shù)據(jù)類型通用函數(shù)：NVA 函數(shù) DECODE 函數(shù)字符函數(shù)：接收字符輸入并返回字符或數(shù)值1、lower(字符表達式)函數(shù)原型： lower(字符表達式) 返回：字符串功能：將指定字符串轉(zhuǎn)化為小寫 注意：偽表 dual例題1：select lower('HELLO WORLD!') from dual;例題2：查詢出job中含有G字母的雇員姓名,職稱（g字母大小寫不區(qū)分）select ename as 雇員姓名,job as 職稱 from emp where lower(job) like

17、'%g%'2、upper(字符表達式) 函數(shù)原型： upper(字符表達式) 返回值類型：字符型功能：將小字母轉(zhuǎn)換為大寫字母select upper('hello world!') as result from dual;3、initcap(字符表達式) 函數(shù)原型：initcap(字符表達式) 返回值類型： 字符型功能：將制定字符串的首字母轉(zhuǎn)換為大寫字母，其余字符小寫select initcap('hello WORLD!') as result from dual;4、concat(字符串表達式1,字符串表達式2)函數(shù)原型： concat(字符

18、串表達式1,字符串表達式2) 返回值類型： 字符串功能： 將指定兩個字符串進行收尾連接，最終成為一個字符串和 <字符表達式1 >| <字符表達式2> 等價，字符常量必須寫在一對單引號之間select concat('hello','world') as result from dual;select 'hello'|'world' as result from dual;select concat(ename,job) as result from emp;5、substr(字符表達式,起始位置,長度)函數(shù)

19、原型：substr(字符表達式,起始位置,長度)功能：從指定的字符表達式中取出從起始位置到規(guī)定長度的子串注意：1） 改函數(shù)若省略,長度，從起始位置一直取到字符串的尾部2） 起始位置：可以為正式時，從左邊開始計數(shù)，若為負數(shù)時，從右邊開始計數(shù)若起始位置的值為0或1的時候，從左邊第一個開始取子串（包含起始位置）3）column <字段名> format a<列寬> 功能：設(shè)置指定字段的顯示列寬，一遍將列名顯示完全6、length(字符表達式)函數(shù)原型：length(字符表達式) 返回值類型： 數(shù)值型功能：返回指定字符串的長度注意：漢字并不是兩個寬度查詢出雇員名字有6個字符組成

20、的所有雇員信息select * from emp where length(ename) = 6;7、replace(字符表達式1,字符表達式2,字符表達式3)函數(shù)原型： replace(字符表達式1,字符表達式2,字符表達式3) 返回值類型：字符型功能：在字符表達式1中尋找到字符表達式2的內(nèi)容時用字符表達式3來替換注意：1) 該函數(shù)區(qū)分大小寫2) 將字符表達式3用空串來代替時，可以刪除字符表達式1中的指定字符表達式28、chr(整數(shù))函數(shù)原型：chr(整數(shù)) 返回值類型：字符型 功能：將指定整數(shù)作為ASCII碼返回對應(yīng)的一個字符select chr(65) as result from du

21、al;9、ascii(字符)函數(shù)原型：ascii(字符) 返回值類型：數(shù)值型 功能：將指定字符的ASCII碼返回select ascii('A') from dual;數(shù)值型函數(shù)函數(shù)說明ABS（value）返回指定數(shù)值的絕對值CEIL（value）返回大于或等于Value的最小整數(shù)值FLOOR（value）返回小于或等于value的最大整數(shù)值COS（value）求余弦值COSH（value）求反余弦值EXP（value）返回以e為底的指數(shù)值LN（value）返回以value為底的自然對數(shù)LOG（x,y）返回以x為底y的對數(shù)POWER（value,exponent）求value的

22、exponent的指數(shù)值ROUND（value,precision）按value的precision精度四舍五入MOD（value,divisor）取余SQRT（value）返回value的平方根trunc(value)取整數(shù)部分，不會進行四舍五入操作1、abs(數(shù)值)函數(shù)原型Abs(數(shù)值表達式)功能返回數(shù)值表達式的絕對值例題select abs(-100) as result from dual;2、ceil(數(shù)值表達式)函數(shù)原型ceil(數(shù)值表達式)功能大于或等于數(shù)值表達式的最小整數(shù)例子3、Floot(數(shù)值表達式)函數(shù)原型Floot(數(shù)值表達式)功能小于或等于數(shù)值表達式的最大整數(shù)例題pow

23、er(數(shù)值表達式1，數(shù)值表達式2)函數(shù)原型power(數(shù)值表達式1，數(shù)值表達式2)功能求數(shù)值表達式1的數(shù)值表達式2次方例題Round(數(shù)值表達式,整數(shù)表達式)函數(shù)原型Round(數(shù)值表達式,整數(shù)表達式)功能將數(shù)值表達式1，保留整數(shù)個小數(shù)后得到的結(jié)果整數(shù)表達式2：個位為0，往整數(shù)部分走是負數(shù)，往小數(shù)部分走是正數(shù)表示例子 Mod(value,divisor)函數(shù)原型Mod(被除數(shù),除數(shù))功能返回兩個數(shù)的余數(shù)例題SQRT（value）函數(shù)原型SQRT（數(shù)值表達式）功能求出數(shù)值表達式的算術(shù)平方根例題日期函數(shù)函數(shù)說明ADD_MONTHS（date,count）指定日期 date上增加count個月LAS

24、T_DAY(date)返回日期date所在月的最后一天MONTHS_BETWEEN(date1,date2)返回date1到date2之間相隔的月數(shù)NEW_TIME(date,this,orther)指date由this時區(qū)轉(zhuǎn)為other時區(qū)NEXT_DAY(date,day)返回指定日期或最后一個星期幾的日期.day為星期幾SYSDATE獲取當前的系統(tǒng)日期CURRENT_TIMESTAMP()獲取當前的系統(tǒng)日期及時間add_months(日期表達式1,數(shù)值表達式)函數(shù)原型1） add_months(日期表達式1,數(shù)值表達式)功能在日期表達式的基礎(chǔ)上加上數(shù)值表達式個月份后的那個日期返回值類型日

25、期型注意Select sysdate from dual； 返回當前系統(tǒng)的日期例題今天日期基礎(chǔ)上加1個月后是那個日期2、Last_day(日期表達式)函數(shù)原型Last_day(日期表達式)功能返回日期表達式當前月份最后一天的日期返回值日期型 Months_between(日期表達式1，日期表達式2)函數(shù)原型Months_between(日期表達式1，日期表達式2)功能：求出日期表達式1和日期表達式2之間所相差的月數(shù)返回值類型數(shù)值型例題NEXT_DAY(日期表達式,指定星期幾)函數(shù)原型NEXT_DAY(日期表達式,指定星期幾)功能指定日期表達式下一個星期的指定星期幾的日期 返回值類型是，日期例題

26、類型轉(zhuǎn)換函數(shù)To_char()將數(shù)字或日期轉(zhuǎn)化為字符串函數(shù)原型To_char(日期表達式|數(shù)值表達式,格式)功能將數(shù)字或日期轉(zhuǎn)化為字符串當參數(shù)為日期時to_char()在使用的時候要用到格式控制的符,格式控制符不區(qū)分大小寫字母 年:Y,年份為四位數(shù)，故應(yīng)寫成:YYYY或yyyy 月:M,月份為兩位數(shù),故應(yīng)寫成:MM或mm 日:D，日為兩位數(shù)，故應(yīng)寫成:DD或dd格式前加fm表示去掉前導0日期當格式用yyyy單獨可以將指定日期的年份取出mm取出月份dd 取出日部分當參數(shù)為數(shù)值型時1） 格式可以用9或0來作為格式的限定2） 若數(shù)值表示的貨幣，可以中格式前加$符號，顯示值時，顯示前導$3） 若要顯示

27、￥貨幣符，應(yīng)用本地貨幣符，中前面加前導L4） 可以用 000,000,000來添加數(shù)值的千位符顯示返回值類型字符串類型to_char()在使用的時候要用到格式控制的符,格式控制符不區(qū)分大小寫字母 年:Y,年份為四位數(shù)，故應(yīng)寫成:YYYY或yyyy 月:M,月份為兩位數(shù),故應(yīng)寫成:MM或mm 日:D，日為兩位數(shù)，故應(yīng)寫成:DD或ddTo_number()將當前的固定格式的字符串轉(zhuǎn)化為數(shù)字能夠轉(zhuǎn)換的字符（09的數(shù)字字符 正負號，小數(shù)點，e字母看情況）To_date()將當前固定格式的字符串轉(zhuǎn)化為日期To_date(字符表達式,格式)查詢結(jié)果的排序：u ORDER BY 排序字段名1 DESC|AS

28、C,排序字段名2 DESC|ASCu 排序?qū)ψ詈蟮慕Y(jié)果進行排序，ORDER BY 一般寫中SQL語句的最后一個部分u 若排序字段有多個時，從左到右依次，若第一個排序字段有相同值時，然后按第二個排序字段區(qū)進行排序。u 默認是升序排序（ASC，也可以不寫ASC）按工資的降序顯示所有雇員信息select * from emp order by sal desc;先按部門的升序，部門相同的按工資的降序排序，若工資相同時再按入職時間升序顯示出雇員信息select * from emp order by deptno asc,sal desc,hiredate asc;SQL強化訓練選擇部門30中的所有職

29、工篩選條件：deptno = 30所求內(nèi)容：emp.*來自的表：empSelect 所求內(nèi)容 from 來自的表 where 篩選條件select emp.* from emp where deptno = 30;列出所有業(yè)務(wù)員(CLERK)的姓名，編號，和部門編號篩選條件： job = CLERK所求內(nèi)容：ename,empno,deptno所用的表：empselect ename,empno,deptno from emp where job='CLERK'找出獎金高于薪金的員工篩選條件：comm > sal所求內(nèi)容：emp.*來自的表：empSelect emp.*

30、 from emp where comm > sal;找出獎金高于薪金的60%的員工篩選條件：comm > sal*0.6所求內(nèi)容：*來自的表：empSelect * from emp where comm > sal*0.6;找出部門10中所有經(jīng)理(MANAGER)和部門20中所有業(yè)務(wù)員(CLERK)的詳細資料篩選條件：(deptno = 10 and job= MANAGER) or (deptno = 20 and job = CLERK )所求內(nèi)容： *來自的表: empselect * from emp where (deptno=10 and job='M

31、ANAGER') or (deptno = 20 and job='CLERK');找出部門10中所有經(jīng)理(MANAGER),部門20中所有業(yè)務(wù)員(CLERK),既不是經(jīng)理又不是業(yè)務(wù)員但其薪水大于等于2000的所有員工的詳細資料篩選條件：(deptno =10 and job =MANAGER) OR (deptno =20 and job=CLERK) OR (job!=MANAGER AND JOB !=CLERK AND SAL >=2000)所求內(nèi)容: *來自的表： empSELECT * FROM EMP WHERE (DEPTNO=10 AND JOB

32、='MANAGER') OR (DEPTNO = 20 AND JOB='CLERK') OR (JOB != 'MANAGER' AND JOB != 'CLERK' AND SAL >= 2000) ;找出收取傭金的員工的不同工作篩選條件： SAL IS NOT NULL所求內(nèi)容： JOB所用的表： empSELECT DISTINCT JOB FROM EMP WHERE SAL IS NOT NULL;找出不收取傭金或收取的傭金低于1000的員工篩選條件： sal is null or sal <1000所求內(nèi)

33、容： *所用的表： empselect * from emp where sal is null or sal<1000;找出各月倒數(shù)第3天受雇的所有員工篩選條件： hiredate = LAST_DAY(HIREDATE)-2 所求內(nèi)容: *來自的表：empselect * from emp where hiredate = last_day(hiredate)-2;找出早于12年前受雇的員工篩選條件：trunc(months_between(sysdate,hiredate)/12) > 12select * from emp where trunc(months_betwee

34、n(sysdate,hiredate)/12) > 12;以首字母大寫的方式顯示所有員工的姓名select initcap(ename) as ename from emp;顯示正好為5個字符的員工姓名select ename from emp where length(ename) = 5;顯示不帶有”R”的員工姓名select * from emp where NOT(ename like '%R%');select * from emp where ename not like '%R%'顯示所有員工姓名的前三個字符select substr(ena

35、me,1,3) as ename from emp;顯示所有員工的姓名，并用a替換所有Aselect replace(ename,'A','a') as ename from emp;顯示滿10年服務(wù)年限的員工姓名和受雇日期select ename,hiredate from emp where trunc(months_between(sysdate,hiredate)/12)>=10;顯示員工的詳細資料，按姓名由大到小排序select * from emp order by ename desc;顯示員工的姓名和受雇日期，根據(jù)其服務(wù)年限，將最老的員工排

36、在最前面select ename,hiredate from emp order by hiredate asc;select * from emp order by trunc(months_between(sysdate,hiredate)/12) desc;顯示所有員工的姓名，工作和薪金，按工作降序排列，若工作相同則按薪金升序排序select ename,job,sal from emp order by job desc,sal asc;顯示所有員工的姓名，加入公司的年份和月份，按受雇日期所在的月排序，若月份相同，則將最早年份排在最前面select ename,to_char(hire

37、date,'yyyy') as year,trunc(months_between(sysdate,hiredate) as month from emp order by to_char(hiredate,'fmmm'),year asc;select ename,to_char(hiredate,'yyyy') as year,trunc(months_between(sysdate,hiredate) as month from emp order by to_char(hiredate,'fmmm'),2 asc;顯示一個

38、月為30天的情況所員工的日薪金，忽略余數(shù)select trunc(sal/30) as 日薪金 from emp;找出在(任何年份)2月受聘的所有員工selct * from emp where to_char(hiredate,'fmmm') = '2'對每個員工，顯示其加入公司的天數(shù)select trunc(sysdate-hiredate) as 入職天數(shù) from emp;顯示姓名中任意位置包含“A”的所有員工姓名select ename from emp where ename like '%A%'以年月日方式顯示所有員工的服務(wù)年限se

39、lect trunc(months_between(sysdate,hiredate)/12) as year,trunc(mod(months_between(sysdate,hiredate),12) month, trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate) as dayfrom emp;三、 多表查詢查詢格式SELECT <所求內(nèi)容> FROM <表1>,<表2> WHERE <連接條件> AND <篩選條件>用到多張表時，需要重表之間的連

40、接 連接（內(nèi)連接（等值連接和自然連接）和外連接（左連接，右連接，全連接）連接：將多張有聯(lián)系的表組合稱為一張例題1：查詢出查詢每一個雇員的雇員信息和工資等級信息所求內(nèi)容：emp.*,grade所用的表：emp,salgrade連接條件： sal>=losal and sal <= hisalSelect emp.* ,grade from emp,salgrade where sal>=losal and sal <= hisal;例題2：查詢出雇員姓名以及雇員所做的部門名篩選條件：所求內(nèi)容: ename,dname所用的表：emp,dept連接條件：emp.deptno

41、 = dept.deptnoselect a.ename,b.dname from emp a,dept b where a.deptno = b.deptno;例題3：查詢出雇員姓名，所做部門名，工資等級篩選條件：所求內(nèi)容：ename,dname,grade所用的表：emp,dept,salgrade連接條件：emp和dept ： emp.deptno = dept.deptno Emp 和grade： sal>=losal and sal <= hisalselect ename,dname,grade from emp,dept,salgrade where (emp.dep

42、tno = dept.deptno) and (sal>=losal and sal<=hisal);例題4：查詢出工資等級為5的所有職工所中的部門信息篩選條件：grade = 5所求內(nèi)容：dept.*所用的表：1）從篩選條件來自的表 salgrade2） 從所求內(nèi)容中來自動表 dept3） 兩張表若需要連接，就必須有一張中間表emp才能將上面兩張表連接起來，中間表也要包括進來select a.* from dept a,emp b,salgrade c where a.deptno = b.deptno and (b.sal>=c.losal and b.sal <=

43、 c.hisal) and grade = 5;例題5：查詢出地址中NEW YORK工作的雇員信息例題6:查詢出地址中NEW YORK工作的雇員姓名和工資等級四、左、右連接查詢左連接查詢：以左邊表為標準查詢內(nèi)容，左邊表的所有記錄都會出現(xiàn)的查詢結(jié)果中，若中右邊有對應(yīng)記錄則對應(yīng)出現(xiàn)，沒有對應(yīng)右邊表字段值出用null來填充補齊。改變：左邊表名.公共字段名 = 右邊表名.公共字段名(+)例題：將dept、emp兩張表進行左連接查詢select * from dept,emp where dept.deptno = emp.deptno(+);右連接：以右邊表為標準，去連接左邊表，右邊表的所有記錄都會出

44、現(xiàn)在查詢結(jié)果中，有對應(yīng)則對應(yīng)，沒有對應(yīng)的左邊表字段值用null值填充。連接條件：左邊表名.公共字段名(+) = 右邊表名.公共字段名例題：將dept、emp兩張表進行右連接查詢select * from dept,emp where dept.deptno(+) = emp.deptno;SQL：1999SQL定義 (了解)SELECT table1.column,table2.columnFROM table1 CROSS JOIN table2|NATURAL JOIN table2|JOIN table2 USING(column_name)|JOIN table2 ON (table1

45、.column_name=table2.column_name)|LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name=table2.column_name)1、交叉連接(CROSS JOIN):迪卡爾積運算92標準：select * from emp,dept;99標準：select * from emp cross join dept;2、table1 INNER JOIN table2 ON (table1.column_name=table2.column_name)、內(nèi)連接（實質(zhì)就是等值連接）例題：Emp表和dept表進行內(nèi)

46、連接（列相加，記錄相對應(yīng)）92標準： select * from emp,dept where emp.deptno = dept.deptno;格式： select <所求內(nèi)容> from 表1,表2 where 表1.公共字段名 = 表2.公共字段名99標準： select * from emp inner join dept on emp.deptno = dept.deptno;格式：select <所求內(nèi)容> from 表1 INNER JOIN表2 ON 表1.公共字段名 = 表2.公共字段名select * from emp join dept on em

47、p.deptno = dept.deptno;格式：select <所求內(nèi)容> from 表1 JOIN表2 ON 表1.公共字段名 = 表2.公共字段名99標準：select * from emp join dept using(deptno);格式： select <所求內(nèi)容> from 表1 Join 表2 USING(公共字段名)用USING(公共字段名) 來代替連接條件3、自然連接 (NATURAL JOIN)：是等值連接基礎(chǔ)上去掉一個重復的列所得到結(jié)果就是自然連接得到結(jié)果例題：寫emp和dept兩張進行自然連接92標準：select emp.*,dname,

48、loc from emp,dept where emp.deptno = dept.deptno;99標準：select * from emp natural join dept;4、外連接（左連接，右連接，全連接）Table 1 LEFT|RIGHT|FULL OUTER JOIN table2 左連接/右連接/全連接1、左連接92標準：select * from emp,dept where emp.deptno = dept.deptno(+);格式： select <所求內(nèi)容> from 表1，表2 where 表1.公共字段名=表2.公共字段名(+)99標準：select

49、 * from emp left join dept on emp.deptno = dept.deptno;select <所求內(nèi)容> from 表1 left outer join表2 on表1.公共字段名=表2.公共字段名2、右連接92標準：select * from emp,dept where emp.deptno (+)= dept.deptno;格式： select <所求內(nèi)容> from 表1，表2 where 表1.公共字段名(+)=表2.公共字段名99標準：select * from emp right join dept on emp.deptno

50、 = dept.deptno;select <所求內(nèi)容> from 表1 right outer join表2 on表1.公共字段名=表2.公共字段名3、全連接92標準：沒有表達方式99標準：select * from emp full join dept on emp.deptno = dept.deptno;select <所求內(nèi)容> from 表1 full outer join表2 on表1.公共字段名=表2.公共字段名四、嵌套查詢將某一個查詢的結(jié)果作為另一查詢的條件值，查詢成為嵌套查詢Select <所求內(nèi)容> from <所求內(nèi)容來自的表&

51、gt; where 公共字段名 IN|NOT IN (SELECT 公共字段名 from 篩選條件所涉及的表 where 篩選條件 and 其他條件 ) and 其他條件若子查詢前面：> < >= <= = 時，子查詢的結(jié)果只能有一個容易子查詢前面： in | not in，子查詢的結(jié)果又多個1）例題查詢出地址在NEW YORK的雇員編號，雇員姓名 A)先將地址在NEW YORK部門編號是多少select deptno from dept where loc = 'NEW YORK' B)在通過上面的部門編號求出所有該部門的雇員信息select * fr

52、om emp where deptno = 10;select * from emp where deptno = (select deptno from dept where loc = 'NEW YORK');2)查詢出工資雇員編號為7788雇員的工資高于的所有雇員信息select * from emp where sal >= (select sal from emp where empno=7788);3）查詢出和雇員編號為7788或 7839雇員在同一個部門的雇員信息select * from emp where deptno in (select deptno

53、 from emp where empno=7788 or empno = 7839);4） 查詢出不和雇員編號為7788或 7839雇員在同一個部門的雇員信息select * from emp where deptno not in (select deptno from emp where empno=7788 or empno = 7839);五、組函數(shù) 計算查詢組函數(shù)：對一組數(shù)據(jù)進行操作的函數(shù)SUM() 求和函數(shù)AVG() 求平均值MAX() 求最大值MIN() 求最小值COUNT() 統(tǒng)計應(yīng)用組函數(shù)：例題：計算出所有雇員的工資總和select sum(sal) as sum_sal

54、from emp;例題：求出所有雇員的平均工資select avg(sal) as avg_sal from emp;例題：求出所有雇員中最低工資和最高工資select max(sal) as max_sal,min(sal) as min_sal from emp;例題：統(tǒng)計出雇員表中出現(xiàn)的部門有多少個（重復值統(tǒng)計問題）參數(shù)中加： distinct 重復值只統(tǒng)計一次select count(distinct deptno) as count_dept from emp;例題：查詢出工資高于平均工資的所有雇員信息select * from emp where sal > (select

55、avg(sal) from emp);注意：當組函數(shù)作為條件時，不能寫中where后面，只能寫group by 部分的having后面作為分組所滿足的條件。例題：查詢出工資都高于20部門的所有員工的雇員信息。select * from emp where sal > (select max(sal) from emp where deptno = 20);例題：查詢出工資等級為5級的雇員的所在部門名稱內(nèi)容.分析：篩選條件：grade = 5所求內(nèi)容：dname所用的表：所求內(nèi)容涉及的字段來自的表 dept 篩選條件涉及字段來自的表 salgrade Dept和salgrade兩張表沒有直

56、接聯(lián)系，必須使用中間表 emp后，兩張表才能連接，查詢時必須包含中間表。方法1：思想：將dept，salgrade，emp三張表等值連接成為一張表后，進行條件和所內(nèi)容的篩選select distinct dname from dept,emp,salgrade where dept.deptno = emp.deptno and (sal>=losal and sal <=hisal) and grade = 5;方法2：思想：將dept和emp先連接成為一張表后，再和salgrade進行嵌套查詢select distinct dname from dept,emp where dept.deptno = emp.deptno and sal >= (select losal from salgra