第05章-SQL語言基礎(chǔ)

Oracle應(yīng)用開發(fā)第05章SQL語言基礎(chǔ)本章內(nèi)容ODP-C05-01關(guān)系型數(shù)據(jù)庫概述

ODP-C05-02SQL語言概述

ODP-C05-03檢索數(shù)據(jù)

ODP-C05-04多表關(guān)聯(lián)查詢

ODP-C05-05Oracle常用系統(tǒng)函數(shù)

ODP-C05-06子查詢

ODP-C05-07操作數(shù)據(jù)庫

ODP-C05-08事務(wù)處理

本章目標1、掌握數(shù)據(jù)庫操作的基本語法。2、掌握數(shù)據(jù)庫的多種查詢方法。3、掌握數(shù)據(jù)庫的關(guān)聯(lián)查詢方法。4、掌握數(shù)據(jù)庫的條件查詢方法。5、掌握數(shù)據(jù)庫查詢過程中可使用的函數(shù)6、掌握數(shù)據(jù)庫的子查詢方法。7、掌握數(shù)據(jù)庫的插入、更新和刪除方法。8、掌握數(shù)據(jù)庫的事務(wù)概念。9、掌握數(shù)據(jù)庫的事務(wù)操作方法。第01節(jié)

關(guān)系型數(shù)據(jù)庫概述知識點預(yù)覽#節(jié)知識點難點重點應(yīng)用1ODP-C05-01關(guān)系型數(shù)據(jù)庫概述1、XML與數(shù)據(jù)庫比較

2、關(guān)系型數(shù)據(jù)庫與數(shù)據(jù)庫管理系統(tǒng)

3、關(guān)系型數(shù)據(jù)庫的E-R模型√√4、1-1關(guān)聯(lián)關(guān)系√√5、1-N關(guān)聯(lián)關(guān)系√√6、N-N關(guān)聯(lián)關(guān)系√√7、關(guān)系型數(shù)據(jù)庫的設(shè)計范式√1、XML與數(shù)據(jù)庫比較XML語言優(yōu)勢：是自描述性的（所用的標記描述了數(shù)據(jù)的結(jié)構(gòu)和類型，盡管缺乏語義）可交換的，能夠以樹型或圖形結(jié)構(gòu)描述的數(shù)據(jù)XML語言缺陷：較繁瑣，要對它進行解析和文本轉(zhuǎn)換，所以數(shù)據(jù)訪問速度較慢XML語言的數(shù)據(jù)庫特性：XML文件是以文檔為中心的數(shù)據(jù)的集合存儲特性——XML文檔模式——DTD,XMLschema等查詢語言特性——XQuery,XPath,XQL等編程接口特性——SAX,DOM,JDOM等XML缺乏的數(shù)據(jù)庫特性：高效的存儲、索引、安全機制、事務(wù)和數(shù)據(jù)一致性、多用戶訪問、觸發(fā)器等結(jié)論：在數(shù)據(jù)量小、用戶少和性能要求不太高的環(huán)境下，可以將XML文檔用作數(shù)據(jù)庫，但是卻不適用于用戶量大、數(shù)據(jù)集成度高以及性能要求高的作業(yè)環(huán)境。2、關(guān)系型數(shù)據(jù)庫和數(shù)據(jù)庫管理系統(tǒng)-1數(shù)據(jù)(Data)：描述事物的符號記錄稱為數(shù)據(jù)數(shù)據(jù)的種類有數(shù)字、文字、圖形、圖像、聲音、文本等數(shù)據(jù)庫（DataBase,簡稱DB）：存儲數(shù)據(jù)的倉庫數(shù)據(jù)之間存在一定的關(guān)聯(lián)關(guān)系，并非完全獨立，數(shù)據(jù)庫是儲存在計算機內(nèi)的、有組織的、可共享的數(shù)據(jù)集合數(shù)據(jù)庫中的數(shù)據(jù)按一定的數(shù)據(jù)模型儲存和管理，具有較小的冗余度、較高的數(shù)據(jù)獨立性和易擴展性，并可為各種用戶共享數(shù)據(jù)庫使得我們對數(shù)據(jù)的操作更加高效數(shù)據(jù)庫模型層次結(jié)構(gòu)模型：該模型的實質(zhì)是將數(shù)據(jù)以一棵定向有序樹的結(jié)構(gòu)存儲網(wǎng)狀結(jié)構(gòu)模型：是對層次結(jié)構(gòu)模型的提升，能更靈活地描述關(guān)系關(guān)系結(jié)構(gòu)模型：該模型把一些復(fù)雜的數(shù)據(jù)結(jié)構(gòu)歸結(jié)為簡單的二元關(guān)系(即二維表格形式)；由關(guān)系結(jié)構(gòu)模型組成的數(shù)據(jù)庫系統(tǒng)被稱為關(guān)系型數(shù)據(jù)庫2、關(guān)系型數(shù)據(jù)庫和數(shù)據(jù)庫管理系統(tǒng)-2關(guān)系模型E.F.Codd博士在1970年提出了數(shù)據(jù)庫系統(tǒng)的關(guān)系模型關(guān)系模型是將復(fù)雜的數(shù)據(jù)結(jié)構(gòu)歸結(jié)為簡單的二元關(guān)系，用二維表格的形式表示實體和實體間聯(lián)系的數(shù)據(jù)模型關(guān)系模型的組成由大量對象或關(guān)系組成的集合對關(guān)系可進行一系列操作的運算符可保證數(shù)據(jù)的完整性和一致性要求關(guān)系型數(shù)據(jù)庫使用關(guān)系模型建立的數(shù)據(jù)庫包含一組被組織為表結(jié)構(gòu)（table）的相關(guān)信息的集合，表結(jié)構(gòu)包括：列（Column）：又稱為字段，包含類型和約束信息行（row）：又稱為記錄，每條記錄描述一個實例表在數(shù)據(jù)庫中被存儲在稱為模式（schema）的結(jié)構(gòu)中2、關(guān)系型數(shù)據(jù)庫和數(shù)據(jù)庫管理系統(tǒng)-3OracleServer

ID LAST_NAME FIRST_NAME 10 Havel Marta 11 Magee Colin 12 Giljum Henry 14 Nguyen Mai

SALES_ ID NAME PHONE REP_ID 201 Unisports 55-2066101 12 202 SimmsAtheletics 81-20101 14 203 DelhiSports 91-10351 14 204 Womansport 1-206-104-0103 11TableName:S_CUSTOMERTableName:S_EMPRow(行)Column(列)2、關(guān)系型數(shù)據(jù)庫和數(shù)據(jù)庫管理系統(tǒng)-4數(shù)據(jù)庫系統(tǒng)(DataBase

Sytem，簡稱DBS)：數(shù)據(jù)庫系統(tǒng)是指在計算機系統(tǒng)中引入數(shù)據(jù)庫后的系統(tǒng)構(gòu)成通常數(shù)據(jù)庫系統(tǒng)由數(shù)據(jù)庫、數(shù)據(jù)庫管理系統(tǒng)(及其開發(fā)工具)、應(yīng)用系統(tǒng)、數(shù)據(jù)庫管理員等構(gòu)成數(shù)據(jù)庫管理系統(tǒng)(DataBase

Management

Sytem，簡稱DBMS)數(shù)據(jù)庫管理系統(tǒng)是位于用戶與操作系統(tǒng)之間的一層數(shù)據(jù)管理軟件，用于科學地組織和存儲數(shù)據(jù)、高效地獲取和維護數(shù)據(jù)DBMS的主要功能包括數(shù)據(jù)定義功能、數(shù)據(jù)操縱功能、數(shù)據(jù)庫的運行管理功能、數(shù)據(jù)庫的建立和維護功能常見的DBMS工具Oracle

DB2SQLServerMySQLDBMS主要功能數(shù)據(jù)庫定義功能數(shù)據(jù)庫操縱功能數(shù)據(jù)庫運行管理數(shù)據(jù)庫的建立和維護功能2、關(guān)系型數(shù)據(jù)庫和數(shù)據(jù)庫管理系統(tǒng)-5核心OS操作系統(tǒng)3、關(guān)系型數(shù)據(jù)庫的ER模型-1關(guān)系型數(shù)據(jù)庫設(shè)計的三個階段

概要設(shè)計將用戶需求轉(zhuǎn)換為概念模式設(shè)計數(shù)據(jù)庫E-R模型圖

邏輯設(shè)計將概念模型轉(zhuǎn)換為關(guān)系型數(shù)據(jù)模型E-R模型轉(zhuǎn)換為數(shù)據(jù)庫表運用數(shù)據(jù)庫設(shè)計三大范式進行審核

物理設(shè)計選擇具體數(shù)據(jù)庫進行物理實現(xiàn)建立表結(jié)構(gòu)，并編寫代碼實現(xiàn)前端應(yīng)用用戶需求概念模式（E-R模型）數(shù)據(jù)模式（關(guān)系模型）DB數(shù)據(jù)庫概要設(shè)計邏輯設(shè)計物理設(shè)計3、關(guān)系型數(shù)據(jù)庫的ER模型-2E-R模型（E-R圖）使用E-R圖描述概念模型，反映實體集和實體集間的關(guān)系E-R模型基本組成實體（Entity）：表示客觀存在且相互區(qū)別的事物，使用矩形框表示屬性（Attribute）：表示實體所具有的特性，使用橢圓形表示聯(lián)系（Relationship）：表示事物（實體）間的聯(lián)系，使用菱形框表示一對一聯(lián)系：反映在E-R圖中為1:1一對多聯(lián)系：反映在E-R圖中為1:n多對多聯(lián)系：反映在E-R圖中為m:n學生隸屬學號年齡名稱年級班級n1姓名4、1-1關(guān)聯(lián)關(guān)系1-1關(guān)聯(lián)關(guān)系（1:1）如果實體集E1中的每個實體只能和實體集E2中的一個實體有聯(lián)系，反之亦然，則稱實體集E1和E2是一對一的聯(lián)系，表示為1：1

關(guān)系模型設(shè)計一對一的關(guān)系模型，設(shè)計時可分為兩張表或合并為一張表需要設(shè)計兩張表時，可在其中一張表中建立新的字段，使其關(guān)聯(lián)另一張表的主鍵字段首都隸屬名稱人口名稱位置國家11IDCOUNTRYLOCATION1中國亞洲2美國北美洲IDNAMEPEOPLESCOUNTRYID1北京1000萬12紐約1500萬25、1-N關(guān)聯(lián)關(guān)系1-N關(guān)聯(lián)關(guān)系（1:n）如果實體集E1中的每個實體能和實體集E2中的多個實體有聯(lián)系，而反之E2中的每個實體只能關(guān)聯(lián)E1中的一個實體，則稱實體集E1(父/一)和E2(子/多)是一對多的聯(lián)系，表示為1：n

關(guān)系模型設(shè)計一對多的關(guān)系模型，設(shè)計時需將父表中的主關(guān)鍵字放入子表中IDNAMEGRADES1軟件一班03H2硬件二班04IDNAMEAGECLASSID1001Angel21S11002Sandy20S1學生隸屬學號年齡名稱年級班級n1姓名6、N-N關(guān)聯(lián)關(guān)系N-N關(guān)聯(lián)關(guān)系（n:n）實體集E1中的每個實體能和實體集E2中的多個實體有聯(lián)系，反之E2中的每個實體也能關(guān)聯(lián)E1中的多個實體，則稱實體集E1和E2是多對多的聯(lián)系，表示為n：n

關(guān)系模型設(shè)計多對多的關(guān)系模型，設(shè)計時需新增一個表，稱為關(guān)聯(lián)表，在關(guān)聯(lián)表中分別添加兩個表的主鍵和其他關(guān)聯(lián)字段學生選修學號年齡名稱學時課程nnIDNAMETIMEJ001Java語言48C002C++語言64STUIDNAMEAGES1Angel21S2Sandy20姓名編號STUIDCIDSCORES1J00185S2J00164S1C00278成績7、關(guān)系型數(shù)據(jù)庫的設(shè)計范式-1關(guān)系型數(shù)據(jù)庫的設(shè)計范式

關(guān)系型數(shù)據(jù)庫的設(shè)計標準就是三個范式：第一范式（1NF–FirstNormalFromate）：

要求數(shù)據(jù)庫表中的字段都是不可再分的最小數(shù)據(jù)單元例如：地址Address字段，可進一步拆分為多個字段7、關(guān)系型數(shù)據(jù)庫的設(shè)計范式-2第二范式（2NF–SecondNormalFromate）：

要求數(shù)據(jù)庫表中不存在非主鍵字段對任一候選關(guān)鍵字段的部分函數(shù)依賴；即對于組合主鍵（AB），要求里面的A或B與其他字段不能存在組合重復(fù)。解決方式：不采用組合主鍵的方式，盡量使用單一主鍵，確保表中的其他字段都只依賴于該單一主鍵7、關(guān)系型數(shù)據(jù)庫的設(shè)計范式-3第三范式（3NF–ThirdNormalFromate）：

要求數(shù)據(jù)庫表中不存在非主鍵字段對任一候選關(guān)鍵字段的傳遞函數(shù)依賴；

即如果存在A決定B，B決定C的關(guān)系，則C傳遞依賴于A

解決方法：將不直接依賴于主鍵的列拆分成獨立的表，確保表中的每個列只與主鍵直接相關(guān)而非間接相關(guān)第02節(jié)SQL語言概述知識點預(yù)覽#節(jié)知識點難點重點應(yīng)用2ODP-C05-02SQL語言概述1、SQL語言的特點

2、DDL數(shù)據(jù)定義語言

√3、DML數(shù)據(jù)操作語言√4、DCL數(shù)據(jù)控制語言√5、數(shù)據(jù)庫事務(wù)處理√1、SQL語言的特點什么是SQL語言？SQL（StructuredQueryLanguage）語言，全稱叫結(jié)構(gòu)化查詢語言，是一種數(shù)據(jù)庫訪問的通用語言現(xiàn)行的數(shù)據(jù)庫幾乎都支持SQL語言，如Sybase、Oracle、SQLServer等SQL語言的特點高度非過程化的語言統(tǒng)一的語法結(jié)構(gòu)，可以獨立完成數(shù)據(jù)庫生命周期中的全部活動語法結(jié)構(gòu)簡潔，易學易用所有關(guān)系型數(shù)據(jù)庫的公共語言SQL的功能與分類定義數(shù)據(jù)庫中存儲的信息，包括表、視圖、索引等（DDL）對數(shù)據(jù)庫中的表進行增刪改操作（DML）對數(shù)據(jù)庫中的表進行查詢檢索（DQL）對數(shù)據(jù)庫中的對象進行權(quán)限管理（DCL）2、DDL數(shù)據(jù)定義語言數(shù)據(jù)定義語言(DataDefinitionLanguage，簡稱DDL)用于定義數(shù)據(jù)庫的邏輯結(jié)構(gòu)DDL語言包括對數(shù)據(jù)庫對象的定義，包括數(shù)據(jù)庫、數(shù)據(jù)表、視圖或索引的創(chuàng)建、修改、刪除等操作語句說明CREATE創(chuàng)建數(shù)據(jù)庫對象ALTER修改數(shù)據(jù)庫對象DROP刪除數(shù)據(jù)庫對象RENAME更改表名TRUNCATE刪除表的全部內(nèi)容3、DML數(shù)據(jù)操作語言數(shù)據(jù)操作語言(DataManipulationLanguage,簡稱DML)用于對數(shù)據(jù)庫表中的數(shù)據(jù)進行操作包括對表中的數(shù)據(jù)進行增加、修改、刪除和查詢等操作語句說明SELECT從表中查詢數(shù)據(jù)INSERT向表中添加記錄DELETE刪除記錄UPDATE修改記錄的內(nèi)容MERGE將一個表中的記錄合并到另外一個表中4、DCL數(shù)據(jù)控制語言數(shù)據(jù)控制語言(DataControlLanguage，簡稱DCL)用于對數(shù)據(jù)庫中的對象進行權(quán)限設(shè)置只有數(shù)據(jù)庫管理員才有權(quán)限進行該操作提高數(shù)據(jù)庫的安全性語句說明GRANT授予其他用戶對數(shù)據(jù)庫結(jié)構(gòu)的訪問權(quán)限REVOKE阻止其他用戶訪問數(shù)據(jù)庫結(jié)構(gòu)5、數(shù)據(jù)庫事物處理事務(wù)控制語言(TransactionControl,簡稱TCL)用于控制對表中的記錄所做的一系列修改永久性地存儲到表中，或者取消這些修改操作語句說明COMMIT永久性地保存對數(shù)據(jù)所做的修改ROLLBACK取消對數(shù)據(jù)所做的修改SAVEPOINT設(shè)置一個“保存點”，可以將對行所做的修改回滾到此處第03節(jié)

檢索數(shù)據(jù)知識點預(yù)覽#節(jié)知識點難點重點應(yīng)用3ODP-C05-03檢索數(shù)據(jù)1、簡單查詢

√√2、篩選查詢

√√3、排序查詢√√√4、分組查詢√√√1、簡單查詢-1基本的SELECT語句SELECT語句用于從數(shù)據(jù)庫中檢索信息，通常稱為讀數(shù)據(jù)基本語法星號符（*）：表示查詢表中所有的列Col_name：指定要查詢的列名，多個列之間用逗號分隔Table_name：要查詢的列所在的表名稱示例SELECT*|{col_name1,col_name2,…}FROMtable_name;SELECT*FROMemp;SELECTempno,ename,job,salFROMemp;1、簡單查詢-2算術(shù)表達式Oracle允許在SQL語句中使用算術(shù)表達式來進行算術(shù)運算算數(shù)表達式可以修改數(shù)據(jù)的顯示方式、執(zhí)行計算等常用的算術(shù)運算符：+：加法-：減法*：乘法/：除法示例查看某個員工的工號，姓名和年薪，其中員工表中的sal字段只代表月薪，年薪需要進行運算才能獲得Selectempno,ename,sal*12fromemp;1、簡單查詢-3使用列別名Oracle允許在查詢語句中重命名列名別名緊跟在列名后（列名和別名之間也可加關(guān)鍵字AS）基本語法示例查看某個員工的工號，姓名和年薪如果別名包含空格或特殊字符，或者區(qū)分大小寫則需要使用雙引號Selectempno,ename,sal*12asannual_salaryfromemp;SELECTcol_name[AS]new_col_nameFROMtable_name;Selectempno,ename,sal*12“AnnualSalary”fromemp;1、簡單查詢-4連接操作符“||”O(jiān)racle允許在SQL中使用”||”連接操作符實現(xiàn)字符串連接使用||可以使多列的值或者特殊的字符串合并到一個列進行顯示基本語法示例查看某個員工的工號和姓名，要求顯示全名SELECTempno,first_name||

''||last_nameAS“EmployeeName"FROMs_emp;SELECTcol_name1||

‘special_char'||

col_name2FROMtable_name;1、簡單查詢-5使用DISTINCT關(guān)鍵字在SQL中使用DISTINCT可以確保重復(fù)的記錄只顯示一條基本語法注意：distinct關(guān)鍵詞必須緊跟select關(guān)鍵詞后面如果distinct關(guān)鍵詞后面如果出現(xiàn)多列，表示多列聯(lián)合去重，即多列的值都相同的時候才會認為是重復(fù)的記錄示例查看所有員工的職位名稱和部門id，要求同職位同部門的只顯示一次SELECTDISTINCTjob,deptnoFROMemp;SELECTDISTINCTcol_name1,col_name2,…FROM

table_name;2、篩選查詢-1限制查詢在Oracle中使用WHERE子句可以指定條件進行查詢基本語法注意：where子句的優(yōu)先級別最高condition條件語句可以包含多個條件表達式條件表達式由列名、表達式、常數(shù)和比較操作符組成，常見的比較操作符包括：比較運算符SQL操作符邏輯操作符SELECTcol_name1,col_name2,…FROM

table_nameWHERE

condition(s);2、篩選查詢-2比較運算符運算符說明=等于<>或!=不等于<小于>大于<=小于或等于>=大于或等于ANY與一個列表中的任何值進行比較ALL與一個列表中的所有值進行比較2、篩選查詢-3使用比較運算符查看薪資小于1000的員工查看部門編號大于20或30中任意一個的員工記錄查看部門編號比20和30都大的員工記錄Selectempno,ename,salfromempwheresal<1000;Selectempno,ename,deptnofromempwheredeptno>ANY(20,30);Selectempno,ename,deptnofromempwheredeptno>ALL(20,30);2、篩選查詢-4SQL操作符LIKE操作符使用LIKE能實現(xiàn)模糊查詢功能，當取值不是精確值時使用搜索條件可包含通配符（代替任何內(nèi)容的符號）：

%：

通配零個或多個字符_：僅通配一個字符示例：查找名字第三個字母為’O’的員工運算符說明LIKE模糊查詢，當取值不是精確的值時使用IN與值列表中的任一值匹配BETWEENAND介于兩個值之間（包含這兩個值）SELECT*FROMempWHEREenameLIKE‘__O%’;2、篩選查詢-5IN操作符使用IN關(guān)鍵字可以檢索其列值在某個列表中的記錄示例查看工號為7788,7844,7566的員工信息BETWEENAND操作符使用BETWEENAND操作符可以檢索值在指定區(qū)間內(nèi)的記錄示例查看工資在700到1500之間的員工信息SELECTempno,ename,salFROMempWHEREsalBETWEEN700AND1500;SELECTempno,ename,salFROMempWHEREempnoIN(7788,7844,7566);2、篩選查詢-6邏輯操作符當條件存在多個的時候可使用該操作符示例查看員工部門為10或者30號部門且工資大于1000的員工運算符說明XANDY當x和y都為ture時，返回trueXORY當x和y中有一個為true時，就返回trueNOTX如果x為false,則返回true；如果x為true，則返回falseSELECTempno,ename,deptno,jobFROM

empWHEREsal>1000AND

(deptno=10ORdeptno=30);3、排序查詢-1排序排序就是查詢時將指定字段的值按照升序或者降序排列ASC：升序排列（默認）DESC：降序排列注意：使用orderby子句只對查詢記錄的顯示進行調(diào)整，并不改變查詢結(jié)果，所以執(zhí)行權(quán)最低，即最后才執(zhí)行如果有多個列排序，后面的列排序的前提是前面的列已經(jīng)排好序以后有重復(fù)（相同）的值SELECTcol_name,...FROMtable_nameORDERBYcol_name[ASC|DESC],...3、排序查詢-2示例查看s_emp中的員工信息，并按員工姓名降序排列查看s_emp中的員工信息，并按first_name升序排列，當first_name存在重復(fù)值時，將按last_name進行降序排列SELECTid,last_name,salaryFROMs_empORDERBYlast_nameDESC;

SELECTid,first_name,last_nameFROMs_empORDERBYfirst_nameASC, last_nameDESC;

4、分組查詢-1使用GROUPBY語句SQL查詢語句中可以使用GROUPBY子句實現(xiàn)分組查詢，即將某些相同值的列進行分組，并實現(xiàn)對數(shù)據(jù)的歸納統(tǒng)計GROUPBY子句通常需要結(jié)合分組函數(shù)實現(xiàn)統(tǒng)計功能GROUPBYcol_name：將數(shù)據(jù)按照col_name列進行分組常見的組函數(shù)：AVG（）：求平均值COUNT（）：求總數(shù)MAX（）：求最大值MIN（）：求最小值SUM（）：求和SELECTcol_name,...FROMtable_nameWHEREcondition…GROUPBYcol_name,...[ORDERBYcol_name…]4、分組查詢-2GROUPBY示例查看所有部門的部門員工工資總和，并按照部門工資降序排序注意：沒有出現(xiàn)在groupby子句中或組函數(shù)中的列，不能出現(xiàn)在select子句中當groupby子句中出現(xiàn)多列的時候，會按照從左至右的順序進行分組，即先按照第一列分組，然后在第一列分好的組里面，再按照第二列進行分組，以此類推。。。SELECTdept_id,SUM(salary)dept_salaryFROMs_empGROUPBYdept_idORDERBYdept_salaryDESC;---------------錯誤案例---------------SELECTdept_id,last_name,MAX(salary)FROMs_empGROUPBYdept_id;4、分組查詢-3使用HAVING語句HAVING語句通常與GROUPBY一起使用，限制搜索條件，實現(xiàn)分組過濾與WHERE語句不同，HAVING子句只與分組相關(guān)，不與單個列值有關(guān)HAVING子句只作用于GROUPBY中創(chuàng)建的組，通常結(jié)合組函數(shù)應(yīng)用如果限制條件中出現(xiàn)組函數(shù)，必須使用HAVING，不能使用WHERESELECTcolumn,group_function(column)FROMtable[WHERE condtion][GROUPBYgroup_by_expression][HAVING group_condtion][ORDERBYcolumn];SELECTproduct_type_id,AVG(price)FROMproductsWHEREprice<15GROUPBYproduct_type_idHAVINGAVG(pirce)>13ORDERBYproduct_type_id;第04節(jié)

多表關(guān)聯(lián)查詢知識點預(yù)覽#節(jié)知識點難點重點應(yīng)用4ODP-C05-04多表關(guān)聯(lián)查詢1、內(nèi)連接√√√2、左外部連接√√√3、右外部連接4、自連接1、內(nèi)連接-1多表查詢又稱多表聯(lián)合查詢，即查詢語句中涉及到多張表，通過特定的方式將多個表進行連接，顯示查詢結(jié)果基本語法在WHERE子句中編寫連接條件如果在多個表中出現(xiàn)了相同的列名，則在列名的前面加上表名作為前綴連接的類型內(nèi)連接：當連接中的列滿足連接條件時才會返回，只能查出匹配的記錄等值連接：在連接中使用（=）操作符不等值連接：在連接中使用除等號之外的操作符外連接：即使連接條件中的一列包含空值也會返回一行自連接：返回連接到同一個表中的行SELECTtable1.column1,table2.column2FROM

table1,table2WHEREtable1.column3=table2.column3…;1、內(nèi)連接-2customeremps外鍵主鍵等值連接等值連接查詢后結(jié)果列表中不存在207客戶信息1、內(nèi)連接-3內(nèi)連接也稱簡單連接，只能查詢出匹配的記錄，不匹配的記錄無法查出內(nèi)連接是最常用的查詢方式可以使用表別名簡化查詢等值連接使用等號（=）操作符連接條件示例：查看每個員工的工號，姓名以及部門名稱和所屬區(qū)域名稱不等值連接使用”>”、”<“、”<>”、”BETWEENAND”等除等號外的操作符連接多個表SELECTe.id,e.last_name,d.id,,FROMs_empe,s_deptd,s_regionrWHEREe.detp_id=d.idANDd.region_id=r.id;SELECTtest1.idFROMtest1,test2WHEREtest1.idBETWEENtest2.min_valueANDtest2.max_value;2、左外連接-1外連接使用內(nèi)連接時，如果某個行不滿足連接條件，那么該行將不會出現(xiàn)在查詢結(jié)果中當一個表中的記錄在另外一個表中不存在的時候，使用外連接可以顯示這些不存在的結(jié)果外連接使用的操作符：(+)外連接的類型左外連接不僅僅返回符合連接條件的行記錄，還保留左邊表中的全部記錄右外連接不僅僅返回符合連接條件的行記錄，還保留右邊表中的全部記錄全外連接不僅僅返回符合連接條件的行記錄，還保留兩個表中的未匹配記錄2、左外連接-2左外連接使用左外連接，表示當左邊表（=號左邊的表）中的記錄在右邊表中不存在的時候，依舊顯示左邊表的所有記錄不僅僅返回符合連接條件的記錄，還保留左邊表中的全部記錄未匹配的記錄，其右表對應(yīng)的列補空值示例：查看所有客戶的工號、名字和對應(yīng)的銷售人員姓名，即使無對應(yīng)銷售人員也顯示SELECTtb_name,col_name,...FROMtb_name1,tb_name2WHEREtb_name1.col_name=tb_name2.col_name(+);SELECTc.id,,e.last_nameFROMs_customerc,s_empeWHEREc.sales_rep_id=e.id(+)ORDERBYc.id;3、右外連接右外連接使用右外連接，表示當右邊表（=號右邊的表）中的記錄在左邊表中不存在的時候，依舊顯示右邊表的所有記錄不僅僅返回符合連接條件的記錄，還保留右邊表中的全部記錄未匹配的記錄，其左表對應(yīng)的列補空值示例：查看所有員工的姓名，及其負責的客戶號和客戶名稱，即使無對應(yīng)客戶的也顯示SELECTtb_name,col_name,...FROMtb_name1,tb_name2WHEREtb_name1.col_name(+)=tb_name2.col_name;SELECTc.id,,e.last_nameFROMs_customerc,s_empeWHEREc.sales_rep_id(+)=e.idORDERBYc.id;4、自連接自連接對同一個表進行的連接必須使用不同的表別名來標識在查詢中每次對表的引用示例：查詢員工姓名及其經(jīng)理名字SELECT……FROMtb_namet1,tb_namet2WHEREt1.col_name=t2.another_col_name;SELECTworker.last_name,manager.last_nameFROMemployeesworker,employeesmanagerWHEREworker.manager_id=manager.employee_id;第05節(jié)Oracle常用系統(tǒng)函數(shù)知識點預(yù)覽#節(jié)知識點難點重點應(yīng)用5ODP-C05-05Oracle常用系統(tǒng)函數(shù)1、字符類函數(shù)√√2、數(shù)字類函數(shù)√√3、日期和時間類函數(shù)√√4、轉(zhuǎn)換類函數(shù)√√√5、聚合類函數(shù)√√√1、字符類函數(shù)-1SQL函數(shù)功能進行數(shù)據(jù)計算修改單個數(shù)據(jù)項處理成組的行的輸出指定日期和數(shù)字的顯示格式轉(zhuǎn)換列的數(shù)據(jù)類型SQL函數(shù)類型單行函數(shù)：查詢表或視圖時，作用于單行記錄上，且只返回一個結(jié)果字符型函數(shù)數(shù)值型函數(shù)日期時間型函數(shù)轉(zhuǎn)換函數(shù)聚合函數(shù)：聚合函數(shù)作用于多行記錄上，返回一個結(jié)果，主要用于分組查詢中1、字符類函數(shù)-2常用的字符型函數(shù)大小寫處理函數(shù)函數(shù)說明LOWER(x)將x中的字母轉(zhuǎn)換成小寫UPPER(x)將x中的字母轉(zhuǎn)換成大寫INITCAP(x)將x中每個單詞的首字母都轉(zhuǎn)換成大寫，并返回得到的字符串1、字符類函數(shù)-3字符處理函數(shù)函數(shù)說明CONCAT（x,y）將y添加到x上，并將得到的字符串作為結(jié)果返回SUBSTR(x,start[,length])返回x中的一個子字符串，這個字符串從start處開始，還可以為這個子字符串指定一個可選的length參數(shù)LENGTH(x)返回x中字符的個數(shù)INSTR(x,find_string[,start[,occurrence]])在x中查找字符串find_string，然后返回find_string所在的位置?？梢蕴峁┛蛇x的start位置來指定該函數(shù)從這個位置開始查找。同樣，也指定一個可選的occurrence參數(shù)，來說明該返回find_string第幾次出現(xiàn)的位置LPAD(x,width[,pad_string])在x的左邊補齊空格，得到總長為width個字符的字符串。該函數(shù)可以提供一個可選的pad_string,這個參數(shù)用于指定重復(fù)使用哪個字符串來補齊x左邊的空位。返回x被補齊之后的結(jié)果字符串RPAD(x,width[,pad_string])與LPAD（）類似，不過要對x的右邊補齊1、字符類函數(shù)-4示例使用LENGTH函數(shù)顯示name列中字符串的長度將description列中的每個單詞的首字母轉(zhuǎn)換成大寫查看員工名字為chang的員工的id和薪資，chang不區(qū)分大小寫，即（CHANGChangchang）都符合SELECTname,LENGTH(name)FROMproducts;SELECTproduct_id,INITCAP(description)FROMproducts;SELECTid,last_name,salaryFROMs_empWHERELOWER(last_name)='chang';2、數(shù)字類函數(shù)-1常見數(shù)值型函數(shù)MOD函數(shù)函數(shù)說明MOD（x,y）返回x除以y的余數(shù)ROUND(x[,y])返回對x取整的結(jié)果，會進行四舍五入；TRUNC(x[,y])返回對x直接截斷的結(jié)果，不進行四舍五入；SELECTMOD(8,3),MOD(8,4)FROMdual;2、數(shù)字類函數(shù)-2ROUND(x,[y])函數(shù)用于計算對x取整的結(jié)果，會進行四舍五入其中y為可選參數(shù)，說明對第幾位小數(shù)取整如果沒有指定y，則x在0位小數(shù)處取整數(shù)，取整時四舍五入如果y是負數(shù)，則對x在小數(shù)點的左邊的第|y|位處取整數(shù)，取整時四舍五入示例使用ROUND函數(shù)分別對5.75在0位、1位和-1位取整SELECT

ROUND(5.75),ROUND(5.75,1),ROUND(5.75,-1)FROMdual;2、數(shù)字類函數(shù)-3TRUNC(x,[y])函數(shù)用于計算對x數(shù)據(jù)直接截斷的結(jié)果，不會進行四舍五入y為可選參數(shù)，說明對第幾位小數(shù)截斷如果沒有y，則對在0位小數(shù)截斷如果y為負數(shù)，則對x在小數(shù)點左邊的第|y|位處截斷示例對5.75在0位、1位、-1位小數(shù)處截斷SELECT

TRUNC(5.75),TRUNC(5.75,1),TRUNC(5.75,-1)FROMdual;3、日期和時間類函數(shù)-1常用日期和時間函數(shù)函數(shù)說明SYSDATE返回系統(tǒng)的當前日期SYSTIMESTAMP返回系統(tǒng)當前時間，包含時區(qū)信息，精確到毫秒ADD_MONTHS(date,integer)返回在指定月份上加指定月數(shù)后的日期NEXT_DAY(date,char)返回當前日期向后一周的char所指定的對應(yīng)日期LAST_DAY(date)返回參數(shù)指定的日期對應(yīng)月份的最后一天MONTHS_BETWEEN(date1,date2)返回date1和date2之間的月份數(shù)…………3、日期和時間類函數(shù)-2系統(tǒng)表空間獲取系統(tǒng)當前日期返回當前日期一周后的星期一所對應(yīng)的日期返回當前日期過兩個月后的日期返回當前日期和指定日期的間隔月數(shù)SELECTSYSDATEFROMDUAL;SELECTNEXT_DAY(SYSDATE,‘星期一’)FROMDUAL;SELECTADD_MONTHS(SYSDATE,2)FROMDUAL;SELECTMONTHS_BETWEEN(SYSDATE,TO_DATE('2013-04-03','yyyy-mm-dd'))FROMDUAL;4、轉(zhuǎn)換類函數(shù)-1常用轉(zhuǎn)換類函數(shù)函數(shù)說明TO_CHAR(x)將一個日期或數(shù)值類型數(shù)據(jù)轉(zhuǎn)換為一個字符串TO_NUMBER（x[,format])將一個字符串類型數(shù)據(jù)轉(zhuǎn)換為一個NUMBER類型TO_DATE(x[,format])將字符串類型數(shù)據(jù)轉(zhuǎn)換為一個DATE類型CAST(xAStype)將x轉(zhuǎn)換為type所指定的兼容數(shù)據(jù)庫類型TO_BLOB(x)將x轉(zhuǎn)換為一個二進制大對象類型…………4、轉(zhuǎn)換類函數(shù)-2使用TO_CHAR函數(shù)處理數(shù)字其中format參數(shù)參數(shù)格式說明9999返回指定位置處的數(shù)字0099999900999：返回一個數(shù)字，前面補09990：返回一個數(shù)字，后面補0.999.99在指定位置處返回一個小數(shù)點,9,999在指定位置處返回一個逗號$$999在數(shù)字開頭返回一個美元符號SELECT

TO_CHAR(12345.67,'99,999.99')FROMdual;4、轉(zhuǎn)換類函數(shù)-3使用TO_CHAR函數(shù)處理日期其中format參數(shù)參數(shù)說明YYYY用數(shù)字表示完整年份YEAR拼寫出的年份（用英文標識）MM月份的兩位數(shù)值MONTH月份的完整名稱MON月份的三個字母縮寫DY一周中某日的三個字母縮寫DAY一周中某日的完整名稱DD用數(shù)字表示的月份中某日4、轉(zhuǎn)換類函數(shù)-4格式樣式具有以下特點必須放在單引號內(nèi)區(qū)分大小寫可以包含任何有效的日期格式元素輸出的日和月的名稱會自動用空白進行填充與日期之間用逗號分隔示例SELECTcustomer_id,TO_CHAR(reg_date,'MONTHDD,YYYY')FROMcustomers;4、轉(zhuǎn)換類函數(shù)-5TO_NUMBER函數(shù)用于將字符串轉(zhuǎn)換為一個數(shù)字指定一個可選參數(shù)format字符來說明char的格式示例使用TO_NUMBER函數(shù)將字符串970.13轉(zhuǎn)換為一個數(shù)字，然后加25.5SELECTto_number('970.13')+25.5fromdual;TO_NUMBER(char[,’format’])5、聚合類函數(shù)-1聚合函數(shù)聚合函數(shù)主要用于分組查詢操作中，因此又稱組函數(shù)參數(shù)說明AVG(x)返回x的平均值COUNT(x)統(tǒng)計指定列的查詢返回的行數(shù)MAX(x)返回x的最大值MEDIAN(x)返回x的中間值MIN(x)返回x的最小值STDDEV(x)返回x的標準差SUM(x)返回x的和VARIANCE(x)返回x的方差5、聚合類函數(shù)-2示例統(tǒng)計產(chǎn)品價格的平均值統(tǒng)計產(chǎn)品的數(shù)量統(tǒng)計產(chǎn)品價格的最大值和最小值統(tǒng)計所有產(chǎn)品價格之和SELECTAVG(price)FROMproducts;SELECTCOUNT(product_id)FROMproducts;SELECTMAX(price),MIN(price)FROMproducts;SELECTSUM(price)FROMproducts;第06節(jié)

子查詢知識點預(yù)覽#節(jié)知識點難點重點應(yīng)用6ODP-C05-06子查詢1、子查詢概述√√2、單行子查詢√√3、多行子查詢√√4、關(guān)聯(lián)子查詢√1、子查詢概述-1為什么需要子查詢？通常當WHERE子句后面的條件不是一個確切的值或表達式，要依賴于另一個查詢語句的查詢結(jié)果時，需要使用子查詢什么是子查詢？子查詢也稱嵌套查詢，是嵌套在另一個SQL語句中的SELECT查詢語句子查詢可以嵌套在SELECT、UPDATE和DELETE語句中子查詢本質(zhì)是WHERE子句的一個條件表達式SELECTcol_name,…FROMtable_nameWHEREcol_name比較操作符( SELECTcol_name1,.. FROMtb_name [WHEREcondition…])1、子查詢概述-2使用子查詢的準則將子查詢擴在括號內(nèi)將子查詢放置在比較條件的右側(cè)比較條件雙方數(shù)據(jù)類型和數(shù)量要匹配單行運算符用于單行子查詢，而多行運算符用于多行子查詢子查詢的類型單行子查詢從內(nèi)部SELECT語句中僅返回一行的查詢使用單行比較操作符：=、>、>=、<、<=、<>多行子查詢從內(nèi)部SELECT語句中返回多行的查詢使用量化操作符：IN、ANY、ALL、SOME2、單行子查詢-1單行子查詢從內(nèi)部SELECT語句中僅返回一行的查詢使用單行比較操作符：=、>、>=、<、<=、<>示例查看職位名稱和名字為Chang的員工一樣的所有員工工號和名字SELECTid,last_name,titleFROMs_empWHEREtitle=(

SELECTtitle FROMs_emp WHERElast_name='Chang’);2、單行子查詢-2示例查詢所有價格高于商品平均價格的產(chǎn)品號、產(chǎn)品名稱和產(chǎn)品價格SELECTproduct_id,name,priceFROMproductsWHERE

price>( SELECTAVG(price)

FROMproducts);2、單行子查詢-3在HAVING子句中使用子查詢Oracle允許在分組查詢中的HAVING子句中使用子查詢示例查詢平均價格低于同產(chǎn)品組平均價格最大值的產(chǎn)品的product_id和平均價格注意：ORDERBY排序子句不能出現(xiàn)在子查詢中，只能用于外部查詢SELECTproduct_type_id,AVG(price)FROMproductsGROUPBYproduct_type_idHAVINGAVG(price)<( SELECTMAX(AVG(price)) FROMproducts GROUPBYproduct_type_id)ORDERBYproduct_type_id;2、單行子查詢-4在FROM中使用子查詢在外部查詢的FROM子句中使用子查詢，子查詢?yōu)镕ROM子句提供內(nèi)聯(lián)數(shù)據(jù)，也稱為內(nèi)聯(lián)視圖示例：在外部查詢中從products表中檢索product_id和price列，在子查詢中檢索一種產(chǎn)品已經(jīng)被購買的次數(shù)SELECTduct_id,price,purchases_duct_countFROMproductsprds,( SELECTproduct_id,COUNT(product_id)product_count FROMpurchases GROUPBYproduct_id)purchases_dataWHEREduct_id=purchases_duct_id;3、多行子查詢-1多行子查詢返回多個行的子查詢稱為多行子查詢使用的是量化運算符：函數(shù)說明IN匹配子查詢返回的結(jié)果列表中的任意一個即可ANY將值與子查詢返回的任意一個值進行比較ALL將值與子查詢返回的每個值進行比較SOME與ANY含義類似，ANY多用于非”=”的情況3、多行子查詢-2IN操作符用來檢查在一個值中是否包含指定的值這個值列表可以是來自一個子查詢返回的結(jié)果示例查找在子查詢中所返回的product_id值列表中是否包含指定的product_id，并顯示其產(chǎn)品號和產(chǎn)品名稱SELECTproduct_id,nameFROMproductsWHEREproduct_idIN( SELECTproduct_id FROMproducts WHEREnameLIKE'%e%‘);3、多行子查詢-3ANY操作符用來將一個值與一個列表中的任何值進行比較在ANY操作符前，必須使用一個=、<>、<、>、<=或>=操作符示例檢查是否有員工的工資低于salary_grades表中任何一級最低工資SELECTemployee_id,last_nameFROMemployeesWHEREsalary<ANY

( SELECTlow_salary FROMsalary_grades);3、多行子查詢-4ALL操作符用來將一個值與一個列表中的所有值進行比較在ALL操作符前，必須使用一個=、<>、<、>、<=或>=操作符示例檢查是否有員工的工資高于salary_grades表中所有級別的最高工資SELECTemployee_id,last_nameFROMemployeesWHEREsalary>ALL

( SELECThigh_salary FROMsalary_grades);4、關(guān)聯(lián)子查詢-1集合操作符使用集合操作符可以將兩個或多個查詢返回的記錄組合起來集合運算符的準則SELECT列表中的表達式在數(shù)量上必須匹配第二個查詢中每一列的數(shù)據(jù)類型必須與第一個查詢中對應(yīng)列的數(shù)據(jù)類型相匹配ORDERBY子句只能出現(xiàn)在語句的末尾函數(shù)說明UNIONALL合并各個檢索出的所有行，包括重復(fù)行UNION合并各個檢索出的所有行，不包括重復(fù)行MINUS返回從第一個查詢檢索出的行減去第二個查詢檢索出的行之后剩余的記錄（差集）INTERSECT返回兩個查詢檢索出的公有記錄（交集）4、關(guān)聯(lián)子查詢-2示例表使用UNIONALL操作符SELECTproduct_id,product_type_id,nameFROMproductsUNIONALLSELECTprd_id,prd_type_id,nameFROMmore_products;4、關(guān)聯(lián)子查詢-3使用UNION操作符使用INTERSECT操作符SELECTproduct_id,product_type_id,nameFROMproductsUNIONSELECTprd_id,prd_type_id,nameFROMmore_products;SELECTproduct_id,product_type_id,nameFROMproductsINTERSECTSELECTprd_id,prd_type_id,nameFROMmore_products;4、關(guān)聯(lián)子查詢-4使用MINUS操作符SELECTproduct_id,product_type_id,nameFROMproductsMINUSSELECTprd_id,prd_type_id,nameFROMmore_products;第07節(jié)

操作數(shù)據(jù)庫知識點預(yù)覽#節(jié)知識點難點重點應(yīng)用7ODP-C05-07操作數(shù)據(jù)庫1、插入數(shù)據(jù)√√2、更新數(shù)據(jù)√√3、刪除數(shù)據(jù)√√4、TRUNCATE語句√1、插入數(shù)據(jù)-1INSERT語句使用INSERT語句可以向數(shù)據(jù)庫表中插入記錄INSERT語句一次只能插入一條記錄基本語法注意插入一行新記錄時，該記錄的每一列都含有值，沒有顯式指定值的列會使用NULL空值INSERT語句中允許只指定表名不指定列名，要求插入時各個值的順序是按照表中列的默認順序排列的，且數(shù)據(jù)類型相互匹配要求字符型和日期型數(shù)據(jù)用單引號括起來INSERTINTOtable_name[(column1[,column2…])]VALUES(value1[,value2…]);1、插入數(shù)據(jù)-2示例向S_CUSTOMERS表中插入一條新記錄顯式插入空值，要求對應(yīng)的列沒有非空約束隱式插入空值INSERTINTO

s_customers(customer_id,first_name,last_name,dob,phone)VALUES(6,'fred','brown','01-1月-1970','800-555-1215');INSERTINTO

s_customersVALUES(6,'fred','brown',NULL,NULL);INSERTINTO

s_customers(customer_id,first_name,last_name)VALUES(6,'fred','brown');1、插入數(shù)據(jù)-3常見錯誤示例錯誤1：主鍵值為NULL空值錯誤2：插入的主鍵值與表中其他記錄的主鍵值重復(fù)錯誤3：對存在NOTNULL非空約束的列插入NULL值INSERTINTO

s_customers(customer_id,first_name,last_name)VALUES(NULL,'fred','brown');INSERTINTO

s_customers(customer_id,first_name)VALUES(6,NULL);INSERTINTO

s_customers(customer_id,first_name,last_name)VALUES(1,'fred','brown');1、插入數(shù)據(jù)-4INSERT中使用子查詢INSERT語句中，可以不使用列值，而是使用查詢從一個表向另外一個表復(fù)制行要求源表和目標表的列數(shù)和列的類型匹配不要使用VALUES子句示例使用SELECT語句檢索顧客編號為1的first_name和last_name值，并將其作為顧客號10的顧客的姓名插入數(shù)據(jù)庫表中INSERTINTOs_customers(customer_id,first_name,last_name)SELECT10,first_name,last_nameFROMs_customersWHEREcustomer_id=1;2、更新數(shù)據(jù)-1UPDATE語句使用UPDATE語句可以更新數(shù)據(jù)庫表中的記錄UPDATE語句一次可以更新多條記錄基本語法注意可以指定WHERE條件語句，指定要更新的記錄如果不指定WHERE條件語句，將更新表中的所有記錄可以在UPDATE語句中使用子查詢UPDATEtable_nameSETcolumn=value[,column=value,…][WHEREconditions…];2、更新數(shù)據(jù)-2UPDATE示例修改滿足WHERE條件的記錄沒有指定WHERE條件時，會修改表中的所有記錄UPDATEs_customersSETlast_name=‘Oracle’WHEREcustomer_id=1;UPDATEs_customersSETlast_name=‘Oracle’;2、更新數(shù)據(jù)-3UPDATE中使用子查詢可以在UPDATE語句的SET子句中使用子查詢更新列值示例更新工號為4的員工的職位與薪資，使之與工號為2的員工的職務(wù)薪資相同UPDATE s_empsSETtitle=(SELECTtitle FROMs_emps WHEREemp_id=2),

salary=(SELECTsalary FROMs_emps WHEREemp_id=2)WHERE emp_id=4;3、刪除數(shù)據(jù)-1DELETE語句使用DELETE語句可以刪除數(shù)據(jù)庫表中的記錄DELETE語句一次可以刪除多條記錄基本語法注意可以使用WHERE條件語句刪除指定的記錄如果不使用WEHER語句，將刪除表中的所有記錄在DELETE語句中也可以使用子查詢DELETE[FROM]table_name[WHEREconditions…];3、刪除數(shù)據(jù)-2DELETE示例刪除滿足WHERE條件的記錄沒有指定WHERE條件時，會刪除表中的所有記錄使用子查詢，從products表中刪除產(chǎn)品類型名稱為Book的產(chǎn)品DELETEFROMs_customersWHEREcustomer_id=1;DELETEFROMs_customers;DELETEFROMproductsWHEREproduct_id= (SELECTpro