MES開(kāi)發(fā)與應(yīng)用 課件 1.2.3 數(shù)據(jù)庫(kù)常用介紹

——基礎(chǔ)語(yǔ)法、基礎(chǔ)操作4.2.3數(shù)據(jù)庫(kù)常用介紹查詢數(shù)據(jù)更新數(shù)據(jù)使用視圖管理事務(wù)處理了解SQL使用存儲(chǔ)過(guò)程知識(shí)回顧學(xué)習(xí)目標(biāo)通過(guò)本節(jié)學(xué)習(xí)可以：重點(diǎn)學(xué)習(xí)如何查詢數(shù)據(jù)重點(diǎn)學(xué)習(xí)如何更新數(shù)據(jù)掌握視圖的使用掌握存儲(chǔ)過(guò)程的使用了解如何管理事務(wù)處理01了解SQL了解SQLSQL（發(fā)音為字母S-Q-L或sequel）是結(jié)構(gòu)化查詢語(yǔ)言（StructuredQueryLanguage）的縮寫(xiě)。SQL是一種專(zhuān)門(mén)用來(lái)與數(shù)據(jù)庫(kù)溝通的語(yǔ)言。SQL有如下的優(yōu)點(diǎn)：SQL不是某個(gè)特定數(shù)據(jù)庫(kù)供應(yīng)商專(zhuān)有的語(yǔ)言。幾乎所有重要的DBMS都支持SQL，所以學(xué)習(xí)此語(yǔ)言使你幾乎能與所有數(shù)據(jù)庫(kù)打交道。SQL簡(jiǎn)單易學(xué)。它的語(yǔ)句全都是由有很強(qiáng)描述性的英語(yǔ)單詞組成，而且這些單詞的數(shù)目不多。SQL雖然看上去很簡(jiǎn)單，但實(shí)際上是一種強(qiáng)有力的語(yǔ)言，靈活使用其語(yǔ)言元素，可以進(jìn)行非常復(fù)雜和高級(jí)的數(shù)據(jù)庫(kù)操作。了解SQL使用PLSQLDeveloperPLSQLDeveloper工具是Oracle常用的數(shù)據(jù)庫(kù)管理工具。在安裝PLSQLDeveloper之前，要先安裝好Oracle客戶端。Oracle客戶端的安裝很簡(jiǎn)單，下載解壓后放到某個(gè)目錄下即可。安裝Oracle客戶端使用PLSQLDeveloperPLSQLDeveloper的安裝很簡(jiǎn)單，按照提示安裝即可。安裝完后，打開(kāi)PLSQLDeveloper，打開(kāi)配置選項(xiàng)窗口，按下面說(shuō)明進(jìn)行配置，配置完后關(guān)閉并重新打開(kāi)PLSQLDeveloper。安裝和配置PLSQLDeveloper指定Oracle客戶端的目錄指定oci.dll的目錄，在Oracle客戶端目錄下使用PLSQLDeveloper連接數(shù)據(jù)庫(kù)需要知道數(shù)據(jù)庫(kù)的IP、端口、服務(wù)名以及用戶名和密碼。連接數(shù)據(jù)庫(kù)用戶名密碼ip:port/service_name02查詢數(shù)據(jù)查詢數(shù)據(jù)_檢索數(shù)據(jù)SELECT

<列名>

FROM

<表名>

WHERE

<條件>;該SQL包含了SELECT、FROM以及WHERE三個(gè)子句。SELECT：子句用于指定要查詢的列名稱。FROM：子句指定查詢的表名稱。WHERE：子句指定過(guò)濾數(shù)據(jù)的條件。一般來(lái)說(shuō)，以分號(hào)作為單個(gè)SQL語(yǔ)句的結(jié)尾，但不是強(qiáng)制的。如果有多個(gè)SQL語(yǔ)句，可以使用分號(hào)區(qū)分不同的SQL，所以加上分號(hào)是一個(gè)好習(xí)慣。基本查詢語(yǔ)法查詢數(shù)據(jù)_檢索數(shù)據(jù)SELECTdnameFROMdept;把要查詢的列放在SELECT子句后面，非常簡(jiǎn)單。檢索單個(gè)列查詢數(shù)據(jù)_檢索數(shù)據(jù)SELECTdeptno,dname,locFROMdept;指定檢索多個(gè)列，列與列之間使用逗號(hào)分隔開(kāi)。如果檢索所有列，可以顯示指定所有字段，也可以只指定*，*表示所有字段，如下所示：SELECT*FROMdept;檢索多個(gè)列查詢數(shù)據(jù)_檢索數(shù)據(jù)SELECTjobFROMemp;如果希望去掉上面重復(fù)的數(shù)據(jù)，比如SALESMAN只顯示一個(gè)，可以加上DISTINCT關(guān)鍵字去重：SELECTDISTINCTjobFROMemp;去除重復(fù)數(shù)據(jù)查詢數(shù)據(jù)_檢索數(shù)據(jù)SELECTdeptnoASno,dnameASname,locFROMdept;當(dāng)字段命名較長(zhǎng)時(shí)，可以使用別名簡(jiǎn)化。如果是表達(dá)式，可以使用別名命名，比如a+bASsalary。當(dāng)從多張表查詢數(shù)據(jù)時(shí)，如果有相同的字段，可以使用別名進(jìn)行區(qū)分。指定別名查詢數(shù)據(jù)_排序數(shù)據(jù)SELECT*FROMempORDERBYjob;通過(guò)使用ORDERBY子句對(duì)數(shù)據(jù)排序。如果不排序，數(shù)據(jù)一般將以它在底層表中出現(xiàn)的順序顯示，這有可能是數(shù)據(jù)最初添加到表中的順序。排序數(shù)據(jù)查詢數(shù)據(jù)_排序數(shù)據(jù)SELECT*FROMemptORDERBYsal,ename;可以指定按多個(gè)列排序，使用逗號(hào)隔開(kāi)。首先會(huì)按前面的字段排序，如果值相同則按后面的字段排序。按多個(gè)列排序查詢數(shù)據(jù)_排序數(shù)據(jù)SELECTempno,ename,job,mgr,hiredate,sal,commFROMempORDERBY2,3;SELECT子句先于ORDERBY執(zhí)行，所以在排序時(shí)可以得到字段所在位置然后進(jìn)行排序。按列的位置排序查詢數(shù)據(jù)_排序數(shù)據(jù)SELECTempno,sal,ename,job,mgrFROMempORDERBYsalASC,enameDESC;ASC表示升序，默認(rèn)排序方式，DESC表示降序。如果想在多個(gè)列上進(jìn)行降序排序，必須對(duì)每一列指定DESC關(guān)鍵字。指定排序方向查詢數(shù)據(jù)_過(guò)濾數(shù)據(jù)比較運(yùn)算符查詢數(shù)據(jù)_過(guò)濾數(shù)據(jù)SELECT*FROMempWHEREjob='MANAGER';要查詢某個(gè)值的數(shù)據(jù)，可以使用=運(yùn)算符判斷。匹配過(guò)濾一般把字段放左邊，具體值放右邊字符串類(lèi)型要放到引號(hào)內(nèi)，即使這個(gè)字串符是數(shù)值查詢數(shù)據(jù)_過(guò)濾數(shù)據(jù)SELECT*FROMempWHEREjob<>'SALESMAN';SELECT*FROMempWHEREjob!='SALESMAN';可以使用<>也可以使用!=，不過(guò)有的數(shù)據(jù)庫(kù)不支持!=，建議使用<>。不匹配過(guò)濾查詢數(shù)據(jù)_過(guò)濾數(shù)據(jù)SELECT*FROMempWHEREsal>3000;SELECT*FROMempWHEREsal<1800;SELECT*FROMempWHEREsal>2000ANDsal<3000;SELECT*FROMempWHEREsal>=1250ANDsal<=1600;SELECT*FROMempWHEREsalBETWEEN1250AND1600;范圍過(guò)濾..between..and....>=..and..<=..查詢數(shù)據(jù)_過(guò)濾數(shù)據(jù)SELECT*FROMempWHEREcomm=NULL;SELECT*FROMempWHEREcommISNULL;SELECT*FROMempWHEREcommISNOTNULL;SELECT*FROMempWHEREcomm<>300;當(dāng)碰到NULL值時(shí)，會(huì)被判定為不滿足條件，所以這里不會(huì)查出NULL值的數(shù)據(jù)。需要添加下面的條件才能把NULL的數(shù)據(jù)查出來(lái)：SELECT*FROMempWHEREcomm<>300ORcommISNULL;空值過(guò)濾這種方式無(wú)法查詢?yōu)榭罩档臄?shù)據(jù)針對(duì)空值，需要使用ISNULL或ISNOTNULL不會(huì)把查詢出包含NULL的數(shù)據(jù)查詢數(shù)據(jù)_過(guò)濾數(shù)據(jù)SELECT*FROMempWHERENOT(job='SALESMAN’);WHERE子句中的NOT操作符有且只有一個(gè)功能，那就是否定其后所跟的任何條件。這里當(dāng)匹配條件時(shí)返回TRUE，然后NOT取反得到FALSE，這樣匹配條件的數(shù)據(jù)被過(guò)濾了，查出的是未匹配的數(shù)據(jù)。NOT運(yùn)算符查詢數(shù)據(jù)_過(guò)濾數(shù)據(jù)SELECT*FROMempWHEREjob='SALESMAN'ANDsal>1300;SELECT*FROMempWHEREjob='SALESMAN'ORdeptno=30;AND運(yùn)算符在其兩側(cè)的查詢條件都成立時(shí)整個(gè)查詢條件才成立，其意思相當(dāng)于“并且”。OR運(yùn)算符在其兩側(cè)的查詢條件有一個(gè)成立時(shí)整個(gè)查詢條件都成立，其意思相當(dāng)于“或者”。AND、OR運(yùn)算符需兩側(cè)條件同時(shí)成立僅需其中一個(gè)條件成立查詢數(shù)據(jù)_過(guò)濾數(shù)據(jù)SELECT*FROMempWHEREjobIN('CLERK','PRESIDENT’);SELECT*FROMempWHEREjob='CLERK'ORjob='PRESIDENT';取出CLERK和PRESIDENT的所有數(shù)據(jù)，功能上與OR是一樣的。當(dāng)值特別多時(shí)，相比較OR而言，使用IN書(shū)寫(xiě)會(huì)更簡(jiǎn)潔。IN運(yùn)算符得到的結(jié)果是一樣的查詢數(shù)據(jù)_過(guò)濾數(shù)據(jù)SELECT*FROMempWHEREsal+comm=1900;可以拿字段進(jìn)行計(jì)算形成一個(gè)新的字段。既可以在WHERE子句也可以在SELECT子句對(duì)字段進(jìn)行計(jì)算。SELECTsal+comm,t.*FROMemptWHEREsal+comm=1900;計(jì)算字段查詢數(shù)據(jù)_過(guò)濾數(shù)據(jù)SELECT*FROMempWHEREename||','||job='ALLEN,SALESMAN';可以使用||把字符串拼接起來(lái)形成一個(gè)新的字段，有些數(shù)據(jù)庫(kù)使用+拼接。既可以在WHERE子句也可以在SELECT子句對(duì)字段進(jìn)行拼接。SELECTename||','||job,t.*FROMemptWHEREename||','||job='ALLEN,SALESMAN';

拼接字段查詢數(shù)據(jù)_模糊匹配在條件中可以使用通配符%或_進(jìn)行模糊匹配，%表示匹配多個(gè)任意字符，_只能匹配一個(gè)任意字符。SELECT*FROMempWHEREenameLIKE'J%’;查出J開(kāi)頭的所有數(shù)據(jù)，不管J后面有多少個(gè)字符。SELECT*FROMempWHEREenameLIKE'%N';查出以N結(jié)尾的所有數(shù)據(jù)，不管N前面有多少個(gè)字符都能匹配出來(lái)。SELECT*FROMempWHEREenameLIKE'%AR%';查出只要包含了AR的所有數(shù)據(jù)，不管AR前后有多少個(gè)字符。模糊匹配查詢數(shù)據(jù)_聚合數(shù)據(jù)SELECTCOUNT(*),SUM(sal),MIN(mgr),MAX(mgr),AVG(sal)FROMemp;聚合函數(shù)用于對(duì)所有數(shù)據(jù)或分組數(shù)據(jù)進(jìn)行計(jì)算。COUNT統(tǒng)計(jì)行數(shù)，SUM匯總，MIN統(tǒng)計(jì)最小值，MAX統(tǒng)計(jì)最大值，AGV計(jì)算平均值；聚合函數(shù)會(huì)忽略NULL值，在使用COUNT的時(shí)候要特別注意，如果指定的是*不會(huì)忽略NULL值，如果指定的是某個(gè)字段，該字段有NULL會(huì)被忽略，意味著所在行不會(huì)被統(tǒng)計(jì)。SELECTCOUNT(*),COUNT(mgr)FROMempt;聚合函數(shù)其中一行的MRG是NULL不會(huì)被統(tǒng)計(jì)查詢數(shù)據(jù)_聚合數(shù)據(jù)可以使用GROUPBY子句對(duì)數(shù)據(jù)進(jìn)行組，GROUPBY后面指定分組字段，會(huì)把字段值相同的視為一組，然后可以基于分組進(jìn)行統(tǒng)計(jì)。如果分組列中包含具有NULL值的行，NULL值將單獨(dú)劃分為一組。SELECTjob,COUNT(*),SUM(sal),AVG(sal)FROMempGROUPBYjob;分組數(shù)據(jù)查詢數(shù)據(jù)_聚合數(shù)據(jù)在分組后，可以使用HAVING子句對(duì)分組統(tǒng)計(jì)進(jìn)一步過(guò)濾。HAVING是基于分組計(jì)算過(guò)濾的，必須結(jié)合GROUPBY子句一起使用。SELECTjob,COUNT(*),SUM(sal),AVG(sal)FROMempGROUPBYjobHAVINGCOUNT(*)>2;該示例統(tǒng)計(jì)出了每個(gè)分組超過(guò)兩行的數(shù)據(jù)：過(guò)濾分組03更新數(shù)據(jù)更新數(shù)據(jù)_插入數(shù)據(jù)INSERTINTO<表名>(字段1,字段2,…)VALUES(值1,值2,…);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7935,'XIAOLI','CLERK',7566,to_date('1985-1-23','yyyy-mm-dd'),1200,NULL,20);在表名旁邊指定字段名稱，在VALUES子句中指定字段對(duì)應(yīng)值。如果對(duì)所有字段賦值，可以不用指定這些字段名稱，只要在VALUES子句指定列值即可。如果僅對(duì)部分字段賦值，那么就必須指定這些字段名稱。對(duì)于設(shè)置為非空的字段，必須要賦值。INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)SELECT7935,'XIAOLI','CLERK',7566,to_date('1985-1-23','yyyy-mm-dd'),1200,NULL,20FROMdual;可以把查詢語(yǔ)句的數(shù)據(jù)插入到表中，意味著可以通過(guò)這種方式把多行的數(shù)據(jù)插入到表中。插入數(shù)據(jù)更新數(shù)據(jù)_修改、刪除數(shù)據(jù)UPDATE<表名>SET字段1=值1,字段2=值2,字段3=值3,…WHERE;UPDATEempSETename='XIAOLI2'WHEREempno=7935;修改滿足條件的數(shù)據(jù)。DELETEFROM<表名>WHERE...;DELETEFROMemp;刪除所有數(shù)據(jù)。DELETEFROMempWHEREempno=7935;刪除滿足條件的數(shù)據(jù)。注意：為了防止誤修改和刪除，在執(zhí)行前要仔細(xì)檢查，有條件的話要先做備份。修改、刪除數(shù)據(jù)04使用視圖更新數(shù)據(jù)_使用視圖視圖并不存放數(shù)據(jù)，相當(dāng)于一張?zhí)摂M表，里面包含了一段SQL語(yǔ)句，然后基于這個(gè)視圖查詢數(shù)據(jù)。使用視圖查詢數(shù)據(jù)的好處如下：重用SQL語(yǔ)句。簡(jiǎn)化復(fù)雜的SQL操作。在編寫(xiě)查詢后，可以方便地重用它而不必知道其基本查詢細(xì)節(jié)。使用表的一部分而不是整個(gè)表。保護(hù)數(shù)據(jù)?？梢允谟栌脩粼L問(wèn)表的特定部分的權(quán)限，而不是整個(gè)表的訪問(wèn)權(quán)限。更改數(shù)據(jù)格式和表示。視圖可返回與底層表的表示和格式不同的數(shù)據(jù)。了解視圖更新數(shù)據(jù)_使用視圖SELECT*FROMempa,deptbWHEREa.deptno=b.deptno;針對(duì)這兩張兩表聯(lián)結(jié)的查詢，可以將其創(chuàng)建為視圖：CREATEVIEWemp_allASSELECTa.empno,a.ename,a.job,a.mgr,a.hiredate,a.sal,m,a.deptno,b.dnameFROMempa,deptbWHEREa.deptno=b.deptno;通過(guò)視圖可以簡(jiǎn)化查詢語(yǔ)句：SELECT*FROMemp_all;創(chuàng)建視圖05使用存儲(chǔ)過(guò)程更新數(shù)據(jù)_使用存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程（StoredProcedure）是一組為了完成特定功能的SQL語(yǔ)句集，它存儲(chǔ)在數(shù)據(jù)庫(kù)中，一次編譯后永久有效，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它。使用存儲(chǔ)過(guò)程的原因：通過(guò)把處理封裝在一個(gè)易用的單元中，可以簡(jiǎn)化復(fù)雜的操作。重復(fù)使用。存儲(chǔ)過(guò)程可以重復(fù)使用，從而可以減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員的工作量。減少網(wǎng)絡(luò)流量。存儲(chǔ)過(guò)程位于服務(wù)器上，調(diào)用的時(shí)候只需要傳遞存儲(chǔ)過(guò)程的名稱以及參數(shù)就可以了，因此降低了網(wǎng)絡(luò)傳輸?shù)臄?shù)據(jù)量。簡(jiǎn)化對(duì)變動(dòng)的管理。如果表名、列名或業(yè)務(wù)邏輯（或別的內(nèi)容）有變化，那么只需要更改存儲(chǔ)過(guò)程的代碼。使用它的人員甚至不需要知道這些變化。了解存儲(chǔ)過(guò)程更新數(shù)據(jù)_使用存儲(chǔ)過(guò)程下面是Oracle數(shù)據(jù)庫(kù)創(chuàng)建存儲(chǔ)過(guò)程的方法：調(diào)用存儲(chǔ)過(guò)程：創(chuàng)建存儲(chǔ)過(guò)程CREATEORREPLACEPROCEDUREp_testAS

v_sal_totalNUMBER;BEGIN

FORvIN(SELECT*FROMemp)LOOPv_sal_total:=nvl(v_sal_total,0)+v.sal;ENDLOOP;dbms_output.put_line(v_sal_total);END;BEGIN

p_test;END;定義存儲(chǔ)過(guò)程名稱定義變量BEGIN..END內(nèi)編寫(xiě)業(yè)務(wù)邏輯06