《Oracle數(shù)據(jù)庫(kù)應(yīng)用開(kāi)發(fā)及實(shí)踐》課件04-第4章 SQL 應(yīng)用基礎(chǔ)

-1-本章目標(biāo)-1了解SQL語(yǔ)句的特點(diǎn)以及編寫(xiě)規(guī)則掌握列查詢(xún)、條件查詢(xún)、運(yùn)算符查詢(xún)、數(shù)據(jù)排序和聯(lián)合查詢(xún)掌握內(nèi)連接和外連接的區(qū)別及其用法

掌握子查詢(xún)的語(yǔ)法及其應(yīng)用理解函數(shù)的分類(lèi)和作用以及單行函數(shù)和分組函數(shù)的定義掌握數(shù)值型函數(shù)、字符函數(shù)、日期函數(shù)、轉(zhuǎn)換函數(shù)以及常用的分組函數(shù)的用法-2-本章目標(biāo)-2掌握使用INSERTINTO語(yǔ)句插入數(shù)據(jù)，以及使用用子查詢(xún)復(fù)制數(shù)據(jù)掌握使用UPDATE語(yǔ)句更新數(shù)據(jù)，以及根據(jù)子查詢(xún)更新數(shù)據(jù)掌握特殊數(shù)據(jù)在進(jìn)行數(shù)據(jù)插入、更新時(shí)的處理掌握使用DELETE語(yǔ)句刪除表格中的數(shù)據(jù)掌握在DML操作中COMMIT、ROLLBACK、SAVEPOINT的用法

-3-SQL（StructuredQueryLanguage）是“結(jié)構(gòu)化查詢(xún)語(yǔ)言”，語(yǔ)言結(jié)構(gòu)簡(jiǎn)潔、功能強(qiáng)大、簡(jiǎn)單易學(xué)。特點(diǎn)：SQL語(yǔ)言是統(tǒng)一的語(yǔ)言SQL語(yǔ)言語(yǔ)法簡(jiǎn)單易學(xué)SQL語(yǔ)言是所有關(guān)系數(shù)據(jù)庫(kù)的公共語(yǔ)言SQL語(yǔ)言概述-4-數(shù)據(jù)查詢(xún)語(yǔ)言(DQL-DataQueryLanguage)包括SELECT；數(shù)據(jù)操縱語(yǔ)言(DML-DataManipulationLanguage)包括INSERT、UPDATE、DELETE；數(shù)據(jù)定義語(yǔ)言(DDL-DataDefinitionLanguage)包括CREATE、ALTER、DROP等；數(shù)據(jù)控制語(yǔ)言(DCL-DataControlLanguage)包括COMMIT、ROLLBACK等。SQL分類(lèi)-5-SQL關(guān)鍵字在執(zhí)行時(shí)并不區(qū)分大小寫(xiě)，為了統(tǒng)一標(biāo)準(zhǔn)，通常指定SQL關(guān)鍵字需要大寫(xiě)。對(duì)象名和列名不區(qū)分大小寫(xiě)，為了統(tǒng)一標(biāo)準(zhǔn)，通常SQL的對(duì)象名或列名指定為小寫(xiě)。字符值和日期值區(qū)分大小寫(xiě)。適當(dāng)?shù)脑黾涌崭窈涂s進(jìn)，使程序更易讀。使用注釋增強(qiáng)程序可讀性。單行注釋?zhuān)?-多行注釋?zhuān)?*和*/

SQL語(yǔ)句編寫(xiě)規(guī)則-6-語(yǔ)句格式：*指示查詢(xún)數(shù)據(jù)表的所有列；DISTINCT指示消除結(jié)果集中的重復(fù)記錄；column指定需要查詢(xún)的表的列名，多列之間用逗號(hào)“,”分隔；alias指定列（表達(dá)式）的別名；table_name指定需要查詢(xún)的表的名字。列查詢(xún)

SELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable_name-7-語(yǔ)法格式：WHERE常用來(lái)構(gòu)成一個(gè)限制檢索表中行數(shù)據(jù)的條件表達(dá)式；<conditionexpression>進(jìn)行數(shù)據(jù)篩選的條件表達(dá)式，在條件表達(dá)式中可以使用常用的比較運(yùn)算、邏輯運(yùn)算符等。條件查詢(xún)SELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable[WHERE<conditionexpression>];-8-語(yǔ)法格式column_order應(yīng)該是查詢(xún)結(jié)果中的一個(gè)字段，且可以進(jìn)行大小比較(如數(shù)值、時(shí)間日期等)；ASC代表升序，默認(rèn)值可以省略；DESC代表降序。排序SELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable[,<table2>[,…]][WHERE<conditionexpression>][ORDERBY<column_order>[ASC|DESC]];-9-顯示10號(hào)部門(mén)的員工名稱(chēng)。顯示工資在1000到3000之間的員工信息。顯示首字母為J的員工。按照工資的升序顯示員工信息。查詢(xún)實(shí)例selectdeptno,enamefromempwheredeptno=10;;selectename,salfromempwheresalbetween1000and3000;selectename,deptnofromempwhereenamelike'J%';selectename,salfromemporderbysalasc;-10-語(yǔ)句格式：滿足條件：兩個(gè)查詢(xún)具有相同的列數(shù)；兩個(gè)查詢(xún)采用相同的列順序；兩個(gè)查詢(xún)對(duì)應(yīng)列的數(shù)據(jù)類(lèi)型兼容聯(lián)合查詢(xún)SELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable[,<table2>[,…]][WHERE<conditionexpression>]UNIONSELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable[,<table2>[,…]][WHERE<conditionexpression>]-11-內(nèi)連接顯式內(nèi)連接：INNERJOIN用于連接兩個(gè)表，ON用于指定連接條件隱式內(nèi)連接：相等連接外連接左外連接：LEFTJOIN-ON右外連接：RIGHTJOIN-ON完整外連接：FULLJOIN-ON交叉連接外連接-12-單行子查詢(xún)：?jiǎn)涡斜容^符（>、<、=、>=、<=，<>）多行子查詢(xún)：ALL、ANY、IN多列子查詢(xún)：operator是指多行運(yùn)算符(IN、ANY、ALL)column1、column2是指成對(duì)比較的列。相關(guān)子查詢(xún)子查詢(xún)SELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable[,<table2>[,…]][WHEREcolumn1,column2[,…]operator(SELECTcolumn1,column2[,…]FROMtable);SELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable1t1WHEREcolumn1operator(SELECTcolumn1,column2FROMtable2t2WHEREexpr1=t1.expr2);-13-等值連接，顯示員工名稱(chēng)和部門(mén)名稱(chēng)自連接，顯示員工的姓名和上級(jí)的姓名左外連接，顯示所有部門(mén)及部門(mén)員工的信息。聯(lián)合查詢(xún)實(shí)例selectw.ename"員工姓名",m.ename"上級(jí)"fromempw,empmwherew.mgr=m.empno(+);selectdname,enamefromdeptleftjoinempon(dept.deptno=emp.deptno);selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno;-14-定義：函數(shù)是用于執(zhí)行數(shù)據(jù)處理和復(fù)雜計(jì)算的，通過(guò)對(duì)一組數(shù)據(jù)進(jìn)行一系列運(yùn)算，得到最終需要的輸出結(jié)果。函數(shù)分類(lèi)：?jiǎn)涡泻瘮?shù)：每行作為一個(gè)輸入?yún)?shù)，經(jīng)過(guò)函數(shù)計(jì)算得到每行的計(jì)算結(jié)果多行函數(shù)：對(duì)多行輸入值進(jìn)行計(jì)算，得到多行對(duì)應(yīng)的單個(gè)結(jié)果。另外，單行函數(shù)可以進(jìn)行嵌套，即函數(shù)可以作為另一個(gè)函數(shù)的輸入?yún)?shù)。系統(tǒng)函數(shù)-15-數(shù)值型函數(shù)：數(shù)值型函數(shù)對(duì)數(shù)字類(lèi)型數(shù)據(jù)進(jìn)行計(jì)算。

字符函數(shù)：字符函數(shù)用于對(duì)字符類(lèi)型數(shù)據(jù)進(jìn)行處理和計(jì)算。日期函數(shù)：日期類(lèi)型函數(shù)針對(duì)日期類(lèi)型數(shù)據(jù)運(yùn)算。轉(zhuǎn)換函數(shù)：轉(zhuǎn)換函數(shù)可以對(duì)數(shù)值、字符、日期三種類(lèi)型的數(shù)據(jù)進(jìn)行類(lèi)型轉(zhuǎn)換。其他函數(shù)：用來(lái)對(duì)特殊數(shù)據(jù)進(jìn)行處理或者完成一些高級(jí)的計(jì)算功能等。單行函數(shù)-16-ABS函數(shù)：返回一數(shù)值的絕對(duì)值CEIL函數(shù)：輸入?yún)?shù)可以是非整數(shù)值，但返回結(jié)果則是大于等于輸入?yún)?shù)的最小整數(shù)。FLOOR函數(shù)：返回一個(gè)小于或等于給定十進(jìn)制數(shù)的最大整數(shù)MOD函數(shù)：返回一個(gè)數(shù)除以另一數(shù)的余數(shù)ROUND函數(shù)：根據(jù)給定的精度舍入數(shù)值TRUNC函數(shù)：不對(duì)指定小數(shù)前或后的部分做相應(yīng)舍入選擇處理，而全部截去數(shù)值函數(shù)ABS(number)CEIL(number)MOD(number,divisor)TRUNC(number,precision)ROUND(number,precision)FLOOR(number)-17-CONCAT(strl,str2)函數(shù)：將兩個(gè)輸入字符串，組成一個(gè)INSTR函數(shù)：確定一個(gè)字符串在另一個(gè)字符串中的位置格式：LOWER函數(shù)：返回指定字符串的小寫(xiě)形式UPPER函數(shù)：返回指定字符串的大寫(xiě)形式LPAD函數(shù)：從左面填充指定的一組字符格式：RPAD函數(shù)：從右面填充指定的一組字符字符函數(shù)-1INSTR(string,substring[,start[,occurrence]])LPAD(STRING,LENGTH[,PADDING])-18-LTRIM函數(shù)：用于刪除字符串左邊的前綴字符格式：RTRIM函數(shù)：用于刪除字符串右邊的尾隨字符TRIM函數(shù)：用于刪除字符串的前綴（或尾隨）字符格式：函數(shù)中trimchar參數(shù)只能是一個(gè)字符SUBSTR函數(shù)：用于取得字符串的子串格式：REPLACE函數(shù)：用于替換字符串中的子串內(nèi)容格式：字符函數(shù)-2LTRIM(STRING[,TRIMCHARS])TRIM([LEADING|TRAILING|BOTH][TRIMCHARFROM]STRING)SUBSTR(STR,START[,LENGTH]))REPLACE(STR,SUB[,REPLACEMENT])-19-ADD_MONTHS函數(shù)：將一個(gè)日期上加上指定的月份數(shù)格式：CURRENT_DATE函數(shù)：返回當(dāng)前session所在時(shí)區(qū)的默認(rèn)時(shí)間EXTRACT函數(shù)：用于從日期時(shí)間值中摘取特定數(shù)據(jù)格式：日期時(shí)間函數(shù)-1ADD_MONTHS(DATE,MONTHS)EXTRACT(DATEPARTFROMDATETIME)-20-MONTHS_BETWEEN函數(shù)：返回兩個(gè)日期之間的月份數(shù)格式：NEXT_DAY函數(shù)：返回從實(shí)參日期開(kāi)始，緊隨其后的指定星期對(duì)應(yīng)的日期格式：ROUND函數(shù)：將一日期處理（舍入）成第二個(gè)參數(shù)指定的形式格式：日期時(shí)間函數(shù)-2MONTHS_BETWEEN(DATE1,DATE2)NEXT_DAY(DATE,WEEKDAY)ROUND(DATE[,FMT])-21-TO_CHAR函數(shù)：將NUMBER轉(zhuǎn)換為字符串TO_DATE函數(shù)：以定制格式將字符串轉(zhuǎn)換為日期型數(shù)據(jù)格式：TO_NUMBER函數(shù)：用于將字符串轉(zhuǎn)換成數(shù)字格式轉(zhuǎn)換函數(shù)TO_DATE(STRING[,FMT[,’NLS_DATE_LANGUAGE=LANGUAGE’]])-22-DECODE函數(shù)：相當(dāng)于條件語(yǔ)句(IF)，將輸入數(shù)值與函數(shù)中的參數(shù)列表相比較，根據(jù)輸入值返回一個(gè)對(duì)應(yīng)值。格式：NVL函數(shù)：用于將NULL轉(zhuǎn)變?yōu)閷?shí)際值格式：參數(shù)expr1和expr2可以是任意數(shù)據(jù)類(lèi)型，但兩者數(shù)據(jù)類(lèi)型必須匹配其他函數(shù)DECODE(INPUT,VALUE,RESULT[,VALUE,RESULT…][,DEFAULT_RESULT]);NVL(expr1,expr2);-23-COUNT函數(shù)：用于取得行數(shù)的總計(jì)。AVG、SUM函數(shù)：分別用于取得并返回指定列或表達(dá)式的平均值和總和。MAX、MIN函數(shù)：分別用于取得并返回指定列或表達(dá)式的最大值和最小值。分組函數(shù)-24-GROUPBY語(yǔ)句：按照指定的列進(jìn)行數(shù)據(jù)分組SELECT語(yǔ)句中出現(xiàn)的列必須出現(xiàn)在GROUPBY語(yǔ)句中HAVING語(yǔ)句：用于過(guò)濾分組后的數(shù)據(jù)，必須與GROUPBY一起使用數(shù)據(jù)分組的限制：引入GROUPBY語(yǔ)句的查詢(xún)操作中，在SELECT語(yǔ)句中出現(xiàn)的列，必須出現(xiàn)在GROUPBY子句中使用分組函數(shù)時(shí)，忽略NULL行分組函數(shù)只能出現(xiàn)在SELECT、ORDERBY、HAVING語(yǔ)句中ORDERBY用于對(duì)查詢(xún)結(jié)果進(jìn)行排序，必須放在分組語(yǔ)句之后

分組語(yǔ)句-25-查出工資最多的員工。查詢(xún)每個(gè)部門(mén)的最大工資是誰(shuí)。查詢(xún)工資總和高于10號(hào)部門(mén)工資總和的部門(mén)。子查詢(xún)、分組函數(shù)實(shí)例selectenamefromempwheresal=(selectmax(sal)fromemp);selectdeptno,ename,salfromempwhere(deptno,sal)in(selectdeptno,max(sal)fromempgroupbydeptno);selectdeptno,sum(sal)fromempgroupbydeptnohavingsum(sal)>(selectsum(sal)fromempwheredeptno=10);-26-插入單行數(shù)據(jù)格式：

按指定列插入數(shù)據(jù)插入所有列數(shù)據(jù)缺省數(shù)據(jù)列插入日期數(shù)據(jù)表的數(shù)據(jù)復(fù)制格式：插入數(shù)據(jù)INSERTINTOtable_name[(column[,column...])]VALUES(value[,value...]);INSERTINTOtable_name[column(,column...)]subquery;-27-插入單行數(shù)據(jù)

表的數(shù)據(jù)復(fù)制插入數(shù)據(jù)實(shí)例INSERTINTOdept(50,’technology’‘qingdao’);INSERTINTOdept2(deptno,dname,loc)selectdeptno,dname,locfromdept;-28-使用表達(dá)式更新數(shù)據(jù)更新指定列數(shù)據(jù)更新日期數(shù)據(jù)列更新缺省數(shù)據(jù)列使用子查詢(xún)更新數(shù)據(jù)更新當(dāng)前表數(shù)據(jù)更新基于另一個(gè)表的數(shù)據(jù)注：在UPDATE語(yǔ)句中使用子查詢(xún)時(shí)，必須確保該子查詢(xún)只為每個(gè)要更新的記錄返回一個(gè)值，否則UPDATE會(huì)失敗更新數(shù)據(jù)-29-刪除滿足條件的數(shù)據(jù)當(dāng)使用DELETE語(yǔ)句刪除表的所有數(shù)據(jù)時(shí)，不會(huì)釋放表格所占用的空間，如要?jiǎng)h除表格中的所有數(shù)據(jù)，可以使用TRUNCATETABLE語(yǔ)句，速度更快使用子查詢(xún)刪除數(shù)據(jù)刪除數(shù)據(jù)DELETE[FROM]table_nameWHEREconditioninsubquery；-30-使用子查詢(xún)刪除數(shù)據(jù)刪除數(shù)據(jù)DELETEdept2WHEREdeptnoin(selectdeptnofromdept)；-31-commit：事務(wù)的提交rollback：事務(wù)的回滾savepoint：可以使用savepoint設(shè)置事務(wù)的保存點(diǎn)，以控制事務(wù)進(jìn)行部分回滾。DML事務(wù)操作-32-SQL是一種介于關(guān)系代數(shù)和關(guān)系演算之間的結(jié)構(gòu)化查詢(xún)語(yǔ)言，是一個(gè)通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言SQL語(yǔ)言集數(shù)據(jù)查詢(xún)、數(shù)據(jù)操縱、數(shù)據(jù)定義和數(shù)據(jù)控制等功能于一體使用連接查詢(xún)可以實(shí)現(xiàn)多表查詢(xún)SQL函數(shù)