01-03 Oracle數(shù)據(jù)庫開發(fā)技術(shù)之簡(jiǎn)單查詢、限制和排序語句（BOBO）（完成）

-課程大綱【課程介紹】本課程全面介紹oracle的SQL語言。介紹查詢語句DML語句DDL語句數(shù)據(jù)庫對(duì)象等等。【課程目標(biāo)】數(shù)據(jù)庫SQL語句是JavaEE項(xiàng)目開發(fā)的基礎(chǔ)。在關(guān)系型數(shù)據(jù)庫當(dāng)中，SQL語句是必備的基礎(chǔ)性知識(shí)。【課程計(jì)劃】全面掌握oracle數(shù)據(jù)庫sql語言。EMFN0ENAJffiJOBMGRHIREDATEISALCOMMDEPTNO?17499ALLEHSALESMAN76981981/2/20▼1600.00300.003027521WARDSALESMAN76981981/2/22▼1250.00500.003037654MARTINSALESMAN76981981/9/28▼1250.001400.003047844TURNERSALESMAN76981981/9/8▼1500.000.0030實(shí)例4.13要求查詢出10部門中的經(jīng)理或者是30部門的業(yè)務(wù)員的信息select*fromempwhere(job='MANAGER'anddeptno=10)or(deptno=30andjob='SALESMAN');EMFNOENAMEJOBMGRHIREDATESALCOMMDEPTNO?17499ALLEHSALESMAN76981981/2/20▼1600.00300.003027521WARDSALESMAN_76981981/2/22▼1250.00500.003037654MARTINSALESMAN76981981/9/28▼1250.001400.003047782CLARKMANAGER二78391981/6/9▼2450.001057844TURNERSALESMAN76981981/9/8▼1500.000.0030實(shí)例4.14查詢不是辦事員的且基本工資大于2000的全部雇員信息select*fromempwherejob!二'CLERK'andsal>2000;EMF1T0ENAMEJOBMGRHIREDATESALCOMMDEPTHO?17566JONESMANAGER78391981/4/2 ▼2975.002027698BLAKEflAHAGER78391981/5/1 ▼2850.003037782CLARKMANAGER78391981/6/9 ▼2450.001047788SCOTTANALYST75661987/4/19▼3000.002057839KINGPRESIDEHT1981/11/17▼5000.001067902FORDANALYST75661981/12/3▼3000.0020范圍運(yùn)算符:BETWEEN…AND…實(shí)例4.15使用BETWEEN??,AND…操作符查詢出工資范圍在1500-3000(包含1500和3000)的全部雇員信息select*fromempwheresal>=1500andsal<=3000;select*fromempwheresalbetween1500and3000;一閉區(qū)間EMFNOENAMEJOBMGRHIREDATE_(SALCOMMDEPTNO?17499ALLENSALESMAN_76981981/2/20▼1600.00300.003027566JONESMANAGER78391981/4/2▼2975.002037698BLAKEFitANAGER78391981/5/1▼2850.003047782CLARKMANAGER78391981/6/9▼2450.001057788SCOTTANALYST75661987/4/19▼3000.002067844TURMERSALESMAN76981981/9/8▼1500.000.003077902FORDANALYST75661981/12/3▼3000.0020實(shí)例4.16查詢出在1981年雇員的全部雇員信息select*fromempwherehiredate>='01T月-1981'andhiredate<='31T2月-1981';select*fromempwherehiredatebetweenJ01-1月T981'andJ31-12月-1981';EMFNOENAME|JOBMGR|HIREDATE|SALCOMMDEPTNO?|17499ALLENSALESMAN76981981/2/20▼1600.00300.0030i27521WARDSALESMAN76981981/2/22▼1250.00500.0030137566JONESMANAGER78391981/4/2▼2975.002047654MARTINSALESMAN76981981/9/28▼1250.001400.003057698BLAKEMANAGER78391981/5/1▼2850.0030(67782CLARKMANAGER78391981/6/9▼2450.0010177839KINGPRESIDENT1981/11/17▼5000.0010187844TURNERSALESMAN76981981/9/8▼1500.000.0030|97900JAMESCLERK76981981/12/3▼950.0030107902FORDANALYST75661981/12/3▼3000.00204.1.6模糊查詢：LIKE和NOTLIKE%:代表任意字符串_：代表任意一個(gè)字符實(shí)例4,17現(xiàn)在查詢出雇員姓名是以S開頭的全部雇員信息select*fromempwhereenamelike'S%';EMPN0ENAME(JOB|MGR|HIREDATE |SAL|C0MMDEPTNO」?17369SMITHHclerk79021980/12/17▼800.0020I27788SCOTT1ANALYST75661987/4/19—3000.00| 20實(shí)例4.18現(xiàn)在要求查詢雇員姓名的第二個(gè)字母是M的全部雇員信息select*fromempwhereenamelike' ;EMPN0ENAMEJOBMGRKIREDATE一[sal1commDEPTNOJ?173691SMITHCLERK79021980/12/17▼800,0020實(shí)例4.19查詢出姓名中任意位置包含字母F的雇員信息select*fromempwhereename1ike'%F%';EMPNO1ENAMEHjob]mgrKIREDATE|SAL COMMDEPTNO?17902FORDANALYST75661981/12/3▼〕3000.00120實(shí)例4.20查詢雇員姓名長(zhǎng)度為6或是超過6個(gè)的雇員信息select*fromempwhereenamelike'%';EMPNO|ENAME|job-iMGRHIREDATE(SAL|COMMDEPTNO?17654.MARTIHSALESMAN76981981/9/28▼1250.001400.0030]27844TURNERSALESMAN76981981/9/8I1500.000.0030]37934MILLER_CLERK77821982/1/23二1300.0010實(shí)例4,21現(xiàn)在要求查詢出雇員基本工資中包含1或是在81年雇傭的全部雇員select*fromempwheresal1ike'/1%'orhiredate1ike;EMPNOENAMEJOB 1MGR|HIREDATE|SAL|COMMDEPTNO?|17499ALLENSALESMAN76981981/2/20▼1600.00300.0030I(27521WARDSALESMAN76981981/2/22▼1250.00500.00301137566JONES由AGER7839[1981/4/2▼2975.00201147654MARTINSALESMAN76981981/9/28▼1250.001400.00301|57698BLAKEMANAGER7839[1981/5/1▼2850.00301i67782CLARKMANAGER78391981/6/9▼2450.00101177839KINGPRESIDENT1981/11/17▼5000.00101J87844TURMERSALESMAN76981981/9/8▼1500.000.0030197876ADAMSCLERK77881987/5/23▼1100.0020實(shí)例4.22找出部門10中所有經(jīng)理(MANAGER),部門20中所有辦事員(CLERK),既不是經(jīng)理又不是辦事員但其薪金大于2000的所有員工的詳細(xì)資料，并且要求這些雇員的姓名之中包含有字母S或字母Koselect*fromempwhere((deptno=10andjob='MANAGER')or(deptno=10andjob='CLERK，)or(job!='MANAGER'andjob!='CLERK'andsal>2000))and(enamelike'96S%'orenamelike'%K%')EMPNO|ENAME|JOB 1MGR|HIREDATE 1SAL|C0MMDEPTNO?17782CLARKMANAGER78391981/6/9 ▼2450.00io]27788SCOTTANALYST75661987/4/19~^|3000.OO]20]37839KINGPRESIDENT1981/11/17▼5000.001 104.1.7判斷是否為空:ISNULLsISNOTNULL空：未知的，不確定的。跟o是有區(qū)別的。實(shí)例4.23查詢出所有領(lǐng)取傭金的雇員的信息select*fromempwherecommisnotnull;EMFNOENAMEJOBMGR|HIREDATE|SALCOMMDEPTNO|?17499ALLEN_SALESMAN_76981981/2/20▼1600.00300.003027521WARDSALESMAN76981981/2/22*1250.00500.0030137654MARTINSALESMAN76981981/9/28▼1250.001400.0030147844TURNERSALESMAN76981981/9/8▼1500.000.0030實(shí)例4.24查詢所有不領(lǐng)取傭金的雇員的信息select*fromempwherecommisnul1;EMFNO|ENAMEJOBMGR|HIREDATESAL|COMMDEPTNO?17369SMITH_CLERK79021980/12/17▼800.0020I27566JOHESMANAGER78391981/4/2 ▼2975.0020137698BLAKEf^ANAGER二78391981/5/1 ▼2850.0030147782CLARKMANAGER78391981/6/9 ▼2450.0010157788SCOTTANALYST75661987/4/19▼3000.0020167839KINGPRESIDEHT1981/11/17▼5000.00101177876ADAMS_CLERK__77881987/5/23▼1100.0020187900JAMESCLERK76981981/12/3▼950.0030197902FORDANALYST二75661981/12/3▼3000700201107934MILLERCLERK_77821982/1/23▼1300.0010實(shí)例4.25列出所有的不領(lǐng)取獎(jiǎng)金的雇員，而且同時(shí)要求這些雇員的基本工資大于2000的全部雇員信息select*fromempwherecommisnullandsal>2000;EMFNOENAMEJOBMGRHIREDATESAL|C0MMDEPTNO±17566JONESMANAGER78391981/4/2▼2975.002027698BLAKEMANAGER78391981/5/1▼2850.003037782CLARKMANAGER78391981/6/9▼2450.001047788SCOTTANALYST75661987/4/19▼3000.002057839KINGPRESIDENT1981/11/17▼5000.001067902FORDANALYST75661981/12/3▼3000.0020實(shí)例4.26找出不收取傭金的或收取傭金低于100的員工select*fromempwherecommisnullorcomm<100;EMFNOENAMEJOBMGRHIREDATESAL|COMMDEPTNO_|?117369SMITHCLERK_79021980/12/17▼800.0020]27566JOHESMANAGER_78391981/4/2▼2975.00201|37698BLAKE_MANAGER7839[1981/5/1▼2850.0030Ij47782CLARKMANAGER一78391981/6/9▼2450.0010Ij57788SCOTTAHALYST75661987/4/19▼3000.0020167839KINGPRESIDEHT1981/11/17▼5000.00101j77844TURMERSALESMAN—76981981/9/8▼1500.000.00301j87876ADAMSCLERK77881987/5/23▼1100.0020]J97900JAMESCLERK76981981/12/3▼950.0030]|107902FORDANALYST二—75661981/12/3▼3000.0020117934KILLERCLERK77821982/1/23▼1300.0010實(shí)例4.27查詢收取傭金的員工從事的工作有哪些selectename,jobfromempwherecommisnotnull;ENAMEJOB |?j1ALLENSALESMAN12WARDSALESMAN13MARTINSALESMAN|4TURNER—SALESMAN太腐敗了！列范圍查找IN、NOTININ：表示間斷，跳躍的實(shí)例4.28查詢出雇員編號(hào)是7369、7788、7566的雇員信息select*fromempwhereempnoin(7369,7788,7566);EMPNO|ENAMETOBMGRHIREDATE〕SALCOMMDEPTHO?17369.SMITHCLERKL79021980/12/17▼800.002027566JOBS1MANAGER_78391981/4/2 ▼2975.0020I37788SCOTTANALYST75661987/4/19▼3000.0020實(shí)例4.29查詢不領(lǐng)取傭金且工作為分析員和經(jīng)理的員工信息select*fromempwherecommISNULLand(job二'MANAGER'orjob='ANALYST');select*fromempwherecommISNULLandjobin('MANAGER','ANALYST');EMPNOENAMETOBMGR|HIREDATESAL|COMMDEFTNO±17566JONESMANAGER78391981/4/2▼2975.0020I27698BLAKE_MANAGER78391981/5/1▼2850.0030137782CLARKMANAGER78391981/6/9▼2450.0010147788SCOTTANALYST75661987/4/19B3000.0020I57902FORDANALYST75661981/12/3J3000.0020實(shí)例4.30查詢除了7369、7788、7566之外的雇員信息select*fromempwhereempnonotin(7369,7788,7566);EMPNOENAMEJOB 1MGR|HIREDATE|SALICCM^DEPTNO?17499ALLENSALESMAN76981981/2/20▼1600.00300.0030127521WARDSALESMAN76981981/2/22▼1250.00500.0030]37654MARTINSALESMAN76981981/9/28▼1250.001400.0030147698BLAKEMANAGER78391981/5/1▼2850.0030157782CLARKMANAGER78391981/6/9▼2450.001067839KINGPRESIDENT1981/11/17▼5000.0010177844TURNERSALESMAN_7698[1981/9/8▼1500.000.0030187876ADAMSCLERK77881987/5/23▼1100.0020197900JAMES_CLERK76981981/12/3▼950.00301107902FORDANALYST75661981/12/3▼3000.00201117934FILLERCLERK77821982/1/23▼1300.0010實(shí)例4.31在使用NOIIN操作符中設(shè)置NULL設(shè)定(什么都不會(huì)有)select*fromempwhereempnonotin(7369,7788,7566,null);使用ORDERBY子句進(jìn)行排序語法SELECT*|{[DISTINCT]列名|表達(dá)式[列別名],……)FROM表名稱[表別名][WHERE過濾條件⑸][ORDERBY排序的字段1ASC|DESC,排序的字段2ASQDESC,…];實(shí)例實(shí)例4.32查詢雇員的完整信息并且按照基本工資由高到低進(jìn)行排序select*fromemporderbysaldesc;EMPNOENAME|JOBMGRHIREDATE—|SALCOMMDEPTNO|?117839KING_PRESIDENT1981/11/17▼5000.00io]27902FORDANALYST75661981/12/3▼3000.0020Ij37788SCOTT_ANALYST75661987/4/19▼3000.00201147566JONESMANAGER78391981/4/2▼2975.0020]57698BLAKEMANAGER-78391981/5/1▼2850.00301167782CLARKMANAGER_78391981/6/9▼2450.00101|77499ALLEN_SALESMAN76981981/2/20▼1600.00300.00301|87844TURMERSALESMAN-76981981/9/8▼1500.000.0030]j97934MILLER_CLERK__77821982/1/23▼1300.00101107521WARDSALESMAN76981981/2/22▼1250.00500.0030]117654MARTIN_SALESMAH_76981981/9/28▼1250.001400.00301127876ADAMSCLERK77881987/5/23▼1100.00201137900JAMESCLERK76981981/12/3▼950.00301147369SMITHCLERK79021980/12/17▼800.0020實(shí)例4.33查詢出所有辦事員(CLERK)的信息，并且按照基本工資由低到高排序select*fromempwherejob='CLERK'orderbysalasc;EMPNO|ENAME[JOBMGR|HIREDATESAL|COMMDEPTNO?17369SMITH_CLERKr79021980/12/17▼800.002027900JAMESCLERK76981981/12/3▼950.0030137876ADAMSCLERK-77881987/5/23▼1100.0020147934MILLERCLERK77821982/1/23二1300.0010實(shí)例4.34查詢出所有雇員信息，要求按照基本工資由高到低排序，如果工資相等則按照雇傭日期進(jìn)行排序，按照由早到晚的順序select*fromemporderbysaldesc,hiredateasc;EMPNO|ENAME|JOBMGRHIREDATESAL|COMMDEPTNO?17839KINGPRESIDEHT1981/11/17▼5000.001027902FORDANALYST75661981/12/3▼3000.002037788SCOTT_ANALYSTT75661987/4/19▼3000.0020j47566JONESMANAGER78391981/4/2▼2975.002057698BLAKEMANAGER78391981/5/1▼2850.003067782CLARKMANAGER78391981/6/9▼2450.001077499ALLEN_SALESMAN76981981/2/20▼1600.00300.003087844TURNERSALESMAN76981981/9/8▼1500.000.003097934MILLERCLERK77821982/1/23▼1300.0010107521WARDSALESMAN76981981/2/22▼1250.00500.0030117654MARTINSALESMANT76981981/9/28▼1250.001400.0030127876ADAMSCLERK77881987/5/23▼1100.0020137900JAMESCLERK76981981/12/3▼950.0030147369SMITHCLERK79021980/12/17▼800.0020實(shí)例4.35查詢雇員的編號(hào)、姓名、工資，并按工資升序排序selectempno,ename,salfromemporderby3;EMPNO|ENAMESAL|?j17369SMITH_800.00|27900JAMES950.0037876ADAMS1100.0047521WARD1250.0057654MARTIN1250.00167934KILLER1300.00j77844TURNER1500.0087499ALLEN1600.0097782CLARK2450.00107698BLAKE2850.00117566JONES_2975.00127788SCOTT3000.00137902FORD3000.00147839KING5000.00實(shí)例4.36查詢雇員的編號(hào)、姓名、工資，并按工資別名降序排序selectempno,ename,salassfromemporderbysdesc;EMFNOENAME|s1?j17839KING_5000.0027902FORD3000.0037788SCOTT_3000.00147566JONES2975.00|57698BLAKE2850.0067782CLARK2450.0077499ALLEN_1600.00187844TURNER1500.00|97934HILLER1300.00107521WARD1250.00117654MARTIN1250.00127876ADAMS1100.00137900JAMES950.00147369SMITH800.00第二章：SQL語言簡(jiǎn)介什么是SQL結(jié)構(gòu)化查詢語言（StructuredQueryLanguage）是高級(jí)的非過程化編程語言，它不要求用戶指定對(duì)數(shù)據(jù)的存取方法，屏蔽了底層的數(shù)據(jù)的存取過程。好像領(lǐng)導(dǎo)交給任務(wù)給下屬，下屬自主去完成任務(wù)，而不是由領(lǐng)導(dǎo)指定下屬該怎么做；領(lǐng)導(dǎo)告訴下屬自己需要什么，而不是領(lǐng)導(dǎo)告訴下屬怎么樣去執(zhí)行。發(fā)展歷史1986年10月，美國(guó)國(guó)家標(biāo)準(zhǔn)協(xié)會(huì)發(fā)布SQL規(guī)范，作為關(guān)系式數(shù)據(jù)庫管理系統(tǒng)的標(biāo)準(zhǔn)語言。1987年得到國(guó)際標(biāo)準(zhǔn)化組織的支持下成為國(guó)際標(biāo)準(zhǔn)。各種數(shù)據(jù)庫生產(chǎn)對(duì)現(xiàn)有的SQL規(guī)范進(jìn)行擴(kuò)充，以滿足自己數(shù)據(jù)庫產(chǎn)品的需要。FoxBase（Dos下運(yùn)行）、FoxPro（Windows3.1/3.2/98）SQLServer、MySQL、Oracle等。數(shù)據(jù)查詢語言數(shù)據(jù)操縱語言數(shù)據(jù)定義語言事務(wù)控制語言數(shù)據(jù)控制語言2.3SQL分類數(shù)據(jù)查詢語言數(shù)據(jù)操縱語言數(shù)據(jù)定義語言事務(wù)控制語言數(shù)據(jù)控制語言（查詢）（增刪改）（定義、修改刪除數(shù)據(jù)庫對(duì)象）（組合增刪改操作形成事務(wù)，事務(wù)有提交、回滾、保存點(diǎn)和狀態(tài)）（授權(quán)和回收）第三章：簡(jiǎn)單查詢語句3.1語法SELECT［DISTINCT］*|列名稱［AS］［列別名］,列名稱［AS］［列別名］,FROM表名稱［表別名］;3.2實(shí)例實(shí)例3.1查詢scott用戶的表select*fromtab;THAMETABTYPECLUSTERID?11BONUS…TABLE_12DEPT …TABLE|3EMPTABLE4SALGRADE…TABLE實(shí)例3.2查詢emp表中的全部數(shù)據(jù)select*fromemp;select*fromdept;select*fromsalgrade;員工編號(hào)員工名字工作上級(jí)領(lǐng)導(dǎo)員工編號(hào)入職時(shí)間工資獎(jiǎng)金部門編號(hào)EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO?17369SMITHCLERK79021980/12/17▼800.002027499ALLENSALESMAN76981981/2/20▼1600.00300.003037521WARDSALESMAN76981981/2/22▼1250.00500.003047566JONESMANAGER78391981/4/2▼2975.002057654fitARTINSALESMAN76981981/9/28▼1250.001400.0030167698BLAKEMANAGER78391981/5/1▼2850.003077782CLARKMANAGER78391981/6/9▼2450.001087788SCOTTANALYST75661987/4/19▼3000.002097839KINGPRESIDENT1981/11/17▼5000.0010107844TURNERSALESMAN76981981/9/8▼1500.000.0030117876ADAMSCLERK77881987/5/23▼1100.0020127900JAMESCLERK76981981/12/3▼950.0030137902FORDANALYST75661981/12/3▼3000.00201147934MILLERCLERK77821982/1/23▼1300.0010部門編號(hào)部門名稱位置DEPTNODNAMELOC |?|110ACCOUNTINGNEWYORK|220RESEARCHDALLAS一330SALESCMICAGOj440OPERATIONSBOSTON等級(jí)下限上限GRADED3SALHISAL?1170012002212011400331401200044200130005530019999實(shí)例3.3查詢公司每個(gè)雇員的編號(hào)、姓名、工資三個(gè)信息進(jìn)行selectempno,ename,salfromemp;EMPN0ENAMESAL?17369SJidTH800.00J27499ALLEN1600.0037521WARD1250.0047566JONES2975.0057654MARTIN1250.0067698BLAKE2850.0077782CLARK2450.0087788SCOTT3000.0097839KING5000.00107844TURNER1500.00117876ADAMS1100.00127900JAMES950.00137902FORD3000.00147934MILLER1300.00實(shí)例3.4查詢公司中所有雇員的職位信息select*fromemp;selectjobfromemp;實(shí)例3.5顯示公司所有職位名稱（去掉重復(fù)）selectdistinctjobfromemp;□IJDB |回ERKJ2SALESMANJ3PRESIDEHldJ4MANAGER|15ANALYST實(shí)例3.6查詢出所有雇員的編號(hào)、雇員姓名和年基本工資、日基本工資selectempno,ename,sal*12,sal/30Promemp;EMPNOENAMESAL*12 1SAL/30?17369SMITH960026.666666666666727499ALLEN1920053.333333333333337521WARD1500041.666666666666747566JONES3570057654MARTIN1500041.666666666666767698BLAKE342009577782CLARK2940081.666666666666787788SCOTT3600010097839KING60000166.666666666667107844TURNER1800050117876ADAMS1320036.6666666666667127900JAMES1140031.6666666666667137902FORD36000100147934MILLER1560043.3333333333333實(shí)例3.7年底增加5000元獎(jiǎng)金，計(jì)算年工資selectempno,ename,saH12+5000fromemp;EMPNOENAME1SAL*12-?5000?17369SMITH1460027499ALLEN2420037521WARD2000047566JUNES4070057654MARTIN2000067698BLAKE3920077782CLARK3440087788SCOTT4100097839KING65000107844TURNER23000117876ADAMAMES16400137902FORD41000147934MILLER20600實(shí)例3.8每月工資增加200元，計(jì)算年工資selectempno,ename,(sal+200)*12fromemp;EMPNO|ENAME(SAL+200)*12?117369SMITH12000|27499ALLEN216001I37521WARD174001j47566JONES381001f57654MARTIH174001I67698BLAKE366001177782CLARK318001j87788SCOTT384001197839KING6240011107844TURNER204001117876ADAMS156001127900JAMES138001137902FORD384001147934HILLER18000年工資fromemp;as年工資fromemp;as“年工資〃fromemp;實(shí)例3.9年工資fromemp;as年工資fromemp;as“年工資〃fromemp;EMPNOENAME1年工資?j17369SMITH1200027499一ALLEN2160037521WARD_1740047566JONES3810057654KARTILAKE36600177782CLARK_3180087788SCOTT3840097839KING_| 62400107844TURNER20400117876ADAMSAMEORD38400147934HILLER18000實(shí)例3.10使用“II”進(jìn)行連接selectename'的年工資是：'||sal*12as員工年工資fromemp;selectenamel|'''ssalaryis:'||sal*12as員工年工資fromemp;員工年工^?1SMITH的年工資是：96002M10莊）年工資是：192003WARD的舁工資是：150004JONES的年工資是：357005MART工N的年工資是：15000…6BLAKE的舁工資是：342007CLARK的本工資是：294008SCOTT的舁工資是：36000…9KI崎舁工資是：60000TURNER的年工資是：18000…101112ADAMS的舁工資是：1320。JAMES的辛工資是：1140013FORD的年工資是：36000MILLER的年工資是：15600…14實(shí)例3.11SQL不區(qū)分大小寫select*fromemp;SELECT*FROMEMP;第四章：限制和排序語句限制查詢?nèi)绻樵兯谐汕先f的記錄的話，有可能耗盡內(nèi)存。語法SELECT*|｛［DISTINCT］列名|表達(dá)式［列別名］,……)FROM表名稱［表別名］類別關(guān)系運(yùn)算符判斷是否為空邏輯運(yùn)算符范圍查詢列表范圍查詢模糊查詢［WHERE過濾條件⑸］類別關(guān)系運(yùn)算符判斷是否為空邏輯運(yùn)算符范圍查詢列表范圍查詢模糊查詢符號(hào)＞、＜、〉二、＜二、二、！二、＜＞ISNULL.ISNOTNULLAND、OR、NOTBETWEEN-AND-INLIKE關(guān)系運(yùn)算符：=實(shí)例4.1查詢10部門的員工select*fromempwheredeptno=10;一字段名運(yùn)算符值EMPN0|ENAME|IJOB 1MGR|HIREDATE 1SAL|COMMDEPTNO117782CLARKMANAGER78391981/6/9 ▼2450.0010|27839KINGPRESIDENT1981/11/17▼5000.00I1037934MILLERCLERK77821982/1/23,1300.0010實(shí)例4.2查詢姓名為KING的信息select*fromempwhereename='KING';--字符串常量是大小寫區(qū)分的EMPNO-jENAMEJOB |MGRHIREDATESALjCOMMDEPTNO?17839KINGPRESIDENT1981/11/17▼5000JO10實(shí)例4.3查詢工資為3000的員工信息select*fromempwheresal=3000;EMPNOENAMEIJOB|mgrHIREDATE|SAL|COMM|DEPTNO|?17788SCOTTANALYSTI75661987/4/19▼3000.002027902FORD_|ANALYST75661981/12/3▼3000.00 20實(shí)例4.4查詢1981年12月3日進(jìn)入公司員工信息select*fromempwherehiredate='03T2月-1981';一日上補(bǔ)0月上不補(bǔ)0EMPNOENAME|JDB|mgrHIREDATE|SALICOMMDEPTNO?17900JAMES二（clerk176981981/12/3950.003027902FORD1ANALYST75661981/12/3▼3000.0020品毋淘口.新t對(duì)話框編輯器ConnectedtoOracleDatabase11gEnterpriseEditionReieasConnectedasscottSBEDUSQL〉descemp;Name Type NullableDefaultCommentsEMPNO NUMBER（4）ENAME VARCMAR2（10） YJOB VARCHAR2（9） YMGR HUMBER（4） YHIREDATEDATE YSAL NIIMBERE2） YCOMM NUMBER（7.2） YDEPTNO NUMBER（2） YSQL>4.L3關(guān)系運(yùn)算符：＞、V、＞=、＜=、！=、＜＞實(shí)例4.5查詢工資高于1300的員工信息select*fromempwheresal>1300;EMPNOENAME|JOB MGRHIREDATE |SALCOMMDEPTNO117499ALLENSALESMAN76981981/2/20▼1600.00300.0030I|27566JONESMANAGER78391981/4/2 ▼2975.00201137698BLAKE_MANAGER78391981/5/1 ▼2850.0030147782CLARKMANAGER78391981/6/9 ▼2450.0010]57788SCOTTANALYST_一75661987/4/19▼3000.0020167839KINGPRESIDENT1981/11/17▼5000.00101|77844TURNERSALESMAN76981981/9/8 ▼1500.000.0030j87902FORDANALYST75661981/12/3▼3000.0020實(shí)例4.6查詢工資低于或等于1500的全部雇員信息select*fromempwheresal<=1500;EMPNOENAMEJOBMGRHIREDATEIsALCOMMDEPTNO?17369SMITHCLERK79021980/12/17▼800.0020127521WARDSALESMAN_76981981/2/22▼1250.00500.003037654MARTINSALESMAN76981981/9/28▼1250.001400.003047844TURNERSALESMAN76981981/9/8▼1500.000.003057876ADAMS_CLERK_77881987/5/23▼1100.002067900JAMES一CLERK-76981981/12/3▼950.003077934MILLERCLERK77821982/1/23▼1300.0010實(shí)例4.7查詢出所有基本工資大于等于2000的全部雇員信息select*fromempwheresal＞=2000;EMFN0ENAMEJOBMGRHIREDATESALCOMMDEPTNO?117566JONESMANAGER78391981/4/2 ▼2975.0020276