Oracle開發(fā)實(shí)用教程

第3章SQL語(yǔ)言與查詢學(xué)習(xí)要點(diǎn)基本查詢多表聯(lián)接子查詢集合運(yùn)算聚合函數(shù)

本章主要內(nèi)容SQL語(yǔ)言概述SQL語(yǔ)言的功能種類數(shù)據(jù)類型、運(yùn)算符與表達(dá)式基本查詢多表聯(lián)接子查詢集合運(yùn)算聚合函數(shù) 3.1SQL語(yǔ)言概述SQL（StructuredQueryLanguage）是一種結(jié)構(gòu)化查詢語(yǔ)言，簡(jiǎn)稱為SQL。它是1972年在SQUARE（SpecifyingQueriesAsRelationalExpression）語(yǔ)言的基礎(chǔ)上由Boyce和Chamberlin提出的一個(gè)標(biāo)準(zhǔn)的、通用的結(jié)構(gòu)化查詢語(yǔ)言，后來(lái)將SEQUEL改為SQL。1989年，美國(guó)ANSI采納了ANSIX3.134—1989報(bào)告中定義的關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)的SQL標(biāo)準(zhǔn)語(yǔ)言，稱為ANSISQL89。該標(biāo)準(zhǔn)被國(guó)際標(biāo)準(zhǔn)化組織（ISO）和美國(guó)聯(lián)邦政府組織所采納。

3.2SQL語(yǔ)言的功能種類SQL語(yǔ)言按照功能可以分為四大類：（1）數(shù)據(jù)查詢語(yǔ)言，主要用于查詢數(shù)據(jù)，以SELECT語(yǔ)句為主要關(guān)鍵詞。（2）數(shù)據(jù)定義語(yǔ)言DDL，用于定義SQL模式、基本表、視圖和索引。（3）數(shù)據(jù)操縱語(yǔ)言DML，主要用于完成數(shù)據(jù)修改操作。（4）數(shù)據(jù)控制語(yǔ)言DCL，用于控制對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)，服務(wù)器的關(guān)閉、啟動(dòng)等

3.3數(shù)據(jù)類型、運(yùn)算符與表達(dá)式3.3.1簡(jiǎn)單數(shù)據(jù)類型常用的簡(jiǎn)單數(shù)據(jù)類型分為9個(gè)：Number(p,s)、smallint、integer、float、double、char(n)、varchar(n)、date、time，下面一一進(jìn)行介紹。（1）Number(p,s)。p是數(shù)字的整個(gè)位數(shù)，s是小數(shù)點(diǎn)后的位數(shù)。如：number(8,4)是一個(gè)8位數(shù)字，其中小數(shù)點(diǎn)后有4位。如果沒有特別指定，例如：number，則系統(tǒng)會(huì)設(shè)為p=5；s=0。（2）smallint。表示16位的整數(shù)。（3）integer。表示32位的整數(shù)。（4）float。表示32位的實(shí)數(shù)。（5）double。表示64位的實(shí)數(shù)。（6）char(n)。表示固定長(zhǎng)度為n的字符串，n不能超過(guò)254。（7）varchar(n)。表示可變長(zhǎng)度的字符串且其最大長(zhǎng)度為n，n不能超過(guò)4000。（8）date。表示日期，包含年份、月份。（9）time。表示時(shí)間，包含小時(shí)、分鐘、秒。3.3.2運(yùn)算符SQL的運(yùn)算符分為如下幾種：（1）算術(shù)運(yùn)算符。加（+），減（-），乘（*），除（/）。（2）比較運(yùn)算符。（3）邏輯運(yùn)算符（4）范圍運(yùn)算符（5）列表運(yùn)算符。3.3.3表達(dá)式SQL語(yǔ)言的表達(dá)式分為三類：①算術(shù)表達(dá)式；②關(guān)系表達(dá)式；③邏輯表達(dá)式。（1）算術(shù)表達(dá)式。用算術(shù)運(yùn)算符連接的表達(dá)式稱為算術(shù)表達(dá)式。如：sal+2000。（2）關(guān)系表達(dá)式。用比較運(yùn)算符連接的表達(dá)式稱為關(guān)系表達(dá)式。如：sal>2000。（3）邏輯表達(dá)式。用邏輯運(yùn)算符連接的表達(dá)式稱為邏輯表達(dá)式。如：name='HERY'ANDSAL=2000。3.4基本查詢SELECT語(yǔ)句的功能是從指定的數(shù)據(jù)庫(kù)的表中提取數(shù)據(jù)。其語(yǔ)法格式為：SELECT[ALL|DISTINCT]{字段名列表}FROM表名[WHERE{條件表達(dá)式}][GROUPBY{group_by_list}][ORDERBY{order_by_list}[ASC/DESC]];說(shuō)明：這條語(yǔ)句分為三大部分：（1）SELECT：SELECT子句用于指定檢索數(shù)據(jù)庫(kù)中的哪些列。（2）FROM：FROM子句用于指定從哪一個(gè)表或視圖中檢索數(shù)據(jù)。（3）WHERE：WHERE字句用于在查詢過(guò)程中的限制條件。關(guān)于DISTINCT子句、GROUPBY子句、ORDERBY子句的使用在本節(jié)后面介紹。3.4.1簡(jiǎn)單查詢簡(jiǎn)單查詢格式如下：SELECT{字段名列表}FROM表名;【例】假設(shè)關(guān)于一個(gè)企業(yè)信息的數(shù)據(jù)庫(kù)中有三個(gè)表：職工表EMP、部門表DEPT、崗位（或工種）表JOBLEVEL。其中：表EMP有如下字段：序號(hào)字段名稱字段類型字段說(shuō)明1name字符型職工姓名2sex字符型性別3birth日期型出生日期4phone字符型員工個(gè)人聯(lián)系方式5deptname字符型所在部門6salary實(shí)型數(shù)據(jù)工資7job字符型崗位（或工種）8title字符型職務(wù)（無(wú)職務(wù)，可以為空）這個(gè)表表主要要描述述企業(yè)業(yè)的職職工信信息，，數(shù)據(jù)據(jù)如下下namesexbirthdeptnametitlephonesalaryjob歐陽(yáng)軍女1986-09-01辦公室867988212500文員底雪洋男1998-10-09軟件開發(fā)部867988233800軟件開發(fā)李一嵐女1984-10-01軟件開發(fā)部139123454500軟件開發(fā)白海燕女1978-02-09市場(chǎng)部867988251700產(chǎn)品銷售劉紫羽女1989-01-09辦公室主任867988213000文員劉徐貝爾女1989-01-09軟件開發(fā)部經(jīng)理867988234500軟件開發(fā)周楚遠(yuǎn)男1989-01-09市場(chǎng)部經(jīng)理867988252000產(chǎn)品銷售表DEPT有如下下字段段序號(hào)字段名稱字段類型字段說(shuō)明1deptname字符型部門名2manager字符型部門經(jīng)理姓名3addr字符型部門地址4phone字符型電話號(hào)碼表JOBLEVEL有如下下字段段：序號(hào)字段名稱字段類型字段說(shuō)明1job字符型崗位名字2level1數(shù)字型工資等級(jí)13level2數(shù)字型工資等級(jí)24level3數(shù)字型工資等級(jí)33.4.2帶條件件的簡(jiǎn)簡(jiǎn)單查查詢SELECT{字段名名列表表}FROM表名WHERE{條件表表達(dá)式式}]這里講講的帶帶條件件的簡(jiǎn)簡(jiǎn)單查查詢指指單條條件的的查詢?cè)兒徒M組合條條件的的查詢?cè)儭Ｏ孪旅娣址謩e介介紹。。1．單條條件的的查詢?cè)儭纠坎樵兤笃髽I(yè)中中工資資超過(guò)過(guò)2100元（包包括2100元）的的所有有員工工的姓姓名及及其崗崗位。。SQL語(yǔ)句如如下：：SQL>SELECTname,salary,jobFROMEMPWHEREsalary>=2100;2．組合合條件件的查查詢組合條條件指指WHERE子句中中的條條件由由多個(gè)個(gè)條件件組成成，這這些組組合主主要是是由邏邏輯運(yùn)運(yùn)算符符（AND、OR、NOT）組成成?？纯聪旅婷娴睦印?。【例】找出企企業(yè)員員工中中做軟軟件開開發(fā)工工作并并且工工資在在4000元以上上的員員工姓姓名和和所在在部門門。SQL語(yǔ)句如如下：：SQL>SELECTname,deptname,salary,jobFROMEMPWHEREjob='軟件開開發(fā)'andsalary>4000;3.4.3單表查查詢應(yīng)應(yīng)用1．查詢?cè)兡承┬┳侄味尾煌挠浻涗浽诓樵冊(cè)児こ坛讨校?，可能能發(fā)現(xiàn)現(xiàn)有某某些相相同的的數(shù)據(jù)據(jù)，為為了只只顯示示不同同的數(shù)數(shù)據(jù)，，可以以使用用DISTINCT關(guān)鍵字字。【例】查詢企企業(yè)中中有多多少種種不同同的崗崗位。。SQL語(yǔ)句如如下：：SQL>SELECTDISTINCTjobFROMEMP;2．BETWEEN…AND關(guān)鍵字字的使使用BETWEEN…AND關(guān)鍵字字給出出了查查詢范范圍內(nèi)內(nèi)的字字段，，而NOTBETWEEN…AND則給出出了查查詢選選擇范范圍之之外的的某些些列。?！纠空页龉すべY在在2000元到3500元的所所有員員工。。SQL語(yǔ)句如如下：：SQL>SELECTname,salaryFROMEMPWHEREsalaryBETWEEN2000AND3500;3．模糊糊查詢?cè)兦懊娴牡牟樵冊(cè)兌际鞘蔷_確查詢?cè)儯俏椅覀兤狡綍r(shí)查查詢時(shí)時(shí)經(jīng)常常會(huì)使使用模模糊查查詢，，比如如“找找姓陳陳的先先生””，““找員員工名名字中中最后后一個(gè)個(gè)字是是琳的的小姐姐”等等，這這些只只給出出某個(gè)個(gè)字段段得到到部分分關(guān)鍵鍵詞而而不是是字段段的整整個(gè)數(shù)數(shù)據(jù)時(shí)時(shí)，就就可以以使用用LIKE關(guān)鍵字字了。。而NOTLIKE的意思思正好好相反反。這里介介紹幾幾個(gè)通通配符符，如如表3-3所示。。序號(hào)通配符說(shuō)明1%代表0到多個(gè)字符2_代表單個(gè)字符4．NULL和NOTNULL關(guān)鍵字字的使使用NULL和NOTNULL主要用用于找找出下下落不不明值值的記記錄。。例如如，在在公司司人員員招聘聘過(guò)程程中，，查詢?cè)兂錾猩形窗舶才琶婷嬖嚨牡暮蜻x選人名名單。。【例】查詢辦辦公室室中一一般員員工的的基本本信息息（不不帶職職務(wù)的的員工工）。。SQL語(yǔ)句如如下：：SQL>SELECT*FROMEMPWHEREdeptname="辦公室室"ANDtitle=null;5．查詢?cè)兘Y(jié)果果排序序ORDERBY子句使使得SQL在顯示示查詢?cè)兘Y(jié)果果時(shí)將將各返返回行行按順順序排排列，，返回回行的的排列列順序序由ORDERBY子句指指定的的表達(dá)達(dá)式的的值A(chǔ)SC或DESC確定。。SELECT[ALL|DISTINCT]{字段名名列表表}FROM表名[WHERE{條件表表達(dá)式式}][GROUPBY{group_by_list}][ORDERBY{order_by_list}[ASC/DESC]];其中：：ASC：升序序。表表示按按照遞遞增順順序從從低到到高對(duì)對(duì)指定定的列列值排排序。。DESC：降序序。表表示按按照遞遞減順順序從從高到到低對(duì)對(duì)指定定的列列值排排序。。6．字段段運(yùn)算算查詢?cè)冊(cè)跀?shù)據(jù)據(jù)庫(kù)查查詢時(shí)時(shí)，我我們希希望把把表中中的數(shù)數(shù)據(jù)進(jìn)進(jìn)行基基本的的算術(shù)術(shù)運(yùn)算算，然然后顯顯示出出來(lái)。。常見的的算術(shù)術(shù)運(yùn)算算有：：+（加））、-（減））、*（乘乘）、、/（除））四種種?！纠咳绻麑④浖_發(fā)發(fā)部的的所有有員工工工資資增加加1000元，請(qǐng)請(qǐng)顯示示出此此部門門的員員工姓姓名和和工資資。SQL語(yǔ)句如如下：：SQL>SELECTname,salary+1000FROMEMPWHEREdeptname='軟件開開發(fā)部部';7．rowid和rownumrowid和rownum是很有有用的的兩個(gè)個(gè)數(shù)據(jù)據(jù)庫(kù)表表中隱隱含的的字段段。rowid是數(shù)據(jù)據(jù)庫(kù)內(nèi)內(nèi)部的的十六六進(jìn)制制的數(shù)數(shù)據(jù)地地址，，表明明信息息放在在第幾幾塊第第幾行行。rownum是行號(hào)號(hào)，并并且順順序排排列，，根據(jù)據(jù)你的的select語(yǔ)句的的執(zhí)行行動(dòng)態(tài)態(tài)生成成。它它表示示是表表的邏邏輯上上的第第幾行行【例】區(qū)別rowid和rownum。SQL>SELECTrownum,rowid,enameFROMEMP;執(zhí)行結(jié)結(jié)果：：rownumrowidename-----------------------------------------------------------------1D9B3333C.0003.0002白海燕燕2DAAAA66D.0002.0002周楚遠(yuǎn)遠(yuǎn)3BAA33331.0003.0002底雪洋洋4D555DFD.0005.0002歐陽(yáng)軍軍5DB133344.0006.0002劉徐貝貝爾………………………………………….8．別名名的使使用在前面面的查查詢中中，顯顯示的的結(jié)果果都是是用字字段名名來(lái)顯顯示的的，這這樣不不是很很直觀觀，我我們可可以給給顯示示的字字段名名起一一個(gè)直直觀的的名字字（叫叫“別別名””），，以這這個(gè)別別名來(lái)來(lái)顯示示。格格式是是：字段名名[as]別名在“字字段名名”和和“別別名””之間間加空空格或或者““as””即可，，可以以將默默認(rèn)的的字段段名以以設(shè)定定的名名稱顯顯示。?！纠匡@示出出軟件件開發(fā)發(fā)部的的每個(gè)個(gè)員工工工資資增加加1000元后的的信息息。SQL>SELECTname員工姓姓名,salary+1000增加工工資FROMEMPWHEREdeptname='軟件開開發(fā)部部';3.4.4多表查查詢多表查查詢是是指查查詢結(jié)結(jié)果要要從兩兩個(gè)以以上的的表中中檢索索結(jié)果果。【例】查詢員員工““劉紫紫羽””所在在的部部門地地址。。分析：：首先先確定定要求求中的的關(guān)鍵鍵字：：劉紫紫羽，，部門門地址址。然然后確確定查查詢的的表有有哪些些。““部門門名稱稱”在在部門門表DEPT中，但但是““劉紫紫羽””是員員工名名稱，，“員員工名名稱””在表表EMP中。所所以我我們確確定查查詢的的表涉涉及兩兩個(gè)表表：EMP和DEPT。SQL語(yǔ)句如如下：：SQL>SELECTd.addrFROMEMPe,DEPTdWHERE='劉紫羽羽';3.5子查詢?cè)內(nèi)绻衬骋粋€(gè)個(gè)SELECT命令（（查詢?cè)?）出現(xiàn)現(xiàn)在另另一個(gè)個(gè)SQL命令（（查詢?cè)?）的一一個(gè)子子句中中，則則稱查查詢1是查詢?cè)?的子查查詢，，又稱稱為““嵌套套查詢?cè)儭薄?。在SELECT查詢語(yǔ)語(yǔ)句里里可以以嵌入入SELECT查詢語(yǔ)語(yǔ)句，，稱為為嵌套套查詢?cè)儭Ｓ杏行鴷袑?nèi)嵌嵌的SELECT語(yǔ)句稱稱為““子查查詢””，子子查詢?cè)冃纬沙傻慕Y(jié)結(jié)果又又稱為為父查查詢的的條件件。下面是是嵌套套查詢?cè)兊母窀袷剑海篠ELECT字段列列表FROM表名WHERE操作符符(SELECTselect_listFROMtable);3.5.1簡(jiǎn)單子子查詢?cè)儭纠坎樵儐T員工中中所有有高于于員工工底雪雪洋薪薪水的的員工工信息息。SQL語(yǔ)句如如下：：SQL>SELECTname,sex,salaryFROMEMPWHEREsalary>=(SELECTsalaryFROMEMPWHEREname='底雪雪洋洋');3.5.2單行行子子查查詢?cè)儐涡行凶幼硬椴樵冊(cè)兊牡慕Y(jié)結(jié)果果僅僅返返回回一一行行記記錄錄。。一一般般使使用用比比較較運(yùn)運(yùn)算算符符（（<、>、<=、>=、<>）。?！纠块喿x讀下下面面的的SQL語(yǔ)句句，，并并解解釋釋其其含含義義。。SQL>SELECTname,sex,salaryFROMEMPWHEREjob=(SELECTjobFROMEMPWHEREname='底雪雪洋洋')ANDsalary>(SELECTsalaryFROMEMPWHEREname='李一一嵐嵐');3.6集合合運(yùn)運(yùn)算算3.6.1UNIONUNION是集集合合的的并并運(yùn)運(yùn)算算，，并并運(yùn)運(yùn)算算的的結(jié)結(jié)果果是是參參加加運(yùn)運(yùn)算算的的集集合合元元素素的的總總和和，，相相當(dāng)當(dāng)于于數(shù)數(shù)學(xué)學(xué)的的加加法法。。如如圖圖3-1所示示。。其其中中集集合合A和集集合合B的交交叉叉部部分分為為集集合合C。集合合A集合合B3.6.2INTERSECTINTERSECT是集集合合的的交交運(yùn)運(yùn)算算。。交交運(yùn)運(yùn)算算的的結(jié)結(jié)果果是是參參加加運(yùn)運(yùn)算算的的集集合合元元素素的的公公共共元元素素。。假設(shè)設(shè)：：集集合合A={1,2,3}，集集合合B={2,3,5}，那那么么執(zhí)執(zhí)行行INTERSECT運(yùn)算算的的結(jié)結(jié)果果是是{2,3}。數(shù)學(xué)學(xué)符符號(hào)號(hào)記記為為：：A∩∩B。在SQL語(yǔ)言言中中它它返返回回查查詢?cè)兘Y(jié)結(jié)果果中中的的相相同同部部分分。?！纠空页龀鯽ccount、research、sales中都都存存在在的的工工種種。。SQL>SELECTjobFROMaccountINTERSECTSELECTjobFROMresearchINTERSECTSELECTjobFROMsales;3.6.3MINUSMINUS是集合的差運(yùn)運(yùn)算。差運(yùn)算算的結(jié)果是參參加運(yùn)算的集集合元素中從從第一個(gè)集合合中去掉二者者相同部分元元素后剩余的的元素，相當(dāng)當(dāng)于數(shù)學(xué)的減減法。假設(shè)：集合A={1,2,3}，集合B={2,3,5}，那么執(zhí)行MINUS運(yùn)算的結(jié)果是是{1}。數(shù)學(xué)符號(hào)記為為：A-B。在SQL語(yǔ)言中它返回回在第一次查查詢結(jié)果中而而不在第二次次查詢結(jié)果中中的記錄。【例】找出在account中存在而在research中不存在的工工種。SQL>SELECTjobFROMaccountMINUSSELECTjobFROMresearch;3.7聚合函數(shù)常用的聚合函函數(shù)包括如下下幾種函數(shù)：：AVG()、MIN()、MAX()、SUM()、COUNT()。1．MAX格式：MAX(x)返回一個(gè)字段段的所有值中中的最大值。。如果x是VARCHAR2數(shù)據(jù)類型，返返回一個(gè)字符符串；如果x是一個(gè)DATE數(shù)據(jù)類型，返返回一個(gè)日期期；如果x是number數(shù)據(jù)類型，返返回一個(gè)數(shù)字字。【例】查詢EMP表中的最高工工資。SQL>SELECTmax(salary)AS最高薪水FROMEMP;2．MIN格式：MIN(x)返回一個(gè)字段段的所有值中中的最小值。。如果字段是是空的，函數(shù)數(shù)MIN()返回空值。【例】查詢EMP表中的最低工工資。SQL>SELECTMIN(salary)AS最少薪水FROMEMP;【例】查詢公司中工工資最高和工工資最低的員員工。SQL>SELECTename,salFROMEMPHAVINGsal=(SELECTMAX(SAL)FROMEMP)ANDsal=(SELECTMIN(SAL)FROMEMP);3．AVG格式：AVG({x}返回指定字段段中的平均值值。注意，函函數(shù)AVG()只能對(duì)數(shù)值型型字段使用，，即x的數(shù)據(jù)類型只只能是數(shù)值型型，如果空值值即AVG('')，忽略不計(jì)。。缺省設(shè)置為為ALL。【例】查詢軟件開發(fā)發(fā)部人員的平平均工資。SQL>SELECTavg(salary)AS平均工資FROMEMPWHEREdeptname='軟件開發(fā)部';4．COUNT格式：COUNT(*)返回查詢中行行的數(shù)目，缺缺省值是ALL，*表示返回回所有的行。。用來(lái)統(tǒng)計(jì)一一個(gè)表